pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWAL-files restore and nextval('PK')
3+ messages / 3 participants
[nested] [flat]
* WAL-files restore and nextval('PK')
@ 2010-05-03 11:16 Andreas Gaab <A.Gaab@scanlab.de>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas Gaab @ 2010-05-03 11:16 UTC (permalink / raw)
To: pgsql-sql; +Cc: Hans Joachim Münzer <H.Muenzer@scanlab.de>
Dear all,
after an WAL-restore of our Postgres DB, we observe seemingly wrong values of our sequences.
We have two postgres server (8.4) with pgpool in replication mode.
Recently we tested our restore procedure and played our WAL-files into the second server after an old file-system backup was restored.
Accidently, we aborted the starting server and had to restart it and therefore started WAL-replay again.
Now we observe, that the newly restored server has higher values in his sequences as the other server.
Could it be that during restart of the WAL-file restore SELECT nextval('PK') ; commands were executed again, whereas the data rows were already restored and thus leading to higher sequence numbers on the newly restored server?
Best regards for any comments!
Andreas
___________________________________________________________________________
SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D
Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.gaab@scanlab.de * www.scanlab.de<http://www.scanlab.de;
Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___________________________________________________________________________
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: WAL-files restore and nextval('PK')
@ 2010-05-03 13:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Gaab <A.Gaab@scanlab.de>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2010-05-03 13:33 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; +Cc: pgsql-sql; Hans Joachim Münzer <H.Muenzer@scanlab.de>
Andreas Gaab <A.Gaab@scanlab.de> writes:
> after an WAL-restore of our Postgres DB, we observe seemingly wrong values of our sequences.
> We have two postgres server (8.4) with pgpool in replication mode.
> Recently we tested our restore procedure and played our WAL-files into the second server after an old file-system backup was restored.
> Accidently, we aborted the starting server and had to restart it and therefore started WAL-replay again.
> Now we observe, that the newly restored server has higher values in his sequences as the other server.
It's normal for sequence counters to be a few counts higher after a
crash-and-restart than they would have been if no crash had occurred.
This is an intentional design tradeoff to minimize the WAL overhead
associated with assigning a sequence value. If you find it intolerable
for what you're doing, I believe you can prevent it by adjusting the
sequence parameters to prevent any "caching" of values.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: WAL-files restore and nextval('PK')
@ 2010-05-03 15:28 Alvaro Herrera <alvherre@commandprompt.com>
parent: Andreas Gaab <A.Gaab@scanlab.de>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2010-05-03 15:28 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; +Cc: pgsql-sql; Hans Joachim Münzer <H.Muenzer@scanlab.de>
Andreas Gaab wrote:
> Dear all,
>
> after an WAL-restore of our Postgres DB, we observe seemingly wrong values of our sequences.
This is normal. The reason is that sequences are WAL-logged in chunks,
not single values (otherwise they'd be too slow). So after recovery,
the values jump to the logged values, which are necessarily higher than
the values they last delivered before the crash.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-05-03 15:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-03 11:16 WAL-files restore and nextval('PK') Andreas Gaab <A.Gaab@scanlab.de>
2010-05-03 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-03 15:28 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox