PostgreSQL Replication Slots
Get New Episodes Weekly!
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');"