PostgreSQL Synchronous Streaming Database Replication

PostgreSQL is a great advanced open source database. It offers many features, one of them being database replication. PostgreSQL 9.4 offers many different solutions to the replication problems, each one of them having it’s number of up’s and down’s. In the end it is up to the system or database administrator to decide which solution is best for specific scenario.

In the following post i decided to present how to configure PostgreSQL synchronous streaming database replication. Follow the steps closely and by the end of this tutorial you will have a working master/standby synchronous PostgreSQL replication. The benefit of a synchronous PostgreSQL replication over an asynchronous is that in case of master PostgreSQL database server failure you are sure you did not lose any data. The database client connected to the master PostgreSQL database gets a success message only once the data is written to both, master and standby PostgreSQL database server therefore there is no chance of loosing valuable data in the process.

Please note that the master/standby configuration actually means that the master server is in read/write mode while the standby server is in read-only mode. The read-only mode can however still be useful for reporting or similar activity.

PostgreSQL Synchronous Streaming Database Replication

Configure PostgreSQL Synchronous Streaming Database Replication

1. Install and Initialize PostgreSQL 9.4

To start configuring PostgreSQL Synchronous Streaming Database Replication i assume you have successfully installed and initialized PostgreSQL 9.4 on both linux servers you would like to use.

2. Create PostgreSQL Replication Role

For the PostgreSQL Synchronous Streaming Database Replication to work, the standby instance connects to the master instance using a database user with replication privileges. Therefore we need to create a role and allow it to be used for replication purposes. Do this on the master instance.

Connect to master PostgreSQL server via “psql” and issue the following command:

3. PostgreSQL Listen on IP Address

By default PostgreSQL only listens on “localhost” therefore denying outside connections to the database server. Firstly we must make the PostgreSQL database server to listen on desired IP address, which can be reconfigured in “/var/lib/pgsql/9.4/data/postgresql.conf” configuration file. Do this on the master instance.

Find the following line:

#listen_addresses = 'localhost'

Uncomment it and change to the desired IP address as follows:

listen_addresses = '192.168.10.100'

Be sure to restart PostgreSQL service for changes to take effect.

4. Allow Replication Connection

You also need to allow connections from the PostgreSQL standby instance using the newly created replication role. You can configure this in “/var/lib/pgsql/9.4/data/pg_hba.conf” configuration file. Do this on the master instance.

Append the following lines at the bottom of configuration file – change the IP address with the one of your standby PostgreSQL server instance:

host replication replication 192.168.10.101/32 trust

Note that by applying the “trust” for “replication” user from slave IP address “192.168.10.101” this will allow connection to master PostgreSQL instance without password authentication.

Be sure to restart PostgreSQL service for changes to take effect.

5. Configure Streaming Parameters on Master

You need to edit “/var/lib/pgsql/9.4/data/postgresql.conf” configuration file and change some parameters accordingly to configure the PostgreSQL streaming replication. Do this on the master instance.

Find the following parameters in “postgresql.conf” and configure them as follows (uncomment if required):

max_wal_senders = 1 # Max number of WAL senders - one slave, one sender
synchronous_standby_names = 'slave_node1' # Make this up, but remember it, will be used on slave instance configuration file

6. Start and Stop Master PostgreSQL

Once “postgresql.conf” file was reconfigured for “hot_standby” we need to start it to write out WAL logs with this configuration enabled. Once the master PostgreSQL has successfully started you can turn it off again.

7. Sync Data – Master to Slave

While both PostgreSQL instances are down, use rsync or some other tool to copy all of the contents in folder “/var/lib/pgsql/” from master PostgreSQL instance to slave PostgreSQL instance.

8. Configure Streaming Parameters on Slave

Your PostgreSQL Synchronous Streaming Database Replication is almost up and running. Now you just need to open up the “/var/lib/pgsql/9.4/data/postgresql.conf” configuration file and apply the following change:

The “recovery.conf” file provides information required for successful slave PostgreSQL connection to master instance. Note that IP address of the master instance must be changed accordingly along with the port number. Also the “application_name” is the “synchronous_standby_names” parameter configured on master PostgreSQL instance.

Change the owner of “/var/lib/pgsql/recovery.conf” to “postgres” user and make it rw for owner only: