There are tons of tutorials about how to setup streaming replication on postgresql 9.0, and detailed documentation on repmgr, the SR manager program from 2ndQuadrant. Like they said on repmgr homepage:
“PostgreSQL 9.0 allow you to have replicated hot standby servers which you can query and/or use for high availability. While the main components of the feature are included with PostgreSQL, the user is expected to manage the high availability parts. repmgr allows you to monitor and manage your replicated PostgreSQL databases as a single cluster.”
This is a tutorial how to set up a postgresql replicated hot standby server with streaming replication, and we also set up the repmgr to monitor and manage the replication cluster. Unlike most tutorials that copy the database file from master to slave (or standby) in the middle of running pg_start_backup() and pg_end_backup(), repmgr is used to simplify the whole procedure. (But I still think that procedure helps you a lot to understand how postgresql warm standby, pitr, and hot standby replication work.)
I would like to give most of the credits to postgresql community and all the contributors like 2ndQuadrant. You guys did a wonderful job for the SR hot standby features on postgresql database. The work I did on this tutorial is just make postgresql SR hot standby replication works on opensuse 11.4.
I wish this tutorial could help more people start to use postgresql, the most advanced database, also Opensuse, my favorite OS like Debian.
Jun 20th 2011
In this tutorial I will run 2 OpenSUSE 11.4 32-bit bare bone servers under VMware Workstation, both servers were built from OpenSUSE 11.4 network installation iso, which you can download from http://www.opensuse.org.
pgmaster: 192.168.5.187 (master database server)
pgslave: 192.168.5.188 (slave database server, or standby)
And modify /etc/hosts in both servers to have above 2 records.
Step 1. Install postgresql on both master and slave server
zypper install postgresql postgresql-contrib postgresql-server
And then start postgresql server to make sure it works:
Step 2. Set up trusted copy between servers (on both master and slave)
repmgr program will copy database with rsync and ssh for user postgres, so we need to set up ssh login with password between master and slave server:
su – postgres
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub pgslave
Test if we can ssh to pgslave from pgmaster with password:
And do the same step on the slave:
su – postgres
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub pgmaster
Make sure you log out from the remote ssh server after your test.
Step 3. Change postgresql configuration files (pg_ha.conf, postgresql.conf), and restart postgresql (master server only)
Make sure the following lines are changed in postgresql.conf on master server only:
listen_addresses = "*" wal_level = hot_standby checkpoint_segments=30 archive_mode=on archive_command='cd .' max_wal_senders=2 wal_keep_segments=5000 hot_standby=on
And in pg_hba.conf on master server only:
host all all 192.168.5.0/24 trust host replication all 192.168.5.0/24 trust
Now restart postgresql, and create a test database for replication use.
We create a test database and generate some data on master database:
su – postgres
pgbench -i -s 10 pgbench
Step 4. Stop postgresql server, and remove all content in the data directory (slave server only)
rm -rf *
Now test the remote postgresql connection to pgmaster:
psql -h pgmaster -d pgbench
Now postgresql has been installed correctly on both the master and slave server. Time to install the repmgr management program. You need to download the source code from 2ndQuadrant website, and compile it yourself.
Step 5. Install repmgr source code manually on both master and slave server
Download repmgr from http://projects.2ndquadrant.it/sites/default/files/repmgr-1.1.0.tar.gz to /tmp.
Before we compile repmgr with postgresql, we need to install some packages first:
zypper install make gcc postgresql-devel libxslt-devel pam-devel libopenssl-devel krb5-devel
And now compile repmgr and install the software:
make USE_PGXS=1 install
Make sure repmgr is installed correctly by checking:
Make sure both master and slave server have repmgr installed correctly. We are going to clone the master database to the slave (or standby) database in the next step. So please make sure you follow everything above.
Step 6. Clone master database to slave (or standby server) [SLAVE ONLY]. Again, this is only on slave server.
su – postgres
repmgr -D /var/lib/pgsql/data -d pgbench -p 5432 -R postgres –verbose standby clone pgmaster
Now you will see a log message on the slave server screen similar to this one…
Now start postgresql on the slave:
Step 7. Set up repmgr configuration file on both master and slave server
Create a repmgr directory and a repmgr.conf configuration file with the following content on the master:
cluster=test node=1 conninfo='host=pgmaster user=postgres dbname=pgbench'
Create a repmgr directory and a repmgr.conf configuration file with the following content on the slave:
cluster=test node=2 conninfo='host=pgslave user=postgres dbname=pgbench'
Step 8. Register repmgr master and slave server, and start monitoring process
On the master server, register the master database:
repmgr -f /var/lib/pgsql/repmgr/repmgr.conf –verbose master register
On the slave server, register the slave database:
repmgr -f /var/lib/pgsql/repmgr/repmgr.conf –verbose standby register
Finally, check the record difference by:
psql pgbench -c ‘select * from repmgr_test.repl_status’
The slave is about 1 second behind the master.
Let’s do one more test for the replication:
On the master database:
psql pgbench -c “create table test ( test varchar(30));”
psql pgbench -c “insert into test values ( ‘test123’);”
psql -h pgslave pgbench -c “select * from test”
Bingo, everything works now.
Work needed to improve in the future:
This is a quick tutorial how to set up the SR replication server for postgresql 9, and there are several things that need to be improved:
1. Set up repmgr user instead of postgres
2. While I prepare the tutorial, I notice there is a bug on repmgr, After consulting with some postgres experts, here is the fix link that you may be interested in:
3. Please read repmgr documentation for detailed information how to promote slave (or standby) server when master server is dead. (Honestly, I have not met this problem yet, but like it said that it is not whether your hard drive is dead or not, but when it is dead, so upgrade your head drive, and keep the backup regularly.)
4. The is more than one way (the one showed in tutorial) to check whether replication is working. Please go to postgresql.org for further information.
5. Feel free to contact me at email@example.com. Thanks.
6. The most important part: read README file in the repmgr packages.
PostgreSQL Wiki: http://wiki.postgresql.org/wiki/Hot_Standby
PostgreSQL Wiki: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial