PostgreSQL 9.0 Streaming replication setting with pgpool-II Part4
Starting pgpool-II Part 4
Login to pgpoolAdmin and start pgpool-II from “pgpool status” menu.
You can see port 5432 PostgreSQL is running as a primary server.
You should be able to connect to port 9999 by using psql. Let’s try to create a table.
$ createdb -p 9999 test $ psql -p 9999 test test=# create table t1(i int); CREATE TABLE test=#
You will find something like this in the pgpool log.
$ tail /var/log/pgpool/pgpool.log.Wednesday 2010-11-03 23:12:55 LOG: pid 4148: DB node id: 0 backend pid: 4607 statement: create table t1(i int);
You will see same thing in the PostgreSQL log as well.
$ tail /home/postgres/data/pg_log/Wednesday.log 4607 2010-11-03 23:12:55 JST LOG: statement: create table t1(i int);
Starting standby server
At this point standby server is not running. To start standby server, just click “Recovery” button. “basebackup.sh” will be executed and the standby server will be automatically started.
Once standby server is running, streaming replication starts. Let’s insert some data into t1.
-- insert into t1 via pgpool-II. -- it will be executed on primary server psql -p 9999 test test=# insert into t1 values(1); test=# q psql -p 5433 test -- now connected to standby server test=# select * from t1; i --- 1 (1 row)
When the standby server goes down.
If the standby goes down, it is disconnected from pgpool-II. Users can issue SQL via pgpool-II as usual. Just streaming replication is stopped. To recover standby server, click “Recovery” button.
When the primary server goes down.
When the primary server is lost, the Standby server is there for this situation, here is a quick test.
$ pg_ctl -D /home/postgres/data -m f stop
As you can see, port 5432 PostgreSQL goes down and 5433 PostgreSQL takes over the primary role.
When pgpool-II finds that primary is going down it executes failover script(failover.sh). The script creates trigger file as /var/log/pgpool/trigger/trigger1. Standby server finds the file and decides to promote to primary. If you click the “Recovery” button of 5432 PostgreSQL, the former primary server will be recovered as standby server.
Summary
PostgreSQL 9.0 supports simple and easy to use built-in replication system. Adding pgpool-II on top of the replication system it is possible to build a high availability (HA) system.
Back all knowledge Sharing: All TutorialsPart 4