Configure Postgres

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 instead. This guide covers PostgreSQL configuration for developers who run or embed Supabase ETL themselves.

Prerequisites

• PostgreSQL 14 through 18. PostgreSQL 15 adds publication filtering; PostgreSQL 16 adds logical decoding on physical read replicas.
• Ability to restart Postgres (required for wal_level changes)
• Superuser access to enable logical WAL and, by default, to install ETL source migrations

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.

wal_level = logical

Restart Postgres after changing this setting. Confirm with SHOW wal_level;.

Replication User

ETL needs a login role with the REPLICATION privilege. A superuser works for local development. For production, create a dedicated role:

CREATE ROLE etl_user WITH LOGIN REPLICATION PASSWORD 'placeholder-password';
GRANT CONNECT ON DATABASE example_database TO etl_user;
GRANT USAGE ON SCHEMA public TO etl_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO etl_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO etl_user;

The role must be able to SELECT every published table so ETL can copy existing rows. Create the publication as a table owner or superuser; the replication role does not have to own the tables. Run the schema and table grants in the database being replicated. ALTER DEFAULT PRIVILEGES applies to future objects created by the role executing it; repeat it with FOR ROLE for each role that creates published tables.

By default, Pipeline::start() also installs ETL source helpers and a ddl_command_end event trigger. Creating that event trigger requires superuser. Either run the pipeline as a superuser, or apply the source migrations in crates/etl/migrations/source/ as an admin and set run_source_migrations: false.

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.

ETL creates its logical slots automatically. Do not pre-create a slot for the pipeline. The apply worker uses supabase_etl_apply_{pipeline_id}; table sync workers create supabase_etl_table_sync_{pipeline_id}_{table_id} slots for initial sync and drop them after catch-up completes. These are persistent PostgreSQL slots with a short lifecycle, not session-scoped TEMPORARY slots; they can remain after a failed or interrupted sync.

Viewing Replication Slots

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

Deleting Replication Slots

-- Drop a leftover ETL slot only after the pipeline has stopped
SELECT pg_drop_replication_slot('supabase_etl_apply_1');

Max Replication Slots

Controls how many replication slots Postgres can maintain.

# Default: 10
max_replication_slots = 20

Each pipeline uses one apply-worker slot and additional slots for table sync workers. Budget at least max_table_sync_workers + 1 slots per pipeline for normal operation, plus capacity for other replication clients and slots left by interrupted or errored syncs. max_table_sync_workers limits concurrent workers, not every slot that may remain in PostgreSQL.

Increase this setting when:

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

Max WAL Senders

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

# Default: 10
max_wal_senders = 20

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

WAL Keep Size

Sets a minimum amount of recent WAL to retain for physical standbys. Logical replication slots retain the WAL they need independently; wal_keep_size is neither a slot-retention limit nor a guarantee against slot invalidation.

wal_keep_size = 1GB

WAL Buildup and Disk Usage

Replication slots retain WAL from their restart_lsn, which can lag behind acknowledged progress. Long copies, slow consumers, and errors can therefore cause significant disk usage. See PostgreSQL's slot fields.

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 needed by its table-sync slot, potentially starting before the consistent snapshot point. Its table-sync slot starts consuming changes after the source copy finishes. Progress on the separate apply-worker slot does not release WAL still needed by a table-sync slot.

Retention covers PostgreSQL WAL files, including activity outside the copied table or publication. Size the budget using the server's WAL generation rate and the longest expected copy and catch-up duration. For example, a sustained 20 MB/s over a two-day copy generates about 3.46 TB of WAL, before catch-up and additional headroom. This is an illustrative calculation, not a workload estimate. Multiple slots can retain overlapping WAL; do not add their lag values together to estimate physical disk usage.

# -1 = unlimited (dangerous for disk space)
# Example only; size for your WAL rate, copy duration, and available disk.
max_slot_wal_keep_size = 10GB

Increasing the setting cannot restore WAL that has already been removed. A lost table-sync slot requires resetting the affected table's state and restarting its initial sync. Invalidation detected during copying produces a manually retryable table error; restarting the pipeline alone does not reset that error. A new copy replaces the partial destination table and uses a fresh snapshot and slot, so it needs enough retention to complete again.

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.

Slots persist across disconnections and pipeline stops. Stopping ETL releases copy transactions and their locks after teardown, but does not release the WAL needed by retained slots. Continue monitoring storage and slot status during maintenance. Table-sync slots are persistent PostgreSQL slots, even though ETL removes them after successful synchronization.

Planning Long Initial Copies

Before copying a large table:

1. Estimate copy duration at the throughput the source and destination can sustain. Budget WAL for that duration, catch-up, and possible retries using the whole server's WAL generation rate. Leave separate disk headroom for ordinary database growth and retained old row versions.
2. Schedule table DDL and truncation outside the copy. Keep publication and shared-object changes within the schema-change operating rules.
3. Set max_table_sync_workers and max_copy_connections_per_table to fit source, network, and destination capacity. Increase them only when the bottleneck has spare capacity.
4. Monitor every active table copy and slot, available disk, application latency, and destination throughput. Set alerts early enough to respond before disk space or WAL retention runs out.

ETL holds ACCESS SHARE, the same table-level lock used by an ordinary SELECT, throughout each copy unit. It also keeps an MVCC snapshot open for the parallel workers. A copy lasting hours or days therefore has the operating characteristics of a long-running read transaction. The lock is per relation, not per row; large partition trees increase lock bookkeeping, while the physical copy competes with application queries for CPU, I/O, cache, and network bandwidth.

For copies from a primary:

A standby copy instead competes with WAL replay: primary DDL still proceeds, and replay can cancel conflicting copy queries or sessions. hot_standby_feedback can reduce cleanup conflicts while increasing primary bloat; it does not prevent DDL conflicts. See Read replicas and PostgreSQL's standby conflict rules.

Vacuum can make reclaimed space reusable after the snapshot ends without necessarily shrinking the physical table file. Snapshot age also matters for transaction ID wraparound prevention; monitor age(backend_xmin) in pg_stat_activity and the ages of slot xmin and catalog_xmin, as well as disk usage. max_slot_wal_keep_size does not bound old-row retention or table bloat. See PostgreSQL's vacuum guidance and concurrent index behavior.

Schedule incompatible DDL outside long copies, or give migration sessions a short lock_timeout so a blocked migration fails instead of holding up application traffic indefinitely. Monitor blocking with pg_blocking_pids() and pg_stat_activity. If a waiting migration is blocking application traffic, cancel it and roll back its transaction when safe, then reschedule it. If availability requires stopping ETL to release its locks, the incomplete table copy must restart from a fresh snapshot; completed chunks are not a resumable checkpoint.

Set copy concurrency to fit source and destination capacity. Fewer workers can reduce immediate load while extending snapshot and WAL retention. Slow destination writes also prolong the source copy's locks and snapshot. Plan storage and an operator response for stalled copies; neither a larger WAL budget nor faster WAL consumption removes the old snapshot's vacuum effects. Monitor each active copy as well as the apply slot: advancing the apply slot does not release another connection's copy locks, snapshot, or table-sync WAL. See Initial-copy schema protection for partition scope, retry behavior, and the DDL changes these locks do not protect.

If copy progress stalls or retention headroom shrinks, fix destination errors or throughput limits, reduce avoidable bulk writes, or add capacity before retrying. Catch-up needs enough processing capacity to consume the backlog faster than new work arrives. Raising retention only provides more time, and only helps while the required WAL still exists and disk space is available. Stopping ETL releases its copy snapshots after teardown, but retained slots continue holding WAL. Do not treat a stop or repeated restart as a disk-space recovery procedure; follow WAL buildup and disk usage for slot recovery.

Tuning Copy Throughput

Two settings control initial-copy parallelism:

Increase concurrency only while the source, network, and destination have spare capacity. More workers use more connections and resources; additional table-sync workers also need replication slots.

Destination-specific settings can further improve performance. Review the available settings for your destination alongside these copy controls.

Change one setting at a time and monitor copy progress and resource usage. Plan changes before a large copy where possible: restarting to apply configuration can repeat an unfinished copy.

Monitoring WAL Usage

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

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.
• Interpret safe_wal_size together with wal_status and max_slot_wal_keep_size: NULL can mean a lost slot or unlimited retention, while a nonpositive value means no remaining headroom. This is WAL headroom, not free disk space or a guaranteed time window. See PostgreSQL's slot monitoring reference.
• Resolve errored tables promptly and size max_table_sync_workers to balance copy speed with resource use.

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.

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:

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

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

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:

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 and enable standby feedback:

primary_conninfo = 'host=primary.example.com port=5432 dbname=postgres user=replicator password=placeholder-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:

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 supports synchronizing logical slots from a primary to its standbys for failover. This is separate from reading directly from a current read replica. Configure it as described in Failover Slots when ETL must continue through a primary promotion.

Failover Slots

PostgreSQL 17 or newer can synchronize a primary's logical replication slots to physical standbys. After a synchronized standby is promoted, ETL can resume from the same apply-worker slot instead of creating a new WAL lineage and resynchronizing tables. See the PostgreSQL documentation on replication slot synchronization.

Failover support is disabled by default. Enable it only when the source and every intended failover target run PostgreSQL 17 or newer.

Enable the feature in the replicator configuration:

pipeline:
  replication_slot:
    failover: true

ETL then creates its apply-worker and short-lived table-sync logical slots with the PostgreSQL FAILOVER option. If the apply-worker slot already exists from an earlier run, startup checks pg_replication_slots.failover and enables the option only when repair is needed. Disabling the setting affects newly created slots but does not downgrade an existing failover-enabled apply slot.

The ETL setting only makes slots eligible for synchronization. PostgreSQL must also be configured to copy and retain them. Use PostgreSQL 17 or newer on the primary and every failover target, create a physical replication slot for each standby, and configure the corresponding standby with a valid database name:

primary_conninfo = 'host=primary.example.com port=5432 dbname=postgres user=replicator password=placeholder-password'
primary_slot_name = 'etl_failover_standby_1'
hot_standby_feedback = on
sync_replication_slots = on

On the primary, list that physical slot in synchronized_standby_slots:

synchronized_standby_slots = 'etl_failover_standby_1'

This prevents ETL's logical consumer from advancing beyond WAL that the named standby has flushed. Logical replication will stop if a listed physical slot is missing or invalidated, so monitor both the physical link and the synchronized logical slots. hot_standby_feedback is required for slot synchronization and can increase vacuum retention on the primary.

Slot synchronization is asynchronous. Before a planned promotion, verify on the target standby that every ETL slot required by the pipeline exists and is ready:

SELECT slot_name,
       synced AND NOT temporary AND invalidation_reason IS NULL AS failover_ready
FROM pg_replication_slots
WHERE slot_name LIKE 'supabase\_etl\_apply\_%' ESCAPE '\'
   OR slot_name LIKE 'supabase\_etl\_table\_sync\_%' ESCAPE '\';

Do not promote that standby for the pipeline until every required slot reports failover_ready = true. A synchronized slot cannot be consumed while the server remains in recovery; it becomes usable after promotion. The pipeline's source connection must then reach the promoted primary, either through a stable HA endpoint or after the orchestrator updates the endpoint and restarts the replicator. ETL may deliver recent events again after a crash or failover, so destinations must remain idempotent.

Publications

Publications define which tables and operations to replicate.

Creating Publications

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

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.

For example, with this hierarchy:

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.

-- This will NOT be replicated
TRUNCATE TABLE orders_2026_01;

-- This WILL be replicated
TRUNCATE TABLE orders_2026;

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.

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

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

Complete Configuration Example

Minimal postgresql.conf setup:

# 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 the replication role and grant SELECT on the published tables
3. Create your publication:

   CREATE PUBLICATION etl_publication FOR TABLE your_table;

4. Verify the setup:

   SHOW wal_level;
   SHOW max_replication_slots;
   SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'etl_user';
   SELECT * FROM pg_publication WHERE pubname = 'etl_publication';

Next Steps

• First Pipeline: Hands-on tutorial using these settings
• Custom Implementations: Build your own components
• Architecture: How ETL uses these settings