Postgres Pro
Downloads
Home   >   mailing lists

Thread: WAL-files restore and nextval('PK')

WAL-files restore and nextval('PK')

From
Andreas Gaab
Date:
<div class="Section1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Times New Roman","serif"">Dear
all,</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif"">after an WAL-restore
ofour Postgres DB, we observe seemingly wrong values of our sequences.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">We have two postgres server (8.4) with pgpool in
replicationmode. </span><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Recentlywe tested our restore procedure and played our WAL-files into the second server after an old
file-systembackup was restored.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Accidently, we aborted the starting server and had to
restartit and therefore started WAL-replay again.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Now we observe, that the newly restored server has higher
valuesin his sequences as the other server.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Could it be that during restart of the WAL-file restore
SELECTnextval(‘PK’) ; commands were executed again, whereas the data rows were already restored and thus leading to
highersequence numbers on the newly restored server?</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Best regards for any comments!</span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif"">Andreas</span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p
class="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">SCANLAB AG</span><pclass="MsoNormal"><span
style="font-size:10.0pt">Dr. Andreas Simon Gaab</span><pclass="MsoNormal"><span style="font-size:10.0pt">Entwicklung •
R & D</span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">Siemensstr. 2a• 82178 Puchheim • Germany</span><p class="MsoNormal"><span
style="font-size:10.0pt">Tel. +49 (89) 800 746-513• Fax +49 (89) 800 746-199</span><p class="MsoNormal"><span
style="font-size:10.0pt"><ahref="mailto:a.gaab@scanlab.de">mailto:a.gaab@scanlab.de</a> • <a
href="http://www.scanlab.de">www.scanlab.de</a></span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Vorstand: Georg Hofner (Sprecher), Christian Huttenloher,
Norbert Petschik</span><pclass="MsoNormal"><span style="font-size:10.0pt">Aufsichtsrat (Vorsitz):
Dr. Hans J. Langer</span><pclass="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""></span><pclass="MsoNormal"> </div>

Re: WAL-files restore and nextval('PK')

From
Tom Lane
Date:
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
backupwas 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

Re: WAL-files restore and nextval('PK')

From
Alvaro Herrera
Date:
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.