v3.3：Extension 404，Odoo, Dify, Supabase, Nginx Enhancement

By Ruohang Feng（@Vonng | 发行注记 |
Tags:

After two months of careful polishing, Pigsty v3.3 is officially released! As an open-source, “battery-included” PostgreSQL distribution, Pigsty aims to optimize the PostgreSQL ecosystem and provide smooth self-hosting experience just like cloud RDS, with minimal maintenance and ease of use.

The newly released v3.3 focuses on three key areas: Postgres Extension, Self-Hosting Experience, and Application Templates,

400+ Available Extensions: Building a Strong Ecosystem

PostgreSQL has always been known for its rich extension mechanism, giving rise to a massive database ecosystem. Pigsty aligns with this trend by maximizing the plugin and extension capabilities of PostgreSQL.

About one year ago, I wrote an article titled “PostgreSQL is eating the Database World,” where I described PG’s trend and vision to dominate the database market through extensions. At that time, the number of available extensions in Pigsty was just 150, mainly consisting of PG’s built-in extensions (70) and those from the official PGDG repository.

Now, with Pigsty v3.3, the number of available extensions has skyrocketed to an astonishing 404 extensions! This means the users can instantly plug in almost any PostgreSQL extension, unlocking a wealth of powerful features for their databases.

Among the newly added extensions, there are some notable “rookie” plugins:

  • pg_documentdb, open-sourced by Microsoft, gives PostgreSQL document database capabilities, making it easier to store and query JSON documents.
  • pg_collection, a high-performance memory-optimized collection data type extension from AWS, adds advanced collection support to PG.
  • pg_tracing, open-sourced by DataDog, provides distributed tracing capabilities for PostgreSQL, useful for performance analysis and debugging.
  • pg_curl allows PostgreSQL to make requests using dozens of network protocols, expanding beyond just HTTP to call external APIs.
  • pgpdf enables users to read and perform full-text searches on PDF documents directly within SQL queries.

Additionally, over thirty Omni-series extensions developed by Omnigres open new doors for web application development within PG. Pigsty has forged a partnership with Omnigres, integrating and distributing their extensions. And omnigres can deliver these extensions from the Pigsty repository to their users, creating a mutually beneficial ecosystem.

We have also partnered with the FerretDB team to deliver a PostgreSQL-based MongoDB solution. Yes, you can now easily use the newly released FerretDB 2.0 to turn PG into a fully-functional MongoDB 5.0 alternative,
and with FerretDB 2.0, backed by Microsoft’s open-source DocumentDB, provides better performance and a more complete feature set.

Meanwhile, the DuckDB stitching competition is still ongoing, and Pigsty v3.3 quickly integrated the latest versions of pg_duckdb 0.3.1, pg_mooncake 0.1.2, and pg_analytics 0.5.4. These extensions add analytical capabilities to PostgreSQL, rivaling ClickHouse in certain dimensions.

Yes, on ClickHouse’s ClickBench leaderboard, PG extensions have now joined the top 10 tier. I believe that in this fierce competition, PostgreSQL will soon see an OLAP player akin to pgvector in the vector database ecosystem, shaking up the OLAP and big data markets.

With so many extensions available, installation and management have become a challenge. Our solution, pig, allows users to effortlessly manage and install extensions with a single command, unlocking the power of 400 extensions—without even needing Pigsty.

While having a unique and extensive extension library is a core competitive advantage for Pigsty, we also aim to contribute more to the PostgreSQL ecosystem, allowing more users to enjoy PostgreSQL’s powerful features. Thus, the pig package manager and the extension repository are open-source under the permissive Apache 2.0 license, and they are open to the public and industry peers. We’ve already seen two PostgreSQL vendors using the Pigsty extension repository to install extensions, and we’ve established a NextGen Package Manager interest group, inviting participants interested in PostgreSQL extension ecosystems to join.

Sefl-Hosting Experience: Nginx IaC and Free HTTPS Certs

Pigsty is not just a PostgreSQL distribution; it’s a complete monitoring infrastructure solution, including Etcd, MinIO, Redis, Docker deployment management, and even a toolbox for self-hosting websites.

Why? Because Pigsty offers a fully-featured Nginx configuration and a certbot Request SOP (Standard Operating Procedure). In fact, Pigsty’s own websites and software repositories are hosted using Pigsty itself. This solution was developed purely to meet my personal needs—how to quickly set up a proper website in minutes?

For instance, the Pigsty Chinese and English websites and beta repositories are defined on a single server with the following configuration. After installing Pigsty and adding a few lines to the configuration file, Nginx automatically creates the necessary configuration and generates the HTTPS certificate. Then, I simply use rsync/scp to upload the site content and repository files, and voilà—the website is live!

infra_portal:                     # domain names and upstream servers
  home         : { domain: home.pigsty.cc ,certbot: home.pigsty.cc }
  grafana      : { domain: demo.pigsty.cc ,endpoint: "${admin_ip}:3000" ,certbot: home.pigsty.cc}
  prometheus   : { domain: p.pigsty.cc    ,endpoint: "${admin_ip}:9090" ,certbot: home.pigsty.cc}
  alertmanager : { domain: a.pigsty.cc    ,endpoint: "${admin_ip}:9093" ,certbot: home.pigsty.cc}
  minio        : { domain: m.pigsty.cc    ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
  blackbox     : { endpoint: "${admin_ip}:9115" }
  loki         : { endpoint: "${admin_ip}:3100" }
  web.cc       : { domain: pigsty.cc      ,path: "/www/web.cc" , domains: [zh.pigsty.cc] ,certbot: home.pigsty.cc , conf: cc.conf }
  web.io       : { domain: en.pigsty.cc   ,path: "/www/web.io"                           ,certbot: home.pigsty.cc}
  repo         : { domain: beta.pigsty.cc ,path: "/www/repo"   , index: true ,certbot: home.pigsty.cc}

Starting with Pigsty v3.2, we’ve integrated certbot and made it the default on infrastructure nodes, allowing users to easily apply and renew HTTPS certificates with just one command, without worrying about the details. You no longer need to pay for expensive certificates or click through tedious processes.

Pigsty’s Nginx can proxy various services, use different domain names, and unify everything through ports 80/443, making firewall management and security best practices straightforward. Simply open ports 80/443 (and maybe 22/SSH), and you’re set.

Of course, all details and SOPs are updated in the doc, so you can quickly deploy a secure, reliable, high-performance website!

Application Templates: Simplified Deployment

Many software applications rely on PostgreSQL, and Pigsty has previously provided Docker Compose templates for such software. But before, users still had to manually copy application directories, modify .env configuration files, and use docker-compose to deploy.

Pigsty v3.3 introduces a new app.yml script that automates this process, turning the last mile of PostgreSQL-based Docker software delivery into a single command.

Let’s look at a few examples:

To deploy a full-featured Odoo ERP system, simply run the following commands:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty 
./bootstrap                # install ansible
./configure -c app/odoo    # use odoo config (please CHANGE CREDENTIALS in pigsty.yml)
./install.yml              # install pigsty
./docker.yml               # install docker compose
./app.yml                  # launch odoo stateless part with docker

For a complex AI workflow orchestration software like Dify, utilizing the high-availability, high-performance PostgreSQL cluster with PITR and IaC management, just a few lines of code are enough:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty 
./bootstrap                # install ansible
./configure -c app/dify    # use dify config (please CHANGE CREDENTIALS in pigsty.yml)
./install.yml              # install pigsty
./docker.yml               # install docker compose
./app.yml                  # launch dify stateless part with docker

Even for the popular Supabase self-hosting solution, we’ve simplified it into a one-click app that offers a beginner-friendly delivery experience:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty 
./bootstrap                # install ansible
./configure -c app/supa    # use supabase config (please CHANGE CREDENTIALS in pigsty.yml)
./install.yml              # install pigsty
./docker.yml               # install docker compose
./app.yml                  # launch supabase stateless part with docker

With just a few commands, you can move from bare metal to a fully operational production service in minutes. We plan to add more Docker-based applications and provide this simplified template and delivery model in the future.

pig Command Line Tool: Growing Build Capabilities

The command-line tool pig provided by Pigsty has also received significant upgrades. Pig v0.3 introduces a new subcommand, pig build, which lets you quickly set up a complete environment for building PG extensions on your server.

With over 400 extension plugins maintained in Pigsty, I personally handle over 200 of them, which is near double the number in the PGDG repository. Previously, building extensions required manually written scripts for basic automation, but with the increase in extensions, this approach became impractical. So, I spent two days integrating all the extension-building tools into the new pig build command.

For example, the workflow for building PG extensions on a specific OS distribution is as follows:

curl https://repo.pigsty.cc/pig | bash # Install pig
pig build repo        # Add upstream repositories
pig build tool        # Install build tools
pig build rust        # Configure Rust/PGRX toolchain (optional)
pig build spec        # Download build specifications
pig build proxy       # Configure proxy
pig build get citus   # Download source package for an extension like citus
pig build ext citus   # Build the citus extension from source

This is how 200+ extensions are built! With this tool, even if your operating system is not one of the top 10 supported by Pigsty, you can still easily DIY RPM/DEB packages for extensions.

A hidden feature is the automatic download of proxy configurations to simplify downloading dependencies from GitHub. Of course, I wouldn’t add that to the documentation! 😄

New Website: First Contact with Next.js

Another major improvement in Pigsty v3.3 is the infrastructure behind the websites. Starting with v3.3, the Pigsty global site (pigsty.io) and Pigsty China site (pigsty.cc) have been fully separated, each with its own domain, documentation, demos, and repositories.

I found a Next.js template and spent two days building a new Pigsty homepage. It actually turned out pretty well. The last time I worked on frontend stuff was in the era of jQuery, but surprisingly, React and Next.js weren’t that complicated, especially with the help of GPT-1 pro and Cursor. Even as a “newbie,” I was able to quickly create a landing page.

I experimented with hosting on native Vercel, exporting statically to Cloudflare Pages, as well as Alibaba Cloud’s ClawCloud in Singapore/Hong Kong and other cloud providers. Thanks to Pigsty’s self-hosting enhancement, the whole process was much smoother. The best options in terms of experience and cost-effectiveness turned out to be:

  1. Cloudflare for global hosting, and 2. Domestic on-demand cloud servers in Beijing. I might write a dedicated post about this in the future.

During this process, I automated the website-building flow to such an extent that now, in just ten minutes, I can deploy a Pigsty doc and repository site in any region.

In addition to a beautiful homepage, the content of the documentation is more important. In Pigsty v3.3, I’ve integrated the ext.pigsty.io PG extension directory into the documentation site at pigsty.io/ext, which is available in both Chinese and English for easy access. I also developed a small tool to automatically scan extensions from both Pigsty and PGDG repositories, generating database records and information pages so that users can easily see which extensions are missing or have inconsistent versions. Users can directly browse and download RPM/DEB packages from the website.

Other Improvements

Pigsty v3.0 already supports multiple PG forks, such as IvorySQL (Oracle compatibility), PolarDB O, Babelfish (SQL Server compatibility), and PolarDB for PostgreSQL.

In version 3.3, we’ve followed up with IvorySQL 4.2, compatible with PG 17, and resolved the issue with pgBackRest backups not working on IvorySQL. Now, IvorySQL runs smoothly and as fully functional as the standard PG kernel.

I’ve also pushed the PolarDB team to provide DEB packages for Debian and ARM64 platforms. Yes, PolarDB now runs smoothly on all ten major operating systems supported by Pigsty!

As for why I use the PolarDB kernel instead of PostgreSQL—if your boss insists on a “domestic” solution, PolarDB may be the easiest, most cost-effective choice!

By the way, the PolarDB Developer Conference is happening tomorrow in Beijing, and I plan to attend to see what new features they’ve come up with.

Looking Ahead to Pigsty 3.4

As we celebrate the success of version 3.3, the Pigsty project is already looking ahead to version 3.4.

Honestly, I feel like Pigsty has already achieved a grand slam with PostgreSQL. Virtually every major extension plugin in the ecosystem has been integrated. To continue expanding, we may need to focus on some forks, like Neon, OrioleDB, and PolarDB, and support extensions for these branches.

But another direction I see as meaningful is further developing the pig command-line tool: integrating more operations into this compact Go-based tool and eventually creating a complete PostgreSQL management toolchain (ideally replacing Ansible). I think when this tool is mature, it will coincide with Pigsty v4.

Another long-term direction is converting more software that uses PostgreSQL into one-click solutions like Odoo, Dify, Supabase, and more. Many SMEs need such solutions—GitLab (code hosting), Odoo (ERP), Dify (AI workflow orchestration), Supabase (backend), MatterMost (chat), Discourse (forum), all powered by PostgreSQL.

v3.3.0 Release Note

New Features

  • Total available postgres extensions: 404!
  • New Website Design: Global website: pigsty.io and zh-cn Site: pigsty.cc
  • PostgreSQL February Minor Updates: 17.4, 16.8, 15.12, 14.17, 13.20
  • New Feature: app.yml for installing apps like Odoo, Supabase, Dify.
  • New Feature: Fine-grained Nginx config customization in infra_portal.
  • New Feature: Added Certbot support for quick free HTTPS certificate requests.
  • Improvement: Added Aliyun mirror for Debian Security repository.
  • Improvement: pgBackRest backup support for IvorySQL kernel.
  • Improvement: ARM64 and Debian/Ubuntu support for PolarDB.
  • pg_exporter 0.8.0 now supports new metrics in pgbouncer 1.24.
  • New Feature: Auto-completion for common commands like git, docker, systemctl #506 #507 by @waitingsong.
  • Improvement: Refined ignore_startup_parameters in pgbouncer config template #488 by @waitingsong.
  • Extension Catalog: Detailed information and download links for RPM/DEB binary packages.
  • Extension Build: pig CLI now auto-sets PostgreSQL extension build environment.

New Extensions

12 new PostgreSQL extensions added, bringing the total to 404 available extensions.

API Change

  • New Parameter: node_aliases to add command aliases for Nodes.
  • New Option: using plain extension name in pg_default_extensions
  • New Option: using conf, index, log, certbot options in infra_portal entries.
  • New Defaults: add mongo, redis, pgroonga repos to repo_upstream default values
  • New Defaults: Remove hydra from default olap list, replace ferretdb with ferretdb2

Bug Fix

  • Fix: Resolved default EPEL repo address issue in Bootstrap script.
  • Fix: Fix Debian Security China Mirror URL

Postgres Update

  • PostgreSQL 17.4, 16.8, 15.12, 14.17, 13.20
  • Pgbouncer 1.24
  • Patroni 4.0.5
  • pgBackRest 2.54.2
  • pg_exporter 0.8.0
  • pig 0.3.0

Extension Update

  • citus 13.0.0 -> 13.0.1
  • pg_duckdb 0.2.0 -> 0.3.1
  • pg_mooncake 0.1.0 -> 0.1.2
  • timescaledb 2.17.2 -> 2.18.2
  • supautils 2.5.0 -> 2.6.0
  • supabase_vault 0.3.1 (become C)
  • VectorChord 0.1.0 -> 0.2.1
  • pg_bulkload 3.1.22 (+pg17)
  • pg_store_plan 1.8 (+pg17)
  • pg_search 0.14 -> 0.15.2
  • pg_analytics 0.3.0 -> 0.3.4
  • pgroonga 3.2.5 -> 4.0.0
  • zhparser 2.2 -> 2.3
  • pg_vectorize 0.20.0 -> 0.21.1
  • pg_net 0.14.0
  • pg_curl 2.4.2
  • table_version 1.10.3 -> 1.11.0
  • pg_duration 1.0.2
  • pg_graphql 1.5.9 -> 1.5.11
  • vchord 0.1.1 -> 0.2.1 ((+13))
  • vchord_bm25 0.1.0 -> 0.1.1
  • pg_mooncake 0.1.1 -> 0.1.2
  • pgddl 0.29
  • pgsql_tweaks 0.11.0

Infra Updates

  • pig 0.1.3 -> 0.3.0
  • pushgateway 1.10.0 -> 1.11.0
  • alertmanager 0.27.0 -> 0.28.0
  • nginx_exporter 1.4.0 -> 1.4.1
  • pgbackrest_exporter 0.18.0 -> 0.19.0
  • redis_exporter 1.66.0 -> 1.67.0
  • mongodb_exporter 0.43.0 -> 0.43.1
  • VictoriaMetrics 1.107.0 -> 1.111.0
  • VictoriaLogs v1.3.2 -> 1.9.1
  • DuckDB 1.1.3 -> 1.2.0
  • Etcd 3.5.17 -> 3.5.18
  • pg_timetable 5.10.0 -> 5.11.0
  • FerretDB 1.24.0 -> 2.0.0-rc
  • tigerbeetle 0.16.13 -> 0.16.27
  • grafana 11.4.0 -> 11.5.2
  • vector 0.43.1 -> 0.44.0
  • minio 20241218131544 -> 20250218162555
  • mcli 20241121172154 -> 20250215103616
  • rclone 1.68.2 -> 1.69.0
  • vray 5.23 -> 5.28

Checksums

5b5dbb91c42068e25efc5b420f540909  pigsty-v3.3.0.tgz
048c7d5b3fbf1727e156e7d4885ac2eb  pigsty-pkg-v3.3.0.d12.aarch64.tgz
bbb5c225e2a429f49a653695678676b6  pigsty-pkg-v3.3.0.d12.x86_64.tgz
756ec04632fb42c6f75a7b4dd29ffb09  pigsty-pkg-v3.3.0.el9.aarch64.tgz
602e1596219c1e33fdf1cd49e5b17c21  pigsty-pkg-v3.3.0.el9.x86_64.tgz
ca0fd50a99cfc5b342717594e0ca1353  pigsty-pkg-v3.3.0.u22.aarch64.tgz
4c694893c6c42e401516f0070d373b69  pigsty-pkg-v3.3.0.u22.x86_64.tgz
Last modified 2025-02-27: update v3.3.0 release note (fa75a7d)