PostgreSQL Streaming Replication

In this tutorial, we demonstrate how to set up a hot standby replica of your PostgreSQL database cluster. This replica can accept read-only queries and be promoted as a new primary.

# Install initial PostgreSQL 10 cluster and verify it exists
sudo pg_lsclusters 

# create a second postgres cluster 
sudo pg_createcluster 10 replica1 
sudo pg_ctlcluster 10 replica1 status 
sudo systemctl status postgresql@10-main 

# create archive directories for both clusters 
sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/main 
sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/replica1 

## Configure Main Cluster (Primary / Master) ################################################### 

# edit configuration file 
sudo nano /etc/postgresql/10/main/postgresql.conf 

  wal_level = replica 
  wal_log_hints = on 
  archive_mode = on # (change requires restart) 
  archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/main/%f' 
  max_wal_senders = 10 
  wal_keep_segments = 64 
  hot_standby = on 

# edit host based access file 
sudo nano /etc/postgresql/10/main/pg_hba.conf 

  local replication rep_user trust # DO NOT USE. Configure your own connection and authentication information 

# create replication user 
sudo -H -u postgres psql -c "CREATE USER rep_user WITH replication;" 

# restart the main cluster 
sudo systemctl restart postgresql@10-main 

## Configure Replica1 Cluster ################################################################### 

# stop replica1 cluster 
sudo systemctl stop postgresql@10-replica1 

# edit configuration file 
sudo nano /etc/postgresql/10/replica1/postgresql.conf 

  wal_level = replica 
  wal_log_hints = on 
  archive_mode = on # (change requires restart) 
  archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/replica1/%f && cp %p /var/lib/postgresql/pg_log_archive/replica1/%f' 
  max_wal_senders = 10 
  wal_keep_segments = 64 
  hot_standby = on 

# edit host based access file 
sudo nano /etc/postgresql/10/replica1/pg_hba.conf 

  local replication rep_user trust 

## Setup Replica1 Cluster Replication ###########################################################

# remove replica1 existing database files 
sudo su - postgres 
rm -rf /var/lib/postgresql/10/replica1 

# sync replica1 with main cluster 
pg_basebackup -D /var/lib/postgresql/10/replica1 -U rep_user -w -P -R # -X stream 

# configure recovery.conf 
nano /var/lib/postgresql/10/replica1/recovery.conf 

  restore_command = 'cp /var/lib/postgresql/pg_log_archive/replica1/%f %p' 
  recovery_target_timeline = 'latest'
  standby_mode = 'on'
  primary_conninfo = 'user=rep_user passfile=''/var/lib/postgresql/.pgpass'' host''/var/run/postgresql'' port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
  archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/pg_log_archive/replica1 %r'

# start replica cluster and verify in sync 
sudo pg_ctlcluster 10 replica1 start 
tail -n 100 /var/log/postgresql/postgresql-10-replica1.log 

## Verify Replica1 Cluster In Sync ############################################################# 

# create database with some data 
sudo su - postgres 
psql -c "create database test;" -p 5432 
psql test -c " 
create table posts ( 
  id integer, 
  title character varying(100), 
  content text, 
  published_at timestamp without time zone, 
  type character varying(100) 
); 

insert into posts (id, title, content, published_at, type) values 
(100, 'Intro to SQL', 'Epic SQL Content', '2018-01-01', 'SQL'), 
(101, 'Intro to PostgreSQL', 'PostgreSQL is awesome!', now(), 'PostgreSQL'); 
"  

# verify data has been replicated on replica1
psql test -c "select * from posts;" -p 5433 

# stop main cluster (simulate failure condition)
sudo systemctl status postgresql@10-main 

# promote replica1 
sudo pg_ctlcluster 10 replica1 promote 

# verify replica1 is now a master / primary cluster 
tail -n 100 /var/log/postgresql/postgresql-10-replica1.log 

psql test -c "insert into posts (id, title, content, type) values 
(102, 'Intro to SQL Where Clause', 'Easy as pie!', 'SQL'), 
(103, 'Intro to SQL Order Clause', 'What comes first?', 'SQL');" -p 5433 

psql test -c "select * from posts;" -p 5433