[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Migrating data from Managed Service for PostgreSQL to Managed Service for OpenSearch using Data Transfer

# Migrating data from Yandex Managed Service for PostgreSQL to Yandex Managed Service for OpenSearch using Yandex Data Transfer

## Delivering data from Yandex Managed Service for PostgreSQL to Yandex Managed Service for OpenSearch using Yandex Data Transfer

You can migrate a database from Yandex Managed Service for PostgreSQL to Yandex Managed Service for OpenSearch using Yandex Data Transfer. Follow these steps:

1. [Set up your infrastructure](#prepare-infrastructure).
1. [Set up your transfer](#prepare-transfer).
1. [Test the transfer](#verify-transfer).

If you no longer need the resources you created, [delete them](#clear-out).


## Getting started {#before-you-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).

### Required paid resources {#paid-resources}

* Managed Service for PostgreSQL cluster: use of computing resources allocated to hosts, storage and backup size (see [Managed Service for PostgreSQL pricing](../../managed-postgresql/pricing.md)).
* Managed Service for OpenSearch cluster: use of computing resources, storage and backup size (see [Managed Service for OpenSearch pricing](../../managed-opensearch/pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Set up your infrastructure {#prepare-infrastructure}

{% list tabs group=instructions %}

- Manually {#manual}

    {% note info %}
    
    Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster.
    
    {% endnote %}

    1. Create a source Managed Service for PostgreSQL cluster using any suitable [configuration](../../managed-postgresql/concepts/instance-types.md) with publicly accessible hosts. Specify the following settings:
        * **DB name**: `db1`.
        * **Username**: `pg-user`.
        * **Password**: `<source_password>`.

    1. [Create a Managed Service for OpenSearch](../../managed-opensearch/operations/cluster-create.md) target cluster in any suitable configuration with publicly accessible hosts.

    1. [Get an SSL certificate](../../managed-opensearch/operations/connect/index.md#ssl-certificate) for connecting to the Managed Service for OpenSearch target cluster.

    1. Configure security groups for connecting to the [source Managed Service for PostgreSQL](../../managed-postgresql/operations/connect/index.md#configuring-security-groups) and the [target Managed Service for OpenSearch clusters](../../managed-opensearch/operations/connect/index.md#configuring-security-groups).

- Terraform {#tf}

    1. If you do not have Terraform yet, [install it](../infrastructure-management/terraform-quickstart.md#install-terraform).
    1. [Get the authentication credentials](../infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file.
    1. [Configure and initialize a provider](../infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf).
    1. Place the configuration file in a separate working directory and [specify the parameter values](../infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file.

    1. Download the [postgresql-to-opensearch.tf](https://github.com/yandex-cloud-examples/yc-data-transfer-from-postgresql-to-opensearch/blob/main/postgresql-to-opensearch.tf) configuration file to your current working directory.

        This file describes:

        * [Network](../../vpc/concepts/network.md#network).
        * [Subnet](../../vpc/concepts/network.md#subnet).
        * [Security group](../../vpc/concepts/security-groups.md) required for cluster access.
        * Managed Service for PostgreSQL source cluster.
        * Managed Service for OpenSearch target cluster.
        * Source endpoint.
        * Transfer.

    1. In the `postgresql-to-opensearch.tf` file, specify the values of the following variables:

        * `folder_id`: [Folder ID](../../resource-manager/operations/folder/get-id.md).
        * `pg_password`: PostgreSQL user password.
        * `mos_version`: OpenSearch version.
        * `mos_password`: OpenSearch user password.
        * `profile_name`: Name of your CLI profile.

           If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

    1. Validate your Terraform configuration files using this command:

        ```bash
        terraform validate
        ```

        Terraform will display any configuration errors detected in your files.

    1. Create the required infrastructure:

        1. Run this command to view the planned changes:
        
           ```bash
           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.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. 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](https://console.yandex.cloud).

{% endlist %}

## Set up the transfer {#prepare-transfer}

1. [Connect to the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/connect/index.md). In the `db1` database, create a table named `x_tab` and populate it with data:

     ```sql
     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');
     ```

1. [Create a target endpoint](../../data-transfer/operations/endpoint/target/opensearch.md) with the following 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**: `<user_password>`.

1. Create a source endpoint and transfer:

    {% list tabs group=instructions %}

    - Manually {#manual}

      1. [Create a `PostgreSQL`-type source endpoint](../../data-transfer/operations/endpoint/source/postgresql.md) with the following cluster connection settings:

          * **Installation type**: `Managed Service for PostgreSQL cluster`.
          * **Managed Service for PostgreSQL cluster**: `<PostgreSQL_source_cluster_name>` from the drop-down list.
          * **Database**: `db1`.
          * **User**: `pg-user`.
          * **Password**: `<user_password>`.

      1. [Create a transfer](../../data-transfer/operations/transfer.md#create) of the **_Snapshot_**-type that will use the endpoints you created.

      1. [Activate the transfer](../../data-transfer/operations/transfer.md#activate).

    - Terraform {#tf}

      1. In the `postgresql-to-opensearch.tf` file, specify the values of the following variables:

          * `target_endpoint_id`: Target endpoint ID.
          * `transfer_enabled`: Set to `1` to create the transfer.

      1. Validate your Terraform configuration files using this command:

          ```bash
          terraform validate
          ```

          Terraform will display any configuration errors detected in your files.

      1. Create the required infrastructure:

          1. Run this command to view the planned changes:
          
             ```bash
             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.
          
          1. If everything looks correct, apply the changes:
             1. Run this command:
          
                ```bash
                terraform apply
                ```
          
             1. Confirm updating the resources.
             1. Wait for the operation to complete.

          The transfer will be activated automatically upon creation.

    {% endlist %}

## Test the transfer {#verify-transfer}

1. Wait for the transfer status to change to **Completed**.
1. Connect to the target cluster via [OpenSearch Dashboards](../../managed-opensearch/operations/connect/clients.md#dashboards).
1. Select the `Global` tenant.
1. Create a new index template named `public.x_tab`:

    1. Open the management panel by clicking ![os-dashboards-sandwich](../../_assets/console-icons/bars.svg).
    1. Under **Management**, select **Stack Management**.
    1. Navigate to the **Index Patterns** section and click **Create index pattern**.
    1. Specify `public.x_tab` in the **Index pattern name** field and click **Next step**.
    1. Click **Create index pattern**.

1. Open the management panel by clicking ![os-dashboards-sandwich](../../_assets/console-icons/bars.svg).
1. Under **OpenSearch Dashboards**, select **Discover**.
1. The dashboard that opens should contain data from the Managed Service for PostgreSQL database.

## Delete the resources you created {#clear-out}

To minimize resource consumption, delete the resources you no longer need:

1. [Delete the target endpoint](../../data-transfer/operations/endpoint/index.md#delete).
1. Delete the other resources depending on how you created them:

   {% list tabs group=instructions %}

   - Manually {#manual}

       1. [Delete the Managed Service for OpenSearch cluster](../../managed-opensearch/operations/cluster-delete.md).
       1. [Delete the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-delete.md).
       1. [Delete the source endpoint](../../data-transfer/operations/endpoint/index.md#delete).
       1. [Delete the transfer](../../data-transfer/operations/transfer.md#delete).

   - Terraform {#tf}

       1. In the terminal window, go to the directory containing the infrastructure plan.
       
           {% note 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.
       
           {% endnote %}
       
       1. Delete resources:
       
           1. Run this command:
       
               ```bash
               terraform destroy
               ```
       
           1. Confirm deleting the resources and wait for the operation to complete.
       
           All the resources described in the Terraform manifests will be deleted.

   {% endlist %}