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
To migrate a database from an Amazon RDS source cluster for PostgreSQL to a Managed Service for PostgreSQL target cluster:
- Configure Amazon RDS.
- Configure the target cluster and create a subscription.
- Migrate sequences.
- Delete the subscription and switch over the load to the target cluster.
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 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
andDELETE
replications on tables without the primary key, change theREPLICA 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 started
Create the required resources:
Create a Managed Service for PostgreSQL cluster with public host access. In which case:
- The PostgreSQL version must be the same or higher than the version in the source cluster. You cannot perform migration while downgrading PostgreSQL version.
- The name of the database must be the same as in the source cluster.
- Enable the same PostgreSQL extensions as in the source database.
Configure Amazon RDS
Warning
The DB instance must have public access: Public accessibility = yes
.
-
Set up logical replication.
-
Set the parameter to
parameter group
of your DB instance:rds.logical_replication = 1
-
Restart the cluster to apply the changes.
-
-
Create a separate user with the role
rds_replication
. To do this, execute the following statements on behalf of the user with therds_superuser
role:CREATE ROLE <username> WITH LOGIN PASSWORD <passport>; GRANT rds_replication TO <username>;
-
Grant the
SELECT
privilege to all the replicated tables:GRANT SELECT ON <table_1>, <table_2>, ..., <table_n> TO <username>;
-
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. -
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 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.
-
(Optional) Assign the Managed Service for PostgreSQL cluster user the
mdb_admin
role. -
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=<database name>' PUBLICATION pub;
To learn more about creating subscriptions, see the PostgreSQL documentation
. -
To get the replication status, check the
pg_subscription_rel
directories.SELECT * FROM pg_subscription_rel;
The
r
value in thesrsubstate
field means that replication was completed.
Migrate sequences
To complete synchronization of the source cluster and the target cluster:
-
Switch the source cluster to
read-only
mode. -
Create a dump with sequences:
pg_dump --host=<source cluster address> \ --username=<username> \ --port=<port> \ --dbname=<database 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
. -
Restore the dump with sequences in the target cluster:
psql \ --host=<FQDN of the target cluster master host> \ --username=<username> \ --port=6432 \ --dbname=<database name> < /tmp/seq-data.sql
Delete the subscription and switch over the load to the target cluster
-
Delete the subscription in the target cluster:
DROP SUBSCRIPTION s_data_migration;
-
Switch over the load to the target cluster.