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:

  1. Contact the technical support team via the Help Center to request the library files developed for PostgreSQL replications.
  2. Extract the files from the zip file to a folder that is accessible to the PostgreSQL lib folder.
  3. If using Microsoft Windows, rename the appropriate DLL to dbm_decoding.dll.
    If using LINUX, rename the .so file to dbm_decoding.so.
  4. Copy the file to the lib folder in your PostgreSQL installation.

  5. Edit the postgresql.conf file to set up the environment for replication using Syniti Data Replication:

    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 Data Replication. This parameter can only be set at server start.

    max_replication_slots sets the maximum number of replication slots available to all applications. Syniti Data Replication requires a “replication_slot” for every connection that it uses to replicate from PostgresSQL. 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 PostgresSQL database, the maximum number of slots should be raised accordingly.
    Details on settings in the configuration file can be found in the PostgreSQL documentation.

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

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.