This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Releases

Pigsty v3.0: Extension Exploding & Plugable Kernels

The 336 extension provided by Pigsty v3 & Plugable Kernels such as Babelfish, IvorySQL, and PolarDB
By Ruohang Feng（@Vonng） |

Get started with:

curl -fsSL https://repo.pigsty.io/get | bash
cd ~/pigsty; ./bootstrap; ./configure; ./install.yml

Highlight Features

Extension Exploding:

Pigsty now has an unprecedented 336 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 pro version.
  • 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}
  • 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 into default, dbms, demo, build.
  • 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
  • Add new playbook cache.yml to make offline packages, instead of bash bin/cache and bin/release-pkg

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 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 in postgresql.auto.conf at the instance level, overriding cluster configurations for personalized settings on different instance members.

  • New parameter: pg_files, used to specify additional files to be written to the PostgreSQL data directory, to support license feature required by some kernel forks.

  • New parameter: repo_extra_packages, used to specify additional packages to download, to be used in conjunction with repo_packages, facilitating the specification of extension lists unique to OS versions.

  • Parameter renaming: patroni_citus_db renamed to pg_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 the configure -x option will automatically write the proxy server configuration of the current environment.

  • Parameter enhancement: Allow using path item in infra_portal entries, to expose local dir as web service rather than proxy to another upstream.

  • Parameter enhancement: The repo_url_packages in repo.pigsty.io will automatically switch to repo.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 in pg_databases.extensions now supports both dictionary and extension name string modes. The dictionary mode offers version 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 from rpm.yml or deb.yml.

  • Parameter enhancement: If the repo_packages parameter is not explicitly overridden, it will extract the default value for the corresponding system from rpm.yml or deb.yml.

  • Parameter enhancement: If the infra_packages parameter is not explicitly overridden, it will extract the default value for the corresponding system from rpm.yml or deb.yml.

  • Parameter enhancement: If the node_default_packages parameter is not explicitly overridden, it will extract the default value for the corresponding system from rpm.yml or deb.yml.

  • Parameter enhancement: The extensions specified in pg_packages and pg_extensions will now perform a lookup and translation from the pg_package_map defined in rpm.yml or deb.yml.

  • Parameter enhancement: Packages specified in node_packages and pg_extensions will be upgraded to the latest version upon installation. The default value in node_packages is now [openssh-server], helping to fix the OpenSSH CVE.

  • Parameter enhancement: pg_dbsu_uid will automatically adjust to 26 (EL) or 543 (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 set server_lifetime to 600.

  • Patroni template parameter update, uniformly increase max_worker_processes +8 available backend processes, increase max_wal_senders and max_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):

The complete list of PostgreSQL extensions can be found here.

Extension (URL) Alias Repo Version Category License LOAD DDL TRUST RELOC Description
timescaledb timescaledb PGDG 2.15.3 TIME Timescale Enables scalable inserts and complex queries for time-series data (Apache 2 Edition)
periods periods PGDG 1.2 TIME PostgreSQL Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
temporal_tables temporal_tables PGDG 1.2.2 TIME BSD 2 temporal tables
emaj emaj PGDG 4.4.0 TIME GPLv3 E-Maj extension enables fine-grained write logging and time travel on subsets of the database.
table_version table_version PGDG 1.10.3 TIME BSD 3 PostgreSQL table versioning extension
pg_cron pg_cron PGDG 1.6 TIME PostgreSQL Job scheduler for PostgreSQL
pg_later pg_later PIGSTY 0.1.1 TIME PostgreSQL pg_later: Run queries now and get results later
pg_background pg_background PGDG 1.0 TIME GPLv3 Run SQL queries in the background
pg_timetable pg_timetable PGDG 5.9.0 TIME PostgreSQL Advanced scheduling for PostgreSQL
postgis postgis PGDG 3.4.2 GIS GPLv2 PostGIS geometry and geography spatial types and functions
postgis_topology postgis PGDG 3.4.2 GIS GPLv2 PostGIS topology spatial types and functions
postgis_raster postgis PGDG 3.4.2 GIS GPLv2 PostGIS raster types and functions
postgis_sfcgal postgis PGDG 3.4.2 GIS GPLv2 PostGIS SFCGAL functions
postgis_tiger_geocoder postgis PGDG 3.4.2 GIS GPLv2 PostGIS tiger geocoder and reverse geocoder
address_standardizer postgis PGDG 3.4.2 GIS GPLv2 Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
address_standardizer_data_us postgis PGDG 3.4.2 GIS GPLv2 Address Standardizer US dataset example
pgrouting pgrouting PGDG 3.6.0 GIS GPLv2 pgRouting Extension
pointcloud pointcloud PIGSTY 1.2.5 GIS BSD 3 data type for lidar point clouds
pointcloud_postgis pointcloud PGDG 1.2.5 GIS BSD 3 integration for pointcloud LIDAR data and PostGIS geometry data
h3 pg_h3 PGDG 4.1.3 GIS Apache-2.0 H3 bindings for PostgreSQL
h3_postgis pg_h3 PGDG 4.1.3 GIS Apache-2.0 H3 PostGIS integration
q3c q3c PIGSTY 2.0.1 GIS GPLv2 q3c sky indexing plugin
ogr_fdw ogr_fdw PGDG 1.1 GIS MIT foreign-data wrapper for GIS data access
geoip geoip PGDG 0.3.0 GIS BSD 2 IP-based geolocation query
pg_geohash pg_geohash PIGSTY 1.0 GIS MIT Handle geohash based functionality for spatial coordinates
mobilitydb mobilitydb PGDG 1.1.1 GIS GPLv3 MobilityDB geospatial trajectory data management & analysis platform
earthdistance earthdistance CONTRIB 1.1 GIS PostgreSQL calculate great-circle distances on the surface of the Earth
vector pgvector PGDG 0.7.3 RAG PostgreSQL vector data type and ivfflat and hnsw access methods
vectorscale pgvectorscale PIGSTY 0.2.0 RAG PostgreSQL pgvectorscale: Advanced indexing for vector data
vectorize pg_vectorize PIGSTY 0.17.0 RAG PostgreSQL The simplest way to do vector search on Postgres
pg_similarity pg_similarity PIGSTY 1.0 RAG BSD 3 support similarity queries
smlar smlar PIGSTY 1.0 RAG PostgreSQL Effective similarity search
pg_tiktoken pg_tiktoken PIGSTY 0.0.1 RAG Apache-2.0 pg_tictoken: tiktoken tokenizer for use with OpenAI models in postgres
pgml pgml PIGSTY 2.9.3 RAG MIT PostgresML: Run AL/ML workloads with SQL interface
pg_search pg_search PIGSTY 0.9.1 FTS AGPLv3 pg_search: Full text search for PostgreSQL using BM25
pg_bigm pg_bigm PGDG 1.2 FTS PostgreSQL create 2-gram (bigram) index for faster full text search.
zhparser zhparser PIGSTY 2.2 FTS PostgreSQL a parser for full-text search of Chinese
hunspell_cs_cz hunspell_cs_cz PIGSTY 1.0 FTS PostgreSQL Czech Hunspell Dictionary
hunspell_de_de hunspell_de_de PIGSTY 1.0 FTS PostgreSQL German Hunspell Dictionary
hunspell_en_us hunspell_en_us PIGSTY 1.0 FTS PostgreSQL en_US Hunspell Dictionary
hunspell_fr hunspell_fr PIGSTY 1.0 FTS PostgreSQL French Hunspell Dictionary
hunspell_ne_np hunspell_ne_np PIGSTY 1.0 FTS PostgreSQL Nepali Hunspell Dictionary
hunspell_nl_nl hunspell_nl_nl PIGSTY 1.0 FTS PostgreSQL Dutch Hunspell Dictionary
hunspell_nn_no hunspell_nn_no PIGSTY 1.0 FTS PostgreSQL Norwegian (norsk) Hunspell Dictionary
hunspell_pt_pt hunspell_pt_pt PIGSTY 1.0 FTS PostgreSQL Portuguese Hunspell Dictionary
hunspell_ru_ru hunspell_ru_ru PIGSTY 1.0 FTS PostgreSQL Russian Hunspell Dictionary
hunspell_ru_ru_aot hunspell_ru_ru_aot PIGSTY 1.0 FTS PostgreSQL Russian Hunspell Dictionary (from AOT.ru group)
fuzzystrmatch fuzzystrmatch CONTRIB 1.2 FTS PostgreSQL determine similarities and distance between strings
pg_trgm pg_trgm CONTRIB 1.6 FTS PostgreSQL text similarity measurement and index searching based on trigrams
citus citus PGDG 12.1-1 OLAP AGPLv3 Distributed PostgreSQL as an extension
citus_columnar citus PGDG 11.3-1 OLAP AGPLv3 Citus columnar storage engine
columnar hydra PIGSTY 11.1-11 OLAP AGPLv3 Hydra Columnar extension
pg_lakehouse pg_lakehouse PIGSTY 0.9.0 OLAP AGPLv3 pg_lakehouse: An analytical query engine for Postgres
pg_duckdb pg_duckdb PIGSTY 0.0.1 OLAP MIT DuckDB Embedded in Postgres
duckdb_fdw duckdb_fdw PIGSTY 1.0.0 OLAP MIT DuckDB Foreign Data Wrapper
parquet_s3_fdw parquet_s3_fdw PIGSTY 0.3.1 OLAP MIT foreign-data wrapper for parquet on S3
pg_fkpart pg_fkpart PGDG 1.7 OLAP GPLv2 Table partitioning by foreign key utility
pg_partman pg_partman PGDG 5.1.0 OLAP PostgreSQL Extension to manage partitioned tables by time or ID
plproxy plproxy PGDG 2.11.0 OLAP BSD 0 Database partitioning implemented as procedural language
pg_strom pg_strom PGDG 5.1 OLAP PostgreSQL PG-Strom - big-data processing acceleration using GPU and NVME
tablefunc tablefunc CONTRIB 1.0 OLAP PostgreSQL functions that manipulate whole tables, including crosstab
age age PIGSTY 1.5.0 FEAT Apache-2.0 AGE graph database extension
hll hll PGDG 2.18 FEAT Apache-2.0 type for storing hyperloglog data
rum rum PGDG 1.3 FEAT PostgreSQL RUM index access method
pg_graphql pg_graphql PIGSTY 1.5.7 FEAT Apache-2.0 pg_graphql: GraphQL support
pg_jsonschema pg_jsonschema PIGSTY 0.3.1 FEAT Apache-2.0 PostgreSQL extension providing JSON Schema validation
jsquery jsquery PGDG 1.1 FEAT PostgreSQL data type for jsonb inspection
pg_hint_plan pg_hint_plan PGDG 1.6.0 FEAT BSD 3 Give PostgreSQL ability to manually force some decisions in execution plans.
hypopg hypopg PGDG 1.4.1 FEAT PostgreSQL Hypothetical indexes for PostgreSQL
index_advisor index_advisor PIGSTY 0.2.0 FEAT PostgreSQL Query index advisor
imgsmlr imgsmlr PIGSTY 1.0 FEAT PostgreSQL Image similarity with haar
pg_ivm pg_ivm PGDG 1.8 FEAT PostgreSQL incremental view maintenance on PostgreSQL
pgmq pgmq PIGSTY 1.2.1 FEAT PostgreSQL A lightweight message queue. Like AWS SQS and RSMQ but on Postgres.
pgq pgq PGDG 3.5.1 FEAT ISC Generic queue for PostgreSQL
rdkit rdkit PGDG 4.3.0 FEAT BSD 3 Cheminformatics functionality for PostgreSQL.
bloom bloom CONTRIB 1.0 FEAT PostgreSQL bloom access method - signature file based index
pg_tle pg_tle PIGSTY 1.2.0 LANG Apache-2.0 Trusted Language Extensions for PostgreSQL
plv8 plv8 PIGSTY 3.2.2 LANG PostgreSQL PL/JavaScript (v8) trusted procedural language
plluau pllua PGDG 2.0 LANG MIT Lua as an untrusted procedural language
hstore_plluau pllua PGDG 1.0 LANG MIT Hstore transform for untrusted Lua
pllua pllua PGDG 2.0 LANG MIT Lua as a procedural language
hstore_pllua pllua PGDG 1.0 LANG MIT Hstore transform for Lua
plprql plprql PIGSTY 0.1.0 LANG Apache-2.0 Use PRQL in PostgreSQL - Pipelined Relational Query Language
pldbgapi pldebugger PGDG 1.1 LANG Artistic server-side support for debugging PL/pgSQL functions
plpgsql_check plpgsql_check PGDG 2.7 LANG MIT extended check for plpgsql functions
plprofiler plprofiler PGDG 4.2 LANG Artistic server-side support for profiling PL/pgSQL functions
plsh plsh PGDG 2 LANG MIT PL/sh procedural language
pljava pljava PGDG 1.6.6 LANG BSD 3 PL/Java procedural language (https://tada.github.io/pljava/)
plr plr PGDG 8.4.6 LANG GPLv2 load R interpreter and execute R script from within a database
pgtap pgtap PGDG 1.3.1 LANG PostgreSQL Unit testing for PostgreSQL
faker faker PGDG 0.5.3 LANG PostgreSQL Wrapper for the Faker Python library
dbt2 dbt2 PGDG 0.45.0 LANG Artistic OSDL-DBT-2 test kit
pltcl pltcl CONTRIB 1.0 LANG PostgreSQL PL/Tcl procedural language
pltclu pltcl CONTRIB 1.0 LANG PostgreSQL PL/TclU untrusted procedural language
plperl plperl CONTRIB 1.0 LANG PostgreSQL PL/Perl procedural language
bool_plperl plperl CONTRIB 1.0 LANG PostgreSQL transform between bool and plperl
hstore_plperl plperl CONTRIB 1.0 LANG PostgreSQL transform between hstore and plperl
jsonb_plperl plperl CONTRIB 1.0 LANG PostgreSQL transform between jsonb and plperl
plperlu plperlu CONTRIB 1.0 LANG PostgreSQL PL/PerlU untrusted procedural language
bool_plperlu plperlu CONTRIB 1.0 LANG PostgreSQL transform between bool and plperlu
jsonb_plperlu plperlu CONTRIB 1.0 LANG PostgreSQL transform between jsonb and plperlu
hstore_plperlu plperlu CONTRIB 1.0 LANG PostgreSQL transform between hstore and plperlu
plpgsql plpgsql CONTRIB 1.0 LANG PostgreSQL PL/pgSQL procedural language
plpython3u plpython3u CONTRIB 1.0 LANG PostgreSQL PL/Python3U untrusted procedural language
jsonb_plpython3u plpython3u CONTRIB 1.0 LANG PostgreSQL transform between jsonb and plpython3u
ltree_plpython3u plpython3u CONTRIB 1.0 LANG PostgreSQL transform between ltree and plpython3u
hstore_plpython3u plpython3u CONTRIB 1.0 LANG PostgreSQL transform between hstore and plpython3u
prefix prefix PGDG 1.2.0 TYPE PostgreSQL Prefix Range module for PostgreSQL
semver semver PGDG 0.32.1 TYPE PostgreSQL Semantic version data type
unit pgunit PGDG 7 TYPE GPLv3 SI units extension
md5hash md5hash PIGSTY 1.0.1 TYPE BSD 2 type for storing 128-bit binary data inline
asn1oid asn1oid PIGSTY 1 TYPE GPLv3 asn1oid extension
roaringbitmap roaringbitmap PIGSTY 0.5 TYPE Apache-2.0 support for Roaring Bitmaps
pgfaceting pgfaceting PIGSTY 0.2.0 TYPE BSD 3 fast faceting queries using an inverted index
pg_sphere pgsphere PIGSTY 1.5.1 TYPE BSD 3 spherical objects with useful functions, operators and index support
country pg_country PIGSTY 0.0.3 TYPE PostgreSQL Country data type, ISO 3166-1
currency pg_currency PIGSTY 0.0.3 TYPE MIT Custom PostgreSQL currency type in 1Byte
pgmp pgmp PGDG 1.1 TYPE LGPLv3 Multiple Precision Arithmetic extension
numeral numeral PIGSTY 1 TYPE GPLv2 numeral datatypes extension
pg_rational pg_rational PIGSTY 0.0.2 TYPE MIT bigint fractions
uint pguint PGDG 0 TYPE PostgreSQL unsigned integer types
ip4r ip4r PGDG 2.4 TYPE PostgreSQL IPv4/v6 and IPv4/v6 range index type for PostgreSQL
uri pg_uri PIGSTY 1.20151224 TYPE PostgreSQL URI Data type for PostgreSQL
pgemailaddr pgemailaddr PIGSTY 0 TYPE PostgreSQL Email address type for PostgreSQL
acl acl PIGSTY 1.0.4 TYPE BSD-2 ACL Data type
debversion debversion PGDG 1.1 TYPE PostgreSQL Debian version number data type
pg_rrule pg_rrule PGDG 0.2.0 TYPE MIT RRULE field type for PostgreSQL
timestamp9 timestamp9 PGDG 1.4.0 TYPE MIT timestamp nanosecond resolution
chkpass chkpass PIGSTY 1.0 TYPE PostgreSQL data type for auto-encrypted passwords
isn isn CONTRIB 1.2 TYPE PostgreSQL data types for international product numbering standards
seg seg CONTRIB 1.4 TYPE PostgreSQL data type for representing line segments or floating-point intervals
cube cube CONTRIB 1.5 TYPE PostgreSQL data type for multidimensional cubes
ltree ltree CONTRIB 1.2 TYPE PostgreSQL data type for hierarchical tree-like structures
hstore hstore CONTRIB 1.8 TYPE PostgreSQL data type for storing sets of (key, value) pairs
citext citext CONTRIB 1.6 TYPE PostgreSQL data type for case-insensitive character strings
xml2 xml2 CONTRIB 1.1 TYPE PostgreSQL XPath querying and XSLT
topn topn PGDG 2.6.0 FUNC AGPLv3 type for top-n JSONB
gzip pg_gzip PGDG 1.0 FUNC MIT gzip and gunzip functions.
zstd pg_zstd PIGSTY 1.1.0 FUNC ISC Zstandard compression algorithm implementation in PostgreSQL
http pg_http PGDG 1.6 FUNC MIT HTTP client for PostgreSQL, allows web page retrieval inside the database.
pg_net pg_net PGDG 0.8.0 FUNC Apache-2.0 Async HTTP Requests
pg_html5_email_address pg_html5_email_address PIGSTY 1.2.3 FUNC PostgreSQL PostgreSQL email validation that is consistent with the HTML5 spec
pgsql_tweaks pgsql_tweaks PGDG 0.10.3 FUNC PostgreSQL Some functions and views for daily usage
pg_extra_time pg_extra_time PGDG 1.1.3 FUNC PostgreSQL Some date time functions and operators that,
timeit pg_timeit PIGSTY 1.0 FUNC PostgreSQL High-accuracy timing of SQL expressions
count_distinct count_distinct PGDG 3.0.1 FUNC BSD 2 An alternative to COUNT(DISTINCT …) aggregate, usable with HashAggregate
extra_window_functions extra_window_functions PGDG 1.0 FUNC PostgreSQL Extra Window Functions for PostgreSQL
first_last_agg first_last_agg PIGSTY 0.1.4 FUNC PostgreSQL first() and last() aggregate functions
tdigest tdigest PGDG 1.4.1 FUNC Apache-2.0 Provides tdigest aggregate function.
aggs_for_vecs aggs_for_vecs PIGSTY 1.3.0 FUNC MIT Aggregate functions for array inputs
aggs_for_arrays aggs_for_arrays PIGSTY 1.3.2 FUNC MIT Various functions for computing statistics on arrays of numbers
arraymath pg_arraymath PIGSTY 1.1 FUNC MIT Array math and operators that work element by element on the contents of arrays
quantile quantile PIGSTY 1.1.7 FUNC BSD Quantile aggregation function
lower_quantile lower_quantile PIGSTY 1.0.0 FUNC BSD-2 Lower quantile aggregate function
pg_idkit pg_idkit PIGSTY 0.2.3 FUNC Apache-2.0 multi-tool for generating new/niche universally unique identifiers (ex. UUIDv6, ULID, KSUID)
pg_uuidv7 pg_uuidv7 PGDG 1.5 FUNC MPLv2 pg_uuidv7: create UUIDv7 values in postgres
permuteseq permuteseq PIGSTY 1.2 FUNC PostgreSQL Pseudo-randomly permute sequences with a format-preserving encryption on elements
pg_hashids pg_hashids PIGSTY 1.3 FUNC MIT Short unique id generator for PostgreSQL, using hashids
sequential_uuids sequential_uuids PGDG 1.0.2 FUNC MIT generator of sequential UUIDs
pg_math pg_math PIGSTY 1.0 FUNC GPLv3 GSL statistical functions for postgresql
random pg_random PIGSTY 2.0.0-dev FUNC PostgreSQL random data generator
base36 pg_base36 PIGSTY 1.0.0 FUNC MIT Integer Base36 types
base62 pg_base62 PIGSTY 0.0.1 FUNC MIT Base62 extension for PostgreSQL
floatvec floatvec PIGSTY 1.0.1 FUNC MIT Math for vectors (arrays) of numbers
financial pg_financial PIGSTY 1.0.1 FUNC PostgreSQL Financial aggregate functions
pgjwt pgjwt PIGSTY 0.2.0 FUNC MIT JSON Web Token API for Postgresql
pg_hashlib pg_hashlib PIGSTY 1.1 FUNC PostgreSQL Stable hash functions for Postgres
shacrypt shacrypt PIGSTY 1.1 FUNC PostgreSQL Implements SHA256-CRYPT and SHA512-CRYPT password encryption schemes
cryptint cryptint PIGSTY 1.0.0 FUNC PostgreSQL Encryption functions for int and bigint values
pguecc pg_ecdsa PIGSTY 1.0 FUNC BSD-2 uECC bindings for Postgres
pgpcre pgpcre PIGSTY 1 FUNC PostgreSQL Perl Compatible Regular Expression functions
icu_ext icu_ext PIGSTY 1.8 FUNC PostgreSQL Access ICU functions
pgqr pgqr PIGSTY 1.0 FUNC BSD-3 QR Code generator from PostgreSQL
envvar envvar PIGSTY 1.0.0 FUNC PostgreSQL Fetch the value of an environment variable
pg_protobuf pg_protobuf PIGSTY 1.0 FUNC MIT Protobuf support for PostgreSQL
url_encode url_encode PIGSTY 1.2.5 FUNC PostgreSQL url_encode, url_decode functions
refint refint CONTRIB 1.0 FUNC PostgreSQL functions for implementing referential integrity (obsolete)
autoinc autoinc CONTRIB 1.0 FUNC PostgreSQL functions for autoincrementing fields
insert_username insert_username CONTRIB 1.0 FUNC PostgreSQL functions for tracking who changed a table
moddatetime moddatetime CONTRIB 1.0 FUNC PostgreSQL functions for tracking last modification time
tsm_system_time tsm_system_time CONTRIB 1.0 FUNC PostgreSQL TABLESAMPLE method which accepts time in milliseconds as a limit
dict_xsyn dict_xsyn CONTRIB 1.0 FUNC PostgreSQL text search dictionary template for extended synonym processing
tsm_system_rows tsm_system_rows CONTRIB 1.0 FUNC PostgreSQL TABLESAMPLE method which accepts number of rows as a limit
tcn tcn CONTRIB 1.0 FUNC PostgreSQL Triggered change notifications
uuid-ossp uuid-ossp CONTRIB 1.1 FUNC PostgreSQL generate universally unique identifiers (UUIDs)
btree_gist btree_gist CONTRIB 1.7 FUNC PostgreSQL support for indexing common datatypes in GiST
btree_gin btree_gin CONTRIB 1.3 FUNC PostgreSQL support for indexing common datatypes in GIN
intarray intarray CONTRIB 1.5 FUNC PostgreSQL functions, operators, and index support for 1-D arrays of integers
intagg intagg CONTRIB 1.1 FUNC PostgreSQL integer aggregator and enumerator (obsolete)
dict_int dict_int CONTRIB 1.0 FUNC PostgreSQL text search dictionary template for integers
unaccent unaccent CONTRIB 1.1 FUNC PostgreSQL text search dictionary that removes accents
pg_repack pg_repack PGDG 1.5.0 ADMIN BSD 3 Reorganize tables in PostgreSQL databases with minimal locks
pg_squeeze pg_squeeze PGDG 1.6 ADMIN BSD 2 A tool to remove unused space from a relation.
pg_dirtyread pg_dirtyread PIGSTY 2 ADMIN BSD 3 Read dead but unvacuumed rows from table
pgfincore pgfincore PGDG 1.3.1 ADMIN BSD 3 examine and manage the os buffer cache
pgdd pgdd PIGSTY 0.5.2 ADMIN MIT An in-database data dictionary providing database introspection via standard SQL query syntax. Developed using pgx (https://github.com/zombodb/pgx).
ddlx ddlx PGDG 0.27 ADMIN PostgreSQL DDL eXtractor functions
prioritize pg_prioritize PGDG 1.0 ADMIN PostgreSQL get and set the priority of PostgreSQL backends
pg_checksums pg_checksums PGDG 1.1 ADMIN BSD 2 Activate/deactivate/verify checksums in offline Postgres clusters
pg_readonly pg_readonly PGDG 1.0.0 ADMIN PostgreSQL cluster database read only
safeupdate safeupdate PGDG 1.4 ADMIN ISC Require criteria for UPDATE and DELETE
pg_permissions pg_permissions PGDG 1.3 ADMIN BSD 2 view object permissions and compare them with the desired state
pgautofailover pgautofailover PGDG 2.1 ADMIN PostgreSQL pg_auto_failover
pg_catcheck pg_catcheck PGDG 1.4.0 ADMIN BSD 3 Diagnosing system catalog corruption
pre_prepare preprepare PIGSTY 0.4 ADMIN PostgreSQL Pre Prepare your Statement server side
pgcozy pgcozy PIGSTY 1.0 ADMIN PostgreSQL Pre-warming shared buffers according to previous pg_buffercache snapshots for PostgreSQL.
pg_orphaned pg_orphaned PIGSTY 1.0 ADMIN PostgreSQL Deal with orphaned files
pg_crash pg_crash PIGSTY 1.0 ADMIN BSD-3 Send random signals to random processes
pg_cheat_funcs pg_cheat_funcs PIGSTY 1.0 ADMIN PostgreSQL Provides cheat (but useful) functions
pg_savior pg_savior PIGSTY 0.0.1 ADMIN Apache-2.0 Postgres extension to save OOPS mistakes
table_log table_log PIGSTY 0.6.1 ADMIN PostgreSQL record table modification logs and PITR for table/row
pg_fio pg_fio PIGSTY 1.0 ADMIN BSD-3 PostgreSQL File I/O Functions
pgpool_adm pgpool PGDG 1.5 ADMIN PostgreSQL Administrative functions for pgPool
pgpool_recovery pgpool PGDG 1.4 ADMIN PostgreSQL recovery functions for pgpool-II for V4.3
pgpool_regclass pgpool PGDG 1.0 ADMIN PostgreSQL replacement for regclass
pgagent pgagent PGDG 4.2 ADMIN PostgreSQL A PostgreSQL job scheduler
vacuumlo vacuumlo CONTRIB 16.3 ADMIN PostgreSQL utility program that will remove any orphaned large objects from a PostgreSQL database
pg_prewarm pg_prewarm CONTRIB 1.2 ADMIN PostgreSQL prewarm relation data
oid2name oid2name CONTRIB 16.3 ADMIN PostgreSQL utility program that helps administrators to examine the file structure used by PostgreSQL
lo lo CONTRIB 1.1 ADMIN PostgreSQL Large Object maintenance
basic_archive basic_archive CONTRIB 16.3 ADMIN PostgreSQL an example of an archive module
basebackup_to_shell basebackup_to_shell CONTRIB 16.3 ADMIN PostgreSQL adds a custom basebackup target called shell
old_snapshot old_snapshot CONTRIB 1.0 ADMIN PostgreSQL utilities in support of old_snapshot_threshold
adminpack adminpack CONTRIB 2.1 ADMIN PostgreSQL administrative functions for PostgreSQL
amcheck amcheck CONTRIB 1.3 ADMIN PostgreSQL functions for verifying relation integrity
pg_surgery pg_surgery CONTRIB 1.0 ADMIN PostgreSQL extension to perform surgery on a damaged relation
pg_profile pg_profile PGDG 4.6 STAT BSD 2 PostgreSQL load profile repository and report builder
pg_show_plans pg_show_plans PGDG 2.1 STAT PostgreSQL show query plans of all currently running SQL statements
pg_stat_kcache pg_stat_kcache PGDG 2.2.3 STAT BSD 3 Kernel statistics gathering
pg_stat_monitor pg_stat_monitor PGDG 2.0 STAT BSD 3 The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram information.
pg_qualstats pg_qualstats PGDG 2.1.0 STAT BSD 3 An extension collecting statistics about quals
pg_store_plans pg_store_plans PGDG 1.8 STAT BSD 3 track plan statistics of all SQL statements executed
pg_track_settings pg_track_settings PGDG 2.1.2 STAT PostgreSQL Track settings changes
pg_wait_sampling pg_wait_sampling PGDG 1.1 STAT PostgreSQL sampling based statistics of wait events
system_stats system_stats PGDG 2.0 STAT PostgreSQL EnterpriseDB system statistics for PostgreSQL
meta pg_meta PIGSTY 0.4.0 STAT BSD-2 Normalized, friendlier system catalog for PostgreSQL
pgnodemx pgnodemx PIGSTY 1.6 STAT Apache-2.0 Capture node OS metrics via SQL queries
pg_proctab pgnodemx PIGSTY 0.0.10-compat STAT BSD 3 PostgreSQL extension to access the OS process table
pg_sqlog pg_sqlog PIGSTY 1.6 STAT BSD 3 Provide SQL interface to logs
bgw_replstatus bgw_replstatus PGDG 1.0.6 STAT PostgreSQL Small PostgreSQL background worker to report whether a node is a replication master or standby
pgmeminfo pgmeminfo PGDG 1.0 STAT MIT show memory usage
toastinfo toastinfo PIGSTY 1 STAT PostgreSQL show details on toasted datums
pg_mon pg_mon PIGSTY 1.0 STAT MIT PostgreSQL extension to enhance query monitoring
pg_statviz pg_statviz PGDG 0.6 STAT BSD 3 stats visualization and time series analysis
pgexporter_ext pgexporter_ext PGDG 0.2.3 STAT BSD 3 pgexporter extension for extra metrics
pg_top pg_top PGDG 3.7.0 STAT BSD 3 Monitor PostgreSQL processes similar to unix top
pagevis pagevis PIGSTY 0.1 STAT MIT Visualise database pages in ascii code
powa powa PGDG 4.2.2 STAT PostgreSQL PostgreSQL Workload Analyser-core
pageinspect pageinspect CONTRIB 1.12 STAT PostgreSQL inspect the contents of database pages at a low level
pgrowlocks pgrowlocks CONTRIB 1.2 STAT PostgreSQL show row-level locking information
sslinfo sslinfo CONTRIB 1.2 STAT PostgreSQL information about SSL certificates
pg_buffercache pg_buffercache CONTRIB 1.4 STAT PostgreSQL examine the shared buffer cache
pg_walinspect pg_walinspect CONTRIB 1.1 STAT PostgreSQL functions to inspect contents of PostgreSQL Write-Ahead Log
pg_freespacemap pg_freespacemap CONTRIB 1.2 STAT PostgreSQL examine the free space map (FSM)
pg_visibility pg_visibility CONTRIB 1.2 STAT PostgreSQL examine the visibility map (VM) and page-level visibility info
pgstattuple pgstattuple CONTRIB 1.5 STAT PostgreSQL show tuple-level statistics
auto_explain auto_explain CONTRIB 16.3 STAT PostgreSQL Provides a means for logging execution plans of slow statements automatically
pg_stat_statements pg_stat_statements CONTRIB 1.10 STAT PostgreSQL track planning and execution statistics of all SQL statements executed
passwordcheck_cracklib passwordcheck PGDG 3.0.0 SEC LGPLv2 Strengthen PostgreSQL user password checks with cracklib
supautils supautils PIGSTY 3.1.9 SEC Apache-2.0 Extension that secures a cluster on a cloud environment
pgsodium pgsodium PGDG 3.1.9 SEC BSD 3 Postgres extension for libsodium functions
supabase_vault pg_vault PIGSTY 0.2.8 SEC Apache-2.0 Supabase Vault Extension
anon anonymizer PGDG 1.3.2 SEC PostgreSQL Data anonymization tools
pg_tde pg_tde PIGSTY 1.0 SEC MIT pg_tde access method
pgsmcrypto pgsmcrypto PIGSTY 0.1.0 SEC MIT PostgreSQL SM Algorithm Extension
pgaudit pgaudit PGDG 16.0 SEC PostgreSQL provides auditing functionality
pgauditlogtofile pgauditlogtofile PGDG 1.6 SEC PostgreSQL pgAudit addon to redirect audit log to an independent file
pg_auth_mon pg_auth_mon PGDG 1.1 SEC MIT monitor connection attempts per user
credcheck credcheck PGDG 2.7.0 SEC MIT credcheck - postgresql plain text credential checker
pgcryptokey pgcryptokey PGDG 1.0 SEC PostgreSQL cryptographic key management
pg_jobmon pg_jobmon PGDG 1.4.1 SEC PostgreSQL Extension for logging and monitoring functions in PostgreSQL
logerrors logerrors PGDG 2.1 SEC BSD 3 Function for collecting statistics about messages in logfile
login_hook login_hook PGDG 1.5 SEC GPLv3 login_hook - hook to execute login_hook.login() at login time
set_user set_user PGDG 4.0.1 SEC PostgreSQL similar to SET ROLE but with added logging
pg_snakeoil pg_snakeoil PIGSTY 1 SEC PostgreSQL The PostgreSQL Antivirus
pgextwlist pgextwlist PIGSTY 1.17 SEC PostgreSQL PostgreSQL Extension Whitelisting
pg_auditor pg_auditor PIGSTY 0.2 SEC BSD-3 Audit data changes and provide flashback ability
sslutils sslutils PIGSTY 1.3 SEC PostgreSQL A Postgres extension for managing SSL certificates through SQL
noset noset PIGSTY 0.3.0 SEC AGPLv3 Module for blocking SET variables for non-super users.
sepgsql sepgsql CONTRIB 16.3 SEC PostgreSQL label-based mandatory access control (MAC) based on SELinux security policy.
auth_delay auth_delay CONTRIB 16.3 SEC PostgreSQL pause briefly before reporting authentication failure
pgcrypto pgcrypto CONTRIB 1.3 SEC PostgreSQL cryptographic functions
passwordcheck passwordcheck CONTRIB 16.3 SEC PostgreSQL checks user passwords and reject weak password
wrappers wrappers PIGSTY 0.4.1 FDW Apache-2.0 Foreign data wrappers developed by Supabase
multicorn multicorn PGDG 3.0 FDW PostgreSQL Fetch foreign data in Python in your PostgreSQL server.
mysql_fdw mysql_fdw PGDG 1.2 FDW BSD 3 Foreign data wrapper for querying a MySQL server
oracle_fdw oracle_fdw PGDG 1.2 FDW PostgreSQL foreign data wrapper for Oracle access
tds_fdw tds_fdw PGDG 2.0.3 FDW PostgreSQL Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
db2_fdw db2_fdw PGDG 6.0.1 FDW PostgreSQL foreign data wrapper for DB2 access
sqlite_fdw sqlite_fdw PGDG 1.1 FDW PostgreSQL SQLite Foreign Data Wrapper
pgbouncer_fdw pgbouncer_fdw PGDG 1.1.0 FDW PostgreSQL Extension for querying PgBouncer stats from normal SQL views & running pgbouncer commands from normal SQL functions
mongo_fdw mongo_fdw PGDG 1.1 FDW LGPLv3 foreign data wrapper for MongoDB access
redis_fdw redis_fdw PIGSTY 1.0 FDW PostgreSQL Foreign data wrapper for querying a Redis server
redis pg_redis_pubsub PIGSTY 0.0.1 FDW MIT Send redis pub/sub messages to Redis from PostgreSQL Directly
kafka_fdw kafka_fdw PIGSTY 0.0.3 FDW PostgreSQL kafka Foreign Data Wrapper for CSV formated messages
hdfs_fdw hdfs_fdw PGDG 2.0.5 FDW BSD 3 foreign-data wrapper for remote hdfs servers
firebird_fdw firebird_fdw PIGSTY 1.4.0 FDW PostgreSQL Foreign data wrapper for Firebird
aws_s3 aws_s3 PIGSTY 0.0.1 FDW Apache-2.0 aws_s3 postgres extension to import/export data from/to s3
log_fdw log_fdw PIGSTY 1.4 FDW Apache-2.0 foreign-data wrapper for Postgres log file access
dblink dblink CONTRIB 1.2 FDW PostgreSQL connect to other PostgreSQL databases from within a database
file_fdw file_fdw CONTRIB 1.0 FDW PostgreSQL foreign-data wrapper for flat file access
postgres_fdw postgres_fdw CONTRIB 1.1 FDW PostgreSQL foreign-data wrapper for remote PostgreSQL servers
orafce orafce PGDG 4.10 SIM BSD 0 Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pgtt pgtt PGDG 4.0.0 SIM ISC Extension to add Global Temporary Tables feature to PostgreSQL
session_variable session_variable PIGSTY 3.3 SIM GPLv3 Registration and manipulation of session variables and constants
pg_statement_rollback pg_statement_rollback PGDG 1.4 SIM ISC Server side rollback at statement level for PostgreSQL like Oracle or DB2
pg_dbms_metadata pg_dbms_metadata PGDG 1.0.0 SIM PostgreSQL Extension to add Oracle DBMS_METADATA compatibility to PostgreSQL
pg_dbms_lock pg_dbms_lock PGDG 1.0.0 SIM PostgreSQL Extension to add Oracle DBMS_LOCK full compatibility to PostgreSQL
pg_dbms_job pg_dbms_job PGDG 1.5.0 SIM PostgreSQL Extension to add Oracle DBMS_JOB full compatibility to PostgreSQL
babelfishpg_common babelfishpg_common WILTON 3.3.3 SIM Apache-2.0 SQL Server Transact SQL Datatype Support
babelfishpg_tsql babelfishpg_tsql WILTON 3.3.1 SIM Apache-2.0 SQL Server Transact SQL compatibility
babelfishpg_tds babelfishpg_tds WILTON 1.0.0 SIM Apache-2.0 SQL Server TDS protocol extension
babelfishpg_money babelfishpg_money WILTON 1.1.0 SIM Apache-2.0 SQL Server Money Data Type
pgmemcache pgmemcache PGDG 2.3.0 SIM MIT memcached interface
pglogical pglogical PGDG 2.4.4 ETL PostgreSQL PostgreSQL Logical Replication
pglogical_origin pglogical PGDG 1.0.0 ETL PostgreSQL Dummy extension for compatibility when upgrading from Postgres 9.4
pglogical_ticker pglogical PGDG 1.4 ETL PostgreSQL Have an accurate view on pglogical replication delay
pgl_ddl_deploy pgl_ddl_deploy PGDG 2.2 ETL MIT automated ddl deployment using pglogical
pg_failover_slots pg_failover_slots PIGSTY 1.0.1 ETL PostgreSQL PG Failover Slots extension
wal2json wal2json PGDG 2.5.3 ETL BSD 3 Changing data capture in JSON format
wal2mongo wal2mongo PIGSTY 1.0.7 ETL Apache-2.0 PostgreSQL logical decoding output plugin for MongoDB
decoderbufs decoderbufs PGDG 0.1.0 ETL MIT Logical decoding plugin that delivers WAL stream changes using a Protocol Buffer format
decoder_raw decoder_raw PIGSTY 1.0 ETL PostgreSQL Output plugin for logical replication in Raw SQL format
test_decoding test_decoding CONTRIB 16.3 ETL PostgreSQL SQL-based test/example module for WAL logical decoding
mimeo mimeo PIGSTY 1.5.1 ETL PostgreSQL Extension for specialized, per-table replication between PostgreSQL instances
repmgr repmgr PGDG 5.4 ETL GPLv3 Replication manager for PostgreSQL
pgcopydb pgcopydb PGDG 0.15 ETL PostgreSQL Copy a Postgres database to a target Postgres server
pgloader pgloader PGDG 3.6.10 ETL PostgreSQL Migrate to PostgreSQL in a single command!
pg_fact_loader pg_fact_loader PGDG 2.0 ETL MIT build fact tables with Postgres
pg_bulkload pg_bulkload PGDG 3.1.21 ETL BSD 3 pg_bulkload is a high speed data loading utility for PostgreSQL
pg_comparator pg_comparator PGDG 2.2.5 ETL BSD 3 Comparation of testing and production services PostgreSQL databases.
pgimportdoc pgimportdoc PGDG 0.1.4 ETL BSD 2 command line utility for importing XML, JSON, BYTEA document to PostgreSQL
pgexportdoc pgexportdoc PGDG 0.1.4 ETL BSD 2 export XML, JSON and BYTEA documents from PostgreSQL

Docker Application

Pigsty now offers out-of-the-box Dify and Odoo Docker Compose templates:

  • Dify: Open Source AI Agent Workflow & LLMOps
  • Odoo: Open Source ERP System

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] in node_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).

v2.7: Extension Overwhelming

Extension Overwhelming & Docker VM preparation
By Ruohang Feng（@Vonng） |

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:

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

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

v2.6: the OLAP New Challenger

PG 16 as default, ParadeDB & DuckDB support
By Ruohang Feng（@Vonng） |

v2.6.0

Highlight

Configuration

  • Disable Grafana Unified Alert to work around the “Database Locked” error。
  • add node_repo_modules to add upstream repos (including local one) to node
  • remove node_local_repo_urls, replaced by node_repo_modules & repo_upstream.
  • remove node_repo_method, replaced by node_repo_modules.
  • add the new local repo into repo_upstream instead of node_local_repo_urls
  • add chrony into node_default_packages
  • remove redis,minio,postgresql client from infra packages
  • replace repo_upstream.baseurl $releasever for pgdg el8/el9 with major.minor instead of major version

Software Upgrade

  • Grafana 10.3.3
  • Prometheus 2.47
  • node_exporter 1.7.0
  • HAProxy 2.9.5
  • Loki / Promtail 2.9.4
  • minio-20240216110548 / mcli-20240217011557
  • etcd 3.5.11
  • Redis 7.2.4
  • Bytebase 2.13.2
  • HAProxy 2.9.5
  • DuckDB 0.10.0
  • FerretDB 1.19
  • Metabase: new docker compose app template added

PostgreSQL x Pigsty Extensions

  • PostgreSQL Minor Version Upgrade 16.2, 15.6, 14.11, 13.14, 12.18
  • PostgreSQL 16 is now used as the default major version
  • pg_exporter 0.6.1, security fix
  • Patroni 3.2.2
  • pgBadger 12.4
  • pgBouncer 1.22
  • pgBackRest 2.50
  • vip-manager 2.3.0
  • PostGIS 3.4.2
  • PGVector 0.6.0
  • TimescaleDB 2.14.1
  • New Extension duckdb_fdw v1.1
  • New Extension pgsql-gzip v1.0.0
  • New Extension pg_sparse from ParadeDB: v0.5.6
  • New Extension pg_bm25 from ParadeDB: v0.5.6
  • New Extension pg_analytics from ParadeDB: v0.5.6
  • Bump AI/ML Extension pgml to v2.8.1 with pg16 support
  • Bump Columnar Extension hydra to v1.1.1 with pg16 support
  • Bump Graph Extension age to v1.5.0 with pg16 support
  • Bump Packaging Extension pg_tle to v1.3.4 with pg16 support
  • Bump GraphQL Extension pg_graphql to v1.5.0 to support supabase
330e9bc16a2f65d57264965bf98174ff  pigsty-v2.6.0.tgz
81abcd0ced798e1198740ab13317c29a  pigsty-pkg-v2.6.0.debian11.x86_64.tgz
7304f4458c9abd3a14245eaf72f4eeb4  pigsty-pkg-v2.6.0.debian12.x86_64.tgz
f914fbb12f90dffc4e29f183753736bb  pigsty-pkg-v2.6.0.el7.x86_64.tgz
fc23d122d0743d1c1cb871ca686449c0  pigsty-pkg-v2.6.0.el8.x86_64.tgz
9d258dbcecefd232f3a18bcce512b75e  pigsty-pkg-v2.6.0.el9.x86_64.tgz
901ee668621682f99799de8932fb716c  pigsty-pkg-v2.6.0.ubuntu20.x86_64.tgz
39872cf774c1fe22697c428be2fc2c22  pigsty-pkg-v2.6.0.ubuntu22.x86_64.tgz

v2.5: Debian / Ubuntu / PG16

Ubuntu/Debian Support, New Extensions & Monitor Update
By Ruohang Feng（@Vonng） |

v2.5.0

curl https://get.pigsty.cc/latest | bash

Highlights

  • Ubuntu / Debian Support: bullseye, bookworm, jammy, focal

  • Dedicate yum/apt repo on repo.pigsty.cc and mirror on packagecloud.io

  • Anolis OS Support (EL 8.8 Compatible)

  • PG Major Candidate: Use PostgreSQL 16 instead of PostgreSQL 14.

  • New Dashboard PGSQL Exporter, PGSQL Patroni, rework on PGSQL Query

  • Extensions Update:

    • Bump PostGIS version to v3.4 on el8, el9, ubuntu22, keep postgis 33 on EL7
    • Remove extension pg_embedding because it is no longer maintained, use pgvector instead.
    • New extension on EL: pointcloud with LIDAR data type support.
    • New extension on EL: imgsmlr， pg_similarity，pg_bigm 扩展。
    • Include columnar extension hydra and remove citus from default installed extension list.
    • Recompile pg_filedump as PG major version independent package.

  • Software Version Upgrade：

    • Grafana to v10.1.5
    • Prometheus to v2.47
    • Promtail/Loki to v2.9.1
    • Node Exporter to v1.6.1
    • Bytebase to v2.10.0
    • patroni to v3.1.2
    • pgbouncer to v1.21.0
    • pg_exporter to v0.6.0
    • pgbackrest to v2.48.0
    • pgbadger to v12.2
    • pg_graphql to v1.4.0
    • pg_net to v0.7.3
    • ferretdb to v0.12.1
    • sealos to 4.3.5
    • Supabase support to 20231013070755

Ubuntu Support

Pigsty has two ubuntu LTS support: 22.04 (jammy) and 20.04 (focal), and ship corresponding offline packages for them.

Some parameters need to be specified explicitly when deploying on Ubuntu, please refer to ubuntu.yml

  • repo_upstream: Adjust according to ubuntu / debian repo.
  • repo_packages: Adjust according to ubuntu / debian naming convention
  • node_repo_local_urls: use the default value: ['deb [trusted=yes] http://${admin_ip}/pigsty ./']
  • node_default_packages ：
    • zlib -> zlib1g, readline -> libreadline-dev
    • vim-minimal -> vim-tiny, bind-utils -> dnsutils, perf -> linux-tools-generic,
    • new packages acl to ensure ansible tmp file privileges are set correctly
  • infra_packages: replace all _ with - in names, and replace postgresql16 with postgresql-client-16
  • pg_packages: replace all _ with - in names, patroni-etcd not needed on ubuntu
  • pg_extensions: different naming convention, no passwordcheck_cracklib on ubuntu.
  • pg_dbsu_uid: You have to manually specify pg_dbsu_uid on ubuntu, because PGDG deb package does not specify pg dbsu uid.

API Changes

default values of following parameters have changed:

  • repo_modules: infra,node,pgsql,redis,minio

  • repo_upstream: Now add Pigsty Infra/MinIO/Redis/PGSQL modular upstream repo.

  • repo_packages: remove unused karma,mtail,dellhw_exporter and pg 14 extra extensions, adding pg 16 extra extensions.

  • node_default_packages now add python3-pip as default packages.

  • pg_libs: timescaledb is remove from shared_preload_libraries by default.

  • pg_extensions: citus is nolonger installed by default, and passwordcheck_cracklib is installed by default

    - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}*
- postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*

87e0be2edc35b18709d7722976e305b0  pigsty-pkg-v2.5.0.el7.x86_64.tgz
e71304d6f53ea6c0f8e2231f238e8204  pigsty-pkg-v2.5.0.el8.x86_64.tgz
39728496c134e4352436d69b02226ee8  pigsty-pkg-v2.5.0.el9.x86_64.tgz
e3f548a6c7961af6107ffeee3eabc9a7  pigsty-pkg-v2.5.0.debian11.x86_64.tgz
1e469cc86a19702e48d7c1a37e2f14f9  pigsty-pkg-v2.5.0.debian12.x86_64.tgz
cc3af3b7c12f98969d3c6962f7c4bd8f  pigsty-pkg-v2.5.0.ubuntu20.x86_64.tgz
c5b2b1a4867eee624e57aed58ac65a80  pigsty-pkg-v2.5.0.ubuntu22.x86_64.tgz

v2.5.1

Routine update with v16.1, v15.5, 14.10, 13.13, 12.17, 11.22

Now PostgreSQL 16 has all the core extensions available (pg_repack & timescaledb added)

  • Software Version Upgrade：
    • PostgreSQL to v16.1, v15.5, 14.10, 13.13, 12.17, 11.22
    • Patroni v3.2.0
    • PgBackrest v2.49
    • Citus 12.1
    • TimescaleDB 2.13.0 (with PG 16 support)
    • Grafana v10.2.2
    • FerretDB 1.15
    • SealOS 4.3.7
    • Bytebase 2.11.1
  • Remove monitor schema prefix from PGCAT dashboard queries
  • New template wool.yml for Aliyun free ECS singleton
  • Add python3-jmespath in addition to python3.11-jmespath for el9
31ee48df1007151009c060e0edbd74de  pigsty-pkg-v2.5.1.el7.x86_64.tgz
a40f1b864ae8a19d9431bcd8e74fa116  pigsty-pkg-v2.5.1.el8.x86_64.tgz
c976cd4431fc70367124fda4e2eac0a7  pigsty-pkg-v2.5.1.el9.x86_64.tgz
7fc1b5bdd3afa267a5fc1d7cb1f3c9a7  pigsty-pkg-v2.5.1.debian11.x86_64.tgz
add0731dc7ed37f134d3cb5b6646624e  pigsty-pkg-v2.5.1.debian12.x86_64.tgz
99048d09fa75ccb8db8e22e2a3b41f28  pigsty-pkg-v2.5.1.ubuntu20.x86_64.tgz
431668425f8ce19388d38e5bfa3a948c  pigsty-pkg-v2.5.1.ubuntu22.x86_64.tgz

v2.4: Monitoring Cloud RDS

PG16, RDS Monitor, New Extensions, Redis Enhancement
By Ruohang Feng（@Vonng） |

v2.4.0

Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)".

Highlights

  • PostgreSQL 16 support
  • The first LTS version with business support and consulting service
  • Monitoring existing PostgreSQL, RDS for PostgreSQL / PolarDB with PGRDS Dashboards
  • New extension: Apache AGE, openCypher graph query engine on PostgreSQL
  • New extension: zhparser, full text search for Chinese language
  • New extension: pg_roaringbitmap, roaring bitmap for PostgreSQL
  • New extension: pg_embedding, hnsw alternative to pgvector
  • New extension: pg_tle, admin / manage stored procedure extensions
  • New extension: pgsql-http, issue http request with SQL interface
  • Add extensions: pg_auth_mon pg_checksums pg_failover_slots pg_readonly postgresql-unit pg_store_plans pg_uuidv7 set_user
  • Redis enhancement: add monitoring panels for redis sentinel, and auto HA configuration for redis ms cluster.

API Change

  • New Parameter: REDIS.redis_sentinel_monitor: specify masters monitor by redis sentinel cluster

Bug Fix

  • Fix Grafana 10.1 registered datasource will use random uid rather than ins.datname
MD5 (pigsty-pkg-v2.4.0.el7.x86_64.tgz) = 257443e3c171439914cbfad8e9f72b17
MD5 (pigsty-pkg-v2.4.0.el8.x86_64.tgz) = 41ad8007ffbfe7d5e8ba5c4b51ff2adc
MD5 (pigsty-pkg-v2.4.0.el9.x86_64.tgz) = 9a950aed77a6df90b0265a6fa6029250

v2.3: Ecosystem Applications

PGSQL/REDIS Update, NODE VIP, Mongo/FerretDB, MYSQL Stub
By Ruohang Feng（@Vonng） |

v2.3.0

PGSQL/REDIS Update, NODE VIP, Mongo/FerretDB, MYSQL Stub

Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"

Highlight

  • INFRA: NODE/PGSQL VIP monitoring support
  • NODE: Allow bind node_vip to node cluster with keepalived
  • REPO: Dedicate yum repo, enable https for get.pigsty.cc and demo.pigsty.cc
  • PGSQL: Fix CVE-2023-39417 with PostgreSQL 15.4, 14.9, 13.12, 12.16, bump patroni version to v3.1.0
  • APP: Bump app/bytebase to v2.6.0, app/ferretdb version to v1.8, new application nocodb
  • REDIS: bump to v7.2 and rework on dashboards
  • MONGO: basic deploy & monitor support with FerretDB 1.8
  • MYSQL: add prometheus/grafana/ca stub for future implementation.

API Change

Add 1 new section NODE.NODE_VIP with 8 new parameter

  • NODE.VIP.vip_enabled : enable vip on this node cluster?
  • NODE.VIP.vip_address : node vip address in ipv4 format, required if vip is enabled
  • NODE.VIP.vip_vrid : required, integer, 1-255 should be unique among same VLAN
  • NODE.VIP.vip_role : master/backup, backup by default, use as init role
  • NODE.VIP.vip_preempt : optional, true/false, false by default, enable vip preemption
  • NODE.VIP.vip_interface : node vip network interface to listen, eth0 by default
  • NODE.VIP.vip_dns_suffix : node vip dns name suffix, .vip by default
  • NODE.VIP.vip_exporter_port : keepalived exporter listen port, 9650 by default
MD5 (pigsty-pkg-v2.3.0.el7.x86_64.tgz) = 81db95f1c591008725175d280ad23615
MD5 (pigsty-pkg-v2.3.0.el8.x86_64.tgz) = 6f4d169b36f6ec4aa33bfd5901c9abbe
MD5 (pigsty-pkg-v2.3.0.el9.x86_64.tgz) = 4bc9ae920e7de6dd8988ca7ee681459d

v2.3.1

Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)".

Highlights

  • PGVector 0.5 with HNSW index support
  • PostgreSQL 16 RC1 for el8/el9 ** Adding SealOS for kubernetes support

Bug Fix

  • Fix infra.repo.repo_pkg task when downloading rpm with * in their names in repo_packages.
    • if /www/pigsty already have package name match that pattern, some rpm will be skipped.
  • Change default value of vip_dns_suffix to '' empty string rather than .vip
  • Grant sudo privilege for postgres dbsu when pg_dbsu_sudo = limit and patroni_watchdog_mode = required
    • /usr/bin/sudo /sbin/modprobe softdog: enable watchdog module before launching patroni
    • /usr/bin/sudo /bin/chown {{ pg_dbsu }} /dev/watchdog: chown watchdog before launching patroni

Documentation Update

  • Add details to English documentation
  • Add Chinese/zh-cn documentation

Software Upgrade

  • PostgreSQL 16 RC1 on el8/el9
  • PGVector 0.5.0 with hnsw index
  • TimescaleDB 2.11.2
  • grafana 10.1.0
  • loki & promtail 2.8.4
  • mcli-20230829225506 / minio-20230829230735
  • ferretdb 1.9
  • sealos 4.3.3
  • pgbadger 1.12.2
ce69791eb622fa87c543096cdf11f970  pigsty-pkg-v2.3.1.el7.x86_64.tgz
495aba9d6d18ce1ebed6271e6c96b63a  pigsty-pkg-v2.3.1.el8.x86_64.tgz
38b45582cbc337ff363144980d0d7b64  pigsty-pkg-v2.3.1.el9.x86_64.tgz

v2.2: Observability Overhaul

Dashboard & Provision overhaul, UOS compatibility
By Ruohang Feng（@Vonng） |

v2.2.0

https://github.com/Vonng/pigsty/releases/tag/v2.2.0

Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"

Release Note: https://doc.pigsty.cc/#/RELEASENOTE?id=v220

Highlight

  • Monitoring Dashboards Overhaul: https://demo.pigsty.cc
  • Vagrant Sandbox Overhaul: libvirt support and new templates
  • Pigsty EL Yum Repo: Building simplified
  • OS Compatibility: UOS-v20-1050e support
  • New config template: prod simulation with 42 nodes
  • Use official pgdg citus distribution for el7

Software Upgrade

  • PostgreSQL 16 beta2
  • Citus 12 / PostGIS 3.3.3 / TimescaleDB 2.11.1 / PGVector 0.44
  • patroni 3.0.4 / pgbackrest 2.47 / pgbouncer 1.20
  • grafana 10.0.3 / loki/promtail/logcli 2.8.3
  • etcd 3.5.9 / haproxy v2.8.1 / redis v7.0.12
  • minio 20230711212934 / mcli 20230711233044

Bug Fix

  • Fix docker group ownership issue [29434bd]https://github.com/Vonng/pigsty/commit/29434bdd39548d95d80a236de9099874ed564f9b
  • Append infra os group rather than set it as primary group
  • Fix redis sentinel systemd enable status 5c96feb
  • Loose bootstrap & configure if /etc/redhat-release not exists
  • Fix grafana 9.x CVE-2023-1410 with 10.0.2
  • Add PG 14 - 16 new command tags and error codes for pglog schema

API Change

Add 1 new parameter

  • INFRA.NGINX.nginx_exporter_enabled : now you can disable nginx_exporter with this parameter

Default value changes:

  • repo_modules: node,pgsql,infra : redis is removed from it
  • repo_upstream:
    • add pigsty-el: distribution independent rpms: such as grafana, minio, pg_exporter, etc…
    • add pigsty-misc: distribution aware rpms: such as redis, prometheus stack binaries, etc…
    • remove citus repo since pgdg now have full official citus support (on el7)
    • remove remi , since redis is now included in pigsty-misc
    • remove grafana in build config for acceleration
  • repo_packages:
    • ansible python3 python3-pip python3-requests python3.11-jmespath dnf-utils modulemd-tools # el7: python36-requests python36-idna yum-utils
    • grafana loki logcli promtail prometheus2 alertmanager karma pushgateway node_exporter blackbox_exporter nginx_exporter redis_exporter
    • redis etcd minio mcli haproxy vip-manager pg_exporter nginx createrepo_c sshpass chrony dnsmasq docker-ce docker-compose-plugin flamegraph
    • lz4 unzip bzip2 zlib yum pv jq git ncdu make patch bash lsof wget uuid tuned perf nvme-cli numactl grubby sysstat iotop htop rsync tcpdump
    • netcat socat ftp lrzsz net-tools ipvsadm bind-utils telnet audit ca-certificates openssl openssh-clients readline vim-minimal
    • postgresql13* wal2json_13* pg_repack_13* passwordcheck_cracklib_13* postgresql12* wal2json_12* pg_repack_12* passwordcheck_cracklib_12* postgresql16* timescaledb-tools
    • postgresql15 postgresql15* citus_15* pglogical_15* wal2json_15* pg_repack_15* pgvector_15* timescaledb-2-postgresql-15* postgis33_15* passwordcheck_cracklib_15* pg_cron_15*
    • postgresql14 postgresql14* citus_14* pglogical_14* wal2json_14* pg_repack_14* pgvector_14* timescaledb-2-postgresql-14* postgis33_14* passwordcheck_cracklib_14* pg_cron_14*
    • patroni patroni-etcd pgbouncer pgbadger pgbackrest pgloader pg_activity pg_partman_15 pg_permissions_15 pgaudit17_15 pgexportdoc_15 pgimportdoc_15 pg_statement_rollback_15*
    • orafce_15* mysqlcompat_15 mongo_fdw_15* tds_fdw_15* mysql_fdw_15 hdfs_fdw_15 sqlite_fdw_15 pgbouncer_fdw_15 multicorn2_15* powa_15* pg_stat_kcache_15* pg_stat_monitor_15* pg_qualstats_15 pg_track_settings_15 pg_wait_sampling_15 system_stats_15
    • plprofiler_15* plproxy_15 plsh_15* pldebugger_15 plpgsql_check_15* pgtt_15 pgq_15* pgsql_tweaks_15 count_distinct_15 hypopg_15 timestamp9_15* semver_15* prefix_15* rum_15 geoip_15 periods_15 ip4r_15 tdigest_15 hll_15 pgmp_15 extra_window_functions_15 topn_15
    • pg_background_15 e-maj_15 pg_catcheck_15 pg_prioritize_15 pgcopydb_15 pg_filedump_15 pgcryptokey_15 logerrors_15 pg_top_15 pg_comparator_15 pg_ivm_15* pgsodium_15* pgfincore_15* ddlx_15 credcheck_15 safeupdate_15 pg_squeeze_15* pg_fkpart_15 pg_jobmon_15
  • repo_url_packages:
  • node_default_packages:
    • lz4,unzip,bzip2,zlib,yum,pv,jq,git,ncdu,make,patch,bash,lsof,wget,uuid,tuned,nvme-cli,numactl,grubby,sysstat,iotop,htop,rsync,tcpdump
    • netcat,socat,ftp,lrzsz,net-tools,ipvsadm,bind-utils,telnet,audit,ca-certificates,openssl,readline,vim-minimal,node_exporter,etcd,haproxy,python3,python3-pip
  • infra_packages
    • grafana,loki,logcli,promtail,prometheus2,alertmanager,karma,pushgateway
    • node_exporter,blackbox_exporter,nginx_exporter,redis_exporter,pg_exporter
    • nginx,dnsmasq,ansible,postgresql15,redis,mcli,python3-requests
  • PGSERVICE in .pigsty is removed, replaced with PGDATABASE=postgres.

FHS Changes:

  • bin/dns and bin/ssh now moved to vagrant/
MD5 (pigsty-pkg-v2.2.0.el7.x86_64.tgz) = 5fb6a449a234e36c0d895a35c76add3c
MD5 (pigsty-pkg-v2.2.0.el8.x86_64.tgz) = c7211730998d3b32671234e91f529fd0
MD5 (pigsty-pkg-v2.2.0.el9.x86_64.tgz) = 385432fe86ee0f8cbccbbc9454472fdd

v2.1: Vector Embedding & RAG

PostgreSQL 12 ~ 16 support
By Ruohang Feng（@Vonng） |

v2.1.0

PostgreSQL 12 ~ 16 support and pgvector for AI embedding.

https://github.com/Vonng/pigsty/releases/tag/v2.1.0

Highlight

  • PostgreSQL 16 beta support, and 12 ~ 15 support.
  • Add PGVector for AI Embedding for 12 - 15
  • Add 6 extra panel & datasource plugins for grafana
  • Add bin/profile to profile remote process and generate flamegraph
  • Add bin/validate to validate pigsty.yml configuration file
  • Add bin/repo-add to add upstream repo files to /etc/yum.repos.d
  • PostgreSQL 16 observability: pg_stat_io and corresponding dashboards

Software Upgrade

  • PostgreSQL 15.3 , 14.8, 13.11, 12.15, 11.20, and 16 beta1
  • pgBackRest 2.46
  • pgbouncer 1.19
  • Redis 7.0.11
  • Grafana v9.5.3
  • Loki / Promtail / Logcli 2.8.2
  • Prometheus 2.44
  • TimescaleDB 2.11.0
  • minio-20230518000536 / mcli-20230518165900
  • Bytebase v2.2.0

Enhancement

  • Now use all id*.pub when installing local user’s public key

v2.0: Free RDS PG Alternative

Compatibility, Security and maintainability overhaul, a truly RDS PG alternative.
By Ruohang Feng（@Vonng） |

v2.0.0

“PIGSTY” is now the abbr of “PostgreSQL in Great STYle”

or “PostgreSQL & Infrastructure & Governance System allTogether for You”.

Get pigsty v2.0.0 release via the following command:

curl -fsSL http://download.pigsty.cc/get) | bash
Download directly from GitHub Release 
bash -c "$(curl -fsSL https://raw.githubusercontent.com/Vonng/pigsty/master/bin/get)"

# or download tarball directly with curl (EL9)
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-v2.0.0.tgz -o ~/pigsty.tgz
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el9.x86_64.tgz  -o /tmp/pkg.tgz
# EL7: https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el7.x86_64.tgz
# EL8: https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el8.x86_64.tgz

Highlights

  • PostgreSQL 15.2, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 now works together and unite as one.
  • Now works on EL 7,8,9 for RHEL, CentOS, Rocky, AlmaLinux, and other EL compatible distributions
  • Security enhancement with self-signed CA, full SSL support, scram-sha-256 pwd encryption, and more.
  • Patroni 3.0 with native HA citus cluster support and dcs failsafe mode to prevent global DCS failures.
  • Auto-Configured, Battery-Included PITR for PostgreSQL powered by pgbackrest, local or S3/minio.
  • Dedicate module ETCD which can be easily deployed and scaled in/out. Used as DCS instead of Consul.
  • Dedicate module MINIO, local S3 alternative for the optional central backup repo for PGSQL PITR.
  • Better config templates with adaptive tuning for Node & PG according to your hardware spec.
  • Use AGPL v3.0 license instead of Apache 2.0 license due to Grafana & MinIO reference.

Compatibility

  • Pigsty now works on EL7, EL8, EL9, and offers corresponding pre-packed offline packages.
  • Pigsty now works on EL compatible distributions: RHEL, CentOS, Rocky, AlmaLinux, OracleLinux,…
  • Pigsty now use RockyLinux 9 as default developing & testing environment instead of CentOS 7
  • EL version, CPU arch, and pigsty version string are part of source & offline package names.
  • PGSQL: PostgreSQL 15.2 / PostGIS 3.3 / TimescaleDB 2.10 / Citus 11.2 now works together.
  • PGSQL: Patroni 3.0 is used as default HA solution for PGSQL, and etcd is used as default DCS.
    • Patroni 3.0 with DCS failsafe mode to prevent global DCS failures (demoting all primary)
    • Patroni 3.0 with native HA citus cluster support, with entirely open sourced v11 citus.
    • vip-manager 2.x with ETCDv3 API, ETCDv2 API is deprecated, so does patroni.
  • PGSQL: pgBackRest v2.44 is introduced to provide battery-include PITR for PGSQL.
    • it will use local backup FS on primary by default for a two-day retention policy
    • it will use S3/minio as an alternative central backup repo for a two-week retention policy
  • ETCD is used as default DCS instead of Consul, And V3 API is used instead of V2 API.
  • NODE module now consist of node itself, haproxy, docker, node_exporter, and promtail
    • chronyd is used as default NTP client instead of ntpd
    • HAPROXY now attach to NODE instead of PGSQL, which can be used for exposing services
    • You can register PG Service to dedicate haproxy clusters rather than local cluster nodes.
    • You can expose ad hoc service in a NodePort manner with haproxy, not limited to pg services.
  • INFRA now consist of dnsmasq, nginx, prometheus, grafana, loki
    • DNSMASQ is enabled on all infra nodes, and added to all nodes as the default resolver.
    • Add blackbox_exporter for ICMP probe, add pushgateway for batch job metrics.
    • Switch to official loki & promtail rpm packages. Use official Grafana Echarts Panel.
    • Add infra dashboards for self-monitoring, add patroni & pg15 metrics to monitoring system
  • Software Upgrade
    • PostgreSQL 15.2 / PostGIS 3.3 / TimescaleDB 2.10 / Citus 11.2
    • Patroni 3.0 / Pgbouncer 1.18 / pgBackRest 2.44 / vip-manager 2.1
    • HAProxy 2.7 / Etcd 3.5 / MinIO 20230222182345 / mcli 20230216192011
    • Prometheus 2.42 / Grafana 9.3 / Loki & Promtail 2.7 / Node Exporter 1.5

Security

  • A full-featured self-signed CA enabled by default
  • Redact password in postgres logs.
  • SSL for Nginx (you have to trust the self-signed CA or use thisisunsafe to dismiss warning)
  • SSL for etcd peer/client traffics by @alemacci
  • SSL for postgres/pgbouncer/patroni by @alemacci
  • scram-sha-256 auth for postgres password encryption by @alemacci
  • Pgbouncer Auth Query by @alemacci
  • Use AES-256-CBC for pgbackrest encryption by @alemacci
  • Adding a security enhancement config template which enforce global SSL
  • Now all hba rules are defined in config inventory, no default rules.

Maintainability

  • Adaptive tuning template for PostgreSQL & Patroni by @Vonng, @alemacci
  • configurable log dir for Patroni & Postgres & Pgbouncer & Pgbackrest by @alemacci
  • Replace fixed ip placeholder 10.10.10.10 with ${admin_ip} that can be referenced
  • Adaptive upstream repo definition that can be switched according EL ver, region & arch.
  • Terraform Templates for AWS CN & Aliyun, which can be used for sandbox IaaS provisioning
  • Vagrant Templates: meta, full, el7 el8, el9, build, minio, citus, etc…
  • New playbook pgsql-monitor.yml for monitoring existing pg instance or RDS PG.
  • New playbook pgsql-migration.yml for migrating existing pg instance to pigsty manged pg.
  • New shell utils under bin/ to simplify the daily administration tasks.
  • Optimize ansible role implementation. which can be used without default parameter values.
  • Now you can define pgbouncer parameters on database & user level

API Changes

69 parameters added, 16 parameters removed, rename 14 parameters

  • INFRA.META.admin_ip : primary meta node ip address
  • INFRA.META.region : upstream mirror region: default|china|europe
  • INFRA.META.os_version : enterprise linux release version: 7,8,9
  • INFRA.CA.ca_cn : ca common name, pigsty-ca by default
  • INFRA.CA.cert_validity : cert validity, 20 years by default
  • INFRA.REPO.repo_enabled : build a local yum repo on infra node?
  • INFRA.REPO.repo_upstream : list of upstream yum repo definition
  • INFRA.REPO.repo_home : home dir of local yum repo, usually same as nginx_home ‘/www’
  • INFRA.NGINX.nginx_ssl_port : https listen port
  • INFRA.NGINX.nginx_ssl_enabled : nginx https enabled?
  • INFRA.PROMTETHEUS.alertmanager_endpoint : altermanager endpoint in (ip|domain):port format
  • NODE.NODE_TUNE.node_hugepage_count : number of 2MB hugepage, take precedence over node_hugepage_ratio
  • NODE.NODE_TUNE.node_hugepage_ratio : mem hugepage ratio, 0 disable it by default
  • NODE.NODE_TUNE.node_overcommit_ratio : node mem overcommit ratio, 0 disable it by default
  • NODE.HAPROXY.haproxy_service : list of haproxy service to be exposed
  • PGSQL.PG_ID.pg_mode : pgsql cluster mode: pgsql,citus,gpsql
  • PGSQL.PG_BUSINESS.pg_dbsu_password : dbsu password, empty string means no dbsu password by default
  • PGSQL.PG_INSTALL.pg_log_dir : postgres log dir, /pg/data/log by default
  • PGSQL.PG_BOOTSTRAP.pg_storage_type : SSD|HDD, SSD by default
  • PGSQL.PG_BOOTSTRAP.patroni_log_dir : patroni log dir, /pg/log by default
  • PGSQL.PG_BOOTSTRAP.patroni_ssl_enabled : secure patroni RestAPI communications with SSL?
  • PGSQL.PG_BOOTSTRAP.patroni_username : patroni rest api username
  • PGSQL.PG_BOOTSTRAP.patroni_password : patroni rest api password (IMPORTANT: CHANGE THIS)
  • PGSQL.PG_BOOTSTRAP.patroni_citus_db : citus database managed by patroni, postgres by default
  • PGSQL.PG_BOOTSTRAP.pg_max_conn : postgres max connections, auto will use recommended value
  • PGSQL.PG_BOOTSTRAP.pg_shared_buffer_ratio : postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4
  • PGSQL.PG_BOOTSTRAP.pg_rto : recovery time objective, ttl to failover, 30s by default
  • PGSQL.PG_BOOTSTRAP.pg_rpo : recovery point objective, 1MB data loss at most by default
  • PGSQL.PG_BOOTSTRAP.pg_pwd_enc : algorithm for encrypting passwords: md5|scram-sha-256
  • PGSQL.PG_BOOTSTRAP.pgbouncer_log_dir : pgbouncer log dir, /var/log/pgbouncer by default
  • PGSQL.PG_BOOTSTRAP.pgbouncer_auth_query : if enabled, query pg_authid table to retrieve biz users instead of populating userlist
  • PGSQL.PG_BOOTSTRAP.pgbouncer_sslmode : SSL for pgbouncer client: disable|allow|prefer|require|verify-ca|verify-full
  • PGSQL.PG_BACKUP.pgbackrest_enabled : pgbackrest enabled?
  • PGSQL.PG_BACKUP.pgbackrest_clean : remove pgbackrest data during init ?
  • PGSQL.PG_BACKUP.pgbackrest_log_dir : pgbackrest log dir, /pg/log by default
  • PGSQL.PG_BACKUP.pgbackrest_method : pgbackrest backup repo method, local or minio
  • PGSQL.PG_BACKUP.pgbackrest_repo : pgbackrest backup repo config
  • PGSQL.PG_SERVICE.pg_service_provider : dedicate haproxy node group name, or empty string for local nodes by default
  • PGSQL.PG_SERVICE.pg_default_service_dest : default service destination if svc.dest=‘default’
  • PGSQL.PG_SERVICE.pg_vip_enabled : enable a l2 vip for pgsql primary? false by default
  • PGSQL.PG_SERVICE.pg_vip_address : vip address in <ipv4>/<mask> format, require if vip is enabled
  • PGSQL.PG_SERVICE.pg_vip_interface : vip network interface to listen, eth0 by default
  • PGSQL.PG_SERVICE.pg_dns_suffix : pgsql cluster dns name suffix, ’’ by default
  • PGSQL.PG_SERVICE.pg_dns_target : auto, primary, vip, none, or ad hoc ip
  • ETCD.etcd_seq : etcd instance identifier, REQUIRED
  • ETCD.etcd_cluster : etcd cluster & group name, etcd by default
  • ETCD.etcd_safeguard : prevent purging running etcd instance?
  • ETCD.etcd_clean : purging existing etcd during initialization?
  • ETCD.etcd_data : etcd data directory, /data/etcd by default
  • ETCD.etcd_port : etcd client port, 2379 by default
  • ETCD.etcd_peer_port : etcd peer port, 2380 by default
  • ETCD.etcd_init : etcd initial cluster state, new or existing
  • ETCD.etcd_election_timeout : etcd election timeout, 1000ms by default
  • ETCD.etcd_heartbeat_interval : etcd heartbeat interval, 100ms by default
  • MINIO.minio_seq : minio instance identifier, REQUIRED
  • MINIO.minio_cluster : minio cluster name, minio by default
  • MINIO.minio_clean : cleanup minio during init?, false by default
  • MINIO.minio_user : minio os user, minio by default
  • MINIO.minio_node : minio node name pattern
  • MINIO.minio_data : minio data dir(s), use {x…y} to specify multi drivers
  • MINIO.minio_domain : minio external domain name, sss.pigsty by default
  • MINIO.minio_port : minio service port, 9000 by default
  • MINIO.minio_admin_port : minio console port, 9001 by default
  • MINIO.minio_access_key : root access key, minioadmin by default
  • MINIO.minio_secret_key : root secret key, minioadmin by default
  • MINIO.minio_extra_vars : extra environment variables for minio server
  • MINIO.minio_alias : alias name for local minio deployment
  • MINIO.minio_buckets : list of minio bucket to be created
  • MINIO.minio_users : list of minio user to be created

Removed Parameters

  • INFRA.CA.ca_homedir: ca home dir, now fixed as /etc/pki/
  • INFRA.CA.ca_cert: ca cert filename, now fixed as ca.key
  • INFRA.CA.ca_key: ca key filename, now fixed as ca.key
  • INFRA.REPO.repo_upstreams: replaced by repo_upstream
  • PGSQL.PG_INSTALL.pgdg_repo: now taken care by node playbooks
  • PGSQL.PG_INSTALL.pg_add_repo: now taken care by node playbooks
  • PGSQL.PG_IDENTITY.pg_backup: not used and conflict with section name
  • PGSQL.PG_IDENTITY.pg_preflight_skip: not used anymore, replace by pg_id
  • DCS.dcs_name : removed due to using etcd
  • DCS.dcs_servers : replaced by using ad hoc group etcd
  • DCS.dcs_registry : removed due to using etcd
  • DCS.dcs_safeguard : replaced by etcd_safeguard
  • DCS.dcs_clean : replaced by etcd_clean
  • PGSQL.PG_VIP.vip_mode : replaced by pg_vip_enabled
  • PGSQL.PG_VIP.vip_address : replaced by pg_vip_address
  • PGSQL.PG_VIP.vip_interface : replaced by pg_vip_interface

Renamed Parameters

  • nginx_upstream -> infra_portal
  • repo_address -> repo_endpoint
  • pg_hostname -> node_id_from_pg
  • pg_sindex -> pg_group
  • pg_services -> pg_default_services
  • pg_services_extra -> pg_services
  • pg_hba_rules_extra -> pg_hba_rules
  • pg_hba_rules -> pg_default_hba_rules
  • pgbouncer_hba_rules_extra -> pgb_hba_rules
  • pgbouncer_hba_rules -> pgb_default_hba_rules
  • node_packages_default -> node_default_packages
  • node_packages_meta -> infra_packages
  • node_packages_meta_pip -> infra_packages_pip
  • node_data_dir -> node_data

Checksums

MD5 (pigsty-pkg-v2.0.0.el7.x86_64.tgz) = 9ff3c973fa5915f65622b91419817c9b
MD5 (pigsty-pkg-v2.0.0.el8.x86_64.tgz) = bd108a6c8f026cb79ee62c3b68b72176
MD5 (pigsty-pkg-v2.0.0.el9.x86_64.tgz) = e24288770f240af0511b0c38fa2f4774

Special thanks to @alemacci for his great contribution!

v2.0.1

Bug fix for v2.0.0 and security improvement.

Enhancement

  • Replace the pig shape logo for compliance with the PostgreSQL trademark policy.
  • Bump grafana version to v9.4 with better UI and bugfix.
  • Bump patroni version to v3.0.1 with some bugfix.
  • Change: rollback grafana systemd service file to rpm default.
  • Use slow copy instead of rsync to copy grafana dashboards.
  • Enhancement: add back default repo files after bootstrap
  • Add asciinema video for various administration tasks.
  • Security Enhance Mode: restrict monitor user privilege.
  • New config template: dual.yml for two-node deployment.
  • Enable log_connections and log_disconnections in crit.yml template.
  • Enable $lib/passwordcheck in pg_libs in crit.yml template.
  • Explicitly grant monitor view permission to pg_monitor role.
  • Remove default dbrole_readonly from dbuser_monitor to limit monitor user privilege
  • Now patroni listen on {{ inventory_hostname }} instead of 0.0.0.0
  • Now you can control postgres/pgbouncer listen to address with pg_listen
  • Now you can use placeholder ${ip}, ${lo}, ${vip} in pg_listen
  • Bump Aliyun terraform image to rocky Linux 9 instead of centos 7.9
  • Bump bytebase to v1.14.0

Bug Fixes

  • Add missing advertise address for alertmanager
  • Fix missing pg_mode error when adding postgres user with bin/pgsql-user
  • Add -a password to redis-join task @ redis.yml
  • Fix missing default value in infra-rm.yml.remove infra data
  • Fix prometheus targets file ownership to prometheus
  • Use admin user rather than root to delete metadata in DCS
  • Fix Meta datasource missing database name due to grafana 9.4 bug.

Caveats

Official EL8 pgdg upstream is broken now, DO use it with caution!

Affected packages: postgis33_15, pgloader, postgresql_anonymizer_15*, postgresql_faker_15

How to Upgrade

cd ~/pigsty; tar -zcf /tmp/files.tgz files; rm -rf ~/pigsty    # backup files dir and remove
cd ~; bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"    # get latest pigsty source
cd ~/pigsty; rm -rf files; tar -xf /tmp/files.tgz -C ~/pigsty  # restore files dir

Checksums

MD5 (pigsty-pkg-v2.0.1.el7.x86_64.tgz) = 5cfbe98fd9706b9e0f15c1065971b3f6
MD5 (pigsty-pkg-v2.0.1.el8.x86_64.tgz) = c34aa460925ae7548866bf51b8b8759c
MD5 (pigsty-pkg-v2.0.1.el9.x86_64.tgz) = 055057cebd93c473a67fb63bcde22d33

Special thanks to @cocoonkid for his feedback.

v2.0.2

Highlight

Store OpenAI embedding and search similar vectors with pgvector

Changes

  • New extension pgvector for storing OpenAI embedding and searching similar vectors.
  • MinIO CVE-2023-28432 fix, and upgrade to 20230324 with new policy API.
  • Add reload functionality to DNSMASQ systemd services
  • Bump pev to v1.8
  • Bump grafana to v9.4.7
  • Bump MinIO and MCLI version to 20230324
  • Bump bytebase version to v1.15.0
  • Upgrade monitoring dashboards and fix dead links
  • Upgrade aliyun terraform template image to rockylinux 9
  • Adopt grafana provisioning API change since v9.4
  • Add asciinema videos for various administration tasks
  • Fix broken EL8 pgsql deps: remove anonymizer_15 faker_15 and pgloader
MD5 (pigsty-pkg-v2.0.2.el7.x86_64.tgz) = d46440a115d741386d29d6de646acfe2
MD5 (pigsty-pkg-v2.0.2.el8.x86_64.tgz) = 5fa268b5545ac96b40c444210157e1e1
MD5 (pigsty-pkg-v2.0.2.el9.x86_64.tgz) = c8b113d57c769ee86a22579fc98e8345

v1.5.0 Release Note

Docker Support, Infra self-monitoring, etcd as DCS, CMDB reforge
By Ruohang Feng（@Vonng） |

v1.5.0

Highlights

  • Complete Docker Support, enable on meta nodes by default with lot’s of software templates.
    • bytebase pgadmin4 pgweb postgrest kong minio,…
  • Infra Self Monitoring: Nginx, ETCD, Consul, Grafana, Prometheus, Loki, etc…
  • New CMDB design compatible with redis & greenplum, visualize with CMDB Overview
  • Service Discovery : Consul SD now works again for prometheus targets management
  • Redis playbook now works on single instance with redis_port option.
  • Better cold backup support: crontab for backup, delayed standby with pg_delay
  • Use ETCD as DCS, alternative to Consul

Monitoring

Dashboards

  • CMDB Overview: Visualize CMDB Inventory
  • DCS Overview: Show consul & etcd metrics
  • Nginx Overview: Visualize nginx metrics & access/error logs
  • Grafana Overview: Grafana self Monitoring
  • Prometheus Overview：Prometheus self Monitoring
  • INFRA Dashboard & Home Dashboard Reforge

Architecture

  • Infra monitoring targets now have a separated target dir targets/infra
  • Consul SD is available for prometheus
  • etcd , consul , patroni, docker metrics
  • Now infra targets are managed by role infra_register
  • Upgrade pg_exporter to v0.5.0 with scale and default support
    • pg_bgwriter, pg_wal, pg_query, pg_db, pgbouncer_stat now use seconds instead of ms and µs
    • pg_table counters now have default value 0 instead of NaN
    • pg_class is replaced by pg_table and pg_index
    • pg_table_size is now enabled with 300s ttl

Provisioning

  • New optional package docker.tgz contains: Pgadmin, Pgweb, Postgrest, ByteBase, Kong, Minio, etc.
  • New Role etcd to deploy & monitor etcd dcs service
  • Specify which type of DCS to use with pg_dcs_type (etcd now available)
  • Add pg_checksum option to enable data checksum
  • Add pg_delay option to setup delayed standby leaders
  • Add node_crontab and node_crontab_overwrite to create routine jobs such as cold backup
  • Add a series of *_enable options to control components
  • Loki and Promtail are now installed using the RPM package made by frpm.

Software Updates

  • Upgrade PostgreSQL to 14.3
  • Upgrade Redis to 6.2.7
  • Upgrade PG Exporter to 0.5.0
  • Upgrade Consul to 1.12.0
  • Upgrade vip-manager to v1.0.2
  • Upgrade Grafana to v8.5.2
  • Upgrade HAproxy to 2.5.7 without rsyslog dependency
  • Upgrade Loki & Promtail to v2.5.0 with RPM packages
  • New packages: pg_probackup

New software / application based on docker:

  • bytebase : DDL Schema Migrator
  • pgadmin4 : Web Admin UI for PostgreSQL
  • pgweb : Web Console for PostgreSQL
  • postgrest : Auto generated REST API for PostgreSQL
  • kong : API Gateway which use PostgreSQL as backend storage
  • swagger openapi : API Specification Generator
  • Minio : S3-compatible object storage

Bug Fix

  • Fix loki & promtail /etc/default config file name issue
  • Now node_data_dir (/data) is created before consul init if not exists
  • Fix haproxy silence /var/log/messages with inappropriate rsyslog dependency

API Change

New Variable

  • node_data_dir : major data mount path, will be created if not exist.
  • node_crontab_overwrite : overwrite /etc/crontab instead of append
  • node_crontab: node crontab to be appended or overwritten
  • nameserver_enabled: enable nameserver on this meta node?
  • prometheus_enabled: enable prometheus on this meta node?
  • grafana_enabled: enable grafana on this meta node?
  • loki_enabled: enable loki on this meta node?
  • docker_enable: enable docker on this node?
  • consul_enable: enable consul server/agent?
  • etcd_enable: enable etcd server/clients?
  • pg_checksum: enable pg cluster data-checksum?
  • pg_delay: recovery min apply delay for standby leader

Reforge

Now *_clean are boolean flags to clean up existing instance during init.

And *_safeguard are boolean flags to avoid purging running instance when executing any playbook.

  • pg_exists_action -> pg_clean
  • pg_disable_purge -> pg_safeguard
  • dcs_exists_action -> dcs_clean
  • dcs_disable_purge -> dcs_safeguard

Rename

  • node_ntp_config -> node_ntp_enabled
  • node_admin_setup -> node_admin_enabled
  • node_admin_pks -> node_admin_pk_list
  • node_dns_hosts -> node_etc_hosts_default
  • node_dns_hosts_extra -> node_etc_hosts
  • node_dns_server -> node_dns_method
  • node_local_repo_url -> node_repo_local_urls
  • node_packages -> node_packages_default
  • node_extra_packages -> node_packages
  • node_packages_meta -> node_packages_meta
  • node_meta_pip_install -> node_packages_meta_pip
  • node_sysctl_params -> node_tune_params
  • app_list -> nginx_indexes
  • grafana_plugin -> grafana_plugin_method
  • grafana_cache -> grafana_plugin_cache
  • grafana_plugins -> grafana_plugin_list
  • grafana_git_plugin_git -> grafana_plugin_git
  • haproxy_admin_auth_enabled -> haproxy_auth_enabled
  • pg_shared_libraries -> pg_libs
  • dcs_type -> pg_dcs_type

v1.5.1

Highlights

WARNING: CREATE INDEX|REINDEX CONCURRENTLY PostgreSQL 14.0 - 14.3 may lead to index data corruption!

Please upgrade postgres to 14.4 ASAP.

Software Upgrade

  • upgrade postgres to 14.4
  • Upgrade haproxy to 2.6.0
  • Upgrade grafana to 9.0.0
  • Upgrade prometheus 2.36.0
  • Upgrade patroni to 2.1.4

Bug fix:

  • Fix typo in pgsql-migration.yml
  • remove pid file in haproxy config
  • remove i686 packages when using repotrack under el7
  • Fix redis service systemctl enabled issue
  • Fix patroni systemctl service enabled=no by default issue

API Changes

  • Mark grafana_database and grafana_pgurl as obsolete

New Apps

  • wiki.js : Local wiki with Postgres

v1.4.0 Release Note

Add matrixDB Support
By Ruohang Feng（@Vonng） |

v1.4.0

Architecture

  • Decouple system into 4 major categories: INFRA, NODES, PGSQL, REDIS, which makes pigsty far more clear and more extensible.
  • Single Node Deployment = INFRA + NODES + PGSQL
  • Deploy pgsql clusters = NODES + PGSQL
  • Deploy redis clusters = NODES + REDIS
  • Deploy other databases = NODES + xxx (e.g MONGO, KAFKA, … TBD)

Accessibility

  • CDN for mainland China.
  • Get the latest source with bash -c "$(curl -fsSL http://download.pigsty.cc/get)"
  • Download & Extract packages with new download script.

Monitor Enhancement

  • Split monitoring system into 5 major categories: INFRA, NODES, REDIS, PGSQL, APP
  • Logging enabled by default
    • now loki and promtail are enabled by default. with prebuilt loki-rpm
  • Models & Labels
    • A hidden ds prometheus datasource variable is added for all dashboards, so you can easily switch different datasource simply by select a new one rather than modifying Grafana Datasources & Dashboards
    • An ip label is added for all metrics, and will be used as join key between database metrics & nodes metrics
  • INFRA Monitoring
    • Home dashboard for infra: INFRA Overview
    • Add logging Dashboards : Logs Instance
    • PGLOG Analysis & PGLOG Session now treated as an example Pigsty APP.
  • NODES Monitoring Application
    • If you don’t care database at all, Pigsty now can be used as host monitoring software alone!
    • Consist of 4 core dashboards: Nodes Overview & Nodes Cluster & Nodes Instance & Nodes Alert
    • Introduce new identity variables for nodes: node_cluster and nodename
    • Variable pg_hostname now means set hostname same as postgres instance name to keep backward-compatible
    • Variable nodename_overwrite control whether overwrite node’s hostname with nodename
    • Variable nodename_exchange will write nodename to each other’s /etc/hosts
    • All nodes metrics reference are overhauled, join by ip
    • Nodes monitoring targets are managed alone under /etc/prometheus/targets/nodes
  • PGSQL Monitoring Enhancement
    • Complete new PGSQL Cluster which simplify and focus on important stuff among cluster.
    • New Dashboard PGSQL Databases which is cluster level object monitoring. Such as tables & queries among the entire cluster rather than single instance.
    • PGSQL Alert dashboard now only focus on pgsql alerts.
    • PGSQL Shard are added to PGSQL
  • Redis Monitoring Enhancement
    • Add nodes monitoring for all redis dashboards.

MatrixDB Support

  • MatrixDB (Greenplum 7) can be deployed via pigsty-matrix.yml playbook
  • MatrixDB Monitor Dashboards : PGSQL MatrixDB
  • Example configuration added: pigsty-mxdb.yml

Provisioning Enhancement

Now pigsty work flow works as this:

 infra.yml ---> install pigsty on single meta node
      |          then add more nodes under pigsty's management
      |
 nodes.yml ---> prepare nodes for pigsty (node setup, dcs, node_exporter, promtail)
      |          then choose one playbook to deploy database clusters on those nodes
      |
      ^--> pgsql.yml   install postgres on prepared nodes
      ^--> redis.yml   install redis on prepared nodes

infra-demo.yml = 
           infra.yml -l meta     +
           nodes.yml -l pg-test  +
           pgsql.yml -l pg-test +
           infra-loki.yml + infra-jupyter.yml + infra-pgweb.yml
  • nodes.yml to setup & prepare nodes for pigsty
    • setup node, node_exporter, consul agent on nodes
    • node-remove.yml are used for node de-register
  • pgsql.yml now only works on prepared nodes
    • pgsql-remove now only responsible for postgres itself. (dcs and node monitor are taken by node.yml)
    • Add a series of new options to reuse postgres role in greenplum/matrixdb
  • redis.yml now works on prepared nodes
    • and redis-remove.yml now remove redis from nodes.
  • pgsql-matrix.yml now install matrixdb (Greenplum 7) on prepared nodes.

Software Upgrade

  • PostgreSQL 14.2
  • PostGIS 3.2
  • TimescaleDB 2.6
  • Patroni 2.1.3 (Prometheus Metrics + Failover Slots)
  • HAProxy 2.5.5 (Fix stats error, more metrics)
  • PG Exporter 0.4.1 (Timeout Parameters, and)
  • Grafana 8.4.4
  • Prometheus 2.33.4
  • Greenplum 6.19.4 / MatrixDB 4.4.0
  • Loki are now shipped as rpm packages instead of zip archives

Bug Fix

  • Remove consul dependency for patroni , which makes it much more easier to migrate to a new consul cluster
  • Fix prometheus bin/new scripts default data dir path : /export/prometheus to /data/prometheus
  • Fix typos and tasks
  • Add restart seconds to vip-manager systemd service

API Changes

New Variable

  • node_cluster: Identity variable for node cluster
  • nodename_overwrite: If set, nodename will be set to node’s hostname
  • nodename_exchange : exchange node hostname (in /etc/hosts) among play hosts
  • node_dns_hosts_extra : extra static dns records which can be easily overwritten by single instance/cluster
  • patroni_enabled: if disabled, postgres & patroni bootstrap will not be performed during role postgres
  • pgbouncer_enabled : if disabled, pgbouncer will not be launched during role postgres
  • pg_exporter_params: extra url parameters for pg_exporter when generating monitor target url.
  • pg_provision: bool var to indicate whether perform provision part of role postgres (template, db,user)
  • no_cmdb: cli args for infra.yml and infra-demo.yml playbook which will not create cmdb on meta node.
MD5 (app.tgz) = f887313767982b31a2b094e5589a75ea
MD5 (matrix.tgz) = 3d063437c482d94bd7e35df1a08bbc84
MD5 (pigsty.tgz) = e143b88ebea1474f9ebaffddc6072c49
MD5 (pkg.tgz) = 73e8f5ce995b1f1760cb63c1904fb91b

v1.4.1

Routine bug fix / Docker Support / English Docs

Now docker is enabled on meta node by default. You can launch ton’s of SaaS with it

English document is available now.

Bug Fix

v1.3.0 Release Note

Redis Support, PGCAT Overhaul
By Ruohang Feng（@Vonng） |

1.3.0

  • [ENHANCEMENT] Redis Deployment (cluster,sentinel,standalone)

  • [ENHANCEMENT] Redis Monitor

    • Redis Overview Dashboard
    • Redis Cluster Dashboard
    • Redis Instance Dashboard

  • [ENHANCEMENT] monitor: PGCAT Overhaul

    • New Dashboard: PGCAT Instance
    • New Dashboard: PGCAT Database Dashboard
    • Remake Dashboard: PGCAT Table

  • [ENHANCEMENT] monitor: PGSQL Enhancement

    • New Panels: PGSQL Cluster, add 10 key metrics panel (toggled by default)
    • New Panels: PGSQL Instance, add 10 key metrics panel (toggled by default)
    • Simplify & Redesign: PGSQL Service
    • Add cross-references between PGCAT & PGSL dashboards

  • [ENHANCEMENT] monitor deploy

    • Now grafana datasource is automatically registered during monly deployment

  • [ENHANCEMENT] software upgrade

    • add PostgreSQL 13 to default package list
    • upgrade to PostgreSQL 14.1 by default
    • add greenplum rpm and dependencies
    • add redis rpm & source packages
    • add perf as default packages

v1.3.1

[Monitor]

  • PGSQL & PGCAT Dashboard polish
  • optimize layout for pgcat instance & pgcat database
  • add key metrics panels to pgsql instance dashboard, keep consist with pgsql cluster
  • add table/index bloat panels to pgcat database, remove pgcat bloat dashboard.
  • add index information in pgcat database dashboard
  • fix broken panels in grafana 8.3
  • add redis index in nginx homepage

[Deploy]

  • New infra-demo.yml playbook for one-pass bootstrap
  • Use infra-jupyter.yml playbook to deploy optional jupyter lab server
  • Use infra-pgweb.yml playbook to deploy optional pgweb server
  • New pg alias on meta node, can initiate postgres cluster from admin user (in addition to postgres)
  • Adjust all patroni conf templates’s max_locks_per_transactions according to timescaledb-tune ’s advise
  • Add citus.node_conninfo: 'sslmode=prefer' to conf templates in order to use citus without SSL
  • Add all extensions (except for pgrouting) in pgdg14 in package list
  • Upgrade node_exporter to v1.3.1
  • Add PostgREST v9.0.0 to package list. Generate API from postgres schema.

[BugFix]

  • Grafana’s security breach (upgrade to v8.3.1 issue)
  • fix pg_instance & pg_service in register role when start from middle of playbook
  • Fix nginx homepage render issue when host without pg_cluster variable exists
  • Fix style issue when upgrading to grafana 8.3.1

v1.2.0 Release Note

Redis Support, PGCAT Overhaul
By Ruohang Feng（@Vonng） |

v1.2.0

  • [ENHANCEMENT] Use PostgreSQL 14 as default version
  • [ENHANCEMENT] Use TimescaleDB 2.5 as default extension
    • now timescaledb & postgis are enabled in cmdb by default
  • [ENHANCEMENT] new monitor-only mode:
    • you can use pigsty to monitor existing pg instances with a connectable url only
    • pg_exporter will be deployed on meta node locally
    • new dashboard PGSQL Cluster Monly for remote clusters
  • [ENHANCEMENT] Software upgrade
    • grafana to 8.2.2
    • pev2 to v0.11.9
    • promscale to 0.6.2
    • pgweb to 0.11.9
    • Add new extensions: pglogical pg_stat_monitor orafce
  • [ENHANCEMENT] Automatic detect machine spec and use proper node_tune and pg_conf templates
  • [ENHANCEMENT] Rework on bloat related views, now more information are exposed
  • [ENHANCEMENT] Remove timescale & citus internal monitoring
  • [ENHANCEMENT] New playbook pgsql-audit.yml to create audit report.
  • [BUG FIX] now pgbouncer_exporter resource owner are {{ pg_dbsu }} instead of postgres
  • [BUG FIX] fix pg_exporter duplicate metrics on pg_table pg_index while executing REINDEX TABLE CONCURRENTLY
  • [CHANGE] now all config templates are minimize into two: auto & demo. (removed: pub4, pg14, demo4, tiny, oltp )
    • pigsty-demo is configured if vagrant is the default user, otherwise pigsty-auto is used.

How to upgrade from v1.1.1

There’s no API change in 1.2.0 You can still use old pigsty.yml configuration files (PG13).

For the infrastructure part. Re-execution of repo will do most of the parts

As for the database. You can still use the existing PG13 instances. In-place upgrade is quite tricky especially when involving extensions such as PostGIS & Timescale. I would highly recommend performing a database migration with logical replication.

The new playbook pgsql-migration.yml will make this a lot easier. It will create a series of scripts which will help you to migrate your cluster with near-zero downtime.

v1.1.0 Release Note

v1.1.0 Release
By Ruohang Feng（@Vonng） |

v1.1.0

  • [ENHANCEMENT] add pg_dummy_filesize to create fs space placeholder
  • [ENHANCEMENT] home page overhaul
  • [ENHANCEMENT] add jupyter lab integration
  • [ENHANCEMENT] add pgweb console integration
  • [ENHANCEMENT] add pgbadger support
  • [ENHANCEMENT] add pev2 support, explain visualizer
  • [ENHANCEMENT] add pglog utils
  • [ENHANCEMENT] update default pkg.tgz software version:
    • upgrade postgres to v13.4 (with official pg14 support)
    • upgrade pgbouncer to v1.16 (metrics definition updates)
    • upgrade grafana to v8.1.4
    • upgrade prometheus to v2.2.29
    • upgrade node_exporter to v1.2.2
    • upgrade haproxy to v2.1.1
    • upgrade consul to v1.10.2
    • upgrade vip-manager to v1.0.1

API Changes

  • nginx_upstream now holds different structures. (incompatible)

  • new config entries: app_list, render into home page’s nav entries

  • new config entries: docs_enabled, setup local docs on default server.

  • new config entries: pev2_enabled, setup local pev2 utils.

  • new config entries: pgbadger_enabled, create log summary/report dir

  • new config entries: jupyter_enabled, enable jupyter lab server on meta node

  • new config entries: jupyter_username, specify which user to run jupyter lab

  • new config entries: jupyter_password, specify jupyter lab default password

  • new config entries: pgweb_enabled, enable pgweb server on meta node

  • new config entries: pgweb_username, specify which user to run pgweb

  • rename internal flag repo_exist into repo_exists

  • now default value for repo_address is pigsty instead of yum.pigsty

  • now haproxy access point is http://pigsty instead of http://h.pigsty

v1.1.1

  • [ENHANCEMENT] replace timescaledb apache version with timescale version
  • [ENHANCEMENT] upgrade prometheus to 2.30
  • [BUG FIX] now pg_exporter config dir’s owner are {{ pg_dbsu }} instead of prometheus

How to upgrade from v1.1.0 The major change in this release is timescaledb. Which replace old apache license version with timescale license version

stop/pause postgres instance with timescaledb
yum remove -y timescaledb_13

[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch
repo_gpgcheck=0
gpgcheck=0
enabled=1

yum install timescaledb-2-postgresql13

v1.0.0 Release Note

v1.0.0 Release
By Ruohang Feng（@Vonng） |

v1.0.0

Highlights

  • Monitoring System Overhaul

    • New Dashboards on Grafana 8.0
    • New metrics definition, with extra PG14 support
    • Simplified labeling system: static label set: (job, cls, ins)
    • New Alerting Rules & Derived Metrics
    • Monitoring multiple database at one time
    • Realtime log search & csvlog analysis
    • Link-Rich Dashboards, click graphic elements to drill-down|roll-up

  • Architecture Changes

    • Add citus & timescaledb as part of default installation
    • Add PostgreSQL 14beta2 support
    • Simply haproxy admin page index
    • Decouple infra & pgsql by adding a new role register
    • Add new role loki and promtail for logging
    • Add new role environ for setting up environment for admin user on admin node
    • Using static service-discovery for prometheus by default (instead of consul)
    • Add new role remove to gracefully remove cluster & instance
    • Upgrade prometheus & grafana provisioning logics.
    • Upgrade to vip-manager 1.0 , node_exporter 1.2 , pg_exporter 0.4, grafana 8.0
    • Now every database on every instance can be auto-registered as grafana datasource
    • Move consul register tasks to role register, change consul service tags
    • Add cmdb.sql as pg-meta baseline definition (CMDB & PGLOG)

  • Application Framework

    • Extensible framework for new functionalities
    • core app: PostgreSQL Monitor System: pgsql
    • core app: PostgreSQL Catalog explorer: pgcat
    • core app: PostgreSQL Csvlog Analyzer: pglog
    • add example app covid for visualizing covid-19 data.
    • add example app isd for visualizing isd data.

  • Misc

    • Add jupyterlab which brings entire python environment for data science
    • Add vonng-echarts-panel to bring Echarts support back.
    • Add wrap script createpg , createdb, createuser
    • Add cmdb dynamic inventory scripts: load_conf.py, inventory_cmdb, inventory_conf
    • Remove obsolete playbooks: pgsql-monitor, pgsql-service, node-remove, etc….

API Change

Bug Fix

  • Fix default timezone Asia/Shanghai (CST) issue
  • Fix nofile limit for pgbouncer & patroni
  • Pgbouncer userlist & database list will be generated when executing tag pgbouncer

v1.0.1

2021-09-14

  • Documentation Update
    • Chinese document now viable
    • Machine-Translated English document now viable
  • Bug Fix: pgsql-remove does not remove primary instance.
  • Bug Fix: replace pg_instance with pg_cluster + pg_seq
    • Start-At-Task may fail due to pg_instance undefined
  • Bug Fix: remove citus from default shared preload library
    • citus will force max_prepared_transaction to non-zero value
  • Bug Fix: ssh sudo checking in configure:
    • now ssh -t sudo -n ls is used for privilege checking
  • Typo Fix: pg-backup script typo
  • Alert Adjust: Remove ntp sanity check alert (dupe with ClockSkew)
  • Exporter Adjust: remove collector.systemd to reduce overhead

v0.9.0 Release Note

v0.9 accessbility enhancement, logging improvement, pigsty CLI/GUI beta
By Ruohang Feng（@Vonng） |

Pigsty v0.9.0

Features

  • One-Line Installation

    Run this on meta node /bin/bash -c "$(curl -fsSL https://pigsty.cc/install)"

  • MetaDB provisioning

    Now you can use pgsql database on meta node as inventory instead of static yaml file affter bootstrap.

  • Add Loki & Prometail as optinal logging collector

    Now you can view, query, search postgres|pgbouncer|patroni logs with Grafana UI (PG Instance Log)

  • Pigsty CLI/GUI (beta)

    Mange you pigsty deployment with much more human-friendly command line interface.

Bug Fix

  • Log related issues
    • fix connection reset by peer entries in postgres log caused by Haproxy health check.
    • fix Connect Reset Exception in patroni logs caused by haproxy health check
    • fix patroni log time format (remove mill seconds, add timezone)
    • set log_min_duration_statement=1s for dbuser_monitor to get ride of monitor logs.
  • Fix pgbouncer-create-user does not handle md5 password properly
  • Fix obsolete Makefile entries
  • Fix node dns nameserver lost when abort during resolv.conf rewrite
  • Fix db/user template and entry not null check

API Change

  • Set default value of node_disable_swap to false
  • Remove example enties of node_sysctl_params.
  • grafana_plugin default install will now download from CDN if plugins not exists
  • repo_url_packages now download rpm via pigsty CDN to accelerate.
  • proxy_env.no_proxy now add pigsty CDN to noproxy sites。
  • grafana_customize set to false by default，enable it means install pigsty pro UI.
  • node_admin_pk_current add current user’s ~/.ssh/id_rsa.pub to admin pks
  • loki_clean whether to cleanup existing loki data during init
  • loki_data_dir set default data dir for loki logging service
  • promtail_enabled enabling promtail logging agent service?
  • promtail_clean remove existing promtail status during init?
  • promtail_port default port used by promtail, 9080 by default
  • promtail_status_file location of promtail status file
  • promtail_send_url endpoint of loki service which receives log data

v0.8.0 Release Note

v0.8 service provision overhaul and general RC
By Ruohang Feng（@Vonng） |

Pigsty v0.8.0

Pigsty now is in RC status with guaranteed API stability.

New Features

  • Service provision.
  • full locale support.

API Changes

Role vip and haproxy are merged into service.

#------------------------------------------------------------------------------
# SERVICE PROVISION
#------------------------------------------------------------------------------
pg_weight: 100              # default load balance weight (instance level)

# - service - #
pg_services:                                  # how to expose postgres service in cluster?
  # primary service will route {ip|name}:5433 to primary pgbouncer (5433->6432 rw)
  - name: primary           # service name {{ pg_cluster }}_primary
    src_ip: "*"
    src_port: 5433
    dst_port: pgbouncer     # 5433 route to pgbouncer
    check_url: /primary     # primary health check, success when instance is primary
    selector: "[]"          # select all instance as primary service candidate

  # replica service will route {ip|name}:5434 to replica pgbouncer (5434->6432 ro)
  - name: replica           # service name {{ pg_cluster }}_replica
    src_ip: "*"
    src_port: 5434
    dst_port: pgbouncer
    check_url: /read-only   # read-only health check. (including primary)
    selector: "[]"          # select all instance as replica service candidate
    selector_backup: "[? pg_role == `primary`]"   # primary are used as backup server in replica service

  # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
  - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
    src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
    src_port: 5436          # bind port, mandatory
    dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
    check_method: http      # health check method: only http is available for now
    check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
    check_url: /primary     # health check url path, / as default
    check_code: 200         # health check http code, 200 as default
    selector: "[]"          # instance selector
    haproxy:                # haproxy specific fields
      maxconn: 3000         # default front-end connection
      balance: roundrobin   # load balance algorithm (roundrobin by default)
      default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

  # offline service will route {ip|name}:5438 to offline postgres (5438->5432 offline)
  - name: offline           # service name {{ pg_cluster }}_replica
    src_ip: "*"
    src_port: 5438
    dst_port: postgres
    check_url: /replica     # offline MUST be a replica
    selector: "[? pg_role == `offline` || pg_offline_query ]"         # instances with pg_role == 'offline' or instance marked with 'pg_offline_query == true'
    selector_backup: "[? pg_role == `replica` && !pg_offline_query]"  # replica are used as backup server in offline service

pg_services_extra: []        # extra services to be added

# - haproxy - #
haproxy_enabled: true                         # enable haproxy among every cluster members
haproxy_reload: true                          # reload haproxy after config
haproxy_policy: roundrobin                    # roundrobin, leastconn
haproxy_admin_auth_enabled: false             # enable authentication for haproxy admin?
haproxy_admin_username: admin                 # default haproxy admin username
haproxy_admin_password: admin                 # default haproxy admin password
haproxy_exporter_port: 9101                   # default admin/exporter port
haproxy_client_timeout: 3h                    # client side connection timeout
haproxy_server_timeout: 3h                    # server side connection timeout

# - vip - #
vip_mode: none                                # none | l2 | l4
vip_reload: true                              # whether reload service after config
# vip_address: 127.0.0.1                      # virtual ip address ip (l2 or l4)
# vip_cidrmask: 24                            # virtual ip address cidr mask (l2 only)
# vip_interface: eth0                         # virtual ip network interface (l2 only)

New Options

# - localization - #
pg_encoding: UTF8                             # default to UTF8
pg_locale: C                                  # default to C
pg_lc_collate: C                              # default to C
pg_lc_ctype: en_US.UTF8                       # default to en_US.UTF8

pg_reload: true                               # reload postgres after hba changes
vip_mode: none                                # none | l2 | l4
vip_reload: true                              # whether reload service after config

Remove Options

haproxy_check_port                            # covered by service options
haproxy_primary_port
haproxy_replica_port
haproxy_backend_port
haproxy_weight
haproxy_weight_fallback
vip_enabled                                   # replace by vip_mode

Service

pg_services and pg_services_extra Defines the services in cluster:

A service has some mandatory fields:

  • name: service’s name
  • src_port: which port to listen and expose service?
  • selector: which instances belonging to this service?
  # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
  - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
    src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
    src_port: 5436          # bind port, mandatory
    dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
    check_method: http      # health check method: only http is available for now
    check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
    check_url: /primary     # health check url path, / as default
    check_code: 200         # health check http code, 200 as default
    selector: "[]"          # instance selector
    haproxy:                # haproxy specific fields
      maxconn: 3000         # default front-end connection
      balance: roundrobin   # load balance algorithm (roundrobin by default)
      default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

Database

Add additional locale support: lc_ctype and lc_collate.

It’s mainly because of pg_trgm ’s weird behavior on i18n characters.

pg_databases:
  - name: meta                      # name is the only required field for a database
    # owner: postgres                 # optional, database owner
    # template: template1             # optional, template1 by default
    # encoding: UTF8                # optional, UTF8 by default , must same as template database, leave blank to set to db default
    # locale: C                     # optional, C by default , must same as template database, leave blank to set to db default
    # lc_collate: C                 # optional, C by default , must same as template database, leave blank to set to db default
    # lc_ctype: C                   # optional, C by default , must same as template database, leave blank to set to db default
    allowconn: true                 # optional, true by default, false disable connect at all
    revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    # tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    extensions:                     # optional, extension name and where to create
      - {name: postgis, schema: public}
    parameters:                     # optional, extra parameters with ALTER DATABASE
      enable_partitionwise_join: true
    pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    comment: pigsty meta database   # optional, comment string for database

v0.7.0 Release Note

v0.7 Monitor Only Deployment
By Ruohang Feng（@Vonng） |

v0.7.0

Overview

  • Monitor Only Deployment

    • Now you can monitor existing postgres clusters without Pigsty provisioning solution.
    • Intergration with other provisioning solution is available and under further test.

  • Database/User Management

    • Update user/database definition schema to cover more usecases.
    • Add pgsql-createdb.yml and pgsql-createuser.yml to mange user/db on running clusters.

Features

Bug Fix

API Changes

New Options

prometheus_sd_target: batch                   # batch|single
exporter_install: none                        # none|yum|binary
exporter_repo_url: ''                         # add to yum repo if set
node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'                          # default opts for node_exporter
pg_exporter_url: ''                           # optional, overwrite default pg_exporter target
pgbouncer_exporter_url: ''                    # optional, overwrite default pgbouncer_expoter target

Remove Options

exporter_binary_install: false                 # covered by exporter_install

Structure Changes

pg_default_roles                               # refer to pg_users
pg_users                                       # refer to pg_users
pg_databases                                   # refer to pg_databases

Rename Options

pg_default_privilegs -> pg_default_privileges  # fix typo

Enhancement

Monitoring Provisioning Enhancement

Haproxy Enhancement

Security Enhancement

Software Update

  • Upgrade to PG 13.2 #6

  • Prometheus 2.25 / Grafana 7.4 / Consul 1.9.3 / Node Exporter 1.1 / PG Exporter 0.3.2

API Change

New Config Entries

service_registry: consul                      # none | consul | etcd | both
prometheus_options: '--storage.tsdb.retention=30d'  # prometheus cli opts
prometheus_sd_method: consul                  # Prometheus service discovery method：static|consul
prometheus_sd_interval: 2s                    # Prometheus service discovery refresh interval
pg_offline_query: false                       # set to true to allow offline queries on this instance
node_exporter_enabled: true                   # enabling Node Exporter
pg_exporter_enabled: true                     # enabling PG Exporter
pgbouncer_exporter_enabled: true              # enabling Pgbouncer Exporter
export_binary_install: false                  # install Node/PG Exporter via copy binary
dcs_disable_purge: false                      # force dcs_exists_action = abort to avoid dcs purge
pg_disable_purge: false                       # force pg_exists_action = abort to avoid pg purge
haproxy_weight: 100                           # relative lb weight for backend instance
haproxy_weight_fallback: 1                    # primary server weight in replica service group

Obsolete Config Entries

prometheus_metrics_path                       # duplicate with exporter_metrics_path 
prometheus_retention                          # covered by `prometheus_options`

Database Definition

Database provisioning interface enhancement #33

Old Schema

pg_databases:                       # create a business database 'meta'
  - name: meta
    schemas: [meta]                 # create extra schema named 'meta'
    extensions: [{name: postgis}]   # create extra extension postgis
    parameters:                     # overwrite database meta's default search_path
      search_path: public, monitor

New Schema

pg_databases:
  - name: meta                      # name is the only required field for a database
    owner: postgres                 # optional, database owner
    template: template1             # optional, template1 by default
    encoding: UTF8                  # optional, UTF8 by default
    locale: C                       # optional, C by default
    allowconn: true                 # optional, true by default, false disable connect at all
    revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    extensions:                     # optional, extension name and where to create
      - {name: postgis, schema: public}
    parameters:                     # optional, extra parameters with ALTER DATABASE
      enable_partitionwise_join: true
    pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    comment: pigsty meta database   # optional, comment string for database

Changes

  • Add new options: template , encoding, locale, allowconn, tablespace, connlimit
  • Add new option revokeconn, which revoke connect privileges from public for this database
  • Add comment field for database

Apply Changes

You can create new database on running postgres clusters with pgsql-createdb.yml playbook.

  1. Define your new database in config files
  2. Pass new database.name with option pg_database to playbook.
./pgsql-createdb.yml -e pg_database=<your_new_database_name>

User Definition

User provisioning interface enhancement #34

Old Schema

pg_users:
  - username: test                  # example production user have read-write access
    password: test                  # example user's password
    options: LOGIN                  # extra options
    groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
    comment: default test user for production usage
    pgbouncer: true                 # add to pgbouncer

New Schema

pg_users:
  # complete example of user/role definition for production user
  - name: dbuser_meta               # example production user have read-write access
    password: DBUser.Meta           # example user's password, can be encrypted
    login: true                     # can login, true by default (should be false for role)
    superuser: false                # is superuser? false by default
    createdb: false                 # can create database? false by default
    createrole: false               # can create role? false by default
    inherit: true                   # can this role use inherited privileges?
    replication: false              # can this role do replication? false by default
    bypassrls: false                # can this role bypass row level security? false by default
    connlimit: -1                   # connection limit, -1 disable limit
    expire_at: '2030-12-31'         # 'timestamp' when this role is expired
    expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
    roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly
    pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
    parameters:                     # user's default search path
      search_path: public
    comment: test user

Changes

  • username field rename to name
  • groups field rename to roles
  • options now split into separated configration entries: login, superuser, createdb, createrole, inherit, replication,bypassrls,connlimit
  • expire_at and expire_in options
  • pgbouncer option for user is now false by default

Apply Changes

You can create new users on running postgres clusters with pgsql-createuser.yml playbook.

  1. Define your new users in config files (pg_users)
  2. Pass new user.name with option pg_user to playbook.
./pgsql-createuser.yml -e pg_user=<your_new_user_name>

v0.6.0 Release Note

v0.6 Provisioning Enhancement
By Ruohang Feng（@Vonng） |

v0.6.0

Bug Fix

Enhancement

Monitoring Provisioning Enhancement

Haproxy Enhancement

Security Enhancement

Software Update

  • Upgrade to PG 13.2 #6

  • Prometheus 2.25 / Grafana 7.4 / Consul 1.9.3 / Node Exporter 1.1 / PG Exporter 0.3.2

API Change

New Config Entries

service_registry: consul                      # none | consul | etcd | both
prometheus_options: '--storage.tsdb.retention=30d'  # prometheus cli opts
prometheus_sd_method: consul                  # Prometheus service discovery method：static|consul
prometheus_sd_interval: 2s                    # Prometheus service discovery refresh interval
pg_offline_query: false                       # set to true to allow offline queries on this instance
node_exporter_enabled: true                   # enabling Node Exporter
pg_exporter_enabled: true                     # enabling PG Exporter
pgbouncer_exporter_enabled: true              # enabling Pgbouncer Exporter
export_binary_install: false                  # install Node/PG Exporter via copy binary
dcs_disable_purge: false                      # force dcs_exists_action = abort to avoid dcs purge
pg_disable_purge: false                       # force pg_exists_action = abort to avoid pg purge
haproxy_weight: 100                           # relative lb weight for backend instance
haproxy_weight_fallback: 1                    # primary server weight in replica service group

Obsolete Config Entries

prometheus_metrics_path                       # duplicate with exporter_metrics_path 
prometheus_retention                          # covered by `prometheus_options`

v0.5.0 Release Note

Pigsty v0.5.0 has significant improvement on database templating
By Ruohang Feng（@Vonng） |

v0.5.0

Pigsty now have an Official Site 🎉 !

New Features

  • Add Database Provision Template
  • Add Init Template
  • Add Business Init Template
  • Refactor HBA Rules variables
  • Fix dashboards bugs.
  • Move pg-cluster-replication to default dashboards
  • Use ZJU PostgreSQL mirror as default to accelerate repo build phase.
  • Move documentation to official site: https://pigsty.cc
  • Download newly created offline installation packages: pkg.tgz (v0.5)

Database Provision Template

Now you can customize your database content with pigsty !

pg_users:
  - username: test
    password: test
    comment: default test user
    groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
pg_databases:                       # create a business database 'test'
  - name: test
    extensions: [{name: postgis}]   # create extra extension postgis
    parameters:                     # overwrite database meta's default search_path
      search_path: public,monitor

pg-init-template.sql wil be used as default template1 database init script pg-init-business.sql will be used as default business database init script

you can customize default role system, schemas, extensions, privileges with variables now:

# - system roles - #
pg_replication_username: replicator           # system replication user
pg_replication_password: DBUser.Replicator    # system replication password
pg_monitor_username: dbuser_monitor           # system monitor user
pg_monitor_password: DBUser.Monitor           # system monitor password
pg_admin_username: dbuser_admin               # system admin user
pg_admin_password: DBUser.Admin               # system admin password

# - default roles - #
pg_default_roles:
  - username: dbrole_readonly                 # sample user:
    options: NOLOGIN                          # role can not login
    comment: role for readonly access         # comment string

  - username: dbrole_readwrite                # sample user: one object for each user
    options: NOLOGIN
    comment: role for read-write access
    groups: [ dbrole_readonly ]               # read-write includes read-only access

  - username: dbrole_admin                    # sample user: one object for each user
    options: NOLOGIN BYPASSRLS                # admin can bypass row level security
    comment: role for object creation
    groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]

  # NOTE: replicator, monitor, admin password are overwritten by separated config entry
  - username: postgres                        # reset dbsu password to NULL (if dbsu is not postgres)
    options: SUPERUSER LOGIN
    comment: system superuser

  - username: replicator
    options: REPLICATION LOGIN
    groups: [pg_monitor, dbrole_readonly]
    comment: system replicator

  - username: dbuser_monitor
    options: LOGIN CONNECTION LIMIT 10
    comment: system monitor user
    groups: [pg_monitor, dbrole_readonly]

  - username: dbuser_admin
    options: LOGIN BYPASSRLS
    comment: system admin user
    groups: [dbrole_admin]

  - username: dbuser_stats
    password: DBUser.Stats
    options: LOGIN
    comment: business read-only user for statistics
    groups: [dbrole_readonly]


# object created by dbsu and admin will have their privileges properly set
pg_default_privilegs:
  - GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly
  - GRANT SELECT                        ON TABLES    TO dbrole_readonly
  - GRANT SELECT                        ON SEQUENCES TO dbrole_readonly
  - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly
  - GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite
  - GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin
  - GRANT CREATE                        ON SCHEMAS   TO dbrole_admin
  - GRANT USAGE                         ON TYPES     TO dbrole_admin

# schemas
pg_default_schemas: [monitor]

# extension
pg_default_extensions:
  - { name: 'pg_stat_statements',  schema: 'monitor' }
  - { name: 'pgstattuple',         schema: 'monitor' }
  - { name: 'pg_qualstats',        schema: 'monitor' }
  - { name: 'pg_buffercache',      schema: 'monitor' }
  - { name: 'pageinspect',         schema: 'monitor' }
  - { name: 'pg_prewarm',          schema: 'monitor' }
  - { name: 'pg_visibility',       schema: 'monitor' }
  - { name: 'pg_freespacemap',     schema: 'monitor' }
  - { name: 'pg_repack',           schema: 'monitor' }
  - name: postgres_fdw
  - name: file_fdw
  - name: btree_gist
  - name: btree_gin
  - name: pg_trgm
  - name: intagg
  - name: intarray

# postgres host-based authentication rules
pg_hba_rules:
  - title: allow meta node password access
    role: common
    rules:
      - host    all     all                         10.10.10.10/32      md5

  - title: allow intranet admin password access
    role: common
    rules:
      - host    all     +dbrole_admin               10.0.0.0/8          md5
      - host    all     +dbrole_admin               172.16.0.0/12       md5
      - host    all     +dbrole_admin               192.168.0.0/16      md5

  - title: allow intranet password access
    role: common
    rules:
      - host    all             all                 10.0.0.0/8          md5
      - host    all             all                 172.16.0.0/12       md5
      - host    all             all                 192.168.0.0/16      md5

  - title: allow local read-write access (local production user via pgbouncer)
    role: common
    rules:
      - local   all     +dbrole_readwrite                               md5
      - host    all     +dbrole_readwrite           127.0.0.1/32        md5

  - title: allow read-only user (stats, personal) password directly access
    role: replica
    rules:
      - local   all     +dbrole_readonly                               md5
      - host    all     +dbrole_readonly           127.0.0.1/32        md5
pg_hba_rules_extra: []

# pgbouncer host-based authentication rules
pgbouncer_hba_rules:
  - title: local password access
    role: common
    rules:
      - local  all          all                                     md5
      - host   all          all                     127.0.0.1/32    md5

  - title: intranet password access
    role: common
    rules:
      - host   all          all                     10.0.0.0/8      md5
      - host   all          all                     172.16.0.0/12   md5
      - host   all          all                     192.168.0.0/16  md5
pgbouncer_hba_rules_extra: []

v0.4.0 Release Note

The second public beta (v0.4.0) of pigsty is available now
By Ruohang Feng（@Vonng） |

v0.4.0

The second public beta (v0.4.0) of pigsty is available now ! 🎉

Monitoring System

Skim version of monitoring system consist of 10 essential dashboards:

  • PG Overview
  • PG Cluster
  • PG Service
  • PG Instance
  • PG Database
  • PG Query
  • PG Table
  • PG Table Catalog
  • PG Table Detail
  • Node

Software upgrade

  • Upgrade to PostgreSQL 13.1, Patroni 2.0.1-4, add citus to repo.
  • Upgrade to pg_exporter 0.3.1
  • Upgrade to Grafana 7.3, Ton’s of compatibility work
  • Upgrade to prometheus 2.23, with new UI as default
  • Upgrade to consul 1.9

Misc

  • Update prometheus alert rules
  • Fix alertmanager info links
  • Fix bugs and typos.
  • add a simple backup script

Offline Installation

  • pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)

v0.3.0 Release Note

Pigsty v0.3.0 First Public Beta now available!
By Ruohang Feng（@Vonng） |

v0.3.0

The first public beta (v0.3.0) of pigsty is available now ! 🎉

Monitoring System

Skim version of monitoring system consist of 8 essential dashboards:

  • PG Overview
  • PG Cluster
  • PG Service
  • PG Instance
  • PG Database
  • PG Table Overview
  • PG Table Catalog
  • Node

Database Cluster Provision

  • All config files are merged into one file: conf/all.yml by default
  • Use infra.yml to provision meta node(s) and infrastructure
  • Use initdb.yml to provision database clusters
  • Use ins-add.yml to add new instance to database cluster
  • Use ins-del.yml to remove instance from database cluster

Offline Installation

  • pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)