Asynchronously replicating data from Yandex Managed Service for PostgreSQL to Yandex Managed Service for YDB

A Managed Service for YDB cluster can get data from PostgreSQL databases in real time. This data is automatically added to YDB tables with names of source schemas and tables.

To run data transfer:

1. Prepare the source cluster.
2. Prepare and activate the transfer.
3. Test the transfer.

If you no longer need these resources, delete them.

Before you begin

Prepare the data transfer infrastructure:

Prepare the source cluster

1. Connect to the database of the source cluster on behalf of the new user.

2. Add test data to the database. In this example, a simple table with information from car sensors is used.

   Create a table:

   CREATE TABLE public.sensors (
       "device_id" text PRIMARY KEY 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
   );
   

   Populate the table with data:

   INSERT INTO public.sensors VALUES
       ('iv9a94th6rztooxh5ur2', '2020-06-05 17:27:00', 55.70329032, 37.65472196,  427.5,    0, 23.5, 17, NULL),
       ('rhibbh3y08qmz3sdbrbu', '2020-06-06 09:49:54', 55.71294467, 37.66542005, 429.13, 55.5, NULL, 18, 32),
       ('iv9a94th678tooxh5ur2', '2020-06-07 15:00:10', 55.70985913, 37.62141918,  417.0, 15.7, 10.3, 17, NULL);
   

3. Prepare the source cluster for transfer.

Prepare and activate the transfer

1. Create a target endpoint:

   • Database type: YDB.

   • Endpoint parameters:

     • Connection settings:
       • Database: Select a Managed Service for YDB database from the list.
       • Service account ID: Select or create a service account with the ydb.editor role.

2. Create a source endpoint and transfer:

   Manually

   Using Terraform

   1. Create an endpoint for the previously created PostgreSQL source with the following settings:

      • DB type: PostgreSQL.
      • Endpoint parameters:
        • Connection settings: MDB cluster.
        • MDB cluster: Managed Service for PostgreSQL cluster that you created.
        • Database name: Name of the database created in the Managed Service for PostgreSQL cluster.
        • Username: Name of the user created in the Managed Service for PostgreSQL cluster.
        • Password: Password of the user.
        • List of included tables: <DB name>.sensors.

   2. Create a transfer of the Snapshot and increment type that will use the created endpoints.

   3. Enable the transfer.

   1. In the data-transfer-mpg-ydb.tf file, specify the values of parameters:

      • target_endpoint_id: ID of the target endpoint.
      • transfer_enabled: For creating a source endpoint and a transfer, the value is 1.

   2. Make sure the Terraform configuration files are correct using the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

   3. Create the required infrastructure:

      1. Run the command to view planned changes:

         terraform plan
         

         If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

      2. If you are happy with the planned changes, apply them:

         1. Run the command:

            terraform apply
            

         2. Confirm the update of resources.

         3. Wait for the operation to complete.

      Once created, a transfer is activated automatically.

Test the transfer

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

2. Make sure that the data from the source Managed Service for PostgreSQL cluster has been moved to the Managed Service for YDB database:

   Management console

   YDB CLI

   1. In the management console, select the folder with the desired DB.
   2. In the list of services, select Managed Service for YDB.
   3. Select the database from the list.
   4. Go to the Navigation tab.
   5. Check that the Managed Service for YDB database contains the public_sensors table with the test data.

   1. Connect to the Managed Service for YDB database.

   2. Check that the database contains the public_sensors table with the test data:

      ydb table query execute \
        --query "SELECT * \
        FROM public_sensors"
      

3. Connect to the Managed Service for PostgreSQL source cluster and add data to the measurements table:

   INSERT INTO public.sensors VALUES
       ('iv7b74th678tooxh5ur2', '2020-06-08 17:45:00', 53.70987913, 36.62549834, 378.0, 20.5, 5.3, 20, NULL);
   

4. Check that the Managed Service for YDB database shows information about the row added:

   Management console

   YDB CLI

   1. In the management console, select the folder with the desired DB.
   2. In the list of services, select Managed Service for YDB.
   3. Select the database from the list.
   4. Go to the Navigation tab.
   5. Check that new data has been added to the public_sensors table.

   1. Connect to the Managed Service for YDB database.

   2. Check that new data has been added to the public_sensors table:

      ydb table query execute \
        --query "SELECT * \
        FROM public_sensors"
      

Delete the resources you created

If you no longer need these resources, delete them: