I'm trying to transition a database from one server to another, the old one running Postgres 7.4.1
and the new, 8.1.1. When I try to restore using a pg_dump plaintext sql file from a nightly backup
via the usual
psql thedb < backup.sql
It works for the most part, but encounters several errors near the end when trying to create
sequences. Also, it produces warnings about "creating implicit sequences" for tables with SERIAL
keys, which strikes me as a bit odd because there's no need for "implicit" sequences when they're
already explicitly defined in the database!
Looking back at the dump file though, I notice some discrepancies between what I see reported for
the original database in phpPgAdmin and the sequences that are actually created. Specifically, it
appears that any sequence that doesn't follow the naming convention postgres uses when
auto-generating sequences, doesn't get created at all. Example:
I have a table 'bands' with a SERIAL primary key 'dbbandcode'. The sequence for this is defined in
the original database as 'bands_dbcode_seq' and the default value for the key is:
nextval('public.bands_dbcode_seq'::text)
In the database dump however, this default is omitted (and consequently, when restoring, the new
server creates an implicit sequence named 'bands_dbbandcode_seq' instead of the correct
bands_dbcode_seq, and it is never set to the correct value).
This happens for a few other tables too; basically anything that had its serial columns or tables
renamed at some point doesn't get its sequences re-created.
So, why is this happening, and how do I fix it without having to manually modify the dump file
before restoring? Is this just a bug in 7.4.1?
Thanks,
Brian