[Pgpool-general] pgpool-II + warm standby

[Yoshiyuki Asaba]

Hi,

I've tried pgpool-II + PostgreSQL warm standby.

* Environment
  - pgpool-II(host1)
    - CVS HEAD version, not 2.0.1
  - Active PostgreSQL server(host2)
    - 8.3.0
  - Standby PostgreSQL server(host3)
    - 8.3.0
    - install pg_standby


* Setting pgpool-II
pgpool-II can set a trigger command executed at failover. I set
"failover_command" parameter to notice to standby server.

- pgpool.conf:
  connection_cache = true
  replication_mode = false
  master_slave_mode = false
  failover_command = 'ssh host3 touch /tmp/pgsql.trigger'
  backend_hostname0 = 'host1'
  backend_port0 = 5432
  backend_hostname1 = 'host2'
  backend_port1 = 5432
  health_check_period = 15


* Setting PostgreSQL
I used pg_standby module to recovery from WAL logs.
See the following URL.

http://www.postgresql.org/docs/8.3/static/pgstandby.html 

- postgresql.conf:
  archive_command = 'scp %p host3:/data/archive_log/%f'
  archive_timeout = 30

- recovery.conf:
  restore_command = 'pg_standby -s 2 -t /tmp/pgsql.trigger /data/archive_log %f %p %r'


* Setting warm standby
  host2% psql -c "SELECT pg_start_backup('warm standby')" postgres
  host2% rsync -az /data/ host3:/data
  host2% psql -c "SELECT pg_stop_backup()" postgres

  host3% cp recovery.conf /data
  host3% pg_ctl start


* Test
I've checked failover from host2 to host3.

  host1% createdb -p 9999 bench
  host1% pgbench -p 9999 -i bench
  host1% psql -p 9999 'SELECT pg_switch_xlog()' postgres

  host2% pg_ctl -D /data -mi stop (crash!!!)

  host1% psql -p 9999 'SELECT count(*) FROM accounts' bench


* Result
Success!


I think pgpool-II + warm standby is a good solution for HA system.
Any comment?

Regards,
--
Yoshiyuki Asaba
y-asaba at sraoss.co.jp