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.

Using logical replicationUsing 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 the table is replicated, the data in the serial columns or ID columns that is generated using sequences are 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 copy process, only create 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 UPDATE and DELETE replications on tables without the primary key, change the REPLICA IDENTITY:

  ALTER TABLE <table_name> REPLICA IDENTITY FULL;
  

• In PostgreSQL version 10, the TRUNCATE command is not replicated.

  You can use DELETE instead.

• 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. Set the parameter to parameter group of your DB instance:

      rds.logical_replication = 1
      

   2. Restart the cluster to apply the changes.

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

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

3. Grant the SELECT privilege to all the replicated tables:

   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 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, e.g.:

   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. (Optional) Assign the Managed Service for PostgreSQL cluster user the mdb_admin role.

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;
   

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

3. To get the replication status, check the pg_subscription_rel directories.

   SELECT * FROM pg_subscription_rel;
   

   The r value in the srsubstate field means that replication was completed.

Migrate sequencesMigrate sequences

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

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

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 *.*_seq pattern. 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=<target_cluster_master_host_FQDN> \
       --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.