Creating a logical replica of Amazon RDS 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. It allows you to migrate data to the target cluster with minimal downtime. Logical replication is available in Amazon RDS for PostgreSQL version 10.4 and higher.

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

To migrate a database from an Amazon RDS source cluster for PostgreSQL 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 transfer the load to the target cluster.

Required paid resourcesRequired paid resources

The support cost for this solution includes:

• Managed Service for PostgreSQL cluster fee: Using computing resources allocated to hosts and disk space (see Managed Service for PostgreSQL pricing).
• Fee for using public IP addresses for cluster hosts (see Virtual Private Cloud pricing).

Features of using logical replicationFeatures of using logical replication

• Changes to the database schema and DDL are not replicated.

  First apply the new schema changes on the subscription side and then on the publication side.

• Sequences (SEQUENCES) are not replicated.

  When a table is replicated, the data in the serial columns or ID columns generated by sequences is also replicated. However, the sequence on the subscriber side will preserve its starting value.

  If you switch to the subscriber database, restart the sequence using the latest value:

  ALTER SEQUENCE serial RESTART WITH <new_value>;
  

• When you create a subscription, data from the source tables is fully copied.

  To accelerate the copying process, create only the primary key (PRIMARY KEY). Create all the other indexes after the copying is complete.

• If a table does not have a primary key, errors will occur during replication:

  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 run the UPDATE and DELETE replications in tables without the primary key, change REPLICA IDENTITY:

  ALTER TABLE <table_name> REPLICA IDENTITY FULL;
  

• External tables are not replicated.

• If you need to recreate the subscription, clear the tables in the target cluster to prevent primary key constraint errors.

• To view errors relating to logical replication, see the Managed Service for PostgreSQL logs.

Getting startedGetting started

Create the required resources:

Configure Amazon RDSConfigure Amazon RDS

1. Set up logical replication.

   1. In the parameter group of your DB instance, set the parameter as follows:

      rds.logical_replication = 1
      

   2. Restart the cluster to apply the changes.

2. Create a separate user with the rds_replication role. To do this, execute the following on behalf of the user with the rds_superuser role:

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

3. Grant the SELECT privilege for all the tables involved 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 you will not be able to edit the table list later.

5. Add a rule for incoming traffic in VPC security groups. Here is an 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 (a user created together with the cluster) and users with the mdb_admin role for the cluster.

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

2. Create a subscription with the source cluster 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 request, add the sslrootcert=none parameter to it.

   To learn more about creating subscriptions, see the PostgreSQL documentation.

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

   SELECT * FROM pg_subscription_rel;
   

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

Migrate sequencesMigrate sequences

To complete synchronization of the source cluster and the target cluster:

1. Switch the source cluster to read-only.

2. Create a dump with sequences:

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

   Pay attention to the used pattern, *.*_seq. If the database you are migrating has sequences that do not match this pattern, enter a different pattern to export them.

   For more information about patterns, see the PostgreSQL documentation.

3. Restore the dump with sequences in the 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 transfer the load to the target clusterDelete the subscription and transfer the load to the target cluster

1. Delete the subscription in the target cluster:

   DROP SUBSCRIPTION s_data_migration;
   

2. Transfer the load to the target cluster.