Share, , Google Plus, Pinterest,

Print

Posted in:

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
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.

You can follow my posts on “Install PostgreSQL 9.3 on CentOS 6” or “Install PostgreSQL 9.4 on CentOS 7” if you need any help.

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:

postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD 'password' LOGIN;
CREATE ROLE
postgres=#

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):

WRITE AHEAD LOG section

wal_level = hot_standby # This sets slave server in read-only mode
fsync = on # Force synchronization
synchronous_commit = on # Enable synchronous commit
wal_sync_method = open_sync # Write WAL files with open() option O_SYNC

REPLICATION section

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.

Skip the “/var/lib/pgsql/9.4/data/postgresql.conf” and “/var/lib/pgsql/9.4/data/pg_hba.conf” configuration files:

[root@node-1 /]# rsync -avr --exclude postgresql.conf --exclude pg_hba.conf /var/lib/pgsql/ 192.168.10.101:/var/lib/pgsql/

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:

hot_standby = on
hot_standby_feedback = on

Create a new PostgreSQL slave instance specific configuration file “/var/lib/pgsql/recovery.conf”. Insert the following lines to “recovery.conf” file:

standby_mode = 'on'
primary_conninfo = 'host=192.168.10.100 port=5432 user=replication application_name=slave_node1'

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:

[root@node-2 /]# chown postgres:postgres /var/lib/pgsql/9.4/data/recovery.conf
[root@node-2 /]# chmod 600 /var/lib/pgsql/9.4/data/recovery.conf

9. Start Slave PostgreSQL

Start slave PostgreSQL instance and check if the recovering process is up and running:

[root@node-2 /]# service postgresql-9.4 start
Redirecting to /bin/systemctl start postgresql-9.4.service
[root@node-2 /]# ps auxf |grep postgres
root 6692 0.0 0.1 112640 984 pts/0 S+ 15:13 0:00 _ grep --color=auto postgres
postgres 6679 0.1 2.0 337480 15212 ? S 15:13 0:00 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
postgres 6680 0.0 0.1 192596 1364 ? Ss 15:13 0:00 _ postgres: logger process 
postgres 6681 0.0 0.2 337596 2240 ? Ss 15:13 0:00 _ postgres: startup process recovering 000000010000000000000001
postgres 6682 0.0 0.2 337480 1676 ? Ss 15:13 0:00 _ postgres: checkpointer process 
postgres 6683 0.0 0.2 337480 1684 ? Ss 15:13 0:00 _ postgres: writer process 
postgres 6684 0.0 0.2 192724 1576 ? Ss 15:13 0:00 _ postgres: stats collector process

10. Start Master PostgreSQL

Start master PostgreSQL instance and check if the WAL sender process is up and running:

[root@node-1 /]# service postgresql-9.4 start
Redirecting to /bin/systemctl start postgresql-9.4.service
[root@node-1 /]# ps auxf |grep postgres
root 28875 0.0 0.1 112640 984 pts/0 S+ 15:14 0:00 _ grep --color=auto postgres
postgres 28864 0.1 1.9 337444 15196 ? S 15:14 0:00 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
postgres 28865 0.0 0.1 190496 1340 ? Ss 15:14 0:00 _ postgres: logger process 
postgres 28867 0.0 0.2 337444 1688 ? Ss 15:14 0:00 _ postgres: checkpointer process 
postgres 28868 0.0 0.2 337444 1696 ? Ss 15:14 0:00 _ postgres: writer process 
postgres 28869 0.0 0.2 337444 1692 ? Ss 15:14 0:00 _ postgres: wal writer process 
postgres 28870 0.0 0.3 337844 2560 ? Ss 15:14 0:00 _ postgres: autovacuum launcher process 
postgres 28871 0.0 0.2 192724 1756 ? Ss 15:14 0:00 _ postgres: stats collector process 
postgres 28873 0.6 0.4 338240 3092 ? Ss 15:14 0:00 _ postgres: wal sender process replication 10.5.5.121(53798) streaming 0/1718C90

11. Check PostgreSQL Synchronous Streaming Database Replication Status

Connect to the master PostgreSQL instance and run the following query to check the replication status:

postgres=# SELECT * FROM pg_stat_replication;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | rep
lay_location | sync_priority | sync_state 
-------+----------+-------------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+----
-------------+---------------+------------
 28383 | 16384 | replication | slave_node1 | 192.168.10.101 | | 53789 | 2015-08-10 14:56:13.610889+02 | | streaming | 0/1718D38 | 0/1718D38 | 0/1718D38 | 0/1718D38 | 1 | sync
(1 row)

As you can see the PostgreSQL Synchronous Streaming Database Replication status is OK, the state is “streaming” and the sync_state is “sync” which means the configured replication is synchronous.

More posts on advanced usage of PostgreSQL similar to PostgreSQL Synchronous Streaming Database Replication comming up soon, stay tuned.

  • Marios Braimiotis

    Very good tutorial. Running without dislaying errors, but why recovery proccess is not running?

    [root@localhost ~]# ps auxf |grep postgres

    root 28604 0.0 0.0 6440 680 pts/0 S+ 22:01 0:00 _ grep postgres

    postgres 28591 0.1 0.3 227832 14972 ? S 22:01 0:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data

    postgres 28593 0.0 0.0 82948 1468 ? Ss 22:01 0:00 _ postgres: logger process

    postgres 28595 0.0 0.0 227832 1700 ? Ss 22:01 0:00 _ postgres: checkpointer process

    postgres 28596 0.0 0.0 227832 1860 ? Ss 22:01 0:00 _ postgres: writer process

    postgres 28597 0.0 0.0 227832 1680 ? Ss 22:01 0:00 _ postgres: wal writer process

    postgres 28598 0.0 0.0 228232 2496 ? Ss 22:01 0:00 _ postgres: autovacuum launcher process

    postgres 28599 0.0 0.0 82944 1656 ? Ss 22:01 0:00 _ postgres: stats collector process

    • Mitch

      Hi Marios, are the PostgreSQL instances seeing each other on port 5432? Is the firewall opened? You can easily test this with telnet client or similar.

  • eknaprasath

    Works great thanks,
    very good tutorial thank you once again

  • Dilip Kumar

    when i doing this in master server i.e., listen_addresses = ‘192.168.10.200’ then database server is not get start.
    and a message reflect in log file i.e., tcp/ip bind error

    • Mitch

      Is the IP address 192.168.10.200 configured and active on your server?

  • Van Nguyen

    The slave is now read only. How do I change it to read and write?