PgSQL Replication

This page describes how to set up direct replication with pgSQL. Generally, replication allows having two database servers (typically one for data entry and one for reporting) that stay synchronised. Concretely, when data is added, deleted or updated on the master server, the changes get immediately replicated on the slave or standby server. The standby database is read-only, i.e. no changes can be made directly there - only changes on the master are being replicated. Currently (August 2014) this mechanism is only in place at MACCA in Kabul.

Requirements

• Two computers/servers that can communicate with each other over the network. The communication goes over the port number 5432 (in a default setup), thus there might be firewall settings to be adapted, to ensure that the 5432 port on each computer is accessible for the other one.
• The exact same IMSMA version on both computers.

Steps for stetting up the replication with pgSQL

On the master server, with IMSMA database already installed:

1. Create a new user on the database by running the following SQL on the IMSMA database:

   CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'password';

2. Uncomment and edit the following lines in the postgresql.conf file in C:/IMSMAng/pgsql/data:

   153 wal_level = hot_standby
   174 checkpoint_segments = 8
   196 max_wal_senders = 3
   199 wal_keep_segments = 8
   

   The numbers on the left correspond to the number of the line.

3. Add the following line in the pg_hba.conf file in C:/IMSMAng/pgsql/data:

   host replication replicator <standby-ip>/32 trust
   

   <standy-ip> is the IP address of the standby server (to be determined by running the command

   ipconfig /all

   in a command window)
4. Restart the PGSQL service

On the standby server, also with IMSMA installed:

1. Stop the PGSQL service and delete all the contents of C:/IMSMAng/pgsql/data
2. Open a command window, cd into the directory C:/IMSMAng/pgsql/bin and run the following command:

   pg_basebackup –h <master-ip> -D c:/IMSMAng/pgsql/data –U replicator –v –P
   

   This command makes the initial synchronisation, i.e. copies the entire database from the master to the standby. This can take some time, depending on the size of the database. The progress can be followed on the command window as shown below.
   The following warning message can be ignored:
   
   

   The hot standby replication mechanism can only be applied to the entire database server, i.e. all the databases from the master will be replication to the standby -- not only the database imsma but any database that is running on the master.

3. Create a new file called recovery.conf in C:/IMSMAng/pgsql/data with the following contents:

   standby_mode = 'on'
   primary_conninfo = 'host=<master-ip> port=5432 user=replicator'
   

   <master-ip> is the IP address of the master server.

4. Uncomment and edit the following lines in postgresql.conf in C:/IMSMAng/pgsql/data:

   210 hot_standby = on
   

5. Restart the PGSQL service

   Depending on the master and standby systems, there might need to be changes made to the pg_hba.conf file, such as commenting the lines relating to IPv6 local connections. Contact VIE if there are any troubles with this.

Template:NavBox Hub