Migrating databases from Managed Service for PostgreSQL

A Managed Service for PostgreSQL cluster supports logical replication. This allows you to use built-in PostgreSQL tools for migrating databases between different PostgreSQL clusters of version 10 and later. Migration across versions is also supported. For example, you can move databases from PostgreSQL ver. 11 to ver. 13.

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:

1. Migrate the database schema.
2. Configure the user to manage replication on the source cluster.
3. Create a publication on the source cluster.
4. Create a subscription on the target cluster.
5. Monitor the migration process until it is complete.
6. Complete your migration.

If you no longer need the resources you created, delete them.

Getting startedGetting started

1. 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 a cluster.

2. 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.

3. If you need to, set up a firewall and security groups so you can connect to the source cluster from the target cluster, as well as to each cluster separately, e.g., using the psql utility.

4. Make sure you can connect to the source cluster's hosts from the target cluster's hosts.

5. Make sure you can connect to the source cluster and the target cluster via SSL.

6. Check that an empty database is created on the target cluster to migrate your data to.

7. Check if there is a user with full access rights to this database in the target cluster.

Migrate the database schemaMigrate 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:

1. Create a dump of the source cluster's database schema using the pg_dump utility:

   pg_dump "host=<FQDN_of_source_cluster_host> 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.

2. If necessary, create users with the appropriate access rights to the target cluster's database objects.

3. 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 <FQDN_of_target_cluster_host> -U <DB_owner_username> -p 5432 -d <DB_name> <dump_directory>
   

Configure the user to manage replication on the source clusterConfigure 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:

1. Create a user.
2. Assign the role mdb_replication to this user.
3. Connect to the database that you want to migrate as the database owner.
4. 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 clusterCreate a publication on the source cluster

1. Connect to the master host and the database to migrate as the database owner.

2. Create a publication that the target cluster will subscribe to:

   CREATE PUBLICATION <publication_name>;
   

3. Include all database tables in the created publication:

   ALTER PUBLICATION <publication_name> ADD TABLE <table_1_name>;
   ...
   ALTER PUBLICATION <publication_name> ADD TABLE <table_N_name>;
   

   Note

   Managed Service for PostgreSQL clusters do not 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 clusterCreate a subscription on the target cluster

1. Connect to the master host and the target database as a superuser (such as postgres).

2. Create a subscription to the source cluster's publication:

   CREATE SUBSCRIPTION <subscription_name> CONNECTION 'host=<FQDN_of_source_cluster_host> port=6432 sslmode=verify-full dbname=<name_of_DB_to_migrate> user=<username_for_replication_management> 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 processMonitoring the migration process

Track the migration process in the pg_subscription_rel directory that shows the replication status:

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 view on the target cluster and the pg_stat_replication view on the source cluster.

Complete your migrationComplete your migration

After the replication is complete:

1. Disable writing data to the migrated database on the source cluster.

2. Transfer sequences, if any, from the source cluster to the target cluster using the pg_dump and psql utilities:

   pg_dump "host=<FQDN_source_cluster_master_host> port=6432 sslmode=verify-full dbname=<DB_name> user=<DB_owner_username>" --data-only -t '*.*_seq' > <name_of_file_with_sequences>
   

   psql -h <FQDN_of_target_cluster_master_host> -U <DB_owner_username> -p 5432 -d <DB_name> < <name_of_file_with_sequences>
   

3. Delete the subscription on the target cluster:

   DROP SUBSCRIPTION <subscription_name>;
   

4. Delete the publication on the source cluster:

   DROP PUBLICATION <publication_name>;
   

5. Remove the user managing replication on the source cluster.

Delete the resources you createdDelete the resources you created

Delete the resources you no longer need to avoid paying for them:

• Delete the virtual machine.
• If you reserved a public static IP for your virtual machine, delete it.
• Delete the Yandex Managed Service for PostgreSQL cluster.