Hello -
After running pg_dump to backup my database, and then running pg_restore to load the db (on a different server),
I run into a problem with creating a sequence. After the pg_restore is completed, the sequence is created, but
the value is not set correctly. As a result calls nextval for the sequence start with 1 and lead id collisions on the table until the sequence value is reset manually.
Here's the relevant output from pg_restore:
pg_restore: creating TABLE entry
... [snipping out other CREATE TABLE and SEQUENCE SET statements]
pg_restore: creating SEQUENCE entry_id_seq
pg_restore: [archiver (db)] Error from TOC entry 1355; 1259 1302158 SEQUENCE entry_id_seq lss
pg_restore: [archiver (db)] could not execute query: ERROR: relation "entry_id_seq" already exists
Command was: CREATE SEQUENCE entry_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
pg_restore: executing SEQUENCE SET entry_id_seq
Looking at the pg_store output, I can see that table 'entry' is created before the sequence 'entry_id_seq'.
The entry table is defined with a serial column named 'id' -- so my understanding is this implicitly creates
a sequence named 'entry_id_seq' during the table create. But then it tries to create the entry_id_seq
again and fails.
Further, it appears the subsequent SEQUENCE SET entry_id_seq doesn't actually set the
sequence value -- because after the restore is finished, select nextval('entry_id_seq') returns 1.
Both databases and all versions of pg_dump and pg_restore are 8.1.5.
Here's the exact commands used:
dump the db on server1:
pg_dump -U postgres -Fc -v bduprod_2 > /data/backups/working/bduprod_2.dump
restore on server2:
nohup pg_restore -d blizzard -c -v -O -U postgres bduprod_2.dump > restore.log
2>&1
(where 'blizzard' is a new database created from template0)
I can fix the sequence easy enough by running:
SELECT setval('entry_id_seq', (SELECT COALESCE(MAX(id)+(SELECT increment_by FROM entry_id_seq), (SELECT min_value FROM entry_id_seq)) FROM entry), false)
In the blizzard database on server2.
BUT -- I dump and restore these snapshots on a regular basis and would like to skip this step if possible.
Thanks in advance,
Mason
