ClickPipes for Postgres FAQ

Yes! Before creating your Postgres ClickPipe, create a publication without DELETE operations. For example:

```
CREATE PUBLICATION <pub_name> FOR TABLES IN SCHEMA <schema_name> WITH (publish = 'insert,update');
```

Then when [setting up](https://clickhouse.com/docs/integrations/clickpipes/postgres#configuring-the-replication-settings) your Postgres ClickPipe, make sure this publication name is selected.

Note that TRUNCATE operations are ignored by ClickPipes and won't be replicated to ClickHouse.

### Why can I not replicate my table which has a dot in it?[​](#replicate-table-dot "Direct link to Why can I not replicate my table which has a dot in it?")

PeerDB has a limitation currently where dots in source table identifiers - aka either schema name or table name - isn't supported for replication as PeerDB can't discern, in that case, what is the schema and what is the table as it splits on dot.
Effort is being made to support input of schema and table separately to get around this limitation.

### Initial load completed but there is no/missing data on ClickHouse. What could be the issue?[​](#initial-load-issue "Direct link to Initial load completed but there is no/missing data on ClickHouse. What could be the issue?")

If your initial load has completed without error but your destination ClickHouse table is missing data, it might be that you have RLS (Row Level Security) policies enabled on your source Postgres tables.
Also worth checking:

- If the user has sufficient permissions to read the source tables.
- If there are any row policies on ClickHouse side which might be filtering out rows.

### Can I have the ClickPipe create a replication slot with failover enabled?[​](#failover-slot "Direct link to Can I have the ClickPipe create a replication slot with failover enabled?")

Yes, for a Postgres ClickPipe with replication mode as CDC or Snapshot + CDC, you can have ClickPipes create a replication slot with failover enabled, by toggling the below switch in the `Advanced Settings` section while creating the ClickPipe. Note that your Postgres version must be 17 or above to use this feature.

If the source is configured accordingly, the slot is preserved after failovers to a Postgres read replica, ensuring continuous data replication. Learn more [here](https://www.postgresql.org/docs/current/logical-replication-failover.html).

### I am seeing errors like `Internal error encountered during logical decoding of aborted sub-transaction`[​](#transient-logical-decoding-errors "Direct link to transient-logical-decoding-errors")

This error suggests a transient issue with the logical decoding of aborted sub-transaction, and is specific to custom implementations of Aurora Postgres. Given the error is coming from `ReorderBufferPreserveLastSpilledSnapshot` routine, this suggests that logical decoding isn't able to read the snapshot spilled to disk. It may be worth trying to increase [`logical_decoding_work_mem`](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-LOGICAL-DECODING-WORK-MEM) to a higher value.

### I am seeing errors like `error converting new tuple to map` or `error parsing logical message` during CDC replication[​](#logical-message-processing-errors "Direct link to logical-message-processing-errors")

Postgres sends information about changes in the form of messages that have a fixed protocol. These errors arise when the ClickPipe receives a message that it is unable to parse, either due to corruption in transit or invalid messages being sent. While the exact issue tends to vary, we've seen several cases from Neon Postgres sources. In case you're seeing this issue with Neon as well, please raise a support ticket with them. In other cases, please reach out to our support team for guidance.

### Can I include columns I initially excluded from replication?[​](#include-excluded-columns "Direct link to Can I include columns I initially excluded from replication?")

This isn't yet supported, an alternative would be to [resync the table](/docs/integrations/clickpipes/postgres/table_resync) whose columns you want to include.

### I am noticing that my ClickPipe has entered Snapshot but data isn't flowing in, what could be the issue?[​](#snapshot-no-data-flow "Direct link to I am noticing that my ClickPipe has entered Snapshot but data isn't flowing in, what could be the issue?")

This can be for a few reasons, mainly around some prerequisites for snapshotting taking longer than usual. For more information, do read our doc on parallel snapshotting [here](/docs/integrations/clickpipes/postgres/parallel_initial_load).

#### Parallel snapshotting is taking time to obtain partitions[​](#parallel-snapshotting-taking-time "Direct link to Parallel snapshotting is taking time to obtain partitions")

Parallel snapshotting has a few initial steps to obtain logical partitions for your tables. If your tables are small, this will finish in a matter of seconds however for very large (order of terabytes) tables, this can take longer. You can monitor the queries running on your Postgres source in the **Source** tab to see if there are any long running queries related to obtaining partitions for snapshotting. Once the partitions are obtained, data will start flowing in.

#### Replication slot creation is transaction locked[​](#replication-slot-creation-transaction-locked "Direct link to Replication slot creation is transaction locked")

In the **Source** tab under the Activity section, you would see the `CREATE_REPLICATION_SLOT` query stuck in `Lock` state. This could be due to another transaction holding locks on objects that Postgres uses to create replication slots.
In order to see the blocking queries, you can run the below query on your Postgres source:

```
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state
FROM pg_locks blocked_lock
JOIN pg_stat_activity blocked
  ON blocked_lock.pid = blocked.pid
JOIN pg_locks blocking_lock
  ON blocking_lock.locktype = blocked_lock.locktype
  AND blocking_lock.database IS NOT DISTINCT FROM blocked_lock.database
  AND blocking_lock.relation IS NOT DISTINCT FROM blocked_lock.relation
  AND blocking_lock.page IS NOT DISTINCT FROM blocked_lock.page
  AND blocking_lock.tuple IS NOT DISTINCT FROM blocked_lock.tuple
  AND blocking_lock.virtualxid IS NOT DISTINCT FROM blocked_lock.virtualxid
  AND blocking_lock.transactionid IS NOT DISTINCT FROM blocked_lock.transactionid
  AND blocking_lock.classid IS NOT DISTINCT FROM blocked_lock.classid
  AND blocking_lock.objid IS NOT DISTINCT FROM blocked_lock.objid
  AND blocking_lock.objsubid IS NOT DISTINCT FROM blocked_lock.objsubid
  AND blocking_lock.pid != blocked_lock.pid
JOIN pg_stat_activity blocking
  ON blocking_lock.pid = blocking.pid
WHERE NOT blocked_lock.granted;
```

Once you identify the blocking query, you can decide to either wait for it to finish or cancel it if it's not critical. After the blocking query is resolved, the replication slot creation should proceed, allowing the snapshot to start and data to flow in.