Migrating a database from Managed Service for PostgreSQL to Object Storage

You can migrate a database from Yandex Managed Service for PostgreSQL to Yandex Object Storage using Yandex Data Transfer. To do this:

1. Set up the transfer.
2. Activate the transfer.
3. Check the copy function upon re-activation.

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

Getting startedGetting started

Prepare the infrastructure:

Set up the transferSet up the transfer

1. Connect to the Managed Service for PostgreSQL cluster, create a table named 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');
   

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

3. Create a source endpoint and a transfer.

Activate the transferActivate the transfer

1. Activate the transfer and wait for its status to change to Completed.

2. Make sure the Object Storage bucket now contains the public_x_tab.csv table with the x_tab table data.

Check the copy function upon re-activationCheck the copy function upon re-activation

1. Connect to the Managed Service for PostgreSQL cluster and, in the x_tab table, delete the row with the 41 ID and edit the row with the 42 ID:

   DELETE FROM x_tab WHERE id = 41;
   UPDATE x_tab SET name = 'Key3' WHERE id = 42;
   

2. Reactivate the transfer and wait for its status to change to Completed.

3. Make sure you can see the changes in the target public_x_tab.csv table.

Delete the resources you createdDelete 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 target endpoint.

• Delete the transfer, source endpoint, cluster, and bucket:

  Manually

  Terraform

  • Transfer.
  • Source endpoint.
  • Managed Service for PostgreSQL.
  • Object Storage bucket.

  1. In the terminal window, go 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 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 postgresql-to-objstorage.tf configuration file will be deleted.