[Yandex Cloud documentation](../../../../index.md) > [Yandex Data Transfer](../../../index.md) > [Step-by-step guides](../../index.md) > [Configuring endpoints](../index.md) > PostgreSQL > Target

# Transferring data to a PostgreSQL target endpoint

Yandex Data Transfer enables you to migrate data to a PostgreSQL database and implement various data transfer, processing, and transformation scenarios. To implement a transfer:

1. [Explore possible data transfer scenarios](#scenarios).
1. [Configure one of the supported data sources](#supported-sources).
1. [Prepare the PostgreSQL](#prepare) database for the transfer.
1. [Configure the target endpoint](#endpoint-settings) in Yandex Data Transfer.
1. [Create](../../transfer.md#create) a transfer and [start](../../transfer.md#activate) it.
1. [Perform the required operations with the database](#db-actions) and [see how the transfer is going](../../monitoring.md).
1. In case of any issues, [use ready-made solutions](#troubleshooting) to resolve them.

## Scenarios for transferring data to PostgreSQL {#scenarios}

1. Migration: Moving data from one storage to another. Migration often means migrating a database from obsolete local databases to managed cloud ones.

    * [Migrating a PostgreSQL cluster](../../../tutorials/managed-postgresql.md).
    * [Migrating from AWS RDS for PostgreSQL](../../../tutorials/rds-to-mpg.md).
    * [Migration with change of storage from MySQL® to PostgreSQL](../../../tutorials/mmy-to-mpg.md).

1. Data delivery is a process of delivering arbitrary data to target storage. It includes data retrieval from a queue and its deserialization with subsequent transformation to target storage format.

    * [Delivering data from Apache Kafka® to PostgreSQL](../../../tutorials/mkf-to-mpg.md).

1. Uploading data to data marts is a process of transferring prepared data to storage for subsequent visualization.

    * [Loading data from Greenplum® to PostgreSQL](../../../tutorials/greenplum-to-postgresql.md).
    * [Loading data from Object Storage to PostgreSQL](../../../tutorials/object-storage-to-postgresql.md).

For a detailed description of possible Yandex Data Transfer scenarios, see [Tutorials](../../../tutorials/index.md).

## Configuring the data source {#supported-sources}

Configure one of the supported data sources:

* [PostgreSQL](../source/postgresql.md)
* [MySQL®](../source/mysql.md)
* [Greenplum®](../source/greenplum.md)
* [Apache Kafka®](../source/kafka.md)
* [Airbyte®](../../../transfer-matrix.md#airbyte)
* [YDS](../source/data-streams.md)
* [Yandex Object Storage](../source/object-storage.md)
* [YTsaurus](../source/yt.md)
* [Managed Service for YDB](../source/ydb.md)
* [Oracle](../source/oracle.md).

For a complete list of supported sources and targets in Yandex Data Transfer, see [Available transfers](../../../transfer-matrix.md).

## Preparing the target database {#prepare}

{% list tabs %}

- Managed Service for PostgreSQL

    1. Make sure that the PostgreSQL major version on the target is not lower than that on the source.

    1. For transfers from PostgreSQL, [enable the same extensions](../../../../managed-postgresql/operations/extensions/cluster-extensions.md) in the target database as in the source database.

        If extensions in the source database are installed in a custom schema and are used in the DDLs of the objects you are moving, create DDLs in the target manually. In these DDLs, a function call must not include the schema name. Set the target endpoint cleanup policy to `Truncate` to prevent the transfer from deleting these objects.

    1. Select the `Drop` cleanup policy for transfer tables.

        If you have created DDLs in the target manually, use the `Truncate` policy. The `Truncate` policy will not delete these DDLs.

    1. [Create a user](../../../../managed-postgresql/operations/cluster-users.md#adduser) with access to the target database.

    1. Grant the user all privileges for the database, schemas, and tables to be transferred:

        ```sql
        GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;
        ```

       If the database is not empty, the user must be its owner:

        ```sql
        ALTER DATABASE <database_name> OWNER TO <username>;
        ```

       Once started, the transfer will connect to the target on behalf of this user.
    1. If the target has the [Save transaction boundaries](postgresql.md#additional-settings) option enabled, grant the new user all the privileges needed to create the `__data_transfer_lsn` auxiliary table in the [current schema](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) (usually `public`) in the target:

       ```sql
       GRANT ALL PRIVILEGES ON SCHEMA <schema_name> TO <username>;
       ```

    1. Configure the [number of user connections](../../../concepts/work-with-endpoints.md#postgresql-connection-limit) to the database.

- PostgreSQL

    1. If not planning to use [Cloud Interconnect](../../../../interconnect/concepts/index.md) or [VPN](https://en.wikipedia.org/wiki/Virtual_private_network) for connections to an external cluster, make such cluster accessible from the Internet from [IP addresses used by Data Transfer](../../../../overview/concepts/public-ips.md#virtual-private-cloud).
       
       For details on linking your network up with external resources, see [this concept](../../../concepts/network.md#source-external).

    1. Make sure that the PostgreSQL major version on the target is not lower than that on the source.

    1. In the target database, enable the same extensions that are enabled in the source database.

    1. Make sure the target has the `DROP transfer tables` cleanup policy selected.

    1. Create a user:

        ```sql
        CREATE ROLE <username> LOGIN ENCRYPTED PASSWORD '<password>';
        ```

    1. Grant the user all privileges for the database, schemas, and tables to be transferred:

        ```sql
        GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;
        ```

       If the database is not empty, the user must be its owner:

        ```sql
        ALTER DATABASE <database_name> OWNER TO <username>;
        ```

       Once started, the transfer will connect to the target on behalf of this user.

    1. If the target has the [Save transaction boundaries](postgresql.md#additional-settings) option enabled, grant the new user all the privileges needed to create the `__data_transfer_lsn` auxiliary table in the [current schema](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) (usually `public`) in the target:

        ```sql
        GRANT ALL PRIVILEGES ON SCHEMA <schema_name> TO <username>;
        ```

    1. Configure the [number of user connections](../../../concepts/work-with-endpoints.md#postgresql-connection-limit) to the database.

{% endlist %}

Data stored in a `MATERIALIZED VIEW` is not transferred. To transfer `MATERIALIZED VIEW` data, create an ordinary `VIEW` that refers to the `MATERIALIZED VIEW` to be transferred.

If the definition of the `VIEW` to be transferred contains an invocation of the `VOLATILE` [function](https://www.postgresql.org/docs/current/xfunc-volatility.html), the transfer reads data from this `VIEW` with the `READ UNCOMMITTED` isolation level. No consistency between the `VIEW` data and the data of other objects being transferred is guaranteed. Reading data from a `MATERIALIZED VIEW` in the `VIEW` definition are equivalent to invoking the `VOLATILE` function.

## Configuring the PostgreSQL target endpoint {#endpoint-settings}

When [creating](../index.md#create) or [updating](../index.md#update) an endpoint, you can define:

* [Yandex Managed Service for PostgreSQL cluster](#managed-service) connection or [custom installation](#on-premise) settings, including those based on Yandex Compute Cloud VMs. These are required parameters.
* [Additional parameters](#additional-settings).

### Managed Service for PostgreSQL cluster {#managed-service}


{% note warning %}

To create or edit an endpoint of a managed database, you will need the [`managed-postgresql.viewer`](../../../../managed-postgresql/security/index.md#mpg-viewer) role or the primitive [`viewer`](../../../../iam/roles-reference.md#viewer) role for the folder the cluster of this managed database resides in.

{% endnote %}


Connection to the database with the cluster specified in Yandex Cloud.

{% list tabs group=instructions %}

- Management console {#console}

    * **Connection type**: Select a database connection option:
    
        * **Self-managed**: Allows you to specify connection settings manually.
    
            Select **Managed Service for PostgreSQL cluster** as the installation type and configure these settings as follows:
    
            * **Managed DB cluster**: Select the cluster to connect to.
            * **Database**: Specify the name of the database in the selected cluster.
            * **User**: Specify the username that Data Transfer will use to connect to the database.
            * **Password**: Enter the user's password to the database.
    
        * **Connection Manager**: Enables using a managed database connection via [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md):
    
            * Select the folder with the Managed Service for PostgreSQL cluster.
            * Select **Managed DB cluster** as the installation type and configure the following settings:
    
                * **Managed DB cluster**: Select the cluster to connect to.
                * **Connection**: Specify the managed connection ID in Connection Manager.
                * **Database**: Specify the name of the database in the selected cluster.
    
            {% note warning %}
            
            To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher.
            
            {% endnote %}
    
    * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic.
      
      Thus, you will be able to apply the specified security group rules to the VMs and clusters in the selected network without changing the settings of these VMs and clusters. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).

- CLI {#cli}

    * Endpoint type: `postgres-target`.

    * `--cluster-id`: ID of the cluster you need to connect to.
    * `--database`: Database name.
    * `--user`: Username that Data Transfer will use to connect to the database.
    
    * To set a user password to access the DB, use one of the following parameters:
    
        * `--raw-password`: Password as text.
        * `--password-file`: The path to the password file.
    
- Terraform {#tf}

    * Endpoint type: `postgres_target`.

    * `connection.mdb_cluster_id`: ID of cluster to connect to.
    * `database` — Database name.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Password in text form.

    Here is an example of the configuration file structure:

    
    ```hcl
    resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
      name = "<endpoint_name>"
      settings {
        postgres_target {
          security_groups = ["<list_of_security_group_IDs>"]
          connection {
            mdb_cluster_id = "<cluster_ID>"
          }
          database = "<name_of_database_to_migrate>"
          user     = "<username_for_connection>"
          password {
            raw = "<user_password>"
          }
        }
      }
    }
    ```


    For more information, see [this Terraform provider guide](../../../../terraform/resources/datatransfer_endpoint.md).

- API {#api}

    * `mdbClusterId`: ID of the cluster you need to connect to.
    * `database`: Database name.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Database user password (in text form).

{% endlist %}

### Custom installation {#on-premise}

For OnPremise, all fields are filled in manually.

{% list tabs group=instructions %}

- Management console {#console}

    * **Connection type**: Select a database connection option:
    
        * **Self-managed**: Allows you to specify connection settings manually.
    
            Select **Custom installation** as the installation type and configure the following settings:
    
            * **Host**: Specify the IP address or FQDN of the master host. If hosts have different ports open for connection, you can specify multiple host values in `host:port` format. If you choose this format, the value of the **Port** field will be disregarded.
            * **Port**: Set the number of the port that Data Transfer will use for the connection.
            * **Database**: Specify the database name in the custom installation.
            * **User**: Specify the username that Data Transfer will use to connect to the database.
            * **Password**: Enter the user's password to the database.
            * **CA certificate**: Upload the [certificate](../../../../managed-postgresql/operations/connect/index.md#get-ssl-cert) file or add its contents as text if data encryption is required, e.g., to comply with the PCI DSS requirements.
              
              {% note warning %}
              
              If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
              
              {% endnote %}
            * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the required [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the cluster.
              
              If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone.
    
        * **Connection Manager**: Enables using a managed database connection using [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md):
    
            * Select the folder where the Connection Manager managed connection was created.
            * Select **Custom installation** as the installation type and configure the following settings:
    
                * **Connection**: Specify the managed connection ID in Connection Manager.
                * **Database**: Specify the database name in the custom installation.
                * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the required [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the cluster.
                  
                  If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone.
    
            {% note warning %}
            
            To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher.
            
            {% endnote %}
    
    * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic.
      
      Thus, you will be able to apply the specified security group rules to the VMs and clusters in the selected network without changing the settings of these VMs and clusters. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).

- CLI {#cli}

    * Endpoint type: `postgres-target`.

    * `--host`: IP address or FQDN of the master host you want to connect to.
    * `--port`: Number of the port that Data Transfer will use for the connection.
    * `--ca-certificate`: CA certificate if the data to transfer must be encrypted to comply with PCI DSS requirements.
      
      {% note warning %}
      
      If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
      
      {% endnote %}
    * `--subnet-id`: ID of the subnet the host is in. The transfer will use that subnet to access the host.
    * `--database`: Database name.
    * `--user`: Username that Data Transfer will use to connect to the database.
    
    * To set a user password to access the DB, use one of the following parameters:
    
        * `--raw-password`: Password as text.
        * `--password-file`: The path to the password file.

- Terraform {#tf}

    * Endpoint type: `postgres_target`.

    * `security_groups`: [Security groups](../../../../vpc/concepts/security-groups.md) for network traffic.
      
      Security group rules apply to a transfer. They allow opening network access from the transfer VM to the database VM. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).
      
      Security groups must belong to the same network as the `subnet_id` subnet, if the latter is specified.
      
      {% note info %}
      
      In Terraform, it is not required to specify a network for security groups.
      
      {% endnote %}
    * `on_premise.hosts`: List of IPs or FQDNs of hosts to connect to. Since only single-item lists are supported, specify the master host address.
    * `on_premise.port`: Port number that Data Transfer will use for connections.
    * `on_premise.tls_mode.enabled.ca_certificate`: CA certificate if the data to transfer must be encrypted to comply with PCI DSS requirements.
      
      {% note warning %}
      
      If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
      
      {% endnote %}
    * `on_premise.subnet_id`: ID of the [subnet](../../../../vpc/concepts/network.md#subnet) the host is in. The transfer will use that subnet to access the host.
    * `database` — Database name.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Password in text form.

    Here is an example of the configuration file structure:

    
    ```hcl
    resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
      name = "<endpoint_name>"
      settings {
        postgres_target {
          security_groups = ["<list_of_security_group_IDs>"]
          connection {
            on_premise {
              hosts = ["<list_of_hosts>"]
              port  = <port_for_connection>
            }
          }
          database = "<name_of_database_to_migrate>"
          user     = "<username_for_connection>"
          password {
            raw = "<user_password>"
          }
        }
      }
    }
    ```


    For more information, see [this Terraform provider guide](../../../../terraform/resources/datatransfer_endpoint.md).

- API {#api}

    * `onPremise`: Database connection parameters:
        * `hosts` — IP address or FQDN of the master host to connect to.
        * `port`: The number of the port that Data Transfer will use for the connection.
        * `tlsMode`: Parameters for encrypting the data to transfer, if required, e.g., for compliance with the PCI DSS requirements.
            * `disabled`: Disabled.
            * `enabled`: Enabled.
                * `caCertificate`: CA certificate.
          
                  {% note warning %}
                  
                  If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
                  
                  {% endnote %}
        * `subnetId`: ID of the subnet the host is in. The transfer will use that subnet to access the host.
    * `database`: Database name.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Database user password (in text form).

{% endlist %}

### Additional settings {#additional-settings}

{% list tabs group=instructions %}

- Management console {#console}

    * **Cleanup policy**: Select a way to clean up data in the target database before the transfer:
      
      * `Don't cleanup`: Select this option if you are only going to do replication without copying data.
      
      * `Drop`: Completely delete tables included in the transfer (used by default).
      
         Use this option so that the latest version of the table schema is always transferred to the target database from the source whenever the transfer is activated.
      
      * `Truncate`: Delete only the data from the tables included in the transfer but keep the schema.
      
         Use this option if the schema in the target database differs from the one that would have been transferred from the source during the transfer.

    * **Save transaction borders**: Enable so that the service writes data to the target database only after fully reading the transaction data from the source database.
      
      We recommend enabling this setting for transfers from PostgreSQL to PostgreSQL. While it may slightly reduce transfer performance, it will prevent [errors](postgresql.md#duplicate-key) related to exceeded limitations.
      
      
      {% note warning %}
      
      This feature is at the [Preview](../../../../overview/concepts/launch-stages.md) stage.
      
      {% endnote %}
  
    * **Disable schema migration**: Select to prevent changes to the target data schema when the source schema is modified. By default, when the source schema is modified, the transfer will update the target schema accordingly: create new tables, add new columns, add new enumerated values and enumerated types. By default, changes like deleting tables and columns are not applied.

- Terraform {#tf}

    * `cleanup_policy`: Way to clean up data in the target database before the transfer:
      
      * `DISABLED`: Do not clean up (default).
      
         Select this option if only replication without copying data is performed.
      
      * `DROP`: Completely delete the tables included in the transfer.
      
         Use this option so that the latest version of the table schema is always transferred to the target database from the source whenever the transfer is activated.
      
      * `TRUNCATE`: Delete only the data from the tables included in the transfer but keep the schema.
      
         Use this option if the schema in the target database differs from the one that would have been transferred from the source during the transfer.

    * `is_schema_migration_disabled`: Set to `true` to prevent changes to the target data schema when the source schema is modified. By default, when the source schema is modified, the transfer will update the target schema accordingly: create new tables, add new columns, add new enumerated values and enumerated types. By default, changes like deleting tables and columns are not applied.

- API {#api}

    `cleanupPolicy`: Way to clean up data in the target database before the transfer:
    
    * `DISABLED`: Do not clean up (default).
    
       Select this option if only replication without copying data is performed.
    
    * `DROP`: Completely delete the tables included in the transfer.
    
       Use this option so that the latest version of the table schema is always transferred to the target database from the source whenever the transfer is activated.
    
    * `TRUNCATE`: Delete only the data from the tables included in the transfer but keep the schema.
    
       Use this option if the schema in the target database differs from the one that would have been transferred from the source during the transfer.

{% endlist %}

After configuring the data source and target, [create and start the transfer](../../transfer.md#create).

## Operations with the database during transfer {#db-actions}

{% note tip %}

The PostgreSQL replication protocol does not support transferring schema changes. Avoid changing the data schema in the source and target databases during the transfer. If this cannot be avoided, perform explicit checks on the target.

{% endnote %}

For _**Snapshot**_ and _**Snapshot and increment**_ transfers:

* In the **Copying** status, changes to the data schema on the source and target are not allowed.
* In the **Replicating** status, any changes to the data schema on the source should be manually applied to the target, otherwise the transfer will not be able to continue.

   For example, suppose we added a new column to the `test_table` table on the source:

   ```sql
   ALTER TABLE test_table ADD COLUMN val2 TEXT;
   ```

   If writing to this table continues, the transfer will not be able to insert data on the target. For replication to continue, run a similar query to change the data schema on the target:

   ```sql
   ALTER TABLE test_table ADD COLUMN val2 TEXT;
   ```

   After that, the transfer can continue.

## Troubleshooting data transfer issues {#troubleshooting}

Known issues when using a PostgreSQL endpoint:

* [Stopping a transfer's master transaction session](#master-trans-stop).
* [Exceeding the connection time-to-live quota](#conn-duration-quota).
* [VIEW transfer error](#view).
* [Error when adding a table entry by constraint](#constraint).
* [Error when migrating all schema tables](#schema).
* [Unable to create objects involving extension functions](#extension-functions).
* [Low transfer speed](#low-speed).
* [Unable to transfer child tables](#successor-tables).
* [Insufficient replication slots in a source database](#replication-slots).
* [No data transfer after changing a source endpoint](#no-data-transfer).
* [Transfer error when changing the master host](#master-change).
* [WAL lacks records to continue replication after changing the master host](#no-wal-story).
* [Error when transferring nested transactions](#inner-tables).
* [Error transferring tables with deferred constraints](#deferrable-constr).
* [Cannot create a replication slot at the activation step](#lock-replication).
* [Excessive WAL size increase](#excessive-wal).
* [Error when replicating from an external source](#external-replication).
* [Error when transferring tables without primary keys](#primary-keys).
* [Duplicate key violates a unique constraint](#duplicate-key).
* [Error when dropping a table under the Drop cleanup policy](#drop-table-error).
* [Error when transferring tables with generated columns](#generated-columns).

For more troubleshooting tips, see [Troubleshooting](../../../troubleshooting/index.md).

### Stopping a transfer's master transaction session {#master-trans-stop}

Error message:

```text
Cannot set transaction snapshot:
ERROR: invalid snapshot identifier: "<snapshot_ID>" (SQLSTATE 22023).
```

Possible causes:

* The source is running a cron job or other application that periodically terminates sessions that are too long.
* Someone manually terminated the master transaction.
* The source's CPU resources are insufficient to execute a query.
* The [Session duration timeout](../../../../managed-postgresql/concepts/settings-list.md#setting-session-duration-timeout) parameter in the PostgreSQL cluster sets a limit for the active session lifetime.

**Solution:** Disable this cron job and add more CPU resources to the source; also set the **Session duration timeout** parameter to `0` for the duration of the transfer. After you make the changes, [re-activate](../../transfer.md#activate) the transfer.

### Exceeding the connection time-to-live quota {#conn-duration-quota}

Yandex Managed Service for PostgreSQL has a connection time-to-live quota of 12 hours.
​​
**Solution:** If transferring a database requires more time, change the [Session duration timeout](../../../../managed-postgresql/concepts/settings-list.md#setting-session-duration-timeout) setting of the [Yandex Managed Service for PostgreSQL cluster](../../../../managed-postgresql/operations/update.md#change-postgresql-config).

### VIEW transfer error {#view}

Error message:

```text
[ERROR] "... _view": no key columns found
```

Can't replicate new data from Views. Under PostgreSQL — PostgreSQL transfers, only those views are transferred which are listed in the **Table filter** → **Included tables** source endpoint parameter.

**Solution:** manually exclude all views from the transfer, list them in the **Table filter** → **Included tables** [source endpoint parameter](../source/postgresql.md#additional-settings), and then [reactivate](../../transfer.md#activate) the transfer.

### Error when adding a table entry by constraint {#constraint}

**Solution:** prepare the source as described in [Preparing for the transfer](../../prepare.md#source-pg).

### Error when transferring all schema tables {#schema}

Error message:

```text
Unable to apply DDL of type 'TABLE', name '<schema>'.'<table>', error:
ERROR: schema "<schema name>" does not exist (SQLSTATE 3F000)
```

A transfer fails if tables of a specific schema are listed as `<schema>.*`. This occurs due to the specifics of `pg_dump` that is used for a schema transfer. When specifying tables of the entire schema in `<schema>.*` format in the [source endpoint parameter](../source/postgresql.md#additional-settings) **Table filter** → **Included tables**, the PostgreSQL types from this schema aren't extracted even if it contains tables that depend on these types.

**Solution:** Create PostgreSQL types in the target database manually.

### Unable to create objects involving extension functions {#extension-functions}

Error message:

```text
Unable to apply DDL of type 'TABLE', <object_name>, error:
failed to push non-row item 0 of kind "pg:DDL" in batch:
Push failed: ERROR: function <schema_name>.<function_name>() does not exist 
(SQLSTATE 42883)
```

In Managed Service for PostgreSQL, you cannot install an extension into a custom schema in a target database. This means the transfer will be interrupted if the user installation of Managed Service for PostgreSQL has extensions installed in a custom schema and these extensions are used in the DDLs of objects you are moving.

**Solution**: check the DDLs of objects whose names appear in the error. If these objects can call a function from a custom schema, manually create DDLs in the target that call functions without specifying a schema. Set the target endpoint cleanup policy to `Truncate` to prevent the transfer from deleting these objects.

### Low transfer speed {#low-speed}

​May affect _**Snapshot**_ or _**Snapshot and increment**_ transfers from PostgreSQL to PostgreSQL.

Possible causes:

* Write protocol.

    Under normal conditions, the transfer uses the fast `copy` protocol but switches to slow line-by-line writes if there are batch write conflicts. The more write conflicts, the lower the transfer speed.

    **Solution**: Set the target endpoint's cleanup policy type to `Drop` and exclude other writing processes.

* Parallel table reading.

    Parallel reading is only available for tables containing a primary key. When using a [`serial` type](https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL) key, parts of tables are read by ranges. Other types of keys allow you to distribute tables evenly according to a special algorithm.

    **Solution**: Set up [parallel copy](../../../concepts/sharded.md) and [reactivate the transfer](../../transfer.md#activate).

### Unable to transfer child tables {#successor-tables}

Child tables are either not transferred or transferred without data if a table is partitioned.

**Solution:** set the following source endpoint parameters:

1. Select **Merge inherited tables** in advanced settings.
1. In the **Included tables** field, specify all child tables to transfer data from.
1. Make sure the user has access to the child tables.

To speed up the transfer of child tables, set up [parallel copy](../../../concepts/sharded.md).

### Insufficient replication slots in a source database {#replication-slots}

Error message:

```text
Warn(Activate): failed to create a replication slot "<transfer_ID>" at source:
failed to create a replication slot:
failed to create a replication slot:
ERROR: all replication slots are in use
(SQLSTATE 53400)
```

**Solution:** Increase the number of [replication slots](https://www.postgresql.org/docs/current/logical-replication-config.html) in the source database (by default, `10`).

### No data transfer after changing a source endpoint {#no-data-transfer}

After adding tables to the **List of included tables** in the source endpoint parameters, a data transfer restarted and stopped.

**Solution:**

* Create tables in the target database manually.

   1\. Create new tables with a `Primary key` and without a `Foreign key` in the target database.
   2\. Add the new tables to the **List of included tables** in the [source endpoint parameters](../source/postgresql.md#additional-setting).
   3\. Transfer a dump with historical data to the target database.
   4\. If there are errors in the logs, fix them depending on a specific error.
   5\. If there are no errors but the logs are empty, contact [technical support](https://center.yandex.cloud/support) or your account manager to dump goroutines. This may help resume replication without restarting the transfer.

* [Deactivate](../../transfer.md#deactivate) and [reactivate](../../transfer.md#activate) the transfer.
* [Create](../../transfer.md#create) a separate transfer of the _**Snapshot**_ type for the new tables. Deactivating the original transfer is not required.

### Transfer error when changing a master host {#master-change}

The error occurs in _**Replication**_ or _**Snapshot and increment**_ transfers due to required Write-Ahead Log (WAL) parts missing. This occurs when the Write Ahead Log (WAL) logical replication lag between the current master and a replica is greater than the allowable WAL size on other hosts, so, when switching from the master to the replica, the replication slot can't sync to the WAL on the new master.

**Solution:** set a limit in the [additional target endpoint parameter](../source/postgresql.md#additional-setting) **Maximum WAL size for the replication slot** and [reactivate](../../transfer.md#activate) the transfer.

### WAL lacks records to continue replication after changing the master host {#no-wal-story}.

When you change the master host in the source cluster, _**Replication**_ or _**Snapshot and increment**_ transfers may throw this error:

```text
ERROR: requested WAL segment pg_wal/0000000E0000022700000087 has already been removed (SQLSTATE 58P01)
```

The error occurs if [WAL](https://www.postgresql.org/docs/current/wal-intro.html) does not have enough records on the new master to resume replication from the same place.

**Solution**: Increase the `Wal keep size` [setting](../../../../managed-postgresql/concepts/settings-list.md#setting-wal-keep-size) value in the source cluster. As the minimum value, use the average value from the **Source Buffer Size** chart in the [Data Transfer monitoring dashboard](../../monitoring.md). If disk has enough capacity, specify a value with some margin.

### Error when transferring nested transactions {#inner-tables}

Transfers of PostgreSQL below version 14 don't support a transfer of tables with committed transactions that are nested more than 1024 times with replication changes at each nesting level. The degree of nesting depends on the number of nested `begin; .. commit;` statements.

**Solution:**

* Use PostgreSQL 14 or higher.
* Exclude transactions with a nesting level like this from a transfer.

### Error transferring tables with deferrable constraints {#deferrable-constr}

An error occurs in transfers of the **Replication** or **Snapshot and increment** types since Data Transfer does not support updating tables and transactions with `DEFERRABLE` constraints. For more information about deferrable constraints, see [this PostgreSQL guide](https://www.postgresql.org/docs/current/sql-set-constraints.html).

**Solution**: Change the constraint type in such tables to `IMMEDIATE` and [reactivate](../../transfer.md#activate) the transfer.

### Unable to create replication slot during activation phase {#lock-replication}

At the beginning of the transfer, one or more [replication slots](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS) are created in the source database. The database objects are locked at this point. If some object is locked by another transaction, this results in a competing lock, which will terminate the transfer with an error.

**Solution:**

1. Find the process competing for locks with the transfer:

   ```sql
   SELECT
     activity.pid,
     activity.usename,
     activity.query,
     blocking.pid AS blocking_id,
     blocking.query AS blocking_query
   FROM
     pg_stat_activity AS activity
     JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(
       pg_blocking_pids(activity.pid)
     )
   WHERE
     activity.query like '%<transfer_ID>%';
   ```

   You can get the transfer ID with the [list of transfers in the folder](../../transfer.md#list).

   Response:

   ```text
          pid      |      usename       |      query      |         blocking_id          |    blocking_query
   ----------------+--------------------+-----------------+------------------------------+----------------------
   <transfer_PID> | <username> | <request_text> | <blocking_transaction_PID> | <blocking_request>
   (1 row)
   ```

1. Stop the transaction using this command:

   ```sql
   SELECT pg_terminate_backend(<blocking_transaction_PID>);
   ```

1. [Reactivate the transfer](../../transfer.md#activate).

### Excessive WAL size increase {#excessive-wal}

The WAL may grow in size during the **Snapshot and increment** and **Replication** transfers. You can be alerted to a WAL size increase by the following:

* Increased used space on the source cluster disk (**Disk usage on primary** chart).
* Increased size of the source cluster WAL files (**Total size of WAL files** chart).
* Growth on the **[Source buffer size](../../monitoring.md#publisher.consumer.log_usage_bytes)** or **[Maximum data transfer delay](../../monitoring.md#sinker.pusher.time.row_max_lag_sec)** diagrams in Data Transfer monitoring.

**Solution:**

You actions should depend on the current transfer stage:

   Transfer stage | Cause of WAL increase | Recommended action                                                                            
   --------|-----|---------------------------------------------------------
   Copying | WAL increase is expected.  |  Wait for the transfer to enter the replication stage.                          
   Replication | The source database has long transactions (over 5 minutes). Such requests cause the WAL to grow and interfere with its archiving. | Wait until the transaction ends or terminate the sessions manually. You can locate long transaction using the following query:<br>```SELECT pid, now() - pg_stat_activity.query_start```<br>```AS duration, query, state```<br>```FROM pg_stat_activity```<br>```WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'```<br>```AND state != 'idle';```
   Replication | There is a large changefeed on the source that the transfer fails to process. | Check the transfer resource utilization. If all the resources are utilized fully, increase them, reduce the number of threads, or split one transfer into multiple transfers. [Check](../../../metrics.md) the source and target resource utilization. If all resources are utilized fully, increase them.
   Replication | There were changes in the transferred objects schema on the source (table fields added or removed, types changed, etc.). Data stopped being written to the target due to schema mismatch. | Change the schema in the same way on the target manually.

### Error when replicating data from an external source {#external-replication}

Error message:

```text
[XX000] ERROR: could not connect to the publisher:
SSL error: certificate verify failed FATAL:
no pg_hba.conf entry for replication connection
from host "<PostgreSQL_host_IP_address>", user "postgres", SSL off
```

**Solution:** Prepare the source as described in [Preparing for the transfer](../../prepare.md#source-pg).

### Error when transfering tables without primary keys {#primary-keys}

Error message:

```text
Primary key check failed: 14 Tables errors: Table no key columns found
```

For _**Replication**_ and _**Snapshot and increment**_ transfers, tables without primary keys are not transferred.

**Solution:** Prepare the source as described in [Preparing for the transfer](../../prepare.md).

### Duplicate key violates a unique constraint {#duplicate-key}

Error message:

```text
ERROR: duplicate key value violates unique constraint "<constraint_name>" (SQLSTATE 23505)
```

You may get this error while replicating data from PostgreSQL to PostgreSQL, e.g., when there was not enough memory for all the transaction events and only some transaction rows were transferred from the source database to the target one. In the target database, those rows are applied in a separate transaction by default, which may lead to a constraint violation, such as a duplicate key.

**Solution**: use one of the following options:

* [Enable the advanced](postgresql.md#additional-settings) **Save transaction borders** setting for the target endpoint.Data Transfer will open the transaction, apply the received events, but will only commit the transaction when it starts receiving next transaction data.

    Using the **Save transaction borders** setting may slightly reduce transfer performance but will prevent errors related to contraint violations.

* Disable constraints in the target database. Sometimes, constraints may be violated, e.g., when part of a transaction from the source database is applied in the target database. However, Data Transfer ensures eventual consistency, i.e., applying the second part of the transaction when there are no constraint violations.

### Error when dropping a table under the Drop cleanup policy {#drop-table-error}

Error message:

```text
ERROR: cannot drop table <table_name> because other objects depend on it (SQLSTATE 2BP01)
```

With the `Drop` cleanup policy, a transfer drops tables in multiple iterations:

1. The transfer tries to drop all tables one by one. Cascade delete is not used, as it may delete tables that are not included in the transfer. If a table cannot be dropped, for example, due to its links to external keys, an error occurs, but the transfer will continue dropping tables.
1. During the next iteration, the transfer will try to drop the remaining tables. If the blocking child tables were dropped in the previous iteration, the table linked to external keys will also be dropped. In this case, the error is fixed while Data Transfer is running and no other actions are required.
1. If the transfer does not drop any table during another iteration, table dropping stops. In which case:

   * The transfer will continue running if all tables were dropped.
   * The transfer will be aborted with an error if there are any tables left.

**Solution:**

* If child tables are not involved in other transfers and their transfer is not in conflict with the transfer goals, add these tables:

   * To the list of included tables in the source endpoint parameters.
   * To the list of objects to be transferred in the transfer parameters.

* Delete the blocking child tables in the target database manually.
* Use the `Truncate` cleanup policy.
* Recreate the target database.

   {% note warning %}

   This will lead to losing all data in the database.

   {% endnote %}

### Error when transferring tables with generated columns {#generated-columns}

Error message:

```text
ERROR: column "<column_name>" is a generated column (SQLSTATE 42P10)
```

The error may occur if a table containing generated columns is transferred from the source database. For example, if the generated column is an identifier column (`GENERATED ... AS IDENTITY`), the error will occur during data replication. If the generated column is a computable one, the error will occur regardless of the transfer type. Learn more about generated columns in [this PostgreSQL guide](https://www.postgresql.org/docs/current/ddl-generated-columns.html).

**Solution**: In the [source endpoint parameters](../source/postgresql.md#additional-settings), exclude from the transfer the tables containing generated columns.