- DDL
- Configuration file
SOURCE(POSTGRESQL(
port 5432
host 'postgresql-hostname'
user 'postgres_user'
password 'postgres_password'
db 'db_name'
table 'table_name'
replica(host 'example01-1' port 5432 priority 1)
replica(host 'example01-2' port 5432 priority 2)
where 'id=10'
invalidate_query 'SQL_QUERY'
query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))
<source>
<postgresql>
<host>postgresql-hostname</hoat>
<port>5432</port>
<user>clickhouse</user>
<password>qwerty</password>
<db>db_name</db>
<table>table_name</table>
<where>id=10</where>
<invalidate_query>SQL_QUERY</invalidate_query>
<query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
</postgresql>
</source>
Setting fields:
| Setting | Description |
|---|---|
host | The host on the PostgreSQL server. You can specify it for all replicas, or for each one individually (inside <replica>). |
port | The port on the PostgreSQL server. You can specify it for all replicas, or for each one individually (inside <replica>). |
user | Name of the PostgreSQL user. You can specify it for all replicas, or for each one individually (inside <replica>). |
password | Password of the PostgreSQL user. You can specify it for all replicas, or for each one individually (inside <replica>). |
replica | Section of replica configurations. There can be multiple sections. |
replica/host | The PostgreSQL host. |
replica/port | The PostgreSQL port. |
replica/priority | The replica priority. When attempting to connect, ClickHouse traverses the replicas in order of priority. The lower the number, the higher the priority. |
db | Name of the database. |
table | Name of the table. |
where | The selection criteria. The syntax for conditions is the same as for WHERE clause in PostgreSQL. For example, id > 10 AND id < 20. Optional. |
invalidate_query | Query for checking the dictionary status. Optional. Read more in the section Refreshing dictionary data using LIFETIME. |
background_reconnect | Reconnect to replica in background if connection fails. Optional. |
query | The custom query. Optional. |
The
table or where fields cannot be used together with the query field. And either one of the table or query fields must be declared.