PostgreSQL Environment Preparation (Linux)

Before synchronizing real-time data from a PostgreSQL database using the Data Pipeline function of FineDataLink deployed in a Linux environment, you need to configure the data source by referring to this document.

Note:

1. Before synchronizing real-time data from a PostgreSQL database using FineDataLink deployed in a Windows environment,  you need to prepare the environment. For details, see PostgreSQL Environment Preparation (Windows).

2. For details about CDC principles and capabilities, see CDC Principle and Support.

Note: Synchronizing views is not supported currently.

Confirming the Database Version

Check if the version is supported by Data Pipeline.

Currently, only non-read-only single-instance PostgreSQL databases of 9.4 and later versions are supported. For details, see Overview of Data Pipeline.

Modifying REPLICA IDENTITY

This attribute determines which fields are logged when data is updated or deleted.

• DEFAULT: The log records old values of primary key columns when for UPDATE or DELETE operations.

• NOTHING: The log does not record old values for UPDATE or DELETE operations

• FULL: The log records old values of all columns for UPDATE or DELETE operations.

• INDEX Index name: The log records old values of columns defined by Index name for UPDATE and DELETE      operations.

Use the following command to modify the table setting.

Note: Perform this operation on all tables requiring real-time synchronization.

alter table Schema name.Table name REPLICA IDENTITY FULL

1. Log in to the fdl database using the default account postgres. In the following command, -U specifies the username, -d specifies the database, -h specifies the host, and -p specifies the port.

psql -U postgres -d fdl -h 192.168.101.119 -p 5432

Enter the password to log in to the database.

2. Modify the table settings. For example, enter the following statement to modify the settings of the Product_Name_Dimension table in the Public schema.

Installing wal2json

Distributions Based on the YUM Package Manager

• PostgreSQL 11 and later releases

The official repository of wal2json of 16 and later versions is only available in Red Hat Enterprise Linux (RHEL) of 8 and later versions. For details, see Packages.

For systems of different versions, you need to choose corresponding URLs when executing yum install. See the following table.

1. Update the repository files to refresh the list of installable wal2json packages.

sudo yum install -y Request URL

2. Use the following command to see the installable wal2json packages. 

yum list wal2json*

3. Select a wal2json package based on the PostgreSQL database version and the Linux kernel version.

Use the following command to check the installed database version.  

rpm -qa | grep postgresql

Use the following command to check the Linux kernel version.   

cat /proc/version

This document uses the wal2json_12.x86_64 package as an example. The installation command is shown as follows.     

yum install wal2json_12.x86_64

4. Use the following command to view the installed wal2json packages after installation.  

yum list wal2json*

• PostgreSQL of versions between 9.5 and 11 (exclusive)

1. Configure the image address for low-version databases. The following content takes PostgreSQL 9.6 as an example.

cd /etc/yum.repos.d
vi pgdg-96.repo

Add the following content to the file.

[pgdg90]
name=PostgreSQL 9.6 RPMs for RHEL/Centos 7
baseurl=https://yum-archive.postgresql.org/9.6/redhat/rhel-7-x86_64
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Save modifications and exit.

2. Use the following command to see the installable wal2json packages. 

yum list wal2json*

3. Select a wal2json package based on the PostgreSQL database version and the Linux kernel version.

Log in to the PostgreSQL database and use the SQL statement SELECT version(); to check the database version.                                                       

After exiting the database, view the Linux kernel version using the following command. 

cat /proc/version

This document uses wal2json96.x86_64 as an example. The installation command is shown as follows. 

yum install wal2json96.x86_64

4. Use the following command to view the installed wal2json packages after installation. 

 yum list wal2json*

Distributions Based on the APT Package Manager

1. Add a repository.

Create a file using the following command.

/etc/apt/sources.list.d/pgdg.list

Add the following content to that file.

deb http://apt.postgresql.org/pub/repos/apt/ Distribution name-pgdg main

The following table describes the correspondence between distribution names and versions.

For example, for Debian 12, the following content needs to be added.

deb http://apt.postgresql.org/pub/repos/apt/ bookworm-pgdg main

2. Add a repository key.

Execute the following command.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

3. Install wal2json.

Execute the following command.

sudo apt install postgresql-16-wal2json

Install the wal2json package corresponding to your PostgreSQL version. For example, use postgresql-16-wal2json for PostgreSQL 16 and postgresql-17-wal2json for PostgreSQL 17.

Modifying the Configuration File

Go to the data folder in the PostgreSQL installation directory, find postgresql.conf and pg_hba.conf, and modify the two files.

1. Modify PostgreSQL replication slot configuration.

In this example, the postgresql.conf file is located in /var/lib/pgsql/12/data. Edit the file using the following command.

 vi /var/lib/pgsql/12/data/postgresql.conf

Replace the path with the actual path. Append the following settings to the end of the file.

Note: You can also locate the following configuration entry in the code, remove the comment, and modify corresponding values.

shared_preload_libraries = 'wal2json'
 wal_level = logical
 max_wal_senders = 10
 max_replication_slots = 10

2. Set the Replication permission of the user account.

In this example, the pg_hba.conf file is located in /var/lib/pgsql/12/data. Edit the file using the following command. 

vi /var/lib/pgsql/12/data/pg_hba.conf

Replace the path with the actual path. Remove the number signs (#) from the two lines before the content highlighted by the red box in the following figure.

Restarting the Database

Adjust the command according to your PostgreSQL version.

systemctl restart postgresql-12

Confirming the User Permission

The above steps are performed using the default user postgres. If you use another user account to configure the database, ensure the user account belongs to roles REPLICATION and LOGIN, and has SELECT permission on the tables to be synchronized.

1. Create a user that belongs to roles REPLICATION and LOGIN.

Note:

Replace wendy and 123456 in the following command with your username and password.

CREATE USER wendy WITH REPLICATION LOGIN PASSWORD '123456';

2. Grant SELECT permission on all tables within a specified schema to the user.

Note:

Replace public and wendy in the following command with your schema name and username.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO wendy;

3. Grant Replication and Login permission to the user.

Go to the data folder in the installation directory and check the configuration file pg_hba.conf to ensure that the user has permission. For details about permission setting steps, see the "Modifying the Configuration File" section of this document.

Confirming Permission Availability

Enter the following command. If you receive a message indicating that the replication slot name slot_test already exists, the permission is available.

select * from pg_create_logical_replication_slot('slot_test', 'wal2json');

After you configure the database, you can configure the data source in FineDataLink.

Configure the data connection to source and target databases in FineDataLink before configuring pipeline tasks for real-time synchronization. This will allow you to control synchronization tasks by selecting the data source name during the task configuration process. For details, see Data Source Creation and Management.

Configure pipeline task. For details, see Pipeline Task Configuration.