error creating/setting sequence, pg_dump / pg_restore 8.1.5

From: "Mason Hale" <masonhale(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: error creating/setting sequence, pg_dump / pg_restore 8.1.5
Date: 2007-04-13 16:39:00
Message-ID: 8bca3aa10704130939p18e11ee1v4392997a1169f334@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message steve shiflett 2007-04-13 16:42:35 Display Cursor Content - Help!
Previous Message richyen3@gmail.com 2007-04-13 16:28:47 Re: median query causes disk to fill up