PostgreSQL as a Source

Overview

Syniti Replicate supports use of PostgreSQL as source connection for:

• Refresh replications

• Mirroring replications. For transactional replication using PostgreSQL as a source system, Syniti Replicate offers the Log Server Agent or Triggers -based replication options.

Enable a Refresh replication from PostgreSQL

Refresh replication is one-way snapshot process

Permissions

The following are the user permissions required using PostgreSQL as source in Refresh (Snapshot) mode:

• Authority to connect a database

• Authority to select tables

CREATE USER <replicate_user> WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE your_database TO <replicate_user>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <replicate_user>;

Enable Mirroring to Run from Replicate Preview

1. Create a read-only user

These steps create a dedicated read-only user for replicating data. Alternatively, you can use an existing Postgres user in your database. Execute the following command to create a new user:

CREATE USER <user_name> PASSWORD 'your_password';

You can now provide this user with read-only access to relevant schemas and tables.

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

Re-run the commands above for each schema you expect to replicate data from.

2. Grant replication permissions to read-only users

Grant Replication permissions to existing users or the new user created in Step 2.1:

ALTER USER <user_name> REPLICATION;

3. Update PostgreSQL configuration file

Replicate Preview supports PostgreSQL version 16.1 or later as a source database for mirroring. It uses the PostgreSQL replication slot feature from the postgresql.conf file by requiring that every connection to PostgreSQL from Replicate Preview use a dedicated replication slot.

Edit the postgresql.conf file (usually located on PostgreSQL/<version>/data ) to set up the environment for replication using Syniti Replicate:

wal_level = logical 
max_replication_slots = 3 
track_commit_timestamp = on

Where,

• wal_level controls how much information is written to the WAL (Write-Ahead Log or PostgreSQL’s transaction log). Set it to logical to enable logical decoding, which is required when using PostgreSQL as a source for change-data capture in Replicate Preview. This setting can only be changed at server start.

• max_replication_slots defines how many replication slots are available to all applications. Syniti Replicate needs one slot per connection. For a single connection, a maximum of 3 is sufficient; increase this if you need more connections to the same database.

For more information on editing these settings, refer to the PostgreSQL documentation.

Restart the PostgreSQL database service so that your changes take effect.

4. Create a replication slot on your PostgreSQL database

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

Enable a Log-based replication from PostgreSQL

Uses a Windows service and a Log Server component to query the PostgreSQL log for increased performance when dealing with large amounts of data.

Enable Transactional Replication wizard or the Manage Transactional Log Settings dialog

PostgreSQL System Settings

Syniti Replicate supports PostgreSQL versions 11 and above as a source database for mirroring. It takes advantage of the PostgreSQL replication slot feature by requiring that every connection to PostgreSQL from Syniti Replicate use a dedicated replication slot. The PostgreSQL documentation provides an in-depth explanation of replication slots, but briefly, a single slot represents an ordered stream of changes that occur on a specific database on the origin server. Note that if you enable a replication slot from Syniti Replicate without using it, the slot will consume resources and prevent the database cleanup functionality to purge old records from the transaction log, because they do not appear as consumed by an existing slot. Therefore, it is important to remove replication slots from the database by removing the Syniti Replicate connection if it is not used in replication.

Log Settings

Decoding Plugin: Plugin Name

The default plugin is called "test_decoding" and is normally installed with every PostgreSQL installation. To verify availability, check the location where the decoding plugins are normally installed:

If the plugin is missing, you can either install it using the Postgres installation files, or request a file from the technical support team via the Help Center to install the dbm_decoding plugin. Note that if you are using PostgreSQL as a cloud database, you need to use the test_decoding plugin.

Replication Slot: Use Existing Slot

Replication slots are channels that you open on the database to allow Syniti Replicate to receive data changes from the log. While the default is to use a new slot specifically for a Syniti Replicate connection, it is possible to reuse an existing slot if it is not consumed by any other connection. A replication slot has to be uniquely assigned to a single Syniti Replicate connection.

Replication Slot: Add New Slot

Syniti Replicate needs to define a "replication_slot" for every connection that it uses to replicate from PostgreSQL. This option allows you to set up a new slot from Syniti Replicate. However, the new slot will be added only if the maximum number of slots has not been exceeded. This value is set in the postgresql.conf file.  Be aware that if slots are created then left unused, they still consume resources, so you should always manage replication slots carefully.

Verify (available only in the Manage Transactional Log Settings dialog box)

If you have made changes in the dialog, click Verify to make sure that the replication slots are set up correctly.

For more information, check the Log Server Agent Settings.

Setup on PostgreSQL (Windows environment)

As we mentioned previously, Syniti Replicate uses the test_decoding.dll (test_decoding.so) that is included with PostgreSQL. However, it is possible to obtain a specialized library for enhanced performance, dbm_decoding.dll, that reduces the size of the stream sent through the network:

• Contact the technical support team via the Help Center to request the library files developed for PostgreSQL replications.

• Extract the files from the zip file to a folder that is accessible to the PostgreSQL lib folder.

• If using Microsoft Windows, rename the appropriate DLL to dbm_decoding.dll. If using LINUX, rename the .so file to dbm_decoding.so.

• Copy the file to the lib folder in your PostgreSQL installation.

Edit the postgresql.conf file (usually located on PostgreSQL/<version>/data ) to set up the environment for replication using Syniti Replicate:

wal_level = logical 
max_replication_slots = 3 
track_commit_timestamp = on

Where: wal_level sets the amount of information that is written to the WAL (Write-Ahead Logging, or Postgres transaction log). The value logical represents the level of detail necessary to support logical decoding, required for using PostgreSQL as a source database for replicating data changes only in Syniti Replicate. This parameter can only be set at server start. max_replication_slots sets the maximum number of replication slots available to all applications. Syniti Replicate requires a “replication_slot” for every connection that it uses to replicate from PostgreSQL. A maximum number of 3 is reasonable if you are defining one single connection to the database. If you require additional connections to the same PostgreSQL database, the maximum number of slots should be raised accordingly. Details on settings in the configuration file can be found in the PostgreSQL documentation.

Restart the PostgreSQL database service so that your changes take effect.

Permissions

Those are the user permissions required using PostgreSQL as source in Mirroring mode using Log Server Agent:

The user setting up and running replications is a superuser or a user with replication role. If you are not using an account with  superuser role, you can create your user with those permissions:

CREATE USER <cdc_user> WITH PASSWORD 'your_password';
ALTER ROLE <cdc_user> WITH REPLICATION;
ALTER ROLE <cdc_user> WITH LOGIN;
GRANT CONNECT ON DATABASE <database_name> TO <cdc_user>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <cdc_user>;
GRANT USAGE ON SCHEMA <schema_name> TO <cdc_user>;

Enable a Triggers-based replication from PostgreSQL

A database trigger is code that is automatically executed in response to certain events on a database table. To define a trigger-based replication (mirroring or synchronization), you need to provide information in the Source and/or Target Connection wizards so that triggers can be created to log table changes for replication.

For each table involved in the replication, Syniti Replicate creates 3 triggers in the source table that fire when a specific event occurs on a record:

• INSERT trigger which fires when a new record is being inserted in the table

• UPDATE trigger which fires when a record is modified

• DELETE trigger which fires when a record is deleted

If the replication is later deleted, the triggers are removed by Syniti Replicate. However, note that if you change a replication from mirroring to refresh, the triggers on the source table are not deleted. All transactions will continue to be recorded in the log tables. If you are not planning to reset the replication to mirroring, it is better to delete the replication, so that the triggers are removed, and create a new refresh replication.

Permissions

This section includes information on the minimum permissions needed to set up and run mirroring where triggers are used to identify changed data.

You can use an owner or superuser user. If you do not want to transfer ownership, an alternative is to create a custom user:

CREATE USER <your_user> WITH PASSWORD 'your_pwd';
GRANT CONNECT ON DATABASE <database_name> TO <your_user>;
GRANT SELECT ON ALL TABLES IN SCHEMA <your_schema> TO <your_user>;
GRANT USAGE ON SCHEMA <your_schema> TO <your_user>;
GRANT CREATE ON SCHEMA <your_schema> TO <your_user>;
GRANT USAGE, UPDATE ON ALL SEQUENCES IN SCHEMA <your_schema> TO <your_user>;
GRANT TRIGGER ON ALL TABLES IN SCHEMA <your_schema> TO <your_user>;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <your_schema> TO <your_user>;

If additional information for Triggers-based replication is required you can check this section.

Limitations in Mirroring Mode

• PostgreSQL versions 10 and lower are not supported as a source for mirroring.

• No DDL (schema changes) operations are supported, only INSERT, UPDATE and DELETE.

• In updates, only the after record of the new changes is logged. No before image is available

• The functions used to create and retrieve changes from the replication slots can only be executed by a superuser or a user with replication role.

• Replicated tables need to have primary keys defined. If a table does not have a primary key, DELETE and UPDATE record operations are ignored.

• A mechanism to get the last replication id by datetime does not exist (even though a datetime is available).

• Timestamp with a time zone type column is not supported.

• An attempt to update a primary key segment is ignored because the target identifies the update as one without updating any rows. The results of updating a primary key in PostgreSQL are unpredictable, so no records are written to the exceptions table.

• Replication of multiple tables with the same name but where each name has a different case is unsupported.

• TRUNCATE operations on the source are not supported