Thread: error creating/setting sequence, pg_dump / pg_restore 8.1.5

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

"Mason Hale" <masonhale@gmail.com> writes:
> 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.

Hmm ... which sequence is entry.id actually referring to on the source
database?  I suspect that it is linked to some differently-named
sequence like "entry_id_seq1" and the source's "entry_id_seq" is not
in truth doing anything (and, in particular, hasn't ever been advanced,
so when the restore script sets its value to 1 it's clobbering the other
sequence).

The methodology that pre-8.2 pg_dump uses for serial columns is
vulnerable to quite a number of problems if serial sequences don't have
the expected names, and this seems to be another one.  8.2 would be able
to handle the case, but in 8.1 the best advice is to drop the unused
sequence to avoid the name collision when SERIAL picks plain "entry_id_seq"
as the serial sequence's name upon reload.

            regards, tom lane

"Mason Hale" <masonhale@gmail.com> writes:
> On 4/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The methodology that pre-8.2 pg_dump uses for serial columns is
>> vulnerable to quite a number of problems if serial sequences don't have
>> the expected names, and this seems to be another one.

> I assume it would not be advisable to use the 8.2 version of pg_dump, if I
> intend to restore to an 8.1 database, correct?

Unfortunately not; the fix for this involves a server-side feature that
doesn't exist in 8.1, so the dump will flat out not work.  You could
however use the newer pg_dump to dump from 8.1 if you were intending an
upgrade, and it would successfully reproduce the older DB's state in 8.2.

            regards, tom lane