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

[Anton Koldaev]

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.


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20110620/df70b95d/attachment.html>