Transferring data from a PostgreSQL source endpoint

1. If not planning to use Cloud Interconnect or VPN for connections to an external cluster, make such cluster accessible from the Internet from IP addresses used by Data Transfer.

   For details on linking your network up with external resources, see this concept.

2. Create a user account the transfer will use to connect to the source:

   • For the Snapshot transfer type, create a user with the following command:

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

   • For Replication and Snapshot and increment transfers, create a user with the REPLICATION privilege by running this command:

     CREATE ROLE <username> WITH REPLICATION LOGIN ENCRYPTED PASSWORD '<password>';
     

3. Grant the new user the SELECT privilege for all the database tables involved in the transfer and the USAGE privilege for these tables' schemas:

   GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
   GRANT USAGE ON SCHEMA <schema_name> TO <username>;
   

4. Grant the new user the privileges for the service table (__consumer_keeper and __data_transfer_mole_finder) schema defined by the endpoint parameter if the endpoint is going to be used for Replication or Snapshot and increment transfers:

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

5. Configure the number of user connections to the database.

6. Install and enable the wal2json extension.

   Installation

   • Linux

     1. Add the PostgreSQL official repository for your distribution.
     2. Update the list of available packages and install wal2json for your PostgreSQL version.

   • Windows 10, 11

     1. If you do not have Microsoft Visual Studio installed yet, download and install it. To build the wal2json extension, the Community Edition is sufficient. During installation, select the following components:

        • MSBuild
        • MSVC v141 x86/x64 build tools
        • C++\CLI support for v141 build tools
        • MSVC v141 - VS 2017 C++ x64\x86 build tools
        • MSVC v141 - VS 2017 C++ x64\x86 Spectre-mitigated libs
        • The latest version of the Windows SDK for the active OS version
        • Other dependencies that are installed automatically for selected components

        Take note of the version number of the installed Windows SDK. You will need it while setting the wal2json build parameters.

     2. Download the wal2json source code from the project page.

     3. Unpack the archive with the source code to the C:\wal2json\ folder.

     4. Go to C:\wal2json.

     5. Within one PowerShell session, make changes to the wal2json.vcxproj file as follows:

        • Replace the C:\postgres\pg103 lines with the path to the folder housing your installed PostgreSQL version, for example:

          (Get-Content .\wal2json.vcxproj).replace('C:\postgres\pg103', 'C:\PostgreSQL\14') | `
           Set-Content .\wal2json.vcxproj
          

        • Replace the /MP build parameter with /MT, for example:

          (Get-Content .\wal2json.vcxproj).replace('/MP', '/MT') | Set-Content .\wal2json.vcxproj
          

        • Specify the version number of the installed Windows SDK in <WindowsTargetPlatformVersion>:

          (Get-Content .\wal2json.vcxproj).replace('<WindowsTargetPlatformVersion>8.1', '<WindowsTargetPlatformVersion><installed_Windows_SDK_version>') | `
           Set-Content .\wal2json.vcxproj
          

        1. Enter the value of the extension variable required for building wal2json. For example, for Visual Studio Community Edition 2022:

           $VCTargetsPath='C:\Program Files\Microsoft Visual Studio\2022\Comminuty\MSBuild\Microsoft\VC\v150'
           

        2. Run the build:

           & 'C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\MSBuild.exe' /p:Configuration=Release /p:Platform=x64
           

        3. Copy the wal2json.dll file from the build/release folder to the lib folder of your PostgreSQL version.

   Configuration

   1. In the postgresql.conf file, set the value of the wal_level parameter to logical:

      wal_level = logical
      

   2. Restart PostgreSQL.

7. If the replication source is a cluster, install and enable the pg_tm_aux extension on its hosts. This will allow replication to continue even after changing the master host. In some cases, a transfer may end in an error after you replace a master in your cluster. For more information, see Troubleshooting.

8. To transfer tables without primary keys for the Replication and Snapshot and increment transfer types, you must add the REPLICA IDENTITY:

   • Do not transfer tables without primary keys. For this purpose, add them to the list of excluded tables in source endpoint settings.
   • Add the replica ID to tables without primary keys:

     • For tables with an index, set REPLICA IDENTITY by unique key:

       ALTER TABLE MY_TBL REPLICA IDENTITY USING INDEX MY_IDX;
       

     • For tables without an index, change REPLICA IDENTITY:

       ALTER TABLE MY_TBL REPLICA IDENTITY FULL;
       

       In this case, Data Transfer will treat such tables as tables where the primary key is a composite key that includes all columns of the table.

   If there are no primary keys in a table, logical replication will not include any changes in the rows (UPDATE or DELETE).

   • If, while trying to transfer tables from PostgreSQL to PostgreSQL, you do not exclude a table without primary keys on the transfer source, the following error message will be returned:

      failed to run (abstract1 source): Cannot parse logical replication message: failed to reload schema: primary key check failed: Tables: n / N check failed: "public"."MY_TBL": no key columns found
     

   • If, while trying to transfer tables from PostgreSQL to a different database, you add a table without primary keys, the following error message will be returned:

     failed to run (abstract1 source): Cannot parse logical replication message: failed to reload schema: primary key check failed: Tables: n / N check failed:
     "public"."MY_TBL": no key columns found
     

9. Disable the transfer of external keys when creating a source endpoint. Recreate them once the transfer is completed.

10. If a database contains tables with generated columns, such tables will not be migrated and the transfer will end with an error. For more information, see Troubleshooting. To make sure the transfer is running properly when migrating a database with such tables, add them to the list of excluded tables in the source endpoint settings.

11. Find and terminate DDL queries that are running for too long. To do this, make a selection from the PostgreSQL pg_stat_activity system table:

    SELECT NOW() - query_start AS duration, query, state
    FROM pg_stat_activity
    WHERE state != 'idle' ORDER BY 1 DESC;
    

    This will return a list of queries running on the server. Pay attention to queries with a high duration value.

12. Deactivate trigger transfer at the transfer initiation stage and reactivate it at the completion stage (for the Replication and the Snapshot and increment transfer types). For more information, see the description of additional endpoint settings for the PostgreSQL source.

13. To enable parallel data reads from the table, set its primary key to serial mode.

    Then specify the number of workers and threads in the transfer parameters under Runtime environment.

14. If replication via Patroni is configured on the source, add an ignore_slots section to the source configuration:

    ignore_slots:
      - database: <database>
        name: <replication_slot>
        plugin: wal2json
        type: logical
    

    Where:

    • database: Name of the database the transfer is configured for.
    • name: Replication slot name.

    The database and the replication slot names must match the values specified in the source endpoint settings. By default, the replication slot name is the same as the transfer ID.

    Otherwise, the start of the replication phase will fail:

    Warn(Termination): unable to create new pg source: Replication slotID <replication_slot_name> does not exist.
    

15. Configure WAL monitoring. Replication and Snapshot and increment transfers use logical replication. To perform the replication, the transfer creates a replication slot where slot_name matches the transfer ID, which you can get by selecting the transfer in the list of your transfers. Your WAL may grow due to different reasons: a long-running transaction or a transfer issue. Therefore, we recommend you to configure WAL monitoring on the source side.

    1. Set up alerts as described in the disk usage recommendations.

    2. Set the maximum WAL size. This feature is available starting with PostgreSQL version 13.

    3. You can monitor the current slot size by running this DB query with the correct slot_name, which matches the transfer ID:

       SELECT slot_name, pg_size_pretty(pg_current_wal_lsn() - restart_lsn), active_pid, catalog_xmin, restart_lsn, confirmed_flush_lsn
       FROM pg_replication_slots WHERE slot_name = '<transfer_ID>'
       

• Endpoint type: postgres-source.

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

• Endpoint type: postgres_source.

• 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 the configuration file example:

resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
  name = "<endpoint_name>"
  settings {
    postgres_source {
      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>"
      }
      <additional_endpoint_settings>
    }
  }
}

For more information, see the Terraform provider documentation.

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

• Endpoint type: postgres-source.

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

  Warning

  If no certificate is added, the transfer may fail with an error.

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

• Endpoint type: postgres_source.

• security_groups: Security groups for network traffic.

  Security group rules apply to a transfer. They allow opening up network access from the transfer VM to the VM with the database. For more information, see Networking in Yandex Data Transfer.

  Security groups must belong to the same network as the subnet_id subnet, if the latter is specified.

  Note

  In Terraform, it is not required to specify a network for security groups.

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

  Warning

  If no certificate is added, the transfer may fail with an error.

• on_premise.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.

• password.raw: Password in text form.

Here is the configuration file example:

resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
  name = "<endpoint_name>"
  settings {
    postgres_source {
      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>"
      }
      <additional_endpoint_settings>
    }
  }
}

For more information, see the Terraform provider documentation.

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

        Warning

        If no certificate is added, the transfer may fail with an error.

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

• --include-table: List of included tables. Only the data from the tables listed here will be transferred.

  When you add new tables when editing an endpoint used in Snapshot and increment or Replication transfers with the Replicating status, the data history for these tables will not get uploaded. To add a table with its historical data, use the List of objects for transfer field in the transfer settings.

• --exclude-table: List of excluded tables. Data from the listed tables will not be transferred.

  The lists include the name of the schema that describes the DB contents, structure, and integrity constraints, as well as the table name. Both lists support expressions in the following format:

  • <schema_name>.<table_name>: Full table name.
  • <schema_name>.*: All tables in the specified schema.

  Warning

  The service does not transfer custom data types if the endpoint specifies a list of included or excluded tables for the source. If this is the case, please transfer your custom data types manually.

• --slot-lag-limit: Maximum size of write-ahead log kept in the replication slot. If exceeded, the replication process is stopped and the replication slot is deleted. The default value is 50 GB.

• --service-schema: Name of the DB schema for service tables.

• Schema transfer settings:

  • --transfer-before-data: At the initial stage of the transfer.
  • --transfer-after-data: At the final stage of the transfer.

• include_tables: List of included tables. Only the data from the tables listed here will be transferred.

  When you add new tables when editing an endpoint used in Snapshot and increment or Replication transfers with the Replicating status, the data history for these tables will not get uploaded. To add a table with its historical data, use the List of objects for transfer field in the transfer settings.

• exclude_tables: List of excluded tables. Data from the listed tables will not be transferred.

  The lists include the name of the schema that describes the DB contents, structure, and integrity constraints, as well as the table name. Both lists support expressions in the following format:

  • <schema_name>.<table_name>: Full table name.
  • <schema_name>.*: All tables in the specified schema.

  Warning

  The service does not transfer custom data types if the endpoint specifies a list of included or excluded tables for the source. If this is the case, please transfer your custom data types manually.

• slot_gigabyte_lag_limit: Maximum size of write-ahead log kept in the replication slot. If exceeded, the replication process is stopped and the replication slot is deleted. The default value is 50 GB.

• service_schema: Name of the DB schema for service tables.

• object_transfer_settings: Schema transfer settings:

  • sequence: Sequences.
  • sequence_owned_by: Custom sequences.
  • table: Tables.
  • primary_key: Primary keys.
  • fk_constraint: Foreign keys.
  • default_values: Default values.
  • constraint: Constraints.
  • index: Indexes.
  • view: Views.
  • function: Functions.
  • trigger: Triggers.
  • type: Types.
  • rule: Rules.
  • collation: Sorting rules.
  • policy: Policies.
  • cast: Type casts.

  You can specify one of the following values for each entity:

  • BEFORE_DATA: Transferring when activating the transfer.
  • AFTER_DATA: Transferring when deactivating the transfer.
  • NEVER: No tansfer.

For more information, see the Terraform provider documentation.

• includeTables: List of included tables. Only the data from the tables listed here will be transferred.

  When you add new tables when editing an endpoint used in Snapshot and increment or Replication transfers with the Replicating status, the data history for these tables will not get uploaded. To add a table with its historical data, use the List of objects for transfer field in the transfer settings.

• excludeTables: List of excluded tables. Data from the listed tables will not be transferred.

  The lists include the name of the schema that describes the DB contents, structure, and integrity constraints, as well as the table name. Both lists support expressions in the following format:

  • <schema_name>.<table_name>: Full table name.
  • <schema_name>.*: All tables in the specified schema.

  Warning

  The service does not transfer custom data types if the endpoint specifies a list of included or excluded tables for the source. If this is the case, please transfer your custom data types manually.

• slotByteLagLimit: Maximum size of write-ahead log kept in the replication slot. If exceeded, the replication process is stopped and the replication slot is deleted. The default value is 50 GB.

• serviceSchema: Name of the DB schema for service tables.

• objectTransferSettings: Settings for transferring the schema at the initial and final stages of the transfer (BEFORE_DATA and AFTER_DATA, respectively).