Postgres Show Replication Slots: Step-by-Step Guide

Postgres replication slots ensure no WAL data loss during streaming replication. Use 'SHOW replication_slots' to monitor active slots, preventing standby lag. Critical for high-availability setups like failover clusters.

This guide walks through querying, managing, and troubleshooting slots in PostgreSQL 12+ with pgAdmin or psql examples.

Step 1: Connect and Query Slots

SELECT * FROM pg_replication_slots;
Shows slot_name, plugin, slot_type, active, restart_lsn.
  • Run as superuser
  • Key columns: wal_status

Step 2: Interpret Output

Active: true if receiver connected. Wal_status: how far behind.
  • 1) Logical slots (pgoutput)
  • 2) Physical (default)

Step 3: Create a Slot

SELECT pg_create_physical_replication_slot('slot1');
  • --drop: pg_drop_replication_slot('slot1')

Step 4: Monitor and Force Advance

SELECT pg_replication_slot_advance('slot1', restart_lsn);
  • Avoid slot bloat

Step 5: Troubleshooting Common Issues

Inactive slots consume WAL. Drop if unused.
  • Check pg_stat_replication
  • Vacuum WAL

Step 6: Best Practices

Limit slots per DB, use standby feedback.
  • Max 10 slots
  • Logical for CDC

Related Topics