Migrating a Yandex Managed Service for PostgreSQL cluster to another version using Data Transfer

You can migrate a loaded production database deployed in a Managed Service for PostgreSQL cluster to a higher version cluster. This tutorial covers migration from version 13 directly to version 17, without going stepwise through multiple versions (13 → 14 → 15 → 16 → 17).

To transfer data:

1. Prepare the source cluster.
2. Prepare the target cluster.
3. Prepare and activate the transfers.
4. Switch to the new cluster.
5. Check the data transfer.

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

Required paid resourcesRequired paid resources

• PostgreSQL cluster, which includes computing resources allocated to hosts, storage and backup size (see PostgreSQL pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Each transfer, which includes the use of computing resources and the number of transferred data rows (see Data Transfer pricing).

Prepare the source clusterPrepare the source cluster

1. Prepare the source database for migration as per this guide.

2. Estimate your database workload. If it exceeds 10,000 writes per second, plan several transfers.

   1. Identify the high-workload tables.
   2. Distribute the tables between several transfers.

Prepare the target clusterPrepare the target cluster

1. Create a Managed Service for PostgreSQL target cluster:

   Manually

   Using Terraform

   Create a Managed Service for PostgreSQL target cluster with the same configuration as the source cluster and with the following settings:

   • Cluster version: 17.
   • Database name: db1.
   • Username: user1.

   If you intend to connect to the cluster from the internet, enable public access to the cluster hosts.

   1. If you do not have Terraform yet, install it.

   2. Get the authentication credentials. You can add them to environment variables or specify them later in the provider configuration file.

   3. Configure and initialize a provider. There is no need to create a provider configuration file manually, you can download it.

   4. Place the configuration file in a separate working directory and specify the parameter values. If you did not add the authentication credentials to environment variables, specify them in the configuration file.

   5. In your current working directory, create a .tf file with the following contents:

      resource "yandex_mdb_postgresql_cluster" "old" { }
      

   6. Write the PostgreSQL version 13 cluster ID to an environment variable:

      export POSTGRESQL_CLUSTER_ID=<cluster_ID>
      

      You can get the cluster ID with the list of clusters in the folder.

   7. Import the PostgreSQL version 13 cluster settings into the Terraform configuration:

      terraform import yandex_mdb_postgresql_cluster.old ${POSTGRESQL_CLUSTER_ID}
      

   8. Get the imported configuration:

      terraform show
      

   9. Copy it from the terminal and paste it into the .tf file.

   10. Place the file in the new imported-cluster directory.

   11. Edit the copied configuration so that you can create a new cluster from it:

       • Specify the new cluster name in the resource string and in the name argument.
       • Under config, set version to 17.
       • Delete created_at, health, id, and status.
       • In the host sections, delete the fqdn, role, and priority arguments.
       • If the disk_size_autoscaling section has disk_size_limit = 0, delete this section.
       • If the maintenance_window section contains type = "ANYTIME", delete the hour argument.
       • Optionally, make further changes if you need to customize the configuration.

   12. Add to the file a resource to create a user named user1:

       resource "yandex_mdb_postgresql_user" "user1" {
         cluster_id = yandex_mdb_postgresql_cluster.<cluster_name>.id
         name       = "user1"
         password   = "<user_password>"
       }
       

       Where <cluster_name> is the new cluster name specified in the yandex_mdb_postgresql_cluster resource.

   13. Add to the file a resource to create the database:

       resource "yandex_mdb_postgresql_database" "db1" {
         cluster_id = yandex_mdb_postgresql_cluster.<cluster_name>.id
         name       = "db1"
         owner      = yandex_mdb_postgresql_user.user1.name
         depends_on = [yandex_mdb_postgresql_user.user1]
       }
       

       Where <cluster_name> is the new cluster name specified in the yandex_mdb_postgresql_cluster resource.

   14. Get the authentication credentials in the imported-cluster directory.

   15. In the same directory, configure and initialize the provider. Download the provider configuration file rather than creating it manually.

   16. Place the configuration file in the imported-cluster directory and specify the parameter values. If you have not set the authentication credentials as environment variables, specify them in the configuration file.

   17. Validate your Terraform configuration:

       terraform validate
       

       Terraform will display any configuration errors detected in your files.

   18. Create the required infrastructure:

       1. Run this command to view the planned changes:

          terraform plan
          

          If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

       2. If everything looks correct, apply the changes:

          1. Run this command:

             terraform apply
             

          2. Confirm updating the resources.

          3. Wait for the operation to complete.

       All the required resources will be created in the specified folder. You can check resource availability and their settings in the management console.

   Timeouts

   The Terraform provider sets the following timeouts for Managed Service for PostgreSQL cluster operations:

   • Creating a cluster, including restoration from a backup: 30 minutes.
   • Updating a cluster: 60 minutes.
   • Deleting a cluster: 15 minutes.

   Operations exceeding the timeout are aborted.

   How can I change these timeouts?

   Add a timeouts section to the cluster description, e.g.:

   resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
     ...
     timeouts {
       create = "1h30m" # 1 hour 30 minutes
       update = "2h"    # 2 hours
       delete = "30m"   # 30 minutes
     }
   }
   

2. If using security groups, make sure they are configured correctly and allow connections to your cluster.

3. Prepare the target database for migration as per this guide.

Prepare and activate the transfersPrepare and activate the transfers

Switch to the new clusterSwitch to the new cluster

1. Wait for the transfer status to change to Replicating.

2. Remove the writing load from the source cluster.

3. On the transfer monitoring page, wait for the Maximum data transfer delay metric to reach zero for each transfer. This indicates that the target cluster now contains all changes made in the source cluster after the data copy completed.

4. Switch the workload to the target cluster.

5. Deactivate the transfers and wait for their status to change to Stopped.

   During deactivation, foreign keys and indexes are being created. This may take a while. The larger your database, the longer the deactivation time.

Check the data transferCheck the data transfer

1. Connect to the db1 database in the Managed Service for PostgreSQL target cluster.

2. Run this query to make sure the tables have appeared in the db1 database:

   SELECT schemaname AS schema, tablename AS table_name
   FROM pg_tables
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema') 
     AND tablename NOT LIKE 'pg\_%'
   ORDER BY schemaname, tablename;
   

   The query will return a list of all non-system tables.

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them: