Search Postgresql Archives

error creating/setting sequence, pg_dump / pg_restore 8.1.5

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux