PostgreSQL (Native) - CData Sync Documentation

This connection can be used only as a source for the Change Data Capture job type.

Prerequisites

Before you add and set up the source connector, you need to set up for change data capture (CDC), as explained in the following sections.

Before you set up for CDC, ensure that you have the following minimum requirements:

Version: Version 12 or later
Only databases with the UTF-8 character encoding are supported for CDC. Strings that contain extended ASCII characters might not be processed correctly during the incremental replication.
User Privileges: LOGIN and REPLICATION (the minimum privileges that are required for connecting to PostgreSQL and reading the replication slot)

Perform the following steps to enable logical replication, specify which tables to replicate, and create a logical replication slot for .

Ensure that the wal_level parameter is set in your database, as follows:
1. Verify the current wal_level setting in by submitting the following query:
  SHOW wal_level;
2. Set the wal_level parameter to enable logical replication in either of the following ways: Method 1: Execute the following command: ALTER SYSTEM SET wal_level = logical;
  
  Method 2: Set wal_level = logical manually in your file, as shown below:
  # REPLICATION wal_level = logical
Regardless of which method you use, you must restart the database for the changes to take effect.
Create a publication to specify which tables to replicate.
```
CREATE PUBLICATION cdatasync_pub1 FOR TABLE table1, table2, ...;
```
You can also create a publication for all tables in the database, as shown below.
```
CREATE PUBLICATION cdatasync_pub1 FOR ALL TABLES;
```
SUPERUSER privileges are required when you create a publication for all tables.
Create a logical replication slot for by submitting the following SELECT statement:
```
SELECT pg_create_logical_replication_slot('SlotName', 'pgoutput');
```
When you create the logical replication slot, you must use the pgoutput plugin.

After you add the connector, you need to set the required properties.

Host: Enter the host name or IP of the server that hosts the database. The default server value is localhost.
Port: Enter the port number of the server that hosts the database. The default port value is 5432.
Database: Enter the name of the database that to which you want to connect when you connect to the server. The default database is postgres.
User: Enter the username that you use to authenticate to your account.
Password: Enter the password that you use to authenticate to your account.

To complete your connection:

Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)
Click Create & Test to create your connection.

⌘I