How to Configure Streaming Replication on PostgreSQL Instances

In this article, I will show you Postgresql installation and its streaming replication configuration of two PostgreSQL instances on different hosts. Our goal is to install Postgres 9.5 on master host and Postgres 9.5 on slave host, and configure streaming replication between them.

Installation and Configuration

To start with, we will install and prepare PostgreSQL 9.5 on master host running CentOS. First of all, we need to to use rpm command to do query for centos-release package to reveal CentOS version:

rpm --query centos-release

In our example we will consider CentOS release 6.8 with x86_64 architecture. Now, we need to install rpm package in order to run yum install command:

rpm -i https://yum.postgresql.org/9.5/redhat/rhel-6.8-x86_64/pgdg-redhat95-9.5-3.noarch.rpm

Please check that everything is on the right place by running:

yum list postgresql95*

Now you can install PostgreSQL 9.5:

yum install postgresql95-server.x86_64

Other packages can be installed according to your needs.

Once Postgresql-9.5 server is installed, a database needs to be initialized and configured. The first command (only needed once) is to initialize the database in PGDATA.

/etc/init.d/postgresql-9.5 initdb

or

service postgresql-9.5 initdb

If you want PostgreSQL to start automatically when the OS start, do the following:

chkconfig postgresql-9.5 on

At this moment you are ready to start the service, but before that we will update /var/lib/pgsql/9.5/data/postgresql.conf file. Please uncomment listen_addresses line and type in your IP address together with localhost.

listen_addresses = 'your IP, 127.0.0.1'

Then, please modify /var/lib/pgsql/9.5/data/pg_hba.conf file as following:

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all your IP md5

To control the database service, use

service postgresql-9.5 [command]

where [command] can be:

start - start the database
stop - stop the database
restart - stop/start the database. Usually used to read changes to core configuration files.
reload - reload pg_hba.conf file while keeping database running

Please start the service and we are done with Postgresql 9.5 installation on master host.

Next step, we will install Postgresql 9.5 on slave host. Steps are pretty much the same. To start with, we need to install rpm package.

Reminder: following steps should be performed on another host, so-called slave host.

rpm -i https://yum.postgresql.org/9.5/redhat/rhel-6.8-x86_64/pgdg-redhat95-9.5-3.noarch.rpm

and install Postgresql 9.5

yum install postgresql95-server.x86_64

Then initialize it

service postgresql-9.5 initdb

Make it start automatically on startups

chkconfig postgresql-9.5 on

And modify configuration file /var/lib/pgsql/9.5/data/postgresql.conf as following:

listen_addresses = 'your IP, 127.0.0.1'

Next, update /var/lib/pgsql/9.5/data/pg_hba.conf file:

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all your IP md5

Now you are ready to start the service

service postgresql-9.5 start

Replication Configuration

Streaming Replication provides the capability to continuously ship and apply the WAL XLOG (Write-Ahead Log) records to some number of standby servers in order to keep the current. This feature was added to PostgresSQL 9.0.
Ok, so we have two different PostgreSQL instances on different hosts installed. Now we need to create a user named replication with REPLICATION privileges on master host.

Ok, so we have two different PostgreSQL instances on different hosts installed. Now we need to create a user named replication with REPLICATION privileges on master host.

su - postgres
psql
CREATE ROLE replication WITH REPLICATION PASSWORD 'password' LOGIN

Set up connections and authentication on the master host so that the standby (slave host) can successfully connect to the replication pseudo-database on the primary. Edit /var/lib/pgsql/9.5/data/pg_hba.conf

host replication replication slave host IP md5

Now you need to update masters config file /var/lib/pgsql/9.5/data/postgresql.conf as following:

# Add settings for extensions here
max_connections = 200
shared_buffers = 15GB
effective_cache_size = 45GB
work_mem = 39321kB
maintenance_work_mem = 2GB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

#logging settings
log_directory = '/var/log/pg_log95'
log_filename = 'postgresql-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 30000
log_connections = true
tcp_keepalives_idle = 30

# Streaming replication
# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.
max_wal_senders = 5

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode = on
archive_command = 'rsync -a %p postgres@[SLAVE IP]:/db/psql95_wal_archive/%f'

We need to move the data directory on the slave host

mv /var/lib/pgsql/9.5/data /var/lib/pgsql/9.5/data_bkp

Now, you need to make a base backup by copying the master hosts data directory to the slave host. You can do it with pg_basebackup command on slave host.

pg_basebackup -h [MASTER IP] -D /var/lib/pgsql/9.5/data -P -U replication --xlog-method=stream

Then you need to modify /var/lib/pgsql/9.3/data/postgresql.conf on slave host as following

# Add settings for extensions here
max_connections = 200
shared_buffers = 15GB
effective_cache_size = 45GB
work_mem = 39321kB
maintenance_work_mem = 2GB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

#logging settings
log_directory = '/var/log/pg_log93'
log_filename = 'postgresql-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 30000

# Streaming replication
hot_standby = on

Create a recovery command file in the slave host /var/lib/pgsql/9.5/data/recovery.conf. The following parameters are required for streaming replication

# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode = 'on'

# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=[MASTER IP] user=replication password=[PASSWORD]'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/var/lib/pgsql/9.5/data/trigger_file'

# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /db/psql95_wal_archive/%f "%p"'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup /db/psql95_wal_archive/ %r'

Please confirm that on both hosts owner of /var/lib/pgsql/9.5/data and its sub-directories is postgres user. Also, you need to create directory /db/psql93_wal_archive/ with postgres owner on slave host.
And the last step before restarting PostgreSQL instances is to generate RSA key on slave host for postgres user and copy it to master

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa postgres@[MASTER IP]

Now, you are ready to restart postgres service on master first and then on slave.

Conclusion

We have configured Streaming replication that allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they're generated, without waiting for the WAL file to be filled.