> 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 _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
