Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

How To Set Up A Postgresql 9.0 Hot Standby Streaming Replication Server With Repmgr On OpenSUSE 11.4


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.

wintel2006@hotmail.com,

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

1

And then start postgresql server to make sure it works:

/etc/init.d/postgresql start

2

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:

passwd postgres

3

su – postgres
ssh-keygen -t rsa

4

ssh-copy-id -i ~/.ssh/id_rsa.pub pgslave

Test if we can ssh to pgslave from pgmaster with password:

ssh pgslave

5

And do the same step on the slave:

su – postgres
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub pgmaster
ssh pgmaster

6

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
7

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
8

Now restart postgresql, and create a test database for replication use.

/etc/init.d/postgresql restart

9

We create a test database and generate some data on master database:

su – postgres
createdb pgbench
pgbench -i -s 10 pgbench

10

Step 4. Stop postgresql server, and remove all content in the data directory (slave server only)

/etc/init.d/postgresql stop
cd /var/lib/pgsql/data
rm -rf *

11

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.

13

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

14

And now compile repmgr and install the software:

make USE_PGXS=1
make USE_PGXS=1 install

15

16

Make sure repmgr is installed correctly by checking:

repmgr –version
repmgrd –version

17

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

18

Now you will see a log message on the slave server screen similar to this one…

19

Now start postgresql on the slave:

/etc/init.d/postgresql start

20

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'
21

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'
22

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

23

On the slave server, register the slave database:

repmgr -f /var/lib/pgsql/repmgr/repmgr.conf –verbose standby register

24

Finally, check the record difference by:

psql pgbench -c ‘select * from repmgr_test.repl_status’

25

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’);”

And now:

psql -h pgslave pgbench -c “select * from test”

26

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:

https://github.com/greg2ndQuadrant/repmgr/commit/7427988628f754e57069453d65a71f79117c3a3d

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 wintel2006@hotmail.com. Thanks.

6. The most important part: read README file in the repmgr packages.

 

2ndquadrant: http://projects.2ndquadrant.com/repmgr
PostgreSQL Wiki: http://wiki.postgresql.org/wiki/Hot_Standby
PostgreSQL Wiki: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

 

Comments

comments