Setting up logical replication as a subscriber in PostgreSQL

The logical replication of databases as a subscriber is available with PostgreSQL 16, which is now supported in Scaleway’s Managed Databases for PostgreSQL.

The feature allows you to replicate data from a non-managed database to a Managed Database for PostgreSQL in real-time, without having to replicate the entire database and keep them in sync, without having to take the original database offline or lock it for an extended period.

Logical replication is especially useful when migrating your databases to Scaleway’s Managed Databases.

By setting up the new database as a subscriber to the original database you can ensure:

• Zero-downtime - Migrate your data without taking the original database offline. Your applications can continue to write data on the original database while the new database is being populated.
• Real-time data synchronization - The subscriber database receives updates in real-time, ensuring that data is consistent across the two databases.
• Flexible migration windows - You can choose when to switch your application to the new database, without having to worry about data consistency. Logical replication ensures that the new database is always up-to-date with the latest changes in the original database.
• Easy rollback - If you encounter issues during migration, such as data corruption and application issues, you can point your application back to the original database. Since the subscriber database constantly keeps a copy of the data in sync with the publisher, you can avoid data loss or inconsistencies.

How to set up the subscriptionLink to this anchor

To be able to create a subscription, you must have the privileges of the pg_create_subscription role, as well as CREATE privileges on the current database.

1. Grant pg_create_subscription and CREATE privileges to your role of choice. In this example we use my_replication_user. Make sure you replace all variables with the information of your databases.

   Note

   You must follow the step below as an admin. Only users with admin rights can grant pg_create_subscription to other users.

   GRANT pg_create_subscription TO my_replication_user;
   GRANT CREATE ON DATABASE my_database TO my_replication_user;
   

2. Create a publication in the original database to define which tables and schemas will be replicated. In the example below we include all tables in the database.

   Tip

   Refer to the official Create Publication PostgreSQL documentation to learn how to detail the specific tables or schemas.

   CREATE PUBLICATION my_publication FOR ALL TABLES;
   

3. Subscribe the subscriber database to the publication created in the previous step. This will create a subscription that connects both databases.

   Important

   Subscriptions in the same database cannot have the same name.

   CREATE SUBSCRIPTION my_subscription
     CONNECTION 'host=publisher_host port=5432 user=my_replication_user password=my_password dbname=my_database'
     PUBLICATION my_publication;
   

4. Run the commands below to respectively grant the user read access to the subscription, and to check its statistics. This helps you make sure the subscription was successful. You can check the number of pending changes and last error message in the output.

   GRANT pg_read_all_stats TO my_replication_user;
   SELECT * FROM pg_stat_subscription;
   

5. Run the command below to get a table containing data about all subscriptions defined in the database. This data includes the subscription ID, name, owner, and whether the subscription is enabled or not.

   SELECT subname, subenabled, subenabled, subslotname, subpublications  FROM pg_catalog.pg_subscription;
   

   Tip

   Refer to the official PostgreSQL documentation for more information on the pg_subscription catalog.

LimitationsLink to this anchor

When creating a subscription from a Scaleway Database Instance to a public IP, the public interface of the Database Instance node is used to connect to the publisher. This might incur in the following consequences:

• If you only have a private endpoint, your Database Instance will still have a public management IP address. The ports are all closed, stopping all connections except the one with the publisher database. All outgoing data is securely encrypted.
• If your Database Instance set-up is composed of more than one node, your IP address is subject to change. In case of failover, for example, the IP address connecting to the publisher database will change. We cannot predict the new IP your nodes might take. However, you can use Scaleway’s Autonomous System to check the number (ASN) of your primary node’s IP address, and look up the IP address allocation for the ASN. This can help you predict the IP address of your failover nodes.
• There is a limit to the number of subscriptions that can be created per Database Instance offer. You can find the table of maximum number of subscriptions per node type below.