Capture from PostgreSQL Using Logical Replication
This section describes the configuration requirements for capturing changes from PostgreSQL, including managed PostgreSQL services, using logical replication (Capture_Method=SQL). In this capture method, Fivetran HVR reads transaction log records using a special SQL function.
Since HVR 6.1.5/5, this capture method has been renamed as Logical Replication in the HVR UI. Previously, it was known as SQL Fetch from Replication Slot.
Replication Slots
The Logical Replication capture method uses PostgreSQL replication slots. The names for these slots have to be unique for an entire PostgreSQL cluster.
HVR uses the following naming convention for these replication slots: hvr_hub_channel_location. For example: hvr_myhub_mychn_myloc.
This should allow multi capture in most situations. This includes multiple HVR capture jobs and also coexistence with other replication products.
PostgreSQL will not remove transaction log files for which changes exist that have not been processed by a replication slot. For this reason, replication slots have to be removed when a channel is no longer needed. This can be done manually or by running Deactivate Replication (hvractivate with option -d
).
To retrieve existing replication slots, execute the following:
SELECT slot_name FROM pg_replication_slots;
To manually remove a specific replication slot, execute the following:
SELECT pg_drop_replication_slot('slot_name');
For example:
SELECT pg_drop_replication_slot('hvr_myhub_mychn_myloc');
Generic PostgreSQL Configuration
This section describes the configuration requirements for capturing changes from PostgreSQL (on-premise) using Logical Replication capture method (Capture_Method=SQL):
PostgreSQL configuration file postgresql.conf should have the following settings:
wal_level = logical
SHOW wal_level; ALTER SYSTEM SET wal_level = logical; -- server restart needed
max_replication_slots = number_of_slots
SHOW max_replication_slots; ALTER SYSTEM SET max_replication_slots = number_of_slots; -- server restart needed
number_of_slots should be set to at least the number of channels multiplied by the number of capture locations in this PostgreSQL installation.
The HVR database User should either be superuser or have replication permission:
ALTER USER username REPLICATION;
The replication plug-in test_decoding should be installed and the HVR database User should have permission to use it. This plug-in is typically installed in $PG_DATA/lib. To test whether the plug-in is installed and the HVR database User has the required permissions to execute the following SQL commands:
SELECT pg_create_logical_replication_slot('hvr', 'test_decoding'); SELECT pg_drop_replication_slot('hvr');
When using the Logical Replication capture method:
- PostgreSQL versions before 9.4.12 should be avoided due to a PostgreSQL bug (detected in 9.4.6) which affects this log read method.
- Capture rewind in Activate Replication (hvractivate with option
-i
) is not supported.
Amazon RDS and Aurora PostgreSQL Configuration
HVR supports capturing changes from Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL using logical replication (Capture_Method=SQL).
To get the required settings and permissions the Parameter Group assigned to the Instance should have rds.logical_replication=1. Changing this needs to be followed by a restart of PostgreSQL.