Migrating PostgreSQL clusters

To migrate your database to Yandex Managed Service for PostgreSQL, you need to directly transfer the data, acquire a write lock for the old database, and switch over the load to the database cluster in Yandex Cloud.

Performing migration with Data Transfer allows you to:

• Eliminate the need for an intermediate VM or public internet access to your Managed Service for PostgreSQL target cluster.
• Minimize the DB downtime when migrating data.
• Migrate from older PostgreSQL versions to newer versions.

For more information, see Problems addressed by Yandex Data Transfer.

To use these migration methods, allow connecting to the source cluster from the internet.

Transferring dataTransferring data

Required paid resourcesRequired paid resources

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

Transfer the dataTransfer the data

1. Prepare the source cluster.

2. Set up the infrastructure:

   Manually

   Terraform

   1. Create a Managed Service for PostgreSQL target cluster with your preferred configuration. For this operation, the following requirements apply:

      • The PostgreSQL version must be the same or higher than the version in the source cluster. Migration to an earlier PostgreSQL version is not supported.
      • When creating a cluster, specify the same database name as in the source cluster.
      • Enable the same PostgreSQL extensions as in the source cluster.

   2. Prepare the target cluster.

   3. Create a source endpoint with the following settings:

      • Database type: PostgreSQL
      • Endpoint parameters → Connection settings: Custom installation

      Configure the source cluster connection settings.

   4. Create a target endpoint with the following settings:

      • Database type: PostgreSQL
      • Endpoint parameters → Connection settings: Managed Service for PostgreSQL cluster

      Specify the target cluster ID.

   5. Create a Snapshot and increment-type transfer configured to use the new endpoints.

   6. Activate the transfer.

      Warning

      Do not make any data schema changes in the source or target cluster during the transfer. For more information, see Working with databases during transfer.

   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. Download the data-transfer-pgsql-mpg.tf configuration file to your current working directory.

      This file describes:

      • Network.
      • Subnet.
      • Security group and the rule allowing cluster connections.
      • Managed Service for PostgreSQL target cluster.
      • Source endpoint.
      • Target endpoint.
      • Transfer.

   6. In the data-transfer-pgsql-mpg.tf file, specify the following:

      • Source endpoint settings.

      • pg-extensions: List of PostgreSQL extensions in the source cluster.

      • Target endpoint settings inherited from the target cluster configuration:

        • target_pgsql_version: PostgreSQL version. This version must be the same or higher than the version in the source cluster.
        • target_user and target_password: Database owner username and password.

   7. Validate your Terraform configuration files using this command:

      terraform validate
      

      Terraform will display any configuration errors detected in your files.

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

      The transfer will activate automatically upon creation.

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

4. Switch the source cluster to read-only.

5. On the transfer monitoring page, wait until the Maximum data transfer delay value drops to zero. This means that all changes made in the source cluster after the initial data copy have been transferred to the target cluster.

6. Deactivate the transfer and wait for its status to change to Stopped.

   For more information about transfer statuses, see Transfer lifecycle.

7. Switch the workload to the target cluster.

8. To reduce the consumption of resources you do not need, delete them:

   Manually created resources

   Resources created with Terraform

   1. Delete the Managed Service for PostgreSQL cluster.
   2. Ensure the transfer is stopped, then delete it.
   3. Delete the source and target endpoints.

   1. In the terminal window, go to the directory containing the infrastructure plan.

      Warning

      Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory.

   2. Delete resources:

      1. Run this command:

         terraform destroy
         

      2. Confirm deleting the resources and wait for the operation to complete.

      All the resources described in the Terraform manifests will be deleted.

Quick data transfer to a new clusterQuick data transfer to a new cluster

After creating a Managed Service for PostgreSQL cluster, you can immediately transfer data from another Managed Service for PostgreSQL cluster or custom database installation into it.

This migration method provides:

• Automatic creation of a transfer and endpoints.
• One-time transfer of the data snapshot from the source to the target.

Required paid resourcesRequired paid resources

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

Transfer the dataTransfer the data

1. Open the Overview page of the new Managed Service for PostgreSQL cluster.

2. At the top of the screen, click Migrate data.

3. Configure the source:

   Custom installation

   Managed Service for PostgreSQL cluster

   • Connecting:

     • Select existing connection: Select this option to use an existing connection.

       • Connection ID: Select the connection in Yandex Connection Manager.

       • Database: Enter the DB name.

     • Create new connection: Select this option to create a new connection.

       • Hosts: Specify the IP address or FQDN of the master host. If hosts have different ports open for connection, you can specify multiple host values in host:port format. In this case, the value of the Port field will be disregarded.

       • Username: Enter a username for connecting to the DB.

       • Password: Enter the password to access the database.

       • Database: Enter the DB name.

       • TLS: Enable this setting to use TLS connection.

         • CA certificate: Add the certificate file contents.

   • Connecting:

     • Select existing connection: Select this option to use an existing connection.

       • Cluster ID: Select the cluster to connect to.

       • Connection ID: Select the connection in Yandex Connection Manager.

       • Database: Specify the DB name in the cluster you selected.

     • Create new connection: Select this option to create a new connection.

       • Cluster ID: Select the cluster to connect to.

       • Username: Enter a username for connecting to the cluster DB.

       • Password: Enter the user password to access the cluster DB.

       • Database: Specify the DB name in the selected cluster.

   Warning

   To use a connection from Connection Manager, the user must have access permissions for this connection of connection-manager.user or higher.

4. Configure the target:

   • Connection ID: Select the connection in Connection Manager.

   • Database: Specify the target cluster’s DB name.

5. Optionally, configure advanced settings:

   • Cleanup policy: Select the method to clean up data in the target database:

     • Drop: Completely delete the tables included in the transfer (default).

     • Truncate: Delete only the data from the tables included in the transfer but keep the schema.

   • Included tables: Only data from the tables listed here will be transferred.

   • Excluded tables: Data from the listed tables is not transferred.

     The lists include the name of the schema that describes the DB contents, structure, and integrity constraints, as well as the table name. Both lists support expressions in the following format:

     • <schema_name>.<table_name>: Full table name.
     • <schema_name>.*: All tables in the specified schema.

     Table names must match this regular expression:

     ^"?[-_a-zA-Z0-9.]+"?\\."?[$-_a-zA-Z0-9.*]+"?$
     

     Double quotes within a table name are not supported. Outer quotes are only used as delimiters and will be deleted when processing paths.

   • Schema migration: Select the DB schema elements to migrate when activating or deactivating a transfer.

6. Click Create to create a transfer and endpoints.

7. Activate the transfer.

8. Wait for the transfer status to change to Completed.

9. To reduce the consumption of resources you do not need, delete them:

   1. Delete the Managed Service for PostgreSQL cluster if you transferred data from it.
   2. Delete the transfer.
   3. Delete the source and target endpoints.

Transferring tables with data types from PostgreSQL extensionsTransferring tables with data types from PostgreSQL extensions

Data Transfer allows you to copy tables, the columns of which contain data types defined in PostgreSQL and tables with derived types, i.e., arrays of these types and composite types with fields of these types. Currently, there is a limitation: the data type must implement binary I/O functions. This means that, for the type of data in the pg_type system view, the typsend and typreceive column values must not be zero.

For example, for the PostGIS extension, columns of the GEOMETRY, GEOMETRY_DUMP, and GEOGRAPHY types can be transferred, while those of the BOX2D and BOX3D types cannot.

See alsoSee also

For other migration methods, see this Yandex Managed Service for PostgreSQL tutorial.