ClickPipes for Postgres FAQ

### How are JSON and JSONB columns replicated from Postgres?[​](#how-are-json-and-jsonb-columns-replicated-from-postgres "Direct link to How are JSON and JSONB columns replicated from Postgres?")

JSON and JSONB columns are replicated as String type in ClickHouse. Since ClickHouse supports a native [JSON type](/docs/sql-reference/data-types/newjson), you can create a materialized view over the ClickPipes tables to perform the translation if needed. Alternatively, you can use [JSON functions](/docs/sql-reference/functions/json-functions) directly on the String columns. We're actively working on a feature that replicates JSON and JSONB columns directly to the JSON type in ClickHouse. This feature is expected to be available in a few months.

### What happens to inserts when a mirror is paused?[​](#what-happens-to-inserts-when-a-mirror-is-paused "Direct link to What happens to inserts when a mirror is paused?")

When you pause the mirror, the messages are queued up in the replication slot on the source Postgres, ensuring they're buffered and not lost. However, pausing and resuming the mirror will re-establish the connection, which could take some time depending on the source.

During this process, both the sync (pulling data from Postgres and streaming it into the ClickHouse raw table) and normalize (from raw table to target table) operations are aborted. However, they retain the state required to resume durably.

- For sync, if it is canceled mid-way, the confirmed\_flush\_lsn in Postgres isn't advanced, so the next sync will start from the same position as the aborted one, ensuring data consistency.
- For normalize, the ReplacingMergeTree insert order handles deduplication.

In summary, while sync and normalize processes are terminated during a pause, it is safe to do so as they can resume without data loss or inconsistency.

### Can ClickPipe creation be automated or done via API or CLI?[​](#can-clickpipe-creation-be-automated-or-done-via-api-or-cli "Direct link to Can ClickPipe creation be automated or done via API or CLI?")

A Postgres ClickPipe can also be created and managed via [OpenAPI](https://clickhouse.com/docs/cloud/manage/openapi) endpoints. This feature is in beta, and the API reference can be found [here](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/beta). We're actively working on Terraform support to create Postgres ClickPipes as well.

### How do I speed up my initial load?[​](#how-do-i-speed-up-my-initial-load "Direct link to How do I speed up my initial load?")

You can't speed up an already running initial load. However, you can optimize future initial loads by adjusting certain settings. By default, the settings are configured with 4 parallel threads and a snapshot number of rows per partition set to 100,000. These are advanced settings and are generally sufficient for most use cases.

For Postgres versions 13 or lower, CTID range scans are very slow and therefore ClickPipes does not use them. Instead we read the entire table as a single partition, essentially making it single-threaded (therefore ignoring both number of rows per partition and parallel threads settings). To speed up the initial load in that case, you can increase the `snapshot number of tables in parallel` or specify a custom, indexed partitioning column for large tables.

### How should I scope my publications when setting up replication?[​](#how-should-i-scope-my-publications-when-setting-up-replication "Direct link to How should I scope my publications when setting up replication?")

You can let ClickPipes manage your publications (requires additional permissions) or create them yourself. With ClickPipes-managed publications, we automatically handle table additions and removals as you edit the pipe. If self-managing, carefully scope your publications to only include tables you need to replicate - including unnecessary tables will slow down Postgres WAL decoding.

If you include any table in your publication, make sure it has either a primary key or `REPLICA IDENTITY FULL`. If you have tables without a primary key, creating a publication for all tables will cause DELETE and UPDATE operations to fail on those tables.

To identify tables without primary keys in your database, you can use this query:

```
SELECT table_schema, table_name
FROM information_schema.tables
WHERE
    (table_catalog, table_schema, table_name) NOT IN (
        SELECT table_catalog, table_schema, table_name
        FROM information_schema.table_constraints
        WHERE constraint_type = 'PRIMARY KEY') AND
    table_schema NOT IN ('information_schema', 'pg_catalog', 'pgq', 'londiste');
```

You have two options when dealing with tables without primary keys:

1. **Exclude tables without primary keys from ClickPipes**:
   Create the publication with only the tables that have a primary key:

   ```
   CREATE PUBLICATION clickpipes_publication FOR TABLE table_with_primary_key1, table_with_primary_key2, ...;
   ```
2. **Include tables without primary keys in ClickPipes**:
   If you want to include tables without a primary key, you need to alter their replica identity to `FULL`. This ensures that UPDATE and DELETE operations work correctly:

   ```
   ALTER TABLE table_without_primary_key1 REPLICA IDENTITY FULL;
   ALTER TABLE table_without_primary_key2 REPLICA IDENTITY FULL;
   CREATE PUBLICATION clickpipes_publication FOR TABLE <...>, <...>;
   ```

Tip

If you're creating a publication manually instead of letting ClickPipes manage it, we don't recommend creating a publication `FOR ALL TABLES`, this leads to more traffic from Postgres to ClickPipes (to sending changes for other tables not in the pipe) and reduces overall efficiency.

For manually created publications, please add any tables you want to the publication before adding them to the pipe.

Note

If you're replicating from a Postgres read replica/hot standby, you will need to create your own publication on the primary instance, which will automatically propagate to the standby. The ClickPipe won't be able to manage the publication in this case as you're unable to create publications on a standby.

### Recommended `max_slot_wal_keep_size` settings[​](#recommended-max_slot_wal_keep_size-settings "Direct link to recommended-max_slot_wal_keep_size-settings")

- **At Minimum:** Set [`max_slot_wal_keep_size`](https://www.postgresql.org/docs/devel/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE) to retain at least **two days' worth** of WAL data.
- **For Large Databases (High Transaction Volume):** Retain at least **2-3 times** the peak WAL generation per day.
- **For Storage-Constrained Environments:** Tune this conservatively to **avoid disk exhaustion** while ensuring replication stability.

#### How to calculate the right value[​](#how-to-calculate-the-right-value "Direct link to How to calculate the right value")

To determine the right setting, measure the WAL generation rate:

##### For PostgreSQL 10+[​](#for-postgresql-10 "Direct link to For PostgreSQL 10+")

```
SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), '0/0') / 1024 / 1024 AS wal_generated_mb;
```

##### For PostgreSQL 9.6 and below:[​](#for-postgresql-96-and-below "Direct link to For PostgreSQL 9.6 and below:")

```
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?")