[Yandex Cloud documentation](../../index.md) > [Yandex Data Transfer](../index.md) > [Tutorials](index.md) > Uploading data to Object Storage > PostgreSQL to Object Storage

# 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. Proceed as follows:

1. [Set up your transfer](#prepare-transfer).
1. [Activate the transfer](#activate-transfer).
1. [Test copying after reactivation](#example-check-copy).

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


## Required paid resources {#paid-resources}

* Managed Service for PostgreSQL cluster: Computing resources allocated to hosts along with storage and backup capacity (see [Managed Service for PostgreSQL pricing](../../managed-postgresql/pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Object Storage bucket: Use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)).


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

Set up your infrastructure:

{% list tabs group=instructions %}

- Manually {#manual}

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

        {% 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. If using security groups, make sure they are [configured correctly](../../managed-postgresql/operations/connect/index.md#configuring-security-groups) and allow connections to your cluster.


    1. [Create an Yandex Object Storage bucket](../../storage/operations/buckets/create.md).

    
    1. [Create a service account](../../iam/operations/sa/create.md#create-sa) named `storage-sa` with the `storage.uploader` role. The transfer will use it to access the bucket.


- Terraform {#tf}

    1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform).
    1. [Get the authentication credentials](../../tutorials/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](../../tutorials/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](../../tutorials/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-objstorage.tf](https://github.com/yandex-cloud-examples/yc-data-transfer-from-postgresql-to-object-storage/blob/main/postgresql-to-objstorage.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.
        * Source Managed Service for PostgreSQL cluster.
        * Service account for creating and accessing the bucket.
        * Object Storage target bucket.
        * Source endpoint.
        * Transfer.

    1. In the `postgresql-to-objstorage.tf` file, specify the following:
        * PostgreSQL user password.
        * Bucket name consistent with the [naming conventions](../../storage/concepts/bucket.md#naming).

    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](../operations/endpoint/target/object-storage.md) 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`

1. Create a source endpoint and set up the transfer.

{% list tabs group=instructions %}

- Manually {#manual}

    1. [Create a `PostgreSQL`-type source endpoint](../operations/endpoint/source/postgresql.md) and configure it using the following settings:

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

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

- Terraform {#tf}

    1. In the `postgresql-to-objstorage.tf` file, specify these variables:

        * `objstorage_endpoint_id`: Target endpoint ID.
        * `transfer_enabled`: Set to `1` to create a 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.

{% endlist %}

## Activate the transfer {#activate-transfer}

1. [Activate the transfer](../operations/transfer.md#activate) and wait for its status to change to **_Completed_**.

1. Verify that the table `public_x_tab.csv`, containing data from `x_tab`, has appeared in the Object Storage bucket.

## Verify that the copy operation works upon reactivation {#example-check-copy}

1. [Connect to the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/connect/index.md) and perform the following operations on the table `x_tab`: delete the row with ID = `41` and update the row with ID = `42`:

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

1. [Reactivate the transfer](../operations/transfer.md#activate) and wait for its status to change to **_Completed_**.
1. Verify that the changes have been propagated to the target `public_x_tab.csv` table.

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

To reduce the consumption of resources, delete those you do not need:

1. Make sure the transfer status is **_Completed_**.
1. [Delete the target endpoint](../operations/endpoint/index.md#delete).
1. Delete other resources, applying the same method used for their creation:

    {% list tabs group=instructions %}

    - Manually {#manual}

        1. [Delete the transfer](../operations/transfer.md#delete).
        1. [Delete the source endpoint](../operations/endpoint/index.md#delete).
        1. [Delete the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-delete.md).
        1. [Delete the Object Storage bucket](../../storage/operations/buckets/delete.md).

    - 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 %}