Migrating a database from a third-party PostgreSQL cluster to Managed Service for PostgreSQL

There are three ways to migrate data from a third-party source cluster to a Managed Service for PostgreSQL target cluster:

• Transferring data using Yandex Data Transfer.

  This method allows you to:

  • Go without creating an intermediate VM or granting online access to your Managed Service for PostgreSQL target cluster.
  • Migrate the database completely without interrupting user service.
  • Migrate from older PostgreSQL versions to newer ones, including upgrading your cluster from PostgreSQL version 15 to 16.

  To use this method, allow connecting to the source cluster from the internet.

  For more information, see What tasks Yandex Data Transfer is used for.

• Migrating data using logical replication.

  Logical replication uses the subscriptions mechanism. It allows you to migrate data to the target cluster with minimal downtime.

  Use this method only if, for some reason, it is not possible to migrate data using Yandex Data Transfer.

• Transferring data by creating and restoring a logical dump.

  A logical dump is a file with a set of commands running which one by one you can restore the state of a database. It is created using the pg_dump utility. To ensure that a logical dump is complete, before creating it, switch the source cluster to read-only mode.

  Use this method only if, for some reason, it is not possible to transfer data using any of the above methods.

Transferring data using Yandex Data TransferTransferring data using Yandex Data Transfer

1. Prepare the source cluster.

2. Prepare the infrastructure:

   Manually

   Terraform

   1. Create a Managed Service for PostgreSQL target cluster in any suitable configuration. Note that:

      • The PostgreSQL version must be the same or higher than the version in the source cluster. You cannot perform migration while downgrading PostgreSQL version.
      • 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 parameters:

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

      Specify the parameters for connecting to the source cluster.

   4. Create a target endpoint with the following parameters:

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

      Specify the ID of the target cluster.

   5. Create a transfer of the Snapshot and increment type that will use the created endpoints.

   6. Activate the transfer.

      Warning

      Abstain from making any changes to the data schema in the source and target clusters when the data transfer is running. 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 the same working directory.

      This file describes:

      • Network.
      • Subnet.
      • Security group and the rule required to connect to a cluster.
      • Managed Service for PostgreSQL target cluster.
      • Source endpoint.
      • Target endpoint.
      • Transfer.

   6. Specify the following in data-transfer-pgsql-mpg.tf:

      • Source endpoint parameters.

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

      • Target cluster parameters also used as target endpoint parameters:

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

   7. Make sure the Terraform configuration files are correct using this command:

      terraform validate
      

      If there are any errors in the configuration files, Terraform will point them out.

   8. Create the required infrastructure:

      1. Run the command to view planned changes:

         terraform plan
         

         If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

      2. If you are happy with the planned changes, apply them:

         1. Run the command:

            terraform apply
            

         2. Confirm the update of 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.

      Once created, your transfer will be activated automatically.

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 for the Maximum data transfer delay metric to decrease to zero. This means that all changes that occurred in the source cluster after data copying was completed are 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. Transfer the load to the target cluster.

8. Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

   Manually created resources

   Resources created with Terraform

   • Delete the Managed Service for PostgreSQL cluster.
   • Delete the stopped transfer.
   • Delete endpoints for both the source and target.

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

   2. Delete the data-transfer-pgsql-mpg.tf configuration file.

   3. Make sure the Terraform configuration files are correct using this command:

      terraform validate
      

      If there are any errors in the configuration files, Terraform will point them out.

   4. Confirm updating the resources.

      1. Run the command to view planned changes:

         terraform plan
         

         If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

      2. If you are happy with the planned changes, apply them:

         1. Run the command:

            terraform apply
            

         2. Confirm the update of resources.

         3. Wait for the operation to complete.

      All the resources described in the data-transfer-pgsql-mpg.tf configuration file will be deleted.

Migrating data using logical replicationMigrating data using logical replication

Logical replication is supported as of PostgreSQL version 10. Besides migrating data between the same PostgreSQL versions, logical replication allows you to migrate to newer PostgreSQL versions.

In Managed Service for PostgreSQL clusters, subscriptions can be used by the database owner (a user created together with the cluster) and users with the mdb_admin role for the cluster.

Migration stages:

1. Set up the source cluster.
2. Export the database schema in the source cluster.
3. Restore the database schema in the target cluster.
4. Create a PostgreSQL publication and subscription.
5. Migrate the PostgreSQL sequence after replication.
6. Disable replication and transfer the load.

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

Getting startedGetting started

Create the required resources:

Set up the source clusterSet up the source cluster

1. Specify the required SSL and WAL settings in the postgresql.conf file. On Debian and Ubuntu Linux distributions, the default path to this file is /etc/postgresql/<PostgreSQL_version>/main/postgresql.conf.

   1. We recommend using SSL for migrating data: this will help not only encrypt data, but also compress it. For more information, see SSL Support and Database Connection Control Functions in the PostgreSQL documentation.

      To enable SSL, set the appropriate value in the configuration:

      ssl = on                   # on, off
      

   2. Change the logging level for Write Ahead Log (WAL) to add the information needed for logical replication. To do this, set the value of the wal_level to logical.

      The setting can be changed in postgresql.conf. Find the line with the wal_level setting, comment it out, if required, and set the value to logical:

      wal_level = logical                    # minimal, replica, or logical
      

2. Configure authentication of hosts in the source cluster. To do this, add the cluster hosts to the pg_hba.conf file on Yandex Cloud (on Debian and Ubuntu distributions, it is located at /etc/postgresql/<PostgreSQL_version>/main/pg_hba.conf by default).

   Add lines to allow connecting to the database from the specified hosts:

   • If you use SSL:

     hostssl         all            all             <host_address>      md5
     hostssl         replication    all             <host_address>      md5
     

   • If you do not use SSL:

     host         all            all             <host_address>      md5
     host         replication    all             <host_address>      md5
     

3. If a firewall is enabled in the source cluster, allow incoming connections from the Managed Service for PostgreSQL cluster hosts. For example, for Ubuntu 18:

   sudo ufw allow from <target_cluster_host_address> to any port <port>
   

4. Restart the PostgreSQL service to apply all your settings:

   sudo systemctl restart postgresql
   

5. Check the PostgreSQL status after restarting:

   sudo systemctl status postgresql
   

Export the database schema in the source clusterExport the database schema in the source cluster

Use the pg_dump utility to create a file with the database schema to be applied in the target cluster.

pg_dump -h <IP_address_or_FQDN_of_source_cluster_master_host> \
        -U <username> \
        -p <port> \
        --schema-only \
        --no-privileges \
        --no-subscriptions \
        -d <DB_name> \
        -Fd -f /tmp/db_dump

This export command skips all data associated with privileges and roles to avoid conflicts with the database settings in Yandex Cloud. If your database requires additional users, create them.

Restore the database schema in the target clusterRestore the database schema in the target cluster

Using the pg_restore utility, restore the database schema in the target cluster:

pg_restore -h <IP_address_or_FQDN_of_target_cluster_master_host> \
           -U <username> \
           -p 6432 \
           -Fd -v \
           --single-transaction \
           -s --no-privileges \
           -d <DB_name> /tmp/db_dump

Create a publication and subscriptionCreate a publication and subscription

For logical replication to work, create a publication (a group of logically replicated tables) in the source cluster and a subscription (a description of connection to another database) on the target cluster.

1. On the source cluster, create a publication for all the database tables. When migrating multiple databases, you need to create a separate publication for each of them.

   Note

   You need superuser rights to create publications to all tables, but not to transfer the selected tables. For more information about creating publications, see the PostgreSQL documentation.

   Query:

   CREATE PUBLICATION p_data_migration FOR ALL TABLES;
   

2. On the Managed Service for PostgreSQL cluster host, create a subscription with a connection string to the publication. For more information about creating subscriptions, see the PostgreSQL documentation.

   Request with SSL enabled:

   CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<source_cluster_address> port=<port> user=<username> sslmode=verify-full dbname=<DB_name>' PUBLICATION p_data_migration;
   

   Without SSL:

   CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<source_cluster_address> port=<port> user=<username> sslmode=disable dbname=<DB_name>' PUBLICATION p_data_migration;
   

3. To get the replication status, check the pg_subscription_rel directories. You can get the general replication status in the target cluster using pg_stat_subscription and in the source cluster using pg_stat_replication.

   SELECT * FROM pg_subscription_rel;
   

   First of all, check the srsubstate field. R in this field means that synchronization has ended and the databases are ready to be replicated.

Migrate PostgreSQL sequences after replicationMigrate PostgreSQL sequences after replication

To complete synchronization of the source cluster and the target cluster:

1. Switch the source cluster to read-only mode.

2. Create a dump with PostgreSQL-sequences in the source cluster:

   pg_dump -h <IP_address_or_FQDN_of_source_cluster_master_host> \
           -U <username> \
           -p <port> \
           -d <DB_name> \
           --data-only -t '*.*_seq' > /tmp/seq-data.sql
   

   Take note of the *.*_seq pattern used. If the database you are migrating has sequences that do not match this pattern, enter a different pattern to export them.

   For more information about patterns, see the PostgreSQL documentation.

3. Restore the dump with sequences in the target cluster:

   psql -h <IP_address_or_FQDN_of_target_cluster_master_host> \
        -U <username> \
        -p 6432 \
        -d <DB_name> \
        < /tmp/seq-data.sql
   

Delete the subscription and switch over the loadDelete the subscription and switch over the load

1. Delete the subscription in the target cluster:

   DROP SUBSCRIPTION s_data_migration;
   

2. Transfer the load to the target cluster.

Delete the resources you createdDelete the resources you created

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

Transferring data by creating and restoring a logical dumpTransferring data by creating and restoring a logical dump

Create a dump of the database you need in the source cluster using the pg_dump utility. To restore the dump in the target cluster, use the pg_restore utility.

Migration stages:

1. Create a dump of the database you want to migrate.
2. (Optional) Create a virtual machine in Yandex Cloud and upload the DB dump to it.
3. Restore data from the dump to the target cluster.

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

Getting startedGetting started

Create the required resources:

Create a database dumpCreate a database dump

1. Switch the database to read-only mode.

2. Create a dump using the pg_dump utility. To speed up the process, run it in multithreaded mode by providing the number of available CPU cores in the --jobs argument:

   pg_dump --host=<IP_address_of_FQDN_of_the_source_cluster_master_host> \
           --port=<port> \
           --username=<username> \
           --jobs=<number_of_CPU_cores> \
           --format=d \
           --dbname=<database_name> \
           --file=db_dump
   

(Optional) Upload the dump to a virtual machine in Yandex Cloud(Optional) Upload the dump to a virtual machine in Yandex Cloud

Transfer your data to an intermediate VM in Compute Cloud if:

• Your Managed Service for PostgreSQL cluster is not accessible from the internet.
• Your hardware or connection to the cluster in Yandex Cloud is not very reliable.

The required amount of RAM and processor cores depends on the amount of data to migrate and the required migration speed.

To prepare the virtual machine to restore the dump:

1. In the management console, create a new VM from an Ubuntu 20.04 image on Marketplace. The VM parameters depend on the size of the database you want to migrate. The minimum configuration (1 core, 2 GB RAM, 10 GB disk space) should be sufficient to migrate a database up to 1 GB in size. The larger the database, the more RAM and storage space you need for migration (at least twice the size of the database).

   The virtual machine must be in the same network and availability zone as the PostgreSQL cluster. Additionally, the VM must be assigned a public IP address so that you can load the dump from outside Yandex Cloud.

2. Set up the PostgreSQL apt repository.

3. Install the PostgreSQL client and additional utilities for working with the DBMS:

   sudo apt install postgresql-client-common
   
   # For PostgreSQL 10
   sudo apt install postgresql-client-10
   
   # For PostgreSQL 11
   sudo apt install postgresql-client-11
   
   # For PostgreSQL 12
   sudo apt install postgresql-client-12
   
   # For PostgreSQL 13
   sudo apt install postgresql-client-13
   
   # For PostgreSQL 14
   sudo apt install postgresql-client-14
   

4. Archive the dump:

   tar -cvzf db_dump.tar.gz db_dump
   

5. Move the archive containing the dump to the VM, e.g., by the scp utility:

   scp db_dump.tar.gz <VM_username>@<VM_public_IP>:/db_dump.tar.gz
   

6. Connect to the VM.

7. Unpack the archive with the dump:

   tar -xzf db_dump.tar.gz
   

Restore data from the dump to the target clusterRestore data from the dump to the target cluster

Restore the database dump using the pg_restore utility.

The version of pg_restore must match the pg_dump version, and the major version must be at least as high as on the DB where the dump is deployed.

That is, to restore a dump of PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, and PostgreSQL 14 use pg_restore 10, pg_restore 11, pg_restore 12, pg_restore 13, and pg_restore 14, respectively.

pg_restore --host=<IP_address_of_FQDN_of_target_cluster_master_host> \
           --username=<username> \
           --dbname=<DB_name> \
           --port=6432 \
           --format=d \
           --verbose \
           db_dump \
           --single-transaction \
           --no-privileges

If you only need to restore a single schema, add the --schema=<schema_name> parameter. Without this parameter, the command will only run on behalf of the database owner.

If the restoring is interrupted by errors saying that required rights for creating and updating extensions are missing, remove the --single-transaction parameter from the command. The errors will be ignored in this case:

pg_restore: warning: errors ignored on restore: 3

Make sure the errors only apply to the extensions and check the integrity of your restored data.

Delete the resources you createdDelete the resources you created

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