PostgreSQL 9.0 Simple Streaming replication setting with pgpool-II Part1
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:
The concepts on this tutorial should be sufficient to extend to production environment settings: i.e. two dedicated DB servers, Mater-Slave
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