Replication in postgres 9.0
We at Genexdb Solutions are constantly test and try new features of open source databases like MySQL,Mongo,PostgreSQL,MariaDB etc. We wanted to explore on how inbuilt replication got started with postgres and thought it is worth sharing.
How to Configure Replication in PostgreSQL 9
Postgres didn’t had its own replication feature in PostgreSQL till 9.0 version, and it had only third party tools to configure replication in postgres in earlier versions which certainly had many limitations. So recently Postgresql has added a feature called Streaming Replication which allows ( master-slave replication ). This “Streaming Replication (SR)”, which has been included in the recent PostgreSQL major release 9.0 is a built-in replication solution, and its working fine and setting this up is quite easy.
steps to implement replication PostgreSQL replication system
- Download and install postgresql 9.0.1.
- Initialize a database and take a Backup.
- Configure the master node.
- Configure the slave node(s).
- Start the master and slave node(s).
Download and install postgresql 9.0.1
This is one of the mirror from where you can get the postres Binaries.
wget http://wwwmaster.postgresql.org/download/mirrors-ftp/source/v9.0.1/postgresql-9.0.1.tar.gz tar -xvf postgresql-9.0.1.tar.gz ./configure --prefix=/usr/local/pgsql make su - make install
Hope you installed postgres 9.0.1 successfully using above steps. Lets move to the next step now.
Initialize a database and take a Backup
/usr/local/pgsql/bin/initdb –D /usr/local/pgsql/data –- no-locale –-encoding=UTF8 #This statement initializes the DB.
Now to take a backup there are few variables which should be changed in the configuration file.
The priciple in postgres before taking backup is host should be set in archive log mode. This can be made by editing the /usr/local/pgsql/data/”’postgresql.conf”’ file with the following changes.
archive_mode = on # to enable the archiving log mode. wal_level = hot_standby # to generate WAL records for Recovery purpose.
Save the above changes in the config file and start postgres because those changes need server restart
TAKING BACKUP
/usr/local/pgsql/bin/pg_ctl –D /usr/local/pgsql/data start #This statement starts postgres /usr/local/pgsql/bin/psql –c "SELECT pg_start_backup('initial backup for SR')" template1 tar cvf pg_master_backup.tar /usr/local/pgsql/data /usr/local/pgsql/bin/psql –c "SELECT pg_stop_backup()" template1
Configure the master node
On the master host, you have to edit two configure files in the data directory, “postgresql.conf” and “pg_hba.conf”.
There are few changes as below which should be made to make the postgres instance as a master node
POSTGRESQL.CONF
Add these 4 entries in postgresql.conf to enable the master node.
listen_addresses = '*' # to accept a connection from the slave archive_command = 'cp %p /home/postgres/backups/archive/%f' # to specify the log archiving command. max_wal_senders = 5 # to specify the max number of the slave(s). wal_keep_segments = 32 # to specify the number of the previous WAL files to hold on the master.
See the official manual for more details.
*18.5. Write Ahead Log
* http://developer.postgresql.org/pgdocs/postgres/
runtime-config-wal.html
PG_HBA.CONF
You have to add an entry to accept a connection from the slave in pg_hba.conf. The database name must be “replication” here, and you have to specify IP addresses of the slave nodes there as below.
host replication all 172.29.160.40/32 trust
See the official manual for more details.
*19.1. The pg_hba.conf file
* http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html
Configure the slave node(s)
In the slave node also we have to modify two files i.e , “postgresql.conf” and “recovery.conf”.
POSTGRESQL.CONF
We have to enable hot_standby mode in ths slave in postgresql.conf as below. This is the
hot_standby = on # to allow read-only queries on the slave (standby) node.
POSTGRESQL.CONF
To enable the standby mode on the slave node, you have to create a “recovery.conf” file and add the options as below.
standby_mode = 'on' # to enable the standby (read-only) mode. primary_conninfo = 'host=172.29.160.40 port=5432 user=repl' # to specify a connection info to the master node. trigger_file = '/tmp/pg_failover_trigger' # to specify a trigger file to recognize a fail over. restore_command = 'cp /home/postgres/backups/archive/%f "%p"' # to specify a recovery command.
See the official manual for more details.
*Chapter 26. Recovery Configuration
*
http://developer.postgresql.org/pgdocs/postgres/recovery-config.html
Now the configuration is finished, and now we can start both master and slave nodes.
Start the master and slave node(s)
Start your master PostgreSQL and slave PostgreSQL. After starting both, you may find a server log record on each.
On the ”’master node”’, you may find a server log record as below.
LOG: replication connection authorized: user=backup host=172.29.160.40 port=55811
And on the ”’slave node”’, you can find a record as below.
LOG: streaming replication successfully connected to primary
Thats it ! postgres replication configuration is complete now.
Enjoy Replication with PostgreSQL !
You can contact genexdb Solutions genex@gmail.com if you are looking for any DBA support for your infrastructure.We can help you with Remote DBA support and save if you don’t have it already !!!