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

[Anton Koldaev]

Okay, this query must be run for pgpool?


    psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class
WHERE relkind = 'S'" $i

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