From A.Gaab@scanlab.de Mon May 3 11:26:50 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7ECD4633514 for ; Mon, 3 May 2010 08:26:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 03233-01 for ; Mon, 3 May 2010 11:26:42 +0000 (UTC) X-Greylist: delayed 00:10:04.759969 by SQLgrey-1.7.6 Received: from mail.scanlab.de (mail.scanlab.de [62.138.55.241]) by mail.postgresql.org (Postfix) with ESMTP id D2A27632FCC for ; Mon, 3 May 2010 08:26:40 -0300 (ADT) Received: from unknown (HELO Exchange-Server.scanlab-intern.de) ([172.16.20.15]) by mail.scanlab-intern.de with ESMTP; 03 May 2010 13:16:32 +0200 Received: from Exchange-Server.scanlab-intern.de ([127.0.0.1]) by Exchange-Server ([127.0.0.1]) with mapi; Mon, 3 May 2010 13:16:31 +0200 From: Andreas Gaab To: pgsql-sql mailing list CC: =?iso-8859-1?Q?Hans_Joachim_M=FCnzer?= Date: Mon, 3 May 2010 13:16:31 +0200 Subject: WAL-files restore and nextval('PK') Thread-Topic: WAL-files restore and nextval('PK') Thread-Index: AcrqshVquzb06AbzR5SWSzsvZm/pmg== Message-ID: <48DA836F3865C54B8FBF424A3B775AF6010D455863@Exchange-Server> Accept-Language: de-DE Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: de-DE Content-Type: multipart/alternative; boundary="_000_48DA836F3865C54B8FBF424A3B775AF6010D455863ExchangeServe_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.791 tagged_above=-5 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/6 X-Sequence-Number: 34054 --_000_48DA836F3865C54B8FBF424A3B775AF6010D455863ExchangeServe_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 theref= ore started WAL-replay again. Now we observe, that the newly restored server has higher values in his seq= uences 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 restor= ed 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 Amtsgericht M=FCnchen: HRB 124707 * USt-IdNr.: DE 129 456 351 Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik Aufsichtsrat (Vorsitz): Dr. Hans J. Langer ___________________________________________________________________________ --_000_48DA836F3865C54B8FBF424A3B775AF6010D455863ExchangeServe_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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 se= rver 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 sequenc= es as the other server.

Could it be that during restart of the WAL-file restore SELECT nextval(‘PK&= #8217;) ; commands were executed again, whereas the data rows were already restored= and thus leading to higher sequence numbers on the newly restored server?<= /o:p>

Best regards for any comments!

Andreas

_____________________= ______________________________________________________

SCANLAB AG<= /o:p>

Dr. Andreas = ;Simon Gaab

Entwicklung • R & D

Siemensstr. 2a &= #8226; 82178 Puchheim • Germany

Tel. +49 (8= 9) 800 746-513 • Fax +49 (89) 800 746-199

mailto:a.gaab@scanlab.de • www.scanlab.de

Amtsgericht M=FC= nchen: HRB 124707 • USt-IdNr.: DE 129 456 351=

Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik

Aufsichtsrat (Vo= rsitz): Dr. Hans J. Langer

_____________________= ______________________________________________________

--_000_48DA836F3865C54B8FBF424A3B775AF6010D455863ExchangeServe_-- From tgl@sss.pgh.pa.us Mon May 3 13:33:30 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id BC5F46334D8 for ; Mon, 3 May 2010 10:33:30 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 54103-05 for ; Mon, 3 May 2010 13:33:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 783DE63326A for ; Mon, 3 May 2010 10:33:23 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o43DXLjS014003; Mon, 3 May 2010 09:33:21 -0400 (EDT) To: Andreas Gaab cc: pgsql-sql mailing list , =?iso-8859-1?Q?Hans_Joachim_M=FCnzer?= Subject: Re: WAL-files restore and nextval('PK') In-reply-to: <48DA836F3865C54B8FBF424A3B775AF6010D455863@Exchange-Server> References: <48DA836F3865C54B8FBF424A3B775AF6010D455863@Exchange-Server> Comments: In-reply-to Andreas Gaab message dated "Mon, 03 May 2010 13:16:31 +0200" Date: Mon, 03 May 2010 09:33:21 -0400 Message-ID: <14002.1272893601@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.879 tagged_above=-5 required=5 tests=AWL=0.031, BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/7 X-Sequence-Number: 34055 Andreas Gaab 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 From alvherre@commandprompt.com Mon May 3 15:28:59 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 8F63163402B for ; Mon, 3 May 2010 12:28:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 80143-01-7 for ; Mon, 3 May 2010 15:28:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id 7A73963395B for ; Mon, 3 May 2010 12:28:52 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id A739B348038; Mon, 3 May 2010 08:28:51 -0700 (PDT) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id SqO5ectKpfCL; Mon, 3 May 2010 08:28:49 -0700 (PDT) Received: from perhan.alvh.no-ip.org (unknown [190.95.8.51]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id 4DCB8348037; Mon, 3 May 2010 08:28:49 -0700 (PDT) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 545A96E355; Mon, 3 May 2010 11:28:47 -0400 (CLT) Date: Mon, 3 May 2010 11:28:47 -0400 From: Alvaro Herrera To: Andreas Gaab Cc: pgsql-sql mailing list , Hans Joachim =?iso-8859-1?Q?M=FCnzer?= Subject: Re: WAL-files restore and nextval('PK') Message-ID: <20100503152847.GC2986@alvh.no-ip.org> References: <48DA836F3865C54B8FBF424A3B775AF6010D455863@Exchange-Server> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF6010D455863@Exchange-Server> User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.708 tagged_above=-5 required=5 tests=AWL=0.192, BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201005/11 X-Sequence-Number: 34059 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.