[pgpool-general: 2069] Re: trouble with recovery of a downed node

Tatsuo Ishii ishii at postgresql.org
Fri Aug 23 08:22:10 JST 2013

> On 13-08-22 08:59 AM, Sean Hogan wrote:
>> On 13-08-21 08:23 PM, Tatsuo Ishii wrote:
>>>> Ah, I believe I understand the problem now: the sequence values are
>>>> different (off by one) between the original and recovered
>>>> databases. The sample pgpool_sample_pitr script has: SELECT
>>>> setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S' When I
>>>> change that to SELECT setval(oid, 1*currval(oid)) FROM pg_class WHERE
>>>> relkind = 'S' I get identical databases and the problem has not
>>>> reoccurred! (The "1*" is to defeat optimization; not sure if it is
>>>> needed.) Thanks for your excellent advice.
>>> Thanks for the report but your changes seems a little bit strange to
>>> me. According to the PostgreSQL manual, calling currval() without
>>> prior calling nextval() will raise an error. Don't you get any error?
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English:http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp
>> That's interesting, I was unaware of that restriction on
>> currval. Indeed if I run the script manually I do get an error.  Now
>> I'm quite confused, because the overall effect is what I need.  Based
>> on the manual page at
>> http://www.postgresql.org/docs/9.2/static/functions-sequence.html if
>> appears the following will work:
>> SELECT setval(oid, nextval(oid), false) FROM pg_class WHERE relkind =
>> 'S'
>> and my initial experiments suggest it does.
>> Sean
> But later experiments show I'm back where I started.  :-( Is there any
> way to get the current sequence value out into the WAL without
> disturbing it?
> Or am I looking at the wrong thing?  Is there some other logical
> reason why the sequence values were all off by one?

Does not reproduce here. What I did was, use pgpool_setup command comes with pgpool-II 3.3 (unfortunately that comes with 3.3.0 is buggy. Please grab it from 3.3-stable head: http://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=test/pgpool_setup;hb=78fde207b7d3a3b80b63618e7e65eeec7eeb159b);

mkdir test
cd test
pgpool_setup -m r (this will create pgpool replication mode setting along with two PostgreSQL clusters "data0" and "data1". They can be accessed using port 11002, 11000 and 11001 repectively).
create pgbench script as "pgb.sql"(attached)
pgbench -i test
psql -c "create table t1(i int, j serial)" test
pg_ctl -D data1 -m f stop
pgbench -p 11002 -T 30 -c 10 -f test &
pcp_recovery_node 1 localhost 11003 t-ishii t-ishii 1 (replace "t-ishii" with your account name)

After finish online recovery, compare t1 on data0 and data1.

psql -p 11000 test
\copy t1 to d0

psql -p 11001 test
\copy t1 to d1

then compare d0 and d1.
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
-------------- next part --------------
\setrandom aid 1 100000
insert into t1 values(:aid);
\sleep 20 ms

More information about the pgpool-general mailing list