PostgreSQL Streaming Replication

Introduction

PostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements,

Streaming replication is a popular method you can use to scale your relational databases horizontally. It uses two or more copies of the same database cluster running on separate machines. One database cluster is the primary and serves both read and write operations; the others are the replicas, which help only read operations. You can also use streaming replication to provide the high availability of a system. For example, if the primary database cluster or server were to fail unexpectedly, the replicas can continue serving read operations, or (one of the replicas) become the new primary cluster.

PostgreSQL is a widely used relational database that supports logical and physical replication. Logical replication streams high-level changes from the primary database cluster to the replica databases. You can stream changes to just a single database or table in a database using logical replication. However, in physical replication, changes to the WAL (Write-Ahead-Logging) log file are streamed and replicated in the replica clusters. As a result, you can’t copy specific areas of a primary database cluster, but instead, all changes to the primary are replicated.

Initialize and start PostgreSQL, if not done already on the Master.

# su - postgres
# echo "export PATH=/usr/pgsql-12/bin:$PATH PAGER=less" >> ~/.pgsql_profile
# source ~/.pgsql_profile

As root, initialize and start PostgreSQL Streaming Replication on the Master.

# /usr/pgsql-12/bin/postgresql-12-setup initdb
# systemctl start postgresql-12

Configure Master PostgreSQL Streaming Replication

Then, modify the parameter listen_addresses to allow a specific IP interface or all (using *). Modifying this parameter requires a restart of the PostgreSQL instance to get the change into effect.

# vi /var/lib/pgsql/data/postgresql.conf
# line 59: uncomment and change
listen_addresses = '*'
# line 193: uncomment
wal_level = replica
# line 198: uncomment
synchronous_commit = on
# line 286: uncomment (max number of concurrent connections from streaming clients)
max_wal_senders = 10
# line 288: uncomment and change (minimum number of past log file segments)
wal_keep_segments = 10
# line 300: uncomment and change
synchronous_standby_names = '*'

# vi /var/lib/pgsql/data/pg_hba.conf
# end line: comment out existing lines and all new lines
# host replication [replication user] [allowed network] [authentication method]
#host    replication     all             127.0.0.1/32            ident
#host    replication     all             ::1/128                 ident
host    replication     rep_user        10.0.0.30/32            md5
host    replication     rep_user        10.0.0.51/32            md5

Create replication PostgreSQL

# su - postgres
postgres$ createuser --replication -P rep_user
Enter password for new role:   # set any password
Enter it again:
postgres$ exit
# systemctl restart postgresql

Configure slave PostgreSQL

Then, stop PostgreSQL and remove existing data.

# systemctl stop postgresql
# rm -rf /var/lib/pgsql/data/*

Get data from Master

# su - postgres
postgres$ pg_basebackup -R -h unixcop.com -U rep_user -D /var/lib/pgsql/data -P
Password:   # password of replication user
32655/32655 kB (100%), 1/1 tablespace
postgres$ exit

# vi /var/lib/pgsql/data/postgresql.conf
# line 59: uncomment and change
listen_addresses = '*'
# line 315: uncomment
hot_standby = on
# vi /var/lib/pgsql/data/postgresql.auto.conf
# add [application_name] to auto generated auth file (any name you like, like hostname and so on)
primary_conninfo = 'user=rep_user password=password host=unixcop.com port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=node01'

# systemctl start postgresql

Master Host is like follows. Make sure the setting works normally to create databases or to insert data on Master Host.

postgres$ psql -c "select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;"
 usename  | application_name | client_addr |   state   | sync_priority | sync_state
----------+------------------+-------------+-----------+---------------+------------
 rep_user | node01           | 10.0.0.51   | streaming |             1 | sync
(1 row)

Most of the time, the default or modified retention settings of WAL segments on the Master may not be enough to maintain a healthy replication between itself and its standby. So, we need the WALs to be safely archived to another disk or a remote backup server. Then, the standby can use these archived WAL segments to replay them when the WALs are gone from the Master.