Setting up logical replication as a publisher in PostgreSQL

Reviewed on August 25, 2025

The logical replication of databases as a publisher is available with PostgreSQL 17, which is supported in Scaleway's Managed Databases for PostgreSQL.

The feature allows you to replicate data from a managed PostgreSQL to another Database Instance (a Managed PostgreSQL in another region, for example).

Replication slots can be created with the failover flag, which ensures that replication slots survive failovers. When a secondary node is promoted to primary, the replication slot is automatically available on the new main node.

Before you start

To complete the actions presented below, you must have:

• A Scaleway account logged into the console
• Owner status or IAM permissions allowing you to perform actions in the intended Organization
• A Database Instance running PostgreSQL 17

Configuring advanced settings and user rights

As the service uses physical replication internally, some advanced settings and parameters must be enabled to allow the logical replication to work properly with our system, for standalone and High Availability clusters.

Follow the steps in the How to configure advanced settings documentation page to configure the following settings:

To enable logical replication, you must:

• Enable the rdb.enable_logical_replication setting. This automatically changes the wal_level settings to logical.
• Set hot_standby_feedback to ON
• Set sync_replication_slots to ON- replication slots is an important part of the logical replication. Each subscription receives changes via one replication slot created on the "publishing" node. You have one replication slot available per logical database.

To use replication, users must also have the corresponding rights.

Follow the steps in the How to manage users documentation page to attribute user rights.

To create publications and replication slots, the replication_user must:

• Either be an member of admin user role (_rdb_admin)
• Or be granted the following rights from a role member of the_rdb_admin role:
  • ALTER replication_user with REPLICATION
  • GRANT CREATE ON DATABASE my_database TO my_replication_user;

Setting up logical replication

We use an example scenario to show how logical replication can be set up.

1. Create the table and publication on the publisher database.

   CREATE TABLE mytable (id INTEGER PRIMARY KEY, val TEXT);
   CREATE PUBLICATION pub FOR TABLE mytable;

2. Insert a sample row into the table. This ensures that there is data to test the replication with.

   INSERT INTO mytable VALUES (1, 'foo');

3. Create the same table structure on the subscriber database. Leave the table empty, as data will be copied into it upon subscription.

   CREATE TABLE mytable (id INTEGER PRIMARY KEY, val TEXT);

4. Create a subscription. Two methods are available:

   • Direct subscription with failover - use this method if your environment supports the failover option in CREATE SUBSCRIPTION
   • Creating a replication slot manually on the publisher -

5. Query the publisher table in the subscriber to test the replication.

   SELECT * FROM mytable;

Limitations

• 100 replication slots are available for each Database Instance.

• 25 out of the 100 slots are reserved for Scaleway's internal systems.

• During the synchronization phase, 2 or more slots might be needed for a subscription.

  Important

  • If you need more than 50 replication slots, create a support ticket.
  • Exceeding this limit may cause the Instance's internal replication to fail. High Availability and cluster operations may also become degraded or not functional. In extreme cases, service availability may be affected.
  Tip

  Run the following command to get a list of your replication slots:

  SELECT * FROM pg_replication_slots where slot_type = 'logical';

  And the following command to check publications:

  SELECT * from pg_publication;

• If a logical replication is not active (not consumed by a subscription or other tool), PostgreSQL WAL files will accumulate in the primary node, filling up storage. In the worst case scenario, your database can be set to read only mode (check disk full section) or unavailable.

  Tip

  • We recommend you delete any replication slots that are no longer used.
  • If a logical replication is inactive for 24h, it will be automatically removed.