Can someone help me in my simple pgpool replication? Objective: To implement simple postgres database replication using Pgpool
Versions used : Postgres 9.2.4 , Pgpool 3.2
(1) I have created two Centos Virtual machines on my laptop- Centos32, and CentOS32A. Their hostnames are ‘localhost’ and ‘laserga.wipro.tcpn.com’ (as provided by hostname command and Their IPs are 192.168.2.128, and ‘192.168.2.129 ‘ respectively. (as provided by ifconfig command).
(2) I installed Postgres 9.2.4 and Pgpool 3.2 on both machines successfully.
(3) I configured pgpool config files and pcp.conf and pgpool.conf for basic replication. As shown at the end. And restarted the machine.
(4) As ‘postgres’ user I have created a small database : yash01 on both the hosts. Then created a schema and table ‘sch1.tab1 (aa int)’. Inserted 5 integers (1,2, 3, 4, 5) in the table on both the machines.
(5) I start the pgpool on host1 – ‘localhost’. I verify that pgpool started correctly by looking at xxx.pid and xxx.log files. And then doing ps –ef and verifying that pgpool and child processes are running.
(6) On machine one – ‘Localhost’, I insert 5 additional rows in ‘tab1’ in the table (11, 12, 13, 14, 15). Commited it. And then checked it using select.
(7) I go and verify on machine -2 , ‘laserga.wipro.tcpn.com.’ whether the ‘tab1’ entries are replicated on it.
I find that it is NOT replicated. I want to know what mistake I am doing?
Is there something more to be configured - in config files or soemewhere else? I have taken default files pcp.conf and pgpool.conf.sample-replication for replication and made the changes as follows. I did not make any changes to the default database config file.
The glib answer is that using the wrong tool. I've never used pgpool to setup replication, and don't even know if that's possible. pgpool is a proxy interface system for replicated servers allowing read-only queries to be routed to a hot-standby read-only server while write queries only are sent to the master. You really need synchronous replication setup first and the standard postgresql 9.x documentation does a really good job at explaining that setup.