PostgreSQL Setup

Configure your PostgreSQL database for logical replication with scry-backfill.

Prerequisites Checklist

Before scry-backfill can replicate data from your PostgreSQL database, ensure the following requirements are met.

  • PostgreSQL 10 or later - Logical replication requires PostgreSQL 10+
  • wal_level = logical - WAL must include logical decoding information
  • max_replication_slots >= 1 - At least one slot for scry-backfill
  • max_wal_senders >= 1 - At least one WAL sender process
  • User with REPLICATION privilege - Required for creating replication slots
  • User with SELECT on tables to replicate - Required for initial snapshot

1. Enable Logical Replication

PostgreSQL must be configured to output logical decoding information in the write-ahead log (WAL).

postgresql.conf Settings

Add or modify these settings in your postgresql.conf file:

# Required for logical replication
wal_level = logical

# At least 1 for scry-backfill (recommend 4+ for production)
max_replication_slots = 4

# At least 1 for scry-backfill (recommend 4+ for production)
max_wal_senders = 4

# Optional: useful for large transactions
max_worker_processes = 8

Restart Required: Changes to wal_level, max_replication_slots, and max_wal_senders require a PostgreSQL restart to take effect.

Verify Configuration

After restarting PostgreSQL, verify the settings:

SHOW wal_level;
-- Should return: logical

SHOW max_replication_slots;
-- Should return: 4 (or your configured value)

SHOW max_wal_senders;
-- Should return: 4 (or your configured value)

Cloud Databases (RDS, Cloud SQL, Azure): Managed PostgreSQL services typically expose these settings through their own configuration interfaces. For AWS RDS, use parameter groups. For Google Cloud SQL, use database flags. For Azure Database for PostgreSQL, use server parameters. Consult your cloud provider's documentation for specific instructions.

2. Create Replication User

Create a dedicated user for scry-backfill with the minimum required privileges.

Create User with REPLICATION

-- Create the replication user
CREATE USER scry_replication WITH REPLICATION LOGIN PASSWORD 'your-secure-password';

-- Grant connect to the database
GRANT CONNECT ON DATABASE your_database TO scry_replication;

Grant Schema Permissions

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO scry_replication;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO scry_replication;

-- Grant SELECT on future tables (important!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO scry_replication;

Multiple Schemas Example

If your database uses multiple schemas, repeat the grants for each:

-- For each schema you want to replicate
DO $$
DECLARE
    schema_name TEXT;
BEGIN
    FOR schema_name IN SELECT unnest(ARRAY['public', 'app', 'analytics'])
    LOOP
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO scry_replication', schema_name);
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO scry_replication', schema_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO scry_replication', schema_name);
    END LOOP;
END $$;

3. Configure pg_hba.conf

The pg_hba.conf file controls client authentication. Add entries to allow the replication user to connect.

# TYPE  DATABASE        USER                ADDRESS         METHOD

# Allow replication connections from scry-backfill host
host    your_database   scry_replication    10.0.0.0/8      scram-sha-256

# For replication protocol connections (if using physical replication features)
host    replication     scry_replication    10.0.0.0/8      scram-sha-256

# For local development (adjust as needed)
host    your_database   scry_replication    127.0.0.1/32    scram-sha-256

Reload Required: After modifying pg_hba.conf, reload the configuration with SELECT pg_reload_conf(); or pg_ctl reload. No restart is needed.

4. Replication Slots

Replication slots ensure PostgreSQL retains WAL segments until they've been consumed by scry-backfill. scry-backfill creates slots automatically, but you can also manage them manually.

View Existing Slots

SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

Create Slot Manually (Optional)

-- scry-backfill creates this automatically, but you can pre-create it
SELECT pg_create_logical_replication_slot('scry_backfill_slot', 'pgoutput');

Drop Slot (Cleanup)

-- Only do this when you're sure you no longer need the slot
SELECT pg_drop_replication_slot('scry_backfill_slot');

WAL Retention Warning: Inactive replication slots prevent WAL from being recycled, which can cause disk space exhaustion. If scry-backfill is stopped for an extended period, either drop the slot or ensure you have sufficient disk space. Monitor with pg_replication_slots and check the active column.

5. Publications

Publications define which tables are included in logical replication. scry-backfill can create publications automatically, or you can configure them manually for more control.

Publish All Tables

-- Replicate all tables in the database
CREATE PUBLICATION scry_publication FOR ALL TABLES;

Publish Specific Tables

-- Replicate only specific tables
CREATE PUBLICATION scry_publication FOR TABLE
    users,
    orders,
    products,
    inventory;

View Publications

-- List all publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

-- List tables in a publication
SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'scry_publication';

Verify Setup

Use the scry-backfill connection checker to verify your PostgreSQL configuration is correct.

scry-backfill check-connections

This command verifies:

  • Connection to PostgreSQL with provided credentials
  • wal_level is set to logical
  • Sufficient max_replication_slots available
  • Sufficient max_wal_senders available
  • User has REPLICATION privilege
  • User has SELECT permission on target tables
  • Publication exists or can be created

Tip: Run check-connections before starting replication to catch configuration issues early. It provides detailed error messages and suggestions for any problems found.

Ready to Start Replicating?

Get early access to scry-backfill and start creating shadow databases for migration testing.

Request Early Access