[Pgpool-general] [Pgpool-II PITR recovery] Serial data type "+1" after PITR recovery

[Tatsuo Ishii]

> Got it!
> Script pgpool_recvery_pitr:
> 
>> psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND
>> datallowconn' template1|
>> while read i
>> do
>> if [ "$i" != "" ];then
>> psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind =
>> 'S'" $i
>> fi
>> done
> 
> 
> *After recovery all sequences in
> database advance 1. Please, add it to the official doc!*
> 
> There is no information in official docs about it:
> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery
> .
> They forgot about other enabled nodes in pgpool. This statement MUST BE RUN
> for every enabled node in pgpool to keep all sequence points on all nodes
> equal.

No. It doesn't need.

You did not show your configuration file, so this is my guess. I think
the reason why you are seeing the sequence difference is, you connect
to your PostgreSQL directly to execute SELECT nextval(). You MUST not
do it under replication mode.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> On Mon, Jun 20, 2011 at 3:28 AM, Anton Koldaev <koldaevav at gmail.com> wrote:
> 
>> Hello
>> I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4
>> In both cases I'm getting the same result: after online recovery on primary
>> and target nodes all 'serial' values got "+1". Other nodes enabled in pgpool
>> have old serial values.
>> Here is my commands:
>>
>>> postgres at server01:~> psql -h primary_node -p 5432 -c 'create database
>>> serial_test'
>>> postgres at server01:~> psql -h primary_node -p 5432 -d serial_test -c
>>> 'create schema sch'
>>> postgres at server01:~> psql -h primary_node -p 5432 -d serial_test -c
>>> 'create table sch.mytable (id serial, name text);'
>>> postgres at server01:~> psql -h primary_node -p 5432 -d serial_test -c '\d
>>> sch.mytable'
>>>                              Table "sch.mytable"
>>>  Column |  Type   |                        Modifiers
>>>
>>> --------+---------+----------------------------------------------------------
>>>  id     | integer | not null default
>>> nextval('sch.mytable_id_seq'::regclass)
>>>  name   | text    |
>>> postgres at server01:~> psql -h primary_node -p 5432 -d serial_test -c
>>> "insert into sch.mytable (name) values ('usera')"
>>> postgres at server01:~> psql -h primary_node -p 5432 -d serial_test -c
>>> 'select * from sch.mytable'
>>>  id | name
>>> ----+-------
>>>   1 | usera
>>> postgres at server01:~> psql -h primary_node -p 5432 -d serial_test -c
>>> "select nextval('sch.mytable_id_seq')"
>>>  nextval = 2 # Thats ok
>>> # Online recovery via PITR (
>>> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery
>>> )
>>> # Recover the first standby node: standby_1
>>> *postgres at server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600
>>> localhost 9999 user pass 1 &
>>> *# DONE recovery
>>> *postgres at server01:~> psql -h primary_node -p 5433 -d serial_test -c
>>> "select nextval('sch.mytable_id_seq')"
>>> * *nextval = 4* # Where is "3"??
>>> *postgres at server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select
>>> nextval('sch.mytable_id_seq')"
>>> * *nextval = 4* # Where is "3"??
>>> # Recover the second node: standby_2
>>> *postgres at server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600
>>> localhost 9999 user pass 2 &
>>> *# DONE recovery
>>> *postgres at server01:~> psql -h primary_node -p 5433 -d serial_test -c
>>> "select nextval('sch.mytable_id_seq')"
>>>  nextval = 6* # Where is "5"?
>>> *postgres at server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select
>>> nextval('sch.mytable_id_seq')"
>>>  nextval = 5* # Thats ok
>>> *postgres at server01:~> psql -h standby_2 -p 5433 -d serial_test -c "select
>>> nextval('sch.mytable_id_seq')"
>>> * *nextval = 6* # Where is "5?"
>>>
>>
>> What am I doing wrong?
>>
>> I've tried to set this options without success:
>>
>>> black_function_list = 'lastval,currval,nextval,setval'
>>
>> insert_lock = true
>>
>>
>> --
>> Best regards,
>> Koldaev Anton
>>
> 
> 
> 
> -- 
> Best regards,
> Koldaev Anton