Re: Bash script to update sequences

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