PostgreSQL 9.0 Simple Streaming replication setting with PgPool-II Part1

Government | Commercial

KNOWLEDGE BASE

PostgreSQL 9.0 Simple Streaming replication setting with pgpool-II Part1

Overview
In this multi part tutorial, I will show how to create a simplified streaming replication setting using pgpool-II step by step. What you need is just a Linux box. Onto the box I install two PostgreSQL instances, pgpool-II and pgpoolAdmin.
Setting PgPool-II will make possible to implement important functionalities, which PostgreSQL 9.0 lacks:

  • Automated failover. If one of the two PostgreSQL goes down, pgpool-II will automatically let remaining node take over and keep on providing database service to applications
  • Query dispatching and load balancing. In streaming replication mode, applications need to carefully chose queries to be sent to standby node. Pgpool-II checks the query and automatically chose primary or standby node in sending queries. So applications need not to worry about it
  • Online recovery. Recover failed node without stopping pgpool-II and PostgreSQL

The concepts on this tutorial should be sufficient to extend to production environment settings: i.e. two dedicated DB servers, Mater-Slave

Here is a high level view of the architecture of our implementation:
PgPool II Architecture

Please note that in this figure port 5432 PostgreSQL is assigned to the primary server and port 5433 PostgreSQL is assigned to the standby server. This is just an initial setting and afterward you can interchange the role of each PostgreSQL by using online recovery.

Installing PostgreSQL 9.0

The easiest way is downloading Linux package from PostgreSQL official site. However usually Linux packages allow starting only 1 PostgreSQL instance. So you need to disable automatic starting at the system booting time and start PostgreSQL by hand using pg_ctl.
Another way to install PostgreSQL is from the source code. This is surprisingly easy actually. Just unpack the tar ball and configure; make; make install.
See the PostgreSQL Tutorial to Install and Configure PostgreSQL in OpenSUSE 12 Part1 For a step by step implementation on Open SuSe

From now on, let’s assume that database clusters are located at /home/postgres/data and /home/postgres/standby and are owned by postgres user: 

$ initdb -D /home/postgres/data 
$ initdb -D /home/postgres/standby

Next add followings to /home/postgres/data/postgresql.conf and /home/postgres/standby/postgresql.conf. “logging_collector”

The lists of parameters below are not really relevant to Streaming replication but these make things easier. The “log_statement = ‘all'” might be removed for production environments.

hot_standby = on 
wal_level = hot_standby 
max_wal_senders = 1 
logging_collector = on 
log_filename = '%A.log' 
log_line_prefix = '%p %t ' 
log_truncate_on_rotation = on 
log_statement = 'all'

Add following to /home/postgres/standby/postgresql.conf because we want to run standby PostgreSQL at 5433 port.

port = 5433

Make sure pg_hba.conf is in /home/postgres/data if no then find it and cp it over:

$ cp source/path pg_hba.conf" /home/postgres/data"

Start PostgreSQL primary server.

$ pg_ctl -D /home/postgres/data start

Next, we will be doing the installing pgpool-II Part2