Skip to main content

DB Setup for Self-Hosted PostgreSQL

PostgreSQL uses write-ahead logging (WAL) to ensure transactional integrity and recoverability. Changes are first written to the WAL before being applied to the database files. Logical replication works by reading and publishing these changes from the WAL in a structured format.

To set up logical replication on a self-hosted PostgreSQL instance:

1. Update the PostgreSQL Configuration​

Update the postgresql.conf file:

  • Set the following parameters:

    • wal_level = logical
    • max_slot_wal_keep_size
      This defines the maximum amount of WAL data a replication slot is allowed to retain on disk.

      Starting with PostgreSQL 13, this parameter helps prevent replication slots from consuming all disk space if a subscriber falls behind.

  • After applying these changes, restart your PostgreSQL instance for them to take effect.

2. Create a Publication​

Connect to your database and run:

CREATE PUBLICATION DubHubPub FOR ALL TABLES;

If you already have a publication that Dubhub should use, you can run:

ALTER PUBLICATION DubHubPub FOR ALL TABLES;

Note: When using FOR ALL TABLES, the database user must have privileges on all current and future tables.
The Dub setup wizard will validate this and, if needed, provide a SQL script to grant the required permissions.

3. Grant Replication Permissions​

Connect to your database and run:

GRANT rds_replication TO dubhub;

Replace dubhub with the name of the user you’ll be using for the connection.

Next Step​

You're now ready to create a Dub using Logical Replication.