PostgreSQL

Use PostgreSQL as a current tapstate source for an initial full load and ongoing change data capture (CDC). The documented preview path uses PostgreSQL logical replication with pgoutput and writes the resulting operational state to MongoDB.

PostgreSQL is a source in the current preview. This page does not make it a published tapstate target.

Before you begin

Connector availability

The PostgreSQL connector is registered automatically by the Quickstart script. On standalone or fresh server container deployments, the connector catalog starts empty. Confirm that PostgreSQL is registered before authoring connections:

connectors
register postgres

See When registration is required.

Prepare a source database

Choose the outcome you need. Each tab is a complete preparation path; you do not need to combine steps from both tabs. In tapstate, snapshot copies the rows that exist when the run starts, while cdc performs that initial copy and then follows PostgreSQL changes from WAL.

Create a connection

tapstate stores a PostgreSQL source connection as a .tap.yml file in the workspace.

Source connection

The example below creates a CDC-capable source connection. The connection's mode controls its read capability.

tapstate new --non-interactive \
  --kind source \
  --connector postgres \
  --id orders_postgres \
  --mode cdc \
  --set host=postgres.internal \
  --set port=5432 \
  --set database=orders \
  --set schema=public \
  --set user='${POSTGRES_READER_USER}' \
  --set password='${POSTGRES_READER_PASSWORD}' \
  --workdir tapstate-work

For a full-load-only source, change --mode cdc to --mode snapshot. The generated connection uses the same PostgreSQL fields; only the read mode differs.

version: tapstate/v1
kind: source
id: orders_postgres
connector: postgres
config:
  deploymentMode: standalone
  host: postgres.internal
  port: 5432
  database: orders
  schema: public
  user: ${POSTGRES_READER_USER}
  password: ${POSTGRES_READER_PASSWORD}
  logPluginName: pgoutput
mode: cdc
tables:
  - orders

Use mode: cdc only after completing the WAL, plugin, publication, and replication preparation. The mode does not by itself prove that those database prerequisites are ready.

Validate the configuration

tapstate validate --workdir tapstate-work

Validation does not resolve the host, credentials, or a PostgreSQL server. A syntactically valid resource that uses postgres.invalid can therefore pass validation. Register the official artifact and run a server-side connection test before relying on the configuration.

A successful connection test does not prove that CDC can start. It can confirm network access and credentials while logical replication, a replication slot, a publication, or replica identity is still missing. Run a small snapshot and then an insert, update, and delete in a staging database.

Run a staging check that:

1. discovers a selected table;
2. completes a small snapshot;
3. observes an insert, update, and delete through CDC;
4. stops with state retained, starts again, and verifies that later changes reach the target. Recovery uses persisted progress and can replay unconfirmed changes.

Use these read-only checks while diagnosing the source:

SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;

SELECT slot_name, plugin, slot_type, active, restart_lsn
FROM pg_replication_slots;

Replication slots and recovery

The documented pgoutput CDC path uses a logical replication slot. PostgreSQL retains WAL until the slot confirms progress. With shared capture, tapstate confirms changes after writing them to its durable change log; each pipeline then confirms its own target writes. With direct capture (srs: false in the pipeline source), confirmation follows that pipeline's target writes.

Cleanup can fail or time out while state clearing still completes. Check connector.release-failed warnings and the named slot before considering the source clean. Custom slots and connectors with automatic cleanup disabled need separate ownership and cleanup review.

Monitor retained WAL while a pipeline is paused, stopped, or unable to write:

SELECT slot_name, active, active_pid, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

A replication slot ... is active for PID ... error means a connection already owns the slot. Check active_pid and the owning capture. Stop the affected pipeline with its state retained and wait for the stop to complete before retrying. If the error persists, preserve the metadata, operator state, and connector logs for investigation.

Do not drop a slot solely because active is false. Check every pipeline and retained recovery dependency, including those on other servers. Older releases can leave additional slots behind; remove only confirmed unused slots using your database administration procedure.

Once PostgreSQL confirms a position, writing an earlier checkpoint into tapstate does not make PostgreSQL replay WAL already released. Likewise, do not restore old tapstate metadata while readers are active: newer changes may exist only in the durable shared log. If retained history is missing or unsafe, tapstate refuses recovery rather than skipping it. See Resume or rerun and Upgrade to v0.7.

Limitations

• Replication slots retain WAL until consumers advance. Monitor pg_replication_slots and disk use, and remove a slot only after confirming that no task still depends on it.
• Tables without a suitable replica identity can produce incomplete keys for updates and deletes. REPLICA IDENTITY FULL increases WAL volume and should be evaluated on busy tables.
• Capturing changes from partitioned parent tables requires PostgreSQL 13+ with pgoutput.
• The current preview does not make a DDL-capture compatibility promise.
• PostgreSQL string types cannot store the zero byte (\0); normalize it before writing if it can appear in source data.
• A replication slot can be blocked by long-running database work. Investigate materialized-view refreshes and other sessions if slot creation stalls.
• Do not drop an inactive slot merely because it is inactive; a stopped pipeline may still depend on its position. Remove it only after the owning pipeline has been retired or reset.
• Configure server-side encryption as required and use the TLS connection options available in your tapstate environment.

Reference

Connection settings

The documented current path uses the fields below. The local demo relies on the connector's default pgoutput behavior and does not pre-create a slot or publication. For another deployment, verify that behavior with a staged table and its intended database account.

Additional catalog fields

The catalog recognizes the fields below, including decoder and publication settings inherited from the underlying connector. They are not required by the documented pgoutput path and are not used by the Quickstart. Do not use them to infer another published PostgreSQL deployment mode.

Database-side data reference

This table summarizes PostgreSQL data behavior for the connector. Test uncommon extension types with representative data before migration.

Verify uncommon extension types with representative data before relying on the current preview path.