Thread: Bash script to update sequences

Bash script to update sequences

From
"Matt"
Date:
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 (!).

-------------------------
#!/bin/bash

usage="Description:\nA script to update every sequence in a postgres
database to the current\n
value of the count of the number of rows in a corresponding table\n
NB: this script assumes that the sequence includes the name of the
table\n
    at the start of the sequence name, and parses the sequence name as
such\n"

if [ $# -lt 1 ];
then
        echo -e "$0: Error: Please enter the name of the database to
connect to. \n"
        echo -e "Usage: $0 <database>\n"
        echo -e $usage
        exit 1;
fi


database="$1"
sequences=`echo "\ds" | psql -t ${database} | cut -d "|" -f2`

for i in $sequences
do
        table=`echo $i | cut -d "_" -f1`
        idfield=`echo "\d \"${table}\"" | psql -t ${database} | grep $i
| cut -d "|" -f1 | sed -e 's/ //g'`

        countsql="select max(\"$idfield\") from \"$table\""
        tablecount=`echo $countsql | psql -t ${database} | sed -e 's/
//g'`

        updateseq="select setval('\"$i\"', (select max(\"$idfield\")
from \"$table\")+1);"

        newval=`echo $updateseq | psql -t ${database} |  sed -e 's/
//g'`
        echo "Table $table: $idfield has sequence: $i, with max value:
$tablecount, updated to $newval"
done
-------------------------

Hopefully this may solve someone some grief.

Cheers,
Matt


Re: Bash script to update sequences

From
Tino Wildenhain
Date:
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