持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第11天，点击查看活动详情
前言
我们讲解过Mysql的数据同步，这一节我们讲解PostgreSQL数据同步，大体是一致的，不同的在PostgreSQL的配置上。同样，PostgreSQL表引擎允许从ClickHouse对存储在远程PostgreSQLServer上的数据执行SELECT和INSERT操作。注意：Postgresql和clickshoue都安装在Linux服务器上面，且需要保证这两个可以正常运行。
正文
案例一
postgresql配置(PG操作)
a) 在postgresql.conf文件中添加以下条目以使PostgreSQL能够侦听网络接口
listen_addresses = '*'
b) 创建一个PG的用户并设置角色
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
c) 创建库表
CREATE DATABASE db_in_psg;
CREATE TABLE table1 (
id integer primary key,
column1 varchar(10)
);
d) 插入几条测试数据
INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');
d) 要配置PostgreSQL以允许与新用户连接到新数据库进行复制，请将以下条目添加到pg_hba.conf文件。使用PostgreSQL服务器的子网或IP地址更新地址
# TYPE DATABASE USER ADDRESS METHOD
host db_in_psg clickhouse_user 192.168.1.0/24 password
e) 重新加载配置，并验证是否可以登录
/usr/pgsql-12/bin/pg_ctl reload
psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>
Clickhouse操作
a) 通过终端登录clickhouse
clickhouse-client --user default --password ClickHouse123!
b) 创建库表
CREATE DATABASE db_in_ch;
CREATE TABLE db_in_ch.table1
(
id UInt64,
column1 String
)
ENGINE = PostgreSQL('postgres-host.domain.com:5432', 'db_in_psg', 'table1', 'clickhouse_user', 'ClickHouse_123');
环境配置好了，现在我们来操作一下。
案例实操
a) 在clickhouse输入查询语句
SELECT * FROM db_in_ch.table1
b) 在postgresql插入数据，然后再在clickhouse查询表数据。
INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');
SELECT * FROM db_in_ch.table1
c) 在clickhouse中插入几行数据
INSERT INTO db_in_ch.table1
(id, column1)
VALUES
(5, 'mno'),
(6, 'pqr');
d) 在clickhouse和postgresql都查询一下表数据 我们会发现，他们数据是一样的，因此我们通过clickouse的http接口插入数据至Postgresql中，而clickhouse查询的时候也是走http去postgresql做查询，将结果返回至clickhouse客户端。
案例二
PostgreSQL数据库引擎使用PostgreSQLreplication功能创建包含所有或部分模式和表的数据库副本，操作的 以下过程中，将使用PostgreSQL CLI（psql）和ClickHouse CLI（ClickHouse客户端）。
postgresql配置
a) postgresql配置 在postgresql.conf，设置最小侦听级别、replication wal级别为逻辑复制 和replication插槽不少于2。
listen_addresses = '*'
max_replication_slots = 2
wal_level = logical
b) 创建一个用户，并设置角色为SUPERUSER
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
c) 创建库表并插入数据
CREATE DATABASE db1;
\connect db1 ;
CREATE TABLE table1 (
id integer primary key,
column1 varchar(10)
);
INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');
d) 在pg_hba.conf配置，运行新用户登录
# TYPE DATABASE USER ADDRESS METHOD
host db1 clickhouse_user 192.168.1.0/24 password
e) 重新加载配置，并测试登录
/usr/pgsql-12/bin/pg_ctl reload
psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>
clickhouse操作
a) 登录clickhouse
clickhouse-client --user default --password ClickHouse123!
b) 开启实验功能
SET allow_experimental_database_materialized_postgresql=1
c) 利用物化视图创建一个副本库
CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';
d) 查看数据
select * from db1_postgres.table1;
数据同步测试实操
a) 在postgresql插入数据
INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');
b) 在clickhouse查看数据
select * from db1_postgres.table1;
当然，我们也可以测试修改以及删除等操作。
总结
本节主要讲解Postgresql实时同步，案例一：clickhouse充当postgresql的一个客户端，不存数据，只是通过http操作postgresql，结果返回给clickhouse。案例二：clickhouse作为postgresql的副本，是一个实时同步的案例，clickhouse存储postgresql的数据。
