# Configure Postgres

> Set up Postgres with the permissions and settings required by Supabase ETL.

- Canonical HTML: https://supabase.github.io/etl/guides/configure-postgres/
- Agent-readable Markdown: https://supabase.github.io/etl/guides/configure-postgres.md
- Source: https://github.com/supabase/etl/blob/main/site/content/docs/guides/configure-postgres.mdx

Configure the Postgres settings, slots, publications, and retention needed by
the open-source replication engine.

Using the managed product in the Supabase Dashboard? Follow the canonical
[Supabase Pipelines documentation](https://supabase.com/docs/guides/database/replication/pipelines)
instead. This guide covers PostgreSQL configuration for developers who run or
embed Supabase ETL themselves.

## Prerequisites [#prerequisites]

* **PostgreSQL 14 through 18.** PostgreSQL 15 adds publication filtering;
  PostgreSQL 16 adds logical decoding on physical read replicas.
* Superuser access to the Postgres server
* Ability to restart Postgres (required for `wal_level` changes)

## Enable Logical WAL [#enable-logical-wal]

Set `wal_level = logical` to enable Postgres to record **logical change data**
in the WAL, which external tools can then decode and replicate.

```ini title="postgresql.conf"
wal_level = logical
```

**Restart Postgres** after changing this setting.

## Replication Slots [#replication-slots]

A replication slot records a consumer's progress and retains the WAL it still
needs. This lets ETL catch up after a temporary disconnect.

### Creating Replication Slots [#creating-replication-slots]

```sql
-- Create a logical replication slot
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');
```

### Viewing Replication Slots [#viewing-replication-slots]

```sql
-- See all replication slots
SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;
```

### Deleting Replication Slots [#deleting-replication-slots]

```sql
-- Drop a replication slot when no longer needed
SELECT pg_drop_replication_slot('my_slot');
```

> **Warning**
>
> Only delete slots when you are sure they are not in use. Deleting an active
> slot will break replication.

## Max Replication Slots [#max-replication-slots]

Controls how many replication slots Postgres can maintain.

```ini title="postgresql.conf"
# Default: 10
max_replication_slots = 20
```

ETL uses a **single replication slot** for its main apply worker. Additional slots are created for parallel table copies during initial sync or when new tables are added to the publication. The `max_table_sync_workers` pipeline parameter controls parallel copies, so total slots used by ETL never exceed `max_table_sync_workers + 1`.

Increase this setting when:

* Running multiple ETL pipelines against the same database
* Development/testing environments with frequent slot creation

## Max WAL Senders [#max-wal-senders]

Controls concurrent streaming connections. Each active replication slot uses
one WAL sender.

```ini title="postgresql.conf"
# Default: 10
max_wal_senders = 20
```

Set this to at least `max_replication_slots` to ensure all slots can connect.

## WAL Keep Size [#wal-keep-size]

Provides a disk-backed safety buffer when replication consumers fall behind.

```ini title="postgresql.conf"
wal_keep_size = 1GB
```

## WAL Buildup and Disk Usage [#wal-buildup-and-disk-usage]

Replication slots prevent Postgres from deleting WAL files until all consumers have processed them. This can cause **significant disk usage** if the pipeline falls behind or encounters errors.

### Common Causes of WAL Buildup [#common-causes-of-wal-buildup]

* **Errored tables:** ETL retains their slots to preserve consistency. Fix the
  error or remove tables you no longer replicate.
* **Slow destinations:** High latency, large transactions, or outages can make
  ingestion fall behind source writes.
* **Long initial copies:** Each active table copy retains WAL from its
  consistent snapshot point.

> **Warning**
>
> If WAL grows beyond the configured limit, Postgres will terminate the
> replication slot. Control this with `max_slot_wal_keep_size`:

```ini title="postgresql.conf"
# -1 = unlimited (dangerous for disk space)
max_slot_wal_keep_size = 10GB
```

If the main replication slot is invalidated, ETL fails pipeline startup by
default and requires operator intervention. Set `invalidated_slot_behavior` to
`recreate` to have ETL delete and recreate the slot, reset table states, and
resynchronize eligible tables from scratch. Automatic recreation can repeat a
large initial copy, so choose it deliberately and monitor destination capacity.

### Monitoring WAL Usage [#monitoring-wal-usage]

```sql
-- Check replication slot WAL usage.
SELECT slot_name,
   active,
   wal_status,
   pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_wal_bytes,
   pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS confirmed_flush_lag_bytes,
   safe_wal_size
FROM pg_replication_slots;

-- Check total WAL directory size
SELECT pg_size_pretty(sum(size)) AS wal_size
FROM pg_ls_waldir();
```

`wal_status` reports whether the WAL required by a replication slot is still
available:

* `reserved`: required WAL is within normal retention.
* `extended`: required WAL exceeds `max_wal_size`, but Postgres is still retaining it.
* `unreserved`: required WAL is no longer fully reserved and may be removed at the next checkpoint.
* `lost`: required WAL was removed and the slot is no longer usable.
* `NULL`: the slot has not reserved WAL yet, usually because `restart_lsn` is `NULL`.

ETL API responses return unknown future Postgres `wal_status` values as `unknown`.

### Recommendations [#recommendations]

* Bound retention with `max_slot_wal_keep_size` based on available disk.
* Alert on `confirmed_flush_lag_bytes`, `retained_wal_bytes`, `safe_wal_size`,
  and `wal_status`.
* Treat `safe_wal_size IS NULL` as unlimited retention and `0` as no remaining
  headroom.
* Resolve errored tables promptly and size `max_table_sync_workers` to balance
  copy speed with resource use.

## Read Replicas [#read-replicas]

ETL can read logical replication from a physical read replica when the replica runs **PostgreSQL 16 or newer**. PostgreSQL 14 and 15 can still be used with ETL, but logical decoding must run on the primary. See the PostgreSQL documentation on [logical slots on hot standby](https://www.postgresql.org/docs/16/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS).

When using a read replica:

* Configure `pg_connection` to point at the replica. ETL uses this connection for logical replication, table copy, schema reads, publications, slots, keepalives, and status updates.
* Configure `store_pg_connection` when using `PostgresStore` and `pg_connection` points at a read-only replica. The store connection must be writable because it runs store migrations and persists pipeline state.
* Apply ETL source migrations on the primary before starting the pipeline. Standby connections are read-only, so ETL skips source migration execution when the configured source is in recovery.
* Let ETL create its logical replication slots on the read replica. Do not pre-create ETL logical slots on the primary for this mode.

Publication, table, and ETL source-migration changes are ordinary WAL records. When you create them on the primary, the read replica can only see them after replay reaches that WAL position. Do not wait a fixed number of seconds; wait for a concrete replay LSN:

```sql
-- Run on the primary after creating tables, source migrations, and publications.
SELECT pg_current_wal_flush_lsn();
```

Then wait on the read replica until replay reaches that LSN. If you just created a new database on the primary, connect to an existing maintenance database such as `postgres` for this check; the new database might not exist on the replica until replay catches up.

```sql
-- Run on the read replica before starting ETL.
SELECT pg_last_wal_replay_lsn() >= '0/16B6C50'::pg_lsn AS ready;
```

For extra confidence, also check that the expected publication is visible on the replica:

```sql
SELECT 1 FROM pg_publication WHERE pubname = 'my_publication';
```

If ETL only receives the replica `pg_connection`, it cannot derive the primary's setup LSN itself. The orchestrator that creates or updates the source-side schema and publication should perform this LSN barrier before starting the pipeline, or should retry pipeline startup until the replica catches up. Once the logical slot exists on the replica, ongoing primary writes can lag normally; ETL will decode them as the replica replays WAL and the slot keeps the replica-side restart position.

The primary must generate logical WAL, and each server needs enough sender and slot capacity for the role it plays. On the primary, count the physical slots used by read replicas. On the read replica, count the logical slots ETL creates for its apply worker and table sync workers:

```ini title="postgresql.conf"
wal_level = logical
max_replication_slots = 20
max_wal_senders = 20
```

For the physical replication link between the primary and the read replica, use a [physical replication slot](https://www.postgresql.org/docs/16/warm-standby.html#STREAMING-REPLICATION-SLOTS) and enable standby feedback:

```ini title="postgresql.conf (standby)"
primary_conninfo = 'host=primary.example.com port=5432 dbname=postgres user=replicator password=...'
primary_slot_name = 'etl_read_replica'
hot_standby = on
hot_standby_feedback = on
wal_receiver_status_interval = '1s'
```

`hot_standby_feedback` helps prevent required catalog rows from being vacuumed away on the primary while standby logical slots need them. A physical slot between the primary and the standby keeps that protection across standby reconnects and restarts.

If initial copies can run for longer than your standby conflict delay, tune `max_standby_streaming_delay` for that replica. A larger value reduces copy cancellations at the cost of allowing more replay lag while conflicting standby queries finish.

Logical slot creation on a standby needs information about transactions running on the primary. If the primary is idle, creating a logical slot on the standby can wait until the primary emits that snapshot information. To speed this up during setup or tests, run this on the primary:

```sql
SELECT pg_log_standby_snapshot();
```

This is only a setup-time nudge for slot creation. ETL uses the regular
PostgreSQL logical replication protocol for keepalives and status updates after
ongoing replication starts.

PostgreSQL 17+ also has logical failover slot synchronization, where failover-enabled logical slots on the primary are synchronized to standbys. That is a separate high-availability feature for resuming logical replication after promoting a standby. It is not required for ETL to read from a current read replica, and synchronized standby slots cannot be consumed on the standby while they are marked as synced.

## Publications [#publications]

Publications define **which tables and operations** to replicate.

### Creating Publications [#creating-publications]

```sql
-- Create publication for specific tables
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Create publication for all tables (use with caution)
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- Create publication for all tables in selected schemas
CREATE PUBLICATION schema_tables FOR TABLES IN SCHEMA public, analytics;

-- Include only specific operations
CREATE PUBLICATION inserts_only FOR TABLE users WITH (publish = 'insert');
```

Avoid publishing ETL-owned tables. If the source database is also used as the
ETL state store, the `etl` schema contains ETL internal tables. Do not include
that schema in the publication. In that setup, `FOR ALL TABLES` also includes
ETL-owned tables, so use explicit table lists or `FOR TABLES IN SCHEMA ...` for
customer-owned schemas instead.

#### Partitioned Tables [#partitioned-tables]

ETL supports PostgreSQL partition publications with either
`publish_via_partition_root = true` or `publish_via_partition_root = false`.
At startup, ETL loads the effective relation OIDs through
`pg_get_publication_tables()`, so it tracks the same relation identities and
schemas PostgreSQL uses for logical replication messages.

| Publication shape                                                                                                             | `publish_via_partition_root` | PostgreSQL publishes as             | ETL tracks                              |
| ----------------------------------------------------------------------------------------------------------------------------- | ---------------------------- | ----------------------------------- | --------------------------------------- |
| `FOR TABLE orders` where `orders` is the top partitioned table                                                                | `true`                       | `orders`                            | `orders`                                |
| `FOR TABLE orders` where `orders` is the top partitioned table                                                                | `false`                      | Leaf partitions under `orders`      | The leaf partitions                     |
| `FOR TABLE orders_2026` where `orders_2026` is a partitioned subtree                                                          | `true`                       | `orders_2026`                       | `orders_2026`                           |
| `FOR TABLE orders_2026` where `orders_2026` is a partitioned subtree                                                          | `false`                      | Leaf partitions under `orders_2026` | The leaf partitions under `orders_2026` |
| `FOR TABLE orders_2026_01` where `orders_2026_01` is a leaf partition                                                         | Either                       | `orders_2026_01`                    | `orders_2026_01`                        |
| `FOR ALL TABLES` for the whole database, or `FOR TABLES IN SCHEMA ...` for selected schemas. Do not include ETL-owned tables. | `true`                       | Partition roots plus regular tables | Partition roots plus regular tables     |
| `FOR ALL TABLES` for the whole database, or `FOR TABLES IN SCHEMA ...` for selected schemas. Do not include ETL-owned tables. | `false`                      | Leaf partitions plus regular tables | Leaf partitions plus regular tables     |

For example, with this hierarchy:

```text
orders
  ├── orders_2025
  │   ├── orders_2025_01
  │   └── orders_2025_02
  └── orders_2026
  ├── orders_2026_01
  └── orders_2026_02
```

`FOR TABLE orders_2026 WITH (publish_via_partition_root = true)` replicates the
2026 subtree as `orders_2026`. `FOR TABLE orders_2026 WITH
(publish_via_partition_root = false)` replicates `orders_2026_01` and
`orders_2026_02` as separate leaf tables.

On PostgreSQL 15+, row filters on partition publications are applied during
both the initial sync and ongoing replication. ETL uses the row filter attached to the effective
publication table entry: the published root or subtree when
`publish_via_partition_root = true`, and the published leaf relation when
`publish_via_partition_root = false`.

**Limitation:** With `publish_via_partition_root = true`, `TRUNCATE` operations on individual partitions are not replicated. Execute truncates on the published partition table instead. For a top-level publication, that is the top root; for a subtree publication, that is the published subtree root.

```sql
-- This will NOT be replicated
TRUNCATE TABLE orders_2026_01;

-- This WILL be replicated
TRUNCATE TABLE orders_2026;
```

### Managing Publications [#managing-publications]

Create the intended publication shape before starting a pipeline whenever
possible. For a running pipeline, ETL currently supports changing the column
list of a table it already tracks. Other `ALTER PUBLICATION` changes are not
live publication-reconciliation operations.

In particular, adding or removing tables, adding or removing schemas, and
changing `publish_via_partition_root` can change the effective relation OIDs.
ETL discovers those new and removed identities during pipeline startup, not
from schema messages emitted by the DDL trigger. Restarting reconciles ETL's
table state, but it does not rename, merge, or remove destination tables created
under the previous relation identities. A root-to-leaf or leaf-to-root change
may therefore require a table resynchronization or a new pipeline.

> **Warning**
>
> Do not change publication membership, row filters, publication operation
> settings, or `publish_via_partition_root` while a pipeline is running. These
> changes are outside the supported live-update contract. ETL may skip events
> for newly effective relation OIDs, and partition-identity changes can leave
> stale destination tables or cause missing or duplicated data. Behavior in
> this state is unsupported and must not be relied upon.

```sql
-- View existing publications
SELECT * FROM pg_publication;

-- See which tables are in a publication
SELECT * FROM pg_publication_tables WHERE pubname = 'my_publication';

-- Add tables to existing publication
ALTER PUBLICATION my_publication ADD TABLE products;

-- Remove tables from publication
ALTER PUBLICATION my_publication DROP TABLE products;

-- Drop publication
DROP PUBLICATION my_publication;
```

## Version Compatibility [#version-compatibility]

ETL supports PostgreSQL 14 through 18. PostgreSQL 14 is limited to table-level
publication filtering; use the newer versions for the features below.

| Feature                                    | PostgreSQL 14 | PostgreSQL 15 | PostgreSQL 16+ |
| ------------------------------------------ | ------------- | ------------- | -------------- |
| Table-level publication                    | Yes           | Yes           | Yes            |
| Column-level filtering                     | No            | Yes           | Yes            |
| Row-level filtering                        | No            | Yes           | Yes            |
| `FOR TABLES IN SCHEMA`                     | No            | Yes           | Yes            |
| Partitioned table support                  | Yes           | Yes           | Yes            |
| Logical decoding on physical read replicas | No            | No            | Yes            |

## Complete Configuration Example [#complete-configuration-example]

Minimal `postgresql.conf` setup:

```ini title="postgresql.conf"
# Enable logical replication
wal_level = logical

# Replication capacity
max_replication_slots = 20
max_wal_senders = 20

# WAL retention
wal_keep_size = 1GB

# Limit WAL retention per slot (optional but recommended)
max_slot_wal_keep_size = 10GB
```

After editing the configuration:

1. Restart Postgres
2. Create your publication:
   ```sql
   CREATE PUBLICATION etl_publication FOR TABLE your_table;
   ```
3. Verify the setup:
   ```sql
   SHOW wal_level;
   SHOW max_replication_slots;
   SELECT * FROM pg_publication WHERE pubname = 'etl_publication';
   ```

## Next Steps [#next-steps]

* [First Pipeline](https://supabase.github.io/etl/guides/first-pipeline.md): Hands-on tutorial using these settings
* [Custom Implementations](https://supabase.github.io/etl/guides/custom-implementations.md): Build your own components
* [Architecture](https://supabase.github.io/etl/explanation/architecture.md): How ETL uses these settings