pg_restore issue

On 02/12/2014 09:41 AM, Leonardo M. Ram� wrote:

Hi, I'm trying to restore a database dump using pg_restore with the
following parameters:

pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup

Note I used "\" to wrap the command, but the real one does not have
those.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
CONSTRAINT fkidturno postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.turnodocumento" does not exist
Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
fkidturno;

Why is this happening?, should I change the command I'm using to create
the backup?.

Did you look in the restored database to see if everything is correct or
not?

What version of pg_dump did you use to do the pg_dump, the 8.4 or 9.2 one?

It is recommended that you use the later version to dump older databases
as it can deal with any changes that have occurred.

To backup the database I'm using:

pg_dump -Fc -h 127.0.0.1 -U postgres mydb > my_dump.backup

P.S.: the original db is PostgreSql 8.4, the target is 9.2

Regards,

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= <l.rame@griensu.com> writes:

Hi, I'm trying to restore a database dump using pg_restore with the
following parameters:

pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup

Note I used "\" to wrap the command, but the real one does not have
those.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
CONSTRAINT fkidturno postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.turnodocumento" does not exist
Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
fkidturno;

Why is this happening?, should I change the command I'm using to create
the backup?.

The -c switch causes pg_restore to try to DROP every object it's about to
restore. If you're restoring into an empty database then this is useless,
and in fact will not work if you're also using --exit-on-error. Remove
one or the other switch.

Now, if you expected that all the objects do exist in the target database,
then it might be worth inquiring a bit more closely into what's happening.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2014-02-12 09:51:10 -0800, Adrian Klaver wrote:

On 02/12/2014 09:41 AM, Leonardo M. Ram� wrote:

Hi, I'm trying to restore a database dump using pg_restore with the
following parameters:

pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup

Note I used "\" to wrap the command, but the real one does not have
those.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
CONSTRAINT fkidturno postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.turnodocumento" does not exist
Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
fkidturno;

Why is this happening?, should I change the command I'm using to create
the backup?.

Did you look in the restored database to see if everything is
correct or not?

What version of pg_dump did you use to do the pg_dump, the 8.4 or 9.2 one?

It is recommended that you use the later version to dump older
databases as it can deal with any changes that have occurred.

To backup the database I'm using:

pg_dump -Fc -h 127.0.0.1 -U postgres mydb > my_dump.backup

P.S.: the original db is PostgreSql 8.4, the target is 9.2

Regards,

Thanks Adrian, but I cannot use a newer pg_dump version because I cannot
upgrade it inside the remote server, also, as the db is very large I
prefer to do the backup internally and rsync the file.

--
Leonardo M. Ram�
Medical IT - Griensu S.A.
Av. Col�n 636 - Piso 8 Of. A
X5000EPT -- C�rdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2014-02-12 13:30:52 -0500, Tom Lane wrote:

Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= <l.rame@griensu.com> writes:

Hi, I'm trying to restore a database dump using pg_restore with the
following parameters:

pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup

Note I used "\" to wrap the command, but the real one does not have
those.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
CONSTRAINT fkidturno postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.turnodocumento" does not exist
Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
fkidturno;

Why is this happening?, should I change the command I'm using to create
the backup?.

The -c switch causes pg_restore to try to DROP every object it's about to
restore. If you're restoring into an empty database then this is useless,
and in fact will not work if you're also using --exit-on-error. Remove
one or the other switch.

Now, if you expected that all the objects do exist in the target database,
then it might be worth inquiring a bit more closely into what's happening.

regards, tom lane

Tom, I've dropped the db, then createdb again, then removed the -c
option, now I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 735; 2612 213488
PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language
"plpgsql" already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;

--
Leonardo M. Ram�
Medical IT - Griensu S.A.
Av. Col�n 636 - Piso 8 Of. A
X5000EPT -- C�rdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Leonardo M. Ram� <l.rame@griensu.com> writes:

On 2014-02-12 13:30:52 -0500, Tom Lane wrote:

Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= <l.rame@griensu.com> writes:

Hi, I'm trying to restore a database dump using pg_restore with the
following parameters:

pg_restore -h 127.0.0.1 -U _postgresql \
-c -d postgres --exit-on-error \
my_dump.backup

Note I used "\" to wrap the command, but the real one does not have
those.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 432226 FK
CONSTRAINT fkidturno postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.turnodocumento" does not exist
Command was: ALTER TABLE ONLY public.turnodocumento DROP CONSTRAINT
fkidturno;

Why is this happening?, should I change the command I'm using to create
the backup?.

The -c switch causes pg_restore to try to DROP every object it's about to
restore. If you're restoring into an empty database then this is useless,
and in fact will not work if you're also using --exit-on-error. Remove
one or the other switch.

Now, if you expected that all the objects do exist in the target database,
then it might be worth inquiring a bit more closely into what's happening.

regards, tom lane

Tom, I've dropped the db, then createdb again, then removed the -c
option, now I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 735; 2612 213488
PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language
"plpgsql" already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;

You can ignore that.

Just make sure you're doing the loading auto-commit and without
ON_ERROR_STOP set.

Of course, you could drop lang plpgsql from your new DB and try again
which, if that was going to be your only error, you may then run clean.

HTH

--
Leonardo M. Ram�
Medical IT - Griensu S.A.
Av. Col�n 636 - Piso 8 Of. A
X5000EPT -- C�rdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= <l.rame@griensu.com> writes:

On 2014-02-12 13:30:52 -0500, Tom Lane wrote:

The -c switch causes pg_restore to try to DROP every object it's about to
restore. If you're restoring into an empty database then this is useless,
and in fact will not work if you're also using --exit-on-error. Remove
one or the other switch.

Tom, I've dropped the db, then createdb again, then removed the -c
option, now I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 735; 2612 213488
PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language
"plpgsql" already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;

pg_dump versions more recent than 8.4 use "CREATE OR REPLACE PROCEDURAL
LANGUAGE" to work around the possibility that plpgsql is preinstalled.
If you don't want to use a modern pg_dump, you'll need to not use
--exit-on-error.

In general, it's recommended to use the newer pg_dump when trying to
transfer data from an older installation to a newer one. You can
generally make it work without that, but it's not necessarily going
to be seamless, and one of the ways it tends to not be seamless is
that you have to be willing to ignore harmless errors.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2014-02-12 14:04:41 -0500, Tom Lane wrote:

Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= <l.rame@griensu.com> writes:

On 2014-02-12 13:30:52 -0500, Tom Lane wrote:

The -c switch causes pg_restore to try to DROP every object it's about to
restore. If you're restoring into an empty database then this is useless,
and in fact will not work if you're also using --exit-on-error. Remove
one or the other switch.

Tom, I've dropped the db, then createdb again, then removed the -c
option, now I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 735; 2612 213488
PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language
"plpgsql" already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;

pg_dump versions more recent than 8.4 use "CREATE OR REPLACE PROCEDURAL
LANGUAGE" to work around the possibility that plpgsql is preinstalled.
If you don't want to use a modern pg_dump, you'll need to not use
--exit-on-error.

In general, it's recommended to use the newer pg_dump when trying to
transfer data from an older installation to a newer one. You can
generally make it work without that, but it's not necessarily going
to be seamless, and one of the ways it tends to not be seamless is
that you have to be willing to ignore harmless errors.

Ok, I understand your reasoning. Removing -c and --exit-on-error fixed
the issue.

BTW, I've used --exit-on-error because there were many errors, and I wanted
to fix each one of them.

Regards,
--
Leonardo M. Ram�
Medical IT - Griensu S.A.
Av. Col�n 636 - Piso 8 Of. A
X5000EPT -- C�rdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general