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