PostgreSQL Database Setup (On Premise Installations)

Syniti Data Replication supports PostgreSQL versions 9.5 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 DR 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 DR 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 DR connection if it is not used in replication.

Your PostgreSQL environment should be set up as follows:

Use a PostgreSQL database that is version 9.4.x or later.
Grant superuser permissions for the user account specified for the PostgreSQL source database. The account should have rds_superuser role and the rds_replication role. The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.

Add the IP address of the PostgreSQL server to the pg_hba.conf configuration file.

Set the following parameters and values in the postgresql.conf configuration file:
- Set wal_level = logical
- Set max_replication_slots to a value greater than 1.
  The max_replication_slots value should be set according to the number of tasks that you want to run. For example, to run five tasks you need to set a minimum of five slots. Slots open automatically as soon as a task starts and remain open even when the task is no longer running. You need to manually delete open slots.
- Set max_wal_senders to a value greater than 1.
  The max_wal_senders parameter sets the number of concurrent tasks that can run.
- Set wal_sender_timeout =0
  The wal_sender_timeout parameter terminates replication connections that are inactive longer than the specified number of milliseconds. Although the default is 60 seconds, we recommend that you set this parameter to zero, which disables the timeout mechanism.
- Do not allow idle transactions to end by setting the parameter idle_in_transaction_session_timeout appropriately in PostgreSQL versions 9.6 and later. Some transactions may remain idle for some time during replication so it is critical that ended transactions do not cause the replication to fail.
Set the rds.logical_replication parameter in your DB parameter group to 1. This is a static parameter that requires a reboot of the DB instance for the parameter to take effect. As part of applying this parameter, PostgreSQL sets the wal_level, max_wal_senders, max_replication_slots, and max_connections parameters. These parameter changes can increase WAL generation, so you should only set the rds.logical_replication parameter when you are using logical slots.

Further details on settings in the configuration file can be found in the PostgreSQL documentation.

Notes and Limitations on Using PostgreSQL as a Source in Mirroring

PostgreSQL Versions 9.5 and above are supported as a source for mirroring. Synchronization is not supported.
No DDL (schema changes) operations are logged, 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.
There does not seem to be a mechanism to get the replication last id by datetime (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 are not supported
The OID LOB data type is not migrated to the target.