> For the complete documentation index, see [llms.txt](https://docs.erathos.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.erathos.com/connectors/databases/postgresql.md).

# PostgreSQL

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

<figure><img src="/files/6gwguOMS2CUGtvJcLa7D" alt=""><figcaption></figcaption></figure>

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.

<figure><img src="/files/BOsPQVvR4szWCSIL3SoP" alt=""><figcaption></figcaption></figure>

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).

```sql
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';
```

{% hint style="warning" %}
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.
{% endhint %}

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.

```sql
-- 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.

```sql
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;
```

{% hint style="danger" %}
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.
{% endhint %}

#### 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:

```sql
-- 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');
```