How To Set Up A Postgresql 9.0 Hot Standby Streaming Replication Server With Repmgr On OpenSUSE 11.4 - Page 2
On this page
- 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 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 protected] 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