Copying data from Managed Service for OpenSearch to Yandex MPP Analytics for PostgreSQL using Yandex Data Transfer

With Data Transfer, you can transfer data from a Managed Service for OpenSearch source cluster to a Yandex MPP Analytics for PostgreSQL target cluster.

To transfer data:

1. Get your cloud ready.
2. Set up your infrastructure.
3. Prepare the test data.
4. Set up and activate the transfer.
5. Test your transfer.

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

Get your cloud readyGet your cloud ready

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. 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 navigate to the cloud page to create or select a folder for your infrastructure.

Learn more about clouds and folders here.

Required paid resourcesRequired paid resources

The infrastructure support costs include:

• Fee for Managed Service for OpenSearch cluster computing resources and storage volume (see Managed Service for OpenSearch pricing).
• Fee for Yandex MPP Analytics for PostgreSQL cluster computing resources, storage volume, and backups (see Yandex MPP Analytics for PostgreSQL pricing).

Set up the infrastructureSet up the infrastructure

Prepare your test dataPrepare your test data

1. In the source cluster, create a test index named people and define its mapping:

   curl --cacert ~/.opensearch/root.crt \
        --user <source_cluster_username>:<user_password_in_source_cluster> \
        --header 'Content-Type: application/json' \
        --request PUT 'https://<address_of_OpenSearch_host_with_DATA_role>:9200/people' && \
   curl --cacert ~/.opensearch/root.crt \
        --user <source_cluster_username>:<user_password_in_source_cluster> \
        --header 'Content-Type: application/json' \
        --request PUT 'https://<address_of_OpenSearch_host_with_DATA_role>:9200/people/_mapping?pretty' -d'
        {
              "properties": {
                 "name": {"type": "text"},
                 "age": {"type": "integer"}
              }
        }
        '
   

2. Populate the test index with data:

   curl --cacert ~/.opensearch/root.crt \
        --user <source_cluster_username>:<user_password_in_source_cluster> \
        --header 'Content-Type: application/json' \
        --request POST 'https://<address_of_OpenSearch_host_with_DATA_role>:9200/people/_doc/?pretty' -d'
        {
              "name": "Alice",
              "age": "30"
        }
        ' && \
   curl --cacert ~/.opensearch/root.crt \
        --user <source_cluster_username>:<user_password_in_source_cluster> \
        --header 'Content-Type: application/json' \
        --request POST 'https://<address_of_OpenSearch_host_with_DATA_role>:9200/people/_doc/?pretty' -d'
        {
              "name": "Robert",
              "age": "32"
        }
        '
   

3. Make sure the data is saved in the test index:

   curl --cacert ~/.opensearch/root.crt \
        --user <username_in_target_cluster>:<user_password_in_target_cluster> \
        --header 'Content-Type: application/json' \
        --request GET 'https://<address_of_OpenSearch_host_with_DATA_role>:9200/people/_search?pretty'
   

Set up and activate the transferSet up and activate the transfer

1. Create a source endpoint for your pre-configured Managed Service for OpenSearch cluster using the following settings:

   • Database type: OpenSearch.
   • Connection settings:
     • Connection type: Managed Service for OpenSearch cluster.
     • Managed Service for OpenSearch cluster: Select your Managed Service for OpenSearch cluster from the list.
     • User: admin.
     • Password: admin password.

2. Create a target endpoint for the Yandex MPP Analytics for PostgreSQL cluster you created earlier, with the following settings:

   • Database type: Greenplum.
   • Endpoint parameters:
     • Connection type: Select Managed Service for Greenplum cluster.
     • Managed Service for Greenplum cluster: Select your Yandex MPP Analytics for PostgreSQL cluster from the list.
     • Database: postgres.
     • User: Enter the Yandex MPP Analytics for PostgreSQL cluster username.
     • Password: Enter the Yandex MPP Analytics for PostgreSQL cluster user password.

3. Create a transfer:

   Manually

   Terraform

   1. Create a Snapshot-type transfer and configure it to use the previously created endpoints.
   2. Activate the transfer.

   1. In the opensearch-to-greenplum.tf file, specify the following settings:

      • source_endpoint_id: Source endpoint ID.
      • target_endpoint_id: Target endpoint ID.
      • transfer_enabled: 1 to create a transfer.

   2. Validate your Terraform configuration files using this command:

      terraform validate
      

      Terraform will display any configuration errors detected in your files.

   3. Create the required infrastructure:

      1. Run this command to view the planned changes:

         terraform plan
         

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

      2. If everything looks correct, apply the changes:

         1. Run this command:

            terraform apply
            

         2. Confirm updating the resources.

         3. Wait for the operation to complete.

      The transfer will activate automatically upon creation.

Test your transferTest your transfer

1. Wait for the transfer status to change to Completed.

2. Make sure the data from the source Managed Service for OpenSearch cluster has been migrated to the Yandex MPP Analytics for PostgreSQL cluster:

   1. Obtain an SSL certificate for secure access to the Yandex MPP Analytics for PostgreSQL cluster.

   2. Install the dependencies:

      sudo apt update && sudo apt install --yes postgresql-client
      

   3. Connect to the database in the Yandex MPP Analytics for PostgreSQL cluster.

   4. Verify that the database contains the people table populated with test data:

      SELECT * FROM people;
      

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: