[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Tutorials](index.md) > Replication and migration > Creating an Amazon RDS logical replica for PostgreSQL in Managed Service for PostgreSQL

# Creating an Amazon RDS logical replica for PostgreSQL in Managed Service for PostgreSQL


You can use logical replication to migrate a database from an Amazon RDS for PostgreSQL _source cluster_ to a Managed Service for PostgreSQL _target cluster_.

[Logical replication](https://www.postgresql.org/docs/current/logical-replication.html) uses the [subscription](https://www.postgresql.org/docs/current/sql-createsubscription.html) mechanism, allowing you to migrate data to the target cluster with minimal downtime. Logical replication is supported in Amazon RDS for PostgreSQL, starting from version 10.4.

Use logical replication if [data migration via Yandex Data Transfer](data-migration.md#data-transfer) is not possible for any reason.

To migrate a database from an Amazon RDS for PostgreSQL source cluster to a Managed Service for PostgreSQL target cluster:

1. [Configure Amazon RDS](#amazon-set).
1. [Configure the target cluster and create a subscription](#mdb-pg-set).
1. [Migrate sequences](#transfer-sequences).
1. [Delete the subscription and switch the workload to the target cluster](#transfer-load).


## 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](../pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Logical replication specifics {#logical-replica-specific}

* Database schema and DDL changes are not replicated.

    Always apply schema changes on the [subscription](https://www.postgresql.org/docs/current/logical-replication-subscription.html) side first, and only then on the [publication](https://www.postgresql.org/docs/current/logical-replication-publication.html) side.

* `SEQUENCES` are not replicated.

    Data replication for a table includes `serial` columns and identity columns generated by sequences. However, the sequence on the subscriber side will retain its original starting value.

    Therefore, when switching to the subscriber database, update the sequence to the latest value:

    ```sql
    ALTER SEQUENCE serial RESTART WITH <new_value>;
    ```

* By default, when you create a subscription, the system makes a full copy of data from the source tables.

    To speed up the copy process, create only the `PRIMARY KEY` first, postponing the creation of all other indexes until the copying is complete.

* If a table has no primary key, replication will produce errors:

    ```text
    ERROR: 55000: cannot update table "<table_name>" because it does not have a replica identity and publishes updates
    HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
    ```

    To enable the `UPDATE` and `DELETE` replication on tables with no primary key, change the `REPLICA IDENTITY`:

    ```sql
    ALTER TABLE <table_name> REPLICA IDENTITY FULL;
    ```

* External tables are not replicated.
* To prevent primary key violations when recreating a subscription, clear the tables in the target cluster first.
* Check [Managed Service for PostgreSQL logs](../operations/cluster-logs.md) for logical replication errors.

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

Create the required resources:

{% list tabs group=resources %}

- Manually {#manual}

    [Create a Managed Service for PostgreSQL cluster](../operations/cluster-create.md) with public access to its hosts. For this operation, the following requirements apply:

    * The PostgreSQL version must be the same or higher than the version in the source cluster. Migration to an earlier PostgreSQL version is not supported.
    * The database name must be the same as in the source cluster.
    * Enable the same [PostgreSQL extensions](../operations/extensions/cluster-extensions.md) as in the source database.

- 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 [logical-replica-amazon-rds-to-postgresql.tf](https://github.com/yandex-cloud-examples/yc-postgresql-amazon-rds-replica/blob/main/logical-replica-amazon-rds-to-postgresql.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) and a rule allowing cluster connections.
        * Managed Service for PostgreSQL cluster with public internet access.

    1. Specify the infrastructure settings in the `logical-replica-amazon-rds-to-postgresql.tf` configuration file under `locals`:

        * `pg_version`: PostgreSQL version. This version must be the same or higher than the Amazon RDS version.
        * `db_name`: Database name in the target cluster. It must be the same as the source database name.
        * `username` and `password`: Database owner username and password.
        * Names and versions of PostgreSQL extensions used in Amazon RDS. To list all required extensions, uncomment and copy the `extension` section.

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

## Configure Amazon RDS {#amazon-set}

{% note warning %}

The database instance must be publicly accessible: `Public accessibility = yes`.

{% endnote %}

1. Set up logical replication.

    1. In the `parameter group` section of your database instance, specify the following setting:

        ```text
        rds.logical_replication = 1
        ```

    1. Restart the cluster for the changes to take effect.

1. Create a user with the `rds_replication` role. To do this, execute the following as the user with the `rds_superuser` role:

    ```sql
    CREATE ROLE <username> WITH LOGIN PASSWORD <password>;
    GRANT rds_replication TO <username>;
    ```

1. Grant the `SELECT` privilege on all the tables included in the replication:

    ```sql
    GRANT SELECT ON <table_1>, <table_2>, ..., <table_n> TO <username>;
    ```

1. Create a publication:

    ```sql
    CREATE PUBLICATION pub FOR TABLE <table_1>, <table_2>, ..., <table_n>;
    ```

    {% note info %}

    We do not recommend using the `FOR ALL TABLES` publications as it prevents editing the table list in the future.

    {% endnote %}

1. Add an ingress rule to the [VPC security groups](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html). For example:

    ```text
    protocol: tcp, port: 5432, source: 84.201.175.90/32
    ```

    Where `84.201.175.90` is a public IP address.

## Configure the target cluster and create a subscription {#mdb-pg-set}

In Managed Service for PostgreSQL clusters, subscriptions can be used by the database owner, i.e., a user created alongside the cluster, and users with the `mdb_admin` role for that cluster.

1. Optionally, [assign](../operations/grant.md#grant-role) the `mdb_admin` role to the Managed Service for PostgreSQL cluster user.

1. Create a subscription with the following source cluster’s connection string:

    ```sql
    CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<source_cluster_address> port=<port> user=<username> sslmode=prefer dbname=<DB_name>' PUBLICATION pub;
    ```
    
    {% note tip %}

    If you cannot create a subscription using this query, add `sslrootcert=none` to it.

    {% endnote %}

    For details on creating subscriptions, see [this PostgreSQL article](https://www.postgresql.org/docs/current/sql-createsubscription.html).

1. To get the replication status, check the `pg_subscription_rel` folders:

    ```sql
    SELECT * FROM pg_subscription_rel;
    ```

    `r` in the `srsubstate` field indicates that the replication is over.

### Migrate sequences {#transfer-sequences}

To complete synchronization between the source and target clusters:

1. Set the source cluster to <q>read-only</q> mode.
1. Create a database dump including sequences:

    ```bash
    pg_dump --host=<source_cluster_address> \
            --username=<username> \
            --port=<port> \
            --dbname=<DB_name> \
            --data-only \
            --table='*.*_seq' > /tmp/seq-data.sql
    ```

    Note the `*.*_seq` pattern we use. If your source database has sequences that do not match this pattern, use a different pattern to export them.

    To learn more about patterns, see [this PostgreSQL article](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS).

1. Restore the dump containing database sequences into your target cluster:

    ```bash
    psql \
        --host=<master_host_FQDN_of_target_cluster> \
        --username=<username> \
        --port=6432 \
        --dbname=<DB_name> < /tmp/seq-data.sql
    ```

### Delete the subscription and switch the workload to the target cluster{#transfer-load}.

1. Delete the subscription in the target cluster:

    ```sql
    DROP SUBSCRIPTION s_data_migration;
    ```

1. Switch the workload to the target cluster.