Creating an Amazon RDS logical replica for PostgreSQL in Managed Service for PostgreSQL

You can use logical replication to migrate a database from an Amazon RDS for PostgreSQL source cluster to a Managed Service for PostgreSQL target cluster.

Logical replication uses the subscription mechanism, allowing you to migrate data to the target cluster with minimal downtime. Logical replication is supported in Amazon RDS for PostgreSQL, starting from version 10.4.

Use logical replication if data migration via Yandex Data Transfer is not possible for any reason.

To migrate a database from an Amazon RDS for PostgreSQL source cluster to a Managed Service for PostgreSQL target cluster:

1. Configure Amazon RDS.
2. Configure the target cluster and create a subscription.
3. Migrate sequences.
4. Delete the subscription and switch the workload to the target cluster.

Required paid resourcesRequired paid resources

• Managed Service for PostgreSQL cluster: computing resources allocated to hosts, storage and backup size (see Managed Service for PostgreSQL pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Logical replication specificsLogical replication specifics

• Database schema and DDL changes are not replicated.

  Always apply schema changes on the subscription side first, and only then on the publication side.

• SEQUENCES are not replicated.

  Data replication for a table includes serial columns and identity columns generated by sequences. However, the sequence on the subscriber side will retain its original starting value.

  Therefore, when switching to the subscriber database, update the sequence to the latest value:

  ALTER SEQUENCE serial RESTART WITH <new_value>;
  

• By default, when you create a subscription, the system makes a full copy of data from the source tables.

  To speed up the copy process, create only the PRIMARY KEY first, postponing the creation of all other indexes until the copying is complete.

• If a table has no primary key, replication will produce errors:

  ERROR: 55000: cannot update table "<table_name>" because it does not have a replica identity and publishes updates
  HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
  

  To enable the UPDATE and DELETE replication on tables with no primary key, change the REPLICA IDENTITY:

  ALTER TABLE <table_name> REPLICA IDENTITY FULL;
  

• External tables are not replicated.

• To prevent primary key violations when recreating a subscription, clear the tables in the target cluster first.

• Check Managed Service for PostgreSQL logs for logical replication errors.

Getting startedGetting started

Create the required resources:

Configure Amazon RDSConfigure Amazon RDS

1. Set up logical replication.

   1. In the parameter group section of your database instance, specify the following setting:

      rds.logical_replication = 1
      

   2. Restart the cluster for the changes to take effect.

2. Create a user with the rds_replication role. To do this, execute the following as the user with the rds_superuser role:

   CREATE ROLE <username> WITH LOGIN PASSWORD <password>;
   GRANT rds_replication TO <username>;
   

3. Grant the SELECT privilege on all the tables included in the replication:

   GRANT SELECT ON <table_1>, <table_2>, ..., <table_n> TO <username>;
   

4. Create a publication:

   CREATE PUBLICATION pub FOR TABLE <table_1>, <table_2>, ..., <table_n>;
   

   Note

   We do not recommend using the FOR ALL TABLES publications as it prevents editing the table list in the future.

5. Add an ingress rule to the VPC security groups. For example:

   protocol: tcp, port: 5432, source: 84.201.175.90/32
   

   Where 84.201.175.90 is a public IP address.

Configure the target cluster and create a subscriptionConfigure the target cluster and create a subscription

In Managed Service for PostgreSQL clusters, subscriptions can be used by the database owner, i.e., a user created alongside the cluster, and users with the mdb_admin role for that cluster.

1. Optionally, assign the mdb_admin role to the Managed Service for PostgreSQL cluster user.

2. Create a subscription with the following source cluster’s connection string:

   CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<source_cluster_address> port=<port> user=<username> sslmode=prefer dbname=<DB_name>' PUBLICATION pub;
   

   Tip

   If you cannot create a subscription using this query, add sslrootcert=none to it.

   For details on creating subscriptions, see this PostgreSQL article.

3. To get the replication status, check the pg_subscription_rel folders:

   SELECT * FROM pg_subscription_rel;
   

   r in the srsubstate field indicates that the replication is over.

Migrate sequencesMigrate sequences

To complete synchronization between the source and target clusters:

1. Set the source cluster to read-only mode.

2. Create a database dump including sequences:

   pg_dump --host=<source_cluster_address> \
           --username=<username> \
           --port=<port> \
           --dbname=<DB_name> \
           --data-only \
           --table='*.*_seq' > /tmp/seq-data.sql
   

   Note the *.*_seq pattern we use. If your source database has sequences that do not match this pattern, use a different pattern to export them.

   To learn more about patterns, see this PostgreSQL article.

3. Restore the dump containing database sequences into your target cluster:

   psql \
       --host=<master_host_FQDN_of_target_cluster> \
       --username=<username> \
       --port=6432 \
       --dbname=<DB_name> < /tmp/seq-data.sql
   

Delete the subscription and switch the workload to the target cluster.Delete the subscription and switch the workload to the target cluster.

1. Delete the subscription in the target cluster:

   DROP SUBSCRIPTION s_data_migration;
   

2. Switch the workload to the target cluster.