[Yandex Cloud documentation](../../index.md) > [Yandex Data Transfer](../index.md) > [Tutorials](index.md) > Uploading data to data marts > PostgreSQL in ClickHouse®

# Loading data from PostgreSQL to a ClickHouse® data mart


You can migrate a database from PostgreSQL to ClickHouse® using Yandex Data Transfer. Proceed as follows:

1. [Prepare and activate your transfer](#prepare-transfer).
1. [Test replication](#example-check-replication).
1. [Query data in the target system](#working-with-data-ch).

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, storage and backup size (see [Managed Service for PostgreSQL pricing](../../managed-postgresql/pricing.md)).
* Managed Service for ClickHouse® cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Each transfer: use of computing resources and the number of transferred data rows (see [Data Transfer pricing](../pricing.md)).


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

In our example, we will create all required resources in Yandex Cloud. Set up your 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 ClickHouse® target cluster using any suitable [configuration](../../managed-clickhouse/concepts/instance-types.md) with publicly accessible hosts. Specify the following settings:
      * **Number of ClickHouse® hosts**: Minimum of 2 to enable replication within the cluster.
      * **DB name**: `db1`.
      * **Username**: `ch-user`.
      * **Password**: `<target_password>`.

  
  1. If using security groups, make sure they are configured correctly and allow inbound connections to the clusters.

     * [Managed Service for ClickHouse®](../../managed-clickhouse/operations/connect/index.md#configuring-security-groups).
     * [Managed Service for PostgreSQL](../../managed-postgresql/operations/connect/index.md#configuring-security-groups).


  1. [Grant](../../managed-postgresql/operations/grant.md#grant-privilege) the `mdb_replication` role to `pg-user` in the Managed Service for PostgreSQL cluster.

- 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-clickhouse.tf](https://github.com/yandex-cloud-examples/yc-data-transfer-from-postgresql-to-clickhouse/blob/main/postgresql-to-clickhouse.tf) configuration file to your current working directory.

        This file describes:

        * [Networks](../../vpc/concepts/network.md#network).
        * [Subnets](../../vpc/concepts/network.md#subnet).
        * [Security groups](../../vpc/concepts/security-groups.md) for cluster connectivity.
        * Managed Service for PostgreSQL source cluster.
        * Managed Service for ClickHouse® target cluster.
        * Source endpoint.
        * Target endpoint.
        * Transfer.

    1. In the `postgresql-to-clickhouse.tf` file, specify admin passwords for PostgreSQL and ClickHouse®.
    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 %}

## Prepare and activate a transfer {#prepare-transfer}

1. [Connect to the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/connect/index.md).
1. 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)
     );
     CREATE INDEX ON x_tab (id);
     INSERT INTO x_tab (id, name) VALUES
       (40, 'User1'),
       (41, 'User2'),
       (42, 'User3'),
       (43, 'User4'),
       (44, 'User5');
     ```

1. Create a 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**: Select `<source_PostgreSQL_cluster_name>` from the drop-down list.
            * **Database**: `db1`.
            * **User**: `pg-user`.
            * **Password**: `<user_password>`.

        1. [Create a target endpoint](../operations/endpoint/target/clickhouse.md) of the `ClickHouse` type and specify the cluster connection settings in it:

            * **Connection type**: `Managed cluster`.
            * **Managed cluster**: Select `<target_ClickHouse®_cluster_name>` from the drop-down list.
            * **Database**: `db1`.
            * **User**: `ch-user`.
            * **Password**: `<user_password>`.
            * **Cleanup policy**: `DROP`.

        1. Create a [**Snapshot and replication**](../concepts/index.md#transfer-type)-type [transfer](../operations/transfer.md#create), configure it to use the previously created endpoints, then [activate](../operations/transfer.md#activate) it.

    - Terraform {#tf}

        1. In the `postgresql-to-clickhouse.tf` file, set the `transfer_enabled` variable to `1`.

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

        The transfer will be activated automatically as soon as it is created.

    {% endlist %}

## Test replication {#example-check-replication}

1. Wait for the transfer status to change to **Replicating**.
1. To verify that the data has been replicated to the target, connect to the target Yandex Managed Service for ClickHouse® cluster. Make sure the `x_tab` tables in `db1` and the source database have identical schemas, including [timestamp columns](#working-with-data-ch), `__data_transfer_commit_time` and `__data_transfer_delete_time`:

   ```sql
   SELECT * FROM db1.x_tab WHERE id = 41;
   ```

   ```text
   ┌─id─┬──name──┬─── __data-transfer_commit_time─┬───__data-transfer-delete_time─┐
   │ 41 │  User2 │   1633417594957267000          │ 0                             │
   └────┴────────┴────────────────────────────────┴───────────────────────────────┘
   ```

1. Connect to the source cluster.
1. In the `x_tab` table of the source PostgreSQL database, delete the row with ID = `41` and update the row with ID = `42`:

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

1. Make sure the changes have been applied to the `x_tab` table on the ClickHouse® target:

    ```sql
    SELECT * FROM db1.x_tab WHERE (id >= 41) AND (id <= 42);
    ```

    ```text
    ┌─id─┬──name──┬─── __data-transfer_commit_time─┬───__data-transfer-delete_time─┐
    │ 41 │  User2 │   1633417594957267000          │ 1675417594957267000           │
    │ 42 │  Key3  │   1675417594957267000          │ 0                             │
    │ 42 │  User3 │   1633417594957268000          │ 1675417594957267000           │
    └────┴────────┴────────────────────────────────┴───────────────────────────────┘
    ```

## Query data in ClickHouse® {#working-with-data-ch}

For table recovery, ClickHouse® targets with [replication](../../managed-clickhouse/concepts/replication.md) use the [ReplicatedReplacingMergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/replication) and [ReplacingMergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/replacingmergetree) engines. The following columns are automatically added to each table:

* `__data_transfer_commit_time`: Time the row was updated to this value, in `TIMESTAMP` format.
* `__data_transfer_delete_time`: Time the row was deleted from the source, in `TIMESTAMP` format. A value of `0` indicates that the row is still active.

    The `__data_transfer_commit_time` column is essential for the ReplicatedReplacedMergeTree engine. If a record is deleted or updated, a new row gets inserted with a value in this column. Querying by the primary key alone returns several records with different `__data_transfer_commit_time` values.

The source data can be added or deleted while the transfer is in **Replicating** status. For standard SQL command behavior, where the primary key returns a single record, add filtering by the `__data_transfer_delete_time` column when querying tables transferred to ClickHouse®. For example, to query the `x_tab` table, use the following syntax:

```sql
SELECT * FROM x_tab FINAL
WHERE __data_transfer_delete_time = 0;
```

To simplify the `SELECT` queries, create a view filtering rows by `__data_transfer_delete_time`. Use this view for all your queries. For example, to query the `x_tab` table, use the following syntax:

```sql
CREATE VIEW x_tab_view AS SELECT * FROM x_tab FINAL
WHERE __data_transfer_delete_time == 0;
```

{% note info %}

Using the `FINAL` keyword reduces query performance, so avoid it whenever possible, especially on large tables.

{% endnote %}

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

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

1. Make sure the transfer status is **Completed**, upon which you can [delete](../operations/transfer.md#delete) the transfer.
1. Delete the rest of the resources depending on how you created them:

    {% list tabs group=instructions %}

    - Manually {#manual}

        1. [Delete both the source and target endpoints](../operations/endpoint/index.md#delete).
        1. [Delete the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-delete.md).
        1. [Delete the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-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 %}

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._