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
orCREATE SCHEMA
privileges respectively to the user. -
If the user does not have
CREATE DATABASE
privilege, then follow these two steps:- Create a database manually with the name
io
. - Grant all privileges for the
io
database to that user.
Replicant uses this
io
database to maintain internal checkpoint and metadata. - Create a database manually with the name
I. Set up Connection Configuration #
-
From
$REPLICANT_HOME
, navigate to the sample PostgreSQL connection configuration file:vi conf/conn/postgresql_dst.yaml
-
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 themax_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 #
-
From
$REPLICANT_HOME
, naviagte to the sample PostgreSQL Applier configuration file:vi conf/dst/postgresql.yaml
-
The configuration file has two parts:
- Parameters related to snapshot mode.
- Parameters related to realtime mode.
Parameters related to snapshot 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 mapbit(1)
andvarbit(1)
data types from Source toboolean
on Target:true
: mapbit(1)
/varbit(1)
data types from Source toboolean
on Target PostgreSQLfalse
: mapbit(1)
/varbit(1)
data types from Source tobit(1)
/varbit(1)
on Target PostgreSQL
Default:
false
.
Parameters related to realtime mode #
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.