Delivering data from Yandex Managed Service for PostgreSQL to Yandex Object Storage

Written by

Before you begin
Set up the transfer
Activate the transfer
Check the copy function upon re-activation
Delete the resources you created

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 these resources, delete them.

Before you begin

Prepare the infrastructure:

Manually

Using Terraform

Create a source Managed Service for PostgreSQL cluster in any applicable configuration with publicly available hosts and the following settings:
- Database name: db1.
- Username: pg-user.
- Password: <password for the source>.
Make sure that the cluster's security group has been set up correctly and allows connecting to the cluster:
Create a Object Storage bucket.
Create a service account named storage-sa with the storage.uploader role. The transfer will use it to access the bucket.

If you don't have Terraform, install and configure it.
Download the file with provider settings. Place it in a separate working directory and specify the parameter values.
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.
- Target Object Storage bucket.
- Source endpoint.
- Transfer.
In the postgresql-to-objstorage.tf file, specify:
- PostgreSQL user password.
- Bucket name meeting the naming conventions.
Run the command terraform init in the directory with the configuration file. This command initializes the provider specified in the configuration files and enables you to use the provider resources and data sources.
Make sure the Terraform configuration files are correct using the command:
```
terraform validate
```
If there are errors in the configuration files, Terraform will point to them.
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.

Set up the transfer

Connect to the Managed Service for PostgreSQL cluster, create a table called x_tab in the db1 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 the previously created bucket>
- Service account: service-sa.
- Output format: CSV.
- Compression format: UNCOMPRESSED.
- Directory name: from_PostgreSQL.
Create a source endpoint and a transfer.

Manually

Using Terraform

Create a source endpoint of the PostgreSQL type and specify the cluster connection parameters in it:
- Connection type: Managed Service for PostgreSQL cluster.
- Cluster: <PostgreSQL source cluster name> from the drop-down list.
- Database: db1.
- Password: pg-user.
- Password: <user password>.
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 1 to enable transfer creation.
Make sure the Terraform configuration files are correct using the command:
```
terraform validate
```
If there are errors in the configuration files, Terraform will point to them.
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.

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 the x_tab table.

Check the copy function upon re-activation

Connect to the Managed Service for PostgreSQL cluster, delete the row with the ID of 41, and edit the row with the ID 42 in the x_tab table:
```
DELETE FROM x_tab WHERE id = 41;
UPDATE x_tab SET name = 'Key3' WHERE id = 42;
```
Re-activate the transfer and wait for its status to change to Completed.
Make sure that the changes now show up in the public_x_tab.csv file in the target cluster.

Delete the resources you created

If you no longer need these resources, delete them:

Make sure that the transfer is in the Completed status.
Delete the target endpoint.
Delete the transfer, source endpoint, cluster, and bucket:
Manually

Using Terraform
1. In the terminal window, change to the directory containing the infrastructure plan.
2. Delete the postgresql-to-objstorage.tf configuration file.
3. Make sure the Terraform configuration files are correct using the command:
  terraform validate
  If there are errors in the configuration files, Terraform will point to them.
4. Confirm the update of 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:
    
    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.