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

# 1. 安装扩展软件包
./pgsql.yml -l pg-meta -t pg_extension -e '{"pg_extensions":["pgvector"]}'

# 2. 预加载扩展（如需要，修改后需重启）
pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'

# 3. 在数据库中启用扩展
psql -d meta -c 'CREATE EXTENSION vector;'

pig install pgvector                         # 安装扩展包
psql -d meta -c 'CREATE EXTENSION vector;'   # 在数据库中启用

pg_extensions: [ pgvector, postgis, timescaledb ]  # 使用包别名

# 使用通用类别别名（自动适配当前 PG 版本）
pg_extensions: [ pgsql-gis, pgsql-rag, pgsql-fts ]

# 或使用版本特定的类别别名
pg_extensions: [ pg18-gis, pg18-rag, pg18-fts ]

all:
  vars:
    repo_extra_packages: [ pgvector, postgis, timescaledb, pg_duckdb ]

# 重新下载软件包到本地仓库
./infra.yml -t repo_build

# 刷新所有节点的软件源缓存
./node.yml -t node_repo

# 在节点上添加上游软件源
./node.yml -t node_repo -e node_repo_modules=node,pgsql

pg_packages:                           # 全局基础包
  - pgsql-main pgsql-common
pg_extensions:                         # 集群扩展
  - postgis timescaledb pgvector

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_extensions: [ postgis, timescaledb, pgvector, pg_duckdb ]

# 修改配置后使用剧本安装
./pgsql.yml -l pg-meta -t pg_extension

# 或直接在命令行指定扩展
./pgsql.yml -l pg-meta -t pg_extension -e '{"pg_extensions":["pg_duckdb"]}'

# 使用 pig 安装扩展
pig install pg_duckdb

# 批量安装
ansible pg-meta -b -a 'pig install pg_duckdb pgvector'

# EL 系统
sudo yum install -y pg_duckdb_18*

# Debian/Ubuntu 系统
sudo apt install -y postgresql-18-pg-duckdb

pg_extensions:
  - pgvector           # 自动翻译为 pgvector_18* (EL) 或 postgresql-18-pgvector (Debian)
  - postgis            # 自动翻译为 postgis36_18* (EL) 或 postgresql-18-postgis-3* (Debian)
  - pgsql-gis          # 类别别名，安装整个 GIS 类别的扩展

pg_extensions:
  - pgvector_18*                    # EL 系统的原始包名
  - postgresql-18-pgvector          # Debian 系统的原始包名

-- 查看已安装的扩展
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- 查看扩展文件是否存在
\dx

pg_libs: 'citus, timescaledb, pg_stat_statements, auto_explain'

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions: [ timescaledb, postgis, pgvector ]

# 添加 timescaledb 到预加载库
pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'

# 重启集群使配置生效
pg restart pg-meta

ALTER SYSTEM SET shared_preload_libraries = 'timescaledb, pg_stat_statements, auto_explain';

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'pg_cron, pg_stat_statements, auto_explain'
    pg_parameters:
      cron.database_name: postgres           # pg_cron 使用的数据库
      pg_stat_statements.track: all          # 跟踪所有语句
      auto_explain.log_min_duration: 1000    # 记录超过 1 秒的查询

-- 修改参数
ALTER SYSTEM SET pg_stat_statements.track = 'all';

-- 重新加载配置
SELECT pg_reload_conf();

# 使用 patronictl 修改
pg edit-config pg-meta --force -p 'pg_stat_statements.track=all'

SHOW shared_preload_libraries;

-- 查看所有可用扩展
SELECT * FROM pg_available_extensions;

-- 查看特定扩展
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- 查看已启用的扩展
SELECT * FROM pg_extension;

-- 创建扩展
CREATE EXTENSION vector;

-- 创建扩展到指定 Schema
CREATE EXTENSION postgis SCHEMA public;

-- 自动安装依赖的扩展
CREATE EXTENSION postgis_topology CASCADE;

-- 如果不存在则创建
CREATE EXTENSION IF NOT EXISTS vector;

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: meta
        extensions:
          - { name: vector }                         # 使用默认 Schema
          - { name: postgis, schema: public }        # 指定 Schema
          - { name: pg_stat_statements, schema: monitor }

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_databases:
      - name: meta
        extensions:
          - { name: timescaledb }  # 需要预加载

-- postgis_topology 依赖 postgis
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

-- 或使用 CASCADE 自动安装依赖
CREATE EXTENSION postgis_topology CASCADE;

-- 使用 wal2json 创建逻辑复制槽
SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

-- 查看扩展详情
\dx+ vector

-- 查看扩展包含的对象
SELECT * FROM pg_extension_config_dump('vector');

-- 查看扩展版本
SELECT extversion FROM pg_extension WHERE extname = 'vector';

# EL 系统
sudo yum update pgvector_18*

# Debian/Ubuntu 系统
sudo apt update && sudo apt upgrade postgresql-18-pgvector

# 更新指定集群的扩展包
./pgsql.yml -l pg-meta -t pg_extension -e '{"pg_extensions":["pgvector"]}'

# 使用 pig 包管理器
pig update pgvector

-- 查看已安装扩展及其版本
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

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

-- 更新到最新版本
ALTER EXTENSION pgvector UPDATE;

-- 更新到指定版本
ALTER EXTENSION pgvector UPDATE TO '0.8.0';

-- 查看扩展的可用升级路径
SELECT * FROM pg_extension_update_paths('pgvector');

-- 查看扩展依赖
SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgvector');

-- 删除扩展
DROP EXTENSION pgvector;

-- 如果有依赖对象，需要级联删除
DROP EXTENSION pgvector CASCADE;

-- 查看依赖于某扩展的对象
SELECT
    classid::regclass,
    objid,
    deptype
FROM pg_depend
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgvector');

-- 查看使用了扩展类型的表
SELECT
    c.relname AS table_name,
    a.attname AS column_name,
    t.typname AS type_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE t.typname = 'vector';

# 修改 shared_preload_libraries，移除扩展
pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'

# 重启使配置生效
pg restart pg-meta

# EL 系统
sudo yum remove pgvector_18*

# Debian/Ubuntu 系统
sudo apt remove postgresql-18-pgvector

# 使用 pig 包管理器
pig remove pgvector

# 1. 检查依赖关系
psql -d mydb -c "SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgvector');"

# 2. 删除数据库中的扩展
psql -d mydb -c "DROP EXTENSION pgvector;"

# 3. 如果是预加载扩展，从 shared_preload_libraries 移除
pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'

# 4. 重启数据库（如果修改了预加载配置）
pg restart pg-meta

# 5. 可选：卸载软件包
sudo yum remove pgvector_18*

all:
  vars:
    # 修改默认安装的扩展包
    pg_packages:
      - pgsql-main pgsql-common
      - pg_repack_$v* wal2json_$v*

    # 修改默认安装的扩展
    pg_extensions: [ postgis, timescaledb, pgvector ]

    # 修改默认预加载的扩展
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'

    # 修改默认启用的扩展
    pg_default_extensions:
      - { name: pg_stat_statements, schema: monitor }
      - { name: pg_repack }
      # ... 添加更多

# 添加 GPG 公钥
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

# 刷新缓存
sudo yum makecache

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

# 添加 GPG 公钥
curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg

# 获取发行版代号并添加仓库
distro_codename=$(lsb_release -cs)
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/${distro_codename} ${distro_codename} main
EOF

# 刷新缓存
sudo apt update

curl -fsSL https://repo.pigsty.cc/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg

distro_codename=$(lsb_release -cs)
sudo tee /etc/apt/sources.list.d/pigsty.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.cc/apt/infra generic main
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.cc/apt/pgsql/${distro_codename} ${distro_codename} main
EOF