PostgreSQL

Find out how to connect PostgreSQL to your data warehouse effortlessly.

In this page you will learn how to connect PostgreSQL to Erathos to move your data seamlessly.

The available connections between databases and the Erathos platform are through Open Connection, Static IP and SSH tunnel.

Connecting PostgreSQL:

1. Log in to your Erathos account

2. Select PostgreSQL as connector

3. Name your connection

4. Fill in the following form with the required information

On the Advanced Connection Settings, you can optionally activate connecting through a SSH tunnel by filling in the fields ssh_host, ssh_keepalive_interval, ssh_password, ssh_port, and ssh_user.

When using CDC, the following values may be used in cdc_snapshot_mode:

• initial: performs an initial snapshot, after it completes, streaming following changes from WAL.

• no_data: does not perform any snapshots, only streams changes that are currently available on WAL

Don't forget to click on Save and close to advance to the next steps of the integration.

With this connection created you can seemlessly move your PostgreSQL data to your BigQuery, Redshift or Postgres data-warehouse.

CDC setup

In order to enable CDC in your PostgreSQL connection you need to confirm the following settings in your database.

The following query should return the values wal_level = logical, max_replication_slots = 10 (or higher) and max_wal_senders = 10 (or higher), max_slot_wal_keep_size ≠ -1 (to safely prevent replication failures from completely filling the database disk, a hard limit should be set based in your database disk size).

SELECT 
    name, 
    setting, 
    short_desc, 
    context
FROM 
    pg_settings 
WHERE 
    name IN ('wal_level', 'max_replication_slots', 'max_wal_senders', 'max_slot_wal_keep_size');
    
-- If necessary, configuration parameters may be altered with:
ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_slot_wal_keep_size = '5GB';

Important: You must restart the database to apply changes to the settings above. The only exception is max_slot_wal_keep_size, which supports hot-reloading.

By default each UPDATE/DELETE statement on a given table will not collect the complete before/after state of the affected records, only when set as FULL identity will cdc be able to capture such changes. The queries below can be used the ascertain the replica identity configuration and update it to FULL when necessary.

-- Result Legend
-- f = FULL
-- d = DEFAULT (Logs only the primary key).
-- i = INDEX (Logs the columns covered by a specific unique index).
-- n = NOTHING (Logs no information for updates/deletes).
SELECT relreplident 
FROM pg_class 
WHERE oid = 'your_table_name'::regclass;

-- Update table replica identity level
ALTER TABLE your_table_name REPLICA IDENTITY FULL;

At the database side, the last configuration that needs to be confirmed is whether the credentials being used have the privilege for replication access.

SELECT 
    rolname, 
    rolreplication 
FROM 
    pg_roles 
WHERE 
    rolname = 'your_existing_user';
    
-- If rolreplication is FALSE, then grant privilege with:
ALTER ROLE your_existing_user WITH REPLICATION;

Important: By default, a replication slot will retain an infinite amount of WAL data. You need to set a hard ceiling to prevent a CDC failure from completely consuming your disk space through the max_slot_wal_keep_size setting.

Replication slot removal

In order to delete the replication slot when deleting the connection or to free up space in your database disk, use the following queries:

-- Find the exact name of your connection slot by looking up slots starting with 'erathos-cdc'
SELECT slot_name FROM pg_replication_slots;

-- Check memory retained
SELECT 
    slot_name, 
    active, 
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal 
FROM pg_replication_slots;

-- Drop it to free up space in the database
SELECT pg_drop_replication_slot('your_slot_name');