Thread: pg_restore has problems with restoring sequences.

pg_restore has problems with restoring sequences.

From

robert

Date:

04 February 2018, 18:10:27

Hi there,

I want to restore data from a box running V10 in a docker to my local box.

I use

     pg_dump -h localhost  -U USER -Fc DATABASE > dumpfile

then I restore it with:

     dropdb DATABASE

     pg_restore -O  -U user  -d DATABASE  dumpfile


doing this then I get these warnings, and many (not all) sequences are not created.


pg_restore: [archiver (db)] Error from TOC entry 15277; 0 0 SEQUENCE SET 
selected_event_zone_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"selected_event_zone_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('selected_event_zone_id_seq', 6, tr...
                                  ^
     Command was: SELECT pg_catalog.setval('selected_event_zone_id_seq', 6, true);

WARNING: errors ignored on restore: 20


how can I fix this.

This procedure never was problematic using postgresql 9.X


thanks

robert

Re: pg_restore has problems with restoring sequences.

From

Tom Lane

Date:

04 February 2018, 18:43:06

robert <robert@redcor.ch> writes:
> I use
>      pg_dump -h localhost  -U USER -Fc DATABASE > dumpfile

OK ...

> then I restore it with:
>      dropdb DATABASE
>      pg_restore -O  -U user  -d DATABASE  dumpfile

That sequence is missing a createdb step (or else -C in the restore
switches).

> doing this then I get these warnings, and many (not all) sequences are not created.

Usually the thing to do is look at the very first error; everything after
that may just be cascading damage.  I kinda doubt what you showed us
here is the first error.

            regards, tom lane

Re: pg_restore has problems with restoring sequences.

From

"robert@redo2oo.ch"

Date:

04 February 2018, 19:49:21

Thanks Tom
On 04.02.2018 16:43, Tom Lane wrote:
> robert <robert@redcor.ch> writes:
>> I use
>>       pg_dump -h localhost  -U USER -Fc DATABASE > dumpfile
> OK ...
>
>> then I restore it with:
>>       dropdb DATABASE
>>       pg_restore -O  -U user  -d DATABASE  dumpfile
> That sequence is missing a createdb step (or else -C in the restore
> switches).
yes, I am using also the -C switch
sorry
>
>> doing this then I get these warnings, and many (not all) sequences are not created.
> Usually the thing to do is look at the very first error; everything after
> that may just be cascading damage.  I kinda doubt what you showed us
> here is the first error.
no its not, but they are all similar, I assumed (maybe wrongly) that it makes no 
difference ..
Here are the first couple:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE change_email_domain_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALU...
pg_restore: [archiver (db)] Error from TOC entry 11717; 0 0 SEQUENCE OWNED BY 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"change_email_domain_id_seq" does not exist
     Command was: ALTER SEQUENCE change_email_domain_id_seq OWNED BY 
change_email_domain.id;

pg_restore: [archiver (db)] Error from TOC entry 1062; 1259 134467 SEQUENCE 
change_email_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE change_email_wizard_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALU...
pg_restore: [archiver (db)] Error from TOC entry 11724; 0 0 SEQUENCE OWNED BY 
change_email_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"change_email_wizard_id_seq" does not exist
     Command was: ALTER SEQUENCE change_email_wizard_id_seq OWNED BY 
change_email_wizard.id;

pg_restore: [archiver (db)] Error from TOC entry 1070; 1259 142859 SEQUENCE 
delete_user_confirm_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE delete_user_confirm_wizard_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO ...
pg_restore: [archiver (db)] Error from TOC entry 12169; 0 0 SEQUENCE OWNED BY 
delete_user_confirm_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"delete_user_confirm_wizard_id_seq" does not exist
     Command was: ALTER SEQUENCE delete_user_confirm_wizard_id_seq OWNED BY 
delete_user_confirm_wizard.id;

pg_restore: [archiver (db)] Error from TOC entry 1066; 1259 140393 SEQUENCE 
news_publish_queue_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE news_publish_queue_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALUE...
pg_restore: [archiver (db)] Error from TOC entry 13336; 0 0 SEQUENCE OWNED BY 
news_publish_queue_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"news_publish_queue_id_seq" does not exist
     Command was: ALTER SEQUENCE news_publish_queue_id_seq OWNED BY 
news_publish_queue.id;

thanks again
robert

Re: pg_restore has problems with restoring sequences.

From

robert

Date:

04 February 2018, 19:53:28

Thanks Tom
On 04.02.2018 16:43, Tom Lane wrote:
> robert <robert@redcor.ch> writes:
>> I use
>>       pg_dump -h localhost  -U USER -Fc DATABASE > dumpfile
> OK ...
>
>> then I restore it with:
>>       dropdb DATABASE
>>       pg_restore -O  -U user  -d DATABASE  dumpfile
> That sequence is missing a createdb step (or else -C in the restore
> switches).
yes, I am using also the -C switch
sorry
>
>> doing this then I get these warnings, and many (not all) sequences are not created.
> Usually the thing to do is look at the very first error; everything after
> that may just be cascading damage.  I kinda doubt what you showed us
> here is the first error.
no its not, but they are all similar, I assumed (maybe wrongly) that it makes no 
difference ..
Here are the first couple:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE change_email_domain_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALU...
pg_restore: [archiver (db)] Error from TOC entry 11717; 0 0 SEQUENCE OWNED BY 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"change_email_domain_id_seq" does not exist
     Command was: ALTER SEQUENCE change_email_domain_id_seq OWNED BY 
change_email_domain.id;

pg_restore: [archiver (db)] Error from TOC entry 1062; 1259 134467 SEQUENCE 
change_email_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE change_email_wizard_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALU...
pg_restore: [archiver (db)] Error from TOC entry 11724; 0 0 SEQUENCE OWNED BY 
change_email_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"change_email_wizard_id_seq" does not exist
     Command was: ALTER SEQUENCE change_email_wizard_id_seq OWNED BY 
change_email_wizard.id;

pg_restore: [archiver (db)] Error from TOC entry 1070; 1259 142859 SEQUENCE 
delete_user_confirm_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE delete_user_confirm_wizard_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO ...
pg_restore: [archiver (db)] Error from TOC entry 12169; 0 0 SEQUENCE OWNED BY 
delete_user_confirm_wizard_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"delete_user_confirm_wizard_id_seq" does not exist
     Command was: ALTER SEQUENCE delete_user_confirm_wizard_id_seq OWNED BY 
delete_user_confirm_wizard.id;

pg_restore: [archiver (db)] Error from TOC entry 1066; 1259 140393 SEQUENCE 
news_publish_queue_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE news_publish_queue_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALUE...
pg_restore: [archiver (db)] Error from TOC entry 13336; 0 0 SEQUENCE OWNED BY 
news_publish_queue_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR: relation 
"news_publish_queue_id_seq" does not exist
     Command was: ALTER SEQUENCE news_publish_queue_id_seq OWNED BY 
news_publish_queue.id;

thanks again
robert

Re: pg_restore has problems with restoring sequences.

From

Tom Lane

Date:

04 February 2018, 21:16:09

robert <robert@redcor.ch> writes:
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE
> change_email_domain_id_seq odoo
> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or
> near "AS"
> LINE 2:     AS integer
>              ^
>      Command was: CREATE SEQUENCE change_email_domain_id_seq
>      AS integer
>      START WITH 1
>      INCREMENT BY 1
>      NO MINVALUE
>      NO MAXVALU...

Hm.  What this suggests is that you're using a pg_dump that is newer than
the server that you're trying to restore to.  The "AS datatype" clause
in CREATE SEQUENCE is new in v10.

The general rule for pg_dump version choice is "use the newest version you
can, but not newer than the server you're going to restore to".  Otherwise
you run into problems like this of the dump containing syntax the target
server doesn't understand.

            regards, tom lane

Re: pg_restore has problems with restoring sequences.

From

robert

Date:

05 February 2018, 10:20:59

thanks,

On 04.02.2018 19:16, Tom Lane wrote:

robert <robert@redcor.ch> writes:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer            ^    Command was: CREATE SEQUENCE change_email_domain_id_seq    AS integer    START WITH 1    INCREMENT BY 1    NO MINVALUE    NO MAXVALU...

Hm.  What this suggests is that you're using a pg_dump that is newer than
the server that you're trying to restore to.  The "AS datatype" clause
in CREATE SEQUENCE is new in v10.

The general rule for pg_dump version choice is "use the newest version you
can, but not newer than the server you're going to restore to".  Otherwise
you run into problems like this of the dump containing syntax the target
server doesn't understand.
		regards, tom lane

I am using postgres 10 on the server where I create the dump
and 10.1 where i try to restore.
however, the box where I try to restore uses
root@lappi:/home/robert/odoo_instances# pg_restore -V
pg_restore (PostgreSQL) 9.5.10

when I check with :
root@lappi:/home/robert/odoo_instances# apt-cache policy postgresql-client
postgresql-client:
Installed: (none)
Candidate: 10+189.pgdg17.04+1
Version table:
     10+189.pgdg17.04+1 500
        500 http://apt.postgresql.org/pub/repos/apt zesty-pgdg/main amd64 Packages
        500 http://apt.postgresql.org/pub/repos/apt zesty-pgdg/main i386 Packages
     9.6+184ubuntu1.1 500
        500 http://ch.archive.ubuntu.com/ubuntu artful-updates/main amd64 Packages
        500 http://ch.archive.ubuntu.com/ubuntu artful-updates/main i386 Packages
        500 http://security.ubuntu.com/ubuntu artful-security/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu artful-security/main i386 Packages
     9.6+184ubuntu1 500
        500 http://ch.archive.ubuntu.com/ubuntu artful/main amd64 Packages
        500 http://ch.archive.ubuntu.com/ubuntu artful/main i386 Packages

and when I try to reinstall
apt install postgresql-client-common
i get a message, that it is already the newest version.

ah, now I see something:$which pg_restore points to:
/usr/bin/pg_restore -> ../share/postgresql-common/pg_wrapper

what is this pg_wrapper??

thanks

Robert Rottermann CEO

031 333 10 20
robert@redo2oo.ch
Sickingerstrasse 3, 3014 Bern

Ihr Partner wenn es um ERP Lösungen geht.

Re: pg_restore has problems with restoring sequences.

From

robert

Date:

05 February 2018, 10:28:18

thanks,

On 04.02.2018 19:16, Tom Lane wrote:

robert <robert@redcor.ch> writes:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer            ^    Command was: CREATE SEQUENCE change_email_domain_id_seq    AS integer    START WITH 1    INCREMENT BY 1    NO MINVALUE    NO MAXVALU...

Hm.  What this suggests is that you're using a pg_dump that is newer than
the server that you're trying to restore to.  The "AS datatype" clause
in CREATE SEQUENCE is new in v10.

The general rule for pg_dump version choice is "use the newest version you
can, but not newer than the server you're going to restore to".  Otherwise
you run into problems like this of the dump containing syntax the target
server doesn't understand.
		regards, tom lane