This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

The PIG CLI

Postgres Install Genius, the missing extension package manager for PostgreSQL ecosystem

Repo: pgsty/pig Version: v0.4.0 License: Apache-2.0 Extensions: 407

pig is an open-source PostgreSQL (& Extension) Package Manager for mainstream (EL/Debian/Ubuntu) Linux.

Install PostgreSQL 13-17 along with 407 extensions on (amd64 / arm64) with native OS package manager

It is shipped with a supplementary YUM and APT repo, which is fully compatible with official PGDG repo.

Blog: The ideal way to deliver PostgreSQL extensions

note: pig is designed to run directly on compatible Linux OS Distro. If you are using containers, you should use the corresponding Linux distribution image with systemd.

1 - Get Started

Get Started with pig, the PostgreSQL extension manager.

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.

asciicast

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.

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


2 - Why Pig?

Why would we need yet another package manager? especially for Postgres extensions?

Ever wished installing or upgrading PostgreSQL extensions didn’t feel like digging through outdated readmes, cryptic configure scripts, or random GitHub forks & patches? The painful truth is that Postgres’s richness of extension often comes at the cost of complicated setups—especially if you’re juggling multiple distros or CPU architectures.

pig-meme

Enter Pig, a Go-based package manager built to tame Postgres and its ecosystem of 400+ extensions in one fell swoop. TimescaleDB, Citus, PGVector, 20+ Rust extensions, plus every must-have piece to self-host Supabase — Pig’s unified CLI makes them all effortlessly accessible. It cuts out messy source builds and half-baked repos, offering version-aligned RPM/DEB packages that work seamlessly across Debian, Ubuntu, and RedHat flavors, as well as x86 & ARM arch. No guesswork, no drama.

pig

Instead of reinventing the wheel, Pig piggyback your system’s native package manager (APT, YUM, DNF) and follow official PGDG packaging conventions to ensure a glitch-free fit. That means you don’t have to choose between “the right way” and “the quick way”; Pig respects your existing repos, aligns with standard OS best practices, and fits neatly alongside other packages you already use.

Ready to give your Postgres superpowers without the usual hassle? Check out GitHub for documentation, installation steps, and a peek at its massive extension list. Then, watch your local Postgres instance transform into a powerhouse of specialized modules—no black magic is required. If the future of Postgres is unstoppable extensibility, Pig is the genie that helps you unlock it. Honestly, nobody ever complained that they had too many extensions.

《Pig, The Postgres Extension Wizard》

《ANNOUNCE pig: The Postgres Extension Wizard》



3 - Install

Get Started with pig, the PostgreSQL extension manager.

Via Script

The simplest way to install pig is to run the following script:

curl -fsSL https://repo.pigsty.io/pig | bash

It will download the latest pig RPM/DEB directly from the pigsty repo, and install via rpm or dpkg.

Via YUM

The pig RPM package is available in the pigsty-infra YUM repo for any EL 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

Via APT

The pig DEB package is available in the pigsty-infra APT repo for any Debian/Ubuntu 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

Self Update

Once installed, you can self-update pig to the latest version with:

pig update   # upgrade pig itself to the latest version


4 - Pigsty Command Line

The overview of pig cli tool

Overview

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.

Examples

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

Check pig ext for details.

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

Check pig repo for details.

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

Check pig sty for details.

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 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.



5 - Repository Administration

How to manage repositories with pig repo subcommand?

The pig repo command is a comprehensive tool for managing package repositories. It provides functionality to manage, add, remove, create and interact with os software repos. It works on both RPM-based (EL) and Debian-based systems.

Overview

pig repo - Manage Linux APT/YUM Repo

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

Usage:
  pig repo [command]

Aliases:
  repo, r

Examples:

  Get Started: https://pigsty.io/ext/pig/
  pig repo add -ru                 # add all repo and update cache (brute but effective)
  pig repo add pigsty -u           # gentle version, only add pigsty repo and update cache
  pig repo add node pgdg pigsty    # essential repo to install postgres packages
  pig repo add all                 # all = node + pgdg + pigsty
  pig repo add all extra           # extra module has non-free and some 3rd repo for certain extensions
  pig repo update                  # update repo cache
  pig repo create                  # update local repo /www/pigsty meta
  pig repo boot                    # extract /tmp/pkg.tgz to /www/pigsty
  pig repo cache                   # cache /www/pigsty into /tmp/pkg.tgz


Available Commands:
  add         add new repository
  boot        bootstrap repo from offline package
  cache       create offline package from local repo
  create      create local YUM/APT repository
  info        get repo detailed information
  list        print available repo list
  rm          remove repository
  set         wipe and overwrite repository
  status      show current repo status
  update      update repo cache

Flags:
  -h, --help   help for repo

Global Flags:
      --debug              enable debug mode
  -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

Examples

List available repo and add PGDG & Pigsty repo, then update local repo cache.

# list available modules
pig repo list

# add PGDG & Pigsty repo
pig repo add pgdg pigsty

# yum makecache or apt update
pig repo update

You’ll have to update repo metadata cache after adding new repo, you can either use the dedicate pig repo update command or use the -u|--update flag in pig repo add command.

pig repo add pigsty -u    # add pigsty repo and update repo cache

If you wish to WIPE all the existing repo before adding new repo, you can use the extra -r|--remove flag, or use the dedicate pig repo set subcommand instead of pig repo add.

pig repo add all --remove # REMOVE all existing repo and add node, pgdg, pigsty repo and update repo cache
pig repo add -r           # same as above, and missing repo/module will use the default `all` alias to add node, pgdg, pigsty repo
pig repo set              # same as above, set is a shortcut for `add --remove`, REMOVED repo files are backupped to `/etc/yum.repos.d/backup` or `/etc/apt/sources.list.d/backup`

The most brutal but reliable way to setup repo for PostgreSQL installation is to wipe all existing repo and add all the required repo with:

pig repo set -u           # wipe all existing repo and add all the required repo and update repo cache

Modules

In pigsty, all repos are organized into modules, a module is a collection of repos.

Module names may maps to different real repos on different OS distro, major version and architecture, and geo region.

Pigsty will handle all the details, you can list all repos & modules with pig repo list.

repo_modules:   # Available Modules: 19
  - all       : pigsty-infra, pigsty-pgsql, pgdg, baseos, appstream, extras, powertools, crb, epel, base, updates, security, backports
  - pigsty    : pigsty-infra, pigsty-pgsql
  - pgdg      : pgdg
  - 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

Usually these 3 modules are required to install PostgreSQL & all the extensions:

  • pgdg: Official PostgreSQL Repo, with PG kernel packages, utils, and 100+ extensions.
  • pigsty: Pigsty Extension Repo, with 200+ extra extensions and utils.
  • node: Operating System Default Repo, which brings all the libraries and dependencies for PostgreSQL.

There’s a convient pesudo module alias all which includes all the 3 essential modules above. You can add all of them with pig repo add all, or a even simpler abbreviation: pig repo add.

repo list

Lists available repository modules and repositories that can be added to the current system.

print available repo list

Usage:
  pig repo list [flags]

Aliases:
  list, l, ls

Examples:

  pig repo list                # list available repos on current system
  pig repo list all            # list all unfiltered repo raw data


Flags:
  -h, --help   help for list

Available repos are defined in the cli/repo/assets/repo.yml, if you wish to modify the repo list, you can do so by adding your own repo.yml file to ~/.pig/repo.yml.

repo add

Adds repository configuration files to the system.

add new repository

Usage:
  pig repo add [flags]

Aliases:
  add, a, append

Examples:

  pig repo add                      # = pig repo add all
  pig repo add all                  # add node,pgsql,infra repo (recommended)
  pig repo add all -u               # add above repo and update repo cache (or: --update)
  pig repo add all -r               # add all repo, remove old repos       (or: --remove)
  pig repo add pigsty --update      # add pigsty extension repo and update repo cache
  pig repo add pgdg --update        # add pgdg official repo and update repo cache
  pig repo add pgsql node --remove  # add os + postgres repo, remove old repos
  pig repo add infra                # add observability, grafana & prometheus stack, pg bin utils

  (Beware that system repo management require sudo / root privilege)

  available repo modules:
  - all      :  pgsql + node + infra (recommended)
    - pigsty :  PostgreSQL Extension Repo (default)
    - pgdg   :  PGDG the Official PostgreSQL Repo (official)
    - node   :  operating system official repo (el/debian/ubuntu)
  - pgsql    :  pigsty + pgdg (all available pg extensions)
  # check available repo & modules with pig repo list

Flags:
  -h, --help            help for add
      --region string   region code (default|china)
  -r, --remove          remove existing repo before adding new repo
  -u, --update          run apt update or dnf makecache

This command:

  1. Verifies if specified modules exist and translate to real repos according to
  • region, distro, os major version, arch
  1. If -r|--remove flag is provided, it will move the existing repo to backup folder:
  • /etc/yum.repos.d/backup for EL systems
  • /etc/apt/sources.list.d/backup for Debian systems
  1. Creates repo files in the system’s repository directory
  • /etc/yum.repos.d/<module>.repo for EL systems
  • /etc/apt/sources.list.d/<module>.list for Debian systems
  1. If -u|--update flag is provided, it will run apt update or dnf makecache to update the repo cache.

If not running as root, sudo privilege is required.

repo set

Same as repo add <...> --remove, remove existing repo before adding new repo.

wipe and overwrite repository

Usage:
  pig repo set [flags]

Aliases:
  set, overwrite

Examples:

  pig repo set all                  # set repo to node,pgsql,infra  (recommended)
  pig repo set all -u               # set repo to above repo and update repo cache (or --update)
  pig repo set pigsty --update      # set repo to pigsty extension repo and update repo cache
  pig repo set pgdg   --update      # set repo to pgdg official repo and update repo cache
  pig repo set infra                # set repo to observability, grafana & prometheus stack, pg bin utils

  (Beware that system repo management require sudo/root privilege)


Flags:
  -h, --help            help for set
      --region string   region code
  -u, --update          run apt update or dnf makecache

If not running as root, sudo privilege is required.

repo update

Update repo cache, same as apt update or yum makecache.

update repo cache

Usage:
  pig repo update [flags]

Aliases:
  update, u

Examples:

  pig repo update                  # yum makecache or apt update


Flags:
  -h, --help   help for update

If not running as root, sudo privilege is required.

repo rm

Removes repository files from the system.

remove repository

Usage:
  pig repo rm [flags]

Aliases:
  rm, remove

Examples:

  pig repo rm                      # remove (backup) all existing repo to backup dir
  pig repo rm all --update         # remove module 'all' and update repo cache
  pig repo rm node pigsty -u       # remove module 'node' & 'pigsty' and update repo cache


Flags:
  -h, --help     help for rm
  -u, --update   run apt update or dnf makecache

It will remove the repo files from the system, and if -u|--update flag is provided, it will run apt update or dnf makecache to update the repo cache after removing the repo files.

Before removing files, the command creates a backup of existing repository configurations.

If not running as root, sudo privilege is usually required.

repo status

Print the system repo directory and list available repos with system package manager.

show current repo status

Usage:
  pig repo status [flags]

Aliases:
  status, s, st

Flags:
  -h, --help   help for status

repo info

Provides detailed information about specific repositories or modules.

get repo detailed information

Usage:
  pig repo info [flags]

Aliases:
  info, i

Flags:
  -h, --help   help for info

Example:

#-------------------------------------------------
Name       : pgdg
Summary    : PGDG
Available  : Yes (debian d12 amd64)
Module     : pgsql
OS Arch    : [x86_64, aarch64]
OS Distro  : deb [11,12,20,22,24]
Meta       : trusted=yes
Base URL   : http://apt.postgresql.org/pub/repos/apt/ ${distro_codename}-pgdg main
     china : https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ ${distro_codename}-pgdg main

# default repo content
# pgdg PGDG
deb [trusted=yes] http://apt.postgresql.org/pub/repos/apt/ bookworm-pgdg main

# china mirror repo content
# pgdg PGDG
deb [trusted=yes] https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ bookworm-pgdg main

It will print the repo information for the given repo name or module name. And regional mirrors are also supported.

repo create

Creates a local YUM/APT repository in specified directories

create local YUM/APT repository

Usage:
  pig repo create [path...]

Aliases:
  create, cr

Examples:

  pig repo create                    # create repo on /www/pigsty by default
  pig repo create /www/mssql /www/b  # create repo on multiple locations

  (Beware that system repo management require sudo/root privilege)

Default directory: /www/pigsty

This command:

  1. Creates the directory structure if it doesn’t exist
  2. Create local repo with repo utils (make sure they are installed on the system)
  • createrepo_c for EL systems
  • dpkg-dev for Debian systems

If not running as root, read/write permission on that directory is required.

repo cache

Creates a compressed tarball of repository contents for offline use.

pig repo cache [directory_path] [package_path] [repo1,repo2,...]

Parameters:

  • directory_path: Source directory containing repositories (default: /www)
  • package_path: Output tarball path (default: pigsty-pkg-<os>-<arch>.tgz in current directory)
  • repos: Comma-separated list of repository subdirectories to include (default: all)

Example:

pig repo cache /www /tmp/pkg.tgz pigsty
pig repo cache /www /tmp/pkg.tgz pigsty mssql ivory

You can create a tarball on created local repo, and use it to boot a new system from offline package.

repo cache

create offline package from local repo

create offline package from local repo

Usage:
  pig repo cache [flags]

Aliases:
  cache, c

Examples:

  pig repo cache                    # create /tmp/pkg.tgz offline package from /www/pigsty
  pig repo cache -f                 # force overwrite existing package
  pig repo cache -d /srv            # overwrite default content dir /www to /srv
  pig repo cache pigsty mssql       # create the tarball with both pigsty & mssql repo
  pig repo c -f                     # the simplest use case to make offline package

  (Beware that system repo management require sudo/root privilege)


Flags:
  -d, --dir string    source repo path (default "/www/")
  -h, --help          help for cache
  -p, --path string   offline package path (default "/tmp/pkg.tgz")

repo boot

Bootstraps a local repository from an offline package.

bootstrap repo from offline package

Usage:
  pig repo boot [flags]

Aliases:
  boot, b, bt

Examples:

  pig repo boot                    # boot repo from /tmp/pkg.tgz to /www
  pig repo boot -p /tmp/pkg.tgz    # boot repo from given package path
  pig repo boot -d /srv            # boot repo to another directory /srv


Flags:
  -d, --dir string    target repo path (default "/www/")
  -h, --help          help for boot
  -p, --path string   offline package path (default "/tmp/pkg.tgz")

Parameters:

  • offline_package: Path to the tarball created by pig repo cache
  • target_directory: Directory to extract repositories to (default: /www)

Example:

pig repo boot /tmp/pkg.tgz /www

This command:

  1. Extracts the tarball to the target directory
  2. Sets up local repository configuration
  3. Updates repository metadata


6 - Extension Administration

How to manage repositories with pig ext subcommand?

The pig ext command is a comprehensive tool for managing PostgreSQL extensions. It allows users to search, install, remove, update, and manage PostgreSQL extensions and even kernel packages.

Overview

pig ext - Manage PostgreSQL Extensions

  pig repo add -ru             # add all repo and update cache (brute but effective)
  pig ext add pg17             # install optional postgresql 17 package
  pig ext list duck            # search extension in catalog
  pig ext scan -v 17           # scan installed extension for pg 17
  pig ext add pg_duckdb        # install certain postgresql extension

Usage:
  pig ext [command]

Aliases:
  ext, e, ex, pgext, extension

Examples:

  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              # upgrade to the latest extension catalog


Available Commands:
  add         install postgres extension
  import      import extension packages to local repo
  info        get extension information
  link        link postgres to active PATH
  list        list & search available extensions
  rm          remove postgres extension
  scan        scan installed extensions for active pg
  status      show installed extension on active pg
  update      update installed extensions for current pg version
  upgrade     upgrade extension catalog to the latest version

Flags:
  -h, --help          help for ext
  -p, --path string   specify a postgres by pg_config path
  -v, --version int   specify a postgres by major version

Global Flags:
      --debug              enable debug mode
  -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

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

Examples

To install postgres extensions, you’ll have to setup the repo first:

pig repo add pgdg pigsty -u    # gental way to add pgdg and pigsty repo
pig repo set -u                # brute way to remove and add all required repos

Then you can search, and install PostgreSQL extensions:

pig ext install pg_duckdb
pig ext install pg_partman
pig ext install pg_cron
pig ext install pg_repack
pig ext install pg_stat_statements
pig ext install pg_stat_kcache

Check extension list for available extensions and their names.

  1. When no PostgreSQL version is specified, the tool will try to detect the active PostgreSQL installation from pg_config in your PATH
  2. PostgreSQL can be specified either by major version number (-v) or by pg_config path (-p). if -v is given, pig will use the well-known default path of PGDG kernel packages for the given version. - On EL distros, it’s /usr/pgsql-$v/bin/pg_config for PG$v, - On DEB distros, it’s /usr/lib/postgresql/$v/bin/pg_config for PG$v, etc. if -p is given, pig will use the pg_config path to find the PostgreSQL installation.
  3. The extension manager supports different package formats based on the underlying operating system:
    • RPM packages for RHEL/CentOS/Rocky Linux/AlmaLinux
    • DEB packages for Debian/Ubuntu
  4. Some extensions may have dependencies that will be automatically resolved during installation.
  5. Use the -y flag with caution as it will automatically confirm all prompts.

Pigsty assumes you already have installed the official PGDG kernel packages, if not, you can install them with:

pig ext install pg17          # install PostgreSQL 17 kernels (all but devel)

ext list

List and search available extensions in the extension catalog.

list & search available extensions

Usage:
  pig ext list [query] [flags]

Aliases:
  list, l, ls, find

Examples:

  pig ext list                # list all extensions
  pig ext list postgis        # search extensions by name/description
  pig ext ls olap             # list extension of olap category
  pig ext ls gis -v 16        # list gis category for pg 16

The default extension catalog is defined in cli/ext/assets/pigsty.csv

You can update to the latest extension catalog with: pig ext upgrade it will download the latest extension catalog data to ~/.pig/pigsty.csv.

ext info

Display detailed information about specific extensions.

pig ext info [ext...]

Examples:

pig ext info postgis        # Show detailed information about PostGIS
pig ext info timescaledb    # Show information about TimescaleDB

$ pig ext info postgis        # Show detailed information about PostGIS

╭────────────────────────────────────────────────────────────────────────────╮
│ postgis                                                                    │
├────────────────────────────────────────────────────────────────────────────┤
│ PostGIS geometry and geography spatial types and functions                 │
├────────────────────────────────────────────────────────────────────────────┤
│ Extension : postgis                                                        │
│ Alias     : postgis                                                        │
│ Category  : GIS                                                            │
│ Version   : 3.5.2                                                          │
│ License   : GPL-2.0                                                        │
│ Website   : https://git.osgeo.org/gitea/postgis/postgis                    │
│ Details   : https://pigsty.io/gis/postgis                                  │
├────────────────────────────────────────────────────────────────────────────┤
│ Extension Properties                                                       │
├────────────────────────────────────────────────────────────────────────────┤
│ PostgreSQL Ver │  Available on: 17, 16, 15, 14, 13                         │
│ CREATE  :  Yes │  CREATE EXTENSION postgis;                                │
│ DYLOAD  :  No  │  no need to load shared libraries                         │
│ TRUST   :  No  │  require database superuser to install                    │
│ Reloc   :  No  │  Schemas: []                                              │
│ Depend  :  No  │                                                           │
├────────────────────────────────────────────────────────────────────────────┤
│ Required By                                                                │
├────────────────────────────────────────────────────────────────────────────┤
│ - postgis_topology                                                         │
│ - postgis_raster                                                           │
│ - postgis_sfcgal                                                           │
│ - postgis_tiger_geocoder                                                   │
│ - pgrouting                                                                │
│ - pointcloud_postgis                                                       │
│ - h3_postgis                                                               │
│ - mobilitydb                                                               │
│ - documentdb                                                               │
├────────────────────────────────────────────────────────────────────────────┤
│ RPM Package                                                                │
├────────────────────────────────────────────────────────────────────────────┤
│ Repository     │  PGDG                                                     │
│ Package        │  postgis35_$v*                                            │
│ Version        │  3.5.2                                                    │
│ Availability   │  17, 16, 15, 14, 13                                       │
├────────────────────────────────────────────────────────────────────────────┤
│ DEB Package                                                                │
├────────────────────────────────────────────────────────────────────────────┤
│ Repository     │  PGDG                                                     │
│ Package        │  postgresql-$v-postgis-3 postgresql-$v-postgis-3-scripts  │
│ Version        │  3.5.2                                                    │
│ Availability   │  17, 16, 15, 14, 13                                       │
╰────────────────────────────────────────────────────────────────────────────╯

status

Display the status of installed extensions for the active PostgreSQL instance.

pig ext status [-c]

Options:

  • -c, --contrib: Include contrib extensions in the results

Examples:

pig ext status              # Show installed extensions
pig ext status -c           # Show installed extensions including contrib ones
pig ext status -v 16        # Show installed extensions for PostgreSQL 16

ext scan

Scan the active PostgreSQL instance for installed extensions.

pig ext scan [-v version]

It will scan the postgres extension folder to find all the actually installed extensions.

$ pig ext status

Installed:
* PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2)  85  Extensions

Active:
PG Version      :  PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2)
Config Path     :  /usr/lib/postgresql/17/bin/pg_config
Binary Path     :  /usr/lib/postgresql/17/bin
Library Path    :  /usr/lib/postgresql/17/lib
Extension Path  :  /usr/share/postgresql/17/extension
Extension Stat  :  18 Installed (PIGSTY 8, PGDG 10) + 67 CONTRIB = 85 Total

Name                          Version  Cate   Flags   License       Repo    Package                                                  Description
----                          -------  ----   ------  -------       ------  ------------                                             ---------------------
timescaledb                   2.18.2   TIME   -dsl--  Timescale     PIGSTY  postgresql-17-timescaledb-tsl                            Enables scalable inserts and complex queries for time-series dat
postgis                       3.5.2    GIS    -ds---  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  PostGIS geometry and geography spatial types and functions
postgis_topology              3.5.2    GIS    -ds---  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  PostGIS topology spatial types and functions
postgis_raster                3.5.2    GIS    -ds---  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  PostGIS raster types and functions
postgis_sfcgal                3.5.2    GIS    -ds--r  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  PostGIS SFCGAL functions
postgis_tiger_geocoder        3.5.2    GIS    -ds-t-  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  PostGIS tiger geocoder and reverse geocoder
address_standardizer          3.5.2    GIS    -ds--r  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  Used to parse an address into constituent elements. Generally us
address_standardizer_data_us  3.5.2    GIS    -ds--r  GPL-2.0       PGDG    postgresql-17-postgis-3 postgresql-$v-postgis-3-scripts  Address Standardizer US dataset example
vector                        0.8.0    RAG    -ds--r  PostgreSQL    PGDG    postgresql-17-pgvector                                   vector data type and ivfflat and hnsw access methods
pg_search                     0.15.2   FTS    -ds-t-  AGPL-3.0      PIGSTY  postgresql-17-pg-search                                  pg_search: Full text search for PostgreSQL using BM25
pgroonga                      4.0.0    FTS    -ds-tr  PostgreSQL    PIGSTY  postgresql-17-pgroonga                                   Use Groonga as index, fast full text search platform for all lan
pgroonga_database             4.0.0    FTS    -ds-tr  PostgreSQL    PIGSTY  postgresql-17-pgroonga                                   PGroonga database management module
citus                         13.0.1   OLAP   -dsl--  AGPL-3.0      PIGSTY  postgresql-17-citus                                      Distributed PostgreSQL as an extension
citus_columnar                11.3-1   OLAP   -ds---  AGPL-3.0      PIGSTY  postgresql-17-citus                                      Citus columnar storage engine
pg_mooncake                   0.1.2    OLAP   ------  MIT           PIGSTY  postgresql-17-pg-mooncake                                Columnstore Table in Postgres
plv8                          3.2.3    LANG   -ds---  PostgreSQL    PIGSTY  postgresql-17-plv8                                       PL/JavaScript (v8) trusted procedural language
pg_repack                     1.5.2    ADMIN  bds---  BSD 3-Clause  PGDG    postgresql-17-repack                                     Reorganize tables in PostgreSQL databases with minimal locks
wal2json                      2.5.3    ETL    --s--x  BSD 3-Clause  PGDG    postgresql-17-wal2json                                   Changing data capture in JSON format

(18 Rows) (Flags: b = HasBin, d = HasDDL, s = HasSolib, l = NeedLoad, t = Trusted, r = Relocatable, x = Unknown)

ext add

Install one or more PostgreSQL extensions.

install postgres extension

Usage:
  pig ext add [flags]

Aliases:
  add, a, install, ins

Examples:

Description:
  pig ext install pg_duckdb                  # install one extension
  pig ext install postgis timescaledb        # install multiple extensions
  pig ext add     pgvector pgvectorscale     # other alias: add, ins, i, a
  pig ext ins     pg_search -y               # auto confirm installation
  pig ext install pgsql                      # install the latest version of postgresql kernel
  pig ext a pg17                             # install postgresql 17 kernel packages
  pig ext ins pg16                           # install postgresql 16 kernel packages
  pig ext install pg15-core                  # install postgresql 15 core packages
  pig ext install pg14-main -y               # install pg 14 + essential extensions (vector, repack, wal2json)
  pig ext install pg13-devel --yes           # install pg 13 devel packages (auto-confirm)
  pig ext install pgsql-common               # install common utils such as patroni pgbouncer pgbackrest,...


Flags:
  -h, --help   help for add
  -y, --yes    auto confirm install

ext rm

Remove one or more PostgreSQL extensions.

pig ext rm [ext...] [-y]

Options:

  • -y, --yes: Auto-confirm removal

Examples:

pig ext rm pg_duckdb                   # Remove a specific extension
pig ext rm postgis timescaledb         # Remove multiple extensions
pig ext rm pgvector -y                 # Remove with auto-confirmation

ext update

Update installed extensions to their latest versions.

pig ext update [ext...] [-y]

Options:

  • -y, --yes: Auto-confirm updates

Examples:

pig ext update                         # Update all installed extensions
pig ext update postgis                 # Update a specific extension
pig ext update postgis timescaledb     # Update multiple extensions
pig ext update -y                      # Update with auto-confirmation

pig import

Download extension packages to the local repo for offline installation.

Usage:
  pig ext import [ext...] [flags]

Aliases:
  import, get

Examples:

  pig ext import postgis                # import postgis extension packages
  pig ext import timescaledb pg_cron    # import multiple extensions
  pig ext import pg16                   # import postgresql 16 packages
  pig ext import pgsql-common           # import common utilities
  pig ext import -d /www/pigsty postgis # import to specific path


Flags:
  -h, --help          help for import
  -d, --repo string   specify repo dir (default "/www/pigsty")

Options:

  • -d, --repo: Specify the repository directory (default: /www/pigsty)

Examples:

pig ext import postgis                 # Import PostGIS packages
pig ext import timescaledb pg_cron     # Import multiple extension packages
pig ext import pg16                    # Import PostgreSQL 16 packages
pig ext import pgsql-common            # Import common utility packages

Link a PostgreSQL installation to the system PATH.

link postgres to active PATH

Usage:
  pig ext link <-v pgver|-p pgpath> [flags]

Aliases:
  link, ln

Examples:

  pig ext link 16                      # link pgdg postgresql 16 to /usr/pgsql
  pig ext link /usr/pgsql-16           # link specific pg to /usr/pgsql
  pig ext link /u01/polardb_pg         # link polardb pg to /usr/pgsql
  pig ext link null|none|nil|nop|no    # unlink current postgres install


Flags:
  -h, --help   help for link

Examples:

pig ext link 17                        # Link PostgreSQL 17 to /usr/pgsql
pig ext link 16                        # Link PostgreSQL 16 to /usr/pgsql
pig ext link /usr/pgsql-16             # Link from a specific path to /usr/pgsql
pig ext link null                      # Unlink current PostgreSQL installation

upgrade

Update the extension catalog to the latest version.

pig ext upgrade


7 - Pigsty Administration

How to manage pigsty with pig sty subcommand?

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 (Download), Bootstrap, Configure, and Install Pigsty

  pig sty init    [-pfvd]      # install pigsty (~/pigsty by default)
  pig sty boot    [-rpk]       # install ansible and prepare offline pkg
  pig sty conf    [-civrsxn]   # configure pigsty and generate config
  pig sty install              # use pigsty to install & provisioning env (DANGEROUS!)
  pig sty get                  # download pigsty source tarball
  pig sty list                 # list available pigsty versions

Usage:
  pig sty [command]

Aliases:
  sty, s, pigsty

Examples:
  Get Started: https://pigsty.io/docs/setup/install/
  pig sty init                 # extract and init ~/pigsty
  pig sty boot                 # install ansible & other deps
  pig sty conf                 # generate pigsty.yml config file
  pig sty install              # run pigsty/install.yml playbook

Available Commands:
  boot        Bootstrap Pigsty
  conf        Configure Pigsty
  get         download pigsty available versions
  init        Install Pigsty
  install     run pigsty install.yml playbook
  list        list pigsty available versions

Flags:
  -h, --help   help for sty

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

sty init

pig sty init
  -p | --path    : where to install, ~/pigsty by default
  -f | --force   : force overwrite existing pigsty dir
  -v | --version : pigsty version, embedded by default
  -d | --dir     : download directory, /tmp by default

Usage:
  pig sty init [flags]

Aliases:
  init, i

Examples:

  pig sty init                   # install to ~/pigsty with embedded version
  pig sty init -f                # install and OVERWRITE existing pigsty dir
  pig sty init -p /tmp/pigsty    # install to another location /tmp/pigsty
  pig sty init -v 3.3            # get & install specific version v3.3.0
  pig sty init 3                 # get & install specific version v3 latest


Flags:
  -d, --dir string       pigsty download directory (default "/tmp")
  -f, --force            overwrite existing pigsty (false by default)
  -h, --help             help for init
  -p, --path string      target directory (default "~/pigsty")
  -v, --version string   pigsty version string

sty boot

pig sty boot
  [-r|--region <region]   [default,china,europe]
  [-p|--path <path>]      specify another offline pkg path
  [-k|--keep]             keep existing upstream repo during bootstrap

Check https://pigsty.io/docs/setup/offline/#bootstrap for details

Usage:
  pig sty boot [flags]

Aliases:
  boot, b, bootstrap

Flags:
  -h, --help            help for boot
  -k, --keep            keep existing repo
  -p, --path string     offline package path
  -r, --region string   default,china,europe,...

sty conf

Configure pigsty with ./configure

pig sty conf
  [-c|--conf <name>       # [meta|dual|trio|full|prod]
  [--ip <ip>]             # primary IP address (skip with -s)
  [-v|--version <pgver>   # [17|16|15|14|13]
  [-r|--region <region>   # [default|china|europe]
  [-s|--skip]             # skip IP address probing
  [-x|--proxy]            # write proxy env from environment
  [-n|--non-interactive]  # non-interactively mode

Check https://pigsty.io/docs/setup/install/#configure for details

Usage:
  pig sty conf [flags]

Aliases:
  conf, c, configure

Examples:

  pig sty conf                       # use the default conf/meta.yml config
  pig sty conf -c rich -x            # use the rich.yml template, add your proxy env to config
  pig sty conf -c supa --ip=10.9.8.7 # use the supa template with 10.9.8.7 as primary IP
  pig sty conf -c full -v 16         # use the 4-node full template with pg16 as default
  pig sty conf -c oss -s             # use the oss template, skip IP probing and replacement
  pig sty conf -c slim -s -r china   # use the 2-node slim template, designate china as region


Flags:
  -c, --conf string       config template name
  -h, --help              help for conf
      --ip string         primary ip address
  -n, --non-interactive   configure non-interactive
  -p, --proxy             configure proxy env
  -r, --region string     upstream repo region
  -s, --skip              skip ip probe
  -v, --version string    postgres major version

sty install

run pigsty install.yml playbook

Usage:
  pig sty install [flags]

Aliases:
  install, ins, install

Flags:
  -h, --help   help for install


8 - Building Infrastructure

How to setup building infrastructure with pig build subcommand?

pig build is a powerful command-line tool that simplifies the entire workflow of building and managing PostgreSQL extensions - from setting up the build environment to compiling extensions across different operating systems.

Overview

Build Postgres Extension

Usage:
  pig build [command]

Aliases:
  build, b

Examples:
pig build - Build Postgres Extension

  pig build repo                   # init build repo (=repo set -ru)
  pig build tool  [mini|full|...]  # init build toolset
  pig build proxy [id@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


Available Commands:
  ext         Build extension
  get         Download source code tarball
  proxy       Initialize build proxy
  repo        Initialize required repos
  rust        Initialize rust and pgrx environment
  spec        Initialize building spec repo
  tool        Initialize build tools

Flags:
  -h, --help   help for build

Global Flags:
      --debug              enable debug mode
  -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

Examples

Setting up the build environment

# Initialize repositories and tools
pig build repo
pig build tool
pig build spec

# For Rust-based extensions
pig build rust

# Download standard extensions
pig build get std                # download all tarballs
pig build get citus timescaledb  # download specific tarballs

# Build specific extensions
pig build ext citus

Build Workflow

A typical workflow for building PostgreSQL extensions with pig build:

  1. Set up repositories: pig build repo
  2. Install build tools: pig build tool
  3. (Optional) Set up proxy: pig build proxy id@host:port
  4. (Optional, for Rust extensions) Set up Rust: pig build rust
  5. Initialize build specs: pig build spec
  6. Download source code: pig build get [prefixes]
  7. Build extensions: pig build ext [extname...]

Notes

  • The build process differs between EL and DEB-based distributions
  • Some commands may require sudo privileges to install system packages
  • For Rust-based extensions, set up the Rust environment first
  • Proxy setup is optional and only needed in environments with restricted internet access

OS Support

The pig build command supports:

  • EL distributions: RHEL, Rocky, CentOS (tested on versions 8 and 9)
  • DEB distributions: Debian (tested on versions 12), Ubuntu (tested on versions 22.04 and 24.04)
  • May have MacOS support via homebrew in the future

build repo

Aliases: r

Adding required upstream repo for building PostgreSQL extensions by running the repository add command with update and remove flags enabled.

pig build repo

build tool

Aliases: t

Installs the build tools required for compiling PostgreSQL extensions.

pig build tool

Parameters:

  • mode: The installation mode (default: “mini”)
    • Available modes depend on the operating system and distribution

This command installs necessary build dependencies including:

For EL distributions (RHEL, Rocky, CentOS):

make, cmake, ninja-build, pkg-config, lld, git, lz4, unzip, ncdu, rsync, vray,
rpmdevtools, dnf-utils, pgdg-srpm-macros, postgresql1*-devel, postgresql1*-server, jq,
readline-devel, zlib-devel, libxml2-devel, lz4-devel, libzstd-devel, krb5-devel,

For DEB distributions (Debian, Ubuntu):

make, cmake, ninja-build, pkg-config, lld, git, lz4, unzip, ncdu, rsync, vray,
debhelper, devscripts, fakeroot, postgresql-all, postgresql-server-dev-all, jq,
libreadline-dev, zlib1g-dev, libxml2-dev, liblz4-dev, libzstd-dev, libkrb5-dev,

build rust

Aliases: rs

Sets up the Rust environment and PGRX for building PostgreSQL extensions in Rust.

pig build rust [-v <pgrx_ver>]

Parameters:

  • -v <pgrx_ver>: The PGRX version to install (default: “0.12.9”)

This command:

  1. Installs Rust using rustup if not already installed
  2. Installs the specified version of cargo-pgrx
  3. Initializes PGRX with appropriate PostgreSQL configurations (versions 13-17)

build spec

Aliases: s

Initializes the build specification repository based on the operating system:

pig build spec

  • For EL distributions (RHEL, Rocky, CentOS):

    1. Clones the RPM Spec Repo from GitHub
    2. Sets up the RPM build fhs on ~/rpmbuild

  • For DEB distributions (Debian, Ubuntu):

    1. Clones the DEB Spec Repo from GitHub
    2. Setup building directory fhs on ~/deb

The repo contains building specs (rpmspecs or debian control files) for PIGSTY maintained extensions.

build get

Aliases: get

Downloads source code tarballs for PostgreSQL extensions.

pig build get [prefixes|all|std]

Parameters:

  • std: Download standard packages (excluding large ones, default behavior)
  • all: Download all available source code tarballs (for batch building)
  • [prefixes]: One or more prefixes to filter the packages to download

Downloaded files are stored in:

  • EL: ~/rpmbuild/SOURCES/
  • DEB: ~/deb/tarball/

Example:

pig build get std                # get standard packages (except for pg_duckdb/pg_mooncake/omnigres/plv8, too large)
pig build get all                # get all available source code tarballs

pig build get pg_mooncake        # get pg_mooncake source code
pig build get pg_duckdb          # get pg_duckdb source code
pig build get omnigres           # get omnigres source code
pig build get plv8               # get plv8 source code

pig build get citus              # get citus source code
pig build get timescaledb        # get timescaledb source code
pig build get hydra              # get hydra source code
pig build get pgjwt              # get pgjwt source code
....

build ext

Aliases: e

Builds PostgreSQL extensions using the appropriate build environment for the current operating system.

pig build ext [extname...]

Parameters:

  • extname: One or more extension names to build

For each extension, the command:

  1. Changes to the appropriate build directory (~/rpmbuild/BUILD or ~/deb/build)
  2. Runs the make command with the extension name (e.g. make citus)
  3. Reports success or failure for each extension build

It actually leverages the make <ext> command in the spec repo to build the extension.

build proxy

Aliases: p

Sets up a proxy server for accessing external resources, useful in environments with restricted internet access.

This is purely optional, and you dont’t need to it if you don’t have any connection problems.

pig build proxy [id@host:port] [local]

Parameters:

  • id@host:port: The remote v2ray proxy specification in the format user-id@host:port
  • local: The local address for v2ray to bind to (default: “127.0.0.1:12345”)

This command:

  1. Installs proxy software if not already present (/usr/local/bin/v2ray)
  2. Configures the proxy with specified remote and local settings
  3. Creates environment setup scripts in /etc/profile.d/proxy.sh
  4. Configures the proxy service
  5. Tests the proxy connectivity via curl google

You can load the proxy environment variables by running:

. /etc/profile.d/proxy.sh

After setup, you can use these following aliases:

  • po: Enable proxy (set proxy environment variables in your current shell session)
  • px: Disable proxy (unset proxy environment variables)
  • pck: Check proxy status (via ping google)


9 - Compatibility

Linux Distribution compatibility matrix

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 Distro Maojr Minor x86_64 aarch64 PG17 PG16 PG15 PG14 PG13
EL9 RHEL 9 / Rocky9 / Alma9 9.4 el9.x86_64 el9.aarch64
D12 Debian 12 (bookworm) 12.7 d12.x86_64 d12.aarch64
U22 Ubuntu 22.04 (jammy) 22.04.5 u22.x86_64 u22.aarch64
U24 Ubuntu 24.04 (noble) 24.04.5 u24.x86_64 u24.aarch64
EL8 RHEL 8 / Rocky8 / Alma8 8.10 el8.x86_64 el8.aarch64

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



10 - Release Note

pig 包管理器发布历史与变更记录
Version Date Summary Release
v0.4.0 2025-05-0? do & pt sub-cmd, halo & orioledb v0.4.0
v0.3.4 2025-04-05 routine update v0.3.4
v0.3.3 2025-03-25 alias, repo, deps v0.3.3
v0.3.2 2025-03-21 new extensions v0.3.2
v0.3.1 2025-03-19 minior bug fix v0.3.1
v0.3.0 2025-02-24 new home page and extension catalog v0.3.0
v0.2.2 2025-02-22 404 extensions v0.2.2
v0.2.0 2025-02-14 400 extensions v0.2.0
v0.1.4 2025-02-12 routine bugfix v0.1.4
v0.1.3 2025-01-23 390 extensions v0.1.3
v0.1.2 2025-01-12 the anon extension and 350 other ext v0.1.2
v0.1.1 2025-01-09 Update Extension List v0.1.1
v0.1.0 2024-12-29 repo, ext, sty, and self-update v0.1.0
v0.0.1 2024-12-23 Genesis Release v0.0.1

v0.4.0 (WIP)

  • Updated extension list, available extensions reached 407
  • Added pig do subcommand for executing Pigsty playbook tasks
  • Added pig pt subcommand for wrapping Patroni command-line tools
  • Added extension aliases: openhalo and orioledb
  • Added gitlab-ce / gitlab-ee repository distinction
  • Built with the latest Go 1.24.2 and upgraded dependency versions
  • Fixed pig ext status panic issue under specific conditions
  • Fixed pig ext scan unable to match several extensions

Extension Package Updates

  • Updated pg_search to 0.15.13
  • Updated citus to 13.0.3
  • Updated timescaledb to 2.19.1
  • Updated pgcollection RPM to 1.0.0
  • Updated pg_vectorize RPM to 0.22.1
  • Updated pglite_fusion RPM to 0.0.4
  • Updated aggs_for_vecs RPM to 1.4.0
  • Updated pg_tracing RPM to 0.1.3
  • Updated pgmq RPM to 1.5.1

Release: https://github.com/pgsty/pig/releases/tag/v0.4.0

v0.3.4

curl https://repo.pigsty.io/pig | bash -s 0.3.4
  • routine extension metadata update
  • use aliyun epel mirror instead of broken tsinghua tuna mirror
  • bump pigsty version string
  • add gitlab repo to repo list
5c0bba04d955bbe6a29d24d31aa17c6b  pig-0.3.4-1.aarch64.rpm
42636b9fc64d7882391d856d36d715e7  pig-0.3.4-1.x86_64.rpm
1a6296421d642000ad75a5a41bc9ab96  pig-v0.3.4.linux-amd64.tar.gz
f7ea5ba8abaa89e866811e5b2508e82f  pig-v0.3.4.linux-arm64.tar.gz
2dd63cdb5965f78a48da462a0453001d  pig_0.3.4-1_amd64.deb
094b9e028e81c46d71ee315d8a223ada  pig_0.3.4-1_arm64.deb

Release: https://github.com/pgsty/pig/releases/tag/v0.3.4

v0.3.3

  • add pig build dep command to install extension build dependencies
  • update default repo list
    • use pigsty.io mirror for mssql module (wiltondb/babelfish)
    • merge docker module into infra
    • remove pg16/17 from el7 target
  • allow install extensions in el7
  • update package alias
    • pgsql, pgsql-main, pgsql-core, pgsql-mini, pgsql-full
    • ivorysql now maps to ivorysql4
    • timescaledb-utils
    • pgbackrest_exporter
    • remove pgsql-simple
  • Pulls #13 Bump github.com/golang-jwt/jwt/v5 from 5.2.1 to 5.2.2
  • Bump polardb to 15.12.3.0-e1e6d85b
  • pig repo set now will auto update the meta cache
  • clean up embedded pigsty tarball

What’s Changed

New Contributors

Full Changelog: https://github.com/pgsty/pig/compare/v0.3.2...v0.3.3

Release: https://github.com/pgsty/pig/releases/tag/v0.3.3

Checksums

4e10567077e5d8cefd94d1c7aeb9478b  pig-0.3.3-1.aarch64.rpm
cc8a423abeb0f5316b427097993b9c6e  pig-0.3.3-1.x86_64.rpm
835d4f63b4ee0b36e2322a4ffef6527a  pig-v0.3.3.linux-amd64.tar.gz
c43e082c661e75d91f1c726e60911ea3  pig-v0.3.3.linux-arm64.tar.gz
938db83c5ca065419b8185adb285ed5a  pig_0.3.3-1_amd64.deb
75af6731adc4d31aa3458d70fc7f4e42  pig_0.3.3-1_arm64.deb

v0.3.2

Enhancement

  • new extensions
  • use upx to reduce binary size
  • remove embeded pigsty to reudce binary size
  • allow specifiy -y to force re-install rust in pig build rust
  • allow specifiy -v to specifiy pgrx version to be installed

New Extensions

List of 405 PG extensions:

  • apache age 13 - 17 el rpm (1.5.0)
  • pgspider_ext 1.3.0 (new extension)
  • timescaledb 2.18.2 -> 2.19.0
  • citus 13.0.1 -> 13.0.2
  • documentdb 1.101-0 -> 1.102-0
  • pg_analytics: 0.3.4 -> 0.3.7
  • pg_search: 0.15.2 -> 0.15.8
  • pg_ivm 1.9 -> 1.10
  • emaj 4.4.0 -> 4.6.0
  • pgsql_tweaks 0.10.0 -> 0.11.0
  • pgvectorscale 0.4.0 -> 0.6.0 (pgrx 0.12.5)
  • pg_session_jwt 0.1.2 -> 0.2.0 (pgrx 0.12.6)
  • wrappers 0.4.4 -> 0.4.5 (pgrx 0.12.9)
  • pg_parquet 0.2.0 -> 0.3.1 (pgrx 0.13.1)
  • vchord 0.2.1 -> 0.2.2 (pgrx 0.13.1)
  • pg_tle 1.2.0 -> 1.5.0
  • supautils 2.5.0 -> 2.6.0
  • sslutils 1.3 -> 1.4
  • pg_profile 4.7 -> 4.8
  • pg_snakeoil 1.3 -> 1.4
  • pg_jsonschema 0.3.2 -> 0.3.3
  • pg_incremental: 1.1.1 -> 1.2.0
  • pg_stat_monitor 2.1.0 -> 2.1.1
  • fix ddl_historization ver 0.7 -> 0.0.7
  • fix pg_sqlog 3.1.7 -> 1.6
  • fix pg_random remove dev suffix
  • asn1oid 1.5 -> 1.6
  • table_log 0.6.1 -> 0.6.4

Checksums

f773aedf4a76d031f411cb38bc623134  pig-0.3.2-1.aarch64.rpm
fa9084877deb57d4882b7d9531ea0369  pig-0.3.2-1.x86_64.rpm
7f9a03c9dd23cba094191a8044fa0263  pig-v0.3.2.linux-amd64.tar.gz
adda8986efc048565834cda1ef206a20  pig-v0.3.2.linux-arm64.tar.gz
5b27cefdc716629db8f1fbc534f58691  pig_0.3.2-1_amd64.deb
936e85bda5818da4c20b758ebd65e618  pig_0.3.2-1_arm64.deb

Release: https://github.com/pgsty/pig/releases/tag/v0.3.2

v0.3.1

Routine bugfix

  • fix repo format string
  • fix ext info links
  • update pg_mooncake metadata

Checksums

9251aa18e663f1ecf239adcba3a798b9  pig-0.3.1-1.aarch64.rpm
3b91e7faa78c5f0283d27ffe632dda46  pig-0.3.1-1.x86_64.rpm
87c75dfd114252230c53ee8c5d60dac4  pig-v0.3.1.linux-amd64.tar.gz
82832ae767e226627087b97a87982daf  pig-v0.3.1.linux-arm64.tar.gz
4d99f9c03915accf413b6374b75f1bdb  pig_0.3.1-1_amd64.deb
e38e8a21ed73a37d4588053f8c900f7c  pig_0.3.1-1_arm64.deb

Release: https://github.com/pgsty/pig/releases/tag/v0.3.1

v0.3.0

The pig project now has a new homepage, alone with the PostgreSQL Extension Catalog.

curl https://repo.pigsty.io/pig | bash    # cloudflare 
curl https://repo.pigsty.cc/pig | bash    # china cdn

You can install PostgreSQL Kernels along with & 404 extensions with a simple command. Besides, pig v0.3 is also embedded & shipped with the latest Pigsty v3.3.0.

New Features

pig build subcommand with the ability to setup extension building environment

pig build repo     # init build repo (=repo set -ru)
pig build tool     # init build toolset
pig build rust     # init rustc & pgrx (0.12.9)
pig build spec     # init rpm/deb spec repo
pig build get      # get extension src tarball
pig build ext      # build extension

## 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
pig build get citus

pig build ext citus
pig build ext timescaledb

And other utils such as building proxy:

pig build proxy                  # install v2ray proxy
pig build proxy [user@host:port] # init & setup proxy

And pig 0.3.0 is shipped with Pigsty 3.3.0

New Extensions

The ext.pigsty.io catalog is moving to https://pigsty.io/ext with more information!

Checksums

9cc3848ab13c41a0415f1fea6294ad2d  pig-0.3.0-1.aarch64.rpm
ee99a6c1ff17975ed184f009a4b1aac5  pig-0.3.0-1.x86_64.rpm
b06f6b5aeaa83a9d76c9b563b2516e1c  pig-v0.3.0.linux-amd64.tar.gz
d783732413e4f32074adeab2d5d092c3  pig-v0.3.0.linux-arm64.tar.gz
7c942b8dbd78458d5371c1abca2571c6  pig_0.3.0-1_amd64.deb
c0a411cf53cb58706ca81b49b4fc840e  pig_0.3.0-1_arm64.deb

Release: https://github.com/pgsty/pig/releases/tag/v0.3.0

v0.2.2

404 Extensions Available in Pig v0.2.2

curl https://repo.pigsty.io/pig | bash -s v0.2.2
  • documentdb 0.101-0
  • pgcollection (new) 0.9.1
  • pg_bzip (new) 1.0.0
  • pg_net 0.14.0 (some distro)
  • pg_curl 2.4.2
  • vault 0.3.1 (SQL -> C)
  • table_version 1.10.3 -> 1.11.0
  • pg_duration 1.0.2
  • timescaledb 2.18.2
  • pg_analytics 0.3.4
  • pg_search 0.15.2
  • pg_graphql 1.5.11
  • vchord 0.1.1 -> 0.2.1 ((+13))
  • vchord_bm25 0.1.0 -> 0.1.1
  • pg_mooncake 0.1.1 -> 0.1.2
  • pg_duckdb 0.2.0 -> 0.3.1
  • pgddl 0.29
  • pgsql_tweaks 0.11.0

Release: https://github.com/pgsty/pig/releases/tag/v0.2.2

v0.2.0

Install the latest pig version with:

curl -fsSL https://repo.pigsty.io/pig | bash

New Extensions

Update Extension Version

  • citus 13.0.0 -> 13.0.1
  • pg_mooncake 0.1.0 -> 0.1.1
  • timescaledb 2.17.2 -> 2.18.1
  • supautils 2.5.0 -> 2.6.0
  • VectorChord 0.1.0 -> 0.2.0
  • pg_bulkload 3.1.22 (+pg17)
  • pg_store_plan 1.8 (+pg17)
  • pg_search 0.14 -> 0.15.1
  • pg_analytics 0.3.0 -> 0.3.2
  • pgroonga 3.2.5 -> 4.0.0
  • zhparser 2.2 -> 2.3
  • pg_vectorize 0.20.0 -> 0.21.1

Release: https://github.com/pgsty/pig/releases/tag/v0.2.0

v0.1.4

Install the latest pig version with:

curl -fsSL https://repo.pigsty.io/pig | bash

New Extensions

Update Extension Version

  • citus 13.0.0 -> 13.0.1
  • pg_mooncake 0.1.0 -> 0.1.1
  • timescaledb 2.17.2 -> 2.18.1
  • supautils 2.5.0 -> 2.6.0
  • VectorChord 0.1.0 -> 0.2.0
  • pg_bulkload 3.1.22 (+pg17)
  • pg_store_plan 1.8 (+pg17)
  • pg_search 0.14 -> 0.15.1
  • pg_analytics 0.3.0 -> 0.3.2
  • pgroonga 3.2.5 -> 4.0.0
  • zhparser 2.2 -> 2.3
  • pg_vectorize 0.20.0 -> 0.21.1

Checksums

6da06705be1c179941327c836d455d35  pig-0.1.4-1.aarch64.rpm
9fa5712e3cfe56e0dcf22a11320b01b1  pig-0.1.4-1.x86_64.rpm
af506dc37f955a7a2e31ff11e227450c  pig-v0.1.4.linux-amd64.tar.gz
1e6eb3dc1ad26f49b07afabdd9142d4e  pig-v0.1.4.linux-arm64.tar.gz
83ae89b58bff003da5c3022eeac1786e  pig_0.1.4_amd64.deb
d6778e628d82bddf3fae1e058e1e05e4  pig_0.1.4_arm64.deb

pig-meme

Release: https://github.com/pgsty/pig/releases/tag/v0.1.4

v0.1.3

v0.1.3, routine update, with 390 extensions available now!

curl https://repo.pigsty.io/pig | bash
curl https://repo.pigsty.cc/pig | bash

Checksums

c79b74f676b03482859f5519b279b657  pig-0.1.3-1.aarch64.rpm
1d00a7cd5855a65e4db964075a5e49f6  pig-0.1.3-1.x86_64.rpm
6cd8507b130fca093247278e36d9478b  pig-v0.1.3.linux-amd64.tar.gz
5eee92908701b0d456ec3c15bc817c0b  pig-v0.1.3.linux-arm64.tar.gz
cb376ef2c3512ad35ff43132942c0052  pig_0.1.3_amd64.deb
2b545abc617670a96c2edd13878e0227  pig_0.1.3_arm64.deb

Release: https://github.com/pgsty/pig/releases/tag/v0.1.3

v0.1.2

351 PostgreSQL Extensions, including the powerful postgresql-anonymizer 2.0

Now you can install pig with:

curl -fsSL https://repo.pigsty.io/pig | bash
curl -fsSL https://repo.pigsty.cc/pig | bash

Add New Extension

  • add pg_anon 2.0.0
  • add omnisketch 1.0.2
  • add ddsketch 1.0.1
  • add pg_duration 1.0.1
  • add ddl_historization 0.0.7
  • add data_historization 1.1.0
  • add schedoc 0.0.1
  • add floatfile 1.3.1
  • add pg_upless 0.0.3
  • add pg_task 1.0.0
  • add pg_readme 0.7.0
  • add vasco 0.1.0
  • add pg_xxhash 0.0.1

Update Extension

  • lower_quantile 1.0.3
  • quantile 1.1.8
  • sequential_uuids 1.0.3
  • pgmq 1.5.0 (subdir)
  • floatvec 1.1.1
  • pg_parquet 0.2.0
  • wrappers 0.4.4
  • pg_later 0.3.0
  • topn fix for deb.arm64
  • add age 17 on debian
  • powa + pg17, 5.0.1
  • h3 + pg17
  • ogr_fdw + pg17
  • age + pg17 1.5 on debian
  • pgtap + pg17 1.3.3
  • repmgr
  • topn + pg17
  • pg_partman 5.2.4
  • credcheck 3.0
  • ogr_fdw 1.1.5
  • ddlx 0.29
  • postgis 3.5.1
  • tdigest 1.4.3
  • pg_repack 1.5.2

Release: https://github.com/pgsty/pig/releases/tag/v0.1.2

v0.1.0

The pig CLI v0.1 is out, with the following new features:

Install Script

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

Extension Management

You can download the extension and its dependencies with import subcommand, activate different postgres major versions with link, and prepare building env with build subcommand

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 build   [ext...]     # setup building env for extension

Repo Management

You can now create local repo and create a tarball (offline package) from it, copy it to somewhere (e.g. without internet access), and create a repo from that offline package:

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

Pigsty Management

The pig can also be used as a CLI tool for Pigsty - the battery-include free PostgreSQL RDS

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

Self-Updating

To update pig itself to the latest version, you can use the following command:

pig update

Info

Now pig info provide more details about your OS & PG environment:

$ pig info

# [Configuration] ================================
Pig Version      : 0.1.0
Pig Config       : /home/vagrant/.pig/config.yml
Log Level        : info
Log Path         : stderr

# [OS Environment] ===============================
OS Distro Code   : el9
OS Architecture  : amd64
OS Package Type  : rpm
OS Vendor ID     : rocky
OS Version       : 9
OS Version Full  : 9.3
OS Version Code  : el9

# [PG Environment] ===============================
Installed:
* PostgreSQL 17.2  74  Extensions

Active:
PG Version      :  PostgreSQL 17.2
Config Path     :  /usr/pgsql-17/bin/pg_config
Binary Path     :  /usr/pgsql-17/bin
Library Path    :  /usr/pgsql-17/lib
Extension Path  :  /usr/pgsql-17/share/extension

# [Pigsty Environment] ===========================
Inventory Path   : /home/vagrant/pigsty/pigsty.yml
Pigsty Home      : /home/vagrant/pigsty
Embedded Version : 3.2.0

# [Network Conditions] ===========================
pigsty.cc  ping ok: 141 ms
pigsty.io  ping ok: 930 ms
google.com request error
Internet Access   :  true
Pigsty Repo       :  pigsty.io
Inferred Region   :  china
Latest Pigsty Ver :  v3.2.0

Enjoy PostgreSQL!

What’s Changed

New Contributors

Full Changelog: https://github.com/pgsty/pig/compare/v0.0.1...v0.1.0

Checksums

46165beec97ab9ff1314f80af953bd59  pig-0.1.0-1.aarch64.rpm
1320a6f9bfbd79948515657d6becbf37  pig-0.1.0-1.x86_64.rpm
bd078a5dc0c41454fcbbe0d8693d5fa0  pig-v0.1.0.linux-amd64.tar.gz
8a15e52f96735b78afa7da42843f1504  pig-v0.1.0.linux-arm64.tar.gz
4d25597cff8425c7e52a2b411344aa4a  pig_0.1.0_amd64.deb
d5f0874601bc1bbd0dd40b5c9982ea9f  pig_0.1.0_arm64.deb

pig

Release: https://github.com/pgsty/pig/releases/tag/v0.1.0

v0.0.1

pig

Get Started

Install the pig package first, you can also install via apt/yum command.

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

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, update cache      
$ pig ext install pg17         # install PostgreSQL 17 kernels with PGDG native packages
$ pig ext install pg_duckdb    # install the pg_duckdb extension (for current pg17)

That’s it! All set! you can check with the pig ext status sub command:

$ pig ext status               # show installed extension and pg status
                               # to print built-in contrib extension, use -c|--contrib flag
Installed PG Vers :  17 (active)
Active PostgreSQL :  PostgreSQL 17.2
PostgreSQL        :  PostgreSQL 17.2
Binary Path       :  /usr/pgsql-17/bin
Library Path      :  /usr/pgsql-17/lib
Extension Path    :  /usr/pgsql-17/share/extension
Extension Stat    :  1 Installed (PIGSTY 1, PGDG 0) + 67 CONTRIB = 68 Total

Name       Version  Cate  Flags   License  Repo    Package        Description
----       -------  ----  ------  -------  ------  ------------   ---------------------
pg_duckdb  0.2.0    OLAP  -dsl--  MIT      PIGSTY  pg_duckdb_17*  DuckDB Embedded in Postgres

(1 Rows) (Flags: b = HasBin, d = HasDDL, s = HasSolib, l = NeedLoad, t = Trusted, r = Relocatable, x = Unknown)

Check the advanced usage for details and list 340 available extensions.

asciicast

Installation

The pig util is a standalone go binary with no dependencies. you can just download the binary or use the following commands to add the repo and install it via package manager (recommended).

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

Compatibility

pig runs on: RHEL 8/9, Ubuntu 22.04/24.04, and Debian 12, on both amd64/arm64 arch

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 distros:

  • citus is not available on aarch64 and ubuntu 24.04
  • pljava is missing on el8
  • jdbc_fdw is missing on el8.aarch64 and el9.aarch64
  • pllua is missing on el8.aarch64 for pg 13,14,15
  • topn is missing on el8.aarch64 and el9.aarch64 for pg13, and all deb.aarch64
  • pg_partman and timeseries is missing on u24 for pg13
  • wiltondb is missing on d12

Release: https://github.com/pgsty/pig/releases/tag/v0.0.1