Linking Postgres as a source

The Postgres connector can link your database tables to PostHog. You can use it for regular scheduled syncs, or enable change data capture (CDC) for near real-time syncs that capture inserts, updates, and deletes.

To link Postgres:

1. Go to the Data pipeline page and the sources tab in PostHog
2. Click New source and select Postgres
3. Enter your database connection details:
   • Connection string: An optional PostgreSQL connection string.
   • Host: The hostname or IP your database server like db.example.com or 123.132.1.100.
   • Port: The port your database server is listening to. The default is 5432.
   • Database: The name of the database you want like analytics_db.
   • User: The username with the necessary permissions to access the database.
   • Password: The password for the user.
   • Schema: (Optional) The schema for your database. Leave blank to browse and sync tables from all schemas, or specify one to limit the selection. The default is public.
4. If you need to connect through an SSH tunnel, enable and configure it (optional):
   • Tunnel host: The hostname of your SSH server.
   • Tunnel port: The port your SSH server is listening on.
   • Authentication type:
     • For password authentication, enter your SSH username and password.
     • For key-based authentication, enter your SSH username, private key, and optional passphrase.
   • Require TLS through tunnel? - Whether to require TLS encryption through the SSH tunnel. Enabled by default. Disable if your database does not support TLS.
5. Optional: enable Change data capture (CDC) if your database is prepared for logical replication. You can also enable CDC later from the source's Configuration tab.
6. Click Next, select the tables to sync, choose the sync method for each table, then click Link

The data warehouse then starts syncing your Postgres data. You can see details and progress in the sources tab.

Configuration

Selecting columns

By default, PostHog syncs all columns from each table. To sync only specific columns:

1. During source setup, click Columns next to any table in the table picker.
2. Uncheck columns you don't want to sync.
3. Primary key columns and the incremental sync field (if configured) are always synced and cannot be disabled.

You can also change column selection after setup:

1. Go to the sources tab and click your Postgres source.
2. Click Configure next to any schema.
3. Under Columns, select which columns to sync.

Inbound IP addresses

We use a set of IP addresses to access your instance. To ensure this connector works, add these IPs to your inbound security rules:

Note: PostHog egresses over IPv4 and does not support IPv6-only database hosts. If your database host only resolves to an IPv6 address, use a connection pooler or enable your provider's IPv4 add-on. See the troubleshooting section for details.

Live queries

In addition to syncing data on a schedule, you can enable live queries on a synced Postgres source. This lets you query your Postgres database directly from the SQL editor — queries run against your live database in real time without duplicating data into PostHog.

Enabling live queries on a new source

When creating a new Postgres source, the source wizard offers three query modes:

• Sync to PostHog — Sync selected tables on a schedule. This is the default.
• Query live only — Run queries directly against your database without syncing any data.
• Sync and query live — Sync tables into PostHog and also run live queries against the database from the SQL editor.

Select Sync and query live to enable both scheduled syncs and live queries on the same source.

Enabling live queries on an existing source

1. Go to the sources tab and click your Postgres source.
2. Open the Configuration tab.
3. Toggle Enable live queries on.
4. Click Save to apply.

Once enabled, you can query your Postgres source directly from the SQL editor.

Querying a live source

There are two ways to start a live query:

• From the source page: Go to the Schemas tab on your source and click Query directly. This opens the SQL editor with your source connection preselected.
• From the SQL editor: Open the SQL editor, click the connection picker in the toolbar, and select your Postgres source. Synced sources with live queries enabled are labeled with · synced (e.g., my_source (Postgres · synced)) to distinguish them from direct-only connections.

When to use live queries vs scheduled syncs only

• Live queries are best for ad-hoc exploration, querying data that changes frequently, or when you want to query your database without waiting for a sync.
• Scheduled syncs only are better when you need faster query performance, want to join external data with PostHog events in dashboards and insights, or need data available across all PostHog features.

Sync methods

Postgres tables support full table, incremental, append-only, xmin, and CDC sync methods.

Use CDC when you need PostHog to stay close to your source database and you care about deletes. It is especially useful when:

• Your table has inserts, updates, and deletes.
• You don't have a reliable updated_at or other incremental cursor field.
• Full table refreshes are too expensive for a frequently changing table.
• You want an optional change-history table for audit or lifecycle analysis.

Use incremental or append-only sync instead when your table is append-heavy, has a reliable non-null cursor field, or you don't want to manage logical replication.

Use xmin sync when your table has no reliable incremental cursor field like updated_at and you want incremental syncing without setting up CDC. xmin uses PostgreSQL's hidden xmin system column (the transaction ID that last modified each row) as a cursor. It's a middle ground between full table refresh and CDC:

• Captures inserts and updates incrementally without requiring you to add a cursor column
• Lighter than CDC since it doesn't require logical replication or a replication slot
• Does not capture deletes (use CDC if you need delete tracking)

xmin sync requires:

• PostgreSQL 13 or newer
• Table must have a primary key
• Only works on regular (heap) tables and materialized views (not partitioned parents, views, or foreign tables)

Because the xmin column is not indexed, syncs scan the entire table to find changed rows. For large tables with frequent syncs, this can be slower than indexed incremental sync. PostHog displays a warning when you select xmin sync to flag the unindexed scan.

A _ph_xmin column is added to the synced table in PostHog containing the transaction ID for each row.

How PostHog CDC works

PostHog's Postgres CDC implementation uses PostgreSQL logical replication with the built-in pgoutput plugin.

When you enable CDC on a source (either during creation or afterward):

1. PostHog creates a replication slot for the source database.
2. In PostHog-managed mode, PostHog creates the publication and adds selected CDC tables to it.
3. In self-managed mode, you or your DBA create and update the publication. PostHog verifies that publication exists, then creates and manages the replication slot itself.
4. Each CDC table first runs an initial snapshot through the normal Postgres import pipeline.
5. After the snapshot completes, the table switches to streaming mode.
6. A source-level CDC extraction schedule reads pending WAL changes, writes them to PostHog's data warehouse storage, then advances the replication slot.

PostHog reads changes with pg_logical_slot_peek_binary_changes(...) and confirms progress with pg_replication_slot_advance(...) only after the batch is safely written. CDC extraction is scheduled from the most frequent active CDC table interval. For newly created CDC schemas, PostHog uses a 5 minute interval by default.

The decoder currently handles Postgres logical replication messages for:

• Inserts
• Updates
• Deletes
• Truncates

Truncates are not replayed as row-by-row deletes. If PostHog sees a TRUNCATE, it marks the table for a new snapshot so the destination can recover to the new source state.

Output table modes

For each CDC table, choose how PostHog exposes the captured data.

Consolidated table only

PostHog keeps one destination table with the latest row state per primary key. CDC batches are deduplicated by primary key before being merged. Delete events are represented with CDC metadata columns such as _ph_deleted and _ph_deleted_at.

Use this when you mainly want a current-state table for analytics.

CDC history table only

PostHog creates a _cdc-suffixed table, for example users_cdc. It stores change history using SCD type 2-style columns:

• _ph_cdc_op: I, U, or D
• _ph_cdc_timestamp: the Postgres commit timestamp
• _ph_deleted: whether the event is a delete
• _ph_deleted_at: delete timestamp for delete events
• valid_from: when this version became valid
• valid_to: when this version stopped being current

The initial snapshot seeds the _cdc table as the starting point. History before the initial snapshot is not available.

Both

PostHog creates both the consolidated current-state table and the _cdc history table. This costs more storage and processing, but gives you both easy current-state analysis and a row-level history of changes from the CDC starting point.

CDC requirements

PostHog includes a Check CDC compatibility step in the source configuration screen. It validates the main prerequisites against your database before the source is created, including PostgreSQL version, logical replication, selected table primary keys, SELECT permissions, replication permissions, replication slot capacity, and publication existence for self-managed CDC.

You should still prepare the database before enabling CDC, especially if logical replication is not already enabled.

PostgreSQL version

Postgres CDC requires PostgreSQL 13 or later. PostHog creates publications with publish_via_partition_root = true, which requires Postgres 13+.

Check your version:

SHOW server_version_num;

Logical replication

Your database must have logical replication enabled:

SHOW wal_level;

The value must be logical.

On self-managed Postgres, this usually means setting the following in postgresql.conf, then restarting PostgreSQL:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

On AWS RDS or Aurora PostgreSQL, enable logical replication in the DB parameter group:

rds.logical_replication = 1

Changing this setting requires a database restart. Other managed Postgres providers have their own equivalent logical replication setting.

Replication slot capacity

PostHog creates one logical replication slot per Postgres CDC source. The source database must have a free replication slot:

SHOW max_replication_slots;
SELECT COUNT(*) FROM pg_replication_slots;

Don't reuse an existing replication slot. A slot should have exactly one consumer.

Table primary keys

Every table you sync with CDC must have a primary key. PostHog uses the primary key to merge updates, represent deletes, and build the optional history table.

Tables without a primary key won't be offered as CDC-capable tables in the setup flow.

Database user permissions

The PostHog database user needs schema USAGE and table SELECT permissions for every schema and table you want to sync. You don't need to grant access to public unless you're syncing tables from the public schema.

If all selected tables are in one schema, grant access to that schema:

GRANT USAGE ON SCHEMA "analytics" TO "posthog";
GRANT SELECT ON ALL TABLES IN SCHEMA "analytics" TO "posthog";
ALTER DEFAULT PRIVILEGES IN SCHEMA "analytics" GRANT SELECT ON TABLES TO "posthog";

If you're syncing tables across multiple schemas, repeat the grants for each selected schema, or grant SELECT on only the specific tables you selected:

GRANT USAGE ON SCHEMA "analytics" TO "posthog";
GRANT USAGE ON SCHEMA "billing" TO "posthog";
GRANT SELECT ON "analytics"."users", "billing"."invoices" TO "posthog";

It also needs permission to create and read logical replication slots.

For self-managed Postgres or most managed providers:

ALTER USER "posthog" WITH REPLICATION;

For AWS RDS:

GRANT rds_replication TO "posthog";

On RDS, membership in rds_superuser also satisfies PostHog's prerequisite check.

Slot and publication management

PostHog supports two CDC management modes.

PostHog-managed

PostHog creates and manages both the replication slot and publication. It generates names like:

• Slot: posthog_<source id prefix>
• Publication: posthog_pub_<source id prefix>

PostHog-managed mode is the easiest path if the database user has enough privileges. The user needs REPLICATION, SELECT, and ownership of the synced tables or superuser-equivalent permissions so PostHog can add and remove tables from the publication.

When you disable a CDC table, PostHog removes it from the publication. Changes made while the table is disabled are permanently lost from the CDC stream, so re-enabling the table requires a full resync.

When you delete the source, PostHog soft-deletes the warehouse tables and attempts to drop the PostHog-managed replication slot and publication. Cleanup is best-effort, so you should still verify the slot is gone if the source was failing or unreachable.

Self-managed

Use self-managed mode when you don't want to grant table ownership or superuser-equivalent permissions to the PostHog user.

In this mode:

• You or your DBA create the publication as the table owner.
• PostHog verifies the publication exists.
• PostHog still creates and manages the replication slot itself.
• PostHog still needs REPLICATION, schema USAGE, and table SELECT.

After you select CDC tables, PostHog shows a setup SQL dialog. It looks like this:

-- Reading a replication slot requires REPLICATION, or rds_replication on RDS.
ALTER USER "posthog" WITH REPLICATION;
-- GRANT rds_replication TO "posthog";

GRANT USAGE ON SCHEMA "analytics" TO "posthog";
GRANT SELECT ON "analytics"."users", "analytics"."orders" TO "posthog";

CREATE PUBLICATION "posthog_pub" FOR TABLE "analytics"."users", "analytics"."orders"
  WITH (publish_via_partition_root = true);

Do not manually create the replication slot for normal self-managed setup. PostHog creates the slot with pg_create_logical_replication_slot(..., 'pgoutput').

If you later add a CDC table in self-managed mode, add it to the publication yourself:

ALTER PUBLICATION "posthog_pub" ADD TABLE "analytics"."new_table";

Avoid putting unrelated tables in the publication. PostHog filters out changes for tables that are not active CDC schemas, but the database still has to decode and send those changes.

Managing CDC after source creation

You can enable, disable, repair, or adjust CDC settings on an existing Postgres source from the Configuration tab without recreating the source.

Enabling CDC on an existing source

To enable CDC on a source that was created without it:

1. Go to the sources tab and click your Postgres source.
2. Open the Configuration tab and scroll to the Change data capture (CDC) section.
3. Choose a management mode:
   • PostHog-managed - PostHog creates and manages both the replication slot and publication.
   • Self-managed - You create the publication yourself. PostHog creates and manages the replication slot.
4. Click Check database prerequisites to verify your database is ready.
5. Click Enable CDC (PostHog-managed) or Set up & enable CDC (self-managed).
6. After enabling, go to the Schemas tab and switch individual tables to the CDC sync type.

Disabling CDC

To disable CDC on a source, open the Configuration tab and click Disable CDC.

Disabling CDC:

• Cancels running CDC extraction workflows for that source.
• Drops the replication slot (and publication in PostHog-managed mode) on your source database.
• Soft-deletes any _cdc companion history tables.
• Clears the sync type on all CDC schemas and pauses them. You must pick a new sync strategy (incremental, append, or full refresh) for each schema before syncing resumes.

Repairing CDC

If a CDC source loses its replication slot — either because WAL lag protection dropped it or because it was removed on the source database — CDC syncs stop and PostHog marks the source as broken.

Instead of disabling and re-enabling CDC (which resets every schema's sync type and requires you to reconfigure each one), you can use Repair CDC to recover in place:

1. Go to the sources tab and click your Postgres source.
2. Open the Configuration tab. A banner indicates that the replication slot or publication is missing.
3. Click Repair CDC and confirm the action.

Repair recreates the replication slot and publication on your source database, resets every active CDC schema to snapshot mode for a full re-sync, clears the broken markers, and resumes the paused schedules. Changes that occurred between the slot being lost and the repair completing cannot be recovered — tables will match the current source state once the re-sync finishes.

The action is idempotent. If a repair fails partway through (for example, due to a connection error), you can safely retry it.

Updating CDC settings

While CDC is enabled, you can edit the following settings from the Configuration tab without disabling CDC:

• Automatic slot protection - Toggle whether PostHog drops the replication slot when WAL lag exceeds the critical threshold.
• WAL lag warning threshold - The lag level (in MB) that triggers a warning. Default is 512 MB.
• WAL lag critical threshold - The lag level (in MB) that triggers slot protection. Default is 2048 MB. Must be greater than the warning threshold.

To change the management mode (PostHog-managed vs. self-managed) or slot/publication names, disable CDC and re-enable it with the new settings.

WAL lag protection

Replication slots retain WAL until the consumer advances the slot. If PostHog stops consuming changes, retained WAL can grow.

PostHog stores two lag thresholds on CDC sources:

• Warning threshold: 512 MB by default
• Critical threshold: 2048 MB by default

For PostHog-managed sources, automatic slot protection is enabled by default. If lag exceeds the critical threshold, PostHog attempts to drop the replication slot and publication, then marks the source as broken. This protects your source database from unbounded WAL growth, but it means you must repair CDC or resync after fixing the issue. You can adjust both thresholds from the Configuration tab.

For self-managed sources, PostHog does not drop the slot or publication. It marks the source as errored at the critical threshold, but you remain responsible for cleanup.

PostHog also detects when PostgreSQL has invalidated a slot due to max_slot_wal_keep_size. When this happens, PostHog automatically recreates the replication slot (and publication, if PostHog-managed and missing), resets all CDC schemas to snapshot mode, and resumes streaming. Tables re-sync from their current state without manual intervention.

You can monitor a Postgres slot yourself:

SELECT
  slot_name,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name = 'posthog_your_slot';

Operational risks and limitations

CDC is powerful, but it changes how your production database retains WAL. Keep these constraints in mind:

• Enabling logical replication often requires a database restart.
• A stuck replication slot can fill your database disk with retained WAL.
• PostHog CDC only supports tables with primary keys.
• CDC captures row changes, not a complete history before the initial snapshot.
• PostgreSQL logical replication doesn't send DDL as normal row changes. Schema changes may require a full resync, especially destructive changes like dropping or changing column types.
• Large TOASTed values can be omitted from some update messages when they are unchanged. Validate CDC output carefully if your tables include very large text, jsonb, or bytea columns.
• Disabling a table removes it from the publication in PostHog-managed mode. Changes made while disabled are not recoverable from the slot. Disabling CDC entirely drops the slot and requires a full resync if re-enabled.
• If a publication does not include a selected table, PostHog won't receive changes for that table.
• If a table is truncated, PostHog triggers a new snapshot instead of replaying the truncate as individual deletes.
• If PostHog stops consuming changes long enough for PostgreSQL to invalidate the slot (via max_slot_wal_keep_size), PostHog auto-recovers by recreating the slot and re-syncing tables. The _cdc history has a gap for changes during the outage.

For production databases, we recommend enabling CDC on one or two representative tables first so your team can validate database permissions, WAL settings, monitoring, and internal rollout steps before adding more tables.

Troubleshooting

wal_level must be set to logical

Enable logical replication on the database and restart PostgreSQL. On RDS or Aurora PostgreSQL, set rds.logical_replication = 1 in the parameter group and restart the instance.

Failed to create replication slot or permission denied

This error occurs when the database user lacks permission to create logical replication slots, which is required for CDC.

To fix this, either:

1. Grant replication permissions to the PostHog database user:

   For self-managed Postgres or most managed providers:

   SQL

   Run in PostHog

   PostHog AI

   ALTER USER "posthog" WITH REPLICATION;

   For AWS RDS:

   SQL

   Run in PostHog

   PostHog AI

   GRANT rds_replication TO "posthog";

2. Switch to Incremental sync instead of CDC. Incremental sync only requires SELECT permission and works well for tables with a reliable timestamp or incrementing ID column. Go to the Schemas tab in your source configuration and change the sync type from CDC to Incremental.

See Database user permissions for more details on required permissions.

No replication slot capacity available

Increase max_replication_slots or remove unused slots. Check existing slots with:

SELECT slot_name, plugin, active, database
FROM pg_replication_slots;

Publication does not exist

This happens in self-managed mode when PostHog can't find the publication name you entered. Create the publication as the table owner, then click Verify & create source again.

Source is errored because of WAL lag

Check the slot lag query above. If PostHog-managed slot protection dropped the slot, use Repair CDC to recreate it and re-sync. If you're in self-managed mode, your DBA must decide whether to keep, advance, or drop the slot.

Replication slot or publication is missing

This happens when the replication slot is dropped — either by WAL lag protection, manually on the source database, or by the database itself (for example, when max_slot_wal_keep_size is exceeded). PostHog marks the CDC source as broken and pauses its schedules.

To recover, use Repair CDC from the Configuration tab. Repair recreates the slot and publication, then re-syncs every CDC schema from a fresh snapshot. WAL changes since the slot was lost cannot be recovered.

materialized view has not been populated

This error occurs when you try to sync a materialized view that was created with WITH NO DATA and has never been refreshed. PostgreSQL can't read from an unpopulated materialized view.

To fix this, run the following command in your PostgreSQL database:

REFRESH MATERIALIZED VIEW "your_view_name";

After refreshing, re-enable the sync in PostHog.

exceeded the compute time quota

This error occurs with serverless Postgres providers like Neon when the account or project has exceeded its compute-time quota. The provider suspends the database until the quota resets at the next billing period or the plan is upgraded.

To fix this, upgrade your provider's plan or wait for the quota to reset, then re-enable the sync in PostHog.

cannot call jsonb_each on a non-object

This error occurs when syncing a view or materialized view whose definition calls jsonb_each() or jsonb_each_text() on a JSONB column that contains non-object values (such as arrays, scalars, or null) for at least one row. PostgreSQL evaluates the view at fetch time, and the jsonb_each() function fails on non-object values.

To fix this, update the view definition in your database to guard the jsonb_each() call:

-- Only process rows where the column is a JSON object
SELECT key, value
FROM jsonb_each(your_column)
WHERE jsonb_typeof(your_column) = 'object'

Alternatively, remove the problematic view from your sync selection in PostHog.

Network is unreachable or No route to host

This error occurs when PostHog cannot establish a network route to your PostgreSQL host. The most common cause is an IPv6-only host — PostHog egresses over IPv4, so connections to IPv6-only addresses fail.

This is a non-retryable error — PostHog stops syncing until you fix the underlying issue and re-enable the sync or CDC.

Common causes:

• IPv6-only host — Your database host only resolves to an IPv6 address, but PostHog egresses over IPv4.
• Firewall blocking PostHog's IPs — Your database server's firewall blocks connections from PostHog's IP addresses.
• Private network — The hostname resolves to a private IP address (like 10.x.x.x, 172.16.x.x, or 192.168.x.x).

Solutions:

• Use a connection pooler — Configure a connection pooler (like PgBouncer, Supabase Supavisor, or Neon's pooler) that's reachable over IPv4, then point PostHog at the pooler's host and port.
• Enable IPv4 on your provider — Many managed Postgres providers offer IPv4 add-ons or dual-stack endpoints (e.g., Supabase IPv4 add-on, Neon's shared pooler).
• Allowlist PostHog's IPs — If your firewall is blocking connections, allowlist PostHog's IP addresses (see the Inbound IP addresses section above).
• Use an SSH tunnel — If your database is on a private network, configure an SSH tunnel during source setup.

Slot invalidated by max_slot_wal_keep_size

When PostgreSQL invalidates a replication slot due to max_slot_wal_keep_size, PostHog automatically recovers by recreating the slot and triggering a full re-sync of all CDC tables. You'll see an error message explaining that the slot was invalidated and CDC will resume automatically.

No manual intervention is required. Tables re-sync from their current state and streaming resumes once the snapshot completes. The _cdc history table has a gap for the window between the last confirmed position and the new slot.

Looking for an example of the Postgres source? Check out our tutorial where we connect and query Supabase data.