# Technical Documentation: pgsty/pigsty > ℹ️ **Provenance:** Hybrid Fusion: `pgsty/pigsty` (README + 2 In-Tree Chapters) · [CodeWiki Reference](https://codewiki.google/github.com/pgsty/pigsty) · Recency: Active (< 180 days) ## 1. Project Overview & Quickstart (pgsty/pigsty) # PIGSTY - Self-Host Postgres like a Pro [](https://pigsty.io) [](https://pigsty.cc) [](https://github.com/pgsty/pigsty/releases) [](https://pigsty.io/docs/about/license/) [](https://star-history.com/#pgsty/pigsty&Date) [](https://pigsty.io/ext/list) [](https://deepwiki.com/pgsty/pigsty) Enterprise-Grade Open-Source **PostgreSQL** Distribution with HA, PITR, IaC, Monitor, and 575 PG extensions. > "**P**ostgreSQL **I**n **G**reat **STY**le": **P**ostgres, **I**nfras, **G**raphics, **S**ervice, **T**oolbox, it's all **Y**ours. [Website](https://pigsty.io) | [ORG](https://github.com/pgsty) | [PGEXT](https://pigsty.io/ext) | [Piglet](https://pigsty.io/docs/piglet) | [Public Demo](https://demo.pigsty.io) | [Blog](https://pigsty.io/blog/) | [Discuss](https://github.com/orgs/pgsty/discussions) | [Support](https://pigsty.io/price) | [Release Notes](https://pigsty.io/docs/about/release) | [中文](https://pigsty.cc/) [](https://pigsty.io) Pigsty scales from [**laptop**](https://pigsty.io/docs/setup) to [**datacenter**](https://pigsty.io/docs/deploy). Enterprise-grade. Developer-friendly. [**Get Started**](https://pigsty.io/docs/setup/install) with the latest [**v4.5.0**](https://github.com/pgsty/pigsty/releases/tag/v4.5.0): ```bash curl -fsSL https://repo.pigsty.io/get | bash -s v4.5.0 ``` ## Features - **Extensible**: **575** [**PG Extensions**](https://pigsty.io/ext/list) & **12** [**PG Kernels**](https://pigsty.io/docs/pgsql/kernel) available, Packed for [**16 Linux Platforms**](https://pigsty.io/docs/ref/linux), Battery-Included. - **Reliable**: Self-healing [**HA**](https://pigsty.io/docs/concept/ha) clusters with pre-configured [**PITR**](https://pigsty.io/docs/pgsql/backup) and built-in [**ACL**](https://pigsty.io/docs/pgsql/config/acl), [**CA & SSL**](https://pigsty.io/docs/infra/admin/cert) secure best practice. - **Observable**: SOTA monitoring for [**PG**](https://demo.pigsty.io/ui/d/pgrds-instance/pgrds-instance) / [**Infra**](https://pigsty.io/docs/infra) / [**Node**](https://pigsty.io/docs/node) based on the **Victoria** & **Grafana** stack: [**Demo**](https://demo.pigsty.io) & [**Gallery**](https://github.com/pgsty/pigsty/wiki/Gallery). - **Scalable**: Auto-routed & pooled customizable database [**Services**](https://pigsty.io/docs/pgsql/service) [**Access**](https://pigsty.io/docs/pgsql/service#access-service) with **haproxy**, **pgbouncer**, and **VIP**. - **Maintainable**: [**One-Cmd Deploy**](https://pigsty.io/docs/setup/install), [**Admin SOP**](https://pigsty.io/docs/pgsql/admin), **Auto-Tune**, **Local Repo**, [**IaC**](https://pigsty.io/docs/pgsql/config) and [**Vagrant**](https://pigsty.io/docs/deploy/vagrant) / [**Terraform**](https://pigsty.io/docs/deploy/terraform) support. - **Composable**: Bonus [**Modules**](https://pigsty.io/docs/ref/module) such as [**Redis / Valkey**](https://pigsty.io/docs/redis), [**MinIO**](https://pigsty.io/docs/minio), [**Etcd**](https://pigsty.io/docs/etcd), [**Docker**](https://pigsty.io/docs/app), [**DuckDB**](https://pigsty.io/docs/pilot/duckdb), [**Supabase**](https://pigsty.io/docs/app/supabase), [**& More**](https://pigsty.io/docs/ref/module). - **Controllable**: Self-sufficient [**Infra Closure**](https://pigsty.io/docs/infra), Cloud Neutral FOSS. Run on [**bare Linux**](https://pigsty.io/docs/ref/linux) without [**Docker & K8S**](https://vonng.com/en/db/db-in-k8s/). - **Affordable**: Self-Hosting like a [**Pro**](https://pigsty.io/price/), without the cost of [**RDS**](https://vonng.com/en/cloud/rds/). Empowering Cloud-Exit for Ultra [**Cost Saving!**](https://pigsty.io/docs/about/compare/) You can even use many exotic [**PG Kernel Forks**](https://pigsty.io/docs/pgsql/kernel) as in-place replacement and wrap it as a full RDS service: [](https://pigsty.io/docs/pgsql/kernel/postgres) [](https://pigsty.io/docs/app/supabase) [](https://pigsty.io/docs/pgsql/kernel/citus) [](https://pigsty.io/docs/pgsql/kernel/babelfish) [](https://pigsty.io/docs/pgsql/kernel/ivorysql) [](https://pigsty.io/docs/pgsql/kernel/openhalo) [](https://pigsty.io/docs/conf/mongo) [](https://pigsty.io/docs/pgsql/kernel/percona) [](https://pigsty.io/docs/pgsql/kernel/orioledb) [](https://pigsty.io/docs/pgsql/kernel/agensgraph) [](https://pigsty.io/docs/pgsql/kernel/pgedge) [](https://pigsty.io/docs/pgsql/kernel/polardb) [](https://pigsty.io/docs/pgsql/kernel/cloudberry) | Kernel | Key Feature | Description | |:------------------------------------------------------------:|:-------------------------------:|:-------------------------------------------------| | [PostgreSQL](https://pigsty.io/docs/pgsql/kernel/postgres) | **Battery-Included** | Original PGDG PostgreSQL with 575 extensions | | [Supabase](https://pigsty.io/docs/app/supabase) | **Backend as Service** | BaaS over PostgreSQL, Firebase alternative | | [Citus](https://pigsty.io/docs/pgsql/kernel/citus) | **Horizontal Scaling** | Distributive PostgreSQL via native extension | | [Babelfish](https://pigsty.io/docs/pgsql/kernel/babelfish) | **SQL Server Simulation** | Microsoft SQL Server wire-compatibility | | [IvorySQL](https://pigsty.io/docs/pgsql/kernel/ivorysql) | **Oracle Simulation** | Oracle Grammar and PL/SQL compatible | | [OpenHalo](https://pigsty.io/docs/pgsql/kernel/openhalo) | **MySQL Simulation** | MySQL wire-protocol compatibility | | [DocumentDB](https://pigsty.io/docs/conf/mongo) | **MongoDB Simulation** | MongoDB wire-protocol compatibility | | [Percona](https://pigsty.io/docs/pgsql/kernel/percona) | **Transparent Data Encryption** | Percona Distribution with pg_tde | | [OrioleDB](https://pigsty.io/docs/pgsql/kernel/orioledb) | **OLTP Optimization Engine** | No bloat, No XID Wraparound, S3 Storage | | [PolarDB](https://pigsty.io/docs/pgsql/kernel/polardb) | **Aurora flavor RAC** | RAC, China domestic compliance | | [AgensGraph](https://pigsty.io/docs/pgsql/kernel/agensgraph) | **Graph-Native Database** | Transactional graph database based on PostgreSQL | | [pgEdge](https://pigsty.io/docs/pgsql/kernel/pgedge) | **Multi-Master Distributive** | Geo-Distributive multi-writer Postgres | | [Cloudberry](https://pigsty.io/docs/pgsql/kernel/cloudberry) | **MPP Data Warehouse** | Greenplum 7 Apache OSS Fork | And gather the synergistic superpowers of all [**575 PostgreSQL Extensions**](https://pigsty.io/ext/list) all together: [](https://pigsty.io/ext) ## Get Started [](https://pigsty.io/docs/pgsql) [](https://pigsty.io/docs/node) [](https://pigsty.io/docs/node) [](https://pigsty.io/docs/ref/linux#el) [](https://pigsty.io/docs/ref/linux#debian) [](https://pigsty.io/docs/ref/linux#ubuntu) [](https://pigsty.io/docs/setup/docker) [**Prepare**](https://pigsty.io/docs/deploy/prepare) a fresh `x86_64` / `aarch64` node runs any [**compatible**](https://pigsty.io/docs/ref/linux) **Linux** OS Distros, then [**Install**](https://pigsty.io/docs/setup/install#install) **Pigsty** with: ```bash curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty; ``` Then [**configure**](https://pigsty.io/docs/concept/iac/configure) and run the [**`deploy.yml`**](https://pigsty.io/docs/setup/playbook) playbook with an [**admin user**](https://pigsty.io/docs/deploy/admin) (**nopass** `ssh` & `sudo`): ```bash ./configure -g # generate config and random passwords ./deploy.yml # deploy everything on current node ``` Finally, you will get a pigsty [**singleton node ready**](https://pigsty.io/docs/setup/install), with [**WebUI**](https://pigsty.io/docs/setup/webui) on port `80/443` and [**Postgres**](https://pigsty.io/docs/setup/pgsql) on port `5432`. For dev/testing purposes, you can also run Pigsty inside [**Docker**](https://pigsty.io/docs/setup/docker) containers: `cd docker; make launch` -------- > [**Single-Node Setup**](https://pigsty.io/docs/setup/install) | [**Production Deploy**](https://pigsty.io/docs/deploy) | [**Offline Install**](https://pigsty.io/docs/setup/offline) | [**Minimal Install**](https://pigsty.io/docs/setup/slim) | [**Docker Install**](https://pigsty.io/docs/setup/docker) | [**Run Supabase**](https://pigsty.io/docs/app/supabase) Install with the pig cli Then you can launch pigsty with `pig sty` sub command: ```bash curl -fsSL https://repo.pigsty.io/pig | bash # install pig pig sty init # install latest pigsty src to ~/pigsty pig sty conf # auto-generate pigsty.yml config file pig sty deploy # run the deploy.yml playbook ``` Install with get script ``` /* Detailed source-code truncated for AI context efficiency. */ ``` > HINT: To install a specific version, pass the version string as the first parameter: > > ```bash > curl -fsSL https://repo.pigsty.io/get | bash -s v4.5.0 > ``` Clone src with git You can also download the pigsty source with `git`, remember to check out a specific version tag, the `main` branch is for development. ```bash git clone https://github.com/pgsty/pigsty; cd pigsty; git checkout v4.5.0 ``` ## Architecture Pigsty uses a [**modular**](https://pigsty.io/docs/concept/arch) design: you can [**use one or all**](https://pigsty.io/docs/deploy/planning), with best-of-breed products integrated as a platform. [](https://pigsty.io/docs/concept/arch) [](https://pigsty.io/docs/pgsql) Self-healing PostgreSQL HA cluster powered by Patroni, Pgbouncer, PgBackrest & HAProxy [](https://pigsty.io/docs/infra) Nginx, Local Repo, DNSMasq, and the entire Victoria & Grafana observability stack. [](https://pigsty.io/docs/node) Init node name, repo, pkg, NTP, ssh, admin, tune, expose services, collect logs & metrics. [](https://pigsty.io/docs/etcd) Etcd cluster is used as a reliable distributed configuration store by PostgreSQL HA agents. You can compose them freely in a declarative manner. `INFRA` & `NODE` will suffice for host monitoring. `ETCD` and `PGSQL` are used for HA PG clusters; installing them on multiple nodes automatically forms HA clusters. The default [`deploy.yml`](https://github.com/pgsty/pigsty/blob/main/deploy.yml) playbook will deploy `INFRA`, `NODE`, `ETCD` & `PGSQL` on the current node. This gives you an out-of-the-box PostgreSQL singleton instance (`admin_ip:5432`) with everything ready. [](https://pigsty.io/docs/concept/arch) The node can be used as an admin controller to deploy & monitor more nodes & clusters. For example, you can install these **6** **OPTIONAL** [extra modules](https://pigsty.io/docs/ref/module#extra-modules) for advanced use cases: [](https://pigsty.io/docs/minio) S3-compatible object storage service; used as an optional central backup server for `PGSQL`. [](https://pigsty.io/docs/redis) Deploy Redis or Valkey servers in standalone master-replica, sentinel, and native cluster mode. [](https://pigsty.io/docs/docker) Launch optional docker daemons to run other stateless parts besides Pigsty RDS. [](https://pigsty.io/docs/juice) JuiceFS can mount S3/MinIO, and even PostgreSQL as a filesystem shared by multi users. [](https://pigsty.io/docs/vibe) Vibe coding environment with VS Code Server, JupyterLab, Node.js, and Claude Code. Of course, you can deploy different kinds of HA **PostgreSQL** clusters on multiple nodes, as much as you want. ---------------- ## PostgreSQL RDS To deploy an [**additional**](https://pigsty.io/docs/deploy) 3-node HA Postgres cluster `pg-test`. Add the cluster [**definition**](https://github.com/pgsty/pigsty/blob/main/conf/ha/full.yml#L66) to the [**config inventory**](https://pigsty.io/docs/concept/iac/inventory): ```yaml pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } 10.10.10.12: { pg_seq: 2, pg_role: replica } 10.10.10.13: { pg_seq: 3, pg_role: offline } vars: { pg_cluster: pg-test } ``` The default config file is [`pigsty.yml`](https://github.com/pgsty/pigsty/blob/main/pigsty.yml) under pigsty home, add the snippet above to the `all.children.pg-test`, Then, create the cluster with built-in playbooks in one command: ```bash bin/pgsql-add pg-test # init pg-test cluster ``` Example: Complex PostgreSQL Customization This config file provides a detailed example of a complex PostgreSQL cluster `pg-meta` with multiple [databases](https://pigsty.io/docs/pgsql/config/db), [users](https://pigsty.io/docs/pgsql/config/user), and [service](https://pigsty.io/docs/pgsql/service) definition: ``` /* Detailed source-code truncated for AI context efficiency. */ ``` [](https://pigsty.io/img/pigsty/home.jpg) It will create a cluster with everything properly configured: [**High Availability**](https://pigsty.io/docs/concept/ha) powered by patroni & etcd; [**Point-In-Time-Recovery**](https://pigsty.io/docs/concept/pitr) powered by pgBackRest & optional MinIO / S3; auto-routed, pooled [**Services & Access**](https://pigsty.io/docs/pgsql/service#default-service) pooled by pgBouncer and exposed by haproxy; and out-of-the-box [**Monitoring**](https://pigsty.io/docs/pgsql/monitor/dashboard) & alerting powered by the **`INFRA`** module. [](https://pigsty.io/docs/concept/ha) The cluster keeps serving as long as **ANY** instance survives, with excellent [**Fault-Tolerance**](https://pigsty.io/docs/concept/ha/failure) performance: > [**RPO = 0**](https://pigsty.io/docs/concept/ha/rpo) on sync mode, **RPO < 1MB** on async mode; **RTO ~ 2s** on switchover, [**RTO < 30s**](https://pigsty.io/docs/concept/ha/rto) on failover. ---------------- ## Customization Pigsty is highly customizable. You can describe the entire database and infra deployment with **300+** [**parameters**](https://pigsty.io/docs/concept/iac/inventory) in a single config file and materialize them with one command. There are many built-in configuration [**templates**](https://pigsty.io/docs/concept/iac/template). Example: Sandbox (4-node) with two PG cluster The [`conf/full.yml`](https://github.com/pgsty/pigsty/blob/main/conf/full.yml) utilizes four nodes to deploy two PostgreSQL clusters, `pg-meta` and `pg-test`: ```yaml pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_users: - {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user } - {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database } pg_databases: - {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty]} pg_hba_rules: - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'} pg_vip_enabled: true pg_vip_address: 10.10.10.2/24 # pgsql 3 node ha cluster: pg-test pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } # primary instance, leader of cluster 10.10.10.12: { pg_seq: 2, pg_role: replica } # replica instance, follower of leader 10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access vars: pg_cluster: pg-test # define pgsql cluster name pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }] pg_databases: [{ name: test }] pg_vip_enabled: true pg_vip_address: 10.10.10.3/24 ``` You can even deploy PostgreSQL with different major versions and kernel forks in the same deployment: [](https://pigsty.io/docs/pgsql/kernel) Example: Security Setup & Delayed Replica The following [`conf/safe.yml`](https://github.com/pgsty/pigsty/blob/main/conf/ha/safe.yml) provisions a 4-node [security](https://pigsty.io/docs/deploy/security//) enhanced PostgreSQL cluster `pg-meta` with a delayed replica `pg-meta-delay`: ``` /* Detailed source-code truncated for AI context efficiency. */ ``` Example: Horizontal Sharding with Citus You can perform horizontal sharding on vanilla postgres with [**`CITUS`**](https://pigsty.io/docs/pgsql/kernel/citus/). ``` /* Detailed source-code truncated for AI context efficiency. */ ``` [](https://pigsty.io/docs/pgsql/kernel/citus) You can deploy different kinds of PostgreSQL instance such as [`primary`](https://pigsty.io/docs/pgsql/config/cluster#primary), [`replica`](https://pigsty.io/docs/pgsql/config/cluster#replica), [`offline`](https://pigsty.io/docs/pgsql/config/cluster#offline), [`delayed`](https://pigsty.io/docs/pgsql/config/cluster#delayed), [`sync standby`](https://pigsty.io/docs/pgsql/config/cluster#sync-standby), etc., and customize with scene-optimize [**config templates**](https://pigsty.io/docs/concept/iac/template) and all **575** [**extensions**](https://pigsty.io/ext/list) out-of-the-box. You can define [**Users**](https://pigsty.io/docs/pgsql/config/user), [**Databases**](https://pigsty.io/docs/pgsql/config/db), [**Service**](https://pigsty.io/docs/pgsql/service), [**HBAs**](https://pigsty.io/docs/pgsql/config/hba) and other entities with code and provision them in one pass. -------- You can also self-host postgres-centric software like [**`SUPABASE`**](https://pigsty.io/docs/app/supabase), [**`Odoo`**](https://pigsty.io/docs/app/odoo) & [**`Dify`**](https://pigsty.io/docs/app/dify), Electric, GitLab, ... with Pigsty: Example: Self-hosting Supabase You can launch a [self-hosting supabase](https://pigsty.io/docs/app/supabase) with MinIO and PostgreSQL with just several commands: ```bash ./configure -c supabase # use supabase config ./deploy.yml # install pigsty ./docker.yml # install docker compose ./app.yml # launch supabase stateless part with docker ``` The [`conf/supabase.yml`](https://github.com/pgsty/pigsty/blob/main/conf/supabase.yml) just describes everything you need: ``` /* Detailed source-code truncated for AI context efficiency. */ ``` There are other pro, beta, or pilot modules, and there will be more coming in the future: [](https://pigsty.io/docs/pgsql/kernel/supabase) [](https://pigsty.io/docs/kafka) [](https://pigsty.io/docs/pilot/mysql) [](https://pigsty.io/docs/pilot/duckdb) [](https://pigsty.io/docs/pilot/tigerbeetle) [](https://pigsty.io/docs/infra/) [](https://pigsty.io/docs/pilot/kube) [](https://pigsty.io/docs/pilot/consul) [](https://pigsty.io/docs/vibe/) ---------------- ## Compatibility We recommend using Rocky Linux 9.8 / 10.2, Debian 12.15 / 13.6, and Ubuntu 22.04.5 / 24.04.4 / 26.04.0 for production use. Pigsty runs directly on bare Linux, and focuses on actively maintained mainstream LTS [**Linux Distributions**](https://pigsty.io/docs/ref/linux): | Code | Distro | Minor | `x86_64` | `aarch64` | |:--------:|-------------------------|---------|:---------------------------------------------------:|:-----------------------------------------------------:| | **EL10** | RHEL / Rocky / Alma 10 | 10.2 | [`el10.x86_64`](roles/node_id/vars/el10.x86_64.yml) | [`el10.aarch64`](roles/node_id/vars/el10.aarch64.yml) | | **EL9** | RHEL / Rocky / Alma 9 | 9.8 | [`el9.x86_64`](roles/node_id/vars/el9.x86_64.yml) | [`el9.aarch64`](roles/node_id/vars/el9.aarch64.yml) | | **EL8** | RHEL / Rocky / Alma 8 | 8.10 | [`el8.x86_64`](roles/node_id/vars/el8.x86_64.yml) | [`el8.aarch64`](roles/node_id/vars/el8.aarch64.yml) | | **U26** | Ubuntu 26.04 (resolute) | 26.04.0 | [`u26.x86_64`](roles/node_id/vars/u26.x86_64.yml) | [`u26.aarch64`](roles/node_id/vars/u26.aarch64.yml) | | **U24** | Ubuntu 24.04 (noble) | 24.04.4 | [`u24.x86_64`](roles/node_id/vars/u24.x86_64.yml) | [`u24.aarch64`](roles/node_id/vars/u24.aarch64.yml) | | **U22** | Ubuntu 22.04 (jammy) | 22.04.5 | [`u22.x86_64`](roles/node_id/vars/u22.x86_64.yml) | [`u22.aarch64`](roles/node_id/vars/u22.aarch64.yml) | | **D13** | Debian 13 (trixie) | 13.6 | [`d13.x86_64`](roles/node_id/vars/d13.x86_64.yml) | [`d13.aarch64`](roles/node_id/vars/d13.aarch64.yml) | | **D12** | Debian 12 (bookworm) | 12.15 | [`d12.x86_64`](roles/node_id/vars/d12.x86_64.yml) | [`d12.aarch64`](roles/node_id/vars/d12.aarch64.yml) | ## Sponsor Many thanks to our contributors and [sponsors](https://pigsty.io/docs/about/sponsor/) for making Pigsty possible. Special thanks to MiraclePlus for funding, to Cloudflare for hosting the Pigsty repo, Jetbrains for the dev tools, and to Vercel for hosting the Pigsty website. | Vercel | JetBrains | |:--------------------------------------------------------------------:|:-----------------------------------------------------------------:| | [](https://vercel.com/oss) | [](jb.gg/OpenSource) | ## License Pigsty is licensed under [**Apache-2.0**](LICENSE). Check [**NOTICE**](NOTICE) and [**License Documentation**](https://pigsty.io/docs/about/license) for details. ## About [](https://pigsty.io) [](https://github.com/pgsty/pigsty/discussions) [](https://t.me/joinchat/gV9zfZraNPM3YjFh) [](https://discord.gg/j5pG8qfKxU) [](https://pigsty.io/img/pigsty/pigsty-cc.jpg) [](https://qm.qq.com/q/vm8LIeUqGc) [](https://vonng.com/en) [](https://github.com/Vonng) [](mailto:rh@vonng.com) [-red?logo=c&color=steelblue)](https://github.com/Vonng) [](https://pigsty.io/docs/about/license/) [](https://pigsty.cc/price) ## 2. In-Tree Documentation Chapters (pgsty/pigsty) ## File: README.md # PIGSTY - Self-Host Postgres like a Pro [](https://pigsty.io) [](https://pigsty.cc) [](https://github.com/pgsty/pigsty/releases) [](https://pigsty.io/docs/about/license/) [](https://star-history.com/#pgsty/pigsty&Date) [](https://pigsty.io/ext/list) [](https://deepwiki.com/pgsty/pigsty) Enterprise-Grade Open-Source **PostgreSQL** Distribution with HA, PITR, IaC, Monitor, and 575 PG extensions. > "**P**ostgreSQL **I**n **G**reat **STY**le": **P**ostgres, **I**nfras, **G**raphics, **S**ervice, **T**oolbox, it's all **Y**ours. [Website](https://pigsty.io) | [ORG](https://github.com/pgsty) | [PGEXT](https://pigsty.io/ext) | [Piglet](https://pigsty.io/docs/piglet) | [Public Demo](https://demo.pigsty.io) | [Blog](https://pigsty.io/blog/) | [Discuss](https://github.com/orgs/pgsty/discussions) | [Support](https://pigsty.io/price) | [Release Notes](https://pigsty.io/docs/about/release) | [中文](https://pigsty.cc/) [](https://pigsty.io) Pigsty scales from [**laptop**](https://pigsty.io/docs/setup) to [**datacenter**](https://pigsty.io/docs/deploy). Enterprise-grade. Developer-friendly. [**Get Started**](https://pigsty.io/docs/setup/install) with the latest [**v4.5.0**](https://github.com/pgsty/pigsty/releases/tag/v4.5.0): ```bash curl -fsSL https://repo.pigsty.io/get | bash -s v4.5.0 ``` ## Features - **Extensible**: **575** [**PG Extensions**](https://pigsty.io/ext/list) & **12** [**PG Kernels**](https://pigsty.io/docs/pgsql/kernel) available, Packed for [**16 Linux Platforms**](https://pigsty.io/docs/ref/linux), Battery-Included. - **Reliable**: Self-healing [**HA**](https://pigsty.io/docs/concept/ha) clusters with pre-configured [**PITR**](https://pigsty.io/docs/pgsql/backup) and built-in [**ACL**](https://pigsty.io/docs/pgsql/config/acl), [**CA & SSL**](https://pigsty.io/docs/infra/admin/cert) secure best practice. - **Observable**: SOTA monitoring for [**PG**](https://demo.pigsty.io/ui/d/pgrds-instance/pgrds-instance) / [**Infra**](https://pigsty.io/docs/infra) / [**Node**](https://pigsty.io/docs/node) based on the **Victoria** & **Grafana** stack: [**Demo**](https://demo.pigsty.io) & [**Gallery**](https://github.com/pgsty/pigsty/wiki/Gallery). - **Scalable**: Auto-routed & pooled customizable database [**Services**](https://pigsty.io/docs/pgsql/service) [**Access**](https://pigsty.io/docs/pgsql/service#access-service) with **haproxy**, **pgbouncer**, and **VIP**. - **Maintainable**: [**One-Cmd Deploy**](https://pigsty.io/docs/setup/install), [**Admin SOP**](https://pigsty.io/docs/pgsql/admin), **Auto-Tune**, **Local Repo**, [**IaC**](https://pigsty.io/docs/pgsql/config) and [**Vagrant**](https://pigsty.io/docs/deploy/vagrant) / [**Terraform**](https://pigsty.io/docs/deploy/terraform) support. - **Composable**: Bonus [**Modules**](https://pigsty.io/docs/ref/module) such as [**Redis / Valkey**](https://pigsty.io/docs/redis), [**MinIO**](https://pigsty.io/docs/minio), [**Etcd**](https://pigsty.io/docs/etcd), [**Docker**](https://pigsty.io/docs/app), [**DuckDB**](https://pigsty.io/docs/pilot/duckdb), [**Supabase**](https://pigsty.io/docs/app/supabase), [**& More**](https://pigsty.io/docs/ref/module). - **Controllable**: Self-sufficient [**Infra Closure**](https://pigsty.io/docs/infra), Cloud Neutral FOSS. Run on [**bare Linux**](https://pigsty.io/docs/ref/linux) without [**Docker & K8S**](https://vonng.com/en/db/db-in-k8s/). - **Affordable**: Self-Hosting like a [**Pro**](https://pigsty.io/price/), without the cost of [**RDS**](https://vonng.com/en/cloud/rds/). Empowering Cloud-Exit for Ultra [**Cost Saving!**](https://pigsty.io/docs/about/compare/) You can even use many exotic [**PG Kernel Forks**](https://pigsty.io/docs/pgsql/kernel) as in-place replacement and wrap it as a full RDS service: [](https://pigsty.io/docs/pgsql/kernel/postgres) [](https://pigsty.io/docs/app/supabase) [](https://pigsty.io/docs/pgsql/kernel/citus) [](https://pigsty.io/docs/pgsql/kernel/babelfish) [](https://pigsty.io/docs/pgsql/kernel/ivorysql) [](https://pigsty.io/docs/pgsql/kernel/openhalo) [](https://pigsty.io/docs/conf/mongo) [](https://pigsty.io/docs/pgsql/kernel/percona) [](https://pigsty.io/docs/pgsql/kernel/orioledb) [](https://pigsty.io/docs/pgsql/kernel/agensgraph) [](https://pigsty.io/docs/pgsql/kernel/pgedge) [](https://pigsty.io/docs/pgsql/kernel/polardb) [](https://pigsty.io/docs/pgsql/kernel/cloudberry) | Kernel | Key Feature | Description | |:------------------------------------------------------------:|:-------------------------------:|:-------------------------------------------------| | [PostgreSQL](https://pigsty.io/docs/pgsql/kernel/postgres) | **Battery-Included** | Original PGDG PostgreSQL with 575 extensions | | [Supabase](https://pigsty.io/docs/app/supabase) | **Backend as Service** | BaaS over PostgreSQL, Firebase alternative | | [Citus](https://pigsty.io/docs/pgsql/kernel/citus) | **Horizontal Scaling** | Distributive PostgreSQL via native extension | | [Babelfish](https://pigsty.io/docs/pgsql/kernel/babelfish) | **SQL Server Simulation** | Microsoft SQL Server wire-compatibility | | [IvorySQL](https://pigsty.io/docs/pgsql/kernel/ivorysql) | **Oracle Simulation** | Oracle Grammar and PL/SQL compatible | | [OpenHalo](https://pigsty.io/docs/pgsql/kernel/openhalo) | **MySQL Simulation** | MySQL wire-protocol compatibility | | [DocumentDB](https://pigsty.io/docs/conf/mongo) | **MongoDB Simulation** | MongoDB wire-protocol compatibility | | [Percona](https://pigsty.io/docs/pgsql/kernel/percona) | **Transparent Data Encryption** | Percona Distribution with pg_tde | | [OrioleDB](https://pigsty.io/docs/pgsql/kernel/orioledb) | **OLTP Optimization Engine** | No bloat, No XID Wraparound, S3 Storage | | [PolarDB](https://pigsty.io/docs/pgsql/kernel/polardb) | **Aurora flavor RAC** | RAC, China domestic compliance | | [AgensGraph](https://pigsty.io/docs/pgsql/kernel/agensgraph) | **Graph-Native Database** | Transactional graph database based on PostgreSQL | | [pgEdge](https://pigsty.io/docs/pgsql/kernel/pgedge) | **Multi-Master Distributive** | Geo-Distributive multi-writer Postgres | | [Cloudberry](https://pigsty.io/docs/pgsql/kernel/cloudberry) | **MPP Data Warehouse** | Greenplum 7 Apache OSS Fork | And gather the synergistic superpowers of all [**575 PostgreSQL Extensions**](https://pigsty.io/ext/list) all together: [](https://pigsty.io/ext) ## Get Started [](https://pigsty.io/docs/pgsql) [](https://pigsty.io/docs/node) [](https://pigsty.io/docs/node) [](https://pigsty.io/docs/ref/linux#el) [](https://pigsty.io/docs/ref/linux#debian) [](https://pigsty.io/docs/ref/linux#ubuntu) [](https://pigsty.io/docs/setup/docker) [**Prepare**](https://pigsty.io/docs/deploy/prepare) a fresh `x86_64` / `aarch64` node runs any [**compatible**](https://pigsty.io/docs/ref/linux) **Linux** OS Distros, then [**Install**](https://pigsty.io/docs/setup/install#install) **Pigsty** with: ```bash curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty; ``` Then [**configure**](https://pigsty.io/docs/concept/iac/configure) and run the [**`deploy.yml`**](https://pigsty.io/docs/setup/playbook) playbook with an [**admin user**](https://pigsty.io/docs/deploy/admin) (**nopass** `ssh` & `sudo`): ```bash ./configure -g # generate config and random passwords ./deploy.yml # deploy everything on current node ``` Finally, you will get a pigsty [**singleton node ready**](https://pigsty.io/docs/setup/install), with [**WebUI**](https://pigsty.io/docs/setup/webui) on port `80/443` and [**Postgres**](https://pigsty.io/docs/setup/pgsql) on port `5432`. For dev/testing purposes, you can also run Pigsty inside [**Docker**](https://pigsty.io/docs/setup/docker) containers: `cd docker; make launch` -------- > [**Single-Node Setup**](https://pigsty.io/docs/setup/install) | [**Production Deploy**](https://pigsty.io/docs/deploy) | [**Offline Install**](https://pigsty.io/docs/setup/offline) | [**Minimal Install**](https://pigsty.io/docs/setup/slim) | [**Docker Install**](https://pigsty.io/docs/setup/docker) | [**Run Supabase**](https://pigsty.io/docs/app/supabase) Install with the pig cli Then you can launch pigsty with `pig sty` sub command: ```bash curl -fsSL https://repo.pigsty.io/pig | bash # install pig pig sty init # install latest pigsty src to ~/pigsty pig sty conf # auto-generate pigsty.yml config file pig sty deploy # run the deploy.yml playbook ``` Install with get script ``` /* Detailed source-code truncated for AI context efficiency. */ ``` > HINT: To install a specific version, pass the version string as the first parameter: > > ```bash > curl -fsSL https://repo.pigsty.io/get | bash -s v4.5.0 > ``` Clone src with git You can also download the pigsty source with `git`, remember to check out a specific version tag, the `main` branch is for development. ```bash git clone https://github.com/pgsty/pigsty; cd pigsty; git checkout v4.5.0 ``` ## Architecture Pigsty uses a [**modular**](https://pigsty.io/docs/concept/arch) design: you can [**use one or all**](https://pigsty.io/docs/deploy/planning), with best-of-breed products integrated as a platform. [](https://pigsty.io/docs/concept/arch) [](https://pigsty.io/docs/pgsql) Self-healing PostgreSQL HA cluster powered by Patroni, Pgbouncer, PgBackrest & HAProxy [](https://pigsty.io/docs/infra) Nginx, Local Repo, DNSMasq, and the entire Victoria & Grafana observability stack. [](https://pigsty.io/docs/node) Init node name, repo, pkg, NTP, ssh, admin, tune, expose services, collect logs & metrics. [](https://pigsty.io/docs/etcd) Etcd cluster is used as a reliable distributed configuration store by PostgreSQL HA agents. You can compose them freely in a declarative manner. `INFRA` & `NODE` will suffice for host monitoring. `ETCD` and `PGSQL` are used for HA PG clusters; installing them on multiple nodes automatically forms HA clusters. The default [`deploy.yml`](https://github.com/pgsty/pigsty/blob/main/deploy.yml) playbook will deploy `INFRA`, `NODE`, `ETCD` & `PGSQL` on the current node. This gives you an out-of-the-box PostgreSQL singleton instance (`admin_ip:5432`) with everything ready. [](https://pigsty.io/docs/concept/arch) The node can be used as an admin controller to deploy & monitor more nodes & clusters. For example, you can install these **6** **OPTIONAL** [extra modules](https://pigsty.io/docs/ref/module#extra-modules) for advanced use cases: [](https://pigsty.io/docs/minio) S3-compatible object storage service; used as an optional central backup server for `PGSQL`. [](https://pigsty.io/docs/redis) Deploy Redis or Valkey servers in standalone master-replica, sentinel, and native cluster mode. [](https://pigsty.io/docs/docker) Launch optional docker daemons to run other stateless parts besides Pigsty RDS. [](https://pigsty.io/docs/juice) JuiceFS can mount S3/MinIO, and even PostgreSQL as a filesystem shared by multi users. [](https://pigsty.io/docs/vibe) Vibe coding environment with VS Code Server, JupyterLab, Node.js, and Claude Code. Of course, you can deploy different kinds of HA **PostgreSQL** clusters on multiple nodes, as much as you want. ---------------- ## PostgreSQL RDS To deploy an [**additional**](https://pigsty.io/docs/deploy) 3-node HA Postgres cluster `pg-test`. Add the cluster [**definition**](https://github.com/pgsty/pigsty/blob/main/conf/ha/full.yml#L66) to the [**config inventory**](https://pigsty.io/docs/concept/iac/inventory): ```yaml pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } 10.10.10.12: { pg_seq: 2, pg_role: replica } 10.10.10.13: { pg_seq: 3, pg_role: offline } vars: { pg_cluster: pg-test } ``` The default config file is [`pigsty.yml`](https://github.com/pgsty/pigsty/blob/main/pigsty.yml) under pigsty home, add the snippet above to the `all.children.pg-test`, Then, create the cluster with built-in playbooks in one command: ```bash bin/pgsql-add pg-test # init pg-test cluster ``` Example: Complex PostgreSQL Customization This config file provides a detailed example of a complex PostgreSQL cluster `pg-meta` with multiple [databases](https://pigsty.io/docs/pgsql/config/db), [users](https://pigsty.io/docs/pgsql/config/user), and [service](https://pigsty.io/docs/pgsql/service) definition: ``` /* Detailed source-code truncated for AI context efficiency. */ ``` [](https://pigsty.io/img/pigsty/home.jpg) It will create a cluster with everything properly configured: [**High Availability**](https://pigsty.io/docs/concept/ha) powered by patroni & etcd; [**Point-In-Time-Recovery**](https://pigsty.io/docs/concept/pitr) powered by pgBackRest & optional MinIO / S3; auto-routed, pooled [**Services & Access**](https://pigsty.io/docs/pgsql/service#default-service) pooled by pgBouncer and exposed by haproxy; and out-of-the-box [**Monitoring**](https://pigsty.io/docs/pgsql/monitor/dashboard) & alerting powered by the **`INFRA`** module. [](https://pigsty.io/docs/concept/ha) The cluster keeps serving as long as **ANY** instance survives, with excellent [**Fault-Tolerance**](https://pigsty.io/docs/concept/ha/failure) performance: > [**RPO = 0**](https://pigsty.io/docs/concept/ha/rpo) on sync mode, **RPO < 1MB** on async mode; **RTO ~ 2s** on switchover, [**RTO < 30s**](https://pigsty.io/docs/concept/ha/rto) on failover. ---------------- ## Customization Pigsty is highly customizable. You can describe the entire database and infra deployment with **300+** [**parameters**](https://pigsty.io/docs/concept/iac/inventory) in a single config file and materialize them with one command. There are many built-in configuration [**templates**](https://pigsty.io/docs/concept/iac/template). Example: Sandbox (4-node) with two PG cluster The [`conf/full.yml`](https://github.com/pgsty/pigsty/blob/main/conf/full.yml) utilizes four nodes to deploy two PostgreSQL clusters, `pg-meta` and `pg-test`: ```yaml pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_users: - {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user } - {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database } pg_databases: - {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty]} pg_hba_rules: - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'} pg_vip_enabled: true pg_vip_address: 10.10.10.2/24 # pgsql 3 node ha cluster: pg-test pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } # primary instance, leader of cluster 10.10.10.12: { pg_seq: 2, pg_role: replica } # replica instance, follower of leader 10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access vars: pg_cluster: pg-test # define pgsql cluster name pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }] pg_databases: [{ name: test }] pg_vip_enabled: true pg_vip_address: 10.10.10.3/24 ``` You can even deploy PostgreSQL with different major versions and kernel forks in the same deployment: [](https://pigsty.io/docs/pgsql/kernel) Example: Security Setup & Delayed Replica The following [`conf/safe.yml`](https://github.com/pgsty/pigsty/blob/main/conf/ha/safe.yml) provisions a 4-node [security](https://pigsty.io/docs/deploy/security//) enhanced PostgreSQL cluster `pg-meta` with a delayed replica `pg-meta-delay`: ``` /* Detailed source-code truncated for AI context efficiency. */ ``` Example: Horizontal Sharding with Citus You can perform horizontal sharding on vanilla postgres with [**`CITUS`**](https://pigsty.io/docs/pgsql/kernel/citus/). ``` /* Detailed source-code truncated for AI context efficiency. */ ``` [](https://pigsty.io/docs/pgsql/kernel/citus) You can deploy different kinds of PostgreSQL instance such as [`primary`](https://pigsty.io/docs/pgsql/config/cluster#primary), [`replica`](https://pigsty.io/docs/pgsql/config/cluster#replica), [`offline`](https://pigsty.io/docs/pgsql/config/cluster#offline), [`delayed`](https://pigsty.io/docs/pgsql/config/cluster#delayed), [`sync standby`](https://pigsty.io/docs/pgsql/config/cluster#sync-standby), etc., and customize with scene-optimize [**config templates**](https://pigsty.io/docs/concept/iac/template) and all **575** [**extensions**](https://pigsty.io/ext/list) out-of-the-box. You can define [**Users**](https://pigsty.io/docs/pgsql/config/user), [**Databases**](https://pigsty.io/docs/pgsql/config/db), [**Service**](https://pigsty.io/docs/pgsql/service), [**HBAs**](https://pigsty.io/docs/pgsql/config/hba) and other entities with code and provision them in one pass. -------- You can also self-host postgres-centric software like [**`SUPABASE`**](https://pigsty.io/docs/app/supabase), [**`Odoo`**](https://pigsty.io/docs/app/odoo) & [**`Dify`**](https://pigsty.io/docs/app/dify), Electric, GitLab, ... with Pigsty: Example: Self-hosting Supabase You can launch a [self-hosting supabase](https://pigsty.io/docs/app/supabase) with MinIO and PostgreSQL with just several commands: ```bash ./configure -c supabase # use supabase config ./deploy.yml # install pigsty ./docker.yml # install docker compose ./app.yml # launch supabase stateless part with docker ``` The [`conf/supabase.yml`](https://github.com/pgsty/pigsty/blob/main/conf/supabase.yml) just describes everything you need: ``` /* Detailed source-code truncated for AI context efficiency. */ ``` There are other pro, beta, or pilot modules, and there will be more coming in the future: [](https://pigsty.io/docs/pgsql/kernel/supabase) [](https://pigsty.io/docs/kafka) [](https://pigsty.io/docs/pilot/mysql) [](https://pigsty.io/docs/pilot/duckdb) [](https://pigsty.io/docs/pilot/tigerbeetle) [](https://pigsty.io/docs/infra/) [](https://pigsty.io/docs/pilot/kube) [](https://pigsty.io/docs/pilot/consul) [](https://pigsty.io/docs/vibe/) ---------------- ## Compatibility We recommend using Rocky Linux 9.8 / 10.2, Debian 12.15 / 13.6, and Ubuntu 22.04.5 / 24.04.4 / 26.04.0 for production use. Pigsty runs directly on bare Linux, and focuses on actively maintained mainstream LTS [**Linux Distributions**](https://pigsty.io/docs/ref/linux): | Code | Distro | Minor | `x86_64` | `aarch64` | |:--------:|-------------------------|---------|:---------------------------------------------------:|:-----------------------------------------------------:| | **EL10** | RHEL / Rocky / Alma 10 | 10.2 | [`el10.x86_64`](roles/node_id/vars/el10.x86_64.yml) | [`el10.aarch64`](roles/node_id/vars/el10.aarch64.yml) | | **EL9** | RHEL / Rocky / Alma 9 | 9.8 | [`el9.x86_64`](roles/node_id/vars/el9.x86_64.yml) | [`el9.aarch64`](roles/node_id/vars/el9.aarch64.yml) | | **EL8** | RHEL / Rocky / Alma 8 | 8.10 | [`el8.x86_64`](roles/node_id/vars/el8.x86_64.yml) | [`el8.aarch64`](roles/node_id/vars/el8.aarch64.yml) | | **U26** | Ubuntu 26.04 (resolute) | 26.04.0 | [`u26.x86_64`](roles/node_id/vars/u26.x86_64.yml) | [`u26.aarch64`](roles/node_id/vars/u26.aarch64.yml) | | **U24** | Ubuntu 24.04 (noble) | 24.04.4 | [`u24.x86_64`](roles/node_id/vars/u24.x86_64.yml) | [`u24.aarch64`](roles/node_id/vars/u24.aarch64.yml) | | **U22** | Ubuntu 22.04 (jammy) | 22.04.5 | [`u22.x86_64`](roles/node_id/vars/u22.x86_64.yml) | [`u22.aarch64`](roles/node_id/vars/u22.aarch64.yml) | | **D13** | Debian 13 (trixie) | 13.6 | [`d13.x86_64`](roles/node_id/vars/d13.x86_64.yml) | [`d13.aarch64`](roles/node_id/vars/d13.aarch64.yml) | | **D12** | Debian 12 (bookworm) | 12.15 | [`d12.x86_64`](roles/node_id/vars/d12.x86_64.yml) | [`d12.aarch64`](roles/node_id/vars/d12.aarch64.yml) | ## Sponsor Many thanks to our contributors and [sponsors](https://pigsty.io/docs/about/sponsor/) for making Pigsty possible. Special thanks to MiraclePlus for funding, to Cloudflare for hosting the Pigsty repo, Jetbrains for the dev tools, and to Vercel for hosting the Pigsty website. | Vercel | JetBrains | |:--------------------------------------------------------------------:|:-----------------------------------------------------------------:| | [](https://vercel.com/oss) | [](jb.gg/OpenSource) | ## License Pigsty is licensed under [**Apache-2.0**](LICENSE). Check [**NOTICE**](NOTICE) and [**License Documentation**](https://pigsty.io/docs/about/license) for details. ## About [](https://pigsty.io) [](https://github.com/pgsty/pigsty/discussions) [](https://t.me/joinchat/gV9zfZraNPM3YjFh) [](https://discord.gg/j5pG8qfKxU) [](https://pigsty.io/img/pigsty/pigsty-cc.jpg) [](https://qm.qq.com/q/vm8LIeUqGc) [](https://vonng.com/en) [](https://github.com/Vonng) [](mailto:rh@vonng.com) [-red?logo=c&color=steelblue)](https://github.com/Vonng) [](https://pigsty.io/docs/about/license/) [](https://pigsty.cc/price) --- ## File: app/postgrest/README.md # PostgREST PostgREST: https://postgrest.org/en/stable/index.html Serve a RESTful API from any Postgres database This is an example of creating pigsty cmdb API with PostgREST ```bash cd ~/pigsty/app/postgrest; make up ``` http://10.10.10.10:8884 is the default endpoint for PostgREST Public API: http://api.pigsty.cc ## Makefile ```bash make up # pull up postgrest with docker compose make run # launch postgrest with docker make ui # run swagger ui container make view # print postgrest access point make log # tail -f postgrest logs make info # introspect postgrest with jq make stop # stop postgrest container make clean # remove postgrest container make rmui # remove swagger ui container make pull # pull latest postgrest image make rmi # remove postgrest image make save # save postgrest image to /tmp/docker/postgrest.tgz make load # load postgrest image from /tmp/docker/postgrest.tgz ``` ## Swagger UI Launch a swagger OpenAPI UI and visualize PostgREST API on 8883 with: ```bash docker run --init --name swagger -p 8883:8080 -e API_URL=http://10.10.10.10:8884 swaggerapi/swagger-ui # docker run -d -e API_URL=http://10.10.10.10:8884 -p 8883:8080 swaggerapi/swagger-editor # swagger editor ``` Check [http://10.10.10.10:8883/](http://10.10.10.10:8883/) --- METRICS --- - Files Extracted: 3 - Estimated Token Budget: ~11256 tokens - Recency Window: Active (< 180 days) - Canonical Reference: https://codewiki.google/github.com/pgsty/pigsty