Loading data from PostgreSQL to Yandex Object Storage
You can migrate a database from Yandex Managed Service for PostgreSQL to Yandex Object Storage using Yandex Data Transfer. To do this:
If you no longer need the resources you created, delete them.
Getting started
Prepare the infrastructure:
-
Create a source Managed Service for PostgreSQL cluster in any applicable configuration with publicly available hosts and the following settings:
- DB name:
db1
- Username:
pg-user
- Password:
<source_password>
- DB name:
-
If using security groups in your cluster, make sure they are configured correctly and allow connecting to the cluster.
-
Create a service account named
storage-sa
with thestorage.uploader
role. The transfer will use it to access the bucket.
-
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. There is no need to create a provider configuration file manually, you can download it
. -
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.
-
Download the postgresql-to-objstorage.tf
configuration file to the same working directory.This file describes:
- Network.
- Subnet.
- Security group required to connect to a cluster.
- Managed Service for PostgreSQL source cluster.
- Service account to be used to create and access the bucket.
- Object Storage target bucket.
- Source endpoint.
- Transfer.
-
In the
postgresql-to-objstorage.tf
file, specify:- PostgreSQL user password.
- Bucket name consistent with the naming conventions.
-
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.
-
Create the required infrastructure:
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of 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
. -
Set up the transfer
-
Connect to the Managed Service for PostgreSQL cluster, create a table called
x_tab
in thedb1
database, and populate it with data:CREATE TABLE x_tab ( id NUMERIC PRIMARY KEY, name CHAR(5) ); INSERT INTO x_tab (id, name) VALUES (40, 'User1'), (41, 'User2'), (42, 'User3'), (43, 'User4'), (44, 'User5');
-
Create a target endpoint of the
Object Storage
type with the following settings:- Bucket:
<name_of_previously_created_bucket>
- Service account:
storage-sa
- Serialization format:
CSV
- Encoding format:
UNCOMPRESSED
- Folder name:
from_PostgreSQL
- Bucket:
-
Create a source endpoint and a transfer.
-
Create a source endpoint of the
PostgreSQL
type and specify the cluster connection parameters in it:- Installation type:
Managed Service for PostgreSQL cluster
- Managed Service for PostgreSQL cluster:
<name_of_PostgreSQL_source_cluster>
from the drop-down list - Database:
db1
- User:
pg-user
- Password:
<user_password>
- Installation type:
-
Create a transfer of the Snapshot type that will use the created endpoints.
-
In the
postgresql-to-objstorage.tf
file, specify the variables:objstorage_endpoint_id
: ID of the target endpoint.transfer_enabled
: Set to1
to enable transfer creation.
-
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.
-
Create the required infrastructure:
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Activate the transfer
-
Activate the transfer and wait for its status to change to Completed.
-
Make sure that the Object Storage bucket now includes the
public_x_tab.csv
file with the data from thex_tab
table.
Check the copy function upon re-activation
-
Connect to the Managed Service for PostgreSQL cluster and, in the
x_tab
table, delete the row where ID equals41
, and edit the row where ID is42
:DELETE FROM x_tab WHERE id = 41; UPDATE x_tab SET name = 'Key3' WHERE id = 42;
-
Reactivate the transfer and wait for its status to change to Completed.
-
Make sure the changes are now reflected in the
public_x_tab.csv
file in the target cluster.
Delete the resources you created
Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:
-
Make sure the transfer status is Completed.
-
Delete the transfer, source endpoint, cluster, and bucket:
ManuallyTerraform-
In the terminal window, go to the directory containing the infrastructure plan.
-
Delete the
postgresql-to-objstorage.tf
configuration file. -
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.
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
All the resources described in the
postgresql-to-objstorage.tf
configuration file will be deleted. -
-