管理 PostgreSQL 扩展插件

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_extensions: [ postgis, timescaledb, pgvector ]           # <--- 安装扩展软件包
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'    # <--- 配置预加载扩展
    pg_databases:
      - name: meta
        extensions: [ postgis, timescaledb, vector ]            # <--- 在数据库中启用

#all.children.pg-meta.vars: # 省略上级缩进
pg_extensions: [ postgis, timescaledb, pgvector ]

pig install postgis timescaledb       # 安装多个扩展
pig install pgvector -v 17            # 针对特定 PG 大版本安装

ansible pg-test -b -a 'pig install pg_duckdb'   # 使用 Ansible 在集群上批量安装

# EL 系统（RHEL、Rocky、Alma、Oracle Linux）
sudo yum install -y pgvector_17*

# Debian / Ubuntu 系统
sudo apt install -y postgresql-17-pgvector

repo_extra_packages: [ pgvector, postgis, timescaledb ]

./node.yml -t node_repo -e node_repo_modules=node,pgsql   # 添加 PGDG 与 Pigsty 上游仓库

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'   # 预加载扩展
    pg_extensions: [ timescaledb, postgis, pgvector ]          # 安装扩展包

pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'
pg restart pg-meta   # 修改 pg-meta 集群的参数，并重启集群使配置生效

pg_databases:
  - name: meta
    extensions:
      - vector                             # 简单形式
      - { name: postgis, schema: public }  # 指定 Schema

-- 查看可升级的扩展
SELECT name, installed_version, default_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL AND installed_version <> default_version;

-- 更新扩展到最新版本
ALTER EXTENSION vector UPDATE;

-- 更新到指定版本
ALTER EXTENSION vector UPDATE TO '0.8.1';

DROP EXTENSION vector;              -- 删除扩展
DROP EXTENSION vector CASCADE;      -- 级联删除（删除依赖对象）

pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'
pg restart pg-meta   # 重启使配置生效

SELECT extname, extversion, nspname AS schema
FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid
ORDER BY extname;

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL   -- 仅显示已安装的
ORDER BY name;

SELECT * FROM pg_available_extensions WHERE name = 'vector';

SELECT e.extname, d.refobjid::regclass AS depends_on
FROM pg_extension e
JOIN pg_depend d ON d.objid = e.oid
WHERE d.deptype = 'e' AND e.extname = 'postgis_topology';

SELECT classid::regclass, objid, deptype
FROM pg_depend
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'vector');

\dx                    # 列出已启用的扩展
\dx+ vector            # 显示扩展详情

./node.yml -t node_repo -e node_repo_modules=node,pgsql        # 添加 PGDG 与 Pigsty 仓库
./node.yml -t node_repo -e node_repo_modules=node,pgsql,local  # 包括本地仓库

# Pigsty 仓库
curl -fsSL https://repo.pigsty.io/key | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null
curl -fsSL https://repo.pigsty.io/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo >/dev/null

# 中国大陆镜像
curl -fsSL https://repo.pigsty.cc/key | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null
curl -fsSL https://repo.pigsty.cc/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo >/dev/null

curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg
sudo tee /etc/apt/sources.list.d/pigsty.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/infra generic main
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/pgsql $(lsb_release -cs) main
EOF
sudo apt update

# 中国大陆镜像：将 repo.pigsty.io 替换为 repo.pigsty.cc

CREATE EXTENSION postgis;                    -- 先创建基础扩展
CREATE EXTENSION postgis_topology;           -- 再创建依赖扩展
-- 或
CREATE EXTENSION postgis_topology CASCADE;   -- 自动创建依赖

SELECT * FROM pg_available_extension_versions WHERE name = 'vector';