Pigsty v2.7 Released, free RDS PG with 255 extensions available

The Pigsty community is thrilled to announce Pigsty v2.7.0, which has 255 unique extensions available, to the free PostgreSQL distribution and RDS alternative. We also have introduced some new docker-compose templates for Odoo, Jupyter, PolarDB, and GA Supabase.

About Pigsty

Pigsty is a Battery-included, local-first PostgreSQL Distribution as a Free RDS alternative.

Links: Website | GitHub | Demo | Blog | Install | Feature

Images: Introduction | Extensions | Architecture | Dashboards

Getting Started with the latest v2.7.0 release with: curl -L https://get.pigsty.cc/install | bash

Pigsty v2.7: Extension Overwhelming

I wrote a popular article last month - Postgres is eating the database world, explaining why extensions matter to the PostgreSQL ecosystem.

Based on this idea, we've packaged 20 brand-new extensions in v2.7. With these extensions added, Pigsty offers 157 non-contrib extensions for EL Distros and 116 for Debian/Ubuntu Distros. Combined with 73 built-in Contrib extensions, Pigsty now has a total of 255 unique extensions available, which takes PostgreSQL's versatility to a whole new level!

Complete list of available extensions: https://pigsty.io/docs/reference/extension/

v2.7.0 Release Note

Highlight

Adding numerous new extensions written in rust & pgrx:

• pg_search v0.7.0 : Full text search over SQL tables using the BM25 algorithm
• pg_lakehouse v0.7.0 : Query engine over object stores like S3 and table formats like Delta Lake
• pg_analytics v0.6.1 : Accelerates analytical query processing inside Postgres
• pg_graphql v1.5.4 : GraphQL support to your PostgreSQL database.
• pg_jsonschema v0.3.1 : PostgreSQL extension providing JSON Schema validation
• wrappers v0.3.1 : Postgres Foreign Data Wrappers Collections by Supabase
• pgmq v1.5.2 : A lightweight message queue. Like AWS SQS and RSMQ but on Postgres.
• pg_tier v0.0.3 : Postgres Extension written in Rust, to enable data tiering to AWS S3
• pg_vectorize v0.15.0 : The simplest way to orchestrate vector search on Postgres
• pg_later v0.1.0 : Execute SQL now and get the results later.
• pg_idkit v0.2.3 : Generating many popular types of identifiers
• plprql v0.1.0 : Use PRQL in PostgreSQL
• pgsmcrypto v0.1.0 : PostgreSQL SM Algorithm Extension
• pg_tiktoken v0.0.1 : OpenAI tiktoken tokenizer for PostgreSQL
• pgdd v0.5.2 : Access Data Dictionary metadata with pure SQL

And some new extensions in plain C & C++:

• parquet_s3_fdw 1.1.0 : ParquetS3 Foreign Data Wrapper for PostgreSQL
• plv8 3.2.2 : V8 Engine Javascript Procedural Language add-on for PostgreSQL
• md5hash 1.0.1 : Custom data type for storing MD5 hashes rather than text
• pg_tde 1.0 alpha: Experimental encrypted access method for PostgreSQL
• pg_dirtyread 2.6 : Read dead but unvacuumed tuples from a PostgreSQL relation
• New deb PGDG extensions: pg_roaringbitmap, pgfaceting, mobilitydb, pgsql-http, pg_hint_plan, pg_statviz, pg_rrule
• New rpm PGDG extensions: pg_profile, pg_show_plans, use PGDG's pgsql_http, pgsql_gzip, pg_net, pg_bigm instead of Pigsty RPM.

New Features

• Prepare arm64 packages for infra & pgsql packages for el & deb distros.
• New installation script to download from Cloudflare, and more hints.
• New monitoring dashboard PGSQL PITR to assist the PITR procedure.
• Make preparations for running pigsty inside docker VM containers
• Add a fool-proof design for running pgsql.yml on a node that is not managed by Pigsty
• Add separated template for each OS distro: el7, el8, el9, debian11, debian12, ubuntu20, ubuntu22

New Docker Application

• Odoo: launch open-source ERP over PostgreSQL.
• Jupyter: Run Jupyter notebook containers and expose the HTTP service.
• PolarDB: run the demo playground for the shared-storage version of OSS PG.
• supabase: bump to the latest GA version.
• bytebase: use the latest tag instead of the ad hoc version.
• pg_exporter: update docker image example

Software Upgrade

• PostgreSQL 16.3, 15.7, 14.12, 13.15, 12.19
• Patroni 3.3.0
• pgBackRest 2.51
• vip-manager v2.5.0
• Haproxy 2.9.7
• Grafana 10.4.2
• Prometheus 2.51
• Loki & Promtail: 3.0.0 (breaking changes!)
• Alertmanager 0.27.0
• BlackBox Exporter 0.25.0
• Node Exporter 1.8.0
• pgBackrest Exporter 0.17.0
• duckdb 0.10.2
• etcd 3.5.13
• minio-20240510014138 / mcli-20240509170424
• pev2 v1.8.0 → v1.11.0
• pgvector 0.6.1 → 0.7.0
• pg_tle: v1.3.4 → v1.4.0
• hydra: v1.1.1 → v1.1.2
• duckdb_fdw: v1.1.0 recompile with libduckdb 0.10.2
• pg_bm25 0.5.6 → pg_search 0.7.0
• pg_analytics: 0.5.6 → 0.6.1
• pg_graphql: 1.5.0 → 1.5.4
• pg_net 0.8.0 → 0.9.1
• pg_sparse (deprecated due to pgvector 0.7)

Fixed Issues

• Fix role pg_exporters white space in variable templates
• Fix minio_cluster not commented in global variables
• Fix the non-existent postgis34 package name in the el7 config template
• Fix EL8 python3.11-cryptography deps to python3-cryptography according to upstream
• Fix /pg/bin/pg-role can not get OS user name from environ in non-interact mode
• Fix /pg/bin/pg-pitr can not hint -X -P flag properly

API Change

• New parameter node_write_etc_hosts to control whether to write /etc/hosts file on target nodes.
• Relocatable prometheus target directory with new parameter prometheus_sd_dir.
• Add -x|--proxy flag to enable and use the value of global proxy env by @waitingsong in https://github.com/Vonng/pigsty/pull/405
• No longer parse infra nginx log details since it brings too many labels to the log.
• Use alertmanager API Version v2 instead of v1 in prometheus config.
• Use /pg/cert/ca.crt instead of /etc/pki/ca.crt in role pgsql.

Acknowledgment

A huge thank you to all our users who contributed patches reported bugs and proposed new features.

Pigsty thrives on community contributions. We warmly welcome your ideas, feature requests, or patches. Please share your contributions on our GitHub page. We look forward to your feedback on Pigsty 2.7 and your continued support in making Pigsty even better.

Best regards,

Ruohang Feng (@vonng), rh@vonng.com , The Pigsty Community