Skip to content
Docs Extensions Blog Pricing 中文 GitHub

This is the multi-page printable view of this section. .

Return to the regular view of this page.

PIG 1.8 Documentation

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 (about 5 MB). 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 576 packaged extensions with just a few simple commands.

PIG is also automation-friendly by design: consistent parameter styles, clear error messages, preview switches like --plan, and confirmation steps.

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:

  • pig repo: Manage software repositories
  • pig ext: Manage PG extensions
  • pig build: Build extensions from source
  • pig install: Install PostgreSQL and extension packages through the native package manager

Pigsty Management:

  • pig sty: Manage Pigsty installation and Grafana dashboards
  • pig inventory: Inspect, edit, validate, and exchange the Pigsty inventory
  • pig context: Collect host, PostgreSQL, Patroni, pgBackRest, and extension context
  • pig pg: Manage local PostgreSQL server
  • pig pt: Run patronictl transparently to manage Patroni HA clusters
  • pig pb: Manage pgBackRest backup & restore
  • pig pitr: Point-in-time recovery workflow

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:

Global (Cloudflare CDN):

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

China Mainland:

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

PIG binary is about 5 MB. On Linux it uses rpm or dpkg to install the latest version available on the selected mirror. In the example output below, X.Y.Z is that mirrored version:

$ curl -fsSL https://repo.pigsty.io/pig | bash
[INFO] kernel = Linux
[INFO] machine = x86_64
[INFO] package = deb
[INFO] pkg_url = https://repo.pigsty.io/pkg/pig/vX.Y.Z/pig_X.Y.Z-1_amd64.deb
[INFO] download = /tmp/pig_X.Y.Z-1_amd64.deb
[INFO] downloading pig vX.Y.Z
curl -fSL https://repo.pigsty.io/pkg/pig/vX.Y.Z/pig_X.Y.Z-1_amd64.deb -o /tmp/pig_X.Y.Z-1_amd64.deb
######################################################################## 100.0%
[INFO] installing: dpkg -i /tmp/pig_X.Y.Z-1_amd64.deb
(Reading database ... 166001 files and directories currently installed.)
Preparing to unpack /tmp/pig_X.Y.Z-1_amd64.deb ...
Unpacking pig (X.Y.Z-1) ...
Setting up pig (X.Y.Z-1) ...
[INFO] pig vX.Y.Z 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.8.0 linux/amd64

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

$ pig status

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

# [OS Environment] ===============================
OS Distro Code   : u26
OS OSArch        : arm64
OS Package Type  : deb
OS Vendor ID     : ubuntu
OS Version       : 26
OS Version Full  : 26.04
OS Version Code  : resolute

# [PG Environment] ===============================
Installed:
* PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg26.04+1)  72  Extensions

Active:
PG Version      :  PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg26.04+1)
Config Path     :  /usr/lib/postgresql/18/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: 376 ms
pigsty.io  ping ok: 1270 ms
Internet Access   :  true
Pigsty Repo       :  pigsty.io
Inferred Region   :  china
Latest Pigsty Ver :  4.5.0

Automation Tips

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

pig pitr -d --plan            # 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
✓ Found 576 extensions
Name                Status     Version     Cate   Flags   License         Repo     PGVer  Package                               Description
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
timescaledb         available  2.29.1      TIME   -dslt-  Timescale       PIGSTY   15-18  postgresql-18-timescaledb-tsl         Enables scalable inserts and complex queries for time-series dat
timescaledb_toolkit available  1.23.0      TIME   -ds---  Timescale       PIGSTY   15-18  postgresql-18-timescaledb-toolkit     Library of analytical hyperfunctions, time-series pipelining, an
timeseries          available  0.2.1       TIME   -d----  PostgreSQL      PIGSTY   14-18  postgresql-18-pg-timeseries           Convenience API for time series stack
periods             available  1.2.3       TIME   -ds---  PostgreSQL      PGDG     14-18  postgresql-18-periods                 Provide Standard SQL functionality for PERIODs and SYSTEM VERSIO
........
pg_bulkload         available  3.1.23      ETL    bds---  BSD 3-Clause    PIGSTY   14-18  postgresql-18-pg-bulkload             pg_bulkload is a high speed data loading utility for PostgreSQL
test_decoding       available  -           ETL    --s--x  PostgreSQL      CONTRIB  14-18  postgresql-18                         SQL-based test/example module for WAL logical decoding
pgoutput            available  -           ETL    --s---  PostgreSQL      CONTRIB  14-18  postgresql-18                         Logical Replication output plugin

(576 Rows)

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 the latest online catalog is available at pigsty.io/ext/data/extension.csv.

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. In China network environments, -m|--mirror explicitly selects the bundled china repository definitions, including pigsty.cc and maintained domestic mirrors:

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

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, and so on. The actively supported PostgreSQL major versions are now 14-18. For example, for PostgreSQL 18 you can use:

pgsqlpg18pg17pg16pg15pg14
pgsqlpg18pg17pg16pg15pg14
pgsql-minipg18-minipg17-minipg16-minipg15-minipg14-mini
pgsql-corepg18-corepg17-corepg16-corepg15-corepg14-core
pgsql-fullpg18-fullpg17-fullpg16-fullpg15-fullpg14-full
pgsql-mainpg18-mainpg17-mainpg16-mainpg15-mainpg14-main
pgsql-clientpg18-clientpg17-clientpg16-clientpg15-clientpg14-client
pgsql-serverpg18-serverpg17-serverpg16-serverpg15-serverpg14-server
pgsql-develpg18-develpg17-develpg16-develpg15-develpg14-devel
pgsql-basicpg18-basicpg17-basicpg16-basicpg15-basicpg14-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

✓ PostgreSQL 18: 72 extensions installed (3 shown)
PostgreSQL 18: PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg26.04+1)
  Binary: /usr/lib/postgresql/18/bin
  Extension: /usr/share/postgresql/18/extension

Extension Stat: 3 Installed (PGDG 3, PIGSTY 0) + 69 CONTRIB = 72 Total

Name       Version  Cate   Flags   License       Repo  Package                 Description
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
pg_repack  1.5.3    ADMIN  bds---  PostgreSQL    PGDG  postgresql-18-repack    Reorganize tables in PostgreSQL databases with minimal locks
vector     0.8.4    RAG    -ds--r  PostgreSQL    PGDG  postgresql-18-pgvector  vector data type and ivfflat and hnsw access methods
wal2json   2.6      ETL    --s--x  BSD 3-Clause  PGDG  postgresql-18-wal2json  Changing data capture in JSON format

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
----       -------  ----------      -----------                                                        ----
pg_repack  1.5.3    pg_repack.so    Reorganize tables in PostgreSQL databases with minimal locks        module_pathname=$libdir/pg_repack relocatable=false lib=pg_repack.so
vector     0.8.4    vector.so       vector data type and ivfflat and hnsw access methods                relocatable=true lib=vector.so
wal2json   2.6      wal2json.so     Changing data capture in JSON format                                lib=wal2json.so
...

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 576 packaged 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, --plan previews, and 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 x8614-18Near EOL
el8.aarch64EL88.10RockyLinux 8 ARM14-18Near EOL
el9.x86_64EL99.7RockyLinux 9 x8614-18✅
el9.aarch64EL99.7RockyLinux 9 ARM14-18✅
el10.x86_64EL1010.1RockyLinux 10 x8614-18✅
el10.aarch64EL1010.1RockyLinux 10 ARM14-18✅
d11.x86_64Debian1111.11Debian 11 x8614-18EOL
d11.aarch64Debian1111.11Debian 11 ARM14-18EOL
d12.x86_64Debian1212.14Debian 12 x8614-18✅
d12.aarch64Debian1212.14Debian 12 ARM14-18✅
d13.x86_64Debian1313.6Debian 13 x8614-18✅
d13.aarch64Debian1313.6Debian 13 ARM14-18✅
u22.x86_64Ubuntu2222.04.5Ubuntu 22.04 x8614-18✅
u22.aarch64Ubuntu2222.04.5Ubuntu 22.04 ARM14-18✅
u24.x86_64Ubuntu2424.04.4Ubuntu 24.04 x8614-18✅
u24.aarch64Ubuntu2424.04.4Ubuntu 24.04 ARM14-18✅
u26.x86_64Ubuntu2626.04.0Ubuntu 26.04 x8614-18✅
u26.aarch64Ubuntu2626.04.0Ubuntu 26.04 ARM14-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 14-18 means support for PostgreSQL 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. Script installation targets Linux x86_64 / aarch64 RPM or DEB distributions. On macOS, use the binary from the release tarball.

Specify Version

You can request a particular version that is already published on the selected mirror by passing the version number as an argument:

Default Installation (Cloudflare CDN):

curl -fsSL https://repo.pigsty.io/pig | bash -s X.Y.Z

China Mirror:

curl -fsSL https://repo.pigsty.cc/pig | bash -s X.Y.Z

Mirror publication can lag the GitHub release. For the exact current release, use the GitHub artifacts below.

Download Release Artifacts

Current v1.8.0 installation packages (RPM/DEB/tarball) are available from the GitHub Release, with published hashes in checksums.txt. Use the following direct URL pattern:

  • https://github.com/pgsty/pig/releases/download/v1.8.0/<filename>
v1.8.0
├── pig_1.8.0-1_amd64.deb
├── pig_1.8.0-1_arm64.deb
├── pig-1.8.0-1.aarch64.rpm
├── pig-1.8.0-1.x86_64.rpm
├── pig-v1.8.0.linux-amd64.tar.gz
├── pig-v1.8.0.linux-arm64.tar.gz
├── pig-v1.8.0.darwin-amd64.tar.gz
└── pig-v1.8.0.darwin-arm64.tar.gz

After extracting, place the binary file in your system PATH. The equivalent Pigsty mirror directory becomes available after repository synchronization; check the target URL before using a version-pinned installer command.

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
pig update -m         # upgrade using the pigsty.cc mirror
pig update -v X.Y.Z   # upgrade to a version published by the configured repository

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 mod download
make 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.8.0.

VersionDateSummaryGitHub
v1.8.02026-08-14Native sty boot and sty conf, 575 packaged extensionsv1.8.0
v1.7.02026-08-12EL module policy, China mirrors, EL7 compatibility, 575 extensionsv1.7.0
v1.6.22026-08-11572 extensions, Grafana schema v2, SOW-first repository generationv1.6.2
v1.6.12026-07-30Catalog refresh and embedded Pigsty 4.5.0v1.6.1
v1.6.02026-07-28562 packaged extensions, patronictl passthrough, inventory & CMDB, Grafanav1.6.0
v1.5.12026-07-08PG kernel fork updates, mirror mode, bug fixesv1.5.1
v1.5.02026-07-04531 extensions, pigsty v4.4, pg/pt/pb/pitr rework, clone & forkv1.5.0
v1.4.22026-06-18524 extensions, PG19 beta, pgrx 0.18.1, Patroni fixesv1.4.2
v1.4.12026-05-01510 extensions, Ubuntu 26.04 support, repo calibrationv1.4.1
v1.4.02026-04-19510 extensions, pgrx 0.18.0, more building specsv1.4.0
v1.3.42026-04-14504 extensions refreshed, release checksums updatedv1.3.4
v1.3.32026-04-10481 extensions and Go 1.26.2 updatev1.3.3
v1.3.22026-03-23Routine metadata refresh, new pg tune, new build aliasesv1.3.2
v1.3.12026-03-05Retire PG13 defaults, unify PG14-18 support window, 464 extensionsv1.3.1
v1.3.02026-02-27Build pipeline hardening, 461 extensions, new pgedge/ivory supportv1.3.0
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.8.0

Pig v1.8.0 is a native controller-workflow update on top of v1.7.0. pig sty boot and pig sty conf are now implemented in Go instead of wrapping the legacy bootstrap and configure shell scripts. The packaged PostgreSQL extension count remains 575, and the embedded Pigsty version remains 4.5.0.

Highlights

  • pig sty boot can elevate once through sudo, verifies Ansible and its actual Python runtime, and supports explicit local packages, HTTP(S) URLs, trusted automatic offline packages, committed local repositories, and regional online repositories. Repository replacement is backed up and rolled back on failure; localhost SSH and a missing ~/pigsty are repaired on a best-effort basis.
  • pig sty conf now generates Inventory natively from safe templates, with up to ten IP mappings, exact domain replacement, region and proxy settings, PostgreSQL-version selection, and random credentials. The complete candidate is validated and atomically written with mode 0600.
  • Bootstrap download and extraction no longer require curl, wget, tar, or gzip; offline operation is restricted to pigsty-local, with the expected /www -> /data/nginx layout.
  • EL8 and newer consistently prefer DNF, local RPM requirements are resolved by provider capability, extension metadata and availability matrices receive their routine refresh, and the release toolchain moves to Go 1.26.6.

Compatibility Notes

  • pig sty boot no longer executes <PIGSTY_HOME>/bootstrap; automation that relied on script side effects must migrate to the native command.
  • pig sty conf --raw has been removed; --conf MODE and positional MODE are equivalent. --ip accepts up to ten addresses and remains mutually exclusive with --skip.
  • EL8 and newer use DNF. The limited EL7 compatibility catalog keeps its separate legacy YUM path.

Checksums

Artifacts: GitHub Release · checksums.txt

02fd2628810c1b00de730ece32b09dba1318be4c99a4ff1a0551740e32bf223b  pig-1.8.0-1.aarch64.rpm
72ba72a00af52a84b08b1346f85b42668b52bc097e315774ff9f501ca23ece8b  pig-1.8.0-1.x86_64.rpm
f023a5c9049dc532a057e932c73a8197683eaf4d97cb7a8f219492da1ad2a65f  pig-v1.8.0.darwin-amd64.tar.gz
e0ccf61c4d135dbc45359c207751092aeb6df788e826bb73eccc1a1ed8800998  pig-v1.8.0.darwin-arm64.tar.gz
a24a08c1b8d54adcdef5a99ed7b91caeedef1552a1440b1258eb4eb07fb20353  pig-v1.8.0.linux-amd64.tar.gz
9d23875804f87e78039498245059fd6b765831f027aacfc511ad0ac42711fa7b  pig-v1.8.0.linux-arm64.tar.gz
96259ff7584cd52254c91a9fd7d77bd577f23c55cb09f4bc995a3ca0fcbc7321  pig_1.8.0-1_amd64.deb
2e7370211514df6355ef96fb812670febe6ee1b85a28378432c33ebdaecb4b63  pig_1.8.0-1_arm64.deb

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

v1.7.0

Pig v1.7.0 is a repository compatibility and catalog release on top of v1.6.2. It makes China-mirror selection explicit, preserves native DNF module filtering by default, restores a streamlined EL7 repository catalog, and grows the bundled extension snapshot from 572 to 575. The embedded Pigsty version remains 4.5.0.

Highlights

  • -m|--mirror now selects the bundled china repository definitions directly. PGDG, Rocky Linux, Debian, Ubuntu, Docker, and other regional routes use the maintained mirror list instead of the former runtime PGDG proxy rewrite.
  • EL repository definitions no longer receive module_hotfixes=1 globally. Pigsty and PGDG repositories that intentionally override module streams opt in explicitly; BaseOS, AppStream, EPEL, and other repositories retain native DNF module filtering.
  • EL7 keeps a deliberately limited compatibility catalog: archived CentOS 7 Base/Updates/Extras/SCLo and EPEL definitions for x86_64, plus shared Pigsty and supported PGDG entries. The DNF-only module_hotfixes key is stripped when rendering EL7 YUM configuration.
  • Repository setup now reports unsupported platforms when the catalog has no matching definitions, instead of continuing with an empty repository set.

Extension Catalog

  • Packaged extensions: 572 -> 575, with no removals.
  • Add pg_local_cache 1.2.0, pg_statviz 0.1.0, and pg_policy 0.1.0.
  • Refresh biscuit 3.0.0, pg_clickhouse 0.10.0, pg_search 0.25.2, pg_turbovec packages 1.29.0, pg_uuid_v8 1.1.0, and the Debian q3c 2.0.5 package.

Compatibility Notes

  • No commands or global flags are removed in this release.
  • -m|--mirror is now an explicit China-region selection rather than a PGDG proxy rewrite. Use --region=default|china|europe when you need a specific route.
  • Custom EL repository definitions that require module-stream overrides must set meta.module_hotfixes: 1 explicitly. This setting is intentionally omitted for ordinary OS repositories and removed on EL7.
  • EL7 is end-of-life and only has limited compatibility coverage; prefer EL8 or newer for current PostgreSQL and extension packages.

Checksums

Artifacts: GitHub Release · checksums.txt

e3a339fefdd2203825d15438b52f18e729547eb88dae014212a46006a9bd47d1  pig-1.7.0-1.aarch64.rpm
34ce29d75ef9f669f3bf832cc812ae082abda7320ee2b2336ea61e701b9b67f8  pig-1.7.0-1.x86_64.rpm
d26803c685ba29c01cb8e6dfe50c6c1b0f004173be82015618fa8cdf6a329ba7  pig-v1.7.0.darwin-amd64.tar.gz
ea8120d48b93da936919f590ebbefeb72e73277e6bc133c1ef0bb1abc055d3ce  pig-v1.7.0.darwin-arm64.tar.gz
40295b64a2423094fa6f4e6d31da8d8ad5b26698c397d8916c0289591522d0bf  pig-v1.7.0.linux-amd64.tar.gz
7929091732957d85751ef3381285a1e5b0c3c7f82c0e00fc24ed085c496012d5  pig-v1.7.0.linux-arm64.tar.gz
41523c15f36a6c1acaf4af5c851d2626472fc15c21d25f91fc1e991fe8411072  pig_1.7.0-1_amd64.deb
adf7b2d9ce8fe42bad935428d16a9c998337df986b1065e0761dc167ce837ef5  pig_1.7.0-1_arm64.deb

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

v1.6.2

Pig v1.6.2 is a feature and catalog release on top of v1.6.1. It grows the packaged extension catalog from 562 to 572, adds native Grafana dashboard schema v2 support, and improves local repository generation. The embedded Pigsty version remains locked at 4.5.0.

Highlights

  • pig sty grafana now accepts both legacy dashboard JSON and native dashboard.grafana.app/v2 Dashboard resources. Loading a v2 dashboard uses Grafana’s resource API so tabs and section variables survive the round trip; dumping preserves an existing v2 destination while new dumps keep the legacy format by default.
  • pig repo create now prefers sow create --pigsty --timeout 10m -- <dir> when SOW is available, requires the resulting repo_complete marker to exist as a regular file, and falls back to createrepo_c / dpkg-scanpackages on Linux.
  • Local repository creation now works on macOS through SOW, defaults to the current directory there, and does not require sudo. The Linux default remains /www/pigsty.
  • Release metadata is bumped to 1.6.2; the embedded Pigsty version stays at 4.5.0.

Extension Catalog

  • Packaged extensions: 562 -> 572, with no removals.
  • 10 new extensions: pg_turbovec, pg_disorder, pg_mentat, plruby, jsonb_plruby, hstore_plruby, ltree_plruby, pg_describe, cat_tools, and pg_vault_tde.
  • 12 version refreshes: timescaledb 2.29.1, q3c 2.0.5, pgmnemo 0.16.1, pg_search 0.25.1, citus 14.2.0, citus_columnar 14.2.0, provsql 1.12.0, plpgsql_check 2.10.4, pg_rational 0.0.3, pgbson 2.1.0, pg_readme 0.7.1, and pg_readme_test_extension 0.7.1.
  • Package metadata and availability matrices are refreshed. Run pig ext reload to replace the embedded release snapshot with the latest online catalog.

Compatibility Notes

  • No commands or global flags are removed in this release.
  • When SOW is installed, pig repo create now prefers it over the legacy Linux generators and checks that the completion marker exists before reporting success.
  • The catalog count is not a promise that every package is available on every PostgreSQL / OS / architecture combination; use pig ext avail NAME on the target host.

Checksums

Artifacts: GitHub Release · checksums.txt

6697a96bbf476e697a5c3da8b6c861719e4b7208e1e4fe927cf4b475ea1f162f  pig-1.6.2-1.aarch64.rpm
ad0b311867bc6cd689dd73e9a96b84f1fe0f49f6c0f1184abf9eb3232a07a184  pig-1.6.2-1.x86_64.rpm
bb167e04fceb6cebee5c8a2423279cefb4474f46301a5055c464ac98294dc9db  pig-v1.6.2.darwin-amd64.tar.gz
3de74e33321884a0c36596c1e7df9370be594a315395538e9ba5b775bbc1a79d  pig-v1.6.2.darwin-arm64.tar.gz
7b69214e115e6815e772b7e179aa4070bd8553e585b164ba3a0f69a1d53a0294  pig-v1.6.2.linux-amd64.tar.gz
b511e727642987867be5921d72e8019e9c6186b82e63ddc34ad653773abed5a8  pig-v1.6.2.linux-arm64.tar.gz
3d1a80b833c6179b84ac5cc590ad06695b187b2bb4a09f544b1a14f9684dc4bc  pig_1.6.2-1_amd64.deb
00e4c84cd6b07a98401c73fb58dedaafe34bc794d7604edbcb76c5de39b0fb44  pig_1.6.2-1_arm64.deb

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

v1.6.1

Pig v1.6.1 is a maintenance release that refreshes the bundled extension catalog and aligns the embedded Pigsty version with 4.5.0. It introduces no new commands or flag changes.

Highlights

  • Regenerate the embedded extension.csv from the Pigsty package repositories so fresh installs resolve against current package metadata without first running pig ext reload.
  • Update the embedded Pigsty version reported by pig sty and pig status from 4.4.0 to 4.5.0.
  • Keep 562 packaged extensions across PostgreSQL 14-18, EL 8/9/10, Debian 12/13, and Ubuntu 22/24/26 on x86_64 and aarch64.

Checksums

Artifacts: Pigsty Mirror · checksums.txt

088e62bf7c64dbbe1d66cb54e6a6971e4d245e76ac0b9ab9de2280053b692e77  pig-1.6.1-1.aarch64.rpm
11b81f43754bcd7691752025ff0a95aa39745883d1cc17183e81fd357f50f5d3  pig-1.6.1-1.x86_64.rpm
f0512d26fd934a9ac7b0bf5aba22aba151991a4edcc8e995441f79f66fc6080a  pig-v1.6.1.darwin-amd64.tar.gz
595f122c210b1e5a5211de5b397bfcf2a803179f52a97aa38e68e8c567381533  pig-v1.6.1.darwin-arm64.tar.gz
d6c0ad7784c8df33539d60cb22259bb1b6f92a6fc26543f4384448dd630a83fd  pig-v1.6.1.linux-amd64.tar.gz
017fbae1c32858b5438514685e45959ceaeb6bf29ae11ecf0496b52d00908627  pig-v1.6.1.linux-arm64.tar.gz
571bc16bc8490190935ecb6d8dde9c5827e63eced0b7bbd05cfad1e4363e385b  pig_1.6.1-1_amd64.deb
54010b414aaf3971c435db714d5e1c0e039f30a11adf95a902040e177f911fa6  pig_1.6.1-1_arm64.deb

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

v1.6.0

Pig v1.6.0 is a major release: pig pt becomes a transparent patronictl launcher, the new root-level pig inventory command group brings lossless editing and validation of pigsty.yml (with an experimental PostgreSQL CMDB bridge), pig sty grafana adds native Grafana dashboard management, and the packaged extension catalog grows to 562.

Highlights

  • pig pt is rewritten as a native patronictl passthrough: all cluster commands (list, restart, switchover, failover, edit-config, …) forward directly with native flags, prompts, output, and exit codes, so new patronictl features work without waiting for a pig release. Local helpers status, log, set, and service/svc remain, with new -c/--config-file, -d/--dcs-url, -k/--insecure options and a pig pt -- … escape hatch.
  • New root-level pig inventory command group (alias inv): status / list / show / edit / validate / check / diff — a lossless YAML engine that preserves comments, formatting, key order, and anchors byte-for-byte; edit validates before writing and writes atomically, so invalid YAML can never reach disk.
  • New experimental pig inventory cmdb subcommands (check / init / load / dump / enable / disable) exchange the inventory with the Pigsty CMDB in PostgreSQL over a native driver, with bounded timeouts and digest-pinned confirmation for destructive operations.
  • New pig sty grafana (alias gf) manages Grafana dashboards natively over HTTP: info / list / boot / load / init / dump / clean / lang / style. The pig sty surface is simplified: sty edit / validate / check / cmdb / dashboard / release are removed in favor of pig inventory, pig sty grafana, and pig sty list / get.
  • Reliability hardening: repo / catalog / download writes are atomic (no more truncated files on interrupt); structured -o json|yaml keeps stdout as a pure result envelope with wrapped-tool output on stderr; finer exit codes (usage errors → 2, missing --yes confirmation → 7); Ansible list variables are JSON-encoded.
  • Repository refresh: MySQL repos upgraded to 8.4 LTS, new Percona XtraBackup (pxb84) and MySQL Tools repos, Kubernetes v1.36, LLVM apt coverage for Debian/Ubuntu 26, Percona TDE sourced directly from repo.percona.com, wiltondb repo removed.
  • Toolchain: Go 1.26.5, new native PostgreSQL driver (pgx v5), embedded Pigsty version 4.4.0.

Extension Catalog

  • Packaged extensions: 531 -> 562; the broader PGEXT.CLOUD directory contains 2,230 entries.
  • 33 new extensions, including the pg_lake family (pg_lake, pg_lake_table, pg_lake_engine, pg_lake_iceberg, pg_lake_copy), pg_jieba, pg_cjk_parser, pg_fts, pgmonitor, pgmemento, pg_tiktoken_c, online_advisor, pgsqlmock, and plx.
  • 2 removed: pg_analytics, spat; 58 version refreshes, including vector 0.8.5, timescaledb 2.28.3, pg_search 0.24.3, pg_tde 2.2.1, and powa 5.2.0.
  • Package aliases synced with Pigsty: kafka is renamed to kafka-stack; the Debian/Ubuntu postgresql alias now maps to postgresql-$v only (use pgsql / pgsql-full for the full dev set).

Compatibility Notes

  • ⚠ pig pt failover <name>: the positional argument is now the cluster, not the promotion candidate — use pig pt failover CLUSTER --candidate MEMBER and review any failover automation before upgrading.
  • pig pt positionals are native cluster-first (restart CLUSTER [MEMBER]); forwarded commands return native patronictl exit codes, own their confirmation prompts (-y no longer gates them), and no longer support -o json (use native --format json). pig pt config is replaced by pig pt set K=V and native show-config / edit-config.
  • In structured output mode, wrapped-tool output moves to stderr and stdout carries only the JSON/YAML envelope — update scripts that parsed mixed output.
  • pig inventory edit tightens the inventory file mode to 0600 after a successful edit, since the file may contain credentials.

Checksums

6899e8a3e1c0adfe8c0c177c0632b0a00821b304ed5998fcbdf28d02660c6768  pig-1.6.0-1.aarch64.rpm
cabe593fe7f5c31cdbcd8d546ae4925b57f98f70c564452335568389f3f9737c  pig-1.6.0-1.x86_64.rpm
1f46d4a0b4710eed06b2cf8e7e17ee04b8d65331697c5c65afd513cc28282231  pig-v1.6.0.darwin-amd64.tar.gz
845decb95697fc68bc5e12bc80cecfd4c6d23160afee96568b699d82f2e9261d  pig-v1.6.0.darwin-arm64.tar.gz
4f1bb4fda8131db9f40db15e1575a6045b373dee609250cf5ee2bdedc2db89e2  pig-v1.6.0.linux-amd64.tar.gz
4384d11150e31d614ed4ac3de4d6bf7ee7fa111ac84f5575753bb9f2f31f4ed8  pig-v1.6.0.linux-arm64.tar.gz
e35ef0f2c76afe5f3512d34c0440abd8c0106c0e2775c5452e167ae3a4127e8e  pig_1.6.0-1_amd64.deb
c3bc6d04c6acd7e5c3164a33b7525b25a93e2de9822ce957c15c18ee0d551901  pig_1.6.0-1_arm64.deb

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

v1.5.1

Pig v1.5.1 updates PG kernel forks to the latest package names.

Highlights

  • Add mirror/proxy mode to repo, build, sty, update, and extension update workflows; pig build rust -m also configures Cargo mirror settings with rsproxy.cn.
  • Add explicit PostgreSQL 19 beta build switches: pig build repo --beta, pig build tool --beta, and pig build pgrx -b, while keeping PostgreSQL 18 and PG14-18 as the stable defaults.
  • Refresh kernel and fork package aliases for IvorySQL, PolarDB, OrioleDB, OpenHaloDB, Babelfish, and the pgEdge suite.
  • Improve the Cloudberry package build flow for cloudberry, cloudberry-backup, and cloudberry-pxf.
  • Refresh source/package metadata for Cloudberry, Babelfish, OrioleDB, pgEdge, PolarDB, polarstore, zlog, libpgfeutils, and libfq.
  • Improve repository generation for newer EL release strings, including PGDG on EL9.6+ / EL10+ and EPEL’s EL10 10z stream.
  • Refresh extension versions including pg_ivm 1.15, spock 5.0.10, snowflake 2.5.0, pg_tde 2.2, decoderbufs 3.6.0, and IvorySQL 5.4 packages.

Checksums

bc83887d640ed299a967b4eda2ae6db621a985abfa022fccabf508f7ec7b98e3  pig-1.5.1-1.aarch64.rpm
f0eab8e638d9e00a9172751446db869d0fe6ca7f382c7d540a931e0764014c0a  pig-1.5.1-1.x86_64.rpm
b32d894dc444ef2b9ec00816d50d82b5834e64c35b3bb18f08b6286a7ca8e8e7  pig-v1.5.1.darwin-amd64.tar.gz
4d768829b7e93fac6c732e27f665d3ab3945ec8bc1c336e5b91980932e8c9932  pig-v1.5.1.darwin-arm64.tar.gz
69f4a016af52f1ee8f1a1ffc1e405bb3be551c3813b6d24f70ef8394330be5eb  pig-v1.5.1.linux-amd64.tar.gz
f49becb5fd556b36a9aa8de0c27bdbe210f7958f59254d780774828f2340e77b  pig-v1.5.1.linux-arm64.tar.gz
a0d15145409d8a2629a74d3071f7af593e470920f22d9af4bf6f96725dbb6d49  pig_1.5.1-1_amd64.deb
f05b9b5abef5992ed16cbb5b6a6e3e5e58230072e3665ecc57b9ce2df3edb9a6  pig_1.5.1-1_arm64.deb

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

v1.5.0

Pig v1.5.0 is a PostgreSQL operations release for day-to-day DBA work. It adds local database clone/fork workflows, clarifies the boundaries between pg, pt, pb, and pitr, and tightens preview, confirmation, and structured-output behavior for high-risk operations.

Highlights

  • pig pg is now more focused on local PostgreSQL operations. pig pg clone creates quick database-level copies, while pig pg fork creates disposable physical instance forks for local validation, recovery drills, and isolated experiments.
  • Recovery flows are split more clearly: pig pitr is the orchestration entry point across Patroni, PostgreSQL, and pgBackRest; pig pb restore remains the low-level pgBackRest restore primitive. Restore commands now require an explicit target and provide more concrete plans and post-restore guidance.
  • Patroni operations are more predictable: high-risk actions such as pig pt restart, reinit, switchover, and failover use Pig-managed confirmation and plan output; pig pt config pg points operators to pig pt restart --pending when a restart is required.
  • Automation is safer: structured output no longer implies confirmation for destructive commands. High-risk execution requires explicit -y/--yes, while --plan and next_actions are more consistent for preview-then-execute workflows.
  • Logs and status output are more useful during incidents: pg, pb, and pt log commands now cover common latest / tail / show / grep workflows, and structured log snapshots use JSONL semantics.
  • Build and release defaults were refreshed: Pig is 1.5.0, embedded Pigsty is 4.4.0, and pig build pgrx defaults to cargo-pgrx 0.19.1.

Extension Catalog

  • Available extensions: 524 -> 531, with no removals.
  • New extensions: pg_ducklake, pgdisablelogerror, pg_stat_log, pg_stat_plans, passwordpolicy, db2fce, plpgsql_wrap.
  • Refreshed a batch of existing extension versions and package metadata, including timescaledb 2.28.2, postgis 3.6.4, vector 0.8.4, biscuit 2.4.1, citus 14.1.0, orioledb 1.8, documentdb 0.113, credcheck 5.0, and pgtt 4.5.
  • orioledb aliases no longer pin to PG17; they resolve against the requested PostgreSQL major. EL9 ARM64 Patroni aliases now point to noarch packages.

Compatibility Notes

  • Use -y/--yes for destructive operations in automation; structured output mode no longer substitutes for human confirmation.
  • pig pb restore and pig pitr require exactly one explicit recovery target; use --target-action=promote for auto-promote behavior.
  • Several ambiguous short options were cleaned up. For log commands, -o json means a JSONL snapshot and is not used for tail/follow streaming modes.

Checksums

9f83b78ed2eccedd55a86c634f88364f1945c3cefa1b23efdd72a7cf2062e1df  pig-1.5.0-1.aarch64.rpm
b792001498e9907d4659db46640f9c5164152b20689f90f93418f76fb4633e6e  pig-1.5.0-1.x86_64.rpm
ae1081dfbff8564ecdf713c85e8025c91bfd38e6575ea9ac99a92f968ab8a29d  pig-v1.5.0.darwin-amd64.tar.gz
6d69efcdcdc79fd90d2112e1e8042887020402aa037252d89d632243e7085dc6  pig-v1.5.0.darwin-arm64.tar.gz
8f914821b317cde73d3aec4ed311d5e90710bbc8cb372c1de3322083c31f4a85  pig-v1.5.0.linux-amd64.tar.gz
d4de9ef1c28d0a3661c4a4d47c469b7bfd5f5bddb610325796afb669ab162234  pig-v1.5.0.linux-arm64.tar.gz
35fd32affb4cb5bcca845d47a768782fb7005f06fcc1bcb5b7755d2627f96245  pig_1.5.0-1_amd64.deb
2be1df804d3f630560bc3ced0107c49ffad8bb52b004f72c7f8b4d09dc8d3e04  pig_1.5.0-1_arm64.deb

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

v1.4.2

  • Refresh the built-in extension catalog from 510 to 524 available extensions. This adds 14 extensions: pg_stl, pgmnemo, psql_bm25s, pg_orca, pg_sorted_heap, graph, pgrdf, fsm_core, jsonschema, pg_durable, pg_mockable, pg_uuid_v8, pg_stat_backtrace, and pg_projection.
  • Update package metadata for 48 existing extensions, including timescaledb 2.28.0, timescaledb_toolkit 1.23.0, pg_task 2.1.29, pg_search 0.24.0, pg_clickhouse 0.3.2, pg_graphql 1.6.1, documentdb 0.112, toastinfo 1.7, wrappers 0.6.1, and pgclone 4.3.2. No extensions were removed or demoted.
  • Add PostgreSQL 19 beta install/build/config support. PG19 is accepted as an explicit installable major, while PostgreSQL 18 remains the stable default for auto-detection, catalog display, and latest-version aliases.
  • Add pg19 package aliases and PG19 category alias resolution. PG19 category aliases borrow the PostgreSQL 18 visibility template and limit beta package expansion to PGDG-origin entries.
  • Teach pig sty conf -v 19 to enable the beta repository module when the template supports it, and emit clear warnings when a template is not tuned for PG19 beta or cannot enable the beta repo automatically.
  • Fix Patroni cluster operations by passing the resolved CLUSTER_NAME to patronictl restart, reinit, switchover, and failover; pig pt list also accepts an optional cluster name.
  • Bump the default pgrx version for pig build pgrx from 0.18.0 to 0.18.1.
  • Bump release metadata to v1.4.2, refresh Go module checksums, and add focused tests for PG19 aliases/config generation plus Patroni cluster-scope behavior.

Checksums

790afe4d6622cb041b06c622bc466cb1b2960a77487f368238027fb4a3a5ef93  pig-1.4.2-1.aarch64.rpm
ef918166b38a5eb1d108a928718c9e2cb8c3590e5a1f72effee942faca9b4bea  pig-1.4.2-1.x86_64.rpm
3e37ff22aed076cbdd453911dc89fcc9340b1a4faac62c0580094bc1eb2d6273  pig-v1.4.2.darwin-amd64.tar.gz
5eb7db776cb149331ebb33e3e6164e1cf711d0107941b92c6930a1d4c4b4eb23  pig-v1.4.2.darwin-arm64.tar.gz
c536c324e40a861217e31f4699ee5f0e6c2daeb4e6f0f0e8cc5f606da9d787eb  pig-v1.4.2.linux-amd64.tar.gz
8b2d5746264a95269535e5a426f78fe0020f73fa3391fcb613f274551c4786ab  pig-v1.4.2.linux-arm64.tar.gz
566e06f6da1fe9d635c41258d20cd9ff10de4e6e74b3b41ba2c6204ba22743c8  pig_1.4.2-1_amd64.deb
8c09e741975cb2f74b0f88c5995a8fa43d6c30a9a7ab7aaf0b8d83a7c66e1fc1  pig_1.4.2-1_arm64.deb

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

v1.4.1

  • 510 extensions, 3 new extensions, 20 updated.
  • Ubuntu 26.04 resolute support, drop support for Ubuntu 20.04 focal.
  • Bump el9.aarch64 ad hoc patroni to 4.1.2.
  • Bump pgEdge to PostgreSQL 18.
  • Calibrate upstream repo definition.

Checksums

cbfbb04b9640dcf39fcd9ccfa49e500dd0e65b72c0326c0d166e6f43e52b6dc3  pig-1.4.1-1.aarch64.rpm
c3325c7c68559b3149567eddd5963c4bc3bb4b39622740ca469f9d5d274e462b  pig-1.4.1-1.x86_64.rpm
c82e8a5e010aed198748576a3740479ac73eaa4a05f50cd4afc82fbcc9075d4d  pig-v1.4.1.darwin-amd64.tar.gz
a62e223a9f3fb6755cfeabfed2b9241d1be8aad42251917ddb2a4757fa5df6bd  pig-v1.4.1.darwin-arm64.tar.gz
65938e1158deb8a795c32ae99ff09178b7dec6b09743f8bdfbd9fd09a522e89f  pig-v1.4.1.linux-amd64.tar.gz
0fe4172dd1f03ac1b6ac73a28cdfe78b70c6613996252a230710b291faf8db58  pig-v1.4.1.linux-arm64.tar.gz
bdcc318ed50bff505f193f3a265d8fd2c7b093a104d5e5728b8c070e3580827e  pig_1.4.1-1_amd64.deb
1a1acb69d41bcfb9df8fb86c8d3c076050df5a242b639c1215833cd6394e112d  pig_1.4.1-1_arm64.deb

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

v1.4.0

  • Refresh the extension catalog and increase the total available extensions to 510, with version bumps such as timescaledb 2.26.3, decoderbufs 3.5.0, pgclone 4.0.0, and nominatim_fdw 1.3.
  • Bump the default pgrx version from 0.17.0 to 0.18.0 and align related Rust extension builds.
  • Refresh authoritative source bundles for pig build get, covering Cloudberry / OrioleDB build inputs and bundled artifacts for RDKit and OneSparse-related packages.
  • Fix repo set flag isolation and correct schema maintenance SQL.
  • Bump patroni to 4.1.1 for el9.aarch64.

Checksums

c8d2f46ea1b25f7d4665ee0994f0cb403a59f1464f80b3ecfa575ac283e5ecd0  pig-1.4.0-1.aarch64.rpm
fb1fd2f4f1e71894779de7b11a42960c09261620dffa0b54ff7f84e60efbf976  pig-1.4.0-1.x86_64.rpm
aa08045a31c26b9a6bfb770753817581c819022a6ed899e44f7b5a31f57f1733  pig-v1.4.0.darwin-amd64.tar.gz
80e50dd2ccd08d4a4016e85518186e156498e00c56a898e65acb96466db339f0  pig-v1.4.0.darwin-arm64.tar.gz
e425bf35ab6cb7907e94caca802b4418e3baf4bb1642dd957ab4baaa9db9f583  pig-v1.4.0.linux-amd64.tar.gz
840a21695955d64af7df12f7157b49573b18586bb2bf9cc5e7079074b86d39b7  pig-v1.4.0.linux-arm64.tar.gz
401d91bae78b14e3dcc338aaac9e451e94282c79efbe9affabcfeb8b36ece587  pig_1.4.0-1_amd64.deb
d60515f72fb9f8963554dc5668d2398e5ecefd0153a7756a9d555de90115bcce  pig_1.4.0-1_arm64.deb

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

v1.3.4

Bump total available extensions to 504.

Checksums

dc78def9a1e5eb483ac5df4c87f4ac0ef2018bb12b4bedff650d8ba4d58a05fd  pig-1.3.4-1.aarch64.rpm
998fcbdab1846c94c3155391d2100dad9b0fe338f48212022db38980bb11e696  pig-1.3.4-1.x86_64.rpm
031048c561abbeeeaa73fa3ac919b9fb89479b61c8a759bee5db2efba2e8a1df  pig-v1.3.4.darwin-amd64.tar.gz
e13c939330d32fa91819ce2da88d121fb02a1063240dbf6cc8fa7975960f8fd3  pig-v1.3.4.darwin-arm64.tar.gz
46aa321cf45fc9be635d91b38969b7f3602b7f226f43e5ee0e7614a030945c64  pig-v1.3.4.linux-amd64.tar.gz
f25c4f336edba5c9d2145368082f54e5b1a8b2d4261285b7a1721c088df4caa4  pig-v1.3.4.linux-arm64.tar.gz
563516047e37b25da01da9e25bcbada2c55642d1636b1bdab7d62488f9dcdfbb  pig_1.3.4-1_amd64.deb
81bb482892f7fd4be862d0f377cb37d01c925006c96998d81d94c770ed9652ba  pig_1.3.4-1_arm64.deb

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

v1.3.3

  • Refresh extension catalog and increase total available extensions to 481.
  • Bump Go toolchain from 1.26.0 to 1.26.2.

Extension Updates

ExtensionOldNewNotes
timescaledb2.25.22.26.2available, PG15-18
pg_background1.81.9.2DEB only, PG14-18
pg_ivm1.131.14upgraded, PG14-18
system_stats3.24.0upgraded, PG14-18
nominatim_fdw1.1.01.2upgraded, PG14-18
pg_textsearch0.5.01.0.0PG17-18
pg_clickhouse0.1.50.1.10available, PG14-18
pg_search0.22.20.22.6manual download, PG15-18
pg_store_plans1.91.10upgraded, PG14-18
pg_dispatch0.1.5new, PG14-18
pg_fsql1.1.0new, PG14-18
pg_liquid0.1.7new, PG14-18
pg_regresql2.0.0new, PG14-18
pg_slug_gen1.0.0new, PG15-18
pg_stat_ch0.3.3new, PG16-18
pg_variables1.2.5new, PG14-18
pgcalendar1.1.0new, PG14-18
pgclone2.2.0new, PG14-18
pgelog1.0.2new, PG14-18
pglock1.0.0new, PG14-18
pgproto0.2.1new, PG14-18
postgresbson2.0.2new, PG14-18
rdf_fdw2.4.0new, PG14-18
parray_gin1.4.0new, PG14-18

Checksums

e74418061ea975fbc3e8a89b31f274d7dc3617d12b9d681e5c8ef03584392088  pig-1.3.3-1.aarch64.rpm
8450e3e1076425fc8a10f24cc5fd833c3d2d880bab12baff5c10e59a31f62231  pig-1.3.3-1.x86_64.rpm
952a0e94b9020fca5add91f8e9a398fbedfda5d2e5c8736e59ddaa2b7152c826  pig-v1.3.3.darwin-amd64.tar.gz
c896b4fd44b19a250f4c3c47dc78643e10e92fde8cb6531b08cdc78e3623bb8a  pig-v1.3.3.darwin-arm64.tar.gz
d18a92f9aa05d6315c5e9bfde3245afc08fca93d200a8063aa20cb40feb8e85e  pig-v1.3.3.linux-amd64.tar.gz
62d020072360229b47f6c430b014344b912f2d9b58fd528154ae9c4ee805190a  pig-v1.3.3.linux-arm64.tar.gz
7a613a1f1c323ee78276b1733df026b8b0f415e0057b4cb8e509f771bfd3d614  pig_1.3.3-1_amd64.deb
f4c91ce86b787b6ab8cd584949d38c2ca87eb82d5e066bab91b80345252f43d8  pig_1.3.3-1_arm64.deb

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

v1.3.2

Routine maintenance release.

  • Refresh a batch of extension version metadata and catalog entries.
  • Add the pig pg tune subcommand to generate PostgreSQL tuning parameters from hardware resources and workload profiles.
  • Add pdu and pgdog source package aliases for pig build get.
  • Migrate extension catalog URLs from pgext.cloud to pigsty.io/ext.

Checksums

d760f47652ff3e2e4a61eb7b9a68ca68665b2b36c187c52f5eaf50d2f007d8f3  pig-1.3.2-1.aarch64.rpm
c2e02e62497f4c2055a9b448ddb3a24c618fcd488580c28b2b9a0e7cedacef55  pig-1.3.2-1.x86_64.rpm
b8d066ddefa4530946c74c30e7e4acdab6abf8da70a47dcfe2a77719b79e397f  pig-v1.3.2.darwin-amd64.tar.gz
a90e78d879fd720fd2865870c696aed7952558d5ae75591deced3121f2aab1f9  pig-v1.3.2.darwin-arm64.tar.gz
2fe3a9ffbb6383154dfd25ed79420b210828eabf6a96a8af6e8feb9d744b9559  pig-v1.3.2.linux-amd64.tar.gz
522290aaf14f98f0bae83ce75cc76749f2a4e72742eb5c3cba36a1d2fa4d12c2  pig-v1.3.2.linux-arm64.tar.gz
d6c1cf2c52962045f6bbfb2a669058e7f903088526591d6c939e7723f3928d30  pig_1.3.2-1_amd64.deb
4352385c629b26a1837054445a546da89591499848b557699c2fb70fde9377aa  pig_1.3.2-1_arm64.deb

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

v1.3.1

This is a small maintenance release from v1.3.0 to v1.3.1.

  • PG13 install/build support is removed because PGDG upstream has dropped PG13 archive/distribution.
  • Active supported PostgreSQL major versions are now 14-18.
  • Refresh extension catalog (461 -> 464), including pg_pinyin, pg_eviltransform, and qos.
  • Percona PPG upstream repo is bumped to 18.3.
  • Fix pig build dependency/build sync issues; rsync now uses --keep-dirlinks.
  • In YUM repos, Nginx is split out from infra into its own module index (nginx).

Checksums

196e57c7dd46cdedd90ab75965a766f74aabc3bc23ddc8fb757473647bed7b8f  pig-1.3.1-1.aarch64.rpm
e4bdd52ef635524d5aec95f6a5abd76bd49940584ecbb00bd309a4f9186292ac  pig-1.3.1-1.x86_64.rpm
4f3f9479344c158e1c5edc3003471be6b595c01b7d86104bf676b34f8faadce5  pig-v1.3.1.darwin-amd64.tar.gz
05ae2f550ef5062ab5714518a24bbf52f48079ca6d0190359fae5b8f4cb7f20d  pig-v1.3.1.darwin-arm64.tar.gz
940645497e907e56bfd387a478e580ac930aaa72593cc9d04225a08b37880ec4  pig-v1.3.1.linux-amd64.tar.gz
8b2c204fd6c933a1097cd1cd0ce491b02ba5c0025626a331a199684ceca3ab43  pig-v1.3.1.linux-arm64.tar.gz
1cfc23d147795cc4c1ea9596e6978d79ff1ec34c02850fbb224f7c2844548ea5  pig_1.3.1-1_amd64.deb
e495678ae1c762194a56e8c9969fd2109e7a59830f34a4747039fb978f7820cc  pig_1.3.1-1_arm64.deb

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

v1.3.0

This release is a focused engineering update from v1.2.0 to v1.3.0: 15 commits, 74 files changed, +1184 / -236 LOC.

It hardens the pig build pipeline and extends catalog/alias coverage, increasing total extensions from 451 to 461.

Highlights

  • Build source download improvements (pig build get):
    • Parse multi-source Source fields (whitespace/newline/tab) and deduplicate entries.
    • Add source mappings for agensgraph / agentsgraph.
    • pgedge now downloads both postgresql-17.9.tar.gz and spock-5.0.5.tar.gz.
  • Dependency resolution and install improvements (pig build dep):
    • RPM dependencies can infer PG major from pgmajorversion in spec files; missing spec/control files now return explicit errors.
    • DEB dependency parsing now covers Build-Depends / Build-Depends-Arch / Build-Depends-Indep, including multiline fields, alternatives, arch qualifiers, and build-profile cleanup.
    • PGVERSION placeholders can be expanded from --pg, installed PG majors, or extension metadata.
    • Dependency install failures are downgraded to warnings so batch runs continue.
  • DEB build result semantics fixed (pig build ext/pkg):
    • Successful build command exit code is authoritative; artifact discovery is best-effort warning only.
    • Suppress empty package-list banners on successful no-artifact runs.
    • Partial artifacts are warnings, not failures.
    • Build logs now print real metadata source/version values instead of always composing name-version.
  • Better machine-readable ext operation output (pig ext rm/update):
    • After alias resolution, removed/updated now returns resolved package names instead of extension aliases.
  • Extension catalog and alias updates:
    • New aliases: agensgraph / agens, pgedge, babelfishpg.
    • openhalodb is aligned to PG14 package naming; ivorysqldb naming is aligned.
    • Fork metadata and availability matrix were refreshed in batch (including timescaledb, pgmq, orioledb, documentdb, pg_tde, and babelfishpg_* entries).
  • Engineering and release:
    • Version bumped to v1.3.0 (including a v1.2.1 transition commit), copyright year moved to 2026, and README refreshed for 461 extensions and current alias docs.

Compatibility Notes

  • Structured removed/updated fields in pig ext rm/update now contain package names. Automation that matched extension aliases should update parsing logic.

New Extensions (451 -> 461)

ExtensionVersionNotes
aux_mysql1.5openHalo MySQL compatibility helper (PG14)
gb18030_20221.0IvorySQL charset conversion module
ivorysql_ora1.0IvorySQL Oracle compatibility extension
ora_btree_gin1.0Oracle datatype GIN indexing support
ora_btree_gist1.0Oracle datatype GiST indexing support
pg_get_functiondef1.0Function definition utility
plisql1.0PL/iSQL procedural language
snowflake2.4pgEdge Snowflake-style ID generator
spock5.0.5pgEdge multi-master logical replication extension
lolor1.2.2pgEdge logical-replication-friendly large objects

Full Commit List (v1.2.0..v1.3.0)

  • b8ecf8d bump version string to 1.2.1
  • 55df9a4 build/get: support multi-source parsing and pgedge spock tarball
  • da8e347 add agensgraph and pgedge alias
  • 86edbd7 ext: show resolved package names in rm/update results
  • ef3c905 build/dep: improve rpm/deb dependency resolution
  • 7144e09 ext/catalog: refresh fork metadata and matrix entries
  • befffbf build(deb): treat successful build command as authoritative result
  • 33fd517 build(deb): avoid empty package list banner on successful no-artifact runs
  • 3b450f2 avoid concat ext pkg name with version when download
  • 33847ab fix(ext): satisfy staticcheck S1011 in rm/update
  • b8b917d build(dep): treat dependency install failures as warnings
  • 8110c00 adjust ivorysqldb babelfishpg alias
  • fac9faf bump version to 1.3.0
  • 1f88f06 chore: update copyright year to 2026
  • c804757 v1.3.0

Checksums

e8409cc8165139028323094bebede495d4b0d0a52616d1aecd8c7ecd3fb7471d  pig-1.3.0-1.aarch64.rpm
73645ea4b9ce27b44b2c7f4587e6218cdbbba045f32dd45c942e03cf9020c61e  pig-1.3.0-1.x86_64.rpm
a2d8a14b11606f4a23ca7b929686ff020fc8ce29e7cec21074f710f981aee6d4  pig-v1.3.0.darwin-amd64.tar.gz
fffb94bfc1808b45d8bef3fb63783c1a8e78057e0315ab5b8752088e2c9a555f  pig-v1.3.0.darwin-arm64.tar.gz
27220509c22d26eb8821ac189b1de9c4745adc0a0d91719df7d0b1fc1176b765  pig-v1.3.0.linux-amd64.tar.gz
d124450333e61a5c7d0ed387b13b4087cfd2a81a3fde018232e6bf9b4db6ba0e  pig-v1.3.0.linux-arm64.tar.gz
54f3e0561286b3c0af122137fd475213eff54bc82c69b8b46d1148112ab45b80  pig_1.3.0-1_amd64.deb
beb8ff31d1e64dbfcf6896115e6d200d835fc28bbac5a5b92d75096ef1e68c80  pig_1.3.0-1_arm64.deb

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

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: /ext/
  • 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: /docs/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 - pig

pig CLI command reference overview

The pig CLI provides a comprehensive toolkit for managing PostgreSQL installations, extensions, repositories, and extension builds from source. Use pig help <command> to view command documentation.

  • pig repo: manage software repositories
  • pig ext: manage PostgreSQL extensions
  • pig build: build extensions from source
  • pig install: install packages with the native package manager and translate PostgreSQL aliases
  • pig sty: manage Pigsty installation and Grafana dashboards
  • pig inventory: inspect, edit, validate, and exchange the Pigsty inventory (new in v1.6.0)
  • pig do: run Pigsty administration playbook tasks
  • pig pe: access pg_exporter metrics and configuration
  • pig pg: manage local PostgreSQL servers
  • pig pt: run patronictl transparently with service and config helpers
  • pig pb: manage pgBackRest backup and restore
  • pig pitr: run the full PITR workflow
  • pig context: output an environment context snapshot for humans and agents
  • pig status / update / version: inspect environment status, upgrade pig, and print version information

Overview

pig - The Linux package manager for PostgreSQL

Usage:
  pig [command]

Examples:

  pig repo add -ru            # overwrite existing repo and update cache
  pig install pg18            # install PostgreSQL 18 PGDG packages
  pig install pg_duckdb       # install a specific PostgreSQL extension
  pig install pgactive -v 18  # install extension for a specific PG version

  visit https://pigsty.io/ext/ for details

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

Pigsty Management Commands
  context     Show environment context snapshot
  do          Run admin tasks
  inventory   Inspect, edit, validate, check, and exchange Pigsty Inventory
  patroni     Run patronictl with Pigsty service and config helpers (alias: pt)
  pg_exporter Manage pg_exporter and metrics (alias: pe)
  pgbackrest  Manage pgBackRest backup and restore (alias: pb)
  pitr        Point-in-time recovery using pgBackRest
  postgres    Manage local PostgreSQL server and databases (alias: pg)
  sty         Manage Pigsty installation and controller services

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
      --log-level string   log level: debug, info, warn, error, fatal, panic (default "info")
      --log-path string    log file path, terminal by default
  -o, --output string      output format: text, yaml, json, json-pretty (default "text")

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 repository status
pig repo add    pgdg pigsty -u   # add repositories
pig repo rm     old-repo         # remove repository
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 extension
pig ext scan                     # scan installed extensions
pig ext import  pg_duckdb        # download for offline use
pig ext link    17               # link PG version into 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                   # configure repositories
pig build repo --beta            # configure repositories and add PostgreSQL 19 beta repo
pig build tool                   # install build tools
pig build tool --beta            # install build tools plus PG19 beta build packages
pig build rust -y                # force reinstall Rust (default does not reinstall)
pig build rust -m                # use China mirror mode and write Cargo mirror config
pig build pgrx                   # install PGRX framework
pig build pgrx -b                # include PostgreSQL 19 beta pg_config during auto-detection

# 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 install

Install packages through the system’s native package manager and translate PostgreSQL kernel, extension, and common aliases into package names. Use -n/--no-translation when you need to pass raw system package names directly.

pig install pg_duckdb            # install extension and translate package name
pig install pg18                 # install PostgreSQL 18 kernel package group
pig install nginx htop vim       # install multiple system packages
pig install unknown-package -n   # disable translation and use raw package name
pig install pg18 --plan          # preview installation plan
pig install pg_vector -y         # auto-confirm installation

pig sty

Install the 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 sty list                     # list available Pigsty releases
pig sty get 4.5.0                # download a Pigsty release
pig sty grafana list             # manage Grafana dashboards (info/list/boot/load/init/dump/clean/lang/style)

pig inventory

Inspect, edit, validate, check, and exchange the Pigsty inventory (pigsty.yml) with the CMDB. Root-level command group with alias inv; see pig inventory for details. (New in v1.6.0)

pig inventory status             # inspect the active inventory source
pig inventory list               # list inventory topology and value kinds
pig inventory show               # show verbatim inventory YAML (may contain secrets)
pig inventory edit               # edit the inventory (or one fragment) in $EDITOR
pig inventory validate           # validate one complete static Pigsty inventory
pig inventory check              # check inventory, controller, and target readiness
pig inventory diff other.yml     # compare declarations without emitting values
pig inventory cmdb check         # exchange with the PostgreSQL CMDB (experimental)

pig do

Run Pigsty administration tasks through the corresponding Ansible playbooks.

pig do pgsql-add  <cls> [ip...]       # add cluster or instances
pig do pgsql-rm   <cls> [ip...]       # remove cluster or instances
pig do pgsql-db   <cls> <dbname>      # create or update database
pig do pgsql-user <cls> <username>    # create or update user
pig do pgsql-ext  <cls> [ext...]      # install extensions
pig do node-pkg   <sel> [pkg...]      # install node packages

pig pe

Access PostgreSQL monitoring metrics exposed by pg_exporter. The default endpoint is 127.0.0.1:9630.

pig pe list                    # list available metric types
pig pe get                     # get all pg_ prefixed metrics
pig pe stat                    # show exporter statistics
pig pe reload                  # reload pg_exporter configuration
pig pe --host 127.0.0.1 -p 9630 get

pig context

Output an environment context snapshot covering host, PostgreSQL, Patroni, pgBackRest, and installed extensions. This command is useful for troubleshooting and for automation scripts that need a quick view of the current node.

pig context                      # text output
pig context -o json              # JSON output
pig context -m postgres          # only output PostgreSQL module (host included by default)
pig context -m postgres,!host    # exclude host module

pig pg

Manage the 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 tune -p olap              # generate tuned parameters
pig pg fork dev                  # create a local one-off physical fork
pig pg fork list                 # list local forks
pig pg log tail                  # tail logs in real time

pig pt

Run patronictl transparently to manage Patroni HA clusters. See pig pt for details.

pig pt list pg-meta              # list cluster members (native passthrough)
pig pt show-config pg-meta       # show cluster dynamic config (native passthrough)
pig pt set ttl=60                # local sugar: update Patroni/PG settings
pig pt restart pg-test --pending # apply pending restarts (native passthrough)
pig pt status                    # local: combined service and cluster status
pig pt log -f                    # local: tail logs in real time

pig pb

Manage pgBackRest backup and restore. See pig pb for details.

pig pb info                      # show backup information
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 a specific time
pig pb log tail                  # tail logs in real time

pig pitr

Run orchestrated point-in-time recovery (PITR). See pig pitr for details.

pig pitr -d                      # recover to latest data
pig pitr -t "2025-01-01 12:00:00+08"  # recover to a specific time
pig pitr -I                      # recover to backup consistency point
pig pitr -d --plan               # show execution plan without running
pig pitr -d -y                   # skip confirmation for automation

Helper Commands

pig status                       # show current environment status
pig status -o json               # structured status output
pig update                       # upgrade pig itself to the latest version
pig update -m                    # upgrade using the pigsty.cc mirror
pig update -v X.Y.Z              # upgrade to a version published by the configured repository
pig version                      # show pig version information
pig version -o json              # structured version output

6 - pig repo

Manage software repositories with pig repo

The pig repo command is a comprehensive package repository manager. It can add, remove, create, and manage repositories on RPM systems (RHEL/CentOS/Rocky/Alma) and Debian systems (Debian/Ubuntu).

pig repo - Manage Linux APT/YUM Repo

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

Examples:
  pig repo add -ru                 # add all repo and update cache (brute but effective)
  pig repo add pigsty -u           # gentle version, only add pigsty repo and update cache
  pig repo add node pgdg pigsty    # essential repo to install postgres packages
  pig repo add all                 # all = node + pgdg + pigsty
  pig repo add all extra           # extra module has non-free and some 3rd repo for certain extensions
  pig repo add all -m              # prefer the bundled China-region mirrors
  pig repo update                  # update repo cache
  pig repo create                  # update local repo /www/pigsty meta
  pig repo boot                    # extract /tmp/pkg.tgz to /www/pigsty
  pig repo cache                   # cache /www/pigsty into /tmp/pkg.tgz
CommandDescriptionNotes
repo listPrint available repositories and modules
repo infoShow repository details
repo statusShow current repository status
repo addAdd repositoriesRequires sudo or root
repo setClear, overwrite, and update repositoriesRequires sudo or root
repo rmRemove repositoriesRequires sudo or root
repo updateUpdate repository 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 repository from offline packageRequires sudo or root
repo reloadRefresh repository catalog

Quick Start

# Method 1: clean existing repositories, add all required repos, and update cache (recommended)
pig repo add all --remove --update    # remove old repos, add all required repos, update cache

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

# Method 2: gentle mode - only add required repos and keep current repo configuration
pig repo add pgsql                    # add PGDG and Pigsty PGSQL repositories
pig repo add pigsty --region=china    # add Pigsty repositories with China region
pig repo add pgdg   --region=europe   # add PGDG repositories with Europe region
pig repo add infra  --region=default  # add INFRA repositories with default region
pig repo add all -m                   # explicitly select the bundled China-region mirrors

# If the commands above did not use -u|--update, run this as an extra step
pig repo update                       # update system package cache

Modules

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

ModuleDescriptionRepository List
allAll core modules required to install PGnode + infra + pgsql
pgsqlPGDG + Pigsty PG extensionspigsty-pgsql + pgdg
pigstyPigsty Infra + PGSQL repositoriespigsty-infra, pigsty-pgsql
pgdgPGDG official repositoriespgdg-common, pgdg14-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 repository (deprecated)babelfish
perconaPercona PG + PG_TDEpercona
llvmLLVM toolchain repositoryllvm
kubeKubernetes repositorykubernetes
grafanaGrafana repositorygrafana
haproxyHAProxy repositorieshaproxyd, haproxyu
redisRedis repositoryredis
mongoMongoDB repositorymongo
mysqlMySQL repositorymysql
clickClickHouse repositoryclickhouse
gitlabGitLab repositorygitlab-ce, gitlab-ee

Pig also includes APT/DNF repositories for other databases and systems such as redis, kubernetes, grafana, clickhouse, gitlab, haproxy, mongodb, and mysql.

In general, node (Linux system repositories) and pgsql (PGDG + Pigsty) are required for PostgreSQL installation. The infra repository is optional and contains tools, IvorySQL kernel packages, and similar components. The special all module adds all required repositories at once and is a suitable starting point for most users.

pig repo add all      # add node, pgsql, and infra repositories
pig repo add          # default to the all module when no argument is given
pig repo set          # set clears/backups existing definitions and overwrites with new repo definitions

Repository Definitions

The full repository definition bundled with Pigsty is in cli/repo/assets/repo.yml.

You can create ~/.pig/repo.yml to explicitly modify and override pig’s repository definitions. When editing repository definitions, you can add extra regional mirror URLs under baseurl, such as China or Europe mirrors. When --region is specified, pig first looks for the matching regional URL and falls back to the default URL if the region is unavailable. Since v1.7.0, -m|--mirror explicitly selects the bundled china definitions, including pigsty.cc and the maintained domestic mirrors; it no longer rewrites PGDG URLs through a runtime proxy.

Ordinary EL repositories keep native DNF module filtering. Only definitions that explicitly declare module_hotfixes=1—notably Pigsty and PGDG repositories—override module streams, and the key is removed when rendering EL7 YUM configuration.

repo list

pig repo list lists all repository modules available on the current system.

pig repo list                # list repositories available on current system
pig repo list all            # list all repositories without filtering

repo info

Show detailed information for specific repositories or modules, including URLs, metadata, regional mirrors, and .repo / .list repository file content.

pig repo info pgdg               # show pgdg module information
pig repo info pigsty pgdg        # show multiple modules
pig repo info all                # show all modules

repo status

Show current repository configuration on the system.

pig repo status

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 required repositories (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 (full reset)
pig repo add pgdg --region=china # use China mirror
pig repo add pgsql -m            # explicitly select the China-region mirrors

Options:

  • -r|--remove: remove existing repositories before adding new ones
  • -u|--update: run package cache update after adding repositories
  • -m|--mirror: explicitly select the bundled china repository definitions
  • --region <region>: use regional mirror repositories (default / china / europe)
PlatformModule Location
EL/etc/yum.repos.d/<module>.repo
Debian/etc/apt/sources.list.d/<module>.list

repo set

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

pig repo set                     # replace with default repositories
pig repo set pgdg pigsty         # replace with selected repositories and update
pig repo set all --region=china  # use China mirror
pig repo set -m                  # explicitly select the China-region mirrors

repo set supports the same --region and -m|--mirror region selection as repo add; it always uses overwrite semantics, equivalent to repo add all --remove --update.

repo rm

Remove repository configuration files and back them up.

pig repo rm                      # remove all repositories
pig repo rm pgdg                 # remove selected repository
pig repo rm pgdg pigsty -u       # remove and update cache
PlatformBackup Location
EL/etc/yum.repos.d/backup/
Debian/etc/apt/sources.list.d/backup/

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 a local package repository for offline installation.

pig repo create                  # Linux: /www/pigsty; macOS: current directory
pig repo create /srv/repo        # create at custom location

The current implementation prefers sow when it is available in PATH. On Linux, the SOW backend runs the equivalent of the following with sudo for every target directory:

sow create --pigsty --timeout 10m -- /absolute/repository/path

On macOS, SOW is required, runs without sudo, and the default target is the current directory. On Linux, if sow is not installed, EL falls back to createrepo_c and Debian/Ubuntu falls back to dpkg-scanpackages from dpkg-dev. If neither the preferred backend nor the platform fallback is available, pig repo create fails. A SOW execution error is returned directly rather than retried through the legacy backend. The 10m timeout limits the wait for SOW’s directory lock; it does not cap repository indexing time.

SOW’s --pigsty transaction:

  1. Scans only top-level regular .rpm and .deb files—no recursion and no symlink following.
  2. Removes parsed 32-bit x86 packages (RPM i386/i486/i586/i686, DEB i386) and packages whose binary name is exactly patroni with upstream version exactly 3.0.4.
  3. Builds the applicable RPM/DEB metadata atomically and writes repo_complete last. The marker contains SHA-256 hashes for remaining top-level packages, sorted by basename.

Non-package files and directories are left untouched; malformed package candidates or conflicting package coordinates make the SOW transaction fail closed. The legacy fallback has different cleanup and metadata semantics and writes an MD5 package list to repo_complete. After either backend exits, PIG requires repo_complete to exist as a regular file, but it does not validate the marker contents or hashes. Consumers that use the marker as a delivery gate should perform that verification themselves.

repo cache

Create a 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

Options:

  • -d, --dir: source directory, default /www/
  • -p, --path: output path, default /tmp/pkg.tgz

repo boot

Extract and set up a local repository from an 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

Options:

  • -p, --path: package path, default /tmp/pkg.tgz
  • -d, --dir: target directory, default /www/

repo reload

Refresh repository metadata from GitHub to the latest version.

pig repo reload                  # refresh repository catalog

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

7 - pig ext

Manage PostgreSQL extensions with pig ext

The pig ext command is an all-in-one tool for managing PostgreSQL extensions. It lets you search, install, remove, update, and manage PostgreSQL extensions, and it can also handle PostgreSQL kernel packages.

pig ext - Manage PostgreSQL Extensions

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

Examples:
  pig ext list    [query]      # list & search extension
  pig ext info    [ext...]     # get information of a specific extension
  pig ext status  [-v]         # show installed extension and pg status
  pig ext add     [ext...]     # install extension for current pg version
  pig ext rm      [ext...]     # remove extension for current pg version
  pig ext update  [ext...]     # update extension to the latest version
  pig ext import  [ext...]     # download extension to local repo
  pig ext link    [ext...]     # link postgres installation to path
  pig ext reload               # reload the latest extension catalog data
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 extensions for offline useRequires sudo or root
ext linkLink a PG version into PATHRequires sudo or root
ext reloadRefresh extension catalog

Quick Start

Before installing PostgreSQL extensions, add the required repositories with pig repo add:

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

Then 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

See the extension list for available extensions and package names.

Notes:

  1. If no PostgreSQL version is specified, pig tries to detect the active PostgreSQL installation from pg_config in PATH.
  2. PostgreSQL can be selected by major version (-v) or by pg_config path (-p).
    • With -v, pig uses the default PGDG kernel package path for that major version.
      • EL distros: /usr/pgsql-$v/bin/pg_config
      • DEB distros: /usr/lib/postgresql/$v/bin/pg_config
    • With -p, pig locates PostgreSQL directly from that path.
  3. The extension manager automatically adapts to the OS package format:
    • RPM packages for RHEL/CentOS/Rocky Linux/AlmaLinux
    • DEB packages for Debian/Ubuntu
  4. Extension dependencies are resolved automatically when possible.
  5. Use -y carefully because it auto-confirms prompts.

Pigsty assumes official PGDG kernel packages are installed. If not, install them with:

pig ext install pg18          # install PostgreSQL 18 kernel packages except devel

ext list

List or search extensions in the extension catalog.

pig ext list                     # list all extensions
pig ext list duck                # search extensions containing "duck"
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 filtering is done by passing the category name as the query. Supported categories include: time, gis, rag, fts, olap, feat, lang, type, func, util, admin, stat, sec, fdw, sim, and etl.

Options:

  • -v|--version: filter by PG version
  • --pkg: show package names instead of extension names, listing only leading extensions

Status column:

  • installed: extension is installed
  • available: extension is available but not installed
  • not avail: extension is not available on the current system

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

Use pig ext reload to refresh to the latest catalog. The downloaded catalog is stored at ~/.pig/extension.csv; the latest online catalog is also published at pigsty.io/ext/data/extension.csv.

ext info

Show detailed information for selected extensions.

pig ext info postgis        # show PostGIS details
pig ext info timescaledb    # show TimescaleDB details
pig ext info vector postgis # show multiple extensions

ext avail

Show the extension availability matrix across operating systems, architectures, and PostgreSQL versions.

pig ext avail                     # show package availability on current system
pig ext avail timescaledb         # show timescaledb availability matrix
pig ext avail postgis pg_duckdb   # show multiple extensions
pig ext av pgvector               # show pgvector availability
pig ext matrix citus              # alias for avail

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

ext status

Show installed extension status for the current PostgreSQL instance.

pig ext status              # show installed extensions
pig ext status -c           # include contrib extensions
pig ext status -v 16        # show installed extensions for PG 16

Options:

  • -c|--contrib: include contrib extensions in the result

ext scan

Scan installed extensions for the current PostgreSQL instance.

pig ext scan [-v version]

This command scans the PostgreSQL extension directory and finds extensions that are actually installed.

ext add

Install one or more PostgreSQL extensions. Same-level aliases for pig ext add include pig ext install, pig ext ins, and pig ext a. The top-level pig install command is a separate native package-manager wrapper that also supports PostgreSQL and extension alias translation.

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
pig ext add postgis --plan       # preview install plan without executing

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

# Install PostgreSQL kernel packages
pig ext install pgsql            # install latest PostgreSQL kernel
pig ext a pg18                   # install PostgreSQL 18 kernel packages
pig ext ins pg16                 # install PostgreSQL 16 kernel packages
pig ext install pg15-core        # install PostgreSQL 15 core packages
pig ext install pg14-main -y     # install PG 14 + common extensions (vector, repack, wal2json)

Options:

  • -v|--version: specify PG major version
  • -y|--yes: auto-confirm installation
  • --plan: preview the install plan without running package-manager commands

ext rm

Remove one or more PostgreSQL extensions.

pig ext rm pg_duckdb             # remove pg_duckdb
pig ext rm pg_duckdb -v 18       # remove PG 18 package
pig ext rm pgvector -y           # auto-confirm removal
pig ext rm pgvector --plan       # preview remove plan without executing

Options:

  • -v|--version: specify PG major version
  • -y|--yes: auto-confirm removal
  • --plan: preview the remove plan without running package-manager commands

ext update

Update explicitly selected installed extensions to the latest version. For safety, pig ext update with no arguments is a no-op; you must name the targets explicitly.

pig ext update                   # no-op: explicit targets are required
pig ext update pg_duckdb         # update one extension
pig ext update postgis timescaledb  # update multiple extensions
pig ext update pg_duckdb -y      # auto-confirm update
pig ext update pg_duckdb -m      # select the built-in China-region sources

Options:

  • -v|--version: specify PG major version
  • -y|--yes: auto-confirm update
  • -m|--mirror: prefer the pigsty.cc mirror as the update source

ext import

Download extension packages into a local repository for offline installation.

pig ext import postgis                # import PostGIS packages
pig ext import timescaledb pg_cron    # import multiple extension packages
pig ext import pg16                   # import PostgreSQL 16 packages
pig ext import pgsql-common           # import common utility packages
pig ext import -d /www/pigsty postgis # import to a selected path

Options:

  • -d|--repo: local repository directory (default: /www/pigsty)

ext link

Link a selected PG version into the system PATH.

pig ext link 18                  # link PG 18 to PATH
pig ext link pg17                # strip the pg prefix and link PG 17
pig ext link 16                  # link PG 16 to /usr/pgsql
pig ext link /usr/pgsql-16       # link from a selected path to /usr/pgsql
pig ext link polar               # link PolarDB / PolarPG installation
pig ext link /usr/polar-17       # link from a selected PolarDB path
pig ext link null                # remove current PostgreSQL link
pig ext link none                # null / none / nil / nop / no all remove the link

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

ext reload

Refresh extension metadata.

pig ext reload                   # refresh extension catalog

The updated catalog is stored at ~/.pig/extension.csv.

8 - pig build

Build PostgreSQL extensions from source with pig build

The pig build command simplifies the full workflow for building PostgreSQL extensions from source. It provides build infrastructure setup, dependency management, and compilation environments for standard and custom PostgreSQL extensions across supported operating systems.

pig build - Build Postgres Extension

Environment Setup:
  pig build spec                   # init build spec and directory (~ext)
  pig build repo                   # init build repo (=repo set -ru)
  pig build repo --beta            # init build repo with PostgreSQL beta repo
  pig build tool  [mini|full|...]  # init build toolset
  pig build rust  [-y] [-m]        # install Rust toolchain
  pig build pgrx  [-v <ver>] [-b]  # install & init pgrx (0.19.1)
  pig build proxy [id@host:port ]  # init build proxy (optional)

Package Building:
  pig build pkg   [ext|pkg...]     # complete pipeline: get + dep + ext
  pig build get   [ext|pkg...]     # download extension source tarball
  pig build dep   [ext|pkg...]     # install extension build dependencies
  pig build ext   [ext|pkg...]     # build extension package

Quick Start:
  pig build spec                   # setup build spec and directory
  pig build pkg citus              # build citus extension
CommandDescriptionNotes
build specInitialize build specification directory
build repoInitialize required repositoriesRequires 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 tarballs
build depInstall extension build dependenciesRequires sudo or root
build extBuild extension packagesRequires sudo or root
build pkgComplete pipeline: get, dep, extRequires sudo or root

Quick Start

The fastest way to set up a build environment and build an extension:

# Step 1: initialize build specs
pig build spec

# Step 2: build an extension with the complete pipeline
pig build pkg citus

# Built packages will be placed under:
# - EL: ~/ext/pkg/ (also available via ~/rpmbuild/RPMS/)
# - Debian: ~/ext/pkg/ (also available via ~/debbuild/DEBS/)

For finer control:

# Environment setup
pig build spec                   # initialize build specs
pig build repo                   # configure repositories
pig build repo --beta            # configure repositories and add PostgreSQL 19 beta repo
pig build tool                   # install build tools
pig build tool --beta            # install build tools plus PG19 beta build packages

# Build steps
pig build get citus              # download source
pig build dep citus              # install dependencies
pig build ext citus              # build package

# Or run all three steps at once
pig build pkg citus              # get + dep + ext

Build Infrastructure

Directory Layout

~/ext/                           # real working directory
├── pkg/                         # built package output
├── src/                         # source tarball downloads
├── log/                         # build logs
└── tmp/                         # temporary files

~/rpmbuild/                      # EL build directory
├── RPMS -> ~/ext/pkg            # RPM output symlink
├── SOURCES -> ~/ext/src         # source symlink
├── SPECS/
├── BUILD/
├── BUILDROOT/
└── SRPMS/

~/debbuild/                      # Debian / Ubuntu build directory
├── DEBS -> ~/ext/pkg            # DEB output symlink
├── SOURCES -> ~/ext/src         # source symlink
├── SPECS/
└── BUILD/

Build output locations:

  • EL systems: ~/ext/pkg/, also accessible through ~/rpmbuild/RPMS/
  • Debian systems: ~/ext/pkg/, also accessible through ~/debbuild/DEBS/

build spec

Set up build specifications and directory layout.

pig build spec                   # initialize ~/ext at the default location
pig build spec -f                # force re-download of the build spec tarball
pig build spec -m                # prefer the pigsty.cc China mirror

What it does:

  1. Downloads the RPM or DEB build specification tarball.
  2. Creates ~/ext/{pkg,src,log,tmp} and the platform build directory.
  3. Links RPMS/DEBS and SOURCES to ~/ext/pkg and ~/ext/src.
  4. Syncs makefiles, specs, and Debian packaging files with incremental rsync.

Working directory: ~/ext/ stores sources, packages, logs, and temporary files. The platform packaging directory is ~/rpmbuild/ or ~/debbuild/.

build repo

Initialize package repositories required for building extensions.

pig build repo                   # equivalent to: pig repo set -ru
pig build repo -m                # select the built-in China-region sources
pig build repo --beta            # also enable the PostgreSQL 19 beta repo module

What it does: initializes build repositories with pig repo set -ru: remove old repositories, add required repositories, and refresh package caches. --beta/-b appends the beta module for explicit PostgreSQL 19 beta builds; the stable default path still uses PG14-18.

Options:

  • -b|--beta: additionally enable PostgreSQL beta repository modules
  • -m|--mirror: select the built-in china region sources

build tool

Install development tools and compilers.

pig build tool                   # install default toolset
pig build tool mini              # minimal toolset
pig build tool full              # full toolset
pig build tool rust              # add Rust development tools
pig build tool --beta            # add PG19 beta build dependencies

Toolsets:

  • Minimal (mini): GCC/Clang compilers, Make, and generic build essentials; does not install PostgreSQL server/devel packages.
  • Default / full: compilers, development libraries, packaging tools such as rpmbuild and dpkg-dev, plus stable PG14-18 build dependencies.
  • --beta: additionally installs PG19 beta server/devel build packages on top of the default toolset.

build rust

Install the Rust toolchain required by Rust-based extensions.

pig build rust                   # install with confirmation
pig build rust -y                # force reinstall Rust toolchain
pig build rust -m                # use China mirror mode and write Cargo mirror config

Installed components: Rust compiler (rustc), Cargo, Rust standard library, and development tools. -m|--mirror uses mirror mode and writes rsproxy.cn Cargo configuration.

build pgrx

Install and initialize PGRX, the PostgreSQL extension framework for Rust.

pig build pgrx                   # install latest stable version (0.19.1)
pig build pgrx -v 0.19.1         # install a specific pgrx version
pig build pgrx --pg 18,17,16     # initialize pgrx for selected PG versions
pig build pgrx --pg init         # run cargo pgrx init without PG arguments
pig build pgrx -b                # include PostgreSQL 19 beta pg_config during auto-detection

Prerequisites: Rust toolchain and PostgreSQL development headers must be installed first. Default auto-detection only covers stable PG14-18; use -b|--beta, or explicitly pass --pg 19, when PG19 beta is required.

build proxy

Configure a proxy for build environments with restricted internet access.

pig build proxy                  # interactive setup
pig build proxy user@host:8080   # use default local endpoint 127.0.0.1:12345
pig build proxy user@host:8080 127.0.0.1:1080

build get

Download extension source tarballs.

pig build get citus              # single extension
pig build get citus pgvector     # multiple extensions
pig build get pdu pgdog          # use built-in source aliases
pig build get citus -f           # re-download even if the file already exists
pig build get citus -m           # prefer the pigsty.cc China mirror

Arguments to pig build get are extension names, package names, or source filenames. Unknown names are treated as source filenames. It does not expand all or std into built-in package sets; list the target package names explicitly for batch downloads.

Some source packages do not map directly to extension names, so pig build get includes special aliases for direct source downloads.

pig build get pdu                # download pdu-3.0.25.12.tar.gz
pig build get pgdog              # download pgdog-0.1.32.tar.gz
pig build get pgedge             # download both PostgreSQL and spock sources
pig build get onesparse          # download onesparse, graphblas, and lagraph

Common special source aliases include: babelfishpg / babelfish, agensgraph / agentsgraph, oriolepg / orioledb, cloudberry, pgedge, pdu, pgdog, rdkit, onesparse, and libfepgutils.

build dep

Install dependencies required to build extensions.

pig build dep citus              # single extension
pig build dep citus pgvector     # multiple extensions
pig build dep citus --pg 17,16   # for selected PG versions

Options:

  • --pg: specify one or more PostgreSQL major versions. If omitted, pig infers versions from extension metadata or local PostgreSQL installations.

build ext

Compile extensions and create installation packages.

pig build ext citus              # build one extension
pig build ext citus pgvector     # build multiple extensions
pig build ext citus --pg 17      # build for a selected PG version
pig build ext citus -s           # include debug symbols (RPM only)

Options:

  • --pg: specify one or more PostgreSQL major versions.
  • -s|--symbol: build debug symbol packages (RPM only).

build pkg

Run the complete build pipeline: download, dependency installation, and build.

pig build pkg citus              # build one extension
pig build pkg citus pgvector     # build multiple extensions
pig build pkg citus --pg 17,16   # build for multiple PG versions
pig build pkg citus -s           # include debug symbols
pig build pkg citus -m           # prefer the pigsty.cc China mirror for sources

Options:

  • --pg: specify one or more PostgreSQL major versions.
  • -s|--symbol: build debug symbol packages (RPM only).
  • -m|--mirror: prefer the pigsty.cc mirror when downloading source files.

Common Workflows

Workflow 1: Build a Standard Extension

# 1. Set up the build environment once
pig build spec
pig build repo
pig build tool

# 2. Build the extension
pig build pkg pg_partman

# 3. Install the built package
sudo rpm -ivh ~/ext/pkg/pg_partman*.rpm  # EL
sudo dpkg -i ~/ext/pkg/*partman*.deb     # Debian

Workflow 2: Build a Rust Extension

# 1. Set up Rust environment
pig build spec
pig build tool
pig build rust                   # add -y only if you need to force reinstall
pig build rust -m                # use mirror mode in China network environments
pig build pgrx

# 2. Build Rust extension
pig build pkg pgmq

# 3. Install
sudo pig ext add pgmq

Workflow 3: Build Multiple Versions

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

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

Troubleshooting

Build Tools Not Found

# Install build tools
pig build tool

# For specific compiler sets
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 --locked [email protected]

# Reinitialize PGRX
cargo pgrx init

# When PG19 beta is required
pig build repo --beta
pig build tool --beta
pig build pgrx -b

Extension Build Matrix

Common Extensions to Build

ExtensionTypeBuild TimeComplexitySpecial Requirements
pg_repackCFastSimpleNone
pg_partmanSQL/PLPGSQLFastSimpleNone
citusCMediumMediumNone
timescaledbCSlowComplexCMake
postgisCVery slowComplexGDAL, GEOS, Proj
pg_duckdbC++MediumMediumC++17 compiler
pgroongaCMediumMediumGroonga libraries
pgvectorCFastSimpleNone
plpython3CMediumMediumPython development
pgrx extensionsRustSlowComplexRust, PGRX

9 - pig sty

Manage Pigsty installation with pig sty

pig can also be used as a CLI tool for Pigsty, a batteries-included free PostgreSQL RDS solution. It brings HA, PITR, monitoring, infrastructure as code (IaC), and rich extension support to your PostgreSQL clusters.

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

  pig sty init    [-mpfvd]        # install pigsty (~/pigsty by default)
  pig sty boot    [-rmpk]         # native controller bootstrap
  pig sty conf    [mode] [flags]  # native Inventory-aware configuration
  pig sty deploy                  # use pigsty to deploy everything (CAUTION!)
  pig sty get                     # download pigsty source tarball
  pig sty list                    # list available pigsty versions
  pig sty grafana <verb>          # manage grafana dashboards (native HTTP)

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 bootBootstrap the Pigsty controllerAuto-elevates when needed
sty confGenerate and validate InventoryNative Go workflow
sty deployRun deployment playbook
sty listList available Pigsty versions
sty getDownload Pigsty source tarball
sty grafanaManage Grafana dashboards (alias gf)New in v1.6.0

Since v1.8.0, pig sty boot and pig sty conf are native Go workflows. They no longer invoke Pigsty’s legacy bootstrap or configure shell scripts. Since v1.6.0, the former pig sty edit / validate / check commands moved to the root-level pig inventory command group, and the experimental pig sty dashboard was replaced by pig sty grafana.

Quick Start

Use pig sty to bootstrap and deploy Pigsty on the current node.

pig sty boot                     # prepare the controller; auto-elevates when needed
pig sty conf -g                  # generate and validate pigsty.yml
pig inventory edit              # optional: review and adjust the Inventory
pig sty deploy                   # run deployment playbook

See the detailed setup guide: https://pigsty.io/docs/setup/install/

sty boot initializes a missing default ~/pigsty tree on a best-effort basis. Use pig sty init first when you need to select an explicit Pigsty version or installation path.

sty init

Download and install the Pigsty distribution into ~/pigsty.

pig sty init                   # install latest version to ~/pigsty
pig sty init -f                # install and overwrite existing pigsty directory
pig sty init -m                # prefer the pigsty.cc mirror for downloads
pig sty init -p /tmp/pigsty    # install to selected directory /tmp/pigsty
pig sty init -v 3.4            # fetch and install selected version v3.4.1
pig sty init 3                 # fetch and install latest v3 major version

Options:

  • -p|--path: target installation directory, default ~/pigsty
  • -f|--force: force overwrite of existing pigsty directory
  • -m|--mirror: prefer the pigsty.cc mirror
  • -v|--version: Pigsty version
  • -d|--dir: download directory, default /tmp

sty boot

Bootstrap the Pigsty controller with the native Go workflow. The command prepares a usable Ansible environment, supports online and offline repositories, repairs common controller prerequisites, and reports a structured result. It never delegates to Pigsty’s legacy bootstrap script and does not require curl, wget, tar, or gzip for package download and extraction.

sudo pig sty boot                         # online bootstrap with the default region
sudo pig sty boot -r china                # use China-region repositories
sudo pig sty boot -m                      # equivalent to --region china
sudo pig sty boot -k                      # preserve existing repository definitions
sudo pig sty boot -p /path/to/pkg.tgz     # use an explicit offline package
sudo pig sty boot -p https://host/pkg.tgz # download and use an offline package
sudo pig sty boot -o json                 # machine-readable result and warnings

You may invoke the command without sudo. Pig resolves and downloads an explicit source first, then re-executes itself through sudo once when root access is required. Set PIG_NO_SUDO=1 to disable automatic elevation, or PIG_NON_INTERACTIVE=1 to make the sudo attempt non-interactive.

Bootstrap stages

The native workflow performs these stages:

  1. On Debian 12/13, check and repair en_US.UTF-8 when possible so Ansible can start.
  2. Execute ansible-playbook, discover its Python interpreter, and verify yaml, jmespath, and either cryptography or OpenSSL. A binary that cannot actually run is not considered ready.
  3. Resolve the repository source, prepare offline content when selected, and install the lean controller package set only when Ansible is missing or unusable.
  4. Verify Ansible again after package installation and retry locale preparation when packages may have made locale tools available.
  5. Probe controller helpers, repair key-based SSH to 127.0.0.1 for the invoking admin user, and initialize a missing default ~/pigsty tree when possible.

Explicit, automatically discovered, and already committed offline sources are prepared even when Ansible is already usable. This makes sty boot suitable for staging an offline repository on an otherwise ready controller.

Source selection and modes

The result records one of four bootstrap modes:

ModeMeaning
readyAnsible was already usable and no offline source needed preparation.
offlineAn explicit, trusted automatic, or committed offline repository was selected.
onlineRegional online repositories were configured to repair the controller.
existingAn online refresh failed under --keep, and existing repository definitions were successfully used as a fallback.

Source precedence and safety rules are deterministic:

  • --path accepts a local archive or an HTTP(S) URL. URL credentials are rejected, and a bad explicit source is a hard error rather than an online fallback.
  • An automatically discovered /tmp/pkg.tgz must be a regular, non-group/world-writable file owned by root or the invoking sudo user. Unsafe candidates are ignored with a warning.
  • A completed /www/pigsty repository takes precedence over a selected package. If both exist, the committed repository is reused and the package is left untouched with a warning.
  • Archives are downloaded and extracted by Pig itself. If /www does not exist, Pig creates /data/nginx and the expected /www -> /data/nginx symbolic link before committing content.

Offline mode enables only the strict pigsty-local repository. Online mode configures the selected region, installs Pigsty’s embedded signing key with repository signature checks enabled, and installs the node and pigsty controller modules.

Repository transaction and failure boundary

By default, repository definitions are backed up before replacement. If repository setup or package installation fails, Pig attempts to restore the backup and reports whether rollback succeeded or failed. --keep changes the policy to additive operation: existing definitions are preserved, online refresh may fall back to them, and no replacement rollback is required.

Invalid explicit sources, unsupported package management when installation is required, repository/package failures, and an unusable post-install Ansible runtime are command failures. Locale repair, optional helper probes, localhost SSH repair, and Pigsty tree initialization are advisory finishing steps; their failures are retained as warnings in an otherwise usable result.

Options:

  • -r|--region: region, such as default, china, europe
  • -m|--mirror: equivalent to --region china; mutually exclusive with --region
  • -p|--path: offline package file or HTTP(S) URL; an invalid explicit source is a hard error
  • -k|--keep: preserve existing repositories instead of replacing them

Structured output

Use the global -o json or -o yaml flag for automation. The payload kind is pig.sty.boot/v2 and includes the Ansible state, selected mode and package manager, repository policy and rollback result, source and repository paths, locale, localhost SSH and Pigsty tree initialization status, whether the machine changed, warnings, and these recommended next steps:

pig sty conf -g
pig inv edit
pig sty deploy

Dynamic progress is suppressed in structured mode, so stdout remains machine-readable.

See: https://pigsty.io/docs/setup/offline/#bootstrap

sty conf

Generate Pigsty Inventory through the native Go workflow. sty conf reads one template below <PIGSTY_HOME>/conf, applies bounded structural mutations, validates the complete candidate, and atomically writes an owner-only Inventory. It does not invoke or fall back to ./configure.

pig sty conf                         # use conf/meta.yml and write pigsty.yml
pig sty conf -g                      # generate random passwords (recommended)
pig sty conf rich                    # positional mode selects conf/rich.yml
pig sty conf -c ha/full              # equivalent flag form; do not combine both forms
pig sty conf ha/trio --ip 10.0.0.10,10.0.0.11,10.0.0.12
pig sty conf --domain infra.example.com
pig sty conf rich -v 18              # request PostgreSQL 18 for a generic template
pig sty conf -r china -s             # China region, placeholder IP, no admin preflight
pig sty conf -x                       # materialize proxy environment variables
pig sty conf full -g -O ha.yml       # custom owner-only output file
pig sty conf -n --ip 10.0.0.10 -o json

The default mode is meta; pig sty c and pig sty configure are command aliases. Note that -O selects the Inventory output file, while the global lowercase -o selects text, JSON, or YAML command output.

Template and output safety

  • A mode is a slash-separated relative name below <PIGSTY_HOME>/conf; .yml is optional. Absolute paths, traversal, empty segments, and path escape are rejected.
  • Relative output paths are resolved below <PIGSTY_HOME>; absolute output paths are retained.
  • The destination cannot alias the source through the same path, an existing symlink, a symlinked parent, or a hard link. An existing output symlink is always refused.
  • Pig parses the source and rejects conflicting IP mappings before running external preflight checks. A parse, mutation, preflight, or validation failure leaves the destination unchanged.
  • A successful result is written atomically with mode 0600.

Structural mutations

The command edits parsed YAML structures and bounded scalar tokens rather than applying broad text substitutions:

InputNative behavior
--ip A,B,...Maps up to ten distinct addresses, in order, to 10.10.10.10 through 10.10.10.19. Replacement is simultaneous, so swaps are safe and unrelated VIPs remain intact.
no --ipDetects local interfaces. Interactive mode asks when selection is ambiguous; --non-interactive or closed stdin fails with guidance to pass --ip.
--domain NAMEReplaces only the exact i.pigsty token, not names such as cli.pigsty or i.pigsty.cc; NAME must be a valid DNS domain.
small controllerWhen detected CPU count is below four, rewrites node_tune: oltp and pg_conf: oltp.yml to their tiny profiles.
--region REGIONUpdates all.vars.region for non-default regions. china also activates template-provided Docker and pip mirrors, but never invents values absent from the template.
--proxyWrites non-empty HTTP_PROXY/http_proxy, HTTPS_PROXY (falling back to ALL_PROXY), ALL_PROXY, and NO_PROXY values into all.vars.proxy_env; a safe default no-proxy list is used when needed.
--version MAJORUpdates generic templates for PostgreSQL 14-18, or explicit 19 beta, and selects the matching locale. Version-pinned mssql, polar, and pgNN modes keep their template version and report a warning.
--generateGenerates one 24-character value per known credential identifier and consistently replaces its active values and documented placeholders.

An IP mapping that would collide with an unreplaced Inventory key is rejected as an invalid argument. A supplied address with no matching placeholder slot is retained in structured output as a discarded-IP warning rather than being silently ignored.

For PostgreSQL 19 beta, Pig also enables the beta repository after pgsql when the template contains the expected repository list. Modes below conf/build/ intentionally bypass IP mapping and controller-admin preflight so build templates remain portable.

Active password identifiers are grafana_admin_password, pg_admin_password, pg_monitor_password, pg_replication_password, patroni_password, haproxy_admin_password, minio_secret_key, and etcd_root_password. Generation also covers the documented DBUser.Meta, DBUser.Viewer, S3User.Backup, S3User.Meta, S3User.Data, DBUser.Supa, and Vibe.Coding placeholders. Repeated occurrences of one identifier receive the same generated value.

Options:

  • -c|--conf: template mode, equivalent to positional [mode]; the two forms are exclusive
  • --ip: up to ten distinct comma-separated IPv4 addresses
  • --domain: replace the exact i.pigsty placeholder with a valid DNS domain
  • -v|--version: PostgreSQL major version, 18/17/16/15/14; 19 beta can be specified explicitly
  • -r|--region: upstream repository region, such as default/china/europe
  • -m|--mirror: equivalent to --region china; mutually exclusive with --region
  • -O|--output-file: output config file path, default pigsty.yml
  • -s|--skip: keep the placeholder IP and skip admin SSH/sudo preflight; exclusive with --ip
  • -p|--port: SSH port
  • -x|--proxy: write non-empty proxy environment variables into all.vars.proxy_env
  • -n|--non-interactive: refuse ambiguous IP selection instead of prompting
  • -g|--generate: replace known demo credentials with random 24-character values

Preflight and validation

Unless --skip is used, Pig inspects the kernel, architecture, package manager, platform vendor, controller resources, sudo/admin access, localhost SSH, and Ansible availability. --port is used for the SSH check. These diagnostics are returned as actionable warnings where the Inventory can still be generated; invalid arguments and unsafe configuration transformations remain errors.

The rendered candidate must pass Pig’s native Inventory validation. When ansible-inventory is available, Pig also runs a bounded external parse before committing the file. --skip preserves placeholder IPs and bypasses admin SSH/sudo preflight, but it does not disable template parsing, safe mutation, Inventory validation, or atomic writing.

Structured output

With global -o json or -o yaml, the payload kind is pig.sty.configure/v1. It reports the mode, source and output paths, region, chosen primary address, applied and discarded IPs, domain, SSH port, requested and effective PostgreSQL versions, native-workflow marker, generated secret identifiers, and warnings. Secret values are never printed.

See: https://pigsty.io/docs/setup/install/#configure

sty deploy

Deploy Pigsty with the deploy.yml playbook.

pig sty deploy       # run deploy.yml, falling back to install.yml if not found
pig sty d            # short alias
pig sty de           # short alias

This command runs the deploy.yml playbook from your Pigsty installation directory. For backward compatibility, if deploy.yml does not exist but install.yml exists, install.yml is used instead.

Warning: This operation modifies your system, and invocation is explicit consent — deploy starts immediately without a --yes gate; use Ctrl+C to interrupt a mistaken run. (The pig sty install / ins aliases were removed in v1.6.0.)

sty list

List available Pigsty versions.

pig sty list                     # list available versions

sty get

Download the Pigsty source tarball.

pig sty get                      # download latest version
pig sty get v3.4.0               # download selected version
pig sty get -m                   # prefer the pigsty.cc mirror

sty grafana

Since v1.6.0, pig sty grafana (alias gf) manages Grafana dashboards through the native HTTP API, replacing the experimental pig sty dashboard. The PATH argument may select the grafana root, one direct folder, or one dashboard JSON file; without PATH, pig resolves <PIGSTY_HOME>/files/grafana and never falls back to the current directory.

pig sty grafana info             # check Grafana health, authentication, and basic info
pig sty grafana list             # list all dashboards in the active organization
pig sty grafana boot             # bootstrap Grafana around the existing pigsty dashboards
pig sty grafana init             # load the complete dashboard corpus, then bootstrap
pig sty grafana load [PATH]      # load dashboards selected by a local path
pig sty grafana dump [PATH]      # export remote dashboards into a local path
pig sty grafana clean [PATH]     # delete dashboards selected by a path (--dry-run/--yes)
pig sty grafana lang zh-Hans     # set the organization and current-user language
pig sty grafana style            # set the organization and current-user visual style

Connection and credentials:

OptionDescription
--endpointGrafana origin and optional path prefix (default http://i.pigsty/ui)
--usernameGrafana API username
--passwordGrafana API password (insecure: visible in process arguments and shell history)
--password-fileOwner-only file containing the password (recommended)

Password resolution order: --password → --password-file → the GRAFANA_PASSWORD environment variable → all.vars.grafana_admin_password from the inventory. The HTTP client enforces timeouts and response-size limits, refuses redirects, and verifies TLS certificates by default.

Legacy dashboards and schema v2 resources

load and init accept both classic Grafana dashboard JSON and the resource form whose identity is exactly:

{"apiVersion":"dashboard.grafana.app/v2","kind":"Dashboard","metadata":{"name":"pgsql-overview","namespace":"default"},"spec":{}}

The two formats are not flattened into one another during loading:

  • Classic JSON takes its UID from top-level uid and uses the legacy dashboard API.
  • Schema v2 takes its UID from metadata.name, defaults a missing namespace to default, requires an object-valued spec, and uses Grafana’s dashboard resource API.
  • The JSON filename (without .json) must match the resolved UID. Only one local folder layer is allowed; its directory name becomes the Grafana folder UID.
  • For schema v2, PIG preserves spec and the grafana.app/message annotation, sets grafana.app/folder from the local folder, and deliberately removes server-managed metadata/status before upsert.
  • dump preserves schema v2 only when the target file already exists locally in v2 form; it then fetches the native v2 resource using that file’s namespace. A new dump target defaults to classic JSON. Local-only files are never deleted by dump.

Other dashboard.grafana.app/* versions or malformed resource envelopes are rejected instead of being silently treated as classic dashboards. Format preservation therefore depends on keeping the existing local file when round-tripping v2 resources.

10 - pig inventory

Inspect, edit, validate, check, and exchange the Pigsty Inventory

The pig inventory command group (alias pig inv), introduced in v1.6.0, provides lossless inspection, editing, validation, and readiness checks for the Pigsty inventory (pigsty.yml), plus an experimental cmdb subtree that exchanges the inventory with the PostgreSQL CMDB.

The lossless engine preserves YAML comments, formatting, key order, anchors, and line endings byte-for-byte; edit re-parses the whole document before writing and writes atomically — invalid YAML can never reach disk.

Inspect, edit, validate, check, and exchange Pigsty Inventory

Usage:
  pig inventory [command]

Aliases:
  inventory, inv

Available Commands:
  check       Check Inventory, controller, and opt-in target readiness
  cmdb        Exchange Inventory with Pigsty's existing PostgreSQL CMDB (experimental)
  diff        Compare declarations without emitting Inventory values
  edit        Edit Inventory or one selected YAML fragment
  list        List ordered Inventory topology and value kinds
  show        Show verbatim Inventory YAML (may contain secrets)
  status      Inspect the active Inventory source without executing it
  validate    Validate one complete static Pigsty Inventory
CommandAliasDescription
inv statusInspect the active inventory source (without executing)
inv listlsList inventory topology and value kinds (--depth)
inv showShow verbatim inventory YAML (may contain secrets)
inv editeEdit the inventory or one fragment in $EDITOR
inv validatevValidate one complete static Pigsty inventory
inv checkckCheck inventory, controller, and target readiness
inv diffCompare declarations of two inventories (value-free)
inv cmdbExchange with the PostgreSQL CMDB (experimental)

The inventory path comes from pig’s config resolution (the global -i/--inventory flag or the Pigsty home directory), and every command supports -o json|yaml structured output.

Quick Start

pig inventory status                     # which inventory source is active?
pig inventory list                       # browse the inventory topology
pig inventory edit pg-meta               # edit only the pg-meta cluster fragment
pig inventory validate                   # full validation
pig inventory check -p ssh --sudo        # add SSH/sudo readiness probes
pig inventory diff /path/other.yml       # compare declarations with another file

Selectors

list / show / edit accept an optional selector that addresses one fragment:

pig inv e                                # the whole inventory
pig inv e vars                           # all.vars global parameters
pig inv e pg-meta                        # one cluster group
pig inv e pg-meta.vars.pg_databases      # a nested key path
pig inv e 'pg-meta.hosts["10.10.10.10"]' # dotted keys (e.g. IPs) via quoted brackets

inv edit

Opens the selected fragment in $EDITOR. After you save and exit, pig re-applies indentation and newline style → re-parses the whole document (aborting and keeping the temp file on failure) → checks that the on-disk file was not concurrently modified → writes atomically.

pig inventory edit                       # edit the whole inventory
pig inv e pg-meta                        # edit one fragment
pig inv e vars --from vars.yml           # replace the fragment from a file (or stdin: -)
OptionDescription
--fromReplace the selected fragment from a regular file or stdin (-), skipping the editor

Note: after a successful edit, pig tightens the inventory file mode to 0600 (the file may contain database passwords and other secrets); the result reports this via the mode_tightened field. If other users or tools read the file directly, adjust permissions or ownership accordingly.

inv validate

Validates one complete static Pigsty inventory: YAML structure, Ansible conventions, and Pigsty semantics (such as admin_ip, the infra group, and IPv4 host keys) are checked layer by layer; diagnostics never echo sensitive values.

pig inventory validate                   # validate the active inventory
pig inv v ./pigsty.yml                   # validate a specific file
pig inv v --strict                       # treat warnings as failures
pig inv v --ansible                      # cross-check with ansible-inventory
pig inv v --strict -o json               # structured output (CI-friendly)
OptionDescription
--strictTreat validation warnings as failures
--ansibleAlso parse with a bounded ansible-inventory adapter
--timeoutTimeout for --ansible compatibility validation (default 10s)

validate is a Pigsty-semantics validator, not a generic Ansible linter; its rules stay aligned with Pigsty’s own bin/validate (intentionally stricter in a few places). Also note that parsing outright rejects duplicate keys and multi-document YAML — such files cannot even be used with show / edit.

inv check

Readiness checks on top of static validation: by default only the inventory and local controller conditions are checked; add probes with --profile.

pig inventory check                      # inventory + controller basics
pig inv ck -p ansible                    # add ansible environment checks
pig inv ck -p network                    # add network reachability probes
pig inv ck -p ssh --user admin           # add SSH login probes
pig inv ck -p ssh --sudo                 # SSH probes plus sudo -n validation
OptionShortDescription
--profile-pAdditional probes: ansible / network / ssh
--userExplicit SSH user (defaults to the controller user)
--portTCP/SSH target port (default 22)
--sudoWith the ssh profile, also observe sudo -n true

inv cmdb (experimental)

Experimental: interfaces and behavior may change.

pig inventory cmdb exchanges the inventory with Pigsty’s existing CMDB schema (files/cmdb.sql, the pigsty / pglog schemas) over a native PostgreSQL driver. Connection resolution: -d/--database (database name, URI, or libpq conninfo) → METADB_URL environment variable → service=meta.

SubcommandDescription
checkRead-only: verify CMDB projections, optionally against the static inventory
initApply the cmdb.sql baseline (--yes required over existing schemas; --plan preview)
loadReplace all CMDB declaration rows from the static inventory (single transaction; --yes; --plan/--strict)
dumpExport the CMDB as a static inventory file (--force to overwrite a differing target)
enableGuarded switch of the ansible.cfg inventory to the CMDB (inventory.sh)
disableSwitch back to the static pigsty.yml (both support --plan; atomic, rollback-safe writes)
pig inventory cmdb check                 # verify CMDB state
pig inventory cmdb init --plan           # preview the init plan
pig inventory cmdb load -y               # load the static inventory into the CMDB
pig inventory cmdb dump -f pigsty.yml    # export the CMDB as a static inventory
pig inventory cmdb enable                # switch the ansible inventory source to the CMDB
pig inventory cmdb disable               # switch back to the static pigsty.yml

Note: init applies the cmdb.sql baseline directly and does not take a backup of an existing CMDB — back it up yourself before running against real data; load replaces all declaration rows. Destructive operations are gated by a target-fingerprint confirmation, and structured output mode requires an explicit --yes.

11 - pig postgres

Manage local PostgreSQL server with pig postgres subcommand

The pig pg command (alias pig postgres) manages local PostgreSQL servers and databases. It wraps local primitives such as pg_ctl, psql, and vacuumdb; use pig pt for Patroni cluster operations and pig pitr for orchestrated PITR.

pig pg - Local PostgreSQL primitives (pg_ctl / psql / local files).

Server Control (via pg_ctl):
  pig pg init     [-v ver] [-D datadir]     initialize data directory
  pig pg start    [-D datadir]              start PostgreSQL server
  pig pg stop     [-D datadir] [-m fast]    stop PostgreSQL server
  pig pg restart  [-D datadir] [-m fast]    restart PostgreSQL server
  pig pg reload   [-D datadir]              reload configuration
  pig pg status   [-D datadir]              show server status
  pig pg promote  [-D datadir]              promote standby to primary
  pig pg role     [-D datadir] [-V]         detect instance role (primary/replica)

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

Connection & Query:
  pig pg psql     [db] [-c cmd]             connect to database via psql
  pig pg ps       [-a] [-u user]            show current connections
  pig pg kill     [-x] [-u user]            terminate connections (dry-run by default)
  pig pg clone    <src> [dst]               clone database with CREATE DATABASE TEMPLATE

Database Maintenance:
  pig pg vacuum   [db] [-a] [-t table]      vacuum tables
  pig pg analyze  [db] [-a] [-t table]      analyze tables
  pig pg freeze   [db] [-a] [-t table]      vacuum freeze tables
  pig pg repack   [db] [-a] [-t table]      repack tables (online rebuild)

Tuning:
  pig pg tune     [-p profile]     generate optimized parameters

Instance Fork:
  pig pg fork init <name> [-D datadir]      fork PGDATA into /pg/data-<name>
  pig pg fork list                          list managed forks
  pig pg fork start|stop|rm <name>          manage fork lifecycle

Utilities:
  pig pg log <list|tail|cat|less|grep>      view PostgreSQL logs

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
pg cloneClone a single databaseCREATE DATABASE ... TEMPLATE ... FILE_COPY

Database Maintenance:

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

Parameter Tuning:

CommandAliasDescriptionNotes
pg tunetuningGenerate PostgreSQL tuning parametersAuto-detects hardware and supports structured output

Instance Fork:

CommandAliasDescriptionNotes
pg forkShortcut for fork initCreates a managed fork by default, does not start it
pg fork initcreateCreate a local one-off physical copyDefault /pg/data-<name>
pg fork listList managed forksScans /pg/data-*
pg fork startStart an existing forkSupports managed names or unmanaged --dst-data directories
pg fork stopStop an existing forkSupports shutdown mode
pg fork rmremove, deleteRemove a forkRunning forks require --stop

Log Tools:

CommandAliasDescriptionNotes
pg loglLog managementParent command
pg log listlsList log files
pg log tailt, fReal-time log viewingtail -f
pg log showcat, cOutput log content
pg log lessvi, vView with less
pg log grepg, searchSearch logs

Service Subcommand (pg svc, also pg service or pg s):

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)
pig pg clone meta meta_fork       # Clone a single database

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

# Parameter tuning
pig pg tune                       # Auto-detect hardware and generate tuned parameters
pig pg tune -p olap               # Use the OLAP workload profile
pig pg tune -c 8 -m 32768 -d 500  # Override CPU / memory / disk detection

# Instance fork
pig pg fork dev                   # Create /pg/data-dev
pig pg fork init dev --start      # Create and start fork, auto-assign high port
pig pg fork init dev -s --dst-port 15433  # Create and start on specified port
pig pg fork list                  # List /pg/data-* forks

# 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 grep ERROR             # Search logs

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 service operations use the dedicated pig pg svc ... commands; the local pg_ctl primitives do not have a global --systemd / -S switch.

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.

  • Data checksums are enabled by default unless explicitly disabled with -K|--no-data-checksums.
  • Prefer the platform-independent built-in C.UTF-8 locale on PG 17+, fall back to system C.UTF-8 / C, then system default locale.
  • If the data directory already exists, the command refuses to run unless -f|--force is used. If PostgreSQL is running on that data directory, it refuses even with --force to prevent data loss.
  • Extra arguments after -- are passed to initdb, for example --waldir=/wal. Use initdb directly if you need to override locale or encoding options.
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                    # Disable data checksums
pig pg init -f                    # Force init (remove existing data)
pig pg init -- --waldir=/wal      # Pass extra args to initdb

Options:

OptionShortDefaultDescription
--no-data-checksums-KfalseDisable data checksums
--force-ffalseForce init and remove existing data (dangerous!)
--yes-yfalseSkip overwrite confirmation when used with --force

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

pg start

Start PostgreSQL with pg_ctl start. For a Patroni-managed instance, prefer pig pt start so PostgreSQL starts under Patroni. For a systemd-managed PostgreSQL service, use pig pg svc start.

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 -o json              # Structured JSON output

Options:

OptionShortDescription
--log-lRedirect stdout/stderr to log file
--timeout-tWait timeout (seconds)
--no-waitDon’t wait for startup completion
--options-OOptions to pass to postgres

If PostgreSQL is already running, the command prints the existing postmaster PID and returns successfully.

pg stop

Stop PostgreSQL with pg_ctl stop.

On a Patroni-managed instance, stopping PostgreSQL directly can make Patroni restart it or initiate failover. Use pig pt stop or pig pt svc stop to stop Patroni and PostgreSQL together.

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 --plan                # Preview stop plan

Options:

OptionShortDefaultDescription
--mode-mfastShutdown mode: smart/fast/immediate
--timeout-t60Wait timeout (seconds)
--no-waitfalseDon’t wait for shutdown completion
--planfalsePreview local pg_ctl stop plan only

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 --plan             # Preview restart plan

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

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
pig pg promote --plan             # Preview promotion plan
pig pg promote -y                 # Skip confirmation prompt

Options:

OptionShortDescription
--timeout-tWait timeout (seconds)
--no-waitDon’t wait for promotion completion
--planPreview promotion plan only
--yes-ySkip confirmation prompt

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

When the global -D/--data option is specified, pg psql reads postmaster.pid from that directory as the database superuser and connects with the recorded port and Unix socket directory. If the postmaster information cannot be read or parsed, the command fails instead of silently connecting to the default instance.

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 --watch 5 -x          # Repeat every 5 seconds
pig pg kill --plan                # Preview connection termination plan

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
--watchRepeat every N seconds
--planPreview execution plan without terminating connections

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

pg clone

Clone a database inside the current PostgreSQL instance. This command wraps CREATE DATABASE ... TEMPLATE ... STRATEGY FILE_COPY, terminates existing sessions on the source database before cloning, and follows the same semantics as Pigsty’s pgsql-db clone workflow.

pig pg clone meta                       # Clone meta as meta_1/meta_2/...
pig pg clone meta meta_fork            # Clone to a specific database name
pig pg clone meta meta_fork --owner dba # Try to change new database owner
pig pg clone meta meta_fork --port 5433 # Connect to a specific local port
pig pg clone meta meta_fork --plan      # Preview clone plan

Options:

OptionShortDescription
--portPostgreSQL port, default 5432 or $PG_PORT
--conn-dbDatabase used to execute CREATE DATABASE; defaults to template1 when cloning postgres
--ownerTry to change the owner of the cloned database
--conn-limitConnection limit for the new database (-1 unlimited, 0 disallow connections)
--planShow execution plan only
--yes-ySkip confirmation prompt

Notes: On PostgreSQL 18+ with file_copy_method=clone, database cloning can use CoW semantics; otherwise it falls back to ordinary file copy. This command clones a single database and does not create a new PostgreSQL instance.

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 --schema myschema  # Vacuum tables in schema
pig pg vacuum mydb --full         # VACUUM FULL (requires exclusive lock)

Options:

OptionShortDescription
--all-aProcess all databases
--schemaSpecify 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 --schema myschema  # Repack tables in schema
pig pg repack mydb -j 4           # Use 4 parallel jobs
pig pg repack mydb --plan         # Show tables to be repacked

Options:

OptionShortDescription
--all-aProcess all databases
--schemaSpecify schema
--table-tSpecify table
--verbose-VVerbose output
--jobs-jNumber of parallel jobs (default 1)
--planShow tables to be repacked

Parameter Tuning Commands

pg tune

Generate a recommended set of PostgreSQL parameters based on the current PostgreSQL major version, host hardware, and workload profile. By default, it auto-detects CPU, memory, and data disk size, then prints the result as text output.

pig pg tune                       # auto-detect hardware, use oltp profile
pig pg tuning                     # alias
pig pg tune -p olap               # use OLAP profile
pig pg tune -p tiny               # for small instances
pig pg tune -c 8 -m 32768 -d 500  # override hardware detection
pig pg tune -C 500                # override max_connections
pig pg tune -R 0.30               # adjust shared_buffers ratio
pig pg tune -o json               # structured JSON output
pig pg tune -o yaml               # structured YAML output

Options:

OptionShortDefaultDescription
--profile-poltpTuning profile: oltp / olap / tiny / crit
--cpu-c0CPU cores, 0 means auto-detect
--mem-m0Total memory in MB, 0 means auto-detect
--disk-d0Data disk size in GB, 0 means auto-detect
--max-conn-C0Override max_connections, 0 uses profile default
--shmem-ratio-R0.25Fraction of memory used for shared_buffers, range 0.1 ~ 0.4

Profiles:

ProfileBest forCharacteristics
oltpGeneral transactional workloadsBalanced connection count, cache, and parallelism
olapAnalytical workloadsMore aggressive parallelism and work memory
tinySmall instancesConstrained memory footprint and parallelism
critLatency-sensitive workloadsRestricts parallel gather and favors stable response time

Notes:

  • Generated parameters are automatically gated by PostgreSQL major version. For example, io_workers is only emitted for PG 18+.
  • Text output can be redirected into a config snippet, while structured output is better suited for automation.
  • The command currently generates recommendations only; it does not modify PostgreSQL configuration files directly.

Instance Fork

pg fork

Create a local one-off PostgreSQL physical copy for temporary analysis, troubleshooting, recovery validation, and development testing. Managed forks are written to /pg/data-<name> by default and are not registered with Pigsty, systemd, or Patroni. When --dst-data is specified explicitly, the command creates an unmanaged fork that is not enumerated by fork list.

pig pg fork dev                       # Create /pg/data-dev, do not start
pig pg fork init dev --start          # Create and start, probing ports from 15432
pig pg fork init dev -s --dst-port 15433    # Create and start on specified port
pig pg fork init dev -D /pg/data2 --src-port 15431  # Specify source dir and source port
pig pg fork init dev --dst-data /tmp/dev    # Create unmanaged fork
pig pg fork list                      # List managed forks
pig pg fork start dev                 # Start existing managed fork
pig pg fork stop dev                  # Stop existing managed fork
pig pg fork rm dev --stop             # Stop and remove a running fork
pig pg fork init dev --plan           # Show execution plan only

Create Options:

OptionShortDefaultDescription
--dst-data/pg/data-<name>Unmanaged target data directory
--dst-portauto-detectTarget port, probes free ports starting at 15432
--src-data/pg/data or $PG_DATASource data directory; can also be set globally with pg -D/--data
--src-port5432 or $PG_PORTSource port
--start-sfalseStart the fork after creation
--force-ffalseOverwrite an existing stopped target directory and skip confirmation
--timeout-t60Startup wait timeout in seconds
--yes-yfalseSkip confirmation prompt
--planfalseShow execution plan only

Management Commands:

CommandCommon OptionsDescription
pig pg fork list-o json/yamlList managed forks with optional structured output
pig pg fork start <name> or --dst-data <dir>--dst-data, --dst-port, -t/--timeout, --planStart existing fork
pig pg fork stop <name> or --dst-data <dir>--dst-data, -m/--mode, -t/--timeout, --planStop existing fork
pig pg fork rm <name> or --dst-data <dir>--dst-data, --stop, -m/--mode, -t/--timeout, -f/--force, -y/--yes, --planRemove fork; running forks require --stop

Behavior Notes:

  • When the source instance is running, the command uses PostgreSQL low-level backup APIs to create a consistent physical copy; when the source is stopped, it can perform a cold copy.
  • The command prefers CoW/reflink. If only ordinary copy is available, interactive mode warns about disk-space risk and waits for confirmation.
  • To avoid deleting source data by mistake, the target directory cannot be /, /pg, source PGDATA, or a parent/child of source PGDATA. Symlinks are resolved before checks.
  • After copy, runtime and replication state is cleaned from the fork and fork.json is written. The new instance starts only when -s|--start is specified.
  • Managed forks must be managed by name. Unmanaged forks require --dst-data when starting, stopping, or removing.

List Forks:

pig pg fork list scans /pg/data-*, reads fork.json, and refreshes each managed fork’s runtime state. Text output shows NAME, PORT, STATE, PID, AGE, SOURCE, COPY, and DATA; state is reported as running, stopped, or orphan.

JSON and YAML output return the full machine-readable fork objects, including source and target endpoints, copy and backup metadata, management commands, and Pig build metadata.

Structured Output:

pig pg fork init dev --plan -o yaml
pig pg fork list -o json

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)
--lines / -nNumber of lines to show, default 50
--follow / -fFollow latest log, only on parent pg log

Permission Handling: If current user lacks permission to read log directory, command automatically retries with sudo. -o json emits JSONL log records; log snapshots do not support yaml or json-pretty.

pg log

Show the latest log snapshot; with -f, follow the latest log.

pig pg log                        # Show latest 50 lines
pig pg log -n 100                 # Show latest 100 lines
pig pg log -f                     # Follow latest log

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 show

Output log file content.

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

Options:

OptionShortDefaultDescription
--lines-n50Number 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 vi                     # Alias
pig pg log v                      # Alias
pig pg log less postgresql.csv    # Open specific log file

pg log grep

Search log file content.

pig pg log grep ERROR             # Search for ERROR
pig pg log grep --ignore-case error  # Ignore case
pig pg log grep -C 3 ERROR        # Show context
pig pg log grep ERROR pg.csv      # Search specific log file

Options:

OptionShortDescription
--ignore-caseIgnore case
--context-CShow context lines

pg svc Subcommand

pg svc (also pg service or pg s) 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
  • status command shows process and related service status beyond pg_ctl status
  • Connection management commands (psql/ps/kill) call psql
  • clone command uses SQL to create a database copy
  • Maintenance commands (vacuum/analyze/freeze) call vacuumdb
  • repack command calls pg_repack
  • fork command uses PostgreSQL low-level backup APIs and local file copy to create one-off physical copies
  • 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
  • pg clone terminates existing sessions on the source database; use it during a maintenance window
  • pg fork rejects dangerous target paths; ordinary-copy fallback warns about disk-space risk
  • Log commands auto-retry with sudo when permissions insufficient

Platform Support:

This command is designed for Linux systems. Some features depend on systemctl, and log commands depend on readable PostgreSQL log files plus common tools such as tail, less, and grep.

12 - pig patroni

Run patronictl transparently, with Pigsty service, config, and log helpers

Since v1.6.0, the pig patroni command (alias pig pt) is a transparent launcher for the installed patronictl binary: pig only owns config-file selection and a few local helpers, while every other command and all of its arguments are forwarded unchanged to patronictl — with native flags, prompts, output, and exit codes. New patronictl features work without waiting for a pig release.

pig pt transparently launches the installed patronictl binary.

Pig only owns config-file selection and these local helpers:
  pig pt set KEY=VALUE...       update PostgreSQL or scalar Patroni settings
  pig pt start|stop             manage the local patroni service
  pig pt service <action>       start, stop, restart, reload, or show service
  pig pt status                 show local and cluster status
  pig pt log ...                view local Patroni logs

Every other command and all arguments after it are passed unchanged to
patronictl. Use "pig pt -- COMMAND ..." to bypass a local-name collision.

The first non-option command token decides the dispatch:

  • set, start/up, stop/dn, service/svc, status/st, and log/l select pig’s local implementation;
  • every other token (list, restart, reload, reinit, switchover, failover, pause, resume, show-config, edit-config, query, history, topology, dsn, version, …) and everything after it is forwarded verbatim to patronictl;
  • pig pt -- <command> ... bypasses local-name collisions (e.g. pig pt -- set hands set to patronictl);
  • pig pt with no command prints pig’s help and does not run patronictl; use pig pt <command> --help for native subcommand help and pig pt -- --help for patronictl root help.

Command Overview

Forwarded commands (native patronictl):

ExampleDescription
pig pt list [CLUSTER]List cluster members (native output, --format json)
pig pt restart CLUSTER [MEMBER]Restart PostgreSQL of a cluster / member (native prompt)
pig pt reload CLUSTERReload PostgreSQL configuration
pig pt reinit CLUSTER MEMBERReinitialize a member (resync from primary)
pig pt switchover CLUSTER [--candidate X]Planned switchover
pig pt failover CLUSTER --candidate MEMBERManual failover (positional arg is the cluster)
pig pt pause / resume CLUSTEREnter / leave maintenance mode
pig pt show-config / edit-configShow / edit cluster dynamic configuration
pig pt query -c 'select 1'Native query (-c here is query’s own SQL option)

Forwarded positionals follow patronictl’s native cluster-first semantics; confirmation prompts, output formats, and exit codes (including Click usage-error exit code 2) are all owned by patronictl.

Local commands (implemented by pig):

CommandAliasDescription
pt setUpdate PostgreSQL params or scalar Patroni settings
pt statusstCombined status: systemd + processes + cluster
pt servicesvcManage the local patroni systemd service
pt startupHidden shortcut for pt svc start
pt stopdnHidden shortcut for pt svc stop
pt loglView local Patroni logs (show / tail / grep)

Note: top-level pt restart is not a daemon shortcut — it forwards to patronictl restart (restarting PostgreSQL). Use pt svc restart to restart the patroni daemon itself.

Quick Start

# Cluster operations (native passthrough, cluster-first)
pig pt list pg-meta                         # list cluster members
pig pt list pg-meta --format json           # native JSON output
pig pt restart pg-test --pending            # apply pending restarts
pig pt restart pg-test pg-test-1            # restart a specific member
pig pt switchover pg-test --candidate pg-2  # planned switchover
pig pt failover pg-test --candidate pg-2    # manual failover
pig pt pause pg-test                        # enter maintenance mode
pig pt show-config pg-test                  # show dynamic configuration

# Configuration sugar (local, one edit-config call)
pig pt set ttl=60                           # scalar Patroni key → --set
pig pt set max_connections=200              # PostgreSQL parameter → --pg
pig pt set ttl=60 max_connections=200 -y    # mixed change, skip confirmation
pig pt set shared_buffers=4GB --plan        # preview the translated native command

# Service and logs (local)
pig pt status                               # combined status
pig pt svc restart                          # restart the patroni daemon
pig pt log -f                               # follow logs
pig pt log grep ERROR                       # search logs

Pig/PT Options

These wrapper-level options must precede the native command; once the native command token appears, all remaining tokens belong to patronictl:

OptionShortDescription
--config-file-cExplicit Patroni/patronictl configuration file
--dbsuOS user used to execute patronictl (default $PIG_DBSU or postgres)
--dcs-url-dOverride the Patroni DCS URL (--dcs is an alias)
--insecure-kAllow TLS without certificate verification
pig pt -c /path/patroni.yml list pg-meta   # wrapper-level -c: config file
pig pt query -c 'select 1'                 # native query's -c: SQL, not stolen by pig

Since v1.6.0, --dbsu no longer has the -U shorthand (the pg / pb commands keep it).

Config-File Resolution

Every config-aware patronictl invocation receives exactly one pig-selected root -c <path>, resolved in this order:

  1. explicit -c/--config-file given before the command;
  2. non-empty PATRONICTL_CONFIG_FILE environment variable;
  3. /etc/patroni/patroni.yml (exists and readable as the DBSU);
  4. /infra/conf/patronictl.yml (exists and readable as the DBSU);
  5. fallback to /etc/patroni/patroni.yml, so patronictl’s error names the conventional path.

Explicit and environment paths are authoritative: pig does not silently substitute another candidate when they are missing or unreadable; relative paths are made absolute before switching OS user. Conventional candidates are probed for readability as the resolved DBSU (not by mode bits). Resolution is lazy: pure systemd operations like pt svc start never probe a config, and native -h/--help takes a config-independent fast path that works on machines without Patroni configured.

Transparent Execution and Output Modes

Forwarded execution directly inherits stdin / stdout / stderr and the terminal: native prompts, the edit-config editor, --watch streaming, and exit codes are all preserved. Pig performs no output capture, no duplicate error rendering, no retries, and no cluster/DCS reads before or after execution. The logical invocation is:

patronictl -c <selected-config> [--dcs-url URL] [--insecure] <native argv...>

Structured output: the forwarding path supports pig’s text mode only. A pig -o json / -o yaml placed before the native command is rejected (with guidance to use patronictl’s native output options); anything after the native command is forwarded as-is and validated by patronictl:

pig -o json pt list          # rejected: use native output options
pig pt -o json list          # rejected: use native output options
pig pt list --format json    # forwarded: native patronictl JSON
pig pt list -o json          # forwarded unchanged; patronictl validates it

Local commands (set / status / log / service) keep pig’s structured output behavior.

pig pt set

pt set is the only local configuration sugar; it edits the cluster selected by the resolved configuration:

pig pt set KEY=VALUE [KEY=VALUE ...] [--yes] [--plan]

Key classification:

  • These scalar Patroni dynamic keys translate to native --set: loop_wait, ttl, retry_timeout, primary_race_backoff, maximum_lag_on_failover, maximum_lag_on_syncnode, max_timelines_history, primary_start_timeout, primary_stop_timeout, synchronous_mode, synchronous_mode_strict, synchronous_node_count, failsafe_mode, check_timeline, member_slots_ttl (pause is deliberately excluded — use native pause/resume);
  • every other key is treated as a PostgreSQL parameter and translates to native --pg (including dotted custom GUCs like timescaledb.telemetry_level);
  • structural keys starting with postgresql., standby_cluster., slots., or ignore_slots. are rejected, pointing to native pig pt edit-config --set.

All pairs merge into one native edit-config call in input order — one diff, one confirmation, one DCS update:

pig pt set ttl=60 max_connections=200 synchronous_mode=on
# equivalent: patronictl -c <selected> edit-config --set ttl=60 --pg max_connections=200 --set synchronous_mode=on
  • --yes/-y appends native --force to skip confirmation; otherwise patronictl shows the diff and owns confirmation;
  • --plan mutates nothing and renders the selected config plus the translated native command (plans may contain sensitive values);
  • in structured output mode, execution requires an explicit --yes (hidden prompts are forbidden);
  • values parse as YAML: both KEY=null and KEY= remove a key, passed unchanged;
  • after changing restart-required PostgreSQL parameters, pig suggests the follow-up: first pig pt list, then pig pt restart CLUSTER --pending (explicit cluster required).

Service Management

pt service (alias pt svc) manages the local patroni systemd unit:

CommandAliasDescription
pt service startpt svc upStart the Patroni service
pt service stoppt svc dnStop the Patroni service
pt service restartpt svc rsRestart the Patroni service
pt service reloadpt svc rlReload the Patroni service
pt service statuspt svc stShow service status

Top-level pt start / pt stop (aliases up / dn) are hidden shortcuts to the same local implementation. Stopping the Patroni service may also stop PostgreSQL on this node (depending on Patroni configuration).

pt status

Shows combined status: systemd service state, Patroni process info, and cluster members from patronictl.

pig pt status
pig pt st -o json                # structured output

pt log

View local Patroni logs. The log directory comes from log.dir in the selected config, falling back to /pg/log/patroni, or set it explicitly with --log-dir. Only pt log and pt log show support -o json (JSONL snapshot); follow / tail / grep are terminal streams without structured output.

pig pt log                     # show last 50 lines
pig pt log -f                  # follow log output
pig pt log -n 100              # show last 100 lines
pig pt log show -o json        # JSONL snapshot
pig pt log tail -n 100         # follow logs
pig pt log grep ERROR          # search logs
SubcommandAliasesDescription
showcat, c, sPrint recent Patroni logs
tailt, f, followFollow Patroni logs
grepg, searchSearch Patroni logs
OptionShortDefaultDescription
--follow-ffalseFollow log output
--lines-n50Number of lines shown
--log-dirautoLog directory

Migrating from v1.5.x

The v1.6.0 passthrough rewrite is a breaking change — review automation before upgrading:

v1.5.x usagev1.6.0 usage
pig pt failover <candidate>⚠ pig pt failover CLUSTER --candidate MEMBER (the positional is now the cluster)
pig pt restart [member] (auto scope)pig pt restart CLUSTER [MEMBER] (explicit cluster)
pig pt list -o jsonpig pt list --format json (native JSON, different schema)
pig pt config showpig pt show-config
pig pt config editpig pt edit-config
pig pt config set K=V / pg K=Vpig pt set K=V
pig pt restart -y (pig gate)native patronictl confirmation; pt set -y still works
pig pt list -W / -w 5native pig pt list --watch (patronictl semantics)
aliases ls/rs/rl/ri/so/fo/p/r/cremoved — use full native command names
--dbsu -U--dbsu (-U shorthand removed)

Also: forwarded commands return native patronictl exit codes (usage errors exit 2), and the pig-side pause-guard preflight for switchover/failover is gone — maintenance-mode semantics are fully owned by Patroni.

Design Notes

Single authority: there is one cluster-control path — the installed patronictl plus one selected config, acting through the Patroni REST API / DCS. Pig embeds no DCS client or REST control engine, keeps no patronictl command inventory, and adds no confirmation, preflight, retry, or output rewriting to forwarded commands.

Privilege handling (unchanged from v1.5.x):

  • if the current user is already the DBSU: execute directly;
  • if the current user is root: execute via su - postgres -c "...";
  • otherwise: execute via sudo -inu postgres -- ....

Platform: designed for Linux; service management relies on systemctl, and the log helpers require readable Patroni log files.

13 - pig pgbackrest

Manage pgBackRest backup and restore primitives with pig pgbackrest

The pig pgbackrest command (alias pig pb) manages pgBackRest backups and provides low-level restore primitives. It wraps common pgbackrest operations for a simplified backup management experience. All commands run as the database superuser (default postgres). For orchestrated point-in-time recovery on managed clusters, prefer pig pitr.

pig pb - Manage pgBackRest backup and point-in-time recovery.

Information:
  pig pb info                      show backup info
  pig pb list                      list backups
  pig pb list repo                 list configured repositories
  pig pb list stanza               list all stanzas

Backup & Restore:
  pig pb backup                    create backup (auto: full/incr)
  pig pb backup full               create full backup
  pig pb restore                   low-level restore primitive
  pig pb restore -t "..."          restore to specific time
  pig pb expire                    cleanup expired backups

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

Control:
  pig pb check                     verify backup integrity
  pig pb start                     enable pgBackRest operations
  pig pb stop                      disable pgBackRest operations
  pig pb log                       view pgBackRest logs

Examples:
  pig pb info                      # show all backup info
  pig pb backup                    # auto: full if none, else incr
  pig pb backup full               # full backup
  pig pb restore -d                # restore to latest (end of WAL)
  pig pb restore -t "2025-01-01 12:00:00+08"  # restore to time
  pig pb create                    # initialize stanza
  pig pb expire                    # cleanup per retention policy

Command Overview

Information Query:

CommandAliasDescriptionImplementation
pb infoiShow backup repository infopgbackrest info
pb listlsList backup sets, repos, and stanzaspgbackrest info

Backup & Restore:

CommandAliasDescriptionImplementation
pb backupbCreate backuppgbackrest backup
pb restorerLow-level restore primitivepgbackrest restore
pb expireeClean up expired backupspgbackrest expire

Stanza Management:

CommandAliasDescriptionImplementation
pb createcCreate stanza (first-time setup)pgbackrest stanza-create
pb upgradeuUpgrade stanza after PG major upgradepgbackrest stanza-upgrade
pb deletedDelete stanza (dangerous!)pgbackrest stanza-delete

Control Commands:

CommandAliasDescriptionImplementation
pb checkckVerify backup repository integritypgbackrest check
pb startupEnable pgBackRest operationspgbackrest start
pb stopdwDisable pgBackRest operationspgbackrest stop
pb loglView logslatest log snapshot / tail

Quick Start

# View backup info
pig pb info                          # Show all backup info
pig pb info -o json                  # Result-wrapped structured JSON output
pig pb info --raw --raw-output json  # Native pgBackRest 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 (low-level restore primitive, 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 --name 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 --plan                 # Preview cleanup plan only

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 is not specified, pig auto-detects the stanza name from the config file:

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

If the config file contains multiple stanzas, pig emits a warning and uses the first one. In that case, specify --stanza explicitly.

Multi-Repo Support:

pgBackRest supports multiple repositories (repo1, repo2, etc.). Use -r to choose the target repository:

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 information, including all backup sets and WAL archive status.

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

Options:

OptionShortDescription
--raw-RRaw output mode (passes through pgBackRest output)
--raw-outputRaw output format: text, json (only in --raw mode)
--setShow details for a specific backup set

pb ls

List resources in the 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

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 a physical backup. Backups can only run on the 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, otherwise incremental
fullFull backup: backup all data
diffDifferential backup: changes since the last full backup
incrIncremental backup: changes since the last backup of any type

Primary Check:

Before running a backup, the command automatically checks whether the current instance is primary. If it is a replica, the command exits with an error. Use --force to skip this check.

pb expire

Clean up expired backups and WAL archives according to the retention policy.

pig pb expire                        # Clean up per policy
pig pb expire --set 20250101-*       # Delete a specific backup set
pig pb expire --set 20250101-* -y    # Skip confirmation when deleting backup set
pig pb expire --plan                 # Preview cleanup plan only, do not delete

Options:

OptionShortDescription
--setDelete a specific backup set
--planPreview cleanup plan only, do not delete backups
--yes-ySkip confirmation prompt when used with --set

Retention Policy:

Retention policy is 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 recovery target support. At least one recovery target (-d/-I/-t/--name/--lsn/--xid) must be specified explicitly. Without parameters, help is shown.

# Recovery target (mutually exclusive options)
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 --name my-savepoint   # Restore to named restore point
pig pb restore --lsn "0/7C82CB8"     # Restore to LSN
pig pb restore --xid 12345           # Restore to transaction ID

# Backup set selection (must combine with a recovery target)
pig pb restore -b 20251225-120000F -d  # Restore from specific backup set to latest

# Other options
pig pb restore -t "..." -X           # Exclusive mode (stop before target)
pig pb restore -t "..." --target-action=promote  # Promote after reaching target
pig pb restore -t "..." -T current   # Recover along current timeline
pig pb restore -d --plan             # Preview restore plan only
pig pb restore -d -y                 # Skip interactive y/yes confirmation
pig pb restore -d -- --delta         # Pass native pgBackRest restore args after --

Recovery Target Options:

OptionShortDescription
--default-dRestore to end of WAL stream (latest data)
--immediate-IRestore to backup consistency point
--time-tRestore to specific timestamp
--nameRestore to named restore point
--lsnRestore to specific LSN
--xidRestore to specific transaction ID

Backup Set and Other Options:

OptionShortDescription
--set-bRestore from a specific backup set (can combine with target)
--data-DTarget data directory
--exclusive-XExclusive mode: stop before target
--target-actionAction after reaching recovery target: pause/promote/shutdown
--target-timeline-TRecovery timeline: latest/current/N/0xN
--planPreview restore plan only, do not execute
--yes-ySkip interactive y/yes confirmation
Native args after --Pass through pgBackRest restore args, for example -- --delta

Combination Rules: --target-action cannot be used with --default, because --default already means recovery to the end of the WAL stream. --exclusive/-X must be used with an explicit stop target: --time, --lsn, or --xid.

Native pgBackRest restore args after -- cannot override recovery targets, lifecycle, data directory, repository, config, or selection semantics already managed by Pig. Use Pig’s first-class options for those semantics. Tablespace/link migration options such as --tablespace-map, --link-map, and --link-all can still be passed through.

Time Formats:

Supports multiple time formats and auto-completes timezones (including non-integer-hour zones such as +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 (current timezone)
Time only12:00:00Auto-completes to today (current timezone)

Restore Flow:

  1. Validate parameters and environment
  2. Check that PostgreSQL is stopped
  3. Show restore plan and wait for interactive y/yes confirmation
  4. Execute pgbackrest restore
  5. Provide post-restore guidance

Important: Stop PostgreSQL before restore. If the PGDATA is managed by Patroni, use pig pitr to orchestrate Patroni, PostgreSQL, and pgBackRest:

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

Stanza Management Commands

pb create

Initialize a new stanza. Must run before the first backup.

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

Options:

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

pb upgrade

Update stanza after a PostgreSQL major version upgrade.

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

Options:

OptionDescription
--no-onlineUpgrade when PostgreSQL is not running

Use Case:

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

pb delete

Delete a stanza and all its backups.

pig pb delete                        # Delete stanza (interactive y/N confirmation)
pig pb delete --yes                  # Skip interactive y/yes confirmation
pig pb delete --plan                 # Preview delete plan

Options:

OptionShortDescription
--planPreview delete plan only, do not execute
--yes-ySkip interactive y/yes confirmation

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

The command includes multiple safety mechanisms:

  1. In text mode, it requires interactive y/yes confirmation unless --yes is specified
  2. Structured output mode requires explicit --yes
  3. If the config file contains multiple stanzas and --stanza is not specified, pig refuses to auto-select a deletion target

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 this command 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:

Use this command during system maintenance to prevent new backup operations from starting.

Log Commands

pb log

View pgBackRest log files. The log directory is read from pgBackRest config log-path first, and falls back to /pg/log/pgbackrest/ when not configured. The parent command shows the latest log snapshot by default; use tail or -f for live follow. Only pb log and pb log show support -o json JSONL output; log snapshots do not support yaml or json-pretty, and follow/tail does not support structured output.

pig pb log                           # Show latest log lines
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 show                      # Show latest log content
pig pb log show -n 50                # Show last 50 lines
pig pb log cat                       # Alias for show

Subcommands:

SubcommandAliasesDescription
listlsList log files
showcat, cShow latest log content
tailt, f, followReal-time follow latest log

Options:

OptionShortDefaultDescription
--lines-n50Number of lines to show
--follow-ffalseMakes parent command pb log follow; no-op in pb log tail because tail always follows

Permission Handling:

If the current user does not have permission to read the log directory, the command automatically retries with sudo.

Design Notes

Command Execution:

All pig pb commands run as the database superuser (DBSU), because pgBackRest needs access to PostgreSQL data files and WAL archives.

Execution logic:

  • If the current user is DBSU: execute directly
  • If the 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 for pgbackrest; it is a higher-level abstraction for common operations:

  • Auto-detect stanza name without specifying it every time
  • Auto-check primary role before backup
  • Show restore plan and require interactive y/yes confirmation before restore
  • Provide user-friendly time format input
  • Provide post-restore guidance

For full pgbackrest functionality, use the 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, or /pg/data

Security Considerations:

  • pb delete requires interactive y/yes confirmation when --yes is not specified, and --stanza must be explicit under multi-stanza configs
  • pb restore requires an explicit recovery target, validates --time, and requires interactive y/yes confirmation when --yes is not specified
  • pb backup checks primary role by default to prevent running on a replica
  • pb log tail does not support structured output; use pb log show -n N -o json when a JSONL snapshot is required

Platform Support:

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

14 - pig pitr

Perform orchestrated point-in-time recovery (PITR) with pig pitr

The pig pitr command performs point-in-time recovery through pgBackRest and conservatively handles the local PostgreSQL and Patroni lifecycle. Unlike the lower-level pig pb restore, pig pitr runs pre-restore checks, stops Patroni and PostgreSQL when needed, executes restore, then decides whether to start PostgreSQL based on the selected options.

Note: for a managed default data directory, pig pitr leaves Patroni stopped after recovery. Validate the restored result first, then manually restore Patroni management. This command does not automatically rejoin a Patroni cluster, perform failover, or validate cluster member state.

pig pitr - Perform PITR with pgBackRest restore and conservative PostgreSQL stop/start handling.

For the managed default data directory, this command may:
  1. Stop Patroni only to keep the target PGDATA offline during restore
  2. Ensure PostgreSQL is stopped (fast stop with retry; destructive fallback only with --force-stop)
  3. Execute pgbackrest restore
  4. Start PostgreSQL unless --no-restart is used
  5. Leave Patroni stopped; 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             Recover to named restore point
  --lsn              Recover to specific LSN
  --xid              Recover to specific transaction ID

Backup and Target Options:
  --set, -b          Select backup set to start recovery from
  --target-action    Action when target is reached: pause, promote, shutdown
  --target-timeline  Recover along timeline: latest, current, N, or 0xN

Use --no-restart with --target-action=shutdown because PostgreSQL exits
after reaching the recovery target.

Custom -D side restores require --no-restart. The custom directory must
already exist, be owned by the configured DBSU (postgres by default), and
be writable by that user. Pig does not create this directory automatically.

Additional pgBackRest arguments:
  Put raw pgBackRest restore arguments after -- so Cobra stops parsing them.
  Example: pig pitr -d -- --delta

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
  pig pitr -t "2025-01-01 12:00:00+08"            # Recover to time
  pig pitr -d --plan                               # Preview plan
  pig pitr -d -y                                   # Skip y/yes confirmation
  pig pitr -d --no-restart                         # Leave PostgreSQL stopped
  pig pitr -d -D /tmp/pg-restore --no-restart      # Side restore

Overview

The default target of pig pitr is the Pigsty-managed primary data directory. A typical workflow:

  1. Validate recovery target parameters. One of -d/-I/-t/--name/--lsn/--xid is required.
  2. Resolve pgBackRest configuration and target data directory.
  3. For default data-directory recovery, stop Patroni if it is running.
  4. Ensure PostgreSQL is stopped.
  5. Run pgbackrest restore.
  6. Start PostgreSQL unless --no-restart is specified.
  7. Print post-recovery validation and Patroni recovery guidance.

Comparison with pig pb restore:

Featurepig pitrpig pb restore
Stop PatroniAutomatic for default data-directory recoveryManual
Stop PostgreSQLChecks and stops automaticallyMust be pre-stopped
Start PostgreSQLAutomatic by default, can be disabled with --no-restartManual
Patroni recoveryNot automatic; restore manually after verificationNot handled
Use caseProduction recovery orchestrationLow-level restore or scripting

Quick Start

# Most common: recover to the end of the WAL stream
pig pitr -d

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

# Recover to the backup consistency point
pig pitr -I

# Preview the execution plan without running
pig pitr -d --plan

# Skip confirmation, suitable for automation
pig pitr -d -y

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

# Restore the default data directory but do not start PostgreSQL afterward
pig pitr -d --no-restart

# Side restore: restore to a custom directory without touching Patroni or /pg/data
install -d -m 700 -o postgres -g postgres /tmp/pg-restore
pig pitr -d -D /tmp/pg-restore --no-restart

# Extra native pgBackRest restore arguments go after --
pig pitr -d -- --delta

Parameters

Recovery Target

At least one recovery target is required.

ParameterShortDescription
--default-dRecover to the end of the WAL stream, the latest data
--immediate-IRecover to the backup consistency point
--time-tRecover to a specific timestamp
--nameRecover to a named restore point
--lsnRecover to a specific LSN
--xidRecover to a specific transaction ID

Backup And Target Options

ParameterShortDescription
--set-bStart recovery from a specific backup set
--target-actionAction when the recovery target is reached: pause/promote/shutdown
--target-timeline-TRecovery timeline: latest/current/N/0xN
--exclusive-XExclusive mode: stop before target

Use --target-action=shutdown with --no-restart, because PostgreSQL exits after reaching that recovery target. --target-action cannot be used with --default, because --default already means recovery to the end of WAL. --exclusive/-X requires a precise stop-before target: --time, --lsn, or --xid.

Native pgBackRest restore arguments after -- cannot override recovery target, lifecycle, data directory, repository, config, or selection parameters managed by Pig. Use Pig’s first-class parameters for these semantics. This restriction is consistent with the pig pb restore passthrough blocklist.

Flow Control

ParameterShortDescription
--no-restartDo not start PostgreSQL after restore
--planShow execution plan only, do not execute
--yes-ySkip interactive y/yes confirmation
--timeoutPostgreSQL startup/recovery wait timeout, default 120 seconds
--force-stopAllow immediate shutdown and kill fallback if fast stop fails

Configuration

ParameterShortDescription
--stanza-spgBackRest stanza name
--config-cpgBackRest config file path
--repo-rRepository number
--dbsu-UDatabase superuser, default postgres
--data-DTarget data directory

Time Format

The --time parameter supports multiple formats and completes missing parts using the current timezone:

FormatExampleDescription
Full2025-01-01 12:00:00+08Complete timestamp with timezone
Date time without timezone2025-01-01 12:00:00Adds the current local timezone automatically; T separator is also accepted
Date only2025-01-01Completes to 00:00:00 on that date
Time only12:00:00Completes to that time today

Plan output and replayable next-action commands normalize date-only and time-only targets into deterministic timestamps with timezone. This rule is consistent with pig pb restore --plan.

Managed Directory And Side Restore

The managed PostgreSQL data directory comes from the effective pgBackRest pg1-path and command parameters. It is not hardcoded to /pg/data. For example, if managed PGDATA is /var/lib/pgsql/18/data, the command still treats it as a managed restore. Path comparison resolves symlinks as the database superuser when needed, so a symlink to managed PGDATA is not mistaken for a side restore.

An explicit -D/--data that resolves to a path different from the managed directory is a side restore. Side restores must use --no-restart; they do not stop Patroni and do not manage the default PostgreSQL service. After restore, handle the side instance manually with commands such as pg_ctl -D <dir> -o "-p 5433" start, pg_ctl -D <dir> status, and pgbackrest --pg1-path=<dir> stanza-create. The side-restore directory must already exist and be owned by the DBSU. Unlike managed PGDATA, it does not need a pre-existing PG_VERSION marker. Pig does not create this directory automatically because it needs a concrete path for destructive-restore classification, owner checks, and safe guidance before restore.

install -d -m 700 -o postgres -g postgres /tmp/pg-restore
pig pitr -d -D /tmp/pg-restore --no-restart

For managed PGDATA outside /pg/data, post-restore runbook commands explicitly include the effective data directory:

pig pg start -D /var/lib/pgsql/18/data
pig pg psql -D /var/lib/pgsql/18/data
pig pg promote -D /var/lib/pgsql/18/data

pig pg psql -D <dir> reads postmaster.pid under that directory and uses the recorded port and socket directory to connect to the restored instance. If postmaster information cannot be resolved, it does not silently fall back to the default connection target.

Execution Flow

Phase 1: Pre-Checks

  • Validate recovery target parameters; missing targets only print help and return an error.
  • Resolve effective pgBackRest config, stanza, repository, and managed pg1-path.
  • Check that the managed data directory exists and is initialized.
  • For side restore, check that the custom data directory exists and is owned by the DBSU.
  • Verify the selected stanza is healthy and has backups; if --set is specified, verify that backup set exists.
  • Detect Patroni service state and PostgreSQL runtime state.

Phase 2: Patroni Handling

For managed data-directory recovery, if Patroni is running, the command stops Patroni so the target PGDATA stays offline during restore. Patroni remains stopped after recovery. A custom -D side restore does not touch the managed data directory and therefore does not stop Patroni.

Phase 3: Ensure PostgreSQL Is Stopped

The command first waits for PostgreSQL to exit after Patroni stops, then retries pg_ctl stop -m fast. If PostgreSQL still cannot stop, it does not use more aggressive methods by default. Only explicit --force-stop allows immediate shutdown and final kill fallback.

Phase 4: Restore

The command runs pgBackRest restore and maps recovery target, backup set, timeline, target action, and related options into pgbackrest restore. Native pgBackRest arguments can be placed after --:

pig pitr -d -- --delta

Phase 5: Start Or Stay Stopped

Unless --no-restart is specified, the command starts PostgreSQL after restore and waits for recovery completion. For --default and --target-action=promote, it waits until pg_is_in_recovery() becomes false on the recovered instance. Recovery and subsequent SQL probes bind to the port recorded in postmaster.pid for the restored data directory and use the socket directory there when present. Use --no-restart when:

  • Running a custom -D side restore, because the restored config still keeps the original port and must be started manually on a free port.
  • Using --target-action=shutdown, because PostgreSQL exits after reaching the recovery target.
  • You need to inspect the restored directory before deciding whether to start.

Examples

Scenario 1: Recover Dropped Data

# 1. View available backups
pig pb info

# 2. Preview recovery plan
pig pitr -t "2025-01-15 09:30:00+08" --plan

# 3. Execute recovery
pig pitr -t "2025-01-15 09:30:00+08"

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

Scenario 2: Recover To Latest

pig pitr -d

Scenario 3: Recover To Backup Consistency Point

pig pitr -I

Scenario 4: Keep Stopped After Restore

pig pitr -d --no-restart

# PostgreSQL and Patroni both remain stopped; inspect directory or logs before starting
pig pg start

Scenario 5: Custom Directory Side Restore

install -d -m 700 -o postgres -g postgres /tmp/pg-restore
pig pitr -d -D /tmp/pg-restore --no-restart

# Start the side restore manually on a free port
pg_ctl -D /tmp/pg-restore -o "-p 5433" start

Execution Plan Example

Running pig pitr -d --plan shows a 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

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

Post-Recovery Actions

After successful recovery, verify data before restoring orchestration:

# Verify data
pig pg psql

# If recovered to a manual target and promotion is needed
pig pg promote

# Restore Patroni management only after validation
systemctl start patroni

# Re-create pgBackRest stanza if needed
pig pb create

Leaving Patroni stopped after managed data-directory recovery is intentional. It prevents restored old state from re-entering HA orchestration before validation.

Safety Mechanisms

Recovery target required: Without -d/-I/-t/--name/--lsn/--xid, the command only shows help and does not run restore.

Confirmation: In text mode, destructive recovery asks for interactive y/yes confirmation before execution. Automation can use -y|--yes. Structured output mode does not prompt interactively; it must use --yes to execute or --plan to preview.

Patroni boundary: For managed data-directory recovery, the command stops Patroni when needed to prevent Patroni from restarting PostgreSQL during restore. It does not automatically rejoin Patroni after recovery.

Side restore boundary: A custom -D side restore must use --no-restart because the restored PostgreSQL config still uses the original port. Side restore does not manage Patroni or the default PostgreSQL service.

Failure boundary: If restore fails after Patroni has been stopped, Patroni remains stopped and the target data directory may be partially restored. Fix the underlying issue and rerun PITR, or validate the recovery state first; do not start Patroni before confirmation. If restore has run but PostgreSQL startup fails, inspect PostgreSQL and pgBackRest logs and validate the data directory before deciding whether to restore Patroni management.

Structured output: Structured execution requires --yes; --plan is the preview path. After successful execution, structured PITR results put post-restore actions in the Result envelope’s next_actions, not inside data. data includes requested_data_dir, effective_data_dir, managed_data_dir, and side_restore so automation can distinguish user input from the actual recovery target.

Design Notes

  • pig pitr calls pgBackRest restore and handles local Patroni/PostgreSQL stop and optional start.
  • pig pitr is not a cluster recovery controller. It does not handle Patroni failover, rejoin, VIP, or application traffic switching.
  • Use pig pb restore when you need lower-level restore semantics or fine-grained scripting control.
  • Use pig pt switchover CLUSTER or pig pt failover CLUSTER --candidate MEMBER when you need manual Patroni cluster switching (native patronictl passthrough since v1.6.0; the cluster name is required).

Privilege execution:

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

Platform support:

This command is designed for Linux and depends on pgBackRest, systemd for managed service scenarios, and data/log paths accessible by the DBSU.