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