Create a new PostgreSQL CDC capture from scratch using flowctl. Covers all Postgres variants (vanilla, Aurora, RDS, Cloud SQL, Supabase, Neon). Use when user says "capture Postgres", "stream from Postgres", "Postgres CDC", "set up replication slot", or "connect Postgres to Estuary".
How this skill is triggered — by the user, by Claude, or both
Slash command
/estuary-materializations:capture-postgres-createThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Create a PostgreSQL CDC capture that continuously streams changes from Postgres tables into Estuary collections.
Create a PostgreSQL CDC capture that continuously streams changes from Postgres tables into Estuary collections.
Applies to: source-postgres, source-amazon-aurora-postgres, source-amazon-rds-postgres, source-google-cloud-sql-postgres, source-supabase-postgres, source-neon-postgres
Before proceeding, fetch the official connector docs for prerequisites, config reference, and cloud-specific setup.
Always load the main page: https://docs.estuary.dev/reference/Connectors/capture-connectors/PostgreSQL/
Then load the variant subpage based on the user's Postgres type:
| Variant | Docs URL |
|---|---|
| Self-hosted / Vanilla | Main page covers this |
| Amazon Aurora | Main page covers this |
| Azure Database | Main page covers this |
| Amazon RDS | https://docs.estuary.dev/reference/Connectors/capture-connectors/PostgreSQL/amazon-rds-postgres/ |
| Google Cloud SQL | https://docs.estuary.dev/reference/Connectors/capture-connectors/PostgreSQL/google-cloud-sql-postgres/ |
| Supabase | https://docs.estuary.dev/reference/Connectors/capture-connectors/PostgreSQL/Supabase/ |
| Neon | https://docs.estuary.dev/reference/Connectors/capture-connectors/PostgreSQL/neon-postgres/ |
Use WebFetch to load these pages. Together they cover:
This skill provides the flowctl workflow and troubleshooting that docs don't cover.
Before writing any YAML, ask the user:
false) or full event history (true)Always use the latest numbered version tag. Query the connector registry to find it:
# For standard Postgres
flowctl raw get --table connector_tags \
--query 'documentation_url=eq.https://go.estuary.dev/source-postgres' \
--query 'select=image_tag,documentation_url' \
--output yaml
To find any Postgres variant version:
flowctl raw get --table connector_tags \
--query 'documentation_url=ilike.*<connector-name>*' \
--query 'select=image_tag,documentation_url' \
--output yaml
Choose the connector image based on the user's Postgres variant:
| Variant | Connector Image |
|---|---|
| Vanilla / self-hosted | ghcr.io/estuary/source-postgres |
| Amazon RDS | ghcr.io/estuary/source-amazon-rds-postgres |
| Amazon Aurora | ghcr.io/estuary/source-amazon-aurora-postgres |
| Google Cloud SQL | ghcr.io/estuary/source-google-cloud-sql-postgres |
| Supabase | ghcr.io/estuary/source-supabase-postgres |
| Neon | ghcr.io/estuary/source-neon-postgres |
Walk the user through the prerequisites from the docs page loaded in Step 0. Key items:
SHOW wal_level; must return logicalmax_slot_wal_keep_size (50GB is a good starting point, more for high-change-rate databases). Without this, Postgres retains unbounded WAL if the capture ever stalls, which can fill the disk and crash the database. The connector does not check this — it's a safety net for the source database, not a replication prerequisite.CREATE PUBLICATION flow_publication FOR ALL TABLES;If the user is on a managed service (RDS, Cloud SQL, Supabase), refer to the cloud-specific section in the docs.
Build flow.yaml using the config reference from the docs. Minimal required config:
captures:
<tenant>/<path>/source-postgres:
endpoint:
connector:
image: ghcr.io/estuary/source-postgres:<version>
config:
address: "<host>:<port>"
database: "<database_name>"
user: "<username>"
credentials:
auth_type: "UserPassword"
password: "<password>"
historyMode: false
bindings: []
For SSH tunnel, add networkTunnel.sshForwarding block — see docs for full config.
For AWS IAM or GCP IAM auth, set credentials.auth_type accordingly — see docs for required fields.
flowctl discover --source flow.yaml
Note: The first discover attempt occasionally fails with a generic error. If this happens, simply retry — it typically succeeds on the second attempt.
What this does:
flow.yaml with bindingsGenerated file structure:
flow.yaml # Updated with bindings
source-postgres.config.yaml # SOPS-encrypted credentials
<tenant>/
flow.yaml # Import file
<path>/
flow.yaml # Collection definitions
public/
<table>.write.schema.yaml # Write schema per table
<table>.read.schema.yaml # Read schema per table
After discovery, review the generated bindings in flow.yaml:
target fieldflowctl catalog publish --source flow.yaml --auto-approve
The --auto-approve flag is required for non-interactive use.
# Check status (expect PENDING → Backfilling → OK: Streaming CDC Events)
flowctl catalog status <tenant>/<path>/source-postgres
# View recent logs
flowctl logs --task <tenant>/<path>/source-postgres --since 5m | jq -c '{ts, message}'
# Read captured data
flowctl collections read --collection <tenant>/<path>/public/<table> --uncommitted | head -20
Status progression:
WARNING: waiting for task shards to be ready (PENDING) — normal for ~30 secondsBackfilling Tables (N tables backfilling) — initial data syncOK: Streaming CDC Events — capture is running normallyInsert a row in the source database and verify it appears in the collection within seconds:
flowctl collections read --collection <tenant>/<path>/public/<table> --uncommitted | \
jq 'select(.<key_field> == "<test_value>")'
Cause: Wrong image tag (e.g., :dev)
Fix: Query connector_tags for the correct version (see Step 2).
Cause: Config structure is wrong. Common mistakes:
credentials.passwordcredentials.auth_type: "UserPassword"historyMode: falseCause: Database not reachable from Estuary cloud
Fix:
ngrok tcp 5432Cause: User lacks REPLICATION permission
Fix:
ALTER USER flow_capture WITH REPLICATION;
GRANT pg_read_all_data TO flow_capture; -- Postgres 14+
Cause: Publication not created or wrong name in advanced config
Fix:
CREATE PUBLICATION flow_publication FOR ALL TABLES;
Cause: Publication exists but has no tables, or the capture user lacks permissions to see them. This is a very common support issue.
Debug:
-- Check publication has tables (run as admin)
SELECT p.pubname, pt.schemaname, pt.tablename
FROM pg_publication p
JOIN pg_publication_tables pt ON p.oid = pt.pubid
ORDER BY p.pubname, pt.schemaname, pt.tablename;
-- Check capture user can see tables (run as capture user)
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'pg_internal', 'information_schema');
If the publication is empty, add tables or recreate with FOR ALL TABLES. If the capture user sees fewer tables than admin, it's a permissions/schema visibility issue.
Wait 30-60 seconds — this is normal during shard assignment. If still stuck:
flowctl logs --task <tenant>/<path>/source-postgres --since 5m | jq 'select(.level == "error" or .level == "warn")'
Warning: relation "public.flow_watermarks" does not exist
Impact: Capture still works, but backfill accuracy may be affected.
Fix: Create the watermarks table per docs prerequisites.
This is a known intermittent issue. Simply retry flowctl discover --source flow.yaml — it typically succeeds on the second attempt.
connector-disable-enable — Pause/restart existing capturesconnector-delete-recreate — Nuclear option for stuck capturesestuary-logs — Deep log analysisestuary-catalog-status — Status checkingSearches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
Guides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Implements vector databases with Pinecone, Weaviate, Qdrant, Milvus, pgvector for semantic search, RAG, recommendations, and similarity systems. Optimizes embeddings, indexing, and hybrid search.
npx claudepluginhub estuary/agent-skills --plugin estuary-materializations