This is a document on setting a Clickhouse Cloud peer for PeerDB.

If you are using ClickHouse Cloud, we recommend using PeerDB Cloud, which comes with free trial and highly competitive pricing and PeerDB Cloud is specifically designed for ClickHouse Cloud.

​
Steps on setting up the ClickHouse Cloud peer

Head over the SQL Console in the ClickHouse Cloud UI.

Clickhouse Cloud SQL Console

​
Permissions

  1. Create a separate database for the ClickHouse peer called peerdb. This is where tables will be auto-created and synced by PeerDB.
   CREATE DATABASE peerdb;
  1. Create a Clickhouse user for PeerDB with the following permissions:
-- We recommend creating a separate user for PeerDB
CREATE USER peerdb_user IDENTIFIED BY '<password>';

-- PeerDB needs to create tables and insert data into the tables.
-- Drop table permission is needed for DROP MIRROR support
GRANT INSERT, SELECT, DROP, CREATE TABLE ON peerdb.* to peerdb_user;

-- PeerDB uses an intermediary S3 stage for performance
GRANT CREATE TEMPORARY TABLE, s3 on *.* to peerdb_user;

-- For automatic column-addition on the tables in the mirror
GRANT ALTER ADD COLUMN ON peerdb.* to peerdb_user;

​
Whitelist PeerDB Cloud IPs

If you are using PeerDB Cloud, you need to whitelist the PeerDB Cloud IPs in the ClickHouse Cloud settings tab.

Clickhouse Cloud SQL Console

You can find the IPs of your PeerDB instance here.

  1. ClickHouse has several ports exposed, and PeerDB requires to use a port that exposes the ClickHouse native protocol which by default is 9440 for the secure TLS-enabled port and 9000 for the non-TLS port. If the default ports have been changed, please adjust them accordingly.

We do not recommend using the non-TLS enabled port for ClickHouse. If this is needed, please also check the Disable TLS? option when creating the Clickhouse peer (as seen below).

  1. If you are using PeerDB OSS, we use MinIO as the internal transient stage. You might need to adjust your firewall rules to allow connections from ClickHouse to MinIO. In PeerDB Cloud, we abstract all of this from you. If you run into issues, please reach out to support@peerdb.io or join our Slack channel.
  2. Create the ClickHouse Peer through PeerDB UI

    Create ClickHouse Peer

    Enter all the details Name, Host, Port (as determined above), User, Password and Database.
  3. Click on Validate to ensure that the connection is successful.
  4. Click on Create to create the ClickHouse Peer.

​
Troubleshooting

Here is a list of some validation errors that you might encounter and how to resolve them:

You may need to disable TLS

You may need to disable TLS. Validation errors such as the following may occur if the connection is not successful:

 failed to ping to Clickhouse peer: tls:
 first record does not look like a TLS handshake

EOF

If you see the following error, it means that the connection to the ClickHouse peer could not be established:

failed to open connection to Clickhouse peer:
failed to ping to Clickhouse peer: EOF

Please try the following:

  1. Ensure that the PeerDB IP is whitelisted in the ClickHouse server. If you are using PeerDB Cloud, you can find the IPs of your PeerDB instance here.
  2. You may need to enable TLS.

Stuck on Validating…

This means the port entered is incorrect. Please ensure that the port is correct and the ClickHouse server is reachable from the PeerDB server.

Unsupported PostgreSQL ProvidersClickHouse Setup Guide