Get Started
Module:
Install the pig
package via script (or other approaches):
curl -fsSL https://repo.pigsty.io/pig | bash
Then it’s ready to use, assume you want to install the pg_duckdb
extension:
$ pig repo add pigsty pgdg -u # add pgdg & pigsty repo, then update repo cache
$ pig ext install pg17 # install PostgreSQL 17 kernels with native PGDG packages
$ pig ext install pg_duckdb # install the pg_duckdb extension (for current pg17)
Check details about repo and ext admin command.
Examples
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
.
Install PostgreSQL
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.
$ 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)
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.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 │
╰────────────────────────────────────────────────────────────────────────────╯
List Repo
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
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.