Thread: pg_restore issue

pg_restore issue

From

Leonardo M. Ramé

Date:

12 February 2014, 20:41:30

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?.

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,
--
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

Re: pg_restore issue

From

Adrian Klaver

Date:

12 February 2014, 20:51:18

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

Re: pg_restore issue

From

Tom Lane

Date:

12 February 2014, 21:31:00

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

Re: pg_restore issue

From

Leonardo M. Ramé

Date:

12 February 2014, 21:39:54

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

Re: pg_restore issue

From

Leonardo M. Ramé

Date:

12 February 2014, 21:41:45

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

Re: pg_restore issue

From

Tom Lane

Date:

12 February 2014, 22:04:52

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

Re: pg_restore issue

From

Leonardo M. Ramé

Date:

12 February 2014, 22:46:12

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

Re: pg_restore issue

From

Jerry Sievers

Date:

12 February 2014, 23:37:08

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