Ubuntu 上的 PostgreSQL 扩展功能全景
一 核心扩展类别与代表
- 性能与诊断：pg_stat_statements（统计与识别慢 SQL）、auto_explain（自动记录执行计划）、pg_prewarm（缓存预热）。
- 运维与作业：pg_cron（数据库内定时任务）、pgAgent（作业调度代理）。
- 全文与文本检索：pg_trgm（相似度与模糊匹配）、pg_bigm（任意内容模糊搜索）、中文分词如 zhparser/pg_jieba/pg_scws。
- 外部数据与联邦查询：file_fdw（文件外部表）、ogr_fdw（读写多格式 GIS/矢量/栅格）、mysql_fdw（连接 MySQL）。
- 空间与轨迹：PostGIS（矢量/栅格/拓扑/三维）、pgrouting（路网分析）、pointcloud（LiDAR 点云）、h3-pg（H3 六边形索引）、MobilityDB（移动对象/轨迹）。
- 时序与图分析：TimescaleDB（时序数据引擎）、Citus（分布式/水平扩展）。
- 其他常用：hstore（键值对）、citext（不区分大小写）、btree_gin/btree_gist（扩展索引能力）。
二 在 Ubuntu 上的安装与启用步骤
- 安装扩展包
- 通用可选包：sudo apt install postgresql-contrib。
- 指定版本示例（将 16 替换为你的主版本）：
- PostGIS：sudo apt install postgresql-16-postgis
- pgRouting：sudo apt install postgresql-16-pgrouting
- ogr_fdw：sudo apt install postgresql-16-ogr-fdw
- pointcloud：sudo apt install postgresql-16-pointcloud
- mysql_fdw：sudo apt install postgresql-16-mysql-fdw
- 在数据库中启用
- 进入数据库：sudo -i -u postgres psql
- 创建扩展：
- 通用：CREATE EXTENSION 扩展名;（如 hstore、pg_stat_statements）
- PostGIS 系列：CREATE EXTENSION postgis;（按需再启用 postgis_raster、postgis_topology、postgis_sfcgal 等）
- 路由与点云：CREATE EXTENSION pgrouting;、CREATE EXTENSION pointcloud; CREATE EXTENSION pointcloud_postgis;
- 外部数据：CREATE EXTENSION ogr_fdw;、CREATE EXTENSION mysql_fdw;
- 验证示例
- PostGIS 版本：SELECT PostGIS_version();
- 慢 SQL 排行：SELECT query, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
三 典型场景与扩展组合
- 空间分析与地图服务：PostGIS（矢量/栅格/拓扑/三维）+ pgrouting（最短路径/旅行商）+ ogr_fdw（直接查询 Shapefile/GeoJSON 等外部数据）+ h3-pg（六边形网格聚合）。
- 路网与城市基础设施：导入 OSM 数据（如 osm2pgrouting），使用 pgrouting 完成路网分析、服务区/最短路径等高级计算。
- 点云与 LiDAR 管理：pointcloud 存储 LAS/LAZ，结合 PDAL 进行点云处理与入库，适合测绘、林业、城市三维。
- 全文与模糊检索：pg_trgm/pg_bigm 实现模糊匹配与相似度检索，中文可叠加 zhparser/pg_jieba/pg_scws 分词。
- 运维与诊断：pg_stat_statements + auto_explain + pg_prewarm 组合，覆盖慢 SQL 发现、执行计划抓取与缓存预热。
- 定时任务与 ETL：pg_cron 在库内跑定时任务，配合 file_fdw/ogr_fdw/mysql_fdw 做轻量数据集成与抽取。
四 实践建议与常见问题
- 版本匹配与备份恢复
- 扩展包名需与 PostgreSQL 主版本一致（如 postgresql-16-*）；跨机器/跨版本 pg_restore 若报找不到扩展控制文件，多为目标库未安装对应扩展包，先安装再恢复。
- 启用顺序与依赖
- 使用 PostGIS 家族时，通常先启用 postgis，再启用 postgis_raster/topology/sfcgal；pointcloud 还需创建 pointcloud_postgis 以与 PostGIS 互操作。
- 外部数据包装器权限
- ogr_fdw/mysql_fdw 创建扩展后，还需建立 FOREIGN SERVER 与 USER MAPPING，并确保数据库角色具备相应权限。
- 生产环境安全
- 密码存储不建议使用 chkpass（较弱），优先 pgcrypto；模糊匹配与全文检索结合合理索引（GIN/GiST）以获得良好性能。