PostgreSQL

Destination PostgreSQL #

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

I. Set up Connection Configuration #

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

    vi conf/conn/postgresql_dst.yaml
    
  2. Make the necessary changes as follows:

      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 
    
    • Make sure the specified user has CREATE TABLE privilege on the catalogs/schemas into which replicated tables should be created.

    • If you want Replicant to create catalogs/schemas for you on the target PostgresSQL system, then you also need to grant CREATE DATABASE/CREATE SCHEMA privileges to the user.

    • If this user does not have CREATE DATABASE privilege, then create a database manually with name io and grant all privileges for it to the user specified here. Replicant uses this database for internal checkpointing and metadata management.

      The database/schema of your choice on a different instance of your choice name can be configured using the metadata config feature. For more information, see Metadata Configuration.
      The socket-timeout-s parameter has been introduced in v22.02.12.16 and isn’t available in previous versions.

II. Set up Applier Configuration #

  1. From $REPLICANT_HOME, naviagte to the sample PostgreSQL applier configuration file:
    vi conf/dst/postgresql.yaml    
    
  2. Make the necessary changes as follows:
    snapshot:
     threads: 16 #Specify the maximum number of threads Replicant should use for writing to the target
    
     #If bulk-load is used, Replicant will use the native bulk-loading capabilities of the target database
     bulk-load:
       enable: true|false #Set to true if you want to enable bulk loading
       type: FILE|PIPE #Specify the type of bulk loading between FILE and PIPE
       serialize: true|false #Set to true if you want the generated files to be applied in serial/parallel fashion
    
       #For versions 20.09.14.3 and beyond
       native-load-configs: #Specify the user-provided LOAD configuration string which will be appended to the s3 specific LOAD SQL command
    

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