Capture from PostgreSQL using SQL Fetch from Replication Slot

This section describes the configuration requirements for capturing changes from PostgreSQL and Amazon RDS for PostgreSQL using SQL capture method. In this capture method (=SQL), Fivetran Local Data Processing reads transaction log records using a special SQL function.

Replication Slots

The SQL capture method uses PostgreSQL replication slots. The names for these slots have to be unique for an entire PostgreSQL cluster.

Local Data Processing uses the following naming convention for these replication slots:

hvr_hub-name_channel-name_location-name
content_copy

For example: hvr_hubdb_mychn_src

This should allow multi capture in most situations. This includes multiple Local Data Processing 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 ( with option -d).

To retrieve existing replication slots, execute the following:
```
select slot_name from pg_replication_slots;
content_copy
```

To manually remove a specific replication slot, execute the following:

select pg_drop_replication_slot('slot_name');
content_copy

For example:

select pg_drop_replication_slot('hvr_hubdb_mychn_src');
content_copy

Configuration for Capturing from PostgreSQL using SQL

This section describes the configuration requirements for capturing changes from PostgreSQL (on-premise) using SQL 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
content_copy
```
- max_replication_slots = number_of_slots
```
show max_replication_slots;

alter system set max_replication_slots = number_of_slots; -- server restart needed
content_copy
```
  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 Local Data Processing database User should either be superuser or have replication permission:
```
alter user username replication;
content_copy
```
The replication plug-in test_decoding should be installed and the Local Data Processing 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 Local Data Processing 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');
content_copy
```

When using SQL 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 ( with option -i) is not supported.

Configuration for Capturing from AmazonRDS for PostgreSQL using SQL

Local Data Processing supports capturing changes from PostgreSQL at Amazon RDS for PostgreSQL using SQL 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.