扩展管理
如何使用 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://pigsty.cc/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.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 │
╰────────────────────────────────────────────────────────────────────────────╯