快速上手
Get Started with pig, the PostgreSQL extension manager.
Module:
curl -fsSL https://repo.pigsty.io/pig | bash
安装完成后即可使用。假设你想安装 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 repo add pigsty pgdg
会将 PGDG
仓库和 PIGSTY
仓库添加到你的系统中。
而以下命令会备份并清除你现有的仓库,然后添加所有需要的仓库到你的系统:
pig repo add all --ru # 这将用 node、pgdg、pigsty 仓库覆盖所有现有仓库
还有一个更激进的仓库添加版本:repo set
,它默认会覆盖你现有的仓库(-r
)。
你可以在 /etc/apt/backup
或 /etc/yum.repos.d/backup
找到你的旧仓库备份。
安装 PostgreSQL
你可以用以下命令安装 PostgreSQL 内核包:
pig ext install pg17 # 安装 PostgreSQL 17 内核(除开发包外的所有包)
pig ext install pg16-simple # 安装 PostgreSQL 16 内核的最小包集
pig ext install pg15 -y # 安装 PostgreSQL 15 内核并自动确认
pig ext install pg14=14.3 # 安装特定小版本的 PostgreSQL 14 内核
pig ext install pg13=13.10 # 安装 PostgreSQL 13 内核
你可以将已安装的 PostgreSQL 链接到系统路径:
pig ext link pg17 # 创建 /usr/pgsql 软链接,并写入 /etc/profile.d/pgsql.sh
. /etc/profile.d/pgsql.sh # 重新加载路径并立即生效
你也可以使用其他包别名,它们会转换为你的操作系统发行版上对应的包名
其中的 $v
会被替换为当前活动或指定的 pg 版本号,如 17
、16
等:
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",
更多别名
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",
为其他 PG 版本安装
pig
会使用你当前活动 PATH
中的默认 postgres 安装,
但你可以通过 -v
(当使用 PGDG 约定时)为特定安装版本安装扩展,
或者为自定义安装指定任意 pg_config
路径。
pig ext install pg_duckdb -v 16 # 为 pg16 安装扩展
pig ext install pg_duckdb -p /usr/lib/postgresql/17/bin/pg_config # 指定 pg17 的 pg_config
安装特定版本
你也可以安装特定版本的 PostgreSQL 内核包:
pig ext install pgvector=0.7.0 # 安装 pgvector 0.7.0
pig ext install pg16=16.5 # 安装特定小版本的 PostgreSQL 16
注意 APT 仓库可能只提供最新的小版本软件(并且需要完整的版本字符串)
搜索扩展
你可以对扩展名、描述和类别进行模糊搜索:
$ 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* 作为扩展的分布式 PostgreSQL
citus_columnar avail 11.3-1 OLAP -ds--- AGPL-3.0 PIGSTY 14-17 citus_17* Citus 列式存储引擎
columnar n/a 11.1-11 OLAP -ds--- AGPL-3.0 PIGSTY 13-16 hydra_17* Hydra 列式存储扩展
pg_analytics avail 0.3.4 OLAP -ds-t- PostgreSQL PIGSTY 14-17 pg_analytics_17 基于 DuckDB 的 Postgres 分析功能
pg_duckdb avail 0.2.0 OLAP -dsl-- MIT PIGSTY 14-17 pg_duckdb_17* 嵌入 Postgres 的 DuckDB
pg_mooncake avail 0.1.2 OLAP ------ MIT PIGSTY 14-17 pg_mooncake_17* Postgres 中的列式存储表
duckdb_fdw avail 1.0.0 OLAP -ds--r MIT PIGSTY 13-17 duckdb_fdw_17* DuckDB 外部数据包装器
pg_parquet avail 0.2.0 OLAP -dslt- PostgreSQL PIGSTY 14-17 pg_parquet_17 在 Postgres 和 Parquet 间复制数据
pg_fkpart avail 1.7 OLAP -d---- GPL-2.0 PIGSTY 13-17 pg_fkpart_17 基于外键的表分区工具
pg_partman avail 5.2.4 OLAP -ds--- PostgreSQL PGDG 13-17 pg_partman_17* 按时间或 ID 管理分区表的扩展
plproxy avail 2.11.0 OLAP -ds--- BSD 0-Clause PIGSTY 13-17 plproxy_17* 以过程语言实现的数据库分区
pg_strom avail 5.2.2 OLAP -ds--x PostgreSQL PGDG 13-17 pg_strom_17* PG-Strom - 使用 GPU 和 NVME 加速大数据处理
tablefunc added 1.0 OLAP -ds-tx PostgreSQL CONTRIB 13-17 postgresql17-contrib 操作整个表的函数,包括交叉表
(13 Rows) (状态: added=已添加|avail=可用|n/a=不可用, 标志: b=有二进制文件, d=有DDL, s=有共享库, l=需要加载, t=可信任, r=可重定位, x=未知)
你可以使用 -v 16
或 -p /path/to/pg_config
来查找其他 PostgreSQL 安装的扩展可用性。
打印扩展摘要
你可以使用 pig ext info
子命令获取扩展元数据:
$ pig ext info pg_duckdb
╭────────────────────────────────────────────────────────────────────────────╮
│ pg_duckdb │
├────────────────────────────────────────────────────────────────────────────┤
│ DuckDB Embedded in Postgres │
├────────────────────────────────────────────────────────────────────────────┤
│ Extension : pg_duckdb │
│ Alias : pg_duckdb │
│ Category : OLAP │
│ Version : 0.3.1 │
│ License : MIT │
│ Website : https://github.com/duckdb/pg_duckdb │
│ Details : https://pigsty.io/ext/olap/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.3.1 │
│ Availability │ 17, 16, 15, 14 │
├────────────────────────────────────────────────────────────────────────────┤
│ DEB Package │
├────────────────────────────────────────────────────────────────────────────┤
│ Repository │ PIGSTY │
│ Package │ postgresql-$v-pg-duckdb │
│ Version │ 0.3.1 │
│ Availability │ 17, 16, 15, 14 │
├────────────────────────────────────────────────────────────────────────────┤
│ Known Issues │
├────────────────────────────────────────────────────────────────────────────┤
│ el8 │
├────────────────────────────────────────────────────────────────────────────┤
│ Additional Comments │
├────────────────────────────────────────────────────────────────────────────┤
│ broken on el8 (libstdc++ too low), conflict with duckdb_fdw │
╰────────────────────────────────────────────────────────────────────────────╯
列出仓库
你可以使用 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