Migrating databases from Managed Service for PostgreSQL
A Managed Service for PostgreSQL cluster supports logical replication
Note
If you use older clusters, you can migrate your database by making a dump
This use case describes how to migrate a database from Managed Service for PostgreSQL to a different PostgreSQL cluster using logical replication.
To migrate a database from the Managed Service for PostgreSQL source cluster to the PostgreSQL target cluster:
- Migrate the database schema.
- Configure the user to manage replication on the source cluster.
- Create a publication on the source cluster.
- Create a subscription on the target cluster.
- Monitor the migration process until it is complete.
- Complete your migration.
If you no longer need the resources you created, delete them.
Getting started
-
Make sure all the source cluster's hosts are accessible by a public IP address so that the target cluster can connect to the source. For more information, see Creating PostgreSQL clusters.
-
Install the Managed Service for PostgreSQL client SSL certificates on the hosts of the target cluster to successfully connect to the source cluster that is publicly available.
-
Make sure you can connect to the source cluster's hosts from the target cluster's hosts.
-
Make sure you can connect to the source cluster and the target cluster via SSL.
-
Check that an empty database is created on the target cluster to migrate your data to.
-
Check if there is a user with full access rights to this database in the target cluster.
Migrate the database schema
For logical replication to work properly, both the source and the target must have the same database schema. To migrate the database schema:
-
Create a dump of the source cluster's database schema using the pg_dump
utility:pg_dump "host=<source cluster's host FQDN> port=6432 sslmode=verify-full dbname=<DB name> user=<DB owner username>" --schema-only --no-privileges --no-subscriptions --no-publications -Fd -f <dump directory>
You can obtain the host FQDN with a list of hosts in the cluster.
-
If necessary, create users with the appropriate access rights to the target cluster's database objects.
-
Restore the database schema from the dump on the target cluster using the pg_restore
utility:pg_restore -Fd -v --single-transaction -s --no-privileges -h <target cluster's host FQDN> -U <DB owner username> -p 5432 -d <DB name> <dump directory>
Configure the user to manage replication on the source cluster
PostgreSQL uses the publish-subscribe model for logical replication: the target cluster subscribes to the source cluster's publication to transfer data. To successfully subscribe to a publication, make sure the Managed Service for PostgreSQL source cluster is accessed on behalf of the user who is assigned the logical replication management role. To configure this user:
- Create a user.
- Assign the role
mdb_replication
to this user. - Connect to the database that you want to migrate as the database owner.
- Grant the created user a privilege to perform a
SELECT
on all the DB tables.
After creating a subscription, a connection to the source cluster on the target side will be made on behalf of this user.
Create a publication on the source cluster
-
Connect to the master host and the database to migrate as the database owner.
-
Create a publication that the target cluster will subscribe to:
CREATE PUBLICATION <publication name>;
-
Include all database tables in the created publication:
ALTER PUBLICATION <publication name> ADD TABLE <table name 1>; ... ALTER PUBLICATION <publication name> ADD TABLE <table name N>;
Note
Managed Service for PostgreSQL clusters don't support creating a publication for all tables at once
CREATE PUBLICATION ... FOR ALL TABLES;
, since this requires superuser privileges.
Create a subscription on the target cluster
-
Connect to the master host and the target database as a superuser (such as
postgres
). -
Create a subscription to the source cluster's publication:
CREATE SUBSCRIPTION <subscription name> CONNECTION 'host=<source cluster's host FQDN> port=6432 sslmode=verify-full dbname=<name of the DB to migrate> user=<name of the user to manage replication> password=<user password>' PUBLICATION <publication name>;
This starts the process of migrating data from the source cluster's database to the target cluster's database.
Monitoring the migration process
Track the migration process in the pg_subscription_rel
SELECT * FROM pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
...
We recommend that you monitor the replication status by the srsubstate
field in this directory on the target cluster.
You can get the overall replication status using the pg_stat_subscription
Complete your migration
After the replication is complete:
-
Disable writing data to the migrated database on the source cluster.
-
Transfer sequences, if any, from the source cluster to the target cluster using the pg_dump and psql utilities:
pg_dump "host=<source cluster host FQDN> port=6432 sslmode=verify-full dbname=<DB name> user=<DB owner username>" --data-only -t '*.*_seq' > <name of the file with sequences>
psql -h <target cluster host FQDN> -U <DB owner username> -p 5432 -d <DB name> < <name of the file with sequences>
-
Delete the subscription on the target cluster:
DROP SUBSCRIPTION <subscription name>;
-
Delete the publication on the source cluster:
DROP PUBLICATION <publication name>;
-
Remove the user managing replication on the source cluster.
Delete the resources you created
Delete the resources you no longer need to avoid being charged for them: