Skip to main content
PostgreSQL logical replication slots
Last update:

PostgreSQL logical replication slots

To continuously replicate data from one database to another (for example, within Selectel cloud databases or to external storage), you can create persistent slots.

The slots provide fault-tolerant logical replication — data will persist when the DBMS is scaled or rebooted.

Restrictions⁠

The maximum number of logical replication slots is 26.

Only a user with the dbaas_replication role can create and use slots — this role is automatically given to database-owner, it cannot be assigned to other users.

Create a logical replication slot⁠

You can create a logical replication slot for the database via Cloud Database API or in the control panel.

  1. In Control Panel, go to Cloud Platform → Databases.
  2. Open the cluster page → Databases tab.
  3. Open the database card.
  4. In the Replication Slots block, click Add.
  5. Enter a slot name or leave the default name.
  6. Click .

Configure logical replication using the slot⁠

Logical replication in PostgreSQL has restrictions.

  1. Create a publication on the source cluster:

    CREATE PUBLICATION <publication_name> FOR TABLE <table_name>;

    Specify:

    • <publication_name> is the name of the publication;
    • <table_name> is the table name.

  2. Optional: if required, add additional tables to the publication:

    ALTER PUBLICATION <publication_name> ADD TABLE <extra_table_name>;

    Specify <extra_table_name> is the name of the table.

  3. Create a logical replication slot for the database in the source cluster.

  4. Create a data schema of all replicated tables on the receiving cluster. Perform a circuit dump on the source cluster using the pg_dump utility:

    pg_dump \
    "host=<host> \
    port=<port> \
    dbname=<database_name> \
    user=<user_name>" \
    --schema-only \
    --no-privileges \
    --no-subscriptions \
    --no-publications \
    -Fd -f <dump_directory>

    Specify:

    • <host> — The IP address or DNS hostname of the source cluster;
    • <port> — port;
    • <database_name> is the name of the database;
    • <user_name> is the name of the database owner;
    • <dump_directory> — directory for the dump.

  5. Restore the circuitry from the dump on the receiving cluster using the pg_restore utility:

    pg_restore \
    -Fd -v \
    --single-transaction -s \
    --no-privileges -O \
    -h <host> \
    -U <user_name> \
    -p <port> \
    -d <database_name> \
    <dump_directory>

    Specify:

    • <host> — The IP address or DNS name of the master host of the receiving cluster;
    • <user_name> is the database username;
    • <database_name> is the name of the database;
    • <port> — port;
    • <dump_directory> — directory with dump.

  6. Create a subscription on the receiving cluster on behalf of the user with the dbaas_replication role:

    CREATE SUBSCRIPTION <subscription_name> CONNECTION
       'host=<host>
       port=<port>
       dbname=<database_name>
       user=<user_name>
       password=<password>
       sslmode=verify-ca'
    PUBLICATION <publication_name>
    WITH (copy_data=true, create_slot=false, enabled=true, slot_name=<logical_slot_name>);

    Specify:

    • <subscription_name> is the name of the subscription;
    • <host> — IP address or DNS name of the source cluster master host;
    • <port> is the port of the source cluster;
    • <user_name> is the database username;
    • <password> is the user's password;
    • <database_name> is the name of the database;
    • <logical_slot_name> is the name of the logical replication slot.

  7. Existing data will appear in the table on the receiving cluster.

    If new data is added to the table on the source cluster, it is automatically replicated.

  8. To stop logical replication, turn off the subscription, unbind the slot, and delete the subscription:

    ALTER SUBSCRIPTION <subscription_name> DISABLE;
    ALTER SUBSCRIPTION <subscription_name> SET (slot_name=NONE);
    DROP SUBSCRIPTION <subscription_name>;