Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

# 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
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4

# 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
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 5
postgres@server01:~> psql -h standby_2 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6

What am I doing wrong?

I've tried to set this options without success:

black_function_list = 'lastval,currval,nextval,setval'
insert_lock = true 

Resolved

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

There is no information in official docs about it:
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.

share|improve this question
1  
Would you be willing to post your resolution as an answer? –  Richard Aug 12 '11 at 15:39

1 Answer 1

Answer per the question:


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.

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.