Migrating PostgreSQL clusters
You can migrate your database to Yandex Managed Service for PostgreSQL by doing one of the following:
-
Use quick data transfer to a new cluster, which automatically creates the transfer and endpoints.
During migration, put the old database in read-only mode and, once the migration is complete, switch the workload over to the Yandex Cloud database cluster.
Warning
Users are not transferred automatically to a Managed Service for PostgreSQL cluster. You need to create them again in the new cluster.
Using Data Transfer for migration 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 details, see Problems addressed by Yandex Data Transfer.
To use these migration methods, allow connecting to the source cluster from the internet.
Migrating data via Yandex Data Transfer
Required 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 data
-
Set up the infrastructure:
ManuallyTerraform-
Create a Managed Service for PostgreSQL target cluster with your preferred configuration. Enable the same PostgreSQL extensions as in the source cluster.
-
Create a source endpoint with the following settings:
- Database type:
PostgreSQL. - Endpoint parameters → Connection settings:
Custom installation.
Configure the source cluster connection settings.
- Database type:
-
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.
- Database type:
-
Create a transfer of the Snapshot and increment-type that will use the new endpoints.
-
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.
-
If you do not have Terraform yet, install it.
-
Get the authentication credentials. You can add them to environment variables or specify them later in the provider configuration file.
-
Configure and initialize a provider. Instead of manually creating the provider configuration file, you can download it
. -
Place the configuration file in a separate working directory and specify the parameter values. If you have not added the authentication credentials to environment variables, specify them in the configuration file.
-
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.
-
In the
data-transfer-pgsql-mpg.tffile, specify the following:-
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_userandtarget_password: Database owner username and password.
-
Validate your Terraform configuration files using this command:
terraform validateTerraform will display any configuration errors detected in your files.
-
Create the required infrastructure:
-
Run this command to view the intended changes:
terraform planIf 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.
-
If everything looks correct, apply the changes:
-
Run this command:
terraform apply -
Confirm updating the resources.
-
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 be activated automatically as soon as it is created.
-
-
-
Wait for the transfer status to change to Replicating.
-
Remove the writing load from the source cluster.
-
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.
-
Transfer the workload over to the target cluster.
-
Deactivate the transfer and wait for its status to change to Stopped.
For more information about transfer statuses, see Transfer lifecycle.
-
To minimize resource consumption, delete the resources you no longer need:
Manually created resourcesResources created with Terraform-
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.
-
Delete resources:
-
Run this command:
terraform destroy -
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 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.
To use quick data transfer:
This migration method provides:
- Automatic creation of a transfer and endpoints.
- One-time transfer of the data snapshot from the source to the target.
If you no longer need the resources you created, delete them.
Getting started
Sign up for Yandex Cloud and create a billing account:
- Navigate to the management console
and log in to Yandex Cloud or create a new account. - On the Yandex Cloud Billing
page, make sure you have a billing account linked and it has theACTIVEorTRIAL_ACTIVEstatus. If you do not have a billing account, create one and link a cloud to it.
If you have an active billing account, you can create or select a folder for your infrastructure on the cloud page
Learn more about clouds and folders here.
Required 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 the number of transferred data rows (see Data Transfer pricing).
Transfer the data
-
Open the Overview page of the new Managed Service for PostgreSQL cluster.
-
At the top of the screen, click Migrate data.
-
Configure the source:
Custom installationManaged 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:portformat. 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.useror higher. -
-
Configure the target:
-
Connection ID: Select the connection in Connection Manager.
-
Database: Specify the target cluster’s DB name.
-
-
Optionally, configure advanced settings:
-
Cleanup policy: Select the method to clean up data in the target database:
-
Drop: Delete all tables included in the transfer. This is the default option. -
Truncate: Delete data from the transferred tables, leaving the schema intact.
-
-
Included tables: The system will only transfer data from the tables in this list.
-
Excluded tables: The system will not transfer data from the tables in this list.
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.
-
-
Click Create to create a transfer and endpoints.
-
Activate the transfer.
-
Wait for the transfer status to change to Completed.
Delete the resources you created
To reduce resource usage, delete the resources you no longer need:
- Delete the Managed Service for PostgreSQL cluster if you transferred data from it.
- Delete the transfer.
- Delete the source and target endpoints.
Transferring 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_typetypsend and typreceive column values must not be zero.
For example, for the PostGISGEOMETRYGEOMETRY_DUMPGEOGRAPHYBOX2DBOX3D
Useful links
For other migration methods, see this Yandex Managed Service for PostgreSQL tutorial.