v3.0 Beta1: Extension Exploding & Plugable Kernels
Get started with:
curl -fsSL https://repo.pigsty.io/get | bash -s v3.0.0-b1
cd ~/pigsty; ./bootstrap; ./configure; ./install.yml
Highlight Features
Extension Exploding:
Pigsty now has an unprecedented 333 available extensions for PostgreSQL. This includes 121 extension RPM packages and 133 DEB packages, surpassing the total number of extensions provided by the PGDG official repository (135 RPM/109 DEB). Pigsty has ported unique PG extensions from the EL/DEB system to each other, achieving a great alignment of extension ecosystems between the two major distributions.
A crude list of the extension ecosystem is as follows:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background pg_timetable
- postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip #pg_geohash #mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity pg_tiktoken pgml #smlar
- pg_search pg_bigm zhparser hunspell
- hydra pg_lakehouse pg_duckdb duckdb_fdw pg_fkpart pg_partman plproxy #pg_strom citus
- pg_hint_plan age hll rum pg_graphql pg_jsonschema jsquery index_advisor hypopg imgsmlr pg_ivm pgmq pgq #rdkit
- pg_tle plv8 pllua plprql pldebugger plpgsql_check plprofiler plsh #pljava plr pgtap faker dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint ip4r timestamp9 chkpass #pg_uri #pgemailaddr #acl #debversion #pg_rrule
- topn pg_gzip pg_http pg_net pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_arrays pg_arraymath pg_idkit pg_uuidv7 permuteseq pg_hashids
- sequential_uuids pg_math pg_random pg_base36 pg_base62 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext envvar url_encode #pg_zstd #aggs_for_vecs #quantile #lower_quantile #pgqr #pg_protobuf
- pg_repack pg_squeeze pg_dirtyread pgfincore pgdd ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgpool pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_store_plans pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pagevis powa pg_top #pg_statviz #pgexporter_ext #pg_mon
- passwordcheck supautils pgsodium pg_vault anonymizer pg_tde pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor noset #sslutils
- wrappers multicorn mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw mongo_fdw redis_fdw pg_redis_pubsub kafka_fdw hdfs_fdw firebird_fdw aws_s3 log_fdw #oracle_fdw #db2_fdw
- orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
- pglogical pgl_ddl_deploy pg_failover_slots wal2json wal2mongo decoderbufs decoder_raw mimeo pgcopydb pgloader pg_fact_loader pg_bulkload pg_comparator pgimportdoc pgexportdoc #repmgr #slony
- gis-stack rag-stack fdw-stack fts-stack etl-stack feat-stack olap-stack supa-stack stat-stack json-stack
Plugable Kernels:
Pigsty v3 allows you to replace the PostgreSQL kernel, currently supporting Babelfish (SQL Server compatible, with wire protocol emulation), IvorySQL (Oracle compatible), and RAC PolarDB for PostgreSQL. Additionally, self-hosted Supabase is now available on Debian systems. You can emulate MSSQL (via WiltonDB), Oracle (via IvorySQL), Oracle RAC (via PolarDB), MongoDB (via FerretDB), and Firebase (via Supabase) in Pigsty with production-grade PostgreSQL clusters featuring HA, IaC, PITR, and monitoring.
Pro Edition:
We now offer PGSTY Pro, a professional edition that provides value-added services on top of the open-source features. The professional edition includes additional modules: MSSQL, Oracle, Mongo, K8S, Victoria, Kafka, etc., and offers broader support for PG major versions, operating systems, and chip architectures. It provides offline installation packages customized for precise minor versions of all operating systems, and support for legacy systems like EL7, Debian 11, Ubuntu 20.04.
Major Changes
This Pigsty release updates the major version number from 2.x to 3.0, with several significant changes:
- Primary supported operating systems updated to: EL 8 / EL 9 / Debian 12 / Ubuntu 22.04
- EL7 / Debian 11 / Ubuntu 20.04 systems are now deprecated and no longer supported.
- Users needing to run on these systems should consider our subscription service.
- Default to online installation, offline packages are no longer provided to resolve minor OS version compatibility issues.
- The
bootstrap
process will no longer prompt for downloading offline packages, but if/tmp/pkg.tgz
exists, it will still use the offline package automatically. - For offline installation needs, please create offline packages yourself or consider our subscription service.
- The
- Unified adjustment of upstream software repositories used by Pigsty, address changes, and GPG signing and verification for all packages.
- Standard repository:
https://repo.pigsty.io/{apt/yum}
- Domestic mirror:
https://repo.pigsty.cc/{apt/yum}
- Standard repository:
- API parameter changes and configuration template changes
- Configuration templates for EL and Debian systems are now consolidated, with differing parameters managed in the
roles/node_id/vars/
directory. - Configuration directory changes, all configuration file templates are now placed in the
conf
directory and categorized intodefault
,dbms
,demo
,build
.
- Configuration templates for EL and Debian systems are now consolidated, with differing parameters managed in the
Docker
is now completely treated as a separate module, and will not be downloaded by default- New beta module:
KAFKA
- New beta module:
KUBE
Other New Features
- Epic enhancement of PG OLAP analysis capabilities: DuckDB 1.0.0, DuckDB FDW, and PG Lakehouse, Hydra have been ported to the Debian system.
- Strengthened PG vector search and full-text search capabilities: Vectorscale provides DiskANN vector indexing, Hunspell dictionary support, pg_search 0.8.6.
- Resolved package build issues for ParadeDB, now available on Debian/Ubuntu.
- All required extensions for Supabase are now available on Debian/Ubuntu, making Supabase self-hostable across all OSes.
- Provided capability for scenario-based pre-configured extension stacks. If you’re unsure which extensions to install, we offer extension recommendation packages (Stacks) tailored for specific application scenarios.
- Created metadata tables, documentation, indexes, and name mappings for all PostgreSQL ecosystem extensions, ensuring alignment and usability for both EL and Debian systems.
- Enhanced
proxy_env
parameter functionality to mitigate DockerHub ban issues, simplifying configuration. - Established a new dedicated software repository offering all extension plugins for versions 12-17, with the PG16 extension repository implemented by default in Pigsty.
- Upgraded existing software repositories, employing standard signing and verification mechanisms to ensure package integrity and security. The APT repository adopts a new standard layout built through
reprepro
. - Provided sandbox environments for 1, 2, 3, 4, 43 nodes:
meta
,dual
,trio
,full
,prod
, and quick configuration templates for 7 major OS Distros. - Add PostgreSQL 17 and pgBouncer 1.23 metrics support in pg_exporter config, adding related dashboard panels.
- Add logs panel for PGSQL Pgbouncer / PGSQL Patroni Dashboard
API Changes
-
New parameter option:
pg_mode
now have several new options:pgsql
: Standard PostgreSQL high availability cluster.citus
: Citus horizontally distributed PostgreSQL native high availability cluster.gpsql
: Monitoring for Greenplum and GP compatible databases (Pro edition).mssql
: Install WiltonDB / Babelfish to provide Microsoft SQL Server compatibility mode for standard PostgreSQL high availability clusters, with wire protocol level support, extensions unavailable.ivory
: Install IvorySQL to provide Oracle compatibility for PostgreSQL high availability clusters, supporting Oracle syntax/data types/functions/stored procedures, extensions unavailable (Pro edition).polar
: Install PolarDB for PostgreSQL (PG RAC) open-source version to support localization database capabilities, extensions unavailable (Pro edition).
-
New parameter:
pg_parameters
, used to specify parameters inpostgresql.auto.conf
at the instance level, overriding cluster configurations for personalized settings on different instance members. -
New parameter:
repo_extra_packages
, used to specify additional packages to download, to be used in conjunction withrepo_packages
, facilitating the specification of extension lists unique to OS versions. -
Parameter renaming:
patroni_citus_db
renamed topg_primary_db
, used to specify the primary database in the cluster (used in Citus mode). -
Parameter enhancement: Proxy server configurations in
proxy_env
will be written to the Docker Daemon to address internet access issues, and theconfigure -x
option will automatically write the proxy server configuration of the current environment. -
Parameter enhancement: The
repo_url_packages
inrepo.pigsty.io
will automatically switch torepo.pigsty.cc
when the region is China, addressing internet access issues. Additionally, the downloaded file name can now be specified. -
Parameter enhancement: The
extension
field inpg_databases.extensions
now supports both dictionary and extension name string modes. The dictionary mode offersversion
support, allowing the installation of specific extension versions. -
Parameter enhancement: If the
repo_upstream
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. -
Parameter enhancement: If the
repo_packages
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. -
Parameter enhancement: If the
infra_packages
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. -
Parameter enhancement: If the
node_default_packages
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. -
Parameter enhancement: The extensions specified in
pg_packages
andpg_extensions
will now perform a lookup and translation from thepg_package_map
defined inrpm.yml
ordeb.yml
. -
Parameter enhancement: Packages specified in
node_packages
andpg_extensions
will be upgraded to the latest version upon installation. The default value innode_packages
is now[openssh-server]
, helping to fix the OpenSSH CVE. -
Parameter enhancement:
pg_dbsu_uid
will automatically adjust to26
(EL) or543
(Debian) based on the operating system type, avoiding manual adjustments. -
pgBouncer Parameter update,
max_prepared_statements = 128
enabled prepared statement support in transaction pooling mode, and setserver_lifetime
to 600. -
Patroni template parameter update, uniformly increase
max_worker_processes
+8 available backend processes, increasemax_wal_senders
andmax_replication_slots
to 50, and increase the OLAP template temporary file size limit to 1/5 of the main disk.
Software Upgrade
The main components of Pigsty are upgraded to the following versions (as of the release time):
- PostgreSQL 16.3, 15.7, 14.12, 13.15, and 12.19
- pg_exporter: 0.7.0
- Patroni: 3.3.1
- pgBouncer: 1.23
- pgBackRest: 2.53
- vip-manager: 2.5.0
- Haproxy: 3.0.2
- FerretDB: 1.23
- sealos: 5.0.0
- duckdb: v1.0.0
- etcd: 3.5.15
- Grafana: 10.4.5
- Loki: 3.1.0
- Prometheus: 2.53.1
- pushgateway: 1.9.0
- node_exporter: 1.8.2
- nginx: exporter 1.2.0
- victoriametrics: 1.102.0
- victorialogs: 0.28.0
- redis_exporter: 1.62.0
- vector: 0.40.0
- MinIO 20240803043323 / MCLI 20240731155833
The complete list of PostgreSQL extensions can be found here.
Docker Application
Pigsty now offers out-of-the-box Dify and Odoo Docker Compose templates:
There two new beta modules available in Pigsty Pro version:
KAFKA
: Deploy a high-availability Kafka cluster supported by the Kraft protocol.KUBE
: Deploy a Kubernetes cluster managed by Pigsty using cri-dockerd or containerd.
Bug Fix
- Fixed CVE-2024-6387 by automatically repairing during the Pigsty installation process using the default value
[openssh-server]
innode_packages
. - Fixed memory consumption issues caused by Loki parsing Nginx log tag cardinality being too large.
- Fixed bootstrap failure caused by upstream Ansible dependency changes in EL8 systems (python3.11-jmespath upgraded to python3.12-jmespath).