From: | Brian Dimeler <briand(at)lserve(dot)com> |
---|---|
To: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | sequences not restoring properly |
Date: | 2006-01-20 17:26:42 |
Message-ID: | 43D11D52.7030806@lserve.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-01-20 17:29:30 | Re: What is the maximum length of an IN(a, b, c....d) list in PostgreSQL |
Previous Message | Tino Wildenhain | 2006-01-20 17:21:19 | Re: what am I doing wrong with this query? |