The following bug has been logged online:
Bug reference: 4565
Logged by: Marc Schablewski
Email address: ms@clickware.de
PostgreSQL version: 8.3.5
Operating system: Debian, 2.6.24-19 kernel
Description: nextval not updated during wal replication, leading to
pk violations
Details:
Hi!
We are using "Continuous Archiving" of WAL to keep a warm standby database
server. We recently switch over to that backup database for testing purpose.
We first took an online backup of the master database, let the WAL shipping
run for some days and finally started the backup for normal use.
A job tried to insert some records into a few tables and complained about a
PK violation. PK are of type bigint. The column is populated by a sequence
and a "default nextval()" on the PK column. We found that the sequence's
currval was lower than the maximum value in the table and that this value
was already present. Further investigation showed us that the last records
in the table were inserted on the former master server while taking the
initial online backup for the replication. It seems that the records got
replicated but not the currval/nextval of the sequence. When running "select
nextval()" on the backup database it returned the PK value of the first
record inserted during that last run on the former master server.
I couldn't reproduce the problem with two different servers and a simple
database containing one table. Right now, I don't have any idea how to
narrow down the problem or what to check next. So any hint would be
helpful.
Marc