Обсуждение: sequences not restoring properly
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
Brian Dimeler <briand@lserve.com> writes: > 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 The best way to do this is to run the 8.1.1 pg_dump against the 7.4.1 server. The new pg_dump will know better how to create a backup that 8.1.1 will like. -Doug
Doing it that way doesn't produce any errors, but it still produces incorrect sequence names and values. Doug McNaught wrote: > Brian Dimeler <briand@lserve.com> writes: > > >>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 > > > The best way to do this is to run the 8.1.1 pg_dump against the 7.4.1 > server. The new pg_dump will know better how to create a backup that > 8.1.1 will like. > > -Doug >
Brian Dimeler <briand@lserve.com> writes:
> 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.
Oh, they're created all right. But they're created according to what
the name ought to be now given the new column name, and the setval()
commands in the old dump are wrong for that.
Per Doug's response, use the 8.1 pg_dump if you can, as it knows how to
generate setval() calls that can deal with this effect. There's no
very good solution for it in 7.4 unfortunately --- if you want to use
the old pg_dump, you have to do the setvals by hand after loading the
dump.
Note that you'd have the same problem trying to reload that dump into
7.4 ...
regards, tom lane
Brian Dimeler wrote: > Doing it that way doesn't produce any errors, but it still produces > incorrect sequence names and values. Are these sequences that you created by hand and then associated with a column? Versus using serial/bigserial types? Sincerely, Joshua D. Drake > > Doug McNaught wrote: > >> Brian Dimeler <briand@lserve.com> writes: >> >> >>> 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 >> >> >> The best way to do this is to run the 8.1.1 pg_dump against the 7.4.1 >> server. The new pg_dump will know better how to create a backup that >> 8.1.1 will like. >> >> -Doug >> > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
I need to amend what I said before; apparently, the 8.1.1 dump is creating correct sequence values
(except in one instance), but it's changing the names to "match" the tables and columns they go
with. Which is nice, I suppose, except that I use an object-relational mapping API that requires
hard-coding of sequence names. Oh well... will the new version Postgres now prevent me from creating
sequences by hand and associating them with tables, or renaming them, or renaming sequence columns?
I sure hope so! Because if not, dumps should reflect any changes I've been able to make.
As for how the changes were made at first, to be honest, I don't remember. The vast majority of my
tables were created with SERIAL columns initially and they retain the automatically-generated
sequences that went with them.
I think what may have happened is that for a few tables, I decided to change the name of the serial
column in question shortly after creating it; that's probably the case with the 'dbbandcode' example
I posted.
In another case, I believe I had created an entirely new table ('items' and 'itemid'), but kept the
original sequence from a previous table ('garments_garmentid_seq'), thinking I was going to use them
in tandem, generating numbers for each that would not overlap. Unfortunately I had inadvertently
left an auto-generated, but unused, items_itemid_seq in there too, so when 8.1.1 saw that it must
have chucked garments_garmentid_seq and its value in favor of the one that appeared to match the
table and column. The other tables had their values restored correctly, it's just that their *names*
are now a little *too* 'correct'.
Brian
Joshua D. Drake wrote:
> Brian Dimeler wrote:
>
>> Doing it that way doesn't produce any errors, but it still produces
>> incorrect sequence names and values.
>
> Are these sequences that you created by hand and then associated with a
> column? Versus using serial/bigserial types?
>
> Sincerely,
>
> Joshua D. Drake
>
>>
>> Doug McNaught wrote:
>>
>>> Brian Dimeler <briand@lserve.com> writes:
>>>
>>>
>>>> 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
>>>
>>>
>>>
>>> The best way to do this is to run the 8.1.1 pg_dump against the 7.4.1
>>> server. The new pg_dump will know better how to create a backup that
>>> 8.1.1 will like.
>>>
>>> -Doug
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
>
>