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

Return to the regular view of this page.

PIG Package Manager

PostgreSQL Extension Ecosystem Package Manager

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

PIG is a command-line tool specifically designed for installing, managing, and building PostgreSQL and its extensions. Developed in Go, it is ready to use out of the box, simple, and lightweight (4MB). PIG is not a reinvented wheel, but rather a PiggyBack - a high-level abstraction layer that leverages existing Linux distribution package managers (apt/dnf). It abstracts away the differences between operating systems, chip architectures, and PG major versions, allowing you to install and manage PG kernels and 451 extensions with just a few simple commands.

PIG is also automation-friendly by design: consistent parameter styles, clear error messages, and safe guards like --dry-run for high-risk operations.

Please note: for extension installation, pig is not a mandatory component. You can still use apt/dnf package managers to directly access the Pigsty PGSQL repository.

Quick Start

Use the following command to install PIG on your system:

Default Installation (Cloudflare CDN):

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

China Mirror:

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

After installation, you can get started with just a few commands. For example, to install PG 18 and the pg_duckdb extension:

$ pig repo set                        # One-time setup for Linux, Pigsty + PGDG repos (overwrites!)
$ pig install pg18                    # Install PostgreSQL 18 kernel (native PGDG packages)
$ pig install pg_duckdb -v 18         # Install pg_duckdb extension (for PG 18)
$ pig install -y postgis timescaledb  # Install multiple extensions for current active PG version
$ pig install -y vector               # You can use extension name (vector) or package name (pgvector)!

Command Reference

Run pig help <command> to get detailed help for subcommands.

Extension Management:

Pigsty Management:

About

The pig CLI tool is developed by Vonng ([email protected]) and is open-sourced under the Apache 2.0 license.

You can also check out the PIGSTY project, which provides a complete PostgreSQL RDS DBaaS experience including extension delivery.

  • PGEXT: Extension data and management tools
  • PIG: PostgreSQL package manager
  • PIGSTY: Batteries-included PostgreSQL distribution

1 - Getting Started

Quick start with pig, the PostgreSQL package manager

Here is a simple getting started tutorial to help you experience the core capabilities of the PIG package manager.

Short Version

curl -fsSL https://repo.pigsty.io/pig | bash   # Install PIG from Cloudflare
pig repo set                                   # One-time setup for Linux, Pigsty + PGDG repos (overwrites!)
pig install -v 18 -y pg18 pg_duckdb vector     # Install PG 18 kernel, pg_duckdb, pgvector extensions...

Installation

You can install pig with the following command:

China Mainland:

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

Global (Cloudflare CDN):

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

PIG binary is about 4 MB. On Linux it uses rpm or dpkg to install the latest available version:

$ curl -fsSL https://repo.pigsty.cc/pig | bash
[INFO] kernel = Linux
[INFO] machine = x86_64
[INFO] package = deb
[INFO] pkg_url = https://repo.pigsty.cc/pkg/pig/v1.0.0/pig_1.0.0-1_amd64.deb
[INFO] download = /tmp/pig_1.0.0-1_amd64.deb
[INFO] downloading pig v1.0.0
curl -fSL https://repo.pigsty.cc/pkg/pig/v1.0.0/pig_1.0.0-1_amd64.deb -o /tmp/pig_1.0.0-1_amd64.deb
######################################################################## 100.0%
[INFO] md5sum = a543882aa905713a0c50088d4e848951b6957a37a1594d7e9f3fe46453d5ce66
[INFO] installing: dpkg -i /tmp/pig_1.0.0-1_amd64.deb
(Reading database ... 166001 files and directories currently installed.)
Preparing to unpack /tmp/pig_1.0.0-1_amd64.deb ...
Unpacking pig (1.0.0-1) ...
Setting up pig (1.0.0-1) ...
[INFO] pig v1.0.0 installed successfully
check https://pgext.cloud for details

Check Environment

PIG is a Go-written binary program, installed by default at /usr/bin/pig. pig version prints version information:

$ pig version
pig version 1.0.0 linux/amd64
build: HEAD dc8f343 2026-01-26T15:52:04Z

Use pig status to print the current environment status, OS code, PG installation status, repository accessibility and latency.

$ pig status

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

# [OS Environment] ===============================
OS Distro Code   : u24
OS OSArch        : arm64
OS Package Type  : deb
OS Vendor ID     : ubuntu
OS Version       : 24
OS Version Full  : 24.04
OS Version Code  : noble

# [PG Environment] ===============================
Installed:
- PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2)  398 Extensions

Active:
PG Version      :  PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2)
Config Path     :  /usr/bin/pg_config
Binary Path     :  /usr/lib/postgresql/18/bin
Library Path    :  /usr/lib/postgresql/18/lib
Extension Path  :  /usr/share/postgresql/18/extension

# [Pigsty Environment] ===========================
Inventory Path   : Not Found
Pigsty Home      : Not Found

# [Network Conditions] ===========================
pigsty.cc  ping ok: 802 ms
pigsty.io  ping ok: 1410 ms
Internet Access   :  true
Pigsty Repo       :  pigsty.io
Inferred Region   :  china
Latest Pigsty Ver :  v4.1.0

Automation Tips

For production recovery tasks, it is recommended to run --dry-run first to preview the PITR execution plan before actually executing:

pig pitr -d --dry-run         # preview recovery steps only, no execution
pig pitr -d -y                # skip confirmation (automation scenarios)

List Extensions

Use the pig ext list command to print the built-in PG extension catalog.

$ pig ext list

Name                            Status              Version     Cate   Flags   License       Repo     PGVer  Package                               Description
----                            ------              -------     ----   ------  -------       ------   -----  ------------                          ---------------------
timescaledb                     installed  2.24.0      TIME   -dsl--  Timescale     PIGSTY   15-18  postgresql-18-timescaledb-tsl         Enables scalable inserts and complex queries for time-series dat
timescaledb_toolkit             installed  1.22.0      TIME   -ds-t-  Timescale     PIGSTY   15-18  postgresql-18-timescaledb-toolkit     Library of analytical hyperfunctions, time-series pipelining, an
timeseries                      installed  0.2.0       TIME   -d----  PostgreSQL    PIGSTY   13-18  postgresql-18-pg-timeseries           Convenience API for time series stack
periods                         installed  1.2.3       TIME   -ds---  PostgreSQL    PGDG     13-18  postgresql-18-periods                 Provide Standard SQL functionality for PERIODs and SYSTEM VERSIO
temporal_tables                 installed  1.2.2       TIME   -ds--r  BSD 2-Clause  PIGSTY   13-18  postgresql-18-temporal-tables         temporal tables
.........
pg_fact_loader                  not avail  2.0.1       ETL    -ds--x  MIT           PGDG     13-17  postgresql-18-pg-fact-loader          build fact tables with Postgres
pg_bulkload                     installed  3.1.23      ETL    bds---  BSD 3-Clause  PIGSTY   13-18  postgresql-18-pg-bulkload             pg_bulkload is a high speed data loading utility for PostgreSQL
test_decoding                   available  -           ETL    --s--x  PostgreSQL    CONTRIB  13-18  postgresql-18                         SQL-based test/example module for WAL logical decoding
pgoutput                        available  -           ETL    --s---  PostgreSQL    CONTRIB  13-18  postgresql-18                         Logical Replication output plugin


(450 Rows) (Status: installed, available, not avail | Flags: b = HasBin, d = HasDDL, s = HasLib, l = NeedLoad, t = Trusted, r = Relocatable, x = Unknown)

All extension metadata is defined in a data file named extension.csv. This file is updated with each pig release. You can update it directly using the pig ext reload command. The updated file is placed in ~/.pig/extension.csv by default. You can view and modify it, and you can also find the authoritative version in the project.

Add Repositories

To install extensions, you first need to add upstream repositories. pig repo can be used to manage Linux APT/YUM/DNF software repository configuration.

You can use the straightforward pig repo set to overwrite existing repository configuration, ensuring only necessary repositories exist in the system:

pig repo set                # One-time setup for all repos including Linux system, PGDG, PIGSTY (PGSQL+INFRA)

Warning: pig repo set will back up and clear existing repository configuration, then add required repositories with overwrite semantics.

Or choose the gentler pig repo add to add needed repositories:

pig repo add pgdg pigsty     # Add PGDG official repo and PIGSTY supplementary repo
pig repo add pgsql           # [Optional] Add PGDG and PIGSTY together as one "pgsql" module
pig repo update              # Update cache: apt update / yum makecache

PIG detects your network environment and chooses Cloudflare global CDN or China cloud CDN, but you can force a specific region with --region:

pig repo set      --region=china              # use China mirror for faster downloads
pig repo add pgdg --region=default --update   # force PGDG upstream repo

PIG does not support offline installation. You can download RPM/DEB packages yourself and copy them to isolated servers for installation. The related PIGSTY project provides local software repositories. You can use pig to install pre-downloaded extensions from local repos.

Install PG

After adding repositories, you can use pig ext add to install extensions (and related packages):

pig ext add -v 18 -y pgsql timescaledb postgis vector pg_duckdb pg_mooncake # install PG 18 kernel and extensions, auto-confirm

# This command will translate aliases to actual packages
INFO[20:34:44] translate alias 'pgsql' to package: postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl
INFO[20:34:44] translate extension 'timescaledb' to package: timescaledb-tsl_18
INFO[20:34:44] translate extension 'postgis' to package: postgis36_18
INFO[20:34:44] translate extension 'vector' to package: pgvector_18
INFO[20:34:44] translate extension 'pg_duckdb' to package: pg_duckdb_18
INFO[20:34:44] translate extension 'pg_mooncake' to package: pg_mooncake_18
INFO[20:34:44] installing packages: dnf install -y postgresql18 postgresql18-server postgresql18-libs postgresql18-contrib postgresql18-plperl postgresql18-plpython3 postgresql18-pltcl timescaledb-tsl_18 postgis36_18 pgvector_18 pg_duckdb_18 pg_mooncake_18

This uses the “alias translation” mechanism to map clean PG kernel/extension logical names into real RPM/DEB lists. If you do not need translation, use apt/dnf directly, or use the -n|--no-translation option with the pig install variant:

pig install vector     # with translation, installs pgvector_18 or postgresql-18-pgvector for current PG 18
pig install vector -n  # no translation, installs a component named vector (from pigsty-infra repo)

Alias Translation

PostgreSQL kernels and extensions map to many RPM/DEB packages. Remembering them is painful, so pig provides common aliases to simplify installation.

For example, on EL systems the following aliases translate to the RPM lists on the right:

pgsql:        "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl"
pg18:         "postgresql18 postgresql18-server postgresql18-libs postgresql18-contrib postgresql18-plperl postgresql18-plpython3 postgresql18-pltcl"
pg18-client:  "postgresql18"
pg18-server:  "postgresql18-server postgresql18-libs postgresql18-contrib"
pg18-devel:   "postgresql18-devel"
pg18-basic:   "pg_repack_18 wal2json_18 pgvector_18"
pg17-mini:    "postgresql17 postgresql17-server postgresql17-libs postgresql17-contrib"
pg16-full:    "postgresql16 postgresql16-server postgresql16-libs postgresql16-contrib postgresql16-plperl postgresql16-plpython3 postgresql16-pltcl postgresql16-llvmjit postgresql16-test postgresql16-devel"
pg15-main:    "postgresql15 postgresql15-server postgresql15-libs postgresql15-contrib postgresql15-plperl postgresql15-plpython3 postgresql15-pltcl pg_repack_15 wal2json_15 pgvector_15"
pg14-core:    "postgresql14 postgresql14-server postgresql14-libs postgresql14-contrib postgresql14-plperl postgresql14-plpython3 postgresql14-pltcl"

Note the $v placeholder is replaced by the PG major version. When you use the pgsql alias, $v becomes 18, 17, etc. So when you install the pg18-server alias, EL actually installs postgresql18-server, postgresql18-libs, postgresql18-contrib, while Debian/Ubuntu installs postgresql-18. Pig handles all details.

Common PostgreSQL Aliases

Alias translation list for EL

"pgsql":        "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl",
"pgsql-mini":   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib",
"pgsql-core":   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl",
"pgsql-full":   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-llvmjit postgresql$v-test postgresql$v-devel",
"pgsql-main":   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl pg_repack_$v wal2json_$v pgvector_$v",
"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",

Alias translation list for Debian/Ubuntu

"pgsql":        "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v",
"pgsql-mini":   "postgresql-$v postgresql-client-$v",
"pgsql-core":   "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v",
"pgsql-full":   "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v postgresql-server-dev-$v",
"pgsql-main":   "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v postgresql-$v-repack postgresql-$v-wal2json postgresql-$v-pgvector",
"pgsql-client": "postgresql-client-$v",
"pgsql-server": "postgresql-$v",
"pgsql-devel":  "postgresql-server-dev-$v",
"pgsql-basic":  "postgresql-$v-repack postgresql-$v-wal2json postgresql-$v-pgvector",

These aliases can be instantiated with major versions, or you can use versioned aliases like pg18, pg17, etc. For example, for PostgreSQL 18 you can use:

pgsqlpg18pg17pg16pg15pg14pg13
pgsqlpg18pg17pg16pg15pg14pg13
pgsql-minipg18-minipg17-minipg16-minipg15-minipg14-minipg13-mini
pgsql-corepg18-corepg17-corepg16-corepg15-corepg14-corepg13-core
pgsql-fullpg18-fullpg17-fullpg16-fullpg15-fullpg14-fullpg13-full
pgsql-mainpg18-mainpg17-mainpg16-mainpg15-mainpg14-mainpg13-main
pgsql-clientpg18-clientpg17-clientpg16-clientpg15-clientpg14-clientpg13-client
pgsql-serverpg18-serverpg17-serverpg16-serverpg15-serverpg14-serverpg13-server
pgsql-develpg18-develpg17-develpg16-develpg15-develpg14-develpg13-devel
pgsql-basicpg18-basicpg17-basicpg16-basicpg15-basicpg14-basicpg13-basic

Install Extensions

Pig detects your PostgreSQL installation. If there is an active PG installation (detected via pg_config in PATH), pig installs extensions for that PG major by default.

pig install pg_smtp_client          # simplest
pig install pg_smtp_client -v 18    # specify major version (more stable)
pig install pg_smtp_client -p /usr/lib/postgresql/16/bin/pg_config   # another way to target PG

dnf install pg_smtp_client_18       # most direct, but not all extensions are that simple

Tip: to add a specific PG major version into PATH, use pig ext link:

pig ext link pg18             # create /usr/pgsql symlink and write /etc/profile.d/pgsql.sh
. /etc/profile.d/pgsql.sh     # take effect now and update PATH

If you want a specific package version, use name=ver syntax:

pig ext add -v 18 pgvector=0.7.2 # install pgvector 0.7.2 for PG 18
pig ext add pg16=16.5            # install PostgreSQL 16 with a specific minor version

Warning: currently only PGDG YUM repositories provide historical extension versions. PIGSTY repo and PGDG APT repo only provide the latest extension versions.

Show Extensions

pig ext status shows installed extensions.

$ pig ext status

Installed:
- PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2)  398 Extensions

Active:
PG Version      :  PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2)
Config Path     :  /usr/bin/pg_config
Binary Path     :  /usr/lib/postgresql/18/bin
Library Path    :  /usr/lib/postgresql/18/lib
Extension Path  :  /usr/share/postgresql/18/extension
Extension Stat  :  329 Installed (PIGSTY 234, PGDG 95) + 69 CONTRIB = 398 Total

Name                          Version  Cate  Flags   License     Repo    Package                               Description
----                          -------  ----  ------  -------     ------  ------------                          ---------------------
timescaledb                   2.24.0   TIME  -dsl--  Timescale   PIGSTY  postgresql-18-timescaledb-tsl         Enables scalable inserts and complex queries for time-series dat
timescaledb_toolkit           1.22.0   TIME  -ds-t-  Timescale   PIGSTY  postgresql-18-timescaledb-toolkit     Library of analytical hyperfunctions, time-series pipelining, an
timeseries                    0.2.0    TIME  -d----  PostgreSQL  PIGSTY  postgresql-18-pg-timeseries           Convenience API for time series stack
periods                       1.2.3    TIME  -ds---  PostgreSQL  PGDG    postgresql-18-periods                 Provide Standard SQL functionality for PERIODs and SYSTEM VERSIO
temporal_tables               1.2.2    TIME  -ds--r  BSD 2-Clause PIGSTY postgresql-18-temporal-tables         temporal tables
postgis                       3.6.1    GIS   -ds---  GPL-2.0     PGDG    postgresql-18-postgis-3               PostGIS geometry and geography spatial types and functions
postgis_topology              3.6.1    GIS   -ds---  GPL-2.0     PGDG    postgresql-18-postgis-3               PostGIS topology spatial types and functions
postgis_raster                3.6.1    GIS   -ds---  GPL-2.0     PGDG    postgresql-18-postgis-3               PostGIS raster types and functions
vector                        0.8.1    RAG   -ds--r  PostgreSQL  PGDG    postgresql-18-pgvector                vector data type and ivfflat and hnsw access methods
pg_duckdb                     1.1.0    OLAP  -dsl--  MIT         PIGSTY  postgresql-18-pg-duckdb               DuckDB Embedded in Postgres

If PostgreSQL cannot be found in your current PATH (via pg_config), it is recommended to explicitly specify PG major with -v|-p to avoid version detection ambiguity.

Scan Extensions

pig ext scan provides a lower-level scan. It scans shared libraries under the target PG directory to discover installed extensions:

$ pig ext scan

Installed:
- PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2)  398 Extensions

Active:
PG Version      :  PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2)
Config Path     :  /usr/bin/pg_config
Binary Path     :  /usr/lib/postgresql/18/bin
Library Path    :  /usr/lib/postgresql/18/lib
Extension Path  :  /usr/share/postgresql/18/extension

Name                 Version  SharedLibs                                       Description                       Meta
----                 -------  ----------                                       ---------------------             ------
timescaledb          2.25.1   Enables scalable inserts and complex queries...  module_pathname=$libdir/timescaledb-2.24.0 relocatable=false trusted=true lib=...
timescaledb_toolkit  1.22.0   Library of analytical hyperfunctions...          relocatable=false superuser=false module_pathname=$libdir/timescaledb_toolkit lib=...
periods              1.2      Provide Standard SQL functionality for PERIODs   module_pathname=$libdir/periods relocatable=false requires=btree_gist lib=periods.so
pg_cron              1.6      Job scheduler for PostgreSQL                     relocatable=false schema=pg_catalog module_pathname=$libdir/pg_cron lib=pg_cron.so
postgis              3.6.1    PostGIS geometry and geography spatial types...  module_pathname=$libdir/postgis-3 relocatable=false lib=postgis-3.so
vector               0.8.1    vector data type and ivfflat and hnsw access...  relocatable=true lib=vector.so
pg_duckdb            1.1.0    DuckDB Embedded in Postgres                      module_pathname=$libdir/pg_duckdb relocatable=false schema=public lib=...
...

Container Practice

You can create a new VM or use the following Docker container for testing. Create a d13 directory and a Dockerfile:

FROM debian:13
USER root
WORKDIR /root/
CMD ["/bin/bash"]

RUN apt update && apt install -y ca-certificates curl && curl https://repo.pigsty.io/pig | bash

docker build -t d13:latest .
docker run -it d13:latest /bin/bash

pig repo set --region=china    # add China region repos
pig install -y pg18            # install PGDG 18 kernel packages
pig install -y postgis timescaledb pgvector pg_duckdb

2 - Introduction

Why do we need yet another package manager? Especially for Postgres extensions?

Have you ever struggled with installing or upgrading PostgreSQL extensions? Digging through outdated documentation, cryptic configuration scripts, or searching GitHub for forks and patches? Postgres’s rich extension ecosystem also means complex deployment processes, especially across multiple distributions and architectures. PIG can solve these headaches for you.

This is exactly why Pig was created. Developed in Go, Pig is dedicated to one-stop management of Postgres and its 450+ extensions. Whether it’s TimescaleDB, Citus, PGVector, 30+ Rust extensions, or all the components needed to self-host Supabase, Pig’s unified CLI makes everything accessible. It completely eliminates source compilation and messy repositories, directly providing version-aligned RPM/DEB packages that perfectly support Debian, Ubuntu, RedHat, and other mainstream distributions on both x86 and Arm architectures, no guessing, no hassle.

Pig isn’t reinventing the wheel; it fully leverages native system package managers (APT, YUM, DNF) and strictly follows PGDG official packaging standards for seamless integration. You do not need to choose between “the standard way” and “shortcuts”. Pig respects existing repositories, follows OS best practices, and coexists harmoniously with existing repositories and packages. If your Linux system and PostgreSQL major version are not in the supported list, you can use pig build to compile extensions for your specific combination.

Want to supercharge your Postgres and escape the hassle? Visit the PIG official documentation for guides, and check out the extensive extension list, turning your local Postgres database into an all-capable multi-modal data platform with one click. If Postgres’s future is unmatched extensibility, then Pig is the magic lamp that helps you unlock it. After all, no one ever complains about “too many extensions”.

Automation-Friendly

PIG’s command system is automation-ready out of the box: consistent argument conventions, stable output behavior, and --dry-run or confirmation flows for high-risk operations to reduce mistakes.

ANNOUNCE pig: The Postgres Extension Wizard

Linux Compatibility

PIG and the Pigsty extension repository support the following Linux distribution and PostgreSQL version combinations:

OS CodeVendorMajorMinorFull NamePG VersionsNotes
el7.x86_64EL77.9CentOS 7 x8613-15EOL
el8.x86_64EL88.10RockyLinux 8 x8613-18Near EOL
el8.aarch64EL88.10RockyLinux 8 ARM13-18Near EOL
el9.x86_64EL99.7RockyLinux 9 x8613-18✅
el9.aarch64EL99.7RockyLinux 9 ARM13-18✅
el10.x86_64EL1010.1RockyLinux 10 x8613-18✅
el10.aarch64EL1010.1RockyLinux 10 ARM13-18✅
d11.x86_64Debian1111.11Debian 11 x8613-18EOL
d11.aarch64Debian1111.11Debian 11 ARM13-18EOL
d12.x86_64Debian1212.13Debian 12 x8613-18✅
d12.aarch64Debian1212.13Debian 12 ARM13-18✅
d13.x86_64Debian1313.3Debian 13 x8613-18✅
d13.aarch64Debian1313.3Debian 13 ARM13-18✅
u20.x86_64Ubuntu2020.04.6Ubuntu 20.04 x8613-18EOL
u20.aarch64Ubuntu2020.04.6Ubuntu 20.04 ARM13-18EOL
u22.x86_64Ubuntu2222.04.5Ubuntu 22.04 x8613-18✅
u22.aarch64Ubuntu2222.04.5Ubuntu 22.04 ARM13-18✅
u24.x86_64Ubuntu2424.04.3Ubuntu 24.04 x8613-18✅
u24.aarch64Ubuntu2424.04.3Ubuntu 24.04 ARM13-18✅

Notes:

  • EL refers to RHEL-compatible distributions, including RHEL, CentOS, RockyLinux, AlmaLinux, OracleLinux, etc.
  • EOL indicates the operating system has reached or is about to reach end of support; upgrading to a newer version is recommended
  • ✅ indicates full support; recommended for use
  • PG versions 13-18 means support for PostgreSQL 13, 14, 15, 16, 17, and 18 major versions

3 - Installation

How to download and install the pig package manager

Script Installation

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

Default Installation (Cloudflare CDN):

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

China Mirror:

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

This script downloads the latest pig RPM/DEB package from the Pigsty software repository and installs it using rpm or dpkg.

Specify Version

You can specify a particular version to install by passing the version number as an argument:

Default Installation (Cloudflare CDN):

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

China Mirror:

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

Download from Release Page

You can also download pig installation packages (RPM/DEB/tarball) directly from the Pigsty repository: GitHub v1.0.0 Stable Release Page

latest
└── v1.0.0
    ├── pig_1.0.0-1_amd64.deb
    ├── pig_1.0.0-1_arm64.deb
    ├── pig-1.0.0-1.aarch64.rpm
    ├── pig-1.0.0-1.x86_64.rpm
    ├── pig-v1.0.0.linux-amd64.tar.gz
    ├── pig-v1.0.0.linux-arm64.tar.gz
    ├── pig-v1.0.0.darwin-amd64.tar.gz
    └── pig-v1.0.0.darwin-arm64.tar.gz

After extracting, place the binary file in your system PATH.

Repository Installation

The pig software is located in the pigsty-infra repository. You can add this repository to your operating system and then install using the OS package manager:

YUM

For RHEL, RockyLinux, CentOS, Alma Linux, OracleLinux, and other EL distributions:

sudo tee /etc/yum.repos.d/pigsty-infra.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

APT

For Debian, Ubuntu, and other DEB distributions:

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

sudo apt update;
sudo apt install -y pig

Update

To upgrade an existing pig version to the latest available version, use the following command:

pig update            # Upgrade pig itself to the latest version

To update the extension data of an existing pig to the latest available version, use the following command:

pig ext reload        # Update pig extension data to the latest version

Uninstall

apt remove -y pig     # Debian / Ubuntu and other Debian-based systems
yum remove -y pig     # RHEL / CentOS / RockyLinux and other EL distributions
rm -rf /usr/bin/pig   # If installed directly from binary, just delete the binary file

Build from Source

You can also build pig yourself. pig is developed in Go and is very easy to build. The source code is hosted at github.com/pgsty/pig

git clone https://github.com/pgsty/pig.git; cd pig
go get -u; go build

All RPM/DEB packages are automatically built through GitHub CI/CD workflow using goreleaser.

4 - Release

pig — PostgreSQL Package Manager Release Notes

The latest stable version is v1.2.0.

VersionDateSummaryGitHub
v1.2.02026-02-23Unified aliases, routine updates, plan mode, repo fixesv1.2.0
v1.1.02026-02-12451 extensions, Agent-Native CLI frameworkv1.1.0
v1.0.02026-01-26New pg/pt/pb/pitr commands, availability matrixv1.0.0
v0.8.02025-12-26440 extensions, remove sysupdate repov0.8.0
v0.7.52025-12-12Routine extension update, fixed aliyun mirrorv0.7.5
v0.7.42025-12-01Update ivory/pgtde kernel and pgdg extrasv0.7.4
v0.7.32025-11-24Fix repo for el10 & debian13v0.7.3
v0.7.22025-11-20437 extensions, fix pig build issuev0.7.2
v0.7.12025-11-10New Website, improve in-docker experiencev0.7.1
v0.7.02025-11-05Build Enhancement and massive upgradev0.7.0
v0.6.22025-10-03PG 18 official Repov0.6.2
v0.6.12025-08-14CI/CD, el10 stub, PGDG CN Mirrorv0.6.1
v0.6.02025-07-17423 extension, percona pg_tde, mcp toolboxv0.6.0
v0.5.02025-06-30422 extension, new extension catalogv0.5.0
v0.4.22025-05-27421 extension, halo & oriole debv0.4.2
v0.4.12025-05-07414 extension, pg18 alias supportv0.4.1
v0.4.02025-05-01do & pt sub-cmd, halo & orioledbv0.4.0
v0.3.42025-04-05routine updatev0.3.4
v0.3.32025-03-25alias, repo, depsv0.3.3
v0.3.22025-03-21new extensionsv0.3.2
v0.3.12025-03-19minor bug fixv0.3.1
v0.3.02025-02-24new home page and extension catalogv0.3.0
v0.2.22025-02-22404 extensionsv0.2.2
v0.2.02025-02-14400 extensionsv0.2.0
v0.1.42025-02-12routine bugfixv0.1.4
v0.1.32025-01-23390 extensionsv0.1.3
v0.1.22025-01-12the anon extension and 350 other extv0.1.2
v0.1.12025-01-09Update Extension Listv0.1.1
v0.1.02024-12-29repo, ext, sty, and self-updatev0.1.0
v0.0.12024-12-23Genesis Releasev0.0.1

v1.2.0

  • Extension catalog and alias resolution enhancements:

    • Introduce dynamic PG category alias resolution by PG major version.
    • Add OS-level alias overrides (ansible/bootstrap) and converge unknown distro fallback to PGDG-only.
    • Add aliases such as node/infra and babelfish/cloudberry, and refresh extension metadata to reduce package resolution ambiguity.

  • Plan preview for high-risk operations:

    • Add pig install --plan with structured execution plan output.
    • Align preview semantics for pig pitr and pgBackRest repack/expire under --plan / --dry-run.
    • Add plan-flag consistency tests to keep subcommand behavior aligned.

  • Native sty configuration capability:

    • Add pig sty configure with full execution flow (preflight, argument handling, execution orchestration).
    • Unify sty conf/configure behavior: native implementation by default, with --raw fallback retained.
    • Add tests for configure main flow, preflight, routing, and install integration to improve maintainability.

  • Repo/build/reliability fixes:

    • Fix nil dereference in repo cache on os.Stat error paths.
    • Align Ubuntu and Debian repo channel mapping, and add timeout control for mirror pulls during reload.
    • Harden repo rm for dotted module names with safe deletion and path validation.
    • Fix symlink preservation, cross-device migration, and target-directory handling in sty init and build flows.
    • Improve text output and matrix color rendering, and fix ext command validation for empty args/targets.

  • 35 commits, 66 files changed, LOC: +5006 / -379

  • PG extension and kernel package updates

PackageOldNewNotes
timescaledb2.25.02.25.1
citus14.0.0-314.0.0-4Rebuilt from the latest official upstream release
age1.7.01.7.0Add PG 17 support for version 1.7.0
pg_background-1.8DEB-only build; RPM package comes from PGDG
pgmq1.10.01.10.1This extension package is currently unavailable
pg_search0.21.60.21.8Used as direct download package
oriolepg17.1117.16OriolePG kernel update
orioledbbeta12beta14Matched with OriolePG 17.16
cloudberry-2.0.0New package
babelfishpg-5.5.0New BabelfishPG package group
babelfish-5.5.0New Babelfish compatibility package
antlr4-runtime413-4.13New runtime dependency for Babelfish

Checksums

344b77385fa9c3d4fe5e1961340e68716251e38d1cb8308f5af45ce8a03cd206  pig-1.2.0-1.aarch64.rpm
aa9cf1820a9045cc42f0d66689d5e8679cb71452042f3f01ddd4c3a518a2b757  pig-1.2.0-1.x86_64.rpm
f26e4d9e9fa76c39f7c591c18a09287ca3388e016d121c196302ee9eafb5b678  pig-v1.2.0.darwin-amd64.tar.gz
2ca41efc3495822305f6e6a3ae1825d57cc97e764f280581f833c72e6e5019a2  pig-v1.2.0.darwin-arm64.tar.gz
f7aa291b3534d92d0459b6e8301190e39c63db14a45a6c097d4c5d3062c35181  pig-v1.2.0.linux-amd64.tar.gz
38007ecd6d7a69bae0e3d8f7c78f1a4c8bbaead320b7ac319b0d94d6b53853f0  pig-v1.2.0.linux-arm64.tar.gz
e824716ddfbf3805dc0a1fd6d97917241b7780503657e9fd40a37beb6b398d7a  pig_1.2.0-1_amd64.deb
b67baa404d877b37004331041cb270c85b8f9a3f8a92a5083390a54d76553d2a  pig_1.2.0-1_arm64.deb

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

v1.1.0

This version is a planned architecture-level upgrade from v1.0.0 to v1.1.0 (79 commits, 193 files changed), with the core goal of moving pig from a “human-friendly CLI” to an “agent-native orchestratable CLI”.

Seven new extensions are added, bringing the total available extensions to 451.

New Features

  • Land the unified agent-native output framework: introduce global --output (text/yaml/json/json-pretty), and provide unified Result structure, stable status codes, and machine-readable output for ext/repo/pg/pt/pb/pitr/status/version/context.
  • Introduce ANCS (Agent Native Command Schema) metadata: add semantic fields such as type/volatility/parallel/risk/confirm/os_user/cost, and make help emit a command capability tree directly in structured mode for agent-side capability and risk discovery.
  • Add pig context (pig ctx) environment snapshot command: aggregate host, PostgreSQL, Patroni, pgBackRest, and extension information in one call for direct agent workflow context injection.
  • Expand plan capabilities beyond PITR: add pig ext add/rm --plan, pig pg stop/restart --plan, pig pt switchover/failover --plan, and align with pig pitr --plan/--dry-run into a reviewable execution plan format (actions, scope, risks, expected outcomes).
  • Further improve structured result coverage: embed native pgbackrest info JSON, and unify structured return DTOs across Patroni/PostgreSQL/PITR/Repo/Ext subsystems for automation compatibility.
  • Strengthen compatibility layer: add legacy structured wrappers for existing command groups such as pg_exporter/pg_probe/do/sty, preserving legacy interaction behavior while exposing structured execution results and output capture.
  • Update pigsty to v4.1.0.

Extension Update

ExtensionOldNew
timescaledb2.24.02.25.0
citus14.0.0-214.0.0-3
pg_incremental1.2.01.4.1
pg_bigm1.2-202406061.2-20250903
pg_net0.20.00.20.2
pgmq1.9.01.10.0
pg_textsearch0.4.00.5.0
pljs1.0.41.0.5
sslutils1.4-11.4-2
table_version1.11.01.11.1
supautils3.0.23.1.0
pg_math1.01.1.0
pgsentinel1.3.11.4.0
pg_uri1.201512241.20251029
pgcollection1.1.01.1.1
pg_readonly1.0.31.0.4
timestamp91.4.0-11.4.0-2
pg_uint1281.1.11.2.0
pg_roaringbitmap0.5.51.1.0
plprql18.0.018.0.1
pglinter1.0.11.1.0
pg_jsonschema0.3.30.3.4
pg_anon2.5.13.0.1
vchord1.0.01.1.0
pg_search0.21.40.21.6/0.21.7
pg_graphql1.5.12-11.5.12-2
pg_summarize0.0.1-20.0.1-3
nominatim_fdw-1.1.0
pg_utl_smtp-1.0.0
pg_strict-1.0.2
pg_track_optimizer-0.9.1
pgmb-1.0.0

Bug Fixes

  • Security fix: resolve parsing panic in pig build proxy when receiving malformed proxy addresses.
  • Security fix: resolve path traversal risk in pig pg log, preventing access to files outside the log directory via ../../.
  • Security hardening: improve installer/repo path and quoting handling to reduce path injection and invalid-path misuse risks.
  • Build pipeline reliability fixes: correctly propagate errors and return non-zero exit codes in pig build get/pkg/ext when download/build fails; fix false failures in DEB builds caused by pg_ver mismatch.
  • Repo/catalog refresh fixes: support quiet mirror fallback for ext/repo reload; make repo add/set/rm return proper error status when cache updates fail.
  • Extension management fixes: adjust ext update to explicit-target updates and fix status drift issues; ensure ext import downloads requested DEB resources to the specified repo directory.
  • Output/observability fixes: align structured output exit code behavior with text mode rendering; improve permission handling and parsing stability in pg status.

Checksums

95245dc035270df2b02cdd5d19afac57ccf4949a61b07b1b806fffde3a3b780e  pig-1.1.0-1.aarch64.rpm
8b1a26f1b5dd002841a0b31904eea8ce94d1e6c4acde4704a78d9e121e1656f4  pig-1.1.0-1.x86_64.rpm
dbd079510513f1cd0521b0871cc6fe3eed8f7fa26f66c04c682568c43e24c456  pig-v1.1.0.darwin-amd64.tar.gz
3f3ba081b54569a7de4d9a8fce72c02c84d9e1cbeb53173567f970c7291af251  pig-v1.1.0.darwin-arm64.tar.gz
ad61384bf01cbb8346ce869da0bc893203ad316c516fb9420cb748f1519a005e  pig-v1.1.0.linux-amd64.tar.gz
7713632beea1e6ca5c3e2e7172c4adee13a2b1b256755f6c2898b6ca98ee1e00  pig-v1.1.0.linux-arm64.tar.gz
70cfc41b7b0aad48f29e12c22c34afd55b938bf50868ac8ab067b9cb62ccb867  pig_1.1.0-1_amd64.deb
fc5cf16671254f8f3495ff7e80c9d77d06b2328c1a247f90f96cf1e918e0ad0e  pig_1.1.0-1_arm64.deb

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

v1.0.0

This release introduces three major new subcommand groups (pig pg, pig pt, pig pb) for managing PostgreSQL, Patroni, and pgBackRest, along with an orchestrated PITR command and enhanced extension availability display.

New Commands

  • pig pg - PostgreSQL instance management

    • pg init/start/stop/restart/reload/status - Control and manage PostgreSQL instances
    • pg role/promote - Detect and switch instance role (primary/replica)
    • pg psql/ps/kill - Connection and session management
    • pg vacuum/analyze/freeze/repack - Database maintenance operations
    • pg log - Log viewing (list/tail/cat/less)

  • pig pt - Patroni cluster management

    • pt list/config - View cluster status and configuration
    • pt restart/reload/reinit - Manage cluster members
    • pt switchover/failover - Cluster failover operations
    • pt pause/resume - Control automatic failover
    • pt start/stop/status/log - Patroni service management

  • pig pb - pgBackRest backup management

    • pb info/ls - View backup information
    • pb backup/restore/expire - Backup operations
    • pb create/upgrade/delete - Stanza management
    • pb check/start/stop/log - Control operations

  • pig pitr - Orchestrated Point-In-Time Recovery

    • Automatic Patroni/PostgreSQL coordination
    • Multiple recovery targets: time, LSN, XID, restore point
    • Dry-run mode and post-recovery guidance

New Features

  • Add availability matrix to pig ext avail and pig ext ls

Improvements

  • Unified command aliases across pg/pt/pb commands
  • Standardized error message format
  • Code refactoring and cleanup

Bug Fixes

  • Fix missing UTIL extension category

Checksums

306637079e942bcac9ccbc089cd09a80051898f8db1630269bb1acd3fbdaa872  pig-1.0.0-1.aarch64.rpm
d2b9440410f00efbca174d63b507c39d97fc55f402d8e9290ee054c1b1c6414c  pig-1.0.0-1.x86_64.rpm
c8a169e48a8168ee03db508ca2edc22b56ecf6997bae924e9023796ab7ae4e62  pig-v1.0.0.darwin-amd64.tar.gz
c0996037bfeffeae241b545e69d46c06e7fec2d7d456885229f3af9a7f9ea2f8  pig-v1.0.0.darwin-arm64.tar.gz
13837c6f2379edf965888bad9e373e69f70cb72e8428bca18c2c804e2bd879f6  pig-v1.0.0.linux-amd64.tar.gz
08207dfedd6f72745631596a3d3293de65cc12e1544956a643d1da2165d2c876  pig-v1.0.0.linux-arm64.tar.gz
a543882aa905713a0c50088d4e848951b6957a37a1594d7e9f3fe46453d5ce66  pig_1.0.0-1_amd64.deb
4cd6ec54261b09025c12e9c56bcc0cd3c11779ea0e8becdbd4f901cf2e7c8995  pig_1.0.0-1_arm64.deb

Release: https://github.com/pgsty/pig/releases/tag/v1.0.0

v0.8.0

Extension Updates

  • Total extensions reached 440
  • New extension: pg_ai_query 0.1.1
  • New extension: pg_textsearch 0.1.0
  • New extension: pg_clickhouse 0.1.0
  • pg_biscuit upgraded from 1.0 to 2.0.1 (switched to new repo, renamed to biscuit)
  • pg_search upgraded from 0.20.3 to 0.20.5
  • pg_duckdb upgraded to official release 1.1.1
  • vchord_bm25 upgraded from 0.2.2 to 0.3.0
  • pg_semver upgraded from 0.40.0 to 0.41.0
  • pg_timeseries upgraded from 0.1.7 to 0.1.8
  • Fixed debian/ubuntu pg18 extension issues: supautils, pg_summarize, pg_vectorize, pg_tiktoken, pg_tzf, pglite_fusion, pgsmcrypto, pgx_ulid, plprql
  • Pigsty version synced to 4.0.0

Repository Updates

  • Removed pgdg yum sysupdate repo due to upstream changes
  • Removed pgdg yum llvmjit package due to upstream changes
  • Fixed patroni 3.0.4 duplicate package issue on el9.aarch64
  • Added priority for el repo definitions, docker repo skipped when unavailable
  • Added epel 10 / pgdg 9/10 OS minor version hotfix

Checksums

e457832fb290e2f9975bf719966dc36e650bdcbf8505d319c9e0431f4c03bc9e  pig-0.8.0-1.aarch64.rpm
c97b1bfdd7541f0f464cab0ecc273e65535c8dd2603c38d5cf8dccbf7e95b523  pig-0.8.0-1.x86_64.rpm
d892f06d3d3b440671529f40e6cc7949686e0167e2a4758adc666b8a3d75254d  pig-v0.8.0.darwin-amd64.tar.gz
222413bafdf5a62dc682dac32ea1118cbc34ec3544e2a1b85076ec450b9cc7ae  pig-v0.8.0.darwin-arm64.tar.gz
d50aa9806bbab8fee5ad9228e104fc9e7ead48729228116b5bf889000791fedc  pig-v0.8.0.linux-amd64.tar.gz
d2f410f7b243a8323c8d479f462a0267ac72d217aa4a506c80b5a9927d12dff8  pig-v0.8.0.linux-arm64.tar.gz
4ccd330a995911d4f732e8c9d62aa0db479c21c9596f64c4bc129ec43f156abe  pig_0.8.0-1_amd64.deb
5cb9eccce659110f3ba58e502575564bd6befffd51992a43d84df5a17f8eb8a0  pig_0.8.0-1_arm64.deb

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

v0.7.5

Extension Updates

  • timescaledb 2.23.1 -> 2.24.0
  • pg_search 0.20.0 -> 0.20.3
  • convert 0.0.4 -> 0.0.5
  • pglinter 1.0.0 -> 1.0.1
  • pgdd 0.6.0 -> 0.6.1
  • pg_session_jwt 0.3.3 -> 0.4.0
  • pg_anon 2.4.1 -> 2.5.1
  • pg_enigma 0.4.0 -> 0.5.0
  • wrappers 0.5.6 -> 0.5.7
  • pg_vectorize 0.25.0 -> 0.26.0

Repository Updates

Use the fixed Aliyun PGDG mirror repository

Checksums

9de11ac1404fc4100074113f2a5d50e4ec42c353b6e122a0b29edc17e53feca6  pig-0.7.5-1.aarch64.rpm
071d655580f1cc63b33d41a8fb49368556b7b5a276318f4bd772a6ab50e22b34  pig-0.7.5-1.x86_64.rpm
befe0a8f786e5243669ed7219acde8156d13d9adb0a5c2fb88ccf0f614a51f9b  pig-v0.7.5.darwin-amd64.tar.gz
4766b4e9ba390a32a7115e9f2dd6b65cf158439e28f9c099bab5c7f2e588bae2  pig-v0.7.5.darwin-arm64.tar.gz
dc45726c5e7fccd502cacaffc94c659570844151cdc279f2cac6500836071ade  pig-v0.7.5.linux-amd64.tar.gz
1483cf967d4bc9c12d4c6724567644d6b88fcd2a93aaf1d317fc6ad4e1672c13  pig-v0.7.5.linux-arm64.tar.gz
0152b7bd254eccadd640e563845abd9fa62efa68f11c6b67a5f9f0eebfa2d92e  pig_0.7.5-1_amd64.deb
7d22116d26ca09c5e2b8afbf086bb1acb1aea1148905efcc38944c18908fb105  pig_0.7.5-1_arm64.deb

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

v0.7.4

  • Update extension metadata: pg_search, pgmq, pg_stat_monitor
  • Update pgdg repo URL, the extras now move to parent directory
  • Bump ivorysql to 5.0 (compatible with PG 18.0)
  • Bump Percona Postgres TDE Kernel to 18.1

Checksums

5769b0051f04dcda22dd92b30b8effc8ddfa40097308bded76ce2b38d012ce57  pig-0.7.4-1.aarch64.rpm
d15c829fa2e3ce8dcd1adc063c107607b8e70f2cf747646aaa2fa257cdbf979c  pig-0.7.4-1.x86_64.rpm
bb4c90e253a3d470e50316e633a41e90ed2d4a5c5a1fd3a8dbb68ee87d831d47  pig-v0.7.4.darwin-amd64.tar.gz
faaf7ac7b08390f5048c081bb7a78100714387e35dc890e26d9746fc1caef415  pig-v0.7.4.darwin-arm64.tar.gz
037cacddd0dc1283f13dd2c9bace87ad7f2c74ffc245e629f1420be94bbf93df  pig-v0.7.4.linux-amd64.tar.gz
2ce819b2c3686cfb9f86790fdf61acd30bf7798bd6cd3c4f589df22e273dc867  pig-v0.7.4.linux-arm64.tar.gz
97f62d62f1cca61ce6d335efed88e3855d94ea2cd4ed941f2755fbac73931fcd  pig_0.7.4-1_amd64.deb
d2b80af89ed42601716f6b41eda3f8bee16db34023527df9deef8a43aa25a498  pig_0.7.4-1_arm64.deb

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

v0.7.3

  • Add new command: pig repo reload to update repo metadata
  • Fix EL PGDG sysupdate aarch64 repo issue (now aarch64 repo ready)
  • Fix EL10.aarch64 PGDG repo renaming issue
  • Update extension versions
  • Bump Pigsty version to 3.7.0

Checksums

786d72f6b685d6d6abf5f255f0a7de9204988a05630a26a53bfc7631823c0c6f  pig-0.7.3-1.aarch64.rpm
da59e24ef79d1164e348bacc43e3222e8e2778ec0e103e7ffc0c6df064758e8f  pig-0.7.3-1.x86_64.rpm
73062a979749095e89abc07dd583d34d4f57908bb4ee935cf7640f129ca6a2cb  pig-v0.7.3.darwin-amd64.tar.gz
ca5f5576f6d0d9be1d10cad769821be9daa62220b2fb56b94d6e4c0cede6da61  pig-v0.7.3.darwin-arm64.tar.gz
d193b4b87cf9a6e4775b1b07709802d30f0233ccb1b728843a09decb545168d3  pig-v0.7.3.linux-amd64.tar.gz
e7f612df0e8e4d9fac6df3765862b9e491bb50aad651856abf7a6935986e6f99  pig-v0.7.3.linux-arm64.tar.gz
3d5306ce95dcf704dd498b05325d942637564b13115f1e5a5bb9ef6781df1ba6  pig_0.7.3-1_amd64.deb
32e695ba2d49a741d8cd92008f8f2dec29f10754d35b732035f48517b382c30d  pig_0.7.3-1_arm64.deb

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

v0.7.2

  • Extension list update, + 6 new extensions, 437 total

  • Add PGDG EL10 Sysupdate repo

  • Add LLVM APT repo

  • Use local extension.csv catalog in pig build sub command

  • Updated extensions: vchord pg_later pgvectorscale pglite_fusion pgx_ulid pg_search citus timescaledb pg_profile pg_stat_monitor documentdb

  • New extensions: pglinter pg_typeid pg_enigma pg_retry pg_biscuit pg_weighted_statistics

Checksums

f303c391fc28bc74832712e0aa58319abe0ebcae4f6c07fdf9a9e542b735d2ec  pig-0.7.2-1.aarch64.rpm
c096a61a4e3a49b1238659664bbe2cd7f29954c43fb6bb8e8e9fb271f95a612e  pig-0.7.2-1.x86_64.rpm
5e037c891dff23b46856485108d6f64bede5216dfbd4f38a481f0d0672ee910b  pig-v0.7.2.darwin-amd64.tar.gz
736b4b47999c543c3c886781f4d8dddbf4276f363c35c7bf50094b6f18d14600  pig-v0.7.2.darwin-arm64.tar.gz
20b13f059efed29dd76f6927b3e8d7b597c0c8d734f9e22ba3d0a2af6dbcd3bf  pig-v0.7.2.linux-amd64.tar.gz
9548b530c05f2ffdc8d73b8f890718d47b74a51eb62852a99c08b1b52e47f014  pig-v0.7.2.linux-arm64.tar.gz
b6faad9f92b926546a10f590274f2cb2afff21b9cea878094cfc5caf09e67d2c  pig_0.7.2-1_amd64.deb
452f73f1fa035e5417ab49fc51d797925550179ffcc023e8f03d80144309212a  pig_0.7.2-1_arm64.deb

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

v0.7.1

  • The brand-new website: https://pgext.cloud
  • Remove unnecessary sudo usage, now can be used inside docker
  • Allow using pg18, pg17 arg format in pig ext link command
  • Add environment var PIG_NO_SUDO to force not using sudo
  • RPM Changelog: Add PG 18 support to almost all extensions
  • DEB Changelog: Add PG 18 support to almost all extensions
  • Infra Changelog: Routine update to the latest version

Checksums

a696c9ec784e2fc248e5f3d87cc8aae4116e890f78c5997957d30593f2c85ca6  pig-0.7.1-1.aarch64.rpm
f669538a99cd1dc592d3005b949628fcceb9e78114fc78862d7726b340ee194d  pig-0.7.1-1.x86_64.rpm
e42bdaaf93b720c5b76b32b57362320e4b447109740c76089aefe030b7c8b836  pig-v0.7.1.darwin-amd64.tar.gz
b4c240aadad34e785666ee0a755d9b7455724f790c2d088a1dd7c37ad3b2a457  pig-v0.7.1.darwin-arm64.tar.gz
ffc687add0ca71ac90cba5749c8a7a6075cf7618cba85584072831cf3eb182f7  pig-v0.7.1.linux-amd64.tar.gz
7b0d1f158150d0a40c525692f02b6bce9f5b4ac523a4e59278d702c334e222e1  pig-v0.7.1.linux-arm64.tar.gz
43e91a3bea273d7cacb2d7a58c0a5745501dbd06348b5cb3af971171fae70268  pig_0.7.1-1_amd64.deb
fc2a34aeb46e07cb0ae93611de47d6622c3bd46fe4c415ce4c9091840e0e08a2  pig_0.7.1-1_arm64.deb

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

v0.7.0

  • Add support for Debian 13 and EL 10 distributions
  • Massive extension updates to the latest versions with PostgreSQL 18 support
  • Almost all Rust extensions now support PG 18 via pgrx 0.16.1
  • pig build command overhaul
    • pig build pkg <pkg> will now download source, prepare deps, and build in one go
    • pig build pgrx is now separated from pig build rust
    • pig build pgrx [-v pgrx_version] can now use existing PG installation directly
    • pig build dep will now handle extension dependencies on both EL and Debian systems
    • pig build ext now has more compact and elegant output, can build RPM on EL without build script
    • pig build spec now supports downloading spec files directly from Pigsty repo
    • pig build repo / pig repo add / pig repo set now use node,pgsql,infra as default repo modules instead of node,pgdg,pigsty
  • Optimized error logging
  • Brand new catalog website based on hugo and hextra

Checksums

ad60f9abcde954769e46eb23de61965e  pig_0.7.0-1_amd64.deb
aa15d7088d561528e38b2778fe8f7cf9  pig_0.7.0-1_arm64.deb
05549fe01008e04f8d5a59d4f2a5f0b8  pig-0.7.0-1.aarch64.rpm
0cc9e46c7c72d43c127a6ad115873b67  pig-0.7.0-1.x86_64.rpm
ddacfb052f3f3e5567a02e92fdb31cdd  pig-v0.7.0.darwin-amd64.tar.gz
17d25b565308d3d35513e4b0d824946b  pig-v0.7.0.darwin-arm64.tar.gz
ee7e055ceff638039956765fb747f80b  pig-v0.7.0.linux-amd64.tar.gz
284e674807b87447d4b33691fd7a420d  pig-v0.7.0.linux-arm64.tar.gz

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

v0.6.2

  • Use official PG 18 repo instead of testing repo
  • Add v prefix when specifying pigsty version string
  • Improved network connectivity check

Checksums

01f5b7dc20644226c762dbb229768347  pig_0.6.2-1_amd64.deb
ce4f00256adc12cbea91467b7f2241cd  pig_0.6.2-1_arm64.deb
cefc36ae8f348aede533b30836fba720  pig-0.6.2-1.aarch64.rpm
d04a287c6eb92b11ecbf99542c2db602  pig-0.6.2-1.x86_64.rpm
e637ca86a7f38866c67686b060223d9a  pig-v0.6.2.darwin-amd64.tar.gz
79749bc69c683586bd8d761bdf6af98e  pig-v0.6.2.darwin-arm64.tar.gz
ad4f02993c7d7d8eec142f0224551bb4  pig-v0.6.2.linux-amd64.tar.gz
9793affa4a0cb60e9753e65b7cba3dca  pig-v0.6.2.linux-arm64.tar.gz

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

v0.6.1

  • Add el10 and debian 13 trixie support stub
  • Dedicated website: https://pgext.cloud/pig
  • Rebuild with go 1.25 and CI/CD pipeline
  • Use PIGSTY PGDG mirror in mainland China
  • Remove unused pgdg-el10fix repo
  • Use Pigsty WiltonDB mirror
  • Add EL 10 dedicated epel repo
  • pig version output with go build environment

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

v0.6.0

  • New extension catalog: https://ext.pgsty.com
  • New subcommand: pig install to simplify pig ext install
  • Add new kernel support: percona with pg_tde
  • Add new package: Google GenAI MCP toolbox for databases
  • Add new repo: percona repo and clickhouse repo
  • Change extension summary info links to https://ext.pgsty.com
  • Fix orioledb broken on the Debian/Ubuntu system
  • Fix epel repo on EL distributions
  • Bump golang to 1.24.5
  • Bump pigsty to v3.6.0

Checksums

1804766d235b9267701a08f95903bc3b  pig_0.6.0-1_amd64.deb
35f4efa35c1eaecdd12aa680d29eadcb  pig_0.6.0-1_arm64.deb
b523b54d9f2d7dcc5999bcc6bd046b1d  pig-0.6.0-1.aarch64.rpm
9434d9dca7fd9725ea574c5fae1a7f52  pig-0.6.0-1.x86_64.rpm
f635c12d9ad46a779aa7174552977d11  pig-v0.6.0.linux-amd64.tar.gz
165af4e63ec0031d303fe8b6c35c5732  pig-v0.6.0.linux-arm64.tar.gz

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

v0.5.0

  • Update the extension list to 422
  • New extension: pgactive from AWS
  • Bump timescaledb to 2.20.3
  • Bump citus to 13.1.0
  • Bump vchord to 0.4.3
  • Bug fix pgvectorscale debian/ubuntu pg17 failure
  • Bump kubernetes repo to 1.33
  • Bump default pigsty version to 3.5.0

Checksums

9ec6f3caf3edbe867caab5de0e0ccb33  pig_0.5.0-1_amd64.deb
4fbb0a42cd8a88bce50b3c9d85745d77  pig_0.5.0-1_arm64.deb
9cf8208396b068cab438f72c90d39efe  pig-0.5.0-1.aarch64.rpm
d9a8d78c30f45e098b29c3d16471aa8d  pig-0.5.0-1.x86_64.rpm
761df804ff7b83965c41492700717674  pig-v0.5.0.linux-amd64.tar.gz
5d1830069d98030728f08835f883ea39  pig-v0.5.0.linux-arm64.tar.gz

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

v0.4.2

  • Update the extension list to 421
  • Add openhalo/orioledb support for Debian / Ubuntu
  • pgdd 0.6.0 (pgrx 0.14.1)
  • convert 0.0.4 (pgrx 0.14.1)
  • pg_idkit 0.3.0 (pgrx 0.14.1)
  • pg_tokenizer.rs 0.1.0 (pgrx 0.13.1)
  • pg_render 0.1.2 (pgrx 0.12.8)
  • pgx_ulid 0.2.0 (pgrx 0.12.7)
  • pg_ivm 1.11.0 for debian/ubuntu
  • orioledb 1.4.0 beta11
  • Add el7 repo back

Checksums

bbf83fa3e3ec9a4dca82eeed921ae90a  pig_0.4.2-1_amd64.deb
e45753335faf80a70d4f2ef1d3100d72  pig_0.4.2-1_arm64.deb
966d60bbc2025ba9cc53393011605f9f  pig-0.4.2-1.aarch64.rpm
1f31f54da144f10039fa026b7b6e75ad  pig-0.4.2-1.x86_64.rpm
1eec26c4e69b40921e209bcaa4fe257a  pig-v0.4.2.linux-amd64.tar.gz
768d43441917a3625c462ce9f2b9d4ef  pig-v0.4.2.linux-arm64.tar.gz

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

v0.4.1

  • Update the extension list to 414
  • Add citus_wal2json and citus_pgoutput to pig ext scan mapping
  • Add PG 18 beta repo
  • Add PG 18 package alias

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

v0.4.0

  • 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

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 the repo list

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 installing extensions in el7
  • Update package alias

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

v0.3.2

Enhancement

  • New extensions
  • Use upx to reduce binary size
  • Remove embedded pigsty to reduce binary size

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

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

v0.3.0

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

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

v0.2.2

404 Extensions Available in Pig v0.2.2

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

v0.2.0

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

v0.1.4

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

v0.1.3

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

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

v0.1.2

351 PostgreSQL Extensions, including the powerful postgresql-anonymizer 2.0

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

v0.1.0

pig CLI v0.1 released

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

v0.0.1

Genesis Release

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

5 - Command Reference

6 - pig

pig CLI command reference overview

pig CLI provides comprehensive tools for managing PostgreSQL installations, extensions, repositories, and building extensions from source. Check command documentation with pig help <command>.

  • pig repo: Manage software repositories
  • pig ext: Manage PostgreSQL extensions
  • pig build: Build extensions from source
  • pig sty: Manage Pigsty installation
  • pig pg: Manage local PostgreSQL server
  • pig pt: Manage Patroni HA cluster
  • pig pb: Manage pgBackRest backup & restore

Overview

pig - The Linux package manager for PostgreSQL

Usage:
  pig [command]

Examples:

  pig repo add -ru            # overwrite existing repo & update cache
  pig install pg18            # install PostgreSQL 18 PGDG package
  pig install pg_duckdb       # install a PostgreSQL extension
  pig install pgactive -v 18  # install extension for specific PG major

  visit https://pgext.cloud for details

PostgreSQL Extension Manager
  build       Build Postgres extensions
  ext         Manage PostgreSQL extensions (pgext)
  repo        Manage Linux software repo (apt/dnf)

Pigsty Management Commands
  do          Run admin tasks
  patroni     Manage Patroni cluster
  pg_exporter Manage pg_exporter and metrics
  pgbackrest  Manage pgBackRest backup and restore
  pitr        Orchestrated PITR
  postgres    Manage local PostgreSQL server and databases
  sty         Manage Pigsty installation

Additional Commands:
  completion  Generate shell completion scripts
  help        Help about any command
  install     Install packages using native package manager
  status      Show environment status
  update      Upgrade pig itself
  version     Show pig version info

Flags:
      --debug              enable debug mode
  -h, --help               help for pig
  -H, --home string        Pigsty home path
  -i, --inventory string   config inventory path
  -t, --toggle             placeholder flag shown in help output
      --log-level string   log level: debug, info, warn, error, fatal, panic (default "info")
      --log-path string    log file path, terminal by default

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

pig repo

Manage APT/YUM repositories for PostgreSQL packages. See pig repo for details.

pig repo list                    # list available repositories
pig repo info   pgdg             # show repository details
pig repo status                  # check current repo status
pig repo add    pgdg pigsty -u   # add repositories
pig repo rm     old-repo         # remove repositories
pig repo update                  # update package cache
pig repo create /www/pigsty      # create local repository
pig repo cache                   # create offline package
pig repo boot                    # bootstrap from offline package

pig ext

Manage PostgreSQL extensions and kernel packages. See pig ext for details.

pig ext list    duck             # search extensions
pig ext info    pg_duckdb        # extension details
pig ext status                   # show installed extensions
pig ext add     pg_duckdb -y     # install extension
pig ext rm      old_extension    # remove extension
pig ext update                   # update extensions
pig ext scan                     # scan installed extensions
pig ext import  pg_duckdb        # download for offline use
pig ext link    18               # link PG version to PATH
pig ext reload                   # refresh extension catalog

pig build

Build PostgreSQL extensions from source. See pig build for details.

# environment setup
pig build spec                   # initialize build specs
pig build repo                   # setup repositories
pig build tool                   # install build tools
pig build rust -y                # force reinstall Rust (default does not reinstall)
pig build pgrx                   # install PGRX framework

# build extensions
pig build pkg citus              # complete build pipeline = get + dep + ext
pig build get citus              # download source
pig build dep citus              # install dependencies
pig build ext citus              # build package

pig sty

Install Pigsty distribution. See pig sty for details.

pig sty init                     # install Pigsty to ~/pigsty
pig sty boot                     # install Ansible prerequisites
pig sty conf                     # generate configuration
pig sty deploy                   # run deployment playbook

pig pg

Manage local PostgreSQL server. See pig pg for details.

pig pg init                      # initialize data directory
pig pg start                     # start PostgreSQL
pig pg stop                      # stop PostgreSQL
pig pg status                    # check status
pig pg psql mydb                 # connect to database
pig pg ps                        # show current connections
pig pg vacuum mydb               # vacuum database
pig pg log tail                  # real-time log viewing

pig pt

Manage Patroni HA cluster. See pig pt for details.

pig pt list                      # list cluster members
pig pt config                    # show cluster config
pig pt config ttl=60             # modify cluster config
pig pt status                    # check service status
pig pt log -f                    # real-time log viewing

pig pb

Manage pgBackRest backup & recovery. See pig pb for details.

pig pb info                      # show backup info
pig pb ls                        # list all backups
pig pb backup                    # create backup
pig pb backup full               # full backup
pig pb restore -d                # restore to latest
pig pb restore -t "2025-01-01"   # restore to specific time
pig pb log tail                  # real-time log viewing

pig pitr

Orchestrated Point-In-Time Recovery. See pig pitr for details.

pig pitr -d                      # recover to latest (most common)
pig pitr -t "2025-01-01 12:00"   # recover to specific time
pig pitr -I                      # recover to backup consistency point
pig pitr -d --dry-run            # show execution plan without running
pig pitr -d -y                   # skip confirmation (for automation)
pig pitr -d --skip-patroni       # skip Patroni management
pig pitr -d --no-restart         # don't auto-start PostgreSQL after restore

7 - pig repo

Manage software repositories with pig repo subcommand

The pig repo command is a comprehensive tool for managing package repositories on Linux systems. It provides functionality to add, remove, create, and manage software repositories for both RPM-based (RHEL/CentOS/Rocky/Alma) and Debian-based (Debian/Ubuntu) distributions.

pig repo - Manage Linux software repo (apt/dnf)

Usage: pig repo <command>

Commands:
  add       Add new repository
  set       Wipe and overwrite and update repository
  rm        Remove repository
  list      Print available repo and module list
  info      Get repo detailed information
  status    Show current repo status
  update    Update repo cache
  create    Create local YUM/APT repository
  cache     Create offline package from local repo
  boot      Bootstrap repo from offline package
  reload    Refresh repo catalog

Flags:
  -h, --help   help for repo

Global Flags:
      --debug              enable debug mode
  -H, --home string        pigsty home path
  -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 repo [command] --help" for more information about a command.
CommandDescriptionNotes
repo listPrint available repo and module list
repo infoGet repo detailed information
repo statusShow current repo status
repo addAdd new repositoryRequires sudo or root
repo setWipe, overwrite, and update repositoryRequires sudo or root
repo rmRemove repositoryRequires sudo or root
repo updateUpdate repo cacheRequires sudo or root
repo createCreate local YUM/APT repositoryRequires sudo or root
repo cacheCreate offline package from local repoRequires sudo or root
repo bootBootstrap repo from offline packageRequires sudo or root
repo reloadRefresh repo catalog

Quick Start

# Method 1: Clean existing repos, add all necessary repos and update cache (recommended)
pig repo add all --remove --update    # Remove old repos, add all essentials, update cache

# Method 1 variant: One-step
pig repo set                          # = pig repo add all --remove --update

# Method 2: Gentle approach - only add required repos, keep existing config
pig repo add pgsql                    # Add PGDG and Pigsty repos with cache update
pig repo add pigsty --region=china    # Add Pigsty repo, specify China region
pig repo add pgdg   --region=europe   # Add PGDG repo, specify Europe region
pig repo add infra  --region=default  # Add INFRA repo, specify default region

# If no -u|--update option above, run this command additionally
pig repo update                       # Update system package cache

Modules

In pig, APT/YUM repositories are organized into modules — groups of repositories serving a specific purpose.

ModuleDescriptionRepository List
allAll core modules required for PG installnode + infra + pgsql
pgsqlPGDG + Pigsty PG extensionspigsty-pgsql + pgdg
pigstyPigsty Infra + PGSQL repospigsty-infra, pigsty-pgsql
pgdgPGDG official repositoriespgdg-common, pgdg13-18
nodeLinux system repositoriesbase, updates, extras, epel, baseos, appstream…
infraInfrastructure component repositoriespigsty-infra, nginx, docker-ce
dockerDocker repositorydocker-ce
betaPostgreSQL 19 beta repositoriespgdg19-beta, pgdg-beta
extraPGDG non-free and third-party extensionspgdg-extras, timescaledb, citus
groongaPGroonga repositorygroonga
mssqlWiltonDB repositorywiltondb
perconaPercona PG + PG_TDE repositorypercona
llvmLLVM toolchain repositoryllvm
kubeKubernetes repositorykubernetes
grafanaGrafana repositorygrafana
haproxyHAProxy repositoryhaproxyd, haproxyu
redisRedis repositoryredis
mongoMongoDB repositorymongo
mysqlMySQL repositorymysql
clickClickHouse repositoryclickhouse
gitlabGitLab repositorygitlab-ce, gitlab-ee

repo add

Add repository configuration files to the system. Requires root/sudo privileges.

pig repo add pgdg                # Add PGDG repository
pig repo add pgdg pigsty         # Add multiple repositories
pig repo add all                 # Add all essential repos (pgdg + pigsty + node)
pig repo add pigsty -u           # Add and update cache
pig repo add all -r              # Remove existing repos before adding
pig repo add all -ru             # Remove, add, and update (complete reset)
pig repo add pgdg --region=china # Use China mirrors

Options:

  • -r|--remove: Remove existing repos before adding new ones
  • -u|--update: Run package cache update after adding repos
  • --region <region>: Use regional mirror repositories (default / china / europe)

repo set

Equivalent to repo add --remove --update. Wipes existing repositories and sets up new ones, then updates cache.

pig repo set                     # Replace with default repos
pig repo set pgdg pigsty         # Replace with specific repos and update
pig repo set all --region=china  # Use China mirrors

repo rm

Remove repository configuration files and back them up.

pig repo rm                      # Remove all repos
pig repo rm pgdg                 # Remove specific repo
pig repo rm pgdg pigsty -u       # Remove and update cache

repo update

Update package manager cache to reflect repository changes.

pig repo update                  # Update package cache
PlatformEquivalent Command
ELdnf makecache
Debianapt update

repo create

Create local package repository for offline installations.

pig repo create                  # Create at default location (/www/pigsty)
pig repo create /srv/repo        # Create at custom location

repo cache

Create compressed tarball of repository contents for offline distribution.

pig repo cache                   # Default: /www to /tmp/pkg.tgz
pig repo cache -d /srv           # Custom source directory

repo boot

Extract and set up local repository from offline package.

pig repo boot                    # Default: /tmp/pkg.tgz to /www
pig repo boot -p /mnt/pkg.tgz   # Custom package path
pig repo boot -d /srv           # Custom target directory

repo reload

Refresh repo metadata from GitHub to latest version.

pig repo reload                  # Refresh repo catalog

The updated file is placed in ~/.pig/repo.yml.

Common Scenarios

Scenario 1: Fresh PostgreSQL Installation

# Setup repositories
sudo pig repo add -ru

# Install PostgreSQL 18
sudo pig ext install pg18

# Install popular extensions
sudo pig ext add pg_duckdb postgis timescaledb

Scenario 2: Air-gapped Environment

# On internet-connected machine:
sudo pig repo add -ru
sudo pig ext install pg18
sudo pig ext add pg_duckdb postgis
sudo pig repo create
sudo pig repo cache

# Transfer /tmp/pkg.tgz to air-gapped machine

# On air-gapped machine:
sudo pig repo boot
sudo pig repo add local
sudo pig ext install pg18
sudo pig ext add pg_duckdb postgis

Scenario 3: Using Regional Mirrors

# For users in China
sudo pig repo add all --region=china -u

# Check mirror URLs
pig repo info pgdg

8 - pig ext

Manage PostgreSQL extensions 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.

pig ext - Manage PostgreSQL Extensions

Usage: pig ext <command>

Commands:
  add       Install extension for PostgreSQL
  avail     Show extension availability matrix
  info      Get extension information
  link      Link PostgreSQL version to PATH
  list      List & Search PostgreSQL extensions
  reload    Refresh extension catalog
  rm        Remove extension from PostgreSQL
  scan      Scan installed PostgreSQL extensions
  status    Show installed PostgreSQL extensions
  update    Update extension for PostgreSQL

Aliases:
  add, install, ins, get
  rm, remove, del, uninstall

Flags:
  -h, --help               help for ext
  -p, --pgconfig string    pg_config path
  -v, --version int        pg major version

Global Flags:
      --debug              enable debug mode
  -H, --home string        pigsty home path
  -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.
CommandDescriptionNotes
ext listSearch extensions
ext infoShow extension details
ext availShow extension availability matrix
ext statusShow installed extensions
ext scanScan installed extensions
ext addInstall extensionsRequires sudo or root
ext rmRemove extensionsRequires sudo or root
ext updateUpdate extensionsRequires sudo or root
ext importDownload for offline useRequires sudo or root
ext linkLink PG version to PATHRequires sudo or root
ext reloadRefresh extension catalog

Quick Start

pig ext list                     # List all extensions
pig ext list duck                # Search for "duck" extensions
pig ext info pg_duckdb           # Show pg_duckdb extension info
pig install pg_duckdb            # Install pg_duckdb extension
pig install pg_duckdb -v 18      # Install pg_duckdb for PG 18
pig ext status                   # Show installed extensions

ext list

List or search extensions.

pig ext list                     # List all extensions
pig ext list duck                # Search for "duck" extensions
pig ext list -v 18               # Filter by PG version
pig ext ls olap                  # List OLAP category extensions
pig ext ls gis -v 16             # List GIS extensions for PG 16
pig ext ls rag                   # List RAG category extensions

Category filter is achieved by specifying the category name directly as query parameter. Supported categories: time, gis, rag, fts, olap, feat, lang, type, func, util, admin, stat, sec, fdw, sim, etl.

Options:

  • -v|--version: Filter by PG version
  • --pkg: Show package names instead of extension names, list leading extensions only

ext info

Display detailed information about specific extensions.

pig ext info pg_duckdb           # Show pg_duckdb info
pig ext info vector postgis      # Show info for multiple extensions

ext avail

Display the availability matrix for extensions, showing availability across different operating systems, architectures, and PostgreSQL versions.

pig ext avail                     # Show availability for all packages on current system
pig ext avail timescaledb         # Show availability matrix for timescaledb
pig ext avail postgis pg_duckdb   # Show availability for multiple extensions
pig ext av pgvector               # Show availability for pgvector
pig ext matrix citus              # Alias for avail command

The availability matrix shows extension availability across operating systems (EL8/9/10, Debian 12/13, Ubuntu 22/24), architectures (x86_64/aarch64), and PostgreSQL versions (13-18).

ext status

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

pig ext status                   # Show installed extensions
pig ext status -v 18             # Show installed extensions for PG 18

ext add

Install extensions. Also available via alias pig install.

pig ext add pg_duckdb            # Install pg_duckdb
pig ext add pg_duckdb -v 18      # Install for PG 18
pig ext add pg_duckdb -y         # Auto-confirm installation
pig ext add vector postgis       # Install multiple extensions

# Using alias
pig install pg_duckdb
pig install pg_duckdb -v 18 -y

Options:

  • -v|--version: Specify PG major version
  • -y|--yes: Auto-confirm installation

ext rm

Remove extensions.

pig ext rm pg_duckdb             # Remove pg_duckdb
pig ext rm pg_duckdb -v 18       # Remove for PG 18

ext update

Update installed extensions.

pig ext update                   # Update all extensions
pig ext update pg_duckdb         # Update specific extension

ext scan

Scan installed PostgreSQL installations and their extensions.

pig ext scan                     # Scan all installed PG versions
pig ext scan -v 18               # Scan PG 18

ext import

Download extension packages for offline use.

pig ext import pg_duckdb         # Download pg_duckdb
pig ext import pg_duckdb -v 18   # Download for PG 18

Link a specific PG version to the system PATH.

pig ext link 18                  # Link PG 18 to PATH

This command creates a /usr/pgsql symlink and writes to /etc/profile.d/pgsql.sh.

ext reload

Refresh extension metadata from GitHub.

pig ext reload                   # Refresh extension catalog

The updated file is placed in ~/.pig/extension.csv.

Examples

To install PostgreSQL extensions, you’ll have to add the repo first:

pig repo add pgdg pigsty -u    # gentle way to add pgdg and pigsty repo
pig repo set                   # 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.

Notes:

  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
    • 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 pg18          # install PostgreSQL 18 kernels (all but devel)

9 - pig build

Build PostgreSQL extensions from source with pig build subcommand

The pig build command is a powerful tool that simplifies the entire workflow of building PostgreSQL extensions from source. It provides a complete build infrastructure setup, dependency management, and compilation environment for both standard and custom PostgreSQL extensions across different operating systems.

pig build - Build Postgres Extension from source

Usage: pig build <command>

Commands:
  dep       Install extension build dependencies
  ext       Build extension package
  get       Download source code tarball
  pgrx      Install pgrx
  pkg       Complete build pipeline: get, dep, ext
  proxy     Init build proxy
  repo      Init build repo (=repo set, with remove+update)
  rust      Install rust
  spec      Init building spec repo
  tool      Init build tools

Flags:
  -h, --help            help for build
  -v, --version int     pg major version
  -y, --yes             auto confirm

Global Flags:
      --debug              enable debug mode
  -H, --home string        pigsty home path
  -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 build [command] --help" for more information about a command.

Subcommands

CommandDescriptionNotes
build specInitialize building spec repo
build repoInitialize required reposRequires sudo or root
build toolInitialize build toolsRequires sudo or root
build rustInstall Rust toolchainRequires sudo or root
build pgrxInstall and initialize pgrxRequires sudo or root
build proxyInitialize build proxy
build getDownload source code tarball
build depInstall extension build dependenciesRequires sudo or root
build extBuild extension packageRequires sudo or root
build pkgComplete build pipeline: get, dep, extRequires sudo or root

Quick Start

# Setup build environment
pig build spec                   # Initialize build specs
pig build repo                   # Setup repositories
pig build tool                   # Install build tools

# Install Rust (for Rust extensions)
pig build rust -y                # Force reinstall Rust (default does not reinstall)
pig build pgrx                   # Install PGRX framework

# Build extensions
pig build pkg citus              # Complete build pipeline

build spec

Initialize build specification files.

pig build spec                   # Create default spec files
pig build spec -f                # Force overwrite existing files

build repo

Setup repositories required for building.

pig build repo                   # Equivalent to: pig repo set (internally remove+update)

build tool

Install build toolchain (gcc, make, cmake, etc.).

pig build tool                   # Install build tools
pig build tool -y                # Auto-confirm

build rust

Install Rust toolchain (for building Rust extensions).

pig build rust                   # Install Rust
pig build rust -y                # Force reinstall Rust toolchain

build pgrx

Install PGRX framework (for building PGRX extensions).

pig build pgrx                   # Install PGRX
pig build pgrx -v 18             # Install for PG 18

build proxy

Setup proxy configuration for build environments with restricted internet access.

pig build proxy                  # Interactive setup
pig build proxy user@host:8080   # Direct configuration
pig build proxy http://proxy.company.com:3128

build get

Complete build pipeline: download source, install dependencies, build extension.

pig build pkg citus              # Build citus
pig build pkg citus -v 18        # Build for PG 18
pig build pkg citus -y           # Auto-confirm

build get

Download extension source code.

pig build get citus              # Download citus source

build dep

Install extension build dependencies.

pig build dep citus              # Install citus dependencies
pig build dep citus -y           # Auto-confirm

build ext

Build extension package from source.

pig build ext citus              # Build citus
pig build ext citus -v 18        # Build for PG 18

Build Infrastructure

Build Specifications

The build system uses specification files that define how each extension should be built. These specs include:

  • Source code location and version
  • Build dependencies
  • Compilation flags
  • PostgreSQL version compatibility
  • Platform-specific build instructions

Directory Structure

~/ext/                           # Default build spec directory
├── Makefile                     # Master build makefile
├── <extension>/                 # Per-extension directory
│   ├── Makefile                # Extension-specific makefile
│   ├── <extension>.spec        # RPM spec file (EL)
│   └── debian/                 # Debian packaging files
│       ├── control
│       ├── rules
│       └── ...

Build output locations:

  • EL Systems: ~/rpmbuild/RPMS/<arch>/
  • Debian Systems: ~/ (deb files)

Common Workflows

Workflow 1: Building Standard Extension

# 1. Setup build environment (once)
pig build spec
pig build repo
pig build tool

# 2. Build extension
pig build pkg pg_partman

# 3. Install built package
sudo rpm -ivh ~/rpmbuild/RPMS/x86_64/pg_partman*.rpm  # EL
sudo dpkg -i ~/pg_partman*.deb                         # Debian

Workflow 2: Building Rust Extension

# 1. Setup Rust environment
pig build spec
pig build tool
pig build rust                   # add -y only if you need to force reinstall
pig build pgrx

# 2. Build Rust extension
pig build pkg pgmq

# 3. Install
sudo pig ext add pgmq

Workflow 3: Building Multiple Versions

# Build extension for multiple PostgreSQL versions
pig build pkg citus --pg 16,17,18

# Results in packages for each version:
# citus_16-*.rpm
# citus_17-*.rpm
# citus_18-*.rpm

Troubleshooting

Build Tools Not Found

# Install build tools
pig build tool

# For specific compiler
sudo dnf groupinstall "Development Tools"  # EL
sudo apt install build-essential          # Debian

Missing Dependencies

# Install extension dependencies
pig build dep <extension>

# Check error messages for specific packages
# Install manually if needed
sudo dnf install <package>  # EL
sudo apt install <package>  # Debian

PostgreSQL Headers Not Found

# Install PostgreSQL development package
sudo pig ext install pg18-devel

# Or specify pg_config path
export PG_CONFIG=/usr/pgsql-18/bin/pg_config

Rust/PGRX Issues

# Reinstall Rust
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# Update PGRX
cargo install cargo-pgrx --force

# Reinitialize PGRX
cargo pgrx init

10 - pig sty

Manage Pigsty installation with pig sty subcommand

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

pig sty - Init (Download), Bootstrap, Configure, and Deploy Pigsty

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

Examples:
  pig sty init                 # extract and init ~/pigsty
  pig sty boot                 # install ansible & other deps
  pig sty conf                 # generate pigsty.yml config file
  pig sty deploy               # run the deploy.yml playbook
CommandDescriptionNotes
sty initInstall Pigsty
sty bootInstall Ansible prerequisitesRequires sudo or root
sty confGenerate configuration
sty deployRun deployment playbook
sty listList available Pigsty versions
sty getDownload Pigsty source tarball

Quick Start

pig sty init                     # Install Pigsty to ~/pigsty
pig sty boot                     # Install Ansible prerequisites
pig sty conf                     # Generate configuration
pig sty deploy                   # Run deployment playbook

sty init

Download and install Pigsty distribution to ~/pigsty directory.

pig sty init                     # Install latest Pigsty
pig sty init -v 3.5.0            # Install specific version
pig sty init -d /opt/pigsty      # Install to specific directory

Options:

  • -v|--version: Specify Pigsty version
  • -d|--dir: Specify installation directory
  • -f|--force: Overwrite existing pigsty directory

sty boot

Install Ansible and its dependencies.

pig sty boot                     # Install Ansible
pig sty boot -r china            # Use China region mirrors

Options:

  • -r|--region: Upstream repo region (default, china, europe)
  • -k|--keep: Keep existing upstream repo during bootstrap

sty conf

Generate Pigsty configuration file.

pig sty conf                     # Generate default configuration
pig sty conf -c rich             # Use conf/rich.yml template (more extensions)
pig sty conf -c slim             # Use conf/slim.yml template (minimal install)
pig sty conf -c supabase         # Use conf/supabase.yml template (self-hosting)
pig sty conf -g                  # Generate with random passwords (recommended!)
pig sty conf -v 18               # Use PostgreSQL 18
pig sty conf -r china            # Use China region mirrors
pig sty conf --ip 10.10.10.10    # Specify IP address

Options:

  • -c|--conf: Config template name
  • -v|--version: PostgreSQL major version
  • -r|--region: Upstream repo region
  • --ip: Primary IP address
  • -g|--generate: Generate random passwords
  • -s|--skip: Skip IP address probing
  • -o|--output: Output config file path

sty deploy

Run Pigsty deployment playbook.

pig sty deploy                   # Run deploy.yml (or install.yml if not found)
pig sty install                  # Same as deploy (backward compatibility)
pig sty d                        # Short alias
pig sty de                       # Short alias
pig sty ins                      # Short alias

This command runs the deploy.yml playbook from your Pigsty installation. For backward compatibility, if deploy.yml doesn’t exist but install.yml does, install.yml will be used instead.

Warning: This operation makes changes to your system. Use with caution!

Complete Workflow

Here’s the complete workflow to set up Pigsty:

# 1. Download and install Pigsty
pig sty init

# 2. Install Ansible and dependencies
cd ~/pigsty
pig sty boot

# 3. Generate configuration
pig sty conf -g                  # Generate with random passwords

# 4. Deploy Pigsty
pig sty deploy

For detailed setup instructions, check Get Started.

Configuration Templates

Available configuration templates (-c option):

TemplateDescription
metaDefault single-node meta configuration
richConfiguration with more extensions enabled
slimMinimal installation
fullFull 4-node HA template
supabaseSelf-hosting Supabase template

Example:

pig sty conf -c rich -g -v 18 -r china

This generates a configuration using the rich template with PostgreSQL 18, random passwords, and China region mirrors.

sty list

List available Pigsty versions.

pig sty list                     # List available versions

sty get

Download Pigsty source tarball.

pig sty get                      # Download latest version
pig sty get v3.4.0               # Download specific version

11 - pig postgres

Manage local PostgreSQL server with pig postgres subcommand

The pig pg command (alias pig postgres) manages local PostgreSQL server and databases. It wraps native tools like pg_ctl, psql, vacuumdb, providing a simplified server management experience.

pig pg - Manage local postgres server (pg_ctl, psql, vacuumdb)

Control Commands (via pg_ctl or systemctl):
  pig pg init                      initialize postgres data directory
  pig pg start                     start postgres server
  pig pg stop                      stop postgres server
  pig pg restart                   restart postgres server
  pig pg reload                    reload postgres server
  pig pg status                    show postgres server status
  pig pg promote                   promote replica to primary
  pig pg role                      detect and print postgres role

Connection & Query (via psql):
  pig pg psql [db] [-c sql]        connect to postgres
  pig pg ps                        show current connections
  pig pg kill [-a] [-x] [-u user] [-d db] [-q sql] [-w secs]

Maintenance (via vacuumdb & pg_repack):
  pig pg vacuum  [db] [-a]         vacuum database
  pig pg analyze [db] [-a]         analyze database
  pig pg freeze  [db] [-a]         vacuum freeze tables
  pig pg repack  [db] [-a]         online repack database

Log Commands:
  pig pg log list                  list log files
  pig pg log tail <logfile>        tail -f log file
  pig pg log cat  <logfile>        cat log file
  pig pg log less <logfile>        less log file

Service Management (via systemctl):
  pig pg svc start                 start postgres service
  pig pg svc stop                  stop postgres service
  pig pg svc restart               restart postgres service
  pig pg svc reload                reload postgres service
  pig pg svc status                show postgres service status

Command Overview

Service Control (pg_ctl wrapper):

CommandAliasDescriptionNotes
pg initinitdb, iInitialize data directoryWraps initdb
pg startboot, upStart PostgreSQLWraps pg_ctl start
pg stophalt, downStop PostgreSQLWraps pg_ctl stop
pg restartrebootRestart PostgreSQLWraps pg_ctl restart
pg reloadhupReload configurationWraps pg_ctl reload
pg statusst, statShow service statusShows processes & related services
pg promoteproPromote replica to primaryWraps pg_ctl promote
pg rolerDetect instance roleOutputs primary/replica

Connection & Query:

CommandAliasDescriptionNotes
pg psqlsql, connectConnect to databaseWraps psql
pg psactivity, actShow current connectionsQueries pg_stat_activity
pg killkTerminate connectionsDefault dry-run mode

Database Maintenance:

CommandAliasDescriptionNotes
pg vacuumvac, vcVacuum tablesWraps vacuumdb
pg analyzeana, azAnalyze tablesWraps vacuumdb –analyze-only
pg freezefrzFreeze vacuumWraps vacuumdb –freeze
pg repackrpOnline table repackingRequires pg_repack extension

Log Tools:

CommandAliasDescriptionNotes
pg loglLog managementParent command
pg log listlsList log files
pg log tailt, fReal-time log viewingtail -f
pg log catcOutput log content
pg log lessvi, vView with less

Known issue in v1.0.0: pig pg log grep has a parameter conflict and does not work. Use pig pg log cat | grep PATTERN as a workaround.

Service Subcommand (pg svc):

CommandAliasDescription
pg svc startboot, upStart postgres service
pg svc stophalt, dn, downStop postgres service
pg svc restartreboot, rtRestart postgres service
pg svc reloadrl, hupReload postgres service
pg svc statusst, statShow service status

Quick Start

# Service control
pig pg init                       # Initialize data directory
pig pg start                      # Start PostgreSQL
pig pg status                     # Check status
pig pg stop                       # Stop PostgreSQL
pig pg restart                    # Restart PostgreSQL
pig pg reload                     # Reload configuration

# Connection & query
pig pg psql                       # Connect to postgres database
pig pg psql mydb                  # Connect to specific database
pig pg ps                         # View current connections
pig pg kill -x                    # Terminate connections (requires -x to execute)

# Database maintenance
pig pg vacuum mydb                # Vacuum specific database
pig pg analyze mydb               # Analyze specific database
pig pg repack mydb                # Online repack database

# Log viewing
pig pg log tail                   # Real-time view latest log
pig pg log list --log-dir /var/log/pg  # Custom log directory
pig pg log cat | grep ERROR       # Filter logs in shell

Global Options

These options apply to all pig pg subcommands:

OptionShortDefaultDescription
--version-vauto-detectPostgreSQL major version
--data-D/pg/dataData directory path
--dbsu-UpostgresDatabase superuser (or $PIG_DBSU env)
--systemd-SfalseUse systemctl instead of pg_ctl

Version Detection Logic:

  1. If -v specified, use that version
  2. Otherwise read from PG_VERSION file in data directory
  3. If neither available, use default PostgreSQL in PATH

Service Control Commands

pg init

Initialize PostgreSQL data directory. Wraps initdb.

pig pg init                       # Initialize with defaults
pig pg init -v 18                 # Specify PostgreSQL 18
pig pg init -D /data/pg18         # Specify data directory
pig pg init -k                    # Enable data checksums
pig pg init -f                    # Force init (remove existing data)
pig pg init -- --waldir=/wal      # Pass extra args to initdb

Options:

OptionShortDefaultDescription
--encoding-EUTF8Database encoding
--localeCLocale setting
--data-checksum-kfalseEnable data checksums
--force-ffalseForce init, remove existing data (dangerous!)

Safety: Even with --force, command refuses to run if PostgreSQL is running.

pg start

Start PostgreSQL server.

pig pg start                      # Start with defaults
pig pg start -D /data/pg18        # Specify data directory
pig pg start -l /pg/log/pg.log    # Redirect output to log file
pig pg start -o "-p 5433"         # Pass options to postgres
pig pg start -y                   # Force start (skip running check)
pig pg start -S                   # Use systemctl to start

Options:

OptionShortDescription
--log-lRedirect stdout/stderr to log file
--timeout-tWait timeout (seconds)
--no-wait-WDon’t wait for startup completion
--options-oOptions to pass to postgres
--yes-yForce start (even if already running)

pg stop

Stop PostgreSQL server.

pig pg stop                       # Fast shutdown (default)
pig pg stop -m smart              # Wait for clients to disconnect
pig pg stop -m immediate          # Immediate shutdown
pig pg stop -S                    # Use systemctl to stop

Options:

OptionShortDefaultDescription
--mode-mfastShutdown mode: smart/fast/immediate
--timeout-t60Wait timeout (seconds)
--no-wait-WfalseDon’t wait for shutdown completion

Shutdown Modes:

ModeDescription
smartWait for all clients to disconnect
fastRollback active transactions, disconnect clients, clean shutdown
immediateTerminate all processes immediately, requires recovery on next start

pg restart

Restart PostgreSQL server.

pig pg restart                    # Fast restart
pig pg restart -m immediate       # Immediate restart
pig pg restart -o "-p 5433"       # Restart with new options
pig pg restart -S                 # Use systemctl to restart

Options: Same as pg stop, plus --options (-o) to pass to postgres.

pg reload

Reload PostgreSQL configuration. Sends SIGHUP signal to server.

pig pg reload                     # Reload configuration
pig pg reload -D /data/pg18       # Specify data directory
pig pg reload -S                  # Use systemctl reload

pg status

Show PostgreSQL server status. Displays not only pg_ctl status output, but also postgres processes and Pigsty-related service status.

pig pg status                     # Check service status
pig pg status -D /data/pg18       # Specify data directory

Output includes:

  1. pg_ctl status output (running status, PID, etc.)
  2. PostgreSQL process list (ps -u postgres)
  3. Related service status:
    • postgres: PostgreSQL systemd service
    • patroni: Patroni HA manager
    • pgbouncer: Connection pooler
    • pgbackrest: Backup service
    • vip-manager: VIP manager
    • haproxy: Load balancer

pg promote

Promote replica to primary.

pig pg promote                    # Promote replica
pig pg promote -D /data/pg18      # Specify data directory

Options:

OptionShortDescription
--timeout-tWait timeout (seconds)
--no-wait-WDon’t wait for promotion completion

pg role

Detect PostgreSQL instance role (primary or replica).

pig pg role                       # Output: primary, replica, or unknown
pig pg role -V                    # Verbose output, show detection process
pig pg role -D /data/pg18         # Specify data directory

Options:

OptionShortDescription
--verbose-VShow detailed detection process

Output:

  • primary: Current instance is primary
  • replica: Current instance is replica
  • unknown: Cannot determine instance role

Detection Strategy (by priority):

  1. Process detection: Check for walreceiver, recovery processes
  2. SQL query: Execute pg_is_in_recovery() (requires PostgreSQL running)
  3. Data directory check: Check for standby.signal, recovery.signal, recovery.conf files

Connection & Query Commands

pg psql

Connect to PostgreSQL database via psql.

pig pg psql                       # Connect to postgres database
pig pg psql mydb                  # Connect to specific database
pig pg psql mydb -c "SELECT 1"    # Execute single command
pig pg psql -f script.sql         # Execute SQL script file

Options:

OptionShortDescription
--command-cExecute single SQL command
--file-fExecute SQL script file

pg ps

Show PostgreSQL current connections. Queries pg_stat_activity view.

pig pg ps                         # Show client connections
pig pg ps -a                      # Show all connections (including system)
pig pg ps -u admin                # Filter by user
pig pg ps -d mydb                 # Filter by database

Options:

OptionShortDescription
--all-aShow all connections (including system)
--user-uFilter by user
--database-dFilter by database

pg kill

Terminate PostgreSQL connections. Default is dry-run mode, requires -x to execute.

pig pg kill                       # Show connections to be terminated (dry-run)
pig pg kill -x                    # Actually terminate connections
pig pg kill --pid 12345 -x        # Terminate specific PID
pig pg kill -u admin -x           # Terminate user's connections
pig pg kill -d mydb -x            # Terminate database connections
pig pg kill -s idle -x            # Terminate idle connections
pig pg kill --cancel -x           # Cancel queries instead of terminating
pig pg kill -w 5 -x               # Repeat every 5 seconds

Options:

OptionShortDescription
--execute-xActually execute (default is dry-run)
--pidTerminate specific PID
--user-uFilter by user
--database-dFilter by database
--state-sFilter by state (idle/active/idle in transaction)
--query-qFilter by query pattern
--all-aInclude replication connections
--cancel-cCancel queries instead of terminating
--watch-wRepeat every N seconds

Security: --state and --query parameters are validated to accept only simple alphanumeric patterns, preventing SQL injection.

Database Maintenance Commands

pg vacuum

Vacuum database tables. Wraps vacuumdb.

pig pg vacuum                     # Vacuum current database
pig pg vacuum mydb                # Vacuum specific database
pig pg vacuum -a                  # Vacuum all databases
pig pg vacuum mydb -t mytable     # Vacuum specific table
pig pg vacuum mydb -n myschema    # Vacuum tables in schema
pig pg vacuum mydb --full         # VACUUM FULL (requires exclusive lock)

Options:

OptionShortDescription
--all-aProcess all databases
--schema-nSpecify schema
--table-tSpecify table
--verbose-VVerbose output
--full-FVACUUM FULL (requires exclusive lock)

Security: --schema and --table parameters are validated for proper PostgreSQL identifier format.

pg analyze

Analyze database tables to update statistics.

pig pg analyze                    # Analyze current database
pig pg analyze mydb               # Analyze specific database
pig pg analyze -a                 # Analyze all databases
pig pg analyze mydb -t mytable    # Analyze specific table

Options: Same as pg vacuum (without --full).

pg freeze

Freeze vacuum database to prevent transaction ID wraparound.

pig pg freeze                     # Freeze current database
pig pg freeze mydb                # Freeze specific database
pig pg freeze -a                  # Freeze all databases

Options: Same as pg analyze.

pg repack

Online table repacking. Requires pg_repack extension.

pig pg repack mydb                # Repack all tables in database
pig pg repack -a                  # Repack all databases
pig pg repack mydb -t mytable     # Repack specific table
pig pg repack mydb -n myschema    # Repack tables in schema
pig pg repack mydb -j 4           # Use 4 parallel jobs
pig pg repack mydb --dry-run      # Show tables to be repacked

Options:

OptionShortDescription
--all-aProcess all databases
--schema-nSpecify schema
--table-tSpecify table
--verbose-VVerbose output
--jobs-jNumber of parallel jobs (default 1)
--dry-run-NShow tables to be repacked

Log Commands

Log commands view PostgreSQL log files. Default log directory is /pg/log/postgres, can be changed via --log-dir.

Log Command Global Options:

OptionDescription
--log-dirLog directory path (default: /pg/log/postgres)

Permission Handling: If current user lacks permission to read log directory, command automatically retries with sudo.

pg log list

List log files in log directory.

pig pg log list                              # List logs in default directory
pig pg log list --log-dir /var/log/postgres  # List logs in specified directory

pg log tail

Real-time log viewing (like tail -f). Default views latest CSV log file.

pig pg log tail                   # View latest log
pig pg log tail postgresql.csv    # View specific log file
pig pg log tail -n 100            # Show last 100 lines then follow
pig pg log tail --log-dir /var/log/postgres  # Use custom directory

Options:

OptionShortDefaultDescription
--lines-n50Number of lines to show

pg log cat

Output log file content.

pig pg log cat                    # Output latest log
pig pg log cat -n 100             # Output last 100 lines
pig pg log cat postgresql.csv     # Output specific log file

Options:

OptionShortDefaultDescription
--lines-n100Number of lines to show

pg log less

Open log file with less. Defaults to end of file (+G).

pig pg log less                   # Open latest log with less
pig pg log less postgresql.csv    # Open specific log file

pg svc Subcommand

pg svc provides systemctl-based PostgreSQL service management:

pig pg svc start                 # Start postgres service
pig pg svc stop                  # Stop postgres service
pig pg svc restart               # Restart postgres service
pig pg svc reload                # Reload postgres service
pig pg svc status                # Show service status

Alias Reference:

CommandAlias
pg svc startboot, up
pg svc stophalt, dn, down
pg svc restartreboot, rt
pg svc reloadrl, hup
pg svc statusst, stat

Design Notes

Relationship with Native Tools:

pig pg is not a simple wrapper of PostgreSQL native tools, but a higher-level abstraction for common operations:

  • Service control commands (init/start/stop/restart/reload/promote) call pg_ctl or systemctl
  • status command shows process and related service status beyond pg_ctl status
  • Connection management commands (psql/ps/kill) call psql
  • Maintenance commands (vacuum/analyze/freeze) call vacuumdb
  • repack command calls pg_repack
  • Log commands call system tools like tail, less, grep

For full native tool functionality, call the respective commands directly.

Security Considerations:

  • --state, --query, --schema, --table parameters are validated to prevent SQL injection
  • pg kill defaults to dry-run mode to prevent accidents
  • Log commands auto-retry with sudo when permissions insufficient

Platform Support:

This command is designed for Linux systems, some features depend on systemctl and journalctl.

12 - pig patroni

Manage Patroni service and cluster with pig patroni subcommand

The pig patroni command (alias pig pt) manages Patroni service and PostgreSQL HA clusters. It wraps common patronictl and systemctl operations for simplified cluster management.

pig pt - Manage Patroni cluster using patronictl commands.

Cluster Operations (via patronictl):
  pig pt list                      list cluster members
  pig pt restart [member]          restart PostgreSQL (rolling restart)
  pig pt reload                    reload PostgreSQL config
  pig pt reinit <member>           reinitialize a member
  pig pt pause                     pause automatic failover
  pig pt resume                    resume automatic failover
  pig pt switchover                perform planned switchover
  pig pt failover                  perform manual failover
  pig pt config <action>           manage cluster config

Service Management (via systemctl):
  pig pt status                    show comprehensive patroni status
  pig pt start                     start patroni service (shortcut)
  pig pt stop                      stop patroni service (shortcut)
  pig pt svc start                 start patroni service
  pig pt svc stop                  stop patroni service
  pig pt svc restart               restart patroni service
  pig pt svc status                show patroni service status

Logs:
  pig pt log [-f] [-n 100]         view patroni logs

Overview

Cluster Operations (patronictl wrapper):

CommandAliasDescriptionImplementation
pt listls, lList cluster memberspatronictl list -e -t
pt restartreboot, rtRestart PostgreSQL instancepatronictl restart
pt reloadrl, hupReload PostgreSQL configpatronictl reload
pt reinitriReinitialize memberpatronictl reinit
pt switchoverswPlanned switchoverpatronictl switchover
pt failoverfoManual failoverpatronictl failover
pt pausepPause auto-failoverpatronictl pause
pt resumerResume auto-failoverpatronictl resume
pt configcfg, cShow or modify cluster configpatronictl show-config / edit-config

Service Management (systemctl wrapper):

CommandAliasDescriptionImplementation
pt startboot, upStart Patroni servicesystemctl start patroni
pt stophalt, dn, downStop Patroni servicesystemctl stop patroni
pt statusst, statShow service statussystemctl status patroni
pt logl, lgView Patroni logsjournalctl -u patroni

Service Subcommand (pt svc):

CommandAliasDescription
pt svc startboot, upStart Patroni service
pt svc stophalt, dn, downStop Patroni service
pt svc restartreboot, rtRestart Patroni service
pt svc reloadrl, hupReload Patroni service
pt svc statusst, statShow service status

Quick Start

# Check cluster member status
pig pt list                    # List default cluster members
pig pt list pg-meta            # List specific cluster
pig pt list -W                 # Continuous watch mode
pig pt list -w 5               # Refresh every 5 seconds

# View and modify cluster config
pig pt config                  # Show current cluster config
pig pt config ttl=60           # Modify single config item (immediate effect)
pig pt config ttl=60 loop_wait=15  # Modify multiple config items

# Cluster operations
pig pt restart                 # Restart all members' PostgreSQL
pig pt restart pg-test-1       # Restart specific member
pig pt switchover              # Planned switchover
pig pt pause                   # Pause auto-failover
pig pt resume                  # Resume auto-failover

# Manage Patroni service
pig pt status                  # Check service status
pig pt start                   # Start service
pig pt stop                    # Stop service
pig pt log -f                  # Real-time log viewing

Global Options

These options apply to all pig pt subcommands:

OptionShortDescription
--dbsu-UDatabase superuser (default: $PIG_DBSU or postgres)

Cluster Commands

pt list

List Patroni cluster member status. Wraps patronictl list with -e (extended output) and -t (show timestamp) flags by default.

pig pt list                    # List default cluster members
pig pt list pg-meta            # List specific cluster
pig pt list -W                 # Continuous watch mode
pig pt list -w 5               # Refresh every 5 seconds
pig pt list pg-test -W -w 3    # Watch pg-test cluster, 3s refresh

Options:

OptionShortDescription
--watch-WEnable continuous watch mode
--interval-wWatch refresh interval (seconds)

pt restart

Restart PostgreSQL instance via Patroni. This triggers a rolling restart of PostgreSQL, not the Patroni daemon itself.

pig pt restart                   # Restart all members (interactive)
pig pt restart pg-test-1         # Restart specific member
pig pt restart -f                # Skip confirmation
pig pt restart --role=replica    # Restart replicas only
pig pt restart --pending         # Restart pending members

Options:

OptionShortDescription
--force-fSkip confirmation
--roleFilter by role (leader/replica/any)
--pendingRestart only pending members

pt reload

Reload PostgreSQL configuration via Patroni. Triggers config reload on all members.

pig pt reload

pt reinit

Reinitialize cluster member. This re-syncs data from the primary.

pig pt reinit pg-test-1          # Reinit specific member
pig pt reinit pg-test-1 -f       # Skip confirmation
pig pt reinit pg-test-1 --wait   # Wait for completion

Options:

OptionShortDescription
--force-fSkip confirmation
--wait-wWait for reinit completion

Warning: This operation deletes all data on the target member and re-syncs from primary.

pt switchover

Perform planned primary-replica switchover.

pig pt switchover                 # Interactive switchover
pig pt switchover -f              # Skip confirmation
pig pt switchover --leader pg-1   # Specify current primary
pig pt switchover --candidate pg-2  # Specify new primary

Options:

OptionShortDescription
--force-fSkip confirmation
--leaderSpecify current primary
--candidateSpecify candidate new primary

pt failover

Perform manual failover. Used when primary is unavailable.

pig pt failover                   # Interactive failover
pig pt failover -f                # Skip confirmation
pig pt failover --candidate pg-2  # Specify new primary

Options:

OptionShortDescription
--force-fSkip confirmation
--candidateSpecify candidate new primary

pt pause

Pause Patroni’s automatic failover.

pig pt pause                      # Pause auto-failover
pig pt pause --wait               # Wait for confirmation

Options:

OptionShortDescription
--wait-wWait for operation completion

Use case: Pause auto-failover during maintenance operations (e.g., major version upgrade, storage migration) to prevent accidental triggers.

pt resume

Resume Patroni’s automatic failover.

pig pt resume                     # Resume auto-failover
pig pt resume --wait              # Wait for confirmation

Options:

OptionShortDescription
--wait-wWait for operation completion

pt config

Show or modify cluster configuration. Without parameters shows current config, with key=value parameters modifies config.

pig pt config                           # Show current cluster config
pig pt config show                      # Show config (explicit)
pig pt config edit                      # Interactive config edit
pig pt config set ttl=60                # Set TTL to 60 seconds
pig pt config set ttl=60 loop_wait=15   # Modify multiple config items
pig pt config pg max_connections=200    # Modify PostgreSQL parameter

Subcommands:

SubcommandDescription
show (default)Show current config
editInteractive config edit
set key=valueDirectly set config item
pg key=valueSet PostgreSQL parameter

Common config items:

ConfigDescriptionDefault
ttlLeader lock time-to-live (seconds)30
loop_waitMain loop sleep time (seconds)10
retry_timeoutDCS and PostgreSQL operation timeout (seconds)10
maximum_lag_on_failoverMaximum lag allowed during failover (bytes)1048576

Note: This command modifies dynamic cluster config stored in DCS (e.g., etcd), not local config file /etc/patroni/patroni.yml.

Service Commands

pt start

Start Patroni service.

pig pt start                     # Start Patroni service
pig pt up                        # Alias
pig pt boot                      # Alias

Equivalent to sudo systemctl start patroni.

pt stop

Stop Patroni service.

pig pt stop                      # Stop Patroni service
pig pt down                      # Alias
pig pt halt                      # Alias

Equivalent to sudo systemctl stop patroni.

Note: Stopping Patroni service will also stop the PostgreSQL instance on this node (depending on Patroni configuration).

pt status

Show Patroni service comprehensive status, including:

  • systemd service status
  • Patroni process info
  • Cluster member status
pig pt status

pt log

View Patroni service logs.

pig pt log                     # Show last 50 log lines
pig pt log -f                  # Real-time log following
pig pt log -n 100              # Show last 100 log lines
pig pt log -f -n 200           # Show last 200 lines and follow

Options:

OptionShortDefaultDescription
--follow-ffalseReal-time log following
--lines-n50Number of log lines to show

Equivalent to journalctl -u patroni [-f] [-n N].

pt svc Subcommand

pt svc provides the same functionality as top-level service commands, for explicitly operating on the Patroni daemon:

pig pt svc start                 # Start Patroni service
pig pt svc stop                  # Stop Patroni service
pig pt svc restart               # Restart Patroni service
pig pt svc reload                # Reload Patroni service
pig pt svc status                # Show service status

Alias Reference:

CommandAlias
pt svc startboot, up
pt svc stophalt, dn, down
pt svc restartreboot, rt
pt svc reloadrl, hup
pt svc statusst, stat

Design Notes

Relationship with patronictl:

pig pt wraps common patronictl operations:

  • Cluster queries: list, config show
  • Cluster management: restart, reload, reinit, switchover, failover, pause, resume
  • Config modification: config set, config edit
  • Service commands (start/stop/restart/reload/status) call systemctl
  • log command calls journalctl

Default Config Paths:

ConfigDefault
Patroni config file/etc/patroni/patroni.yml
Service namepatroni

Permission Handling:

  • If current user is DBSU: execute commands directly
  • If current user is root: use su - postgres -c "..." to execute
  • Other users: use sudo -inu postgres -- ... to execute

Platform Support:

This command is designed for Linux systems, depends on systemctl and journalctl.

13 - pig pgbackrest

Manage pgBackRest backup and PITR with pig pgbackrest subcommand

The pig pgbackrest command (alias pig pb) manages pgBackRest backup and point-in-time recovery (PITR). It wraps common pgbackrest operations for simplified backup management. All commands execute as database superuser (default postgres).

pig pb - Manage pgBackRest backup & restore commands.

Usage: pig pb <command>

Info Commands:
  pig pb info                      show backup info
  pig pb ls                        list backups (alias for info)
  pig pb ls repo                   list configured repos
  pig pb ls stanza                 list all stanzas

Backup Commands (Primary Only):
  pig pb backup                    create backup (auto mode)
  pig pb backup full               full backup
  pig pb backup diff               differential backup
  pig pb backup incr               incremental backup

Restore Commands:
  pig pb restore -d                restore to latest (end of WAL)
  pig pb restore -I                restore to backup consistency point
  pig pb restore -t <time>         restore to specific time
  pig pb restore -n <name>         restore to named restore point
  pig pb restore -b <set>          restore from specific backup set

Stanza Management:
  pig pb create                    create stanza (first-time setup)
  pig pb upgrade                   upgrade stanza after PG major upgrade
  pig pb delete --force            delete stanza (dangerous!)

Control Commands:
  pig pb check                     verify backup repository
  pig pb start                     enable pgBackRest
  pig pb stop                      disable pgBackRest
  pig pb expire                    cleanup expired backups

Log Commands:
  pig pb log                       list log files
  pig pb log tail                  tail -f latest log
  pig pb log cat                   cat latest log

Command Overview

Information Query:

CommandDescriptionImplementation
pb infoShow backup repository infopgbackrest info
pb lsList backup setspgbackrest info
pb ls repoList configured reposParse pgbackrest.conf
pb ls stanzaList all stanzasParse pgbackrest.conf

Backup & Restore:

CommandDescriptionImplementation
pb backupCreate backuppgbackrest backup
pb restoreRestore from backup (PITR)pgbackrest restore
pb expireClean up expired backupspgbackrest expire

Stanza Management:

CommandDescriptionImplementation
pb createCreate stanza (first-time setup)pgbackrest stanza-create
pb upgradeUpgrade stanza (after PG major upgrade)pgbackrest stanza-upgrade
pb deleteDelete stanza (dangerous!)pgbackrest stanza-delete

Control Commands:

CommandAliasDescriptionImplementation
pb checkVerify backup repository integritypgbackrest check
pb startEnable pgBackRest operationspgbackrest start
pb stopDisable pgBackRest operationspgbackrest stop
pb logl, lgView logstail/cat log files

Quick Start

# View backup info
pig pb info                          # Show all backup info
pig pb info --raw -o json            # Raw JSON output
pig pb ls                            # List all backups
pig pb ls repo                       # List configured repos
pig pb ls stanza                     # List all stanzas

# Create backup (must run on primary)
pig pb backup                        # Auto mode: full if none, else incr
pig pb backup full                   # Full backup
pig pb backup diff                   # Differential backup
pig pb backup incr                   # Incremental backup

# Restore (PITR, at least one recovery target is required)
pig pb restore -d                    # Restore to latest (end of WAL)
pig pb restore -I                    # Restore to backup consistency point
pig pb restore -t "2025-01-01 12:00:00+08"  # Restore to specific time
pig pb restore -n savepoint          # Restore to named restore point

# Stanza management
pig pb create                        # Initialize stanza
pig pb upgrade                       # Upgrade stanza after PG major upgrade
pig pb check                         # Verify repository integrity

# Cleanup
pig pb expire                        # Clean up per retention policy
pig pb expire --dry-run              # Dry run mode

Global Options

These options apply to all pig pb subcommands:

OptionShortDescription
--stanza-spgBackRest stanza name (auto-detected)
--config-cConfig file path
--repo-rRepository number (multi-repo scenario)
--dbsu-UDatabase superuser (default: $PIG_DBSU or postgres)

Stanza Auto-Detection:

If -s not specified, pig auto-detects stanza name from config file:

  1. Read config file (default /etc/pgbackrest/pgbackrest.conf)
  2. Find sections not starting with [global*]
  3. Use first stanza found

If config has multiple stanzas, a warning is issued and first one is used. Explicitly specify --stanza in this case.

Multi-Repo Support:

pgBackRest supports multiple repositories (repo1, repo2, etc.). Use -r to specify target repo:

pig pb backup -r 1                   # Backup to repo1
pig pb backup -r 2                   # Backup to repo2
pig pb info -r 2                     # View repo2 backup info

Information Commands

pb info

Show detailed backup repository info including all backup sets and WAL archive status.

pig pb info                          # Show all backup info
pig pb info --raw -o json            # Raw JSON output
pig pb info --set 20250101-120000F   # Show specific backup set details

Options:

OptionShortDescription
--raw-RRaw output mode (pass through pgBackRest output)
--output-oOutput format: text, json (only in --raw mode)
--setShow specific backup set details

pb ls

List resources in backup repository.

pig pb ls                            # List all backups (default)
pig pb ls backup                     # List all backups (explicit)
pig pb ls repo                       # List configured repos
pig pb ls stanza                     # List all stanzas
pig pb ls cluster                    # Alias for stanza

Types:

TypeDescriptionData Source
backupList all backup sets (default)pgbackrest info
repoList configured reposParse pgbackrest.conf
stanzaList all stanzasParse pgbackrest.conf

Backup Commands

pb backup

Create physical backup. Backups can only run on primary instance.

pig pb backup                        # Auto mode
pig pb backup full                   # Full backup
pig pb backup diff                   # Differential backup
pig pb backup incr                   # Incremental backup
pig pb backup --force                # Skip primary role check

Options:

OptionShortDescription
--force-fSkip primary role check

Backup Types:

TypeDescription
(empty)Auto mode: full if no backup exists, else incremental
fullFull backup: backup all data
diffDifferential: changes since last full backup
incrIncremental: changes since last any backup

Primary Check:

Before backup, command auto-checks if current instance is primary. If replica, command exits with error. Use --force to skip this check.

pb expire

Clean up expired backups and WAL archives per retention policy.

pig pb expire                        # Clean up per policy
pig pb expire --set 20250101-*       # Delete specific backup set
pig pb expire --dry-run              # Dry run (display only)

Options:

OptionDescription
--setDelete specific backup set
--dry-runDry run: only display what would be deleted

Retention Policy:

Configured in pgbackrest.conf:

[global]
repo1-retention-full=2               # Full backups to retain
repo1-retention-diff=4               # Differential backups to retain
repo1-retention-archive=2            # WAL archive retention policy

Restore Commands

pb restore

Restore from backup with point-in-time recovery (PITR) support. At least one recovery target (-d/-I/-t/-n/-l/-x) must be specified. Without parameters, help is shown.

# Recovery target (mutually exclusive)
pig pb restore -d                    # Restore to latest (explicit)
pig pb restore -I                    # Restore to backup consistency point
pig pb restore -t "2025-01-01 12:00:00+08"  # Restore to specific time
pig pb restore -t "2025-01-01"       # Restore to date (00:00:00 that day)
pig pb restore -t "12:00:00"         # Restore to time (today)
pig pb restore -n my-savepoint       # Restore to named restore point
pig pb restore -l "0/7C82CB8"        # Restore to LSN
pig pb restore -x 12345              # Restore to transaction ID

# Backup set selection (can combine with recovery target)
pig pb restore -b 20251225-120000F   # Restore from specific backup set

# Other options
pig pb restore -t "..." -X           # Exclusive mode (stop before target)
pig pb restore -t "..." -P           # Auto-promote after restore
pig pb restore -y                    # Skip confirmation countdown

Recovery Target Options:

OptionShortDescription
--default-dRestore to end of WAL stream (latest data)
--immediate-IRestore to backup consistency point
--time-tRestore to specific timestamp
--name-nRestore to named restore point
--lsn-lRestore to specific LSN
--xid-xRestore to specific transaction ID

Backup Set and Other Options:

OptionShortDescription
--set-bRestore from specific backup set (can combine with target)
--data-DTarget data directory
--exclusive-XExclusive mode: stop before target
--promote-PAuto-promote to primary after restore
--yes-ySkip confirmation and countdown

Time Formats:

Supports multiple time format inputs with timezone auto-completion (including non-integer-hour zones like +05:30):

FormatExampleDescription
Full format2025-01-01 12:00:00+08Complete timestamp with timezone
Date only2025-01-01Auto-completes to 00:00:00 that day (local timezone)
Time only12:00:00Auto-completes to today (local timezone)

Restore Flow:

  1. Validate parameters and environment
  2. Check PostgreSQL is stopped
  3. Display restore plan, wait for confirmation (5-second countdown)
  4. Execute pgbackrest restore
  5. Provide post-restore guidance

Important: Stop PostgreSQL before restore:

pig pg stop                          # Stop PostgreSQL
pig pb restore -t "..."              # Execute restore
pig pg start                         # Start PostgreSQL

Stanza Management Commands

pb create

Initialize new stanza. Must run before first backup.

pig pb create                        # Create stanza
pig pb create --no-online            # Create when PostgreSQL not running
pig pb create --force                # Force create

Options:

OptionShortDescription
--no-onlineCreate when PostgreSQL not running
--force-fForce create

pb upgrade

Update stanza after PostgreSQL major version upgrade.

pig pb upgrade                       # Upgrade stanza
pig pb upgrade --no-online           # Upgrade when PostgreSQL not running

Options:

OptionDescription
--no-onlineUpgrade when PostgreSQL not running

Use Case:

After PostgreSQL major version upgrade (e.g., 16 -> 17), run this command to update stanza metadata.

pb delete

Delete stanza and all its backups.

pig pb delete --force                # Delete stanza (requires --force)
pig pb delete --force --yes          # Skip countdown confirmation

Options:

OptionShortDescription
--force-fConfirm delete (required)
--yes-ySkip countdown confirmation

Warning: This is a destructive and irreversible operation! All backups will be permanently deleted.

Multiple safety mechanisms:

  1. Must provide --force parameter
  2. 5-second countdown (press Ctrl+C to cancel)
  3. Use --yes to skip countdown

Control Commands

pb check

Verify backup repository integrity and configuration.

pig pb check                         # Verify repository

This command checks:

  • WAL archive configuration correctness
  • Repository accessibility
  • Stanza configuration validity

pb start

Enable pgBackRest operations.

pig pb start                         # Enable operations

Use after pb stop to resume normal operations.

pb stop

Disable pgBackRest operations (for maintenance).

pig pb stop                          # Disable operations
pig pb stop --force                  # Terminate running operations

Options:

OptionShortDescription
--force-fTerminate running operations

Use Case:

During system maintenance, use this command to prevent new backup operations from starting.

Log Commands

pb log

View pgBackRest log files. Log directory is /pg/log/pgbackrest/.

pig pb log                           # List log files
pig pb log list                      # List log files
pig pb log tail                      # Real-time view latest log
pig pb log tail -n 100               # Show last 100 lines and follow
pig pb log cat                       # Show latest log content
pig pb log cat -n 50                 # Show last 50 lines
pig pb log cat pg-meta-backup.log    # Show specific log file

Subcommands:

SubcommandAliasesDescription
listlsList log files
tailfollow, fReal-time follow latest log
catshowShow log content

Options:

OptionShortDefaultDescription
--lines-n50Number of lines to show

Permission Handling:

If current user lacks permission to read log directory, command auto-retries with sudo.

Design Notes

Command Execution:

All pig pb commands execute as database superuser (DBSU). This is because pgBackRest needs access to PostgreSQL data files and WAL archives.

Execution logic:

  • If current user is DBSU: execute directly
  • If current user is root: use su - postgres -c "..." to execute
  • Other users: use sudo -inu postgres -- ... to execute

Relationship with pgbackrest:

pig pb is not a complete wrapper of pgbackrest, but a higher-level abstraction for common operations:

  • Auto-detect stanza name, no need to specify each time
  • Auto-check primary role before backup
  • Display plan and require confirmation before restore
  • Provide user-friendly time format input
  • Provide post-restore guidance

For full pgbackrest functionality, use pgbackrest command directly.

Default Configuration Paths:

ConfigDefault
Config file/etc/pgbackrest/pgbackrest.conf
Log directory/pg/log/pgbackrest
Data directorypg1-path from config, or $PGDATA env, or /pg/data

Security Considerations:

  • pb delete requires --force confirmation, with 5-second countdown
  • pb restore displays restore plan, with 5-second countdown confirmation
  • pb backup checks primary role by default, prevents running on replica
  • Log command filename parameter filters paths to prevent path traversal attacks

Platform Support:

This command is designed for Linux systems, depends on Pigsty default directory structure.

14 - pig pitr

Perform orchestrated Point-In-Time Recovery (PITR) with pig pitr command

The pig pitr command performs Orchestrated Point-In-Time Recovery. Unlike pig pb restore, this command automatically coordinates Patroni, PostgreSQL, and pgBackRest to complete the full PITR workflow.

pig pitr - Perform PITR with automatic Patroni/PostgreSQL lifecycle management.

This command orchestrates a complete PITR workflow:
  1. Stop Patroni service (if running)
  2. Ensure PostgreSQL is stopped (with retry and fallback)
  3. Execute pgbackrest restore
  4. Start PostgreSQL
  5. Provide post-restore guidance

Recovery Targets (at least one required):
  --default, -d      Recover to end of WAL stream (latest)
  --immediate, -I    Recover to backup consistency point
  --time, -t         Recover to specific timestamp
  --name, -n         Recover to named restore point
  --lsn, -l          Recover to specific LSN
  --xid, -x          Recover to specific transaction ID

Time Format:
  - Full: "2025-01-01 12:00:00+08"
  - Date only: "2025-01-01" (defaults to 00:00:00)
  - Time only: "12:00:00" (defaults to today)

Examples:
  pig pitr -d                      # Recover to latest (most common)
  pig pitr -t "2025-01-01 12:00"   # Recover to specific time
  pig pitr -I                      # Recover to backup consistency point
  pig pitr -d --dry-run            # Show execution plan without running
  pig pitr -d -y                   # Skip confirmation (for automation)
  pig pitr -d --skip-patroni       # Skip Patroni management
  pig pitr -d --no-restart         # Don't auto-start PostgreSQL after restore

Overview

pig pitr is a highly automated recovery command that:

  1. Automatically stops Patroni service (if running)
  2. Ensures PostgreSQL is stopped (with retry and fallback strategies)
  3. Executes pgBackRest restore
  4. Starts PostgreSQL
  5. Provides post-recovery guidance

Comparison with pig pb restore:

Featurepig pitrpig pb restore
Stop PatroniAutomaticManual
Stop PostgreSQLAutomatic (with retry)Must be pre-stopped
Start PostgreSQLAutomaticManual
Post-recovery guidanceDetailed guidanceNone
Use caseProduction full recoveryLow-level ops or scripting

Quick Start

# Most common: recover to latest data
pig pitr -d

# Recover to specific point in time
pig pitr -t "2025-01-01 12:00:00+08"

# Recover to backup consistency point (fastest)
pig pitr -I

# View execution plan (dry-run)
pig pitr -d --dry-run

# Skip confirmation (for automation)
pig pitr -d -y

# Recover from specific backup set
pig pitr -d -b 20251225-120000F

# Standalone PostgreSQL (non-Patroni managed)
pig pitr -d --skip-patroni

# Don't auto-start PostgreSQL after recovery
pig pitr -d --no-restart

Parameters

Recovery Target (choose one)

ParamShortDescription
--default-dRecover to end of WAL stream (latest data)
--immediate-IRecover to backup consistency point
--time-tRecover to specific timestamp
--name-nRecover to named restore point
--lsn-lRecover to specific LSN
--xid-xRecover to specific transaction ID

Backup Selection

ParamShortDescription
--set-bRecover from specific backup set

Flow Control

ParamShortDescription
--skip-patroni-SSkip Patroni stop operation
--no-restart-NDon’t auto-start PostgreSQL after recovery
--dry-runShow execution plan only, don’t execute
--yes-ySkip confirmation countdown

Recovery Options

ParamShortDescription
--exclusive-XExclusive mode: stop before target
--promote-PAuto-promote to primary after recovery

Configuration

ParamShortDescription
--stanza-spgBackRest stanza name (auto-detected)
--config-cpgBackRest config file path
--repo-rRepository number (multi-repo scenario)
--dbsu-UDatabase superuser (default: postgres)
--data-DTarget data directory

Time Format

The --time parameter supports multiple formats with automatic timezone completion:

FormatExampleDescription
Full format2025-01-01 12:00:00+08Complete timestamp with timezone
Date only2025-01-01Auto-complete to 00:00:00 (current timezone)
Time only12:00:00Auto-complete to today (current timezone)

Execution Flow

Phase 1: Pre-check

  • Validate recovery target parameters (must specify exactly one)
  • Check data directory exists and is initialized
  • Detect Patroni service status
  • Detect PostgreSQL running status

Phase 2: Stop Patroni

If Patroni service is running and --skip-patroni not specified:

  • Execute systemctl stop patroni
  • Wait for PostgreSQL to auto-stop with Patroni

Phase 3: Ensure PostgreSQL Stopped

Progressive strategy to ensure PostgreSQL is fully stopped:

  1. Wait for auto-stop: wait 30 seconds after Patroni stops
  2. Graceful stop: use pg_ctl stop -m fast (retry 3 times with exponential backoff)
  3. Immediate stop: use pg_ctl stop -m immediate
  4. Force kill: use kill -9 (last resort)

Phase 4: Execute Recovery

Call pgBackRest for actual data recovery:

pgbackrest restore --target-action=promote ...

Phase 5: Start PostgreSQL

Unless --no-restart specified, auto-start PostgreSQL:

  • Wait for startup completion (timeout 120 seconds)
  • Verify process is actually running

Phase 6: Post-Recovery Guidance

Display detailed follow-up instructions including:

  • How to verify recovered data
  • How to promote to primary
  • How to resume Patroni cluster management
  • How to re-create pgBackRest stanza

Examples

Scenario 1: Recover from accidental delete

# 1. Check available backups
pig pb info

# 2. Recover to time before deletion
pig pitr -t "2025-01-15 09:30:00+08"

# 3. Verify data
pig pg psql
SELECT * FROM important_table;

# 4. Promote after confirmation
pig pg promote

Scenario 2: Recover to latest state

# Restore to latest data after failure
pig pitr -d

Scenario 3: Quick restore to backup point

# Recover to backup consistency point (no WAL replay)
pig pitr -I

Scenario 4: Automation script

# Skip all confirmations
pig pitr -d -y

Scenario 5: Standalone PostgreSQL

# Instance not managed by Patroni
pig pitr -d --skip-patroni

Scenario 6: Restore without restart

# Restore and inspect before start
pig pitr -d --no-restart

# Check data directory
ls -la /pg/data/

# Start manually
pig pg start

Execution Plan Example

Running pig pitr -d --dry-run shows an execution plan like:

══════════════════════════════════════════════════════════════════
 PITR Execution Plan
══════════════════════════════════════════════════════════════════

Current State:
  Data Directory:  /pg/data
  Database User:   postgres
  Patroni Service: active
  PostgreSQL:      running (PID: 12345)

Recovery Target:
  Latest (end of WAL stream)

Execution Steps:
  [1] Stop Patroni service
  [2] Ensure PostgreSQL is stopped
  [3] Execute pgBackRest restore
  [4] Start PostgreSQL
  [5] Print post-restore guidance

══════════════════════════════════════════════════════════════════

[Dry-run mode] No changes made.

Post-Recovery Actions

After a successful recovery, the command prints guidance like:

══════════════════════════════════════════════════════════════════
 PITR Complete
══════════════════════════════════════════════════════════════════

[1] Verify recovered data:
   pig pg psql

[2] If satisfied, promote to primary:
   pig pg promote

[3] To resume Patroni cluster management:
   WARNING: Ensure data is correct before starting Patroni!
   systemctl start patroni

   Or if you want this node to be the leader:
   1. Promote PostgreSQL first: pig pg promote
   2. Then start Patroni: systemctl start patroni

[4] Re-create pgBackRest stanza if needed:
   pig pb create

══════════════════════════════════════════════════════════════════

Safety Mechanisms

Confirmation Countdown

Unless --yes is specified, the command shows a 5-second countdown before execution:

WARNING: This will overwrite the current database!
Press Ctrl+C to cancel, or wait for countdown...
Starting PITR in 5 seconds...

Progressive Stop Strategy

To ensure data safety, PostgreSQL is stopped progressively:

  1. Try graceful stop first (preserve consistency)
  2. If failed, try immediate stop
  3. Use kill -9 only as last resort

Recovery Verification

After restore, the command verifies PostgreSQL startup and prompts to check logs if it fails.

Design Notes

Relationship with other commands:

  • pig pitr internally calls pig pt stop, pig pg stop, pig pg start, and pig pb restore
  • Provides higher-level automation than individual commands
  • Suitable for production PITR workflows

Error handling:

  • Each phase has detailed error messages
  • On failure, suggests relevant log locations
  • Supports manual continuation after interruption

Privilege execution:

  • If the current user is DBSU: execute directly
  • If current user is root: run su - postgres -c "..."
  • Other users: run sudo -inu postgres -- ...

Platform support:

This command is designed for Linux systems and depends on Pigsty’s default directory layout.