v2.7: Extension Overwhelming

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), [email protected] , The Pigsty Community

Battery-Included PostgreSQL Distro as a Free RDS Alternative, with:

• Extensible Postgres with 255 extensions available: PostGIS, Timescale, Citus, PGVector, AGE, PGML, ParadeDB, Hydra, DuckFDW, GraphQL, ……
• Reliable Infras: Create self-healing HA PostgreSQL clusters with pre-configured PITR, built-in ACL, & SSL, and secure your infra with local CA & best practices.
• Observable Graphics: Unparalleled monitoring best practices build upon the modern Prometheus & Grafana stack. Reuse them to monitor existing DBs & cloud RDS. Check our Gallery & Demo
• High-Available Service: Deliver auto-routed, high-performance, pooled, reliable, flexible database Services Access via Pgbouncer DNSMasq, Keepalived, vip-manager, and HAProxy.
• Maintainable Toolbox: Infra as Code, Declarative API & Idempotent Playbooks, Vagrant sandbox & Terraform IaaS provisioning specs. Local repo, offline package, delivered without Internet access.
• Composable Modules: Modular design, flexible arch with many bonus features. Redis, MinIO, ETCD, FerretDB, DuckDB, Supabase, and Docker compose templates for software that uses Postgres.
• Painless Experience: Easy to use: Download, Install, and Configure in one command. Built-in configuration templates for different scenarios, auto-tuned params, admin SOP, and zero-downtime blue-green migration plans.
• Compatible Distros: Run on base OS without containerization support: EL 7, 8, 9 and Rocky, Alma, CentOS, OracleLinux,… and Ubuntu 20.04 / 22.04 and Debian 11 / 12 Support.
• Open-Source RDS: Free software open-sourced under the AGPLv3 license, a free RDS for PostgreSQL alternative.

v2.7.0

Highlight

Extension Overwhelming, 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 postgres
• 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 PostgresSQL
• 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

• running on certain docker containers.
• prepare arm64 packages for infra & pgsql packages for el & deb distros.
• new installation script to download from cloudflare, and more hint.
• new monitoring dashboard for PGSQL PITR to assist the PITR procedure.
• make preparation for running pigsty inside docker VM containers
• add a fool-proof design for running pgsql.yml on node that is not managed by pigsty
• add config template for each major version: el7, el8, el9, debian11, debian12, ubuntu20, ubuntu22

Software Upgrade

• PostgreSQL 16.3
• 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)

Docker Application

• Odoo: launch open source ERP and plugins
• Jupyter: run jupyter notebook container
• PolarDB: run the demo PG RAC playground.
• supabase: bump to the latest GA version.
• bytebase: use the latest tag instead of ad hoc version.
• pg_exporter: update docker image example

Bug Fix

• Fix role pg_exporters white space in variable templates
• Fix minio_cluster not commented in global variables
• Fix the non-exist postgis34 in 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 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 much 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 pgsql roles.

New Contributors

• @NeroSong made their first contribution in https://github.com/Vonng/pigsty/pull/373
• @waitingsong made their first contribution in https://github.com/Vonng/pigsty/pull/405

Package Checksums

ec271a1d34b2b1360f78bfa635986c3a  pigsty-pkg-v2.7.0.el8.x86_64.tgz
f3304bfd896b7e3234d81d8ff4b83577  pigsty-pkg-v2.7.0.debian12.x86_64.tgz
5b071c2a651e8d1e68fc02e7e922f2b3  pigsty-pkg-v2.7.0.ubuntu22.x86_64.tgz

• ←Previous
• Next→