Re: pg_dump fails to include sequences, leads to restore fail in any version

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Jeffrey Baker escribi:
>> The table was originally created this way:

> Okay, but was it created on 8.1 or was it already created on an older
> version and restored? I don't see this behavior if I create it in 8.1
> -- the field is dumped as SERIAL, unlike what you show.

There's something interesting in the original report:

> --
> -- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema: mercado; Owner: prod
> --
>
> SELECT
> pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
^^^^^^^^^^^^^^^^^^
> 'transaction_id'), 6736138, true);

So pg_dump found a pg_depend entry linking that sequence to some table
named transaction_backup, not transaction. That explains why
transaction isn't being dumped using a SERIAL keyword --- it's not
linked to this sequence. But how things got this way is not apparent
from the stated facts.

One possibility is that Jeffrey is getting bit by this bug or
something related:
http://archives.postgresql.org/pgsql-bugs/2006-07/msg00021.php
There are links to some other known serial-sequence problems in 8.1
in this message:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01250.php

None of those reports seem to exactly match the described behavior, but
anyway I'd bet a good deal that either the table or the sequence has
been altered in some way since they were created. Given that Jeffrey
says all his sequences fail the same way, it must've been something
he did to all his tables/sequences ...

regards, tom lane