PostgreSQL Replication Slots

Learn how to use replication slots with PostgreSQL's streaming physical replication. We also discuss their benefits and disadvantages.

This tutorial assumes you have already setup streaming replication between a primary and a replica PostgreSQL server. If you need to set that up first, run through this tutorial.

# Assumes Replication is already setup as follows:
# Primary DB cluster called "main" on port 5432
# Replica DB cluster called "replica" on port 5433

# create replication slot on main
sudo su - postgres
psql -c "select * from pg_create_physical_replication_slot('replica');"
psql -c "select * from pg_replication_slots;"

# Update recovery.conf on replica
nano /var/lib/postgresql/10/replica/recovery.conf

  restore_command = 'cp /var/lib/postgresql/pg_log_archive/replica/%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/replica %r'
  primary_slot_name = 'replica'

# Restart replica to start using the new slot
sudo systemctl restart postgresql@10-replica
tail -n 100 /var/log/postgresql/postgresql-10-replica.log

# check slot status on main
psql -c "select * from pg_replication_slots;"

# insert data on main
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');"

# verify data added to replica
psql test -c "select * from posts;" -p 5433

# Stop main cluster to simulate failure
sudo systemctl stop postgresql@10-main

# promote replica
sudo pg_ctlcluster 10 replica promote

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

# show replica cluster slots (now primary)
psql -c "select * from pg_replication_slots;" -p 5433

# use this command to drop a slot
psql -c "select * from pg_drop_replication_slot('replica');"