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

Step 5. Install repmgr source code manually on both master and slave server
Step 6. Clone master database to slave (or standby server) [SLAVE ONLY]. Again, this is only on slave server.
Step 7. Set up repmgr configuration file on both master and slave server
Step 8. Register repmgr master and slave server, and start monitoring process
Let's do one more test for the replication:
Work needed to improve in the future:
Reference Links:

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
make USE_PGXS=1 install

Make sure repmgr is installed correctly by checking:

repmgr --version
repmgrd --version

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:

/etc/init.d/postgresql start

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');"

And now:

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:

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 [email protected]. Thanks.

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