>
> > *postgres@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@server01:~> psql -h pgpool -c 'create database serial_test'
> postgres@server01:~> psql -h pgpool -d serial_test -c 'create schema sch'
> postgres@server01:~> psql -h pgpool -d serial_test -c 'create table
> sch.mytable (id serial, name text);'
> postgres@server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('usera')"
> postgres@server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('userb')"
> postgres@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@server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('userc')"
> postgres@server01:~> psql -h primary -d serial_test -c 'select * from
> sch.mytable'
> id | name
> ----+-------
> 1 | usera
> 2 | userb
> 3 | userc
> postgres@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@server01:~> psql -h pgpool -d serial_test -c "insert into
> sch.mytable (name) values ('userd')"
> postgres@server01:~> psql -h primary -d serial_test -c 'select * from
> sch.mytable'
> id | name
> ----+-------
> 1 | usera
> 2 | userb
> 3 | userc
> * 5 | userd (!!!)
> *postgres@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@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 <[email protected]> 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@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 <[email protected]>
> 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 <[email protected]>
> >> 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@server01:~> psql -h primary_node -p 5432 -c 'create
> database
> >> >>> serial_test'
> >> >>> postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c
> >> >>> 'create schema sch'
> >> >>> postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c
> >> >>> 'create table sch.mytable (id serial, name text);'
> >> >>> postgres@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@server01:~> psql -h primary_node -p 5432 -d serial_test -c
> >> >>> "insert into sch.mytable (name) values ('usera')"
> >> >>> postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c
> >> >>> 'select * from sch.mytable'
> >> >>> id | name
> >> >>> ----+-------
> >> >>> 1 | usera
> >> >>> postgres@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@server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d
> 1600
> >> >>> localhost 9999 user pass 1 &
> >> >>> *# DONE recovery
> >> >>> *postgres@server01:~> psql -h primary_node -p 5433 -d serial_test
> -c
> >> >>> "select nextval('sch.mytable_id_seq')"
> >> >>> * *nextval = 4* # Where is "3"??
> >> >>> *postgres@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@server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d
> 1600
> >> >>> localhost 9999 user pass 2 &
> >> >>> *# DONE recovery
> >> >>> *postgres@server01:~> psql -h primary_node -p 5433 -d serial_test
> -c
> >> >>> "select nextval('sch.mytable_id_seq')"
> >> >>> nextval = 6* # Where is "5"?
> >> >>> *postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c
> >> "select
> >> >>> nextval('sch.mytable_id_seq')"
> >> >>> nextval = 5* # Thats ok
> >> >>> *postgres@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
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
