Example - Sync from PostgreSQL to Tacnode

Preparation

Network setup refers to Data Synchronization Network Connection

Permissions

Only full synchronization jobs are included. The permissions outlined below are granted. Replace ${schema1} and ${other_schema} with the schemas that require synchronization. ${user} refers to the individual managing the synchronization job.

postgres=# grant select on all tables in schema ${schema1},[${other_schema}] to ${user};
postgres=# grant usage on ${schema1},[${other_schema}]  to ${user};

Add a user with REPLICATION permissions for incremental synchronization jobs. Existing users cannot be granted these permissions because cloud vendors do not provide SuperUser accounts.

• Log in using a high-privilege account

psql postgres://${user}:${password}@${host}:5432/${your_database_name}

• Set up a CDC synchronization user:

CREATE USER ${cdc_user} WITH REPLICATION ENCRYPTED PASSWORD '${cdc_user_passord}';

• Grant permissions to cdc_user

postgres=# grant select on all tables in schema schema1,[other_schema] to ${cdc_user};
postgres=# grant usage on schema schema1,[other_schema] to ${cdc_user};

Incremental Synchronization Job Settings

wal_level = logical

• If using self-built services, adjust the wal_level setting in the postgresql.conf file and restart the service.

Establish a Subscription (PUBLICATION)

• Log in using a high-privilege account

psql -h ${host} -p 5432 -U ${user} "dbname=${your_database_name} replication=database" -W

• Set up a subscription

CREATE PUBLICATION datasync_pub FOR ALL TABLES;

or

CREATE PUBLICATION datasync_pub FOR TABLE ${your_schema}.${your_table}, ...;

Data Source Configuration

To set up the PostgreSQL data source, configure it using these connection properties:

• Host: Address of the PostgreSQL service
• Port: Typically 5432, this is the port for PostgreSQL services
• Username: The user with the necessary permissions
• Password: The password associated with the specified username

Default Node Mapping

The default mapping rules for mapping nodes are outlined below:

concat(SOURCE_DATABASE, '.', SOURCE_SCHEMA, '.', SOURCE_OBJECT)

Meaning: In Tacnode, PostgreSQL tables are represented as d.s.t, where d indicates the database name, s is the schema, and t denotes the table name.

Default Type Mapping

A leading "_" signifies an array.