pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore has problems with restoring sequences.
7+ messages / 4 participants
[nested] [flat]
* pg_restore has problems with restoring sequences.
@ 2018-02-04 15:10 robert <robert@redcor.ch>
0 siblings, 1 reply; 7+ messages in thread
From: robert @ 2018-02-04 15:10 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore has problems with restoring sequences.
@ 2018-02-04 15:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: robert <robert@redcor.ch>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2018-02-04 15:43 UTC (permalink / raw)
To: robert <robert@redcor.ch>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore has problems with restoring sequences.
@ 2018-02-04 16:49 robert@redo2oo.ch <robert@redo2oo.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: robert@redo2oo.ch @ 2018-02-04 16:49 UTC (permalink / raw)
To: robert <robert@redcor.ch>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore has problems with restoring sequences.
@ 2018-02-04 16:53 robert <robert@redcor.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: robert @ 2018-02-04 16:53 UTC (permalink / raw)
To: robert <robert@redcor.ch>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore has problems with restoring sequences.
@ 2018-02-04 18:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: robert <robert@redcor.ch>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2018-02-04 18:16 UTC (permalink / raw)
To: robert <robert@redcor.ch>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore has problems with restoring sequences.
@ 2018-02-05 07:20 robert <robert@redo2oo.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: robert @ 2018-02-05 07:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 <mailto:robert@redo2oo.ch>
Sickingerstrasse 3, 3014 Bern
<http://maps.apple.com/?q=Sickingerstrasse%203,3014%20Bern;
https://Redo2oo.ch <https://redo2oo.ch;
*Ihr Partner wenn es um ERP Lösungen geht.*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore has problems with restoring sequences.
@ 2018-02-05 07:28 robert <robert@redcor.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: robert @ 2018-02-05 07:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-02-05 07:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-04 15:10 pg_restore has problems with restoring sequences. robert <robert@redcor.ch>
2018-02-04 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-04 16:49 ` robert@redo2oo.ch <robert@redo2oo.ch>
2018-02-04 16:53 ` robert <robert@redcor.ch>
2018-02-04 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-05 07:20 ` robert <robert@redo2oo.ch>
2018-02-05 07:28 ` robert <robert@redcor.ch>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox