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

[Tatsuo Ishii]

> Okay, this query must be run for pgpool?
> 
> 
>     psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class
> WHERE relkind = 'S'" $i

No. The script defined in the recovery script is executed
automatically on master.

I'm talking about this:

> *postgres at server01:~> psql -h primary_node -p 5433 -d serial_test -c
>  "select nextval('sch.mytable_id_seq')" 

I suspect this is executed directly against PostgreSQL master. If so,
you should not do this.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> I really connect directly to Primary PostgreSQL to execute the query,
> because of this comment:
> 
> 
> port=5432		# PostgreSQL port number
> 
> I'll try to execute it to PgPool.
> 
> 
> On Mon, Jun 20, 2011 at 6:46 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > 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
>>
> 
> 
> 
> -- 
> Best regards,
> Koldaev Anton