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

The [Pigsty](https://pigsty.io/) community is thrilled to announce Pigsty [v2.7.0](https://github.com/Vonng/pigsty/releases/tag/v2.7.0), which has [255](https://pigsty.io/docs/reference/extension/) unique extensions available, to the free PostgreSQL distribution and RDS alternative. We also have introduced some new docker-compose templates for [Odoo](https://github.com/Vonng/pigsty/tree/master/app/odoo), [Jupyter](https://github.com/Vonng/pigsty/tree/master/app/jupyter), [PolarDB](https://github.com/Vonng/pigsty/tree/master/app/polardb), and GA [Supabase](https://github.com/Vonng/pigsty/tree/master/app/supabase).

## About Pigsty

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

**Links**: [Website](https://pigsty.io/) | [GitHub](https://github.com/Vonng/pigsty) | [Demo](https://demo.pigsty.cc/) | [Blog](https://pigsty.io/blog) | [Install](https://pigsty.io/docs/setup/install/) | [Feature](https://pigsty.io/docs/about/feature/)

**Images**: [Introduction](https://pigsty.io/img/pigsty/banner.en.jpg) | [Extensions](https://pigsty.io/img/pigsty/extension.png) | [Architecture](https://pigsty.io/img/pigsty/ha.png) | [Dashboards](https://pigsty.io/img/pigsty/dashboard.jpg)

[Getting Started](https://pigsty.io/docs/setup/install/) with the latest [v2.7.0](https://github.com/Vonng/pigsty/releases/tag/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*](https://pigsty.io/blog/pg/pg-eat-db-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](https://pigsty.io/docs/reference/extension/#rpm-extension) and **116** for [Debian/Ubuntu Distros](https://pigsty.io/docs/reference/extension/#deb-extension). Combined with **73** built-in [Contrib extensions](https://pigsty.io/docs/reference/extension/#contrib-extension), 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/): https://pigsty.io/docs/reference/extension/

## v2.7.0 Release Note

### **Highlight**

Adding numerous new extensions written in `rust` & `pgrx`:

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

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

- [parquet_s3_fdw](https://github.com/pgspider/parquet_s3_fdw) 1.1.0 : ParquetS3 Foreign Data Wrapper for PostgreSQL
- [plv8](https://github.com/plv8/plv8) 3.2.2 : V8 Engine Javascript Procedural Language add-on for PostgreSQL
- [md5hash](https://github.com/tvondra/md5hash) 1.0.1 : Custom data type for storing MD5 hashes rather than text
- [pg_tde](https://github.com/Percona-Lab/pg_tde) 1.0 alpha: Experimental encrypted access method for PostgreSQL
- [pg_dirtyread](https://github.com/df7cb/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](https://github.com/Vonng/pigsty/issues/402) 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](https://github.com/Vonng/pigsty/tree/master/app/odoo): launch open-source ERP over PostgreSQL.
- [Jupyter](https://github.com/Vonng/pigsty/tree/master/app/jupyter): Run Jupyter notebook containers and expose the HTTP service.
- [PolarDB](https://github.com/Vonng/pigsty/tree/master/app/polardb): run the demo playground for the shared-storage version of OSS PG.
- [supabase](https://github.com/Vonng/pigsty/tree/master/app/supabase): bump to the latest GA version.
- [bytebase](https://github.com/Vonng/pigsty/tree/master/app/bytebase): use the `latest` tag instead of the ad hoc version.
- [pg_exporter](https://github.com/Vonng/pigsty/tree/master/app/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](https://github.com/Vonng/pigsty/). 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(at)vonng(dot)com , The Pigsty Community