[Yandex Cloud documentation](../../index.md) > [Yandex Data Transfer](../index.md) > [Tutorials](index.md) > Change data capture and delivery to the queue > PostgreSQL to YDS

# PostgreSQL change data capture and delivery to Yandex Data Streams

You can track data changes in a Managed Service for PostgreSQL _source cluster_ and send them to a Data Streams _target cluster_ using [change data capture](../concepts/cdc.md) (CDC).

To set up CDC using Data Transfer:

1. [Set up your transfer](#prepare-transfer).
1. [Activate the transfer](#activate-transfer).
1. [Test replication](#check-replication).

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)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Managed Service for YDB database (see [Managed Service for YDB pricing](../../ydb/pricing/index.md)). Its cost depends on the deployment mode:

    * In serverless mode, you pay for data operations and storage volume, including stored backups.
    * In dedicated instance mode, you pay for the use of computing resources allocated to the database, storage and backup size.

* Data Streams (see [Data Streams pricing](../../data-streams/pricing.md)). The cost depends on the pricing model:

    * [Based on allocated resources](../../data-streams/pricing.md#rules): You pay a fixed hourly rate for the established throughput limit and message retention period, and additionally for the number of units of actually written data.
    * [On-demand](../../data-streams/pricing.md#on-demand): You pay for the performed read/write operations, the amount of read or written data, and the actual storage used for messages that are still within their retention period.


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

Set up your infrastructure:

{% list tabs group=instructions %}

- Manually {#manual}

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

        {% 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. Configure [security groups](../../managed-postgresql/operations/connect/index.md#configuring-security-groups), ensuring they allow cluster connections.


    1. [Grant](../../managed-postgresql/operations/grant.md#grant-privilege) the `mdb_replication` role to `pg-user`.

    1. [Create a Managed Service for YDB database](../../ydb/operations/manage-databases.md#create-db) `ydb-example` with your preferred configuration.

    1. [Create a service account](../../iam/operations/sa/create.md#create-sa) `yds-sa` with the `yds.editor` role. The transfer will use it to access Data Streams.

- 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-yds.tf](https://github.com/yandex-cloud-examples/yc-data-transfer-cdc-from-postgresql-to-yds/blob/main/postgresql-yds.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 YDB database.
        * Service account that will be used to access Data Streams.
        * Source endpoint.
        * Transfer.

    1. In the `postgresql-yds.tf` file, specify the PostgreSQL user password.

    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 your transfer {#prepare-transfer}

1. [Create a data stream `mpg-stream` in Data Streams](../../data-streams/operations/aws-cli/create.md).

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

    ```sql
    CREATE TABLE measurements (
        device_id varchar(200) NOT NULL,
        datetime timestamp NOT NULL,
        latitude real NOT NULL,
        longitude real NOT NULL,
        altitude real NOT NULL,
        speed real NOT NULL,
        battery_voltage real,
        cabin_temperature real NOT NULL,
        fuel_level real,
        PRIMARY KEY (device_id)
    );
    INSERT INTO measurements VALUES
        ('iv9a94th6rzt********', '2022-06-05 17:27:00', 55.70329032, 37.65472196,  427.5,    0, 23.5, 17, NULL),
        ('rhibbh3y08qm********', '2022-06-06 09:49:54', 55.71294467, 37.66542005, 429.13, 55.5, NULL, 18, 32);
    ```

1. [Create a target endpoint](../operations/endpoint/target/data-streams.md) of the `Data Streams` type with the following settings:

    * **Database**: `ydb-example`
    * **Stream**: `mpg-stream`
    * **Service account**: `yds-sa`

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**: `<PostgreSQL_source_cluster_name>` from the drop-down list.
            * **Database**: `db1`.
            * **User**: `pg-user`.
            * **Password**: `pg-user` password.

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

    - Terraform {#tf}

        1. In the `postgresql-yds.tf` file, specify the following variables:

            * `yds_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 **_Replicating_**.

1. Make sure the data from the source has been moved to the Data Streams stream:
   
     {% list tabs group=instructions %}
   
     - Management console {#console}
   
         1. In the [management console](https://console.yandex.cloud), select a folder.
         1. Navigate to **Data Streams**.
         1. Select the target stream from the list and navigate to ![image](../../_assets/console-icons/bars.svg) **Data viewer**.
         1. Make sure `shard-000000` now contains messages with the source table rows. For a closer look at the messages, click ![image](../../_assets/console-icons/eye.svg).
   
     - AWS CLI {#cli}
   
         1. Install the [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html).
         1. [Configure the environment](../../data-streams/quickstart/index.md) for Data Streams.
         1. Read the stream data using:
   
             * [AWS CLI](../../data-streams/operations/aws-cli/get-records.md).
             * [AWS SDK](../../data-streams/operations/aws-sdk/get-records.md).
   
     {% endlist %}

## Test replication {#check-replication}

1. Connect to the source cluster.
1. Add a new row to the `measurements` table:

    ```sql
    INSERT INTO measurements VALUES
        ('ad02l5ck6sdt********', '2022-06-05 17:27:00', 55.70329032, 37.65472196,  427.5,    0, 23.5, 19, 45);
    ```

1. Verify that the new row has appeared in the data stream.

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

{% note info %}

Before deleting the created resources, [deactivate the transfer](../operations/transfer.md#deactivate).

{% endnote %}

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

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

   {% list tabs group=instructions %}

   - Manually {#manual}

       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 Managed Service for YDB database](../../ydb/operations/manage-databases.md#delete-db).

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