ClickHouse DB::Exception: PostgreSQL connection failure

The "DB::Exception: PostgreSQL connection failure" occurs when ClickHouse is unable to establish a connection to a PostgreSQL server. The POSTGRESQL_CONNECTION_FAILURE error code indicates that the libpq connection attempt failed — either the PostgreSQL server could not be reached, or the connection was rejected during the handshake.

Impact

This error blocks all ClickHouse operations that depend on the PostgreSQL connection, including queries against tables using the PostgreSQL engine, the postgresql() table function, dictionaries sourced from PostgreSQL, and MaterializedPostgreSQL replication. If this error affects a dictionary, dependent queries may fail or return stale data.

Common Causes

1. PostgreSQL server is not running or not accepting connections.
2. Incorrect hostname, port, database name, or credentials in the ClickHouse configuration.
3. Firewall or network rules blocking the connection from the ClickHouse host to the PostgreSQL port (default 5432).
4. PostgreSQL pg_hba.conf does not allow connections from the ClickHouse host's IP address.
5. SSL/TLS configuration mismatch — PostgreSQL requires SSL but ClickHouse is not configured for it, or vice versa.
6. PostgreSQL max_connections limit reached, rejecting new connections.
7. DNS resolution failure for the PostgreSQL hostname.

Troubleshooting and Resolution Steps

1. Check the ClickHouse server log for the libpq error message, which usually explains the failure:

   grep -i "POSTGRESQL_CONNECTION_FAILURE\|PostgreSQL\|libpq" /var/log/clickhouse-server/clickhouse-server.log | tail -20
   

2. Test connectivity from the ClickHouse host using psql or a similar client:

   psql -h postgres-host -p 5432 -U clickhouse_user -d target_db -c "SELECT 1"
   

3. Verify network connectivity:

   nc -zv postgres-host 5432
   

4. Check the PostgreSQL pg_hba.conf file to ensure the ClickHouse host IP is permitted:

   # Allow ClickHouse server
   host  target_db  clickhouse_user  10.0.1.50/32  scram-sha-256
   

   Reload PostgreSQL after changes:

   sudo systemctl reload postgresql
   

5. Verify the ClickHouse table or dictionary definition:

   CREATE TABLE pg_table
   (
       id Int32,
       name String
   )
   ENGINE = PostgreSQL('postgres-host:5432', 'target_db', 'my_table', 'clickhouse_user', 'password');
   

6. If SSL is required, add the sslmode option:

   ENGINE = PostgreSQL('postgres-host:5432', 'target_db', 'my_table', 'clickhouse_user', 'password', '', 'sslmode=require');
   

7. Check PostgreSQL connection limits:

   -- Run on PostgreSQL
   SELECT count(*) FROM pg_stat_activity;
   SHOW max_connections;
   

Best Practices

• Use a dedicated PostgreSQL user for ClickHouse with the minimum required privileges.
• Configure pg_hba.conf to allow only the specific ClickHouse server IPs rather than broad network ranges.
• Enable SSL for PostgreSQL connections, especially across untrusted networks.
• Monitor PostgreSQL connection counts and set appropriate max_connections values to avoid exhaustion.
• Use named_collections in ClickHouse to centralize PostgreSQL connection settings.
• Test the PostgreSQL connection from the ClickHouse host before creating tables or dictionaries.

Frequently Asked Questions

Q: Why does the connection fail even though I can connect to PostgreSQL from my workstation?
A: The ClickHouse server runs from a different host, so the pg_hba.conf rules and firewall settings may differ. The server IP must be explicitly allowed in PostgreSQL's host-based authentication configuration.

Q: Does ClickHouse support PostgreSQL SSL certificates?
A: Yes. You can pass sslmode, sslcert, sslkey, and sslrootcert parameters in the connection string options to configure client certificate authentication.

Q: Can connection poolers like PgBouncer cause this error?
A: Yes, if PgBouncer is in transaction or statement pooling mode, certain operations may not work as expected. Additionally, if PgBouncer reaches its own connection limits, ClickHouse will receive a connection failure. Make sure PgBouncer's max_client_conn is high enough.

Q: How do I troubleshoot intermittent PostgreSQL connection failures?
A: Intermittent failures often point to network instability, DNS resolution issues, or PostgreSQL connection limit contention. Enable connection logging on PostgreSQL (log_connections = on, log_disconnections = on) and check for patterns.