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

Mon Jun 20 07:11:28 UTC 2011

>
> > *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.

It's just a check that executed on every pgpool node. It does not matter how
to do tests, result is the same.
I'm not just seeing the sequence difference, its really there.
Here is another tests, I hope they help to understand me:

> *# Only primary node is enabled in pgpool
> *postgres at server01:~> psql -h pgpool -c 'create database serial_test'
> postgres at server01:~> psql -h pgpool -d serial_test -c 'create schema sch'
> postgres at server01:~> psql -h pgpool -d serial_test -c 'create table
> sch.mytable (id serial, name text);'
> postgres at server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('usera')"
> postgres at server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('userb')"
> postgres at server01:~> psql -h pgpool -d serial_test -c 'select * from
> sch.mytable'
>  id | name
> ----+-------
>   1 | usera
>   2 | userb
> *# Online recovery for standby node1 is done
> *postgres at server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('userc')"
> postgres at server01:~> psql -h primary -d serial_test -c 'select * from
> sch.mytable'
>  id | name
> ----+-------
>   1 | usera
>   2 | userb
>   3 | userc
> postgres at server01:~> psql -h standby_1 -d serial_test -c 'select * from
> sch.mytable'
>  id | name
> ----+-------
>   1 | usera
>   2 | userb
>   3 | userc
> ### ^^^ Ok thats cool
> *# Online recovery for standby node2 is done
> *postgres at server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('userd')"
> postgres at server01:~> psql -h primary -d serial_test -c 'select * from
> sch.mytable'
>  id | name
> ----+-------
>   1 | usera
>   2 | userb
>   3 | userc
> *  5 | userd (!!!)
> *postgres at server01:~> psql -h standby_1 -d serial_test -c 'select * from
> sch.mytable'
>  id | name
> ----+-------
>   1 | usera
>   2 | userb
>   3 | userc
> *  4 | userd (ok)
> *postgres at server01:~> psql -h standby_2 -d serial_test -c 'select * from
> sch.mytable'
>  id | name
> ----+-------
>   1 | usera
>   2 | userb
>   3 | userc
> *  5 | userd (!!!)*

*
*
**

As you can see primary and standby_2 has id=5 for the last user because of
this stage in online recovery:

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

If I will add the same line for standby_1 in pgpool_recovery_pitr -

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

everything will be ok and all sequences will have the same start point.

On Mon, Jun 20, 2011 at 9:21 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > 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
>

-- 
Best regards,
Koldaev Anton
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20110620/2bcbb581/attachment-0001.html>