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

Want to support HowtoForge? Become a subscriber!
 
Submitted by wintel2006 (Contact Author) (Forums) on Wed, 2011-06-29 16:56. ::

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

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

 

Reference Links:

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


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by wintel2006 (registered user) on Tue, 2013-07-09 19:05.

for those who follow this tutorial to build the replication server with postgresql version 9.1 or above, you may get the error message like Fatal: the database system is starting up

 Just before start the slave server, you need to modify postgresql.conf on slave server in step 6 after the repmgr standy clone complete, and change hot_standby=off to on

#hot_standby=off

hot_standby=on

 

I appreciate anyone who point out this additional step, and I feel sorry have not updated this tutorial recently.