v3.4: MySQL Wire-Compatibility and Improvements

By Vonng（@Vonng | Release Notes | 2025-03-31

Tags:

After a month of intensive development, Pigsty v3.4 is officially released! This release brings support for a new PostgreSQL kernel and significant architectural optimizations, addressing several key concerns from customers and users:

Support for MySQL-compatible openHalo kernel
Physical backup and PITR recovery between different clusters
Monitoring metrics and dashboards for pgBackRest backup component
Simplified Certbot certificate application process
Best practices for localization sorting rules and character sets
Oracle-compatible IvorySQL now available across all platforms
Graph database extension AGE now available across all platforms

GitHub Release: v3.4.0

GitHub Release: v3.4.1

Let’s dive into the key changes introduced in Pigsty v3.4.

MySQL Compatibility

In Pigsty v3.4.1, we introduced support for openHalo, which provides MySQL wire protocol compatibility based on PostgreSQL 14.10.

This means MySQL users can seamlessly migrate to PostgreSQL without code changes, allowing both PG and MySQL clients to connect to the same database.

Currently, Pigsty provides RPM packages for openHalo and allows users to choose openHalo as the PostgreSQL kernel during installation, creating a complete RDS service.

./configure -c mysql   # Use MySQL-compatible openHalo configuration template

Automated Cert Management

Recently, many users have adopted Pigsty for self-hosting applications like Dify, Odoo, and Supabase. Some feedback indicated that the certificate application process seemed cumbersome, requiring manual certbot calls. Could this be automated?

In this version, Pigsty has enhanced Nginx configuration. Now, if users define the certbot field for an Nginx Server, they can use the make cert command to automatically complete certificate application and deployment with a single click, requiring no additional configuration or commands.

Therefore, in application templates like Dify, Odoo, and Supabase, this new feature is utilized. After installation, make cert will automatically update or apply for required certificates. If you don’t even want to type this command, simply configure certbot_sign = true to automatically request certificates during installation.

Additionally, v3.4 offers more Nginx configuration options. For example, you can use config to inject configuration into nginx, and enforce to force HTTPS redirection. When self-hosting a website, you can achieve flexible customization in most scenarios without touching traditional Nginx configurations.

Here’s a configuration example used by the Pigsty Chinese website. With this configuration, I can self-host a Pigsty documentation/software repository site anywhere in the world within minutes.

    infra_portal:                     # domain names and upstream servers
      home         : { domain: home.pigsty.cc                                                 ,certbot: pigsty.demo }
      grafana      : { domain: demo.pigsty.cc ,endpoint: "${admin_ip}:3000", websocket: true  ,certbot: pigsty.demo }
      prometheus   : { domain: p.pigsty.cc    ,endpoint: "${admin_ip}:9090"                   ,certbot: pigsty.demo }
      alertmanager : { domain: a.pigsty.cc    ,endpoint: "${admin_ip}:9093"                   ,certbot: pigsty.demo }
      blackbox     : { endpoint: "${admin_ip}:9115"                                                               }
      loki         : { endpoint: "${admin_ip}:3100"                                                               }
      postgrest    : { domain: api.pigsty.cc  ,endpoint: "127.0.0.1:8884"                                         }
      pgadmin      : { domain: adm.pigsty.cc  ,endpoint: "127.0.0.1:8885"                                         }
      pgweb        : { domain: cli.pigsty.cc  ,endpoint: "127.0.0.1:8886"                                         }
      bytebase     : { domain: ddl.pigsty.cc  ,endpoint: "127.0.0.1:8887"                                         }
      jupyter      : { domain: lab.pigsty.cc  ,endpoint: "127.0.0.1:8888"   ,websocket: true                      }
      gitea        : { domain: git.pigsty.cc  ,endpoint: "127.0.0.1:8889"                     ,certbot: pigsty.cc }
      wiki         : { domain: wiki.pigsty.cc ,endpoint: "127.0.0.1:9002"                     ,certbot: pigsty.cc }
      noco         : { domain: noco.pigsty.cc ,endpoint: "127.0.0.1:9003"                     ,certbot: pigsty.cc }
      supa         : { domain: supa.pigsty.cc ,endpoint: "10.2.82.163:8000" ,websocket: true  ,certbot: pigsty.cc }
      dify         : { domain: dify.pigsty.cc ,endpoint: "10.2.82.163:8001" ,websocket: true  ,certbot: pigsty.cc }
      odoo         : { domain: odoo.pigsty.cc ,endpoint: "127.0.0.1:8069"   ,websocket: true  ,certbot: pigsty.cc }
      mm           : { domain: mm.pigsty.cc   ,endpoint: "10.2.82.163:8065" ,websocket: true                      }
      web.io:
        domain: en.pigsty.cc
        path: "/www/web.io"
        certbot: pigsty.doc
        enforce_https: true
        config: |
          # rewrite /zh/ to /
              location /zh/ {
                  rewrite ^/zh/(.*)$ /$1 permanent;
              }          
      web.cc:
        domain: pigsty.cc
        path: "/www/web.cc"
        domains: [ zh.pigsty.cc ]
        certbot: pigsty.doc
        config: |
          # rewrite /zh/ to /
              location /zh/ {
                  rewrite ^/zh/(.*)$ /$1 permanent;
              }          
      repo:
        domain: pro.pigsty.cc
        path: "/www/repo"
        index: true
        certbot: pigsty.doc

Locale & Collation

Many programmers are not very familiar with Locale/Collation rules, but they are actually quite important. Improper Collation configuration can not only cause several times performance degradation but also lead to data inconsistency or even data loss. Yes, that’s right - indexes are closely related to sorting rules, and Collation is not an insignificant configuration.

Regarding this topic, I strongly recommend interested readers to check out the article Localization Sorting Rules in PostgreSQL, as well as Jeremy Schneider’s presentation at PGCon.Dev 2024: Collations from A to Z, Putting words in order without losing your mind or your data

As a summary, the best practice is to always use C or C.UTF-8 as the Locale sorting rule. C has the best compatibility - it works on all systems, but it lacks Unicode knowledge for character sets - features like case conversion don’t work for characters beyond ASCII! C.UTF-8 implements Unicode semantics on top of C, which is more intuitive for users, but not all systems support it by default. Fortunately, PostgreSQL 17 introduces a new feature with built-in support for both Collations, no longer relying on the operating system’s libc.

Therefore, Pigsty v3.4 has promptly followed and reflected this best practice. First, all Locale-related parameters now default to C (mainly pg_lc_ctypes changed from en_US.UTF-8 to C), ensuring it works on any system! Then, during automatic configuration, if PG version >= 17 or the OS explicitly supports C.utf8 (counterexamples: default Debian, EL7, MacOS), the Locale will be configured as C.UTF-8 for better Unicode semantics.

pg_locale: C.UTF-8                # overwrite default C local
pg_lc_collate: C.UTF-8            # overwrite default C lc_collate
pg_lc_ctype: C.UTF-8              # overwrite default C lc_ctype

Unless your database works intensively with specific language sorting scenarios, these defaults represent the best practice. You can use PostgreSQL’s COLLATION syntax to easily specify and configure specific sorting rules for queries/indexes/columns. PG + ICU supports a total of 841 sorting rules, satisfying even the most peculiar sorting preferences.

Point-in-Time Recovery Improvements

Point-in-Time Recovery (PITR) is a core feature of relational databases. Previously, Pigsty used pg-pitr to assist users with semi-automatic PITR.

In Pigsty v3.4, PITR support has been significantly improved. For example, you can now easily select any backup from a centralized backup repository for recovery.

You can achieve this in a more Ansible-native way. When you define the pg_pitr parameter on a PG cluster, Pigsty automatically generates the /pg/bin/pg-restore command and /pg/conf/pitr.conf configuration file for recovery.

pg_pitr:
  cluster: pg-test              # Specify the cluster name to recover
  type:   'default'             # default, immediate, time, lsn, xid, name, backup
  path:   '/pg/data'            # restore to which path? /pg/data by default
  time:   '2022-01-01T00:00:00' # if type = time
  lsn:    '0/1000000'           # if type = lsn
  xid:    '123456'              # if type = xid
  name:   'restore_point'       # if type = name
  backup: '20221108-105325F'    # if type = set
  action: 'promote'             # promote, pause, shutdown
  exclusive: false              # Stop just BEFORE the recovery target (xid,time,lsn) is reached?
  db_exclude: []                # Restore excluding the specified databases
  db_include: []                # Restore only specified databases
  link_map: {}                  # Restore with tablespace link map
  process: max                  # Number of parallel processes to use for restore (default to CPU count)

When executing the pg-restore command, Pigsty automatically pauses the Patroni cluster, shuts down PG, performs incremental PITR in place, and starts PG after reaching the specified point. A notable improvement is that if you use a centralized backup repository, you can use backups from other clusters to overwrite the current cluster.

Additionally, for backup monitoring, v3.4 introduces a new component: pgbackrest_exporter, for collecting backup monitoring metrics. The PGSQL PITR monitoring dashboard will also display the current backup status. While users could previously query PGBackRest backup status directly through PGCAT Instance, it only showed the current state without historical records. This improvement is undoubtedly very helpful for analyzing backup status.

Extension Updates

In the past few versions, Pigsty has mainly focused on the PostgreSQL extension ecosystem. During a year of continuous expansion, we have collected almost all significant extensions in the PG ecosystem - totaling 404.

However, the phase of rapid extension expansion has basically ended. Therefore, in recent versions, I have refocused on Pigsty’s architecture and infrastructure, with extensions being consolidated.

So v3.4 only adds one new extension pgspider_ext, which implements multi-data source queries using various FDWs. However, 28 of the extensions maintained by Pigsty have been updated to their latest versions. Additionally, we carefully reviewed existing extensions and fixed version and bug issues for several extensions.

Most notably, the graph database extension Apache AGE seems to have lost its maintainer, leaving it in an unmaintained state. As a distribution author, I can only do my best to “keep it alive.” So this time, I recompiled AGE 1.5.0 extensions for versions 13-17 based on Debian patches, finally filling the gap of missing EL RPM packages.

Exotic Kernels

In Pigsty v3.4, we’ve updated support for the latest versions of PolarDB, IvorySQL, and Babelfish.

It’s worth celebrating that after PolarDB, IvorySQL has become the second PostgreSQL kernel available across all ten Linux distributions supported by Pigsty. The only remaining issue is that WiltonDB doesn’t support Debian.

In this update, we collaborated with the IvorySQL team to improve Pigsty’s integration with IvorySQL. Apart from extension plugins, IvorySQL 4.4’s experience is basically consistent with PostgreSQL 17.4.

Using IvorySQL (Oracle compatibility mode) only requires modifying these four parameters:

pg_mode: ivory                                                 # Use IvorySQL compatibility mode
pg_packages: [ ivorysql, pgsql-common ]                        # Install IvorySQL packages
pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain'  # Load Oracle compatibility extensions
repo_extra_packages: [ ivorysql ]                              # Download IvorySQL packages

I should also mention PolarDB here - their DEB packaging wasn’t done carefully, with the Debian package having an incorrect libicu version dependency that made it impossible to install, suggesting it wasn’t tested at all. Fortunately, their response speed was quite fast. I had already modified the DEB package to fix it, but they quickly released a new fixed version after I reported the issue.

Meanwhile, we’ve also updated the Supabase template to the latest version and updated the distributed extension Citus to 13.0.2. In upcoming versions, I’ll focus on OrioleDB, which specializes in OLTP performance, and the OpenHalo kernel, which provides MySQL protocol compatibility.

Infra Enhancements

In v3.4, we’ve updated many Infra software package versions and added several new components:

JuiceFS: Mount S3/MinIO as local file system
Restic: Similar to pgBackRest, but for file system backups
TimescaleDB EventStreamer: For extracting change data streams from TimescaleDB hypertables

I originally planned to release the JuiceFS Beta module in this version, but time was tight, so this feature will be moved to the next version.

However, in Pigsty v3.4, these components are now downloaded by default, so if you want to use them, just install them directly.

Another change is that the following software packages are now added to Pigsty’s default download list:

extra-modules: "docker-ce docker-compose-plugin ferretdb2 duckdb restic juicefs vray grafana-infinity-ds"

Mainly because I found that Docker is still used by many people, not just for running software but especially for running pgAdmin. Since this is the case, I decided to make Docker part of the default download, not worrying about the extra 100MB.

v3.5 Feature Preview

The v3.5 feature planning has begun, currently including the following features:

The pig command line has been saying it would incorporate fine-grained management of Pigsty Playbook, and now is the perfect time. I hope in the next version, users can say goodbye to directly executing Ansible Playbook and manage everything with a Go command line.

Then there’s the configuration wizard and MCP Server. Many users have feedback that Pigsty’s configuration files are somewhat complex, asking if we could create something like a Vibe Config Wizard. I thought about it and technically it’s not too difficult - feed the documentation as context, ask a few questions, and modify the configuration on the fly like Cursor. Of course, I still hope to automatically make some environment-based judgments like the original configure script, so perhaps we need an MCP server to collect environment information and modify configurations.

The third feature, which has been delayed for a while, is Docker images. I plan to create a Debian 12 x86/ARM version of Pigsty Docker in the next version. With the ARM version, Mac laptops can quickly try it out locally.

Finally, there are two new PG kernels: OrioleDB and openHalo. The former just released Beta10 yesterday, and the latter just went open source yesterday. Both just threw out their code without providing DEB/RPM packages, which I’ll need to create myself. But I think these kernels’ features should appeal to many: the former offers PG + extreme OLTP performance + no bloat, while the latter provides PG + MySQL wire protocol compatibility. Barring any surprises, we should see these two new PG kernels in Pigsty in the next version.

That’s all for the new features in Pigsty v3.4. Enjoy using it!

v3.4.1

MySQL wire-compatible PostgreSQL kernel: openHalo support
pgAdmin application template optimization, auto-filling pgpass passwords
Increased PG default max connections to 250, 500, 1000
Updated pg_search to 0.5.13
Updated pig to 0.3.4
Updated pg_exporter to 0.8.1
Removed mysql_fdw extension with dependency errors from EL8

v3.4.0

New Features

Added new pgBackRest backup monitoring metrics and dashboards
Enhanced Nginx server configuration options, with support for automated Certbot issuance
Now prioritizing PostgreSQL’s built-in C/C.UTF-8 locale settings
IvorySQL 4.4 is now fully supported across all platforms (RPM/DEB on x86/ARM)
Added new software packages: Juicefs, Restic, TimescaleDB EventStreamer
The Apache AGE graph database extension now fully supports PostgreSQL 13–17 on EL
Improved the app.yml playbook: launch standard Docker app without extra config
Bump Supabase, Dify, and Odoo app templates, bump to their latest versions
Add electric app template, local-first PostgreSQL Sync Engine

Infra Packages

+restic 0.17.3
+juicefs 1.2.3
+timescaledb-event-streamer 0.12.0
Prometheus 3.2.1
AlertManager 0.28.1
blackbox_exporter 0.26.0
node_exporter 1.9.0
mysqld_exporter 0.17.2
kafka_exporter 1.9.0
redis_exporter 1.69.0
pgbackrest_exporter 0.19.0-2
DuckDB 1.2.1
etcd 3.5.20
FerretDB 2.0.0
tigerbeetle 0.16.31
vector 0.45.0
VictoriaMetrics 1.113.0
VictoriaLogs 1.17.0
rclone 1.69.1
pev2 1.14.0
grafana-victorialogs-ds 0.16.0
grafana-victoriametrics-ds 0.14.0
grafana-infinity-ds 3.0.0

Patroni 4.0.5
PolarDB 15.12.3.0-e1e6d85b
IvorySQL 4.4
pgbackrest 2.54.2
pev2 1.14
WiltonDB 13.17

PostgreSQL Extensions

pgspider_ext 1.3.0 (new extension)
apache age 13–17 el rpm (1.5.0)
timescaledb 2.18.2 → 2.19.0
citus 13.0.1 → 13.0.2
documentdb 1.101-0 → 1.102-0
pg_analytics 0.3.4 → 0.3.7
pg_search 0.15.2 → 0.15.8
pg_ivm 1.9 → 1.10
emaj 4.4.0 → 4.6.0
pgsql_tweaks 0.10.0 → 0.11.0
pgvectorscale 0.4.0 → 0.6.0 (pgrx 0.12.5)
pg_session_jwt 0.1.2 → 0.2.0 (pgrx 0.12.6)
wrappers 0.4.4 → 0.4.5 (pgrx 0.12.9)
pg_parquet 0.2.0 → 0.3.1 (pgrx 0.13.1)
vchord 0.2.1 → 0.2.2 (pgrx 0.13.1)
pg_tle 1.2.0 → 1.5.0
supautils 2.5.0 → 2.6.0
sslutils 1.3 → 1.4
pg_profile 4.7 → 4.8
pg_snakeoil 1.3 → 1.4
pg_jsonschema 0.3.2 → 0.3.3
pg_incremental 1.1.1 → 1.2.0
pg_stat_monitor 2.1.0 → 2.1.1
ddl_historization 0.7 → 0.0.7 (bug fix)
pg_sqlog 3.1.7 → 1.6 (bug fix)
pg_random removed development suffix (bug fix)
asn1oid 1.5 → 1.6
table_log 0.6.1 → 0.6.4

Interface Changes

Added new Docker parameters: docker_data and docker_storage_driver (#521 by @waitingsong)
Added new Infra parameter: alertmanager_port, which lets you specify the AlertManager port
Added new Infra parameter: certbot_sign, apply for cert during nginx init? (false by default)
Added new Infra parameter: certbot_email, specifying the email used when requesting certificates via Certbot
Added new Infra parameter: certbot_options, specifying additional parameters for Certbot
Updated IvorySQL to place its default binary under /usr/ivory-4 starting in IvorySQL 4.4
Changed the default for pg_lc_ctype and other locale-related parameters from en_US.UTF-8 to C
For PostgreSQL 17, if using UTF8 encoding with C or C.UTF-8 locales, PostgreSQL’s built-in localization rules now take priority
configure automatically detects whether C.utf8 is supported by both the PG version and the environment, and adjusts locale-related options accordingly
Set the default IvorySQL binary path to /usr/ivory-4
Updated the default value of pg_packages to pgsql-main patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager
Updated the default value of repo_packages to [node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, extra-modules]
Removed LANG and LC_ALL environment variable settings from /etc/profile.d/node.sh
Now using bento/rockylinux-8 and bento/rockylinux-9 as the Vagrant box images for EL
Added a new alias, extra_modules, which includes additional optional modules
Updated PostgreSQL aliases: postgresql, pgsql-main, pgsql-core, pgsql-full
GitLab repositories are now included among available modules
The Docker module has been merged into the Infra module
The node.yml playbook now includes a node_pip task to configure a pip mirror on each node
The pgsql.yml playbook now includes a pgbackrest_exporter task for collecting backup metrics
The Makefile now allows the use of META/PKG environment variables
Added /pg/spool directory as temporary storage for pgBackRest
Disabled pgBackRest’s link-all option by default
Enabled block-level incremental backups for MinIO repositories by default

Bug Fixes

Fixed the exit status code in pg-backup (#532 by @waitingsong)
In pg-tune-hugepage, restricted PostgreSQL to use only large pages (#527 by @waitingsong)
Fixed logic errors in the pg-role task
Corrected type conversion for hugepage configuration parameters
Fixed default value issues for node_repo_modules in the slim template

Checksums

768bea3bfc5d492f4c033cb019a81d3a  pigsty-v3.4.0.tgz
7c3d47ef488a9c7961ca6579dc9543d6  pigsty-pkg-v3.4.0.d12.aarch64.tgz
b5d76aefb1e1caa7890b3a37f6a14ea5  pigsty-pkg-v3.4.0.d12.x86_64.tgz
42dacf2f544ca9a02148aeea91f3153a  pigsty-pkg-v3.4.0.el8.aarch64.tgz
d0a694f6cd6a7f2111b0971a60c49ad0  pigsty-pkg-v3.4.0.el8.x86_64.tgz
7caa82254c1b0750e89f78a54bf065f8  pigsty-pkg-v3.4.0.el9.aarch64.tgz
8f817e5fad708b20ee217eb2e12b99cb  pigsty-pkg-v3.4.0.el9.x86_64.tgz
8b2fcaa6ef6fd8d2726f6eafbb488aaf  pigsty-pkg-v3.4.0.u22.aarch64.tgz
83291db7871557566ab6524beb792636  pigsty-pkg-v3.4.0.u22.x86_64.tgz
c927238f0343cde82a4a9ab230ecd2ac  pigsty-pkg-v3.4.0.u24.aarch64.tgz
14cbcb90693ed5de8116648a1f2c3e34  pigsty-pkg-v3.4.0.u24.x86_64.tgz

v3.4.1

GitHub Release Page: v3.4.1

Added support for MySQL wire-compatible PostgreSQL kernel on EL systems: openHalo
Added support for OLTP-enhanced PostgreSQL kernel on EL systems: orioledb
Optimized pgAdmin 9.2 application template with automatic server list updates and pgpass password population
Increased PG default max connections to 250, 500, 1000
Removed the mysql_fdw extension with dependency errors from EL8

Infra Updates

pig 0.3.4
etcd 3.5.21
restic 0.18.0
ferretdb 2.1.0
tigerbeetle 0.16.34
pg_exporter 0.8.1
node_exporter 1.9.1
grafana 11.6.0
zfs_exporter 3.8.1
mongodb_exporter 0.44.0
victoriametrics 1.114.0
minio 20250403145628
mcli 20250403170756

Extension Update

Bump pg_search to 0.15.13
Bump citus to 13.0.3
Bump timescaledb to 2.19.1
Bump pgcollection RPM to 1.0.0
Bump pg_vectorize RPM to 0.22.1
Bump pglite_fusion RPM to 0.0.4
Bump aggs_for_vecs RPM to 1.4.0
Bump pg_tracing RPM to 0.1.3
Bump pgmq RPM to 1.5.1

Last modified 2025-04-05: add orioledb desc (13f3e90)

v3.4: MySQL Wire-Compatibility and Improvements

MySQL Compatibility

Automated Cert Management

Locale & Collation

Point-in-Time Recovery Improvements

Extension Updates

Exotic Kernels

Infra Enhancements

v3.5 Feature Preview

v3.4.1

v3.4.0

New Features

Infra Packages

PostgreSQL Related

PostgreSQL Extensions

Interface Changes

Bug Fixes

Checksums

v3.4.1