Creating a logical replica of Amazon RDS for PostgreSQL in Managed Service for PostgreSQL
If you use Amazon RDS for PostgreSQL, you can create a copy of your data in Managed Service for PostgreSQL.
Settings for Amazon RDS for PostgreSQL
Available for Amazon RDS for PostgreSQL version 10.4 and higher.
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 <user> WITH LOGIN PASSWORD <password>; GRANT rds_replication TO <user>;
-
Grant the
SELECT
privilege to all the replicated tables:GRANT SELECT ON <table>, <table2> TO <user>;
-
Create a publication:
CREATE PUBLICATION pub FOR TABLE <table>, <table2>;
Note
We don't recommend using FOR ALL TABLES
publications as you won't be able to edit the table list later.
-
Add an
inbound
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.
Settings for Managed Service for PostgreSQL
Warning
The hosts must have public access.
-
To create a
subscription
, grant themdb_admin
role to the user:yc managed-postgresql user update {user_name} --grants mdb_admin --cluster-id {cluster_id}
-
On the Managed Service for PostgreSQL cluster host, create a subscription with the Amazon RDS connection string.
CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<Amazon RDS server address> port=<port> user=<username> sslmode=prefer dbname=<database name>' PUBLICATION pub;
Learn more about creating subscriptions in the PostgreSQL documentation.
Using logical replication
-
Changes to the database schema and DDL are not replicated.
First apply the new schema changes on the subscriber side and then on the publication side.
-
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
indexes. Create all the other indexes after the copying is complete. -
If a table doesn't have the
primary key
, you'll get errors like:ERROR: 55000: cannot update table "t2" 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 theprimary key
, changeREPLICA IDENTITY
:alter table t2 replica identity full;
-
In PostgreSQL version 10, the
TRUNCATE
command is not replicated.You can use
DELETE
instead. -
External tables are not replicated.
-
To view errors related to logical replication, see the Managed Service for PostgreSQL logs.
-
If you need to re-create a subscription, delete all data from the tables on the subscriber side to prevent
primary key
restriction errors.