【实战指南】PostgreSQL pg_clickhouse 0.1.0 插件完整安装与使用教程

用户3107127

发布于 2026-07-09 15:54:47

【实战指南】PostgreSQL pg_clickhouse 0.1.0 插件完整安装与使用教程

ClickHouse 官方开发的 pg_clickhouse 插件，能让 PostgreSQL 直接调用 ClickHouse 的分析能力，实现毫秒级海量数据查询！无需重写 SQL，就能享受 ClickHouse 针对 OLAP 场景的极致优化～

👉 官方文档：https://github.com/ClickHouse/pg_clickhouse/blob/main/doc/pg_clickhouse.md

该插件核心是「外部数据包装器（foreign data wrapper）」，支持：

PostgreSQL 13 及以上版本
ClickHouse 23 及以上版本

📌 版本控制策略

pg_clickhouse 遵循「语义化版本控制（Semantic Versioning）」：

主版本号（Major）：API 不兼容变更
次版本号（Minor）：新增向后兼容功能
修订版本号（Patch）：二进制兼容的 Bug 修复

版本跟踪说明

安装后 PostgreSQL 会记录两个版本：

库版本：PostgreSQL 18+ 中由 PG_MODULE_MAGIC 定义，含完整语义化版本，通过 pg_get_loaded_modules() 查看
扩展版本：仅含主/次版本，通过 pg_catalog.pg_extension 或 \dx pg_clickhouse 查看

升级规则

修订版本更新（如 0.1.0 → 0.1.1）：无需执行任何操作，直接生效
主/次版本更新（如 0.1 → 0.2）：需执行 ALTER EXTENSION pg_clickhouse UPDATE 完成升级

🚀 安装指南

提供 3 种安装方式，按需选择：

方式 1：Docker 快速体验（推荐新手）

无需复杂配置，一键启动预装 pg_clickhouse 的 PostgreSQL 容器：

docker run --name pg_clickhouse -ePOSTGRES_PASSWORD=my_pass \
       -d ghcr.io/clickhouse/pg_clickhouse:18

进入容器内的 PostgreSQL 终端：

docker exec -it pg_clickhouse psql -U postgres

方式 2：从源代码编译（Linux 环境）

1. 安装依赖

RedHat / CentOS / Yum 系统：

sudo yum install \
  postgresql-server \
  libcurl-devel \
  libuuid-devel \
  openssl-libs \
  automake \
  cmake \
  gcc

Debian / Ubuntu / APT 系统（补充）：

sudo apt install \
  postgresql-server-18 \
  libcurl4-openssl-dev \
  uuid-dev \
  libssl-dev \
  make \
  cmake \
  g++

2. 编译安装

# 克隆源码（若从 GitHub 安装）
git clone https://github.com/ClickHouse/pg_clickhouse.git
cd pg_clickhouse

# 若存在多个 PostgreSQL 版本，指定 pg_config 路径
exportPG_CONFIG=/usr/lib/postgresql/18/bin/pg_config

# 编译安装
make
sudomake install

方式 3：通过 PGXN 安装（推荐生产环境）

满足依赖后，用 PGXN 客户端一键安装：

# 先安装 pgxnclient（如 CentOS：yum install pgxnclient）
pgxn install pg_clickhouse

特殊情况处理

1. curl_config 未找到

exportCURL_CONFIG=/opt/homebrew/opt/curl/bin/curl-config
make && sudomake install

2. Makefile 语法错误

# 使用 GNU make（部分系统命名为 gmake）
gmake && gmake install && gmake installcheck

3. pg_config 未找到

# 安装 PostgreSQL 开发包（如 yum install postgresql-devel）
exportPG_CONFIG=/path/to/pg_config
make && sudomake install

4. 自定义安装路径（PostgreSQL 18+）

sudomake install prefix=/usr/local/extras

需在 postgresql.conf 中添加：

extension_control_path = '/usr/local/extras/postgresql/share:$system'
dynamic_library_path   = '/usr/local/extras/postgresql/lib:$libdir'

安装验证

执行测试套件确认安装成功：

make installcheck PGUSER=postgres

⚙️ 核心操作：插件启用与配置

1. 加载扩展

以超级用户身份连接 PostgreSQL，执行：

-- 直接安装到默认 schema
CREATE EXTENSION pg_clickhouse;

-- 或指定 schema 安装
CREATESCHEMA ch;
CREATE EXTENSION pg_clickhouse WITHSCHEMA ch;

2. 核心配置步骤（必做）

步骤 1：创建外部服务器（连接 ClickHouse）

CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(
           driver 'binary',  -- 可选 binary（二进制协议）或 http（HTTP 接口）
           host 'localhost', -- ClickHouse 主机
           dbname 'taxi'     -- 目标 ClickHouse 数据库
       );

支持的 OPTIONS 参数：

参数	说明	默认值
driver	连接驱动（binary/http）	无（必填）
dbname	ClickHouse 数据库名	default
host	ClickHouse 主机名	localhost
port	端口（binary：9004/9440；http：8123/8443，Cloud 环境自动适配）	自动匹配驱动和主机类型

步骤 2：创建用户映射（PostgreSQL → ClickHouse）

CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (
           user 'default',  -- ClickHouse 用户名
           password ''      -- 可选：ClickHouse 密码
       );

步骤 3：导入 ClickHouse 表（外部表映射）

-- 创建本地 schema 存储外部表
CREATE SCHEMA taxi;

-- 导入 ClickHouse 中 taxi 数据库的所有表
IMPORT FOREIGN SCHEMA taxi FROM SERVER taxi_srv INTO taxi;

-- 仅导入指定表
IMPORT FOREIGN SCHEMA taxi LIMIT TO (trips) FROM SERVER taxi_srv INTO taxi;

-- 排除指定表
IMPORT FOREIGN SCHEMA taxi EXCEPT (users) FROM SERVER taxi_srv INTO taxi;

3. 验证配置成功

-- 查看外部表列表
\det+ taxi.*

-- 查看表结构
\d taxi.trips

-- 测试查询（下推到 ClickHouse 执行）
SELECT count(*) FROM taxi.trips;

查询计划验证：执行 EXPLAIN 确认查询下推：

EXPLAIN select count(*) from taxi.trips;
-- 输出含「外部扫描」且根节点为聚合，说明完全下推

🔧 常用 SQL 命令参考

扩展管理

-- 升级扩展
ALTER EXTENSION pg_clickhouse UPDATE;

-- 迁移扩展到新 schema
ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

-- 删除扩展（需先删除依赖对象）
DROP EXTENSION pg_clickhouse CASCADE;

外部服务器管理

-- 修改外部服务器配置
ALTER SERVER taxi_srv OPTIONS (SET driver 'http');

-- 删除外部服务器
DROP SERVER taxi_srv CASCADE;

用户映射管理

-- 修改用户映射
ALTER USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (SET user 'demo');

-- 删除用户映射
DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

外部表管理

-- 手动创建外部表（而非 IMPORT）
CREATE FOREIGN TABLE uact (
    user_id    bigint NOT NULL,
    page_views int,
    duration   smallint,
    sign       smallint
) SERVER taxi_srv OPTIONS(
    table_name 'uact',
    engine 'CollapsingMergeTree'
);

-- 修改外部表列选项
ALTER TABLE uact ALTER COLUMN page_views OPTIONS (SET AggregateFunction 'count');

-- 删除外部表
DROP FOREIGN TABLE uact CASCADE;

📊 数据类型映射表

ClickHouse 类型	PostgreSQL 类型	备注
Bool	boolean	-
Date	date	-
DateTime	timestamp	-
Decimal	numeric	-
Float32	real	-
Float64	double precision	-
IPv4/IPv6	inet	-
Int8/Int16	smallint	-
Int32	integer	-
Int64	bigint	-
JSON	jsonb	仅支持 HTTP 引擎
String	text	-
UInt16	integer	-
UInt32/UInt64	bigint	UInt64 超界会报错
UUID	uuid	-
注意：导入含大写/空格的 ClickHouse 表/列名时，PostgreSQL 会自动添加双引号，查询需显式使用双引号（如 SELECT "Name" FROM taxi.trips）。

🚀 实战案例：纽约出租车数据分析师

通过真实数据集演示 pg_clickhouse 的核心用法，步骤完整可复现！

前提准备：启动 ClickHouse 并导入数据

1. 启动 ClickHouse 容器

docker run -d --network host --name clickhouse -p 8123:8123 -p 9000:9000 --ulimit nofile=262144:262144 clickhouse
docker exec -it clickhouse clickhouse-client

2. 创建表并导入数据

CREATE DATABASE taxi;

-- 创建 trips 表（含 40+ 字段，完整结构如下）
CREATE TABLE taxi.trips
(
    trip_id UInt32,
    vendor_id Enum8(
        '1'      =  1, '2'      =  2, '3'      =  3, '4'      =  4,
        'CMT'    =  5, 'VTS'    =  6, 'DDS'    =  7, 'B02512' = 10,
        'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14,
        ''       = 15
    ),
    pickup_date Date,
    pickup_datetime DateTime,
    dropoff_date Date,
    dropoff_datetime DateTime,
    store_and_fwd_flag UInt8,
    rate_code_id UInt8,
    pickup_longitude Float64,
    pickup_latitude Float64,
    dropoff_longitude Float64,
    dropoff_latitude Float64,
    passenger_count UInt8,
    trip_distance Float64,
    fare_amount Decimal(10, 2),
    extra Decimal(10, 2),
    mta_tax Decimal(10, 2),
    tip_amount Decimal(10, 2),
    tolls_amount Decimal(10, 2),
    ehail_fee Decimal(10, 2),
    improvement_surcharge Decimal(10, 2),
    total_amount Decimal(10, 2),
    payment_type Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    trip_type UInt8,
    pickup FixedString(25),
    dropoff FixedString(25),
    cab_type Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    pickup_nyct2010_gid Int8,
    pickup_ctlabel Float32,
    pickup_borocode Int8,
    pickup_ct2010 String,
    pickup_boroct2010 String,
    pickup_cdeligibil String,
    pickup_ntacode FixedString(4),
    pickup_ntaname String,
    pickup_puma UInt16,
    dropoff_nyct2010_gid UInt8,
    dropoff_ctlabel Float32,
    dropoff_borocode UInt8,
    dropoff_ct2010 String,
    dropoff_boroct2010 String,
    dropoff_cdeligibil String,
    dropoff_ntacode FixedString(4),
    dropoff_ntaname String,
    dropoff_puma UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;

导入 S3 上的纽约出租车数据集：

INSERT INTO taxi.trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    trip_id UInt32,
    vendor_id Enum8(
        '1'      =  1, '2'      =  2, '3'      =  3, '4'      =  4,
        'CMT'    =  5, 'VTS'    =  6, 'DDS'    =  7, 'B02512' = 10,
        'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14,
        ''       = 15
    ),
    pickup_date Date,
    pickup_datetime DateTime,
    dropoff_date Date,
    dropoff_datetime DateTime,
    store_and_fwd_flag UInt8,
    rate_code_id UInt8,
    pickup_longitude Float64,
    pickup_latitude Float64,
    dropoff_longitude Float64,
    dropoff_latitude Float64,
    passenger_count UInt8,
    trip_distance Float64,
    fare_amount Decimal(10, 2),
    extra Decimal(10, 2),
    mta_tax Decimal(10, 2),
    tip_amount Decimal(10, 2),
    tolls_amount Decimal(10, 2),
    ehail_fee Decimal(10, 2),
    improvement_surcharge Decimal(10, 2),
    total_amount Decimal(10, 2),
    payment_type Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    trip_type UInt8,
    pickup FixedString(25),
    dropoff FixedString(25),
    cab_type Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    pickup_nyct2010_gid Int8,
    pickup_ctlabel Float32,
    pickup_borocode Int8,
    pickup_ct2010 String,
    pickup_boroct2010 String,
    pickup_cdeligibil String,
    pickup_ntacode FixedString(4),
    pickup_ntaname String,
    pickup_puma UInt16,
    dropoff_nyct2010_gid UInt8,
    dropoff_ctlabel Float32,
    dropoff_borocode UInt8,
    dropoff_ct2010 String,
    dropoff_boroct2010 String,
    dropoff_cdeligibil String,
    dropoff_ntacode FixedString(4),
    dropoff_ntaname String,
    dropoff_puma UInt16
") SETTINGS input_format_try_infer_datetimes = 0;

验证数据（约 200 万行）：

SELECT count() FROM taxi.trips;
quit

案例 1：基础统计查询

-- 开启计时
\timing on

-- 1. 计算平均小费金额（9ms 完成）
SELECT round(avg(tip_amount), 2) FROM taxi.trips;

-- 2. 按乘客数量分组计算平均总费用（27ms）
SELECT
    passenger_count,
    avg(total_amount)::NUMERIC(10, 2) AS average_total_amount
FROM taxi.trips
GROUP BY passenger_count;

案例 2：按时间/地域聚合分析

-- 1. 每日各社区接单量统计（31ms）
SELECT
    pickup_date,
    pickup_ntaname,
    SUM(1) AS number_of_trips
FROM taxi.trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC LIMIT 10;

-- 2. 按行程时长分组分析（45ms）
SELECT
    avg(tip_amount) AS avg_tip,
    avg(fare_amount) AS avg_fare,
    round((date_part('epoch', dropoff_datetime) - date_part('epoch', pickup_datetime)) / 60) as trip_minutes
FROM taxi.trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC LIMIT 5;

案例 3：字典关联查询

1. 创建 ClickHouse 字典（地区映射表）

SELECT clickhouse_raw_query($$
    CREATE DICTIONARY taxi.taxi_zone_dictionary (
        LocationID Int64 DEFAULT 0,
        Borough String, -- 行政区（如 Manhattan）
        zone String,    -- 社区名称
        service_zone String
    )
    PRIMARY KEY LocationID
    SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
    LIFETIME(MIN 0 MAX 0) -- 禁用自动更新
    LAYOUT(HASHED_ARRAY())
$$, 'host=localhost dbname=taxi');

-- 导入字典到 PostgreSQL
IMPORT FOREIGN SCHEMA taxi LIMIT TO (taxi_zone_dictionary) FROM SERVER taxi_srv INTO taxi;

2. 字典关联分析（前往机场的行程按行政区统计）

-- 使用 dictGet 函数（66ms）
SELECT
    count(1) AS total,
    COALESCE(NULLIF(dictGet(
        'taxi.taxi_zone_dictionary', 'Borough',
        toUInt64(pickup_nyct2010_gid)
    ), ''), 'Unknown') AS borough_name
FROM taxi.trips
WHERE dropoff_nyct2010_gid IN (132, 138) -- JFK/LGA 机场
GROUP BY borough_name
ORDER BY total DESC;

-- 或用 JOIN 语法（48ms）
SELECT
    count(1) AS total,
    "Borough" AS borough_name
FROM taxi.trips
JOIN taxi.taxi_zone_dictionary
  ON trips.pickup_nyct2010_gid = toUInt64(taxi.taxi_zone_dictionary."LocationID")
WHERE pickup_nyct2010_gid > 0
  AND dropoff_nyct2010_gid IN (132, 138)
GROUP BY "Borough"
ORDER BY total DESC;

⚠️ 注意事项

避免 SELECT *：明确指定所需列，减少数据传输开销
数据类型兼容：关联查询时需确保字段类型一致（如用 toUInt64 转换）
字典优势：ClickHouse 字典是内存维度表，关联性能优于普通 JOIN
查询下推：复杂查询用 EXPLAIN 验证是否完全下推，避免本地计算
会话设置：可通过 SET pg_clickhouse.session_settings = 'join_use_nulls 1, final 1' 配置 ClickHouse 会话参数

📋 支持的函数与下推能力

1. 基础下推函数

日期函数：date_part（映射 toDayOfMonth 等）、date_trunc（映射 toStartOfDay 等）
字符串函数：btrim（→ trimBoth）、strpos（→ position）、regexp_like（→ match）
数组函数：array_position（→ indexOf）

2. 自定义函数

字典查询：dictGet(dict_name, col_name, key)
类型转换：toUInt8/toUInt16/toUInt64 等

3. 聚合函数下推

基础聚合：count
自定义聚合：argMax/argMin/uniq/quantile 等
有序集合聚合：percentile_cont（→ quantile）

总结

通过 pg_clickhouse，PostgreSQL 能无缝对接 ClickHouse 的 OLAP 能力，既保留了 PostgreSQL 的事务特性，又获得了海量数据的快速分析能力！适合需要在现有 PostgreSQL 架构中增强分析性能的场景～

如果对你有帮助，欢迎点赞收藏～有问题可以在评论区交流，也可参考官方 GitHub Issues 或 ClickHouse 社区求助～

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-12-19，如有侵权请联系 cloudcommunity@tencent.com 删除

教程

本文分享自 ByteHouse 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

【实战指南】PostgreSQL pg_clickhouse 0.1.0 插件完整安装与使用教程

【实战指南】PostgreSQL pg_clickhouse 0.1.0 插件完整安装与使用教程

【实战指南】PostgreSQL pg_clickhouse 0.1.0 插件完整安装与使用教程

📌 版本控制策略

版本跟踪说明

升级规则

🚀 安装指南

方式 1：Docker 快速体验（推荐新手）

方式 2：从源代码编译（Linux 环境）

1. 安装依赖

2. 编译安装

方式 3：通过 PGXN 安装（推荐生产环境）

特殊情况处理

1. curl_config 未找到

2. Makefile 语法错误

3. pg_config 未找到

4. 自定义安装路径（PostgreSQL 18+）

安装验证

⚙️ 核心操作：插件启用与配置

1. 加载扩展

2. 核心配置步骤（必做）

步骤 1：创建外部服务器（连接 ClickHouse）

步骤 2：创建用户映射（PostgreSQL → ClickHouse）

步骤 3：导入 ClickHouse 表（外部表映射）

3. 验证配置成功

🔧 常用 SQL 命令参考

扩展管理

外部服务器管理

用户映射管理

外部表管理

📊 数据类型映射表

🚀 实战案例：纽约出租车数据分析师

前提准备：启动 ClickHouse 并导入数据

1. 启动 ClickHouse 容器

2. 创建表并导入数据

案例 1：基础统计查询

案例 2：按时间/地域聚合分析

案例 3：字典关联查询

1. 创建 ClickHouse 字典（地区映射表）

2. 字典关联分析（前往机场的行程按行政区统计）

⚠️ 注意事项

📋 支持的函数与下推能力

1. 基础下推函数

2. 自定义函数

3. 聚合函数下推

总结

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐