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.

Default Replication Slot Naming

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 coexistence with other replication products.

Custom Replication Slot Configuration

Since v6.2.5/1

Custom replication slot names can be configured using the location property Replication_Slot_Name. If this property is used, the specified custom name will be applied to all channels associated with the location.

Limitations

Using a custom replication slot name may prevent the use of the same location in multiple channels. This is because the custom name applies globally to all channels associated with the location. PostgreSQL requires each replication slot to have a unique name within the cluster. If multiple channels attempt to use the same location, they would all try to use the same custom slot name, resulting in a conflict.

Managing Replication Slots

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 logical replication plugins test_decoding or pgoutput (supported since HVR version 6.2.5/0) should be used, and the HVR database user should have permission to use them.
Since 6.2.5/0, HVR supports the pgoutput plugin, and it is the default plugin. For versions prior to 6.2.5/0, the default plugin is test_decoding.
To use the pgoutput plugin, a PostgreSQL publication is required. Publications are available in PostgreSQL version 10 and later. Publication names must be unique within the entire PostgreSQL cluster.
A publication represents a group of source data tables. HVR will manage the publication on source location to ensure accurate replication.
HVR uses the following naming convention for publications: hvr_hub_channel_location. For example: hvr_myhub_mychn_myloc.
To create a new publication in the source PostgreSQL database, while activating the replication, in the HVR UI, ensure to select the Publication option under Replication Component in Activate Replication dialog (or run hvractivate with option -ou).

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.