PostgreSQL Streaming Replication with pgpool-II Part4

Government | Commercial

KNOWLEDGE BASE

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.

pgPoolStatusMenu

You can see port 5432 PostgreSQL is running as a primary server.
Running_As_Primary
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.
standby_autostarted

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

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

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