Replication in postgres 9.0

by | Mar 12, 2018 | PostgreSQL

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

 

  1. Download and install postgresql 9.0.1.
  2. Initialize a database and take a Backup.
  3. Configure the master node.
  4. Configure the slave node(s).
  5. 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 !!!

%d bloggers like this: