PostgreSQL Table of Contents

Destination PostgreSQL #

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

Required permissions#

Make sure that the user being used for replication, has theCREATE TABLE privilege on the target catalogs or schemas where you want to replicate the tables to. Use the following command to grant the privileges:
```
GRANTCREATEONDATABASE<catalog_name>TO<replication_user>;
```
Replace the following:
- catalog_name: the catalog name in the target
- replication_user: the user being used for replication
In order to store Arcion’s replication metadata, you must ensure one of the following:
- Point to an external metadata database. For more information, seeMetadata configuration.
- Grant theCREATEDB privilege to the user being used for replication. This allows the user to create theio database. The user must also possess the privilege to create tables in theio database. Replicant uses thisio database to maintain internal checkpoint and metadata.
The following command assigns theCREATEDB privilege to a useralex:
```
ALTERUSER alexCREATEDB;
```
If the user does not haveCREATEDB privilege, then follow these two steps:
1. Create a database manually with the nameio:
```
CREATEDATABASE io;
```
2. Grant all privileges for theio database to that user:
```
GRANTALLONDATABASE ioTO alex;
```

I. Set up connection configuration#

To connect to your PostgreSQL target database, you have these two options:

Use a connection configuration file

You can specify your connection details to Replicant with a YAML connection configuration file. You can find a sample connection configuration filecloudsql_postgresql.yaml in the$REPLICANT_HOME/conf/conn directory.

type:POSTGRESQLhost:HOSTNAMEport:PORT_NUMBERdatabase:'DATABASE_NAME'username:'USERNAME'password:'PASSWORD'max-connections:30socket-timeout-s:60max-retries:10retry-wait-duration-ms:1000

Replace the following:

HOSTNAME: the hostname of the target PostgreSQL instance
PORT_NUMBER: the port number
DATABASE_NAME: the database name
USERNAME: the username of the user that connects to the PostgreSQL server
PASSWORD: the password associated withUSERNAME

Feel free to change the following parameter values as you need:

max-connections: the maximum number of connections Replicant opens in Cloud SQL instance.
max-retries: number of times Replicant retries a failed operation.
retry-wait-duration-ms: duration in milliseconds Replicant waits between each retry of a failed operation.
socket-timeout-s: the timeout value in seconds specifying socket read operations. A value of0 disables socket reads. This parameter is only supported for Arcion self-hosted CLI versions 22.02.12.16 and newer.

Important: Make sure thatmax_connections in PostgreSQL exceeds themax-connections parameter in the preceding connection configuration file.

Use a secrets management service

You can store your connection credentials in a secrets management service and tell Replicant to retrieve the credentials. For more information, seeSecrets management.

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 aMySQL-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, seeMapper configuration.

III. Set up Applier configuration#

To configure replication mode according to your requirements, specify your configuration in the Applier configuration file. You can find a sample Applier configuration filepostgresql.yaml in the$REPLICANT_HOME/conf/dst directory. For example:

You can configure PostgreSQL for operating in eithersnapshot orrealtime modes.

Configure`snapshot` mode#

For operating insnapshot mode, specify your configuration under thesnapshot section of the conifiguration file. For example:

snapshot:threads:16batch-size-rows:5_000txn-size-rows:1_000_000skip-tables-on-failures:falsemap-bit-to-boolean:falsebulk-load:enable:truetype:FILE# FILE or PIPE_traceDBTasks:trueuse-quoted-identifiers:true

Additional`snapshot` parameters#

map-bit-to-boolean

Tells Replicant whether to mapbit(1) andvarbit(1) data types from source toboolean on target.

Iftrue, Replicant mapsbit(1)/varbit(1) data types from source toboolean on target PostgreSQL. Iffalse, Replicant mapsbit(1)/varbit(1) data types from source tobit(1)/varbit(1) on target PostgreSQL.

Default:false.

For more information about the Applier parameters forsnapshot mode, seeSnapshot mode.

Configure`realtime` mode#

If you want to operate inreal time, use therealtime section to specify your configuration. For example:

realtime:threads:8txn-size-rows:10000batch-size-rows:1000skip-tables-on-failures:falseuse-quoted-identifiers:true

For more information about the configuration parameters forrealtime mode, seeRealtime mode.

Movatterモバイル変換

Destination PostgreSQL#