Re: [GENERAL] pg_restore load data

On 11/16/2017 03:13 PM, bricklen wrote: On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson > wrote: v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.) During a "whole database" restore using pg_restore of a custom dump,

Re: [GENERAL] pg_restore load data

The command is COPY, not load. Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone Original message From: Ron Johnson <ron.l.john...@cox.net> Date: 11/16/17 16:07 (GMT-05:00) To: pgsql-general@postgresql.org Subject: [GENERAL] pg_restore load data Hi, v9.2.7  (

Re: [GENERAL] pg_restore load data

On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson wrote: > v9.2.7 (Yes, I know, it's old. Nothing I can do about it.) > > During a "whole database" restore using pg_restore of a custom dump, when > is the data actually loaded? I've looked in the list output and don't see >

[GENERAL] pg_restore load data

Hi, v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.) During a "whole database" restore using pg_restore of a custom dump, when is the data actually loaded?  I've looked in the list output and don't see any "load" statements. Thanks -- World Peace Through Nuclear Pacification

Re: [GENERAL] pg_restore error -- missing pg table

--- t...@sss.pgh.pa.us wrote: From: Tom Lane <t...@sss.pgh.pa.us> To: "" <kbran...@pwhome.com> cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_restore error -- missing pg table Date: Mon, 11 Sep 2017 13:43:16 -0400 "" <kbran...@pwhome.com> writ

Re: [GENERAL] pg_restore error -- missing pg table

"" writes: > /opt/pgsql-9.5/bin/pg_restore -v --clean --create --dbname=nms > --schema=public . > ... > pg_restore: [archiver (db)] could not execute query: ERROR: function > _pg_sv_column_array(oid, smallint[]) does not exist > LINE 6: _pg_sv_column_array(k1.conrelid,

[GENERAL] pg_restore error -- missing pg table

We've been doing backups and restores with many tests and uses and never had a problem until now. Sadly, I'm not sure where to even begin looking to figure out what to do, so any pointers would be very much appreciated! The vital stats: Pg: 9.5.1 (this will get upgraded to 10.0 when it's

Re: [GENERAL] pg_restore misuse or bug?

Assuming you have a user called "test", this will create a database that suffers from this problem. create database mvtest; \c mvtest create table main_table (id serial not null, something varchar(20), primary key (id)); create table child_table (id serial not null, parent_id int not null,

Re: [GENERAL] pg_restore misuse or bug?

This is on version 9.5, 9.6 and 10beta2. I could probably make a test-case over the weekend if I'm at home. On 21 July 2017 at 17:03, Tom Lane wrote: > Jordan Gigov writes: >> When running pg_restore as the superuser it gives the following error >>

Re: [GENERAL] pg_restore misuse or bug?

Jordan Gigov writes: > When running pg_restore as the superuser it gives the following error > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 3253; 0 320628 > MATERIALIZED VIEW DATA combined_query_data web_user >

[GENERAL] pg_restore misuse or bug?

So we have this database dump created using "pg_dump -Fc ourdb > file.dump" that has a Materialized View that gets refreshed under certain conditions by our Java web-app. When running pg_restore as the superuser it gives the following error pg_restore: [archiver (db)] Error while PROCESSING TOC:

Re: [GENERAL] pg_restore successful with warnings returns exit code of non-zero

On 03/02/2017 07:39 AM, dhanuj hippie wrote: I have a pg dump (custom format, column-inserts) which I'm restoring on an existing DB as " /usr/bin/pg_restore -a -v -d db1 -F c " and the input is passed in from stdin. It warns on a couple of existing rows, but completes successfully with "WARNING:

[GENERAL] pg_restore successful with warnings returns exit code of non-zero

I have a pg dump (custom format, column-inserts) which I'm restoring on an existing DB as " /usr/bin/pg_restore -a -v -d db1 -F c " and the input is passed in from stdin. It warns on a couple of existing rows, but completes successfully with "WARNING: errors ignored on restore: 5". However, this

Re: [GENERAL] pg_restore to a port where nobody is listening?

>It isn't consistent but it's by purpose. And there's a really good reason for >that behaviour. There's no issue with psql connecting to a >default database >because psql doesn't do anything by itself. pg_restore will do something to >the database it connects to. It might drop >some objects,

Re: [GENERAL] pg_restore to a port where nobody is listening?

On 12/21/2016 12:59 PM, Guillaume Lelarge wrote: 2016-12-21 20:29 GMT+01:00 Daniel Westermann >: >> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d -C -j 2 /var/tmp/exp/ >> >>

Re: [GENERAL] pg_restore to a port where nobody is listening?

On 12/21/2016 11:29 AM, Daniel Westermann wrote: Providing "-d" gives a meaningful message at least: postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p === -d postgres -F d -C /var/tmp/exp/ pg_restore: [archiver (db)] connection to database "postgres" failed: invalid port

Re: [GENERAL] pg_restore to a port where nobody is listening?

2016-12-21 20:29 GMT+01:00 Daniel Westermann < daniel.westerm...@dbi-services.com>: > >> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 > -F d -C -j 2 /var/tmp/exp/ > >> > >> This runs fine but where does it connect to? Nothing is listening on > port 5439. > > >Given the

Re: [GENERAL] pg_restore to a port where nobody is listening?

>> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d >> -C -j 2 /var/tmp/exp/ >> >> This runs fine but where does it connect to? Nothing is listening on port >> 5439. >Given the lack of a -d switch, I'd expect it not to try to connect >anywhere, just emit the

Re: [GENERAL] pg_restore to a port where nobody is listening?

Daniel Westermann writes: > postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d > -C -j 2 /var/tmp/exp/ > This runs fine but where does it connect to? Nothing is listening on port > 5439. Given the lack of a -d switch, I'd expect

Re: [GENERAL] pg_restore to a port where nobody is listening?

On Wed, Dec 21, 2016 at 6:22 PM, Daniel Westermann wrote: > I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on the > same host. . > What do I miss? I can give any port to pg_restore and it just seems to be > fine. , are you by

Re: [GENERAL] pg_restore to a port where nobody is listening?

Am 21.12.2016 um 18:22 schrieb Daniel Westermann: Now I try to import into 9.6.1 => the instance is not running but the environment is set: postgres@pgbox:/home/postgres/ [PG961] netstat -na | grep 5439 postgres@pgbox:/home/postgres/ [PG961] echo $PGPORT 5439

Re: [GENERAL] pg_restore to a port where nobody is listening?

It does Sent from my Phone > On 21 Dec 2016, at 18:40, Andreas Kretschmer wrote: > > > >> Am 21.12.2016 um 18:22 schrieb Daniel Westermann: >> >> >> Now I try to import into 9.6.1 => the instance is not running but the >> environment is set: >> >>

Re: [GENERAL] pg_restore to a port where nobody is listening?

On 12/21/2016 09:22 AM, Daniel Westermann wrote: Hi all, I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on the same host. I dump the 9.5.4 instance with: pg_dump -h localhost -p 5438 -C -c -F d -j 2 -f /var/tmp/exp/ test .. which runs fine. I get the output as expected:

[GENERAL] pg_restore to a port where nobody is listening?

Hi all, I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on the same host. I dump the 9.5.4 instance with: pg_dump -h localhost -p 5438 -C -c -F d -j 2 -f /var/tmp/exp/ test .. which runs fine. I get the output as expected: postgres@pgbox:/home/postgres/ [PG954] ls

Re: [GENERAL] pg_restore --clean failing due to dependancies

Le 16/11/2016 à 20:05, Tom Lane a écrit : Arnaud Lesauvage writes: [ dump from problematic database ] OK, thanks for the test case. The problem here is that pg_dump is setting up a circular dependency that it doesn't know how to break correctly. You've got a

Re: [GENERAL] pg_restore --clean failing due to dependancies

Arnaud Lesauvage writes: > [ dump from problematic database ] OK, thanks for the test case. The problem here is that pg_dump is setting up a circular dependency that it doesn't know how to break correctly. You've got a couple of views that are implicitly dependent on

Re: [GENERAL] pg_restore --clean failing due to dependancies

On 11/15/2016 08:09 AM, Arnaud L. wrote: Le 15/11/2016 à 16:44, Tom Lane a écrit : You'd have to provide a lot more detail before anyone could tell if there was a fixable bug here, but I rather doubt it. There are at least two ways this scenario might lose: 1. There are additional objects in

Re: [GENERAL] pg_restore --clean failing due to dependancies

On 11/15/2016 07:55 AM, Arnaud L. wrote: Le 15/11/2016 à 16:21, Adrian Klaver a écrit : The restore fails on a lot of statements, complaining about dependencies. For instance, "cannot drop rule _RETURN on view myview1 because view myview1requires it". Or "cannot drop constraint mypkey on table

Re: [GENERAL] pg_restore --clean failing due to dependancies

"Arnaud L." writes: > The target databased was restored from the dump in an empty database. > So what I do is : > dropdb -U postgres -h localhost db1 > createdb -U postgres -h localhost db1 > psql -U postgres -h localhost -c "CREATE EXTENSION postgis" db1 > pg_dump -h

Re: [GENERAL] pg_restore --clean failing due to dependancies

Le 15/11/2016 à 16:44, Tom Lane a écrit : You'd have to provide a lot more detail before anyone could tell if there was a fixable bug here, but I rather doubt it. There are at least two ways this scenario might lose: 1. There are additional objects in the target database that have dependencies

Re: [GENERAL] pg_restore --clean failing due to dependancies

Le 15/11/2016 à 16:21, Adrian Klaver a écrit : The restore fails on a lot of statements, complaining about dependencies. For instance, "cannot drop rule _RETURN on view myview1 because view myview1requires it". Or "cannot drop constraint mypkey on table my table because other objects depend on

Re: [GENERAL] pg_restore --clean failing due to dependancies

"Arnaud L." writes: > Hi am making daily backups of a database and omitting two schemas from > the dump (and the public schema). Those schemas are read-only and have > no dependancy with the rest of the database. > My command is : > pg_dump -h pgsql1 -U postgres -b -Fc

Re: [GENERAL] pg_restore --clean failing due to dependancies

On 11/15/2016 07:11 AM, Arnaud L. wrote: Hi all Postgresql 9.3.14 on Windows. Hi am making daily backups of a database and omitting two schemas from the dump (and the public schema). Those schemas are read-only and have no dependancy with the rest of the database. My command is : pg_dump -h

[GENERAL] pg_restore --clean failing due to dependancies

Hi all Postgresql 9.3.14 on Windows. Hi am making daily backups of a database and omitting two schemas from the dump (and the public schema). Those schemas are read-only and have no dependancy with the rest of the database. My command is : pg_dump -h pgsql1 -U postgres -b -Fc -E UTF8 -N

Re: [GENERAL] pg_restore question

ubject: Re: [GENERAL] pg_restore question Date: Mon, 19 Sep 2016 12:46:24 -0700 On 09/19/2016 11:46 AM, kbrannen(at)pwhome(dot)com wrote: But if I dump using: pg_dump --format=d -j4 --file=/tmp/exp # (an empty dir) then the restore with with the schema still there and relying on --clean to

Re: [GENERAL] pg_restore question

>On 09/19/2016 01:06 PM, kbrannen(at)pwhome(dot)com wrote: >>> --- adrian(dot)klaver(at)aklaver(dot)com wrote: >>> >>> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> >>> To: kbrannen(at)pwhome(dot)com, pgsql-general(at)postgresql(dot)org &

Re: [GENERAL] pg_restore question

On 09/19/2016 01:06 PM, kbran...@pwhome.com wrote: --- adrian.kla...@aklaver.com wrote: From: Adrian Klaver <adrian.kla...@aklaver.com> To: kbran...@pwhome.com, pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_restore question Date: Mon, 19 Sep 2016 12:46:24 -0700 On 09/19/2016 11

Re: [GENERAL] pg_restore question

On 09/19/2016 01:06 PM, kbran...@pwhome.com wrote: --- adrian.kla...@aklaver.com wrote: From: Adrian Klaver <adrian.kla...@aklaver.com> To: kbran...@pwhome.com, pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_restore question Date: Mon, 19 Sep 2016 12:46:24 -0700 On 09/19/2016 11

Re: [GENERAL] pg_restore question

> --- adrian.kla...@aklaver.com wrote: > > From: Adrian Klaver <adrian.kla...@aklaver.com> > To: kbran...@pwhome.com, pgsql-general@postgresql.org > Subject: Re: [GENERAL] pg_restore question > Date: Mon, 19 Sep 2016 12:46:24 -0700 > > On 09/19/2016 11:46 AM, kb

Re: [GENERAL] pg_restore question

On 09/19/2016 11:46 AM, kbran...@pwhome.com wrote: I think I'm going to need some help in understanding a couple of restore issues. This is for Pg 9.5.1. It seems that if I create a dump using pg_dump --clean --create --format=p --dbname=nms --schema=public > dump.sql then the restore

[GENERAL] pg_restore question

I think I'm going to need some help in understanding a couple of restore issues. This is for Pg 9.5.1. It seems that if I create a dump using pg_dump --clean --create --format=p --dbname=nms --schema=public > dump.sql then the restore (after "drop schema public cascade") with "psql nms <

Re: [GENERAL] pg_restore out of memory

Greetings, To all those who helped out with this problem, I'm sorry it took me so long to respond. For the record, the matter is solved, at least for us, but we had to repeat a lot of things to make sure. First, the "out of memory" problem repeated itself when restoring that single table,

Re: [GENERAL] pg_restore out of memory

Greetings, To all those who helped out with this problem, I'm sorry it took me so long to respond. For the record, the matter is solved, at least for us, but we had to repeat a lot of things to make sure. First, the "out of memory" problem repeated itself when restoring that single table,

Re: [GENERAL] pg_restore out of memory

A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu: > > A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu: > > > > I wrote: > > > > > > I'm still suspicious that this might be some sort of NOTICE- > > > processing- > > > related buffer bloat.  Could you try loading the data with the > >

Re: [GENERAL] pg_restore out of memory

A Sex, 15-07-2016 às 07:16 -0700, Adrian Klaver escreveu: > On 07/15/2016 12:37 AM, Miguel Ramos wrote: > > What else? > > The pg_dump file you are restoring from is a custom format. > > Do you have room to do something like?: > > 1) pg_restore -d some_db -U some_user -t

Re: [GENERAL] pg_restore out of memory

On 07/15/2016 12:37 AM, Miguel Ramos wrote: A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu: A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu: I wrote: I'm still suspicious that this might be some sort of NOTICE- processing- related buffer bloat. Could you try loading the data

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > I see (transcribed by hand from screenshot): > ... > pg_restore: processing data for table "inspection.positioned_scan" > out of memory > Process returned exit code 1. Right, so that confirms that the OOM happens while sending data for that table;

Re: [GENERAL] pg_restore out of memory

A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu: > > A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu: > > > > I wrote: > > > > > > I'm still suspicious that this might be some sort of NOTICE- > > > processing- > > > related buffer bloat.  Could you try loading the data with the > >

Re: [GENERAL] pg_restore out of memory

Yes. Both 9.1.8, I checked right now. -- Miguel A Qua, 13-07-2016 às 13:59 -0700, John R Pierce escreveu: > On 7/13/2016 1:51 PM, Miguel Ramos wrote: > > Finally, here are the log messages at the moment of the error. > > It is clearly not while building indices. > > > > The table in question

Re: [GENERAL] pg_restore out of memory

A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu: > I wrote: > > I'm still suspicious that this might be some sort of NOTICE- > > processing- > > related buffer bloat.  Could you try loading the data with the > > server's > > log_min_messages level cranked down to NOTICE, so you can see from

Re: [GENERAL] pg_restore out of memory

A Qua, 13-07-2016 às 17:15 -0400, Tom Lane escreveu: > Miguel Ramos writes: > > So, what does this mean? > > Was it the client that aborted? I think I saw that "unexpected > > message > > type 0x58" on other types of interruptions. > > Yeah, 0x58 is ASCII 'X'

Re: [GENERAL] pg_restore out of memory

That's 3 years and 3 months with absolutely zero maintenance. Apart from the scripts I left back then. During that time, it was used by an average of 10 people, some 9T of sensor data entered at the rate of 60G/week, and another 3T of analysis data was produced. The expression "cutting down on

Re: [GENERAL] pg_restore out of memory

I wrote: > I'm still suspicious that this might be some sort of NOTICE-processing- > related buffer bloat. Could you try loading the data with the server's > log_min_messages level cranked down to NOTICE, so you can see from the > postmaster log whether any NOTICEs are being issued to the

Re: [GENERAL] pg_restore out of memory

On 7/13/2016 2:11 PM, Miguel Ramos wrote: Yes. Both 9.1.8, I checked right now. 9.1 is up to 9.1.22, thats a lot of bug fixes you're missing. 9.1.8 was released 2013-02-07, 9.1.22 in 2016-05-12 -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > So, what does this mean? > Was it the client that aborted? I think I saw that "unexpected message > type 0x58" on other types of interruptions. Yeah, 0x58 is ASCII 'X' which is a Terminate message. Between that and the unexpected-EOF

Re: [GENERAL] pg_restore out of memory

On 07/13/2016 01:51 PM, Miguel Ramos wrote: Finally, here are the log messages at the moment of the error. It is clearly not while building indices. The table in question is a big one, 111GB. Fields latitude, longitude and height are arrays of length around 500- 700 on each row (double and

Re: [GENERAL] pg_restore out of memory

Yes. Both 9.1.8, I checked right now. -- Miguel A Qua, 13-07-2016 às 13:59 -0700, John R Pierce escreveu: > On 7/13/2016 1:51 PM, Miguel Ramos wrote: > > Finally, here are the log messages at the moment of the error. > > It is clearly not while building indices. > > > > The table in question

Re: [GENERAL] pg_restore out of memory

A Ter, 12-07-2016 às 13:08 +, Sameer Kumar escreveu: > On Tue, 12 Jul 2016, 7:25 p.m. Miguel Ramos, > wrote: > > I found two relevant threads on the mailing-lists. > > The most recent one sugested that postgresql was being configured > > to use > > more

Re: [GENERAL] pg_restore out of memory

On 7/13/2016 1:51 PM, Miguel Ramos wrote: Finally, here are the log messages at the moment of the error. It is clearly not while building indices. The table in question is a big one, 111GB. Fields latitude, longitude and height are arrays of length around 500- 700 on each row (double and real).

Re: [GENERAL] pg_restore out of memory

Finally, here are the log messages at the moment of the error. It is clearly not while building indices. The table in question is a big one, 111GB. Fields latitude, longitude and height are arrays of length around 500- 700 on each row (double and real). So, what does this mean? Was it the

Re: [GENERAL] pg_restore out of memory

On Tue, Jul 12, 2016 at 12:25:08PM +0100, Miguel Ramos wrote: > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom archiver] out of memory > > 12:09:56.58 9446.593u+1218.508s 24.3% 167+2589k 6+0io 0pf+0sw 6968822cs ... > I suspect that the restore fails

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > This because I have the impression that it is during index creation, > where I think client role would be minimal. Hard to believe really, given the spelling of the message. But anyway, be sure you do the run with log_statement = all so

Re: [GENERAL] pg_restore out of memory

A Ter, 12-07-2016 às 11:58 -0400, Tom Lane escreveu: >  > Anyway, it would be useful to try running the restore with a more > modern > version of pg_restore, to see if that helps. > > regards, tom lane > > I have the scheduled restart tonight. So, I will do the other test

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > Às 15:40 de 12-07-2016, Tom Lane escreveu: >> Unless you're running pg_restore under a really small ulimit, this would >> seem to suggest some kind of memory leak in pg_restore itself. I wonder >> how many objects in your dump (how long is

Re: [GENERAL] pg_restore out of memory

Às 16:23 de 12-07-2016, Miguel Ramos escreveu: It looks to me like this error is pg_restore itself running out of memory, not reporting a server-side OOM condition. You could verify that by looking in the server log to see whether any out-of-memory error appeared there. But assuming that I'm

Re: [GENERAL] pg_restore out of memory

Às 15:40 de 12-07-2016, Tom Lane escreveu: Miguel Ramos writes: We have backed up a database and now when trying to restore it to the same server we get this: # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump pg_restore: [custom archiver]

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > We have backed up a database and now when trying to restore it to the > same server we get this: >>> # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump >>> pg_restore: [custom archiver] out of memory It looks to me like

Re: [GENERAL] pg_restore out of memory

On Tue, 12 Jul 2016, 7:25 p.m. Miguel Ramos, < org.postgre...@miguel.ramos.name> wrote: > > Hi, > > We have backed up a database and now when trying to restore it to the > same server we get this: > > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom

Re: [GENERAL] pg_restore out of memory

2016-07-12 8:54 GMT-03:00 Miguel Ramos : > > Às 12:32 de 12-07-2016, Felipe Santos escreveu: > >> I would try lowering max_connections to 50 and then set work_mem to 128MB. >> >> After that restart your server and retry the restore. >> > > Ok, I will try

Re: [GENERAL] pg_restore out of memory

Às 12:32 de 12-07-2016, Felipe Santos escreveu: I would try lowering max_connections to 50 and then set work_mem to 128MB. After that restart your server and retry the restore. Ok, I will try restarting tonight. work_mem is the parameter I was most afraid of. I'll post some news in 24h...

Re: [GENERAL] pg_restore out of memory

2016-07-12 8:25 GMT-03:00 Miguel Ramos : > > Hi, > > We have backed up a database and now when trying to restore it to the same > server we get this: > > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom archiver] out of

[GENERAL] pg_restore out of memory

Hi, We have backed up a database and now when trying to restore it to the same server we get this: > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > pg_restore: [custom archiver] out of memory > 12:09:56.58 9446.593u+1218.508s 24.3% 167+2589k 6+0io 0pf+0sw 6968822cs

Re: [GENERAL] pg_restore error-s after pg_dump

On 06/22/2016 04:00 AM, SDAG wrote: Hi Postgres version : *PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit* We transfer from one server to another (with better cpu an ram) our system using vm converter and when I try to backup

[GENERAL] pg_restore error-s after pg_dump

Hi Postgres version : *PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit* We transfer from one server to another (with better cpu an ram) our system using vm converter and when I try to backup database have an error : *pg_dump: reading

Re: [GENERAL] pg_restore casts check constraints differently

Amit Langote writes: > destdb=# ALTER TABLE c ADD CONSTRAINT p_a_check CHECK (a IN ('a', 'b', 'c')); > destdb=# \d c > ... > Check constraints: > "p_a_check" CHECK (a::text = ANY (ARRAY['a'::character varying, > 'b'::character varying, 'c'::character

Re: [GENERAL] pg_restore casts check constraints differently

On Wed, Mar 30, 2016 at 6:45 AM, Tom Lane wrote: > Joshua Ma writes: >> This might not be a common case, but we're using pg_dump in a testing >> environment to check migrations - 1) we initialize the db from HEAD, >> pg_dump it, 2) we initialize the db

Re: [GENERAL] pg_restore casts check constraints differently

On Tue, Mar 29, 2016 at 7:40 PM, Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane wrote: > >> It's not really different. What you're seeing is pg_dump (or actually > >> ruleutils.c)

Re: [GENERAL] pg_restore casts check constraints differently

"David G. Johnston" writes: > On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane wrote: >> It's not really different. What you're seeing is pg_dump (or actually >> ruleutils.c) choosing to dump some implicit casts explicitly to ensure >> that the

Re: [GENERAL] pg_restore casts check constraints differently

On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane wrote: > Joshua Ma writes: > > This might not be a common case, but we're using pg_dump in a testing > > environment to check migrations - 1) we initialize the db from HEAD, > > pg_dump it, 2) we initialize the db

Re: [GENERAL] pg_restore casts check constraints differently

Joshua Ma writes: > This might not be a common case, but we're using pg_dump in a testing > environment to check migrations - 1) we initialize the db from HEAD, > pg_dump it, 2) we initialize the db from migration_base.sql, apply > migrations, pg_dump it, and 3) compare the

[GENERAL] pg_restore casts check constraints differently

This might not be a common case, but we're using pg_dump in a testing environment to check migrations - 1) we initialize the db from HEAD, pg_dump it, 2) we initialize the db from migration_base.sql, apply migrations, pg_dump it, and 3) compare the two dumps to verify that our migrations are

Re: [GENERAL] pg_restore fails

Hi Karsten.. On Sun, Mar 13, 2016 at 12:09 AM, Karsten Hilbert wrote: > I am trying to pg_restore from a directory dump. > However, despite using > > --clean > --create > --if-exists > > I am getting an error because schema PUBLIC already exists.

Re: [GENERAL] pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 05:49:56PM -0700, David G. Johnston wrote: > > > I'd operate under the premise that all warnings and errors are fatal > > (i.e., keep --exit-on-error) until you cannot for some very specific >

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 05:49:56PM -0700, David G. Johnston wrote: > I'd operate under the premise that all warnings and errors are fatal > (i.e., keep --exit-on-error) until you cannot for some very specific > reason. --exit-on-error will exit on _any_ perceived error, regardless of whether it

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 5:43 PM, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 05:31:38PM -0700, David G. Johnston wrote: > > > > The reason being, of course, that I want to check the exit > > > code in a pg_restore wrapper script. > > > > > > > > I mistakenly thought

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 05:31:38PM -0700, David G. Johnston wrote: > > The reason being, of course, that I want to check the exit > > code in a pg_restore wrapper script. > > > > > I mistakenly thought public only came from template1...I wouldn't be > opposed to that change. This all seems

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 5:31 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > You probably should just drop the existing database and use --create by > itself. > > You can even use the dropdb command to avoid SQL in your script. > > ​This seems like it is the main problem: # dropdb

Re: [GENERAL] pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote: > > > The docs could probably use improvement here - though I am inferring > > behavior from description and not code. > > > > The create option tells

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote: > The docs could probably use improvement here - though I am inferring > behavior from description and not code. > > The create option tells restore that it is pointless to use conditions or > actively drop objects since the

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 4:32 PM, Adrian Klaver wrote: > On 03/12/2016 03:09 PM, Karsten Hilbert wrote: > >> Hi, >> >> Debian Stretch >> PG 9.5.1 >> >> I am trying to pg_restore from a directory dump. >> >> However, despite using >> >> --clean >>

Re: [GENERAL] pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert wrote: > Hi, > > Debian Stretch > PG 9.5.1 > > I am trying to pg_restore from a directory dump. > > However, despite using > > --clean > --create > --if-exists > > I am getting an error

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 03:32:15PM -0800, Adrian Klaver wrote: > > pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: > > pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8; > > 2615 2200 SCHEMA public postgres > > pg_restore: [Archivierer (DB)] could

Re: [GENERAL] pg_restore fails

On 03/12/2016 03:09 PM, Karsten Hilbert wrote: Hi, Debian Stretch PG 9.5.1 I am trying to pg_restore from a directory dump. However, despite using --clean --create --if-exists I am getting an error because schema PUBLIC already exists. That schema

Re: [GENERAL] pg_restore fails

On Sun, Mar 13, 2016 at 12:09:19AM +0100, Karsten Hilbert wrote: In case it is needed: > pg_restore: erstelle SCHEMA „public“ creating SCHEMA "public" > pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: Error in Phase ... > pg_restore:

[GENERAL] pg_restore fails

Hi, Debian Stretch PG 9.5.1 I am trying to pg_restore from a directory dump. However, despite using --clean --create --if-exists I am getting an error because schema PUBLIC already exists. That schema is, indeed, included in the dump to be restored and

Re: [GENERAL] pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 03:59:58PM -0500, Melvin Davidson wrote: > fyi, since the version of PostgreSQL was NOT stated (or O/S) as is the > proper thing to do when posting, I gave a generic solution which covers all > versions and O/S's That's an important point. Karsten -- GPG key ID E4071346

Re: [GENERAL] pg_restore without dropping db/table

fyi, since the version of PostgreSQL was NOT stated (or O/S) as is the proper thing to do when posting, I gave a generic solution which covers all versions and O/S's On Thu, Mar 10, 2016 at 3:53 PM, Karsten Hilbert wrote: > On Thu, Mar 10, 2016 at 01:49:42PM -0500,

Re: [GENERAL] pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 01:49:42PM -0500, Melvin Davidson wrote: > The best way to accomplish what you want is to create a table with the same > structure in the first database as the one you want to restore to. Then you > can truncate that table, restore the data from the other db into it, and >

Re: [GENERAL] pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 12:53 PM, Adrian Klaver wrote: > On 03/10/2016 09:41 AM, Karsten Hilbert wrote: > >> On Thu, Mar 10, 2016 at 10:51:05AM -0500, anj patnaik wrote: >> >> Does pg_restore only add new rows if I restore without deleting old db? >>> >> >> No. For one

Re: [GENERAL] pg_restore without dropping db/table

On 03/10/2016 09:41 AM, Karsten Hilbert wrote: On Thu, Mar 10, 2016 at 10:51:05AM -0500, anj patnaik wrote: Does pg_restore only add new rows if I restore without deleting old db? No. For one thing, pg_restore cannot know what you consider to be a "new row". If you however do know what is

  1   2   3   4   5   6   >