PostgreSQL

Destination PostgreSQL #

The extracted replicant-cli will be referred to as the $REPLICANT_HOME directory in the following steps.

Required permissions #

  • Make sure that the specified user has CREATE TABLE privilege on the catalogs or schemas where you want to replicate tables to.

  • To create catalogs or schemas on the target PostgresSQL system, you must grant CREATE DATABASE or CREATE SCHEMA privileges respectively to the user.

  • If the user does not have CREATE DATABASE privilege, then follow these two steps:

    1. Create a database manually with the name io.
    2. Grant all privileges for the io database to that user.

    Replicant uses this io database to maintain internal checkpoint and metadata.

I. Set up Connection Configuration #

  1. From $REPLICANT_HOME, navigate to the sample PostgreSQL connection configuration file:

    vi conf/conn/postgresql_dst.yaml

  2. If you store your connection credentials in AWS Secrets Manager, you can tell Replicant to retrieve them. For more information, see Retrieve credentials from AWS Secrets Manager.

    Otherwise, you can put your credentials like usernames and passwords in plain form like the sample below:

    type: POSTGRESQL

host: localhost #Replace localhost with your PostgreSQL host
port: 5432  #Replace the 57565 with the port of your host

database: 'tpch' #Replace tpch with your database name
username: 'replicant' #Replace replicant with the username of your user that connects to your PostgreSQL server
password: 'Replicant#123' #Replace Replicant#123 with your user's password

max-connections: 30 #Specify the maximum number of connections Replicant can open in PostgreSQL
socket-timeout-s: 60 #The timeout value for socket read operations. The timeout is in seconds and a value of zero means that it is disabled.
max-retries: 10 #Number of times any operation on the system will be re-attempted on failures.
retry-wait-duration-ms: 1000 #Duration in milliseconds replicant should wait before performing then next retry of a
    Important: Make sure that the max_connections in PostgreSQL is greater than the max_connections in the preceding connection configuration file.

    The socket-timeout-s parameter is only supported for versions 22.02.12.16 and newer.

II. Configure mapper file (optional) #

If you want to define data mapping from source to your target PostgreSQL, specify the mapping rules in the mapper file. The following is a sample mapper configuration for a MySQL-to-PostgreSQL pipeline:

rules:
  [tpch, public]:
    source:
    - [tpch]

For more information on how to define the mapping rules and run Replicant CLI with the mapper file, see Mapper Configuration.

III. Set up Applier Configuration #

  1. From $REPLICANT_HOME, naviagte to the sample PostgreSQL Applier configuration file:

    vi conf/dst/postgresql.yaml

  2. The configuration file has two parts:

    • Parameters related to snapshot mode.
    • Parameters related to realtime mode.

    For snapshot mode, below is a sample configuration:

    snapshot:
  threads: 16
  batch-size-rows: 5_000
  txn-size-rows: 1_000_000
  skip-tables-on-failures: false

  map-bit-to-boolean: false

  bulk-load:
    enable: true
    type: FILE # FILE or PIPE

  _traceDBTasks: true
  use-quoted-identifiers: true

    • map-bit-to-boolean: Tells Replicant whether to map bit(1) and varbit(1) data types from Source to boolean on Target:

      • true: map bit(1)/varbit(1) data types from Source to boolean on Target PostgreSQL
      • false: map bit(1)/varbit(1) data types from Source to bit(1)/varbit(1) on Target PostgreSQL

      Default: false.

    If you want to operate in realtime mode, you can use the realtime section to specify your configuration. For example:

    realtime:
  threads: 8
  txn-size-rows: 10000
  batch-size-rows: 1000
  skip-tables-on-failures : false

  use-quoted-identifiers: true

For a detailed explanation of configuration parameters in the applier file, see Applier Reference.