这是本节的多页打印视图。点击此处打印.

包管理器：pig

PostgreSQL扩展包管理器 pig

1: 快速上手
2: 为啥用它
3: Pigsty 命令行工具说明
4: 仓库管理
5: 扩展管理
6: Pigsty 管理
7: 扩展构建基础设施
8: 可用系统

pig 是一个开源的 PostgreSQL（与扩展）包管理器，在主流Linux (amd64 / arm64) 发行版上可用。

pig 可以屏蔽 PG 安装的复杂度，利用OS原生包管理器一键安装 PostgreSQL 13-17 以及 404 个 PG生态扩展插件。

博客：交付 PostgreSQL 扩展的理想方式！

1 - 快速上手

快速上手 pig 包管理器

简短版本

使用以下命令安装 pig 软件包：

curl -fsSL https://repo.pigsty.io/pig | bash      # 全球 CDN
curl -fsSL https://repo.pigsty.cc/pig | bash      # 中国 CDN

然后 pig 命令遍会在你的系统中可用。假设你想要安装 PostgreSQL 17 ，以及 pg_duckdb 扩展，那么从零开始只需要以下命令：

$ pig repo add pigsty pgdg -u  # 添加 pgdg & pigsty 仓库，然后更新仓库缓存
$ pig ext install pg17         # 从 PGDG 安装 PostgreSQL 17 原生内核包
$ pig ext install pg_duckdb    # （为当前默认PG17）安装 pg_duckdb 扩展插件

是的，一切都已经准备就绪。 pig 能干的事情远不止这些，你可以看看高级用法的说明，以及完整可用的 400+ 扩展列表。

正式安装

The pig util is a standalone go binary with no dependencies. You can install with the script:

curl -fsSL https://repo.pigsty.io/pig | bash   # cloudflare default
curl -fsSL https://repo.pigsty.cc/pig | bash   # mainland china mirror

Or just download and extract the binary, or use the apt/dnf package manager:

For Ubuntu 22.04 / 24.04 & Debian 12 or any compatible platforms:

sudo tee /etc/apt/sources.list.d/pigsty.list > /dev/null <<EOF
deb [trusted=yes] https://repo.pigsty.io/apt/infra generic main 
EOF
sudo apt update; sudo apt install -y pig

For EL 8/9 and compatible platforms:

sudo tee /etc/yum.repos.d/pigsty.repo > /dev/null <<-'EOF'
[pigsty-infra]
name=Pigsty Infra for $basearch
baseurl=https://repo.pigsty.io/yum/infra/$basearch
enabled = 1
gpgcheck = 0
module_hotfixes=1
EOF
sudo yum makecache; sudo yum install -y pig

For mainland china user: consider replace the repo.pigsty.io with repo.pigsty.cc

pig has self update feature, you can update pig itself to the latest version with:

pig update

Or if you only want to update the extension catalog, you can fetch the latest catalog with:

pig ext upgrade

2 - 为啥用它

为什么我们需要一个专为 PostgreSQL 及其生态组件于扩展而设计的包管理器？

有没有想过，安装或升级 PostgreSQL 扩展时，不再需要翻阅过时的 README、晦涩的 configure 脚本，或者随便找的 GitHub 分支和补丁？痛苦的现实是，Postgres 强大的扩展性往往意味着复杂的设置，尤其是在你需要兼顾多个发行版或 CPU 架构时。

这时，Pig 就应运而生了。Pig 是一个基于 Go 的包管理器，旨在一举解决 Postgres 及其超过 400 个扩展的问题。无论是 TimescaleDB、Citus、PGVector，还是 20 多个 Rust 扩展，甚至是自托管 Supabase 所需的所有工具 —— Pig 的统一命令行界面让这些都触手可及。它去除了混乱的源代码编译和半成品的仓库，提供与 Debian、Ubuntu、RedHat 系列以及 x86 和 ARM 架构完美兼容的、版本对齐的 RPM/DEB 包。无需猜测，也无需麻烦。

pig

Pig 并不是重新发明轮子，而是利用你系统原生的包管理器（APT、YUM、DNF），并遵循官方 PGDG 包装规范，确保无缝对接。这意味着你不必在“正确的方式”和“快速的方式”之间做选择；Pig 尊重你现有的仓库，符合操作系统的最佳实践，并且与其他你已经使用的包完美兼容。

准备好在没有繁琐操作的情况下为你的 Postgres 增加超级能力吗？查看 GitHub 了解文档、安装步骤，并一睹其庞大的扩展列表。然后，看看你的本地 Postgres 实例如何变成一个充满专业模块的强大工具 —— 无需黑魔法。如果 Postgres 的未来是不可阻挡的可扩展性，那么 Pig 就是帮助你解锁这一未来的神灯。说实话，没人抱怨自己拥有太多扩展。

《小猪骑大象：PG内核与扩展包管理神器》

《ANNOUNCE pig: The Postgres Extension Wizard》

3 - Pigsty 命令行工具说明

pig 命令行工具总览

功能总览

pig - the Linux Package Manager for PostgreSQL and CLI tool for Pigsty

Usage:
  pig [command]

Examples:

  # get started: check https://github.com/pgsty/pig for details
  pig repo add -ru        # overwrite existing repo & update cache
  pig ext  add pg17       # install optional postgresql 17 package
  pig ext  add pg_duckdb  # install certain postgresql extension

  pig repo : add rm update list info status create boot cache
  pig ext  : add rm update list info status import link build
  pig sty  : init boot conf install get list


PostgreSQL Extension Manager
  ext         Manage PostgreSQL Extensions (pgext)
  repo        Manage Linux Software Repo (apt/dnf)

Pigsty Management Commands
  sty         Manage Pigsty Installation

Additional Commands:
  build       Build Postgres Extension
  completion  Generate the autocompletion script for the specified shell
  help        Help about any command
  status      Show Environment Status
  update      Upgrade pig itself
  version     Show pig version info

Flags:
      --debug              enable debug mode
  -h, --help               help for pig
  -i, --inventory string   config inventory path
      --log-level string   log level: debug, info, warn, error, fatal, panic (default "info")
      --log-path string    log file path, terminal by default
  -t, --toggle             Help message for toggle

Use "pig [command] --help" for more information about a command.

样例

Environment Status

pig status                    # show os & pg & pig status
pig repo status               # show upstream repo status
pig ext  status               # show pg extensions status

Extension Management

pig ext list    [query]       # list & search extension      
pig ext info    [ext...]      # get information of a specific extension
pig ext status  [-v]          # show installed extension and pg status
pig ext add     [ext...]      # install extension for current pg version
pig ext rm      [ext...]      # remove extension for current pg version
pig ext update  [ext...]      # update extension to the latest version
pig ext import  [ext...]      # download extension to local repo
pig ext link    [ext...]      # link postgres installation to path
pig ext upgrade               # fetch the latest extension catalog

Repo Management

pig repo list                    # available repo list
pig repo info   [repo|module...] # show repo info
pig repo status                  # show current repo status
pig repo add    [repo|module...] # add repo and modules
pig repo rm     [repo|module...] # remove repo & modules
pig repo update                  # update repo pkg cache
pig repo create                  # create repo on current system
pig repo boot                    # boot repo from offline package
pig repo cache                   # cache repo as offline package

Pigsty Management

The pig can also be used as a cli tool for Pigsty - the battery-include free PostgreSQL RDS. Which brings HA, PITR, Monitoring, IaC, and all the extensions to your PostgreSQL cluster.

pig sty init     # install embed pigsty to ~/pigsty 
pig sty boot     # install ansible and other pre-deps 
pig sty conf     # auto-generate pigsty.yml config file
pig sty install  # run the install.yml playbook

You can use the pig sty subcommand to bootstrap pigsty on current node.

4 - 仓库管理

如何使用 pig repo 子命令管理 APT/YUM 软件仓库？

总览

pig 工具会自动为用户处理好软件仓库的添加，根据您的网络环境选择合适的仓库（例如在中国大陆使用墙内镜像仓库）。

pig repo list                    # available repo list
pig repo info   [repo|module...] # show repo info
pig repo status                  # show current repo status
pig repo add    [repo|module...] # add repo and modules
pig repo rm     [repo|module...] # remove repo & modules
pig repo update                  # update repo pkg cache
pig repo create                  # create repo on current system
pig repo boot                    # boot repo from offline package
pig repo cache                   # cache repo as offline package

样例

Radical Repo Admin

The default pig repo add pigsty pgdg will add the PGDG repo and PIGSTY repo to your system. While the following command will backup & wipe your existing repo and add all require repo to your system.

pig repo add all --ru        # This will OVERWRITE all existing repo with node,pgdg,pigsty repo

There’s a brutal version of repo add: repo set, which will overwrite you existing repo (-r) by default.

And you can recover you old repos at /etc/apt/backup or /etc/yum.repos.d/backup.

列出仓库

You can list all available repo / module (repo collection) with pig repo list:

$ pig repo list

os_environment: {code: el8, arch: amd64, type: rpm, major: 8}
repo_upstream:  # Available Repo: 32
  - { name: pigsty-local   ,description: 'Pigsty Local'       ,module: local    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'file:///www/pigsty' }
  - { name: pigsty-infra   ,description: 'Pigsty INFRA'       ,module: infra    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://repo.pigsty.io/yum/infra/$basearch' }
  - { name: pigsty-pgsql   ,description: 'Pigsty PGSQL'       ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://repo.pigsty.io/yum/pgsql/el$releasever.$basearch' }
  - { name: nginx          ,description: 'Nginx Repo'         ,module: infra    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://nginx.org/packages/rhel/$releasever/$basearch/' }
  - { name: baseos         ,description: 'EL 8+ BaseOS'       ,module: node     ,releases: [8,9]            ,arch: [x86_64, aarch64]  ,baseurl: 'https://dl.rockylinux.org/pub/rocky/$releasever/BaseOS/$basearch/os/' }
  - { name: appstream      ,description: 'EL 8+ AppStream'    ,module: node     ,releases: [8,9]            ,arch: [x86_64, aarch64]  ,baseurl: 'https://dl.rockylinux.org/pub/rocky/$releasever/AppStream/$basearch/os/' }
  - { name: extras         ,description: 'EL 8+ Extras'       ,module: node     ,releases: [8,9]            ,arch: [x86_64, aarch64]  ,baseurl: 'https://dl.rockylinux.org/pub/rocky/$releasever/extras/$basearch/os/' }
  - { name: powertools     ,description: 'EL 8 PowerTools'    ,module: node     ,releases: [8]              ,arch: [x86_64, aarch64]  ,baseurl: 'https://dl.rockylinux.org/pub/rocky/$releasever/PowerTools/$basearch/os/' }
  - { name: epel           ,description: 'EL 8+ EPEL'         ,module: node     ,releases: [8,9]            ,arch: [x86_64, aarch64]  ,baseurl: 'http://download.fedoraproject.org/pub/epel/$releasever/Everything/$basearch/' }
  - { name: pgdg-common    ,description: 'PostgreSQL Common'  ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg-el8fix    ,description: 'PostgreSQL EL8FIX'  ,module: pgsql    ,releases: [8]              ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/common/pgdg-centos8-sysupdates/redhat/rhel-8-x86_64/' }
  - { name: pgdg13         ,description: 'PostgreSQL 13'      ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg14         ,description: 'PostgreSQL 14'      ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg15         ,description: 'PostgreSQL 15'      ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg16         ,description: 'PostgreSQL 16'      ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg17         ,description: 'PostgreSQL 17'      ,module: pgsql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/17/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg-extras    ,description: 'PostgreSQL Extra'   ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.postgresql.org/pub/repos/yum/common/pgdg-rhel$releasever-extras/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg13-nonfree ,description: 'PostgreSQL 13+'     ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64]           ,baseurl: 'https://download.postgresql.org/pub/repos/yum/non-free/13/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg14-nonfree ,description: 'PostgreSQL 14+'     ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64]           ,baseurl: 'https://download.postgresql.org/pub/repos/yum/non-free/14/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg15-nonfree ,description: 'PostgreSQL 15+'     ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64]           ,baseurl: 'https://download.postgresql.org/pub/repos/yum/non-free/15/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg16-nonfree ,description: 'PostgreSQL 16+'     ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64]           ,baseurl: 'https://download.postgresql.org/pub/repos/yum/non-free/16/redhat/rhel-$releasever-$basearch' }
  - { name: pgdg17-nonfree ,description: 'PostgreSQL 17+'     ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64]           ,baseurl: 'https://download.postgresql.org/pub/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' }
  - { name: timescaledb    ,description: 'TimescaleDB'        ,module: extra    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://packagecloud.io/timescale/timescaledb/el/$releasever/$basearch' }
  - { name: wiltondb       ,description: 'WiltonDB'           ,module: mssql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.copr.fedorainfracloud.org/results/wiltondb/wiltondb/epel-$releasever-$basearch/' }
  - { name: ivorysql       ,description: 'IvorySQL'           ,module: ivory    ,releases: [7,8,9]          ,arch: [x86_64]           ,baseurl: 'https://repo.pigsty.io/yum/ivory/el$releasever.$basearch' }
  - { name: groonga        ,description: 'Groonga'            ,module: groonga  ,releases: [8,9]            ,arch: [x86_64, aarch64]  ,baseurl: 'https://packages.groonga.org/almalinux/$releasever/$basearch/' }
  - { name: mysql          ,description: 'MySQL'              ,module: mysql    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://repo.mysql.com/yum/mysql-8.0-community/el/$releasever/$basearch/' }
  - { name: mongo          ,description: 'MongoDB'            ,module: mongo    ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/8.0/$basearch/' }
  - { name: redis          ,description: 'Redis'              ,module: redis    ,releases: [8,9]            ,arch: [x86_64, aarch64]  ,baseurl: 'https://rpmfind.net/linux/remi/enterprise/$releasever/redis72/$basearch/' }
  - { name: grafana        ,description: 'Grafana'            ,module: grafana  ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://rpm.grafana.com' }
  - { name: docker-ce      ,description: 'Docker CE'          ,module: docker   ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://download.docker.com/linux/centos/$releasever/$basearch/stable' }
  - { name: kubernetes     ,description: 'Kubernetes'         ,module: kube     ,releases: [7,8,9]          ,arch: [x86_64, aarch64]  ,baseurl: 'https://pkgs.k8s.io/core:/stable:/v1.31/rpm/' }
repo_modules:   # Available Modules: 19
  - all       : pigsty-infra, pigsty-pgsql, pgdg-common, pgdg-el8fix, pgdg-el9fix, pgdg17, pgdg16, pgdg15, pgdg14, pgdg13, baseos, appstream, extras, powertools, crb, epel, base, updates, security, backports
  - pigsty    : pigsty-infra, pigsty-pgsql
  - pgdg      : pgdg-common, pgdg-el8fix, pgdg-el9fix, pgdg17, pgdg16, pgdg15, pgdg14, pgdg13
  - node      : baseos, appstream, extras, powertools, crb, epel, base, updates, security, backports
  - infra     : pigsty-infra, nginx
  - pgsql     : pigsty-pgsql, pgdg-common, pgdg-el8fix, pgdg-el9fix, pgdg13, pgdg14, pgdg15, pgdg16, pgdg17, pgdg
  - extra     : pgdg-extras, pgdg13-nonfree, pgdg14-nonfree, pgdg15-nonfree, pgdg16-nonfree, pgdg17-nonfree, timescaledb, citus
  - mssql     : wiltondb
  - mysql     : mysql
  - docker    : docker-ce
  - kube      : kubernetes
  - grafana   : grafana
  - pgml      : pgml
  - groonga   : groonga
  - haproxy   : haproxyd, haproxyu
  - ivory     : ivorysql
  - local     : pigsty-local
  - mongo     : mongo
  - redis     : redis

5 - 扩展管理

如何使用 pig ext 子命令管理 PostgreSQL 扩展插件？

总览

pig ext list    [query]       # list & search extension      
pig ext info    [ext...]      # get information of a specific extension
pig ext status  [-v]          # show installed extension and pg status
pig ext add     [ext...]      # install extension for current pg version
pig ext rm      [ext...]      # remove extension for current pg version
pig ext update  [ext...]      # update extension to the latest version
pig ext import  [ext...]      # download extension to local repo
pig ext link    [ext...]      # link postgres installation to path
pig ext upgrade               # fetch the latest extension catalog

例子

检索扩展

$ pig ext ls olap

INFO[14:48:13] found 13 extensions matching 'olap':
Name            State  Version  Cate  Flags   License       Repo     PGVer  Package               Description
----            -----  -------  ----  ------  -------       ------   -----  ------------          ---------------------
citus           avail  13.0.1   OLAP  -dsl--  AGPL-3.0      PIGSTY   14-17  citus_17*             Distributed PostgreSQL as an extension
citus_columnar  avail  11.3-1   OLAP  -ds---  AGPL-3.0      PIGSTY   14-17  citus_17*             Citus columnar storage engine
columnar        n/a    11.1-11  OLAP  -ds---  AGPL-3.0      PIGSTY   13-16  hydra_17*             Hydra Columnar extension
pg_analytics    avail  0.3.4    OLAP  -ds-t-  PostgreSQL    PIGSTY   14-17  pg_analytics_17       Postgres for analytics, powered by DuckDB
pg_duckdb       avail  0.2.0    OLAP  -dsl--  MIT           PIGSTY   14-17  pg_duckdb_17*         DuckDB Embedded in Postgres
pg_mooncake     avail  0.1.2    OLAP  ------  MIT           PIGSTY   14-17  pg_mooncake_17*       Columnstore Table in Postgres
duckdb_fdw      avail  1.0.0    OLAP  -ds--r  MIT           PIGSTY   13-17  duckdb_fdw_17*        DuckDB Foreign Data Wrapper
pg_parquet      avail  0.2.0    OLAP  -dslt-  PostgreSQL    PIGSTY   14-17  pg_parquet_17         copy data between Postgres and Parquet
pg_fkpart       avail  1.7      OLAP  -d----  GPL-2.0       PIGSTY   13-17  pg_fkpart_17          Table partitioning by foreign key utility
pg_partman      avail  5.2.4    OLAP  -ds---  PostgreSQL    PGDG     13-17  pg_partman_17*        Extension to manage partitioned tables by time or ID
plproxy         avail  2.11.0   OLAP  -ds---  BSD 0-Clause  PIGSTY   13-17  plproxy_17*           Database partitioning implemented as procedural language
pg_strom        avail  5.2.2    OLAP  -ds--x  PostgreSQL    PGDG     13-17  pg_strom_17*          PG-Strom - big-data processing acceleration using GPU and NVME
tablefunc       added  1.0      OLAP  -ds-tx  PostgreSQL    CONTRIB  13-17  postgresql17-contrib  functions that manipulate whole tables, including crosstab

(13 Rows) (State: added|avail|n/a,Flags: b = HasBin, d = HasDDL, s = HasSolib, l = NeedLoad, t = Trusted, r = Relocatable, x = Unknown)

安装PG内核

You can also install PostgreSQL kernel packages with

pig ext install pg17          # install PostgreSQL 17 kernels (all but devel)
pig ext install pg16-simple   # install PostgreSQL 16 kernels with minimal packages
pig ext install pg15 -y       # install PostgreSQL 15 kernels with auto-confirm
pig ext install pg14=14.3     # install PostgreSQL 14 kernels with an specific minor version
pig ext install pg13=13.10    # install PostgreSQL 13 kernels

You can link the installed PostgreSQL to the system path with:

pig ext link pg17             # create /usr/pgsql soft links, and write it to /etc/profile.d/pgsql.sh
. /etc/profile.d/pgsql.sh     # reload the path and take effect immediately

You can also use other package alias, it will translate to corresponding package on your OS distro and the $v will be replaced with the active or given pg version number, such as 17, 16, etc…

pg17:        "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-llvmjit",
pg16-core:   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-test postgresql$v-devel postgresql$v-llvmjit",
pg15-simple: "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl",
pg14-client: "postgresql$v",
pg13-server: "postgresql$v-server postgresql$v-libs postgresql$v-contrib",
pg17-devel:  "postgresql$v-devel",

More Alias

pgsql:        "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-llvmjit",
pgsql-core:   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-test postgresql$v-devel postgresql$v-llvmjit",
pgsql-simple: "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl",
pgsql-client: "postgresql$v",
pgsql-server: "postgresql$v-server postgresql$v-libs postgresql$v-contrib",
pgsql-devel:  "postgresql$v-devel",
pgsql-basic:  "pg_repack_$v* wal2json_$v* pgvector_$v*",
postgresql:   "postgresql$v*",
pgsql-common: "patroni patroni-etcd pgbouncer pgbackrest pg_exporter pgbadger vip-manager",
patroni:      "patroni patroni-etcd",
pgbouncer:    "pgbouncer",
pgbackrest:   "pgbackrest",
pg_exporter:  "pg_exporter",
vip-manager:  "vip-manager",
pgbadger:     "pgbadger",
pg_activity:  "pg_activity",
pg_filedump:  "pg_filedump",
pgxnclient:   "pgxnclient",
pgformatter:  "pgformatter",
pgcopydb:     "pgcopydb",
pgloader:     "pgloader",
pg_timetable: "pg_timetable",
wiltondb:     "wiltondb",
polardb:      "PolarDB",
ivorysql:     "ivorysql3 ivorysql3-server ivorysql3-contrib ivorysql3-libs ivorysql3-plperl ivorysql3-plpython3 ivorysql3-pltcl ivorysql3-test",
ivorysql-all: "ivorysql3 ivorysql3-server ivorysql3-contrib ivorysql3-libs ivorysql3-plperl ivorysql3-plpython3 ivorysql3-pltcl ivorysql3-test ivorysql3-docs ivorysql3-devel ivorysql3-llvmjit",

Install for another PG

pig will use the default postgres installation in your active PATH, but you can install extension for a specific installation with -v (when using the PGDG convention), or passing any pg_config path for custom installation.

pig ext install pg_duckdb -v 16     # install the extension for pg16
pig ext install pg_duckdb -p /usr/lib/postgresql/17/bin/pg_config    # specify a pg17 pg_config

Install a specific Version

You can also install PostgreSQL kernel packages with:

pig ext install pgvector=0.7.0 # install pgvector 0.7.0
pig ext install pg16=16.5      # install PostgreSQL 16 with a specific minor version

Beware the APT repo may only have the latest minor version for its software (and require the full version string)

Search Extension

You can perform fuzzy search on extension name, description, and category.

u22:~$ pig ext ls olap

INFO[01:16:25] found 12 extensions matching 'olap':
Name            State  Version  Cate  Flags   License       Repo     PGVer  Package                     Description
----            -----  -------  ----  ------  -------       ------   -----  ------------                ---------------------
citus           n/a    12.1-1   OLAP  -dsl--  AGPL-3.0      PIGSTY   14-16  postgresql-17-citus         Distributed PostgreSQL as an extension
citus_columnar  n/a    11.3-1   OLAP  -ds---  AGPL-3.0      PIGSTY   14-16  postgresql-17-citus         Citus columnar storage engine
columnar        n/a    11.1-11  OLAP  -ds---  AGPL-3.0      PIGSTY   13-16  postgresql-17-hydra         Hydra Columnar extension
pg_analytics    avail  0.2.4    OLAP  -ds-t-  PostgreSQL    PIGSTY   14-17  postgresql-17-pg-analytics  Postgres for analytics, powered by DuckDB
pg_duckdb       avail  0.2.0    OLAP  -dsl--  MIT           PIGSTY   14-17  postgresql-17-pg-duckdb     DuckDB Embedded in Postgres
duckdb_fdw      avail  1.1.2    OLAP  -ds--r  MIT           PIGSTY   13-17  postgresql-17-duckdb-fdw    DuckDB Foreign Data Wrapper
pg_parquet      avail  0.2.0    OLAP  -dslt-  PostgreSQL    PIGSTY   14-17  postgresql-17-pg-parquet    copy data between Postgres and Parquet
pg_fkpart       avail  1.7      OLAP  -d----  GPL-2.0       PIGSTY   13-17  postgresql-17-pg-fkpart     Table partitioning by foreign key utility
pg_partman      avail  5.2.4    OLAP  -ds---  PostgreSQL    PGDG     13-17  postgresql-17-partman       Extension to manage partitioned tables by time or ID
plproxy         avail  2.11.0   OLAP  -ds---  BSD 0-Clause  PGDG     13-17  postgresql-17-plproxy       Database partitioning implemented as procedural language
pg_strom        n/a    5.1      OLAP  -ds--x  PostgreSQL             n/a                                PG-Strom - big-data processing acceleration using GPU and NVME
tablefunc       added  1.0      OLAP  -ds-tx  PostgreSQL    CONTRIB  13-17  postgresql-17               functions that manipulate whole tables, including crosstab

(12 Rows) (State: added|avail|n/a,Flags: b = HasBin, d = HasDDL, s = HasSolib, l = NeedLoad, t = Trusted, r = Relocatable, x = Unknown)

You can use the -v 16 or -p /path/to/pg_config to find extension availability for other PostgreSQL installation.

Print Extension Summary

You can get extension metadata with pig ext info subcommand:

$ pig ext info pg_duckdb
╭────────────────────────────────────────────────────────────────────────────╮
│ pg_duckdb                                                                  │
├────────────────────────────────────────────────────────────────────────────┤
│ DuckDB Embedded in Postgres                                                │
├────────────────────────────────────────────────────────────────────────────┤
│ Extension : pg_duckdb                                                      │
│ Alias     : pg_duckdb                                                      │
│ Category  : OLAP                                                           │
│ Version   : 0.2.0                                                          │
│ License   : MIT                                                            │
│ Website   : https://github.com/duckdb/pg_duckdb                            │
│ Details   : https://ext.pigsty.io/#/pg_duckdb                              │
├────────────────────────────────────────────────────────────────────────────┤
│ Extension Properties                                                       │
├────────────────────────────────────────────────────────────────────────────┤
│ PostgreSQL Ver │  Available on: 17, 16, 15, 14                             │
│ CREATE  :  Yes │  CREATE EXTENSION pg_duckdb;                              │
│ DYLOAD  :  Yes │  SET shared_preload_libraries = 'pg_duckdb'               │
│ TRUST   :  No  │  require database superuser to install                    │
│ Reloc   :  No  │  Schemas: []                                              │
│ Depend  :  No  │                                                           │
├────────────────────────────────────────────────────────────────────────────┤
│ RPM Package                                                                │
├────────────────────────────────────────────────────────────────────────────┤
│ Repository     │  PIGSTY                                                   │
│ Package        │  pg_duckdb_$v*                                            │
│ Version        │  0.1.0                                                    │
│ Availability   │  17, 16, 15, 14                                           │
├────────────────────────────────────────────────────────────────────────────┤
│ DEB Package                                                                │
├────────────────────────────────────────────────────────────────────────────┤
│ Repository     │  PIGSTY                                                   │
│ Package        │  postgresql-$v-pg-duckdb                                  │
│ Version        │  0.2.0                                                    │
│ Availability   │  17, 16, 15, 14                                           │
├────────────────────────────────────────────────────────────────────────────┤
│ Known Issues                                                               │
├────────────────────────────────────────────────────────────────────────────┤
│ el8                                                                        │
├────────────────────────────────────────────────────────────────────────────┤
│ Additional Comments                                                        │
├────────────────────────────────────────────────────────────────────────────┤
│ broken on el8 (libstdc++ too low), conflict with duckdb_fdw                │
╰────────────────────────────────────────────────────────────────────────────╯

6 - Pigsty 管理

如何使用 pig sty 子命令管理 Pigsty 发行版本身？

Overview

The pig can also be used as a cli tool for Pigsty - the battery-include free PostgreSQL RDS. Which brings HA, PITR, Monitoring, IaC, and all the extensions to your PostgreSQL cluster.

pig sty init     # install embed pigsty to ~/pigsty 
pig sty boot     # install ansible and other pre-deps 
pig sty conf     # auto-generate pigsty.yml config file
pig sty install  # run the install.yml playbook

You can use the pig sty subcommand to bootstrap pigsty on current node.

7 - 扩展构建基础设施

如何使用 pig build 命令构建 PostgreSQL 扩展并准备构建环境?

Overview

You can build PostgreSQL extension with pig build subcommand, which offers:

pig build repo                   # init build repo (=repo set -ru)
pig build tool  [mini|full|...]  # init build toolset
pig build proxy [user@host:port] # init build proxy (optional)
pig build rust  [-v <pgrx_ver>]  # init rustc & pgrx (0.12.9)
pig build spec                   # init build spec repo
pig build get   [all|std|..]     # get ext code tarball with prefixes
pig build ext   [extname...]     # build extension

Short Version

Prepare building environment

pig build repo
pig build tool
pig build spec
pig build rust

Download extension source tarball

## download big tarball
pig build get std          # download std small tarball
pig build get all          # download all source tarball
pig build get pg_mooncake
pig build get pg_duckdb
pig build get omnigres
pig build get plv8

Build extensions:

pig build ext citus
pig build ext pgjwt

TBD

8 - 可用系统

Linux 操作系统发行版兼容性矩阵，哪些发行版提供了开箱即用的扩展支持？

pig itself runs on any x86_64/aarch64 compatible Linux distribution.

But the pig extension repo only supports the following Linux distributions:

RHEL 8 / 9
Debian 12
Ubuntu 22.04 / 24.04

Code	Distribution	`x86_64`	`aarch64`
el9	RHEL 9 / Rocky9 / Alma9 / …	PG 17 - 13	PG 17 - 13
el8	RHEL 8 / Rocky8 / Alma8 / …	PG 17 - 13	PG 17 - 13
u24	Ubuntu 24.04 (`noble`)	PG 17 - 13	PG 17 - 13
u22	Ubuntu 22.04 (`jammy`)	PG 17 - 13	PG 17 - 13
d12	Debian 12 (`bookworm`)	PG 17 - 13	PG 17 - 13

Here are some bad cases and limitation for above Linux distros:

pg_duckdb el8:*:*
pljava: el8:*:*
pllua: el8:arm:13,14,15
h3: el8.amd.pg17
jdbc_fdw: el:arm:*
pg_partman: u24:*:13
wiltondb: d12:*:*
citus and hydra are mutually exclusive
pg_duckdb will invalidate duckdb_fdw
documentdb_core is not available on arm arch