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