ClickPipes for Postgres FAQ

```
SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), '0/0') / 1024 / 1024 AS wal_generated_mb;
```

- Run the above query at different times of the day, especially during highly transactional periods.
- Calculate how much WAL is generated per 24-hour period.
- Multiply that number by 2 or 3 to provide sufficient retention.
- Set `max_slot_wal_keep_size` to the resulting value in MB or GB.

##### Example[​](#example "Direct link to Example")

If your database generates 100 GB of WAL per day, set:

```
max_slot_wal_keep_size = 200GB
```

### I'm seeing a ReceiveMessage EOF error in the logs. What does it mean?[​](#im-seeing-a-receivemessage-eof-error-in-the-logs-what-does-it-mean "Direct link to I'm seeing a ReceiveMessage EOF error in the logs. What does it mean?")

`ReceiveMessage` is a function in the Postgres logical decoding protocol that reads messages from the replication stream. An EOF (End of File) error indicates that the connection to the Postgres server was unexpectedly closed while trying to read from the replication stream.

It is a recoverable, completely non-fatal error. ClickPipes will automatically attempt to reconnect and resume the replication process.

It can happen for a few reasons:

- **Low wal\_sender\_timeout:** Make sure `wal_sender_timeout` is 5 minutes or higher. This setting controls how long the server waits for a response from the client before closing the connection. If the timeout is too low, it can lead to premature disconnections.
- **Network Issues:** Temporary network disruptions can cause the connection to drop.
- **Postgres Server Restart:** If the Postgres server is restarted or crashes, the connection will be lost.

### My replication slot is invalidated. What should I do?[​](#my-replication-slot-is-invalidated-what-should-i-do "Direct link to My replication slot is invalidated. What should I do?")

The only way to recover ClickPipe is by triggering a resync, which you can do in the Settings page.

The most common cause of replication slot invalidation is a low `max_slot_wal_keep_size` setting on your PostgreSQL database (e.g., a few gigabytes). We recommend increasing this value. [Refer to this section](/docs/integrations/clickpipes/postgres/faq#recommended-max_slot_wal_keep_size-settings) on tuning `max_slot_wal_keep_size`. Ideally, this should be set to at least 200GB to prevent replication slot invalidation.

In rare cases, we have seen this issue occur even when `max_slot_wal_keep_size` isn't configured. This could be due to an intricate and rare bug in PostgreSQL, although the cause remains unclear.

### I am seeing out of memory (OOMs) on ClickHouse while my ClickPipe is ingesting data. Can you help?[​](#i-am-seeing-out-of-memory-ooms-on-clickhouse-while-my-clickpipe-is-ingesting-data-can-you-help "Direct link to I am seeing out of memory (OOMs) on ClickHouse while my ClickPipe is ingesting data. Can you help?")

One common reason for OOMs on ClickHouse is that your service is undersized. This means that your current service configuration doesn't have enough resources (e.g., memory or CPU) to handle the ingestion load effectively. We strongly recommend scaling up the service to meet the demands of your ClickPipe data ingestion.

Another reason we've observed is the presence of downstream Materialized Views with potentially unoptimized joins:

- A common optimization technique for JOINs is if you have a `LEFT JOIN` where the right-hand side table is very large. In this case, rewrite the query to use a `RIGHT JOIN` and move the larger table to the left-hand side. This allows the query planner to be more memory efficient.
- Another optimization for JOINs is to explicitly filter the tables through `subqueries` or `CTEs` and then perform the `JOIN` across these subqueries. This provides the planner with hints on how to efficiently filter rows and perform the `JOIN`.

### I am seeing an `invalid snapshot identifier` during the initial load. What should I do?[​](#i-am-seeing-an-invalid-snapshot-identifier-during-the-initial-load-what-should-i-do "Direct link to i-am-seeing-an-invalid-snapshot-identifier-during-the-initial-load-what-should-i-do")

The `invalid snapshot identifier` error occurs when there is a connection drop between ClickPipes and your Postgres database. This can happen due to gateway timeouts, database restarts, or other transient issues.

It is recommended that you don't carry out any disruptive operations like upgrades or restarts on your Postgres database while Initial Load is in progress and ensure that the network connection to your database is stable.

To resolve this issue, you can trigger a resync from the ClickPipes UI. This will restart the initial load process from the beginning.

### What happens if I drop a publication in Postgres?[​](#what-happens-if-i-drop-a-publication-in-postgres "Direct link to What happens if I drop a publication in Postgres?")

Dropping a publication in Postgres will break your ClickPipe connection since the publication is required for the ClickPipe to pull changes from the source. When this happens, you'll typically receive an error alert indicating that the publication no longer exists.

To recover your ClickPipe after dropping a publication:

1. Create a new publication with the same name and required tables in Postgres
2. Click the 'Resync tables' button in the Settings tab of your ClickPipe

This resync is necessary because the recreated publication will have a different Object Identifier (OID) in Postgres, even if it has the same name. The resync process refreshes your destination tables and restores the connection.

Alternatively, you can create an entirely new pipe if preferred.

Note that if you're working with partitioned tables, make sure to create your publication with the appropriate settings:

```
CREATE PUBLICATION clickpipes_publication
FOR TABLE <...>, <...>
WITH (publish_via_partition_root = true);
```

### What if I am seeing `Unexpected Datatype` errors or `Cannot parse type XX ...`[​](#what-if-i-am-seeing-unexpected-datatype-errors "Direct link to what-if-i-am-seeing-unexpected-datatype-errors")

This error typically occurs when the source Postgres database has a datatype which can't be mapped during ingestion.
For more specific issue, refer to the possibilities below.

### I'm seeing errors like `invalid memory alloc request size <XXX>` during replication/slot creation[​](#postgres-invalid-memalloc-bug "Direct link to postgres-invalid-memalloc-bug")

There was a bug introduced in Postgres patch versions 17.5/16.9/15.13/14.18/13.21 due to which certain workloads can cause an exponential increase in memory usage, leading to a memory allocation request >1GB which Postgres considers invalid. This bug [has been fixed](https://github.com/postgres/postgres/commit/d87d07b7ad3b782cb74566cd771ecdb2823adf6a) and will be in the next Postgres patch series (17.6...). Please check with your Postgres provider when this patch version will be available for upgrade. If an upgrade isn't immediately possible, a resync of the pipe will be needed as it hits the error.

### I need to maintain a complete historical record in ClickHouse, even when the data is deleted from the source Postgres database. Can I completely ignore DELETE and TRUNCATE operations from Postgres in ClickPipes?[​](#ignore-delete-truncate "Direct link to I need to maintain a complete historical record in ClickHouse, even when the data is deleted from the source Postgres database. Can I completely ignore DELETE and TRUNCATE operations from Postgres in ClickPipes?")

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.