Version 24.3.9120

• Prerequisites
  • Minimum Requirements
  • Set Up PostgreSQL for Enhanced CDC
• Add the PostgreSQL Connector
• Authenticate to PostgreSQL
• Complete Your Connection

You can use the PostgreSQL connector from the CData Sync application to capture data from PostgreSQL and move it to any supported destination. To do so, you need to add the connector, authenticate to the connector, and complete your connection.

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

Prerequisites

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

Minimum Requirements

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

• PostgreSQL Version: Version 12 or later

  Note: Only databases with the UTF-8 character encoding are supported for the enhanced 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)

Set Up PostgreSQL for Enhanced CDC

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

1. Ensure that the wal_level parameter is set in your PostgreSQL database, as follows:

   1. Verify the current wal_level setting in PostgreSQL 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 PostgreSQL file, as shown below:

        # REPLICATION
        wal_level = logical   
      

   Note: Regardless of which method you use, you must restart the PostgreSQL database for the changes to take effect.

2. 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;
   

   Note: SUPERUSER privileges are required when you create a publication for all tables.

3. Create a logical replication slot for Sync by submitting the following SELECT statement:

   SELECT pg_create_logical_replication_slot('SlotName', 'pgoutput');
   

   Note: When you create the logical replication slot, you must use the pgoutput plugin.

Add the PostgreSQL Connector

To enable Sync to use data from PostgreSQL, you first must add the connector, as follows:

1. Open the Connections page of the Sync dashboard.

2. Click Add Connection to open the Select Connectors page.

3. Click the Sources tab and locate the PostgreSQL (Native) row.

4. Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the PostgreSQL (Native) connector. For more information about installing new connectors, see Connections.

Authenticate to PostgreSQL

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

• Connection Name - Enter a connection name of your choice.

• Host - Enter the host name or IP of the server that hosts the PostgreSQL database. The default server value is localhost.

• Port - Enter the port number of the server that hosts the PostgreSQL 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 PostgreSQL server. The default database is postgres.

• User - Enter the username that you use to authenticate to your PostgreSQL account.

• Password - Enter the password that you use to authenticate to your PostgreSQL account.

Complete Your Connection

To complete your connection:

1. Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)

2. Click Create & Test to create your connection.