Destination PostgreSQL #
The extracted replicant-cli
will be referred to as the $REPLICANT_HOME
directory in the proceeding steps.
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
-
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 nameio
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. 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.