Matt schrieb:
Hi all,
I've recently been using some older version of postgres on linux
systems, and one of the most annoying problems i've come across is to
do with sequence values not being updated when a database backup is
restored. This is because the insert and copy routines used to restore
the data into tables do not trigger the nextval function on insert, and
so you get a lot of annoying errors about duplicate keys if your ID
fields are based on sequences.
Anyway, if you know what im talking about then im sure you understand
how annoying it is. I'm pretty sure the problem is fixed now in
postgres 8, however older systems such as 7.4 and 7.3.10 do not
correctly update sequence values when restoring from backups.
So in short (or not so short), i've written a simple bash script that
will connect to a given database, get a list of all the sequences for
that database, and then update the current value of the sequence to be
one greater than the max value of the corresponding ID field for that
table.
The script should be run as your postgres user on a linux-ish system
with bash.
Code follows. Watch out for wrapped sentences when copying (!).
Actually sequences have always been updated by pg_dump/restore.
How do you do your restore to avoid this?
Regards
Tino
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
