Administration

• 1: Parameter Tuning
• 2: Maintenance
• 3: Failure SOP
• 4: Data Loss Recovery

bin/pgsql-add   <cls>                   # create pgsql cluster <cls>
bin/pgsql-user  <cls> <username>        # create pg user <username> on <cls>
bin/pgsql-db    <cls> <dbname>          # create pg database <dbname> on <cls>
bin/pgsql-svc   <cls> [...ip]           # reload pg service of cluster <cls>
bin/pgsql-hba   <cls> [...ip]           # reload postgres/pgbouncer HBA rules of cluster <cls>
bin/pgsql-add   <cls> [...ip]           # append replicas for cluster <cls>
bin/pgsql-rm    <cls> [...ip]           # remove replicas from cluster <cls>
bin/pgsql-rm    <cls>                   # remove pgsql cluster <cls>

pg list        <cls>                    # print cluster info
pg edit-config <cls>                    # edit cluster config
pg reload      <cls> [ins]              # reload cluster config
pg restart     <cls> [ins]              # restart pgsql cluster
pg reinit      <cls> [ins]              # reinit cluster members
pg pause       <cls>                    # entering maintenance mode (no auto failover)
pg resume      <cls>                    # exiting maintenance mode
pg switchover  <cls>                    # switchover on cluster <cls>
pg failover    <cls>                    # failover on cluster <cls>

pb info                                 # print pgbackrest repo info
pg-backup                               # make a backup, incr, or full backup if necessary
pg-backup full                          # make a full backup
pg-backup diff                          # make a differential backup
pg-backup incr                          # make a incremental backup
./pgsql-pitr.yml -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00" }}'
./pgsql-pitr.yml -e '{"pg_pitr": { "name": "shit_incoming" }}'
./pgsql-pitr.yml -e '{"pg_pitr": { "xid": "250000", exclusive: true }}'
./pgsql-pitr.yml -e '{"pg_pitr": { "lsn": "0/4001C80", timeline: "1" }}'

systemctl stop patroni                  # start stop restart reload
systemctl stop pgbouncer                # start stop restart reload
systemctl stop pg_exporter              # start stop restart reload
systemctl stop pgbouncer_exporter       # start stop restart reload
systemctl stop node_exporter            # start stop restart
systemctl stop haproxy                  # start stop restart reload
systemctl stop vip-manager              # start stop restart reload
systemctl stop postgres                 # only when patroni_mode == 'remove'

bin/node-add <cls>                # init nodes for cluster <cls>           # ./node.yml  -l <cls>
bin/pgsql-add <cls>               # init pgsql instances of cluster <cls>  # ./pgsql.yml -l <cls>

bin/pgsql-user <cls> <username>   # ./pgsql-user.yml -l <cls> -e username=<username>

bin/pgsql-db <cls> <dbname>       # ./pgsql-db.yml -l <cls> -e dbname=<dbname>

bin/pgsql-svc <cls>               # pgsql.yml -l <cls> -t pg_service -e pg_reload=true
bin/pgsql-svc <cls> [ip...]       # pgsql.yml -l ip... -t pg_service -e pg_reload=true

bin/pgsql-hba <cls>               # pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
bin/pgsql-hba <cls> [ip...]       # pgsql.yml -l ip... -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true

pg edit-config <cls>              # interactive config a cluster with patronictl

bin/node-add <ip>                 # init node <ip> for the new replica
bin/pgsql-add <cls> <ip>          # init pgsql instances on <ip> for cluster <cls>

bin/pgsql-rm <cls> <ip...>        # ./pgsql-rm.yml -l <ip>

bin/pgsql-rm <cls>                # ./pgsql-rm.yml -l <cls>

pg switchover <cls>   # interactive mode, you can skip that with following options
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test

pg-backup                         # make a postgres base backup
pg-backup full                    # make a full backup
pg-backup diff                    # make a differential backup
pg-backup incr                    # make a incremental backup
pb info                           # check backup information

./pgsql-pitr.yml -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00" }}'
./pgsql-pitr.yml -e '{"pg_pitr": { "name": "shit_incoming" }}'
./pgsql-pitr.yml -e '{"pg_pitr": { "xid": "250000", exclusive: true }}'
./pgsql-pitr.yml -e '{"pg_pitr": { "lsn": "0/4001C80", timeline: "1" }}'

ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*"  # install some packages

./pgsql.yml -t pg_ext     # install extensions

ansible <cls> -b -a "yum upgrade/downgrade -y <pkg>"    # upgrade/downgrade packages
pg restart --force <cls>                                # restart cluster

./pgsql.yml -t pg_pkg -e pg_version=15                         # install packages for pg 15
sudo su - postgres; mkdir -p /data/postgres/pg-meta-15/data/   # prepare directories for 15
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ -v -c # preflight
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ --link -j8 -v -c
rm -rf /usr/pgsql; ln -s /usr/pgsql-15 /usr/pgsql;             # fix binary links
mv /data/postgres/pg-meta-14 /data/postgres/pg-meta-15         # rename data directory
rm -rf /pg; ln -s /data/postgres/pg-meta-15 /pg                # fix data dir links

1 - Parameter Tuning

Pigsty provides four scenario-specific parameter templates by default, which can be specified and used via the pg_conf parameter.

• tiny.yml: Optimized for small nodes, virtual machines, and small demos (1-8 cores, 1-16GB)
• oltp.yml: Optimized for OLTP workloads and latency-sensitive applications (4C8GB+) (default template)
• olap.yml: Optimized for OLAP workloads and throughput (4C8G+)
• crit.yml: Optimized for data consistency and critical applications (4C8G+)

Pigsty adopts different parameter optimization strategies for these four default scenarios, as shown below:

Memory Parameters

Pigsty automatically detects system memory size and uses it as the basis for setting maximum connections and memory-related parameters.

• pg_max_conn: PostgreSQL maximum connections, auto will use recommended values for different scenarios
• pg_shared_buffer_ratio: Shared buffer memory ratio, defaults to 0.25

By default, Pigsty uses 25% of memory as PostgreSQL shared buffers, leaving the remaining 75% for the operating system cache.

By default, if users don’t set a pg_max_conn maximum connection count, Pigsty will use default values according to these rules:

• oltp: 500 (pgbouncer) / 1000 (postgres)
• crit: 500 (pgbouncer) / 1000 (postgres)
• tiny: 300
• olap: 300

For OLTP and CRIT templates, if services point directly to the PostgreSQL database instead of the pgbouncer connection pool, maximum connections double to 1000.

After determining maximum connections, work_mem is calculated based on shared memory amount / maximum connections, constrained within a 64MB ~ 1GB range.

{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% if pg_default_service_dest|default('postgres') == 'pgbouncer' %}{% set pg_max_connections = 500 %}{% else %}{% set pg_max_connections = 1000 %}{% endif %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.25)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 1024])|min|int %}

CPU Parameters

In PostgreSQL, there are 4 important parameters related to parallel queries. Pigsty automatically optimizes these parameters based on the current system’s CPU core count. Across all strategies, the total parallel process count (total budget) is typically set to CPU cores + 8, with a minimum of 16, thus reserving sufficient background worker capacity for logical replication and extensions. OLAP and TINY templates vary slightly based on scenario.

Note that CRIT and TINY templates disable parallel queries directly by setting max_parallel_workers_per_gather = 0. Users can set this parameter as needed to enable parallel queries.

Both OLTP and CRIT templates set the following additional parameters, doubling parallel query costs to reduce the tendency to use parallel queries:

parallel_setup_cost: 2000           # double from 100 to increase parallel cost
parallel_tuple_cost: 0.2            # double from 0.1 to increase parallel cost
min_parallel_table_scan_size: 16MB  # double from 8MB to increase parallel cost
min_parallel_index_scan_size: 1024  # double from 512 to increase parallel cost

Note that max_worker_processes parameter adjustments only take effect after a restart. Additionally, when a replica’s configuration value for this parameter exceeds the primary’s, the replica cannot start. This parameter must be adjusted through Patroni configuration management. The parameter is managed by Patroni to ensure consistent primary-replica configuration and prevent new replicas from failing to start during failover.

Storage Parameters

Pigsty automatically detects the total disk space where the /data/postgres main data directory resides and uses it as the basis for specifying the following parameters:

min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB                  # 1/20 disk size, max 200GB
max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB             # 2/10 disk size, max 2000GB
max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB   # 3/10 disk size, max 3000GB
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB               # 1/20 of disk size, max 200GB

• temp_file_limit defaults to 5% of disk space, capped at 200GB maximum.
• min_wal_size defaults to 5% of disk space, capped at 200GB maximum.
• max_wal_size defaults to 20% of disk space, capped at 2TB maximum.
• max_slot_wal_keep_size defaults to 30% of disk space, capped at 3TB maximum.

As a special case, the OLAP template allows 20% for temp_file_limit, capped at 2TB maximum.

2 - Maintenance

Ensuring healthy and stable operation of Pigsty and PostgreSQL clusters requires routine maintenance work.

Regular Monitoring Review

Pigsty provides an out-of-the-box monitoring platform. We recommend reviewing monitoring dashboards daily to track system status. At minimum, we suggest weekly monitoring reviews, focusing on alert events to proactively avoid most failures and issues.

Here’s a list of predefined alert rules in Pigsty.

Failover Follow-up

Pigsty’s high availability architecture allows PostgreSQL clusters to automatically perform primary-replica switching, meaning operations and DBAs don’t require immediate intervention. However, users still need to perform follow-up tasks at appropriate times (e.g., next business day), including:

• Investigate and confirm failure root cause to prevent recurrence
• Optionally restore original primary-replica topology or update configuration manifest to match new state
• Refresh load balancer configuration via bin/pgsql-svc to update service routing state
• Refresh cluster HBA rules via bin/pgsql-hba to prevent primary-replica specific rule drift
• If necessary, remove failed servers with bin/pgsql-rm and expand with new replicas using bin/pgsql-add

Bloat Control

Long-running PostgreSQL instances develop “table bloat” / “index bloat”, degrading system performance.

Regular online rebuilding of tables and indexes using pg_repack helps maintain optimal PostgreSQL performance. Pigsty installs and enables this extension by default in all databases, ready for immediate use.

You can check table and index bloat through Pigsty’s PGCAT Database - Table Bloat panel. Select tables and indexes with high bloat rates (larger tables with >50% bloat) for online reorganization using pg_repack:

pg_repack dbname -t schema.table

Normal reads/writes continue during reorganization, but the switch moment at completion requires an AccessExclusive lock, blocking all access. For high-throughput operations, schedule during low-traffic periods or maintenance windows. For more details, see: Managing Relation Bloat

VACUUM FREEZE

Freezing expired transaction IDs (VACUUM FREEZE) is a critical PostgreSQL maintenance task preventing transaction ID (XID) exhaustion outages. While PostgreSQL provides AutoVacuum mechanisms, for high-standard production environments, we recommend combining automatic and manual approaches, regularly executing database-wide VACUUM FREEZE to ensure XID safety.

3 - Failure SOP

This document outlines potential failures in PostgreSQL and Pigsty, along with SOPs for diagnosing, handling, and analyzing issues.

Disk Space Exhaustion

Disk space exhaustion is the most common type of failure.

Symptoms

When the disk hosting the database runs out of space, PostgreSQL cannot function properly. You may observe: database logs repeatedly reporting “no space left on device”, inability to write new data, or PostgreSQL triggering a PANIC and forcing shutdown.

Pigsty includes a NodeFsSpaceFull alert rule that triggers when filesystem available space drops below 10%. Use the monitoring system’s NODE Instance panel to review FS metric panels for diagnosis.

Diagnosis

You can also log into the database node and use df -h to check usage rates for each mount point, determining which partition is full. For database nodes, focus on these directories and their sizes to determine which file category is consuming space:

• Data directory (/pg/data/base): Stores table and index data files, watch for heavy writes and temporary files
• WAL directory (e.g., pg/data/pg_wal): Stores PG WAL, WAL accumulation/replication slot retention are common causes of disk exhaustion
• Database log directory (e.g., pg/log): If PG logs aren’t rotated timely and massive errors are written, this can consume significant space
• Local backup directory (e.g., data/backups): When using pgBackRest to save backups locally, this can also fill the disk

For Pigsty admin nodes or monitoring nodes, also consider:

• Monitoring data: Both Prometheus time-series metrics storage and Loki log storage consume disk space, check retention policies
• Object storage data: Pigsty’s integrated MinIO object storage may be used for PG backup storage

After identifying directories consuming the most space, use du -sh <directory> to drill down for specific large files or subdirectories.

Resolution

Disk exhaustion is an emergency requiring immediate action to free space and maintain database operation:

Emergency scenario: When data and system disks aren’t separated, disk exhaustion can prevent shell commands from executing. In this case, delete the /pg/dummy placeholder file to free emergency space for shell command recovery.

After freeing space with above measures, PostgreSQL should resume normal operation. If the database crashed due to pg_wal exhaustion, restart the database service after clearing space and carefully verify data integrity.

Transaction ID Wraparound

PostgreSQL uses 32-bit transaction IDs (XIDs) cyclically. When XIDs are exhausted, “transaction ID wraparound” failure occurs.

Symptoms

Initial symptoms include PGSQL Persist - Age Usage panel age saturation entering the warning zone. Database logs begin showing: WARNING: database "postgres" must be vacuumed within xxxxxxxx transactions.

If the problem worsens, PostgreSQL enters protection mode: when remaining transaction IDs drop below ~1 million, the database switches to read-only mode; at the limit of ~2.1 billion (2^31), it refuses new transactions and forces server shutdown to prevent data corruption.

Diagnosis

PostgreSQL and Pigsty enable AutoVacuum by default, so this failure usually indicates deeper root causes. Common causes include: super-aged transactions (SAGE), misconfigured Autovacuum, blocked replication slots, insufficient resources, storage engine/extension bugs, disk corruption.

First identify the database with the oldest age, then use the Pigsty PGCAT Database - Tables panel to check table age distribution. Review database error logs for clues to identify root causes.

Resolution

1. Immediate transaction freezing: If the database hasn’t entered read-only protection, immediately execute manual VACUUM FREEZE on affected databases. Start with the most aged tables rather than the entire database to expedite results. As superuser, run VACUUM FREEZE tablename; on tables with highest relfrozenxid, prioritizing tables with oldest XID age. This quickly reclaims significant transaction ID space.
2. Single-user mode rescue: If the database refuses writes or has crashed for protection, start the database in single-user mode for freeze operations. In single-user mode, run VACUUM FREEZE database_name; to freeze-clean the entire database. Then restart in multi-user mode. This releases wraparound locks and restores write capability. Exercise extreme caution in single-user mode and ensure sufficient transaction ID headroom for freezing.
3. Standby takeover: In complex scenarios (e.g., hardware issues preventing vacuum completion), consider promoting a read-only standby to primary for a cleaner environment. For example, if the primary has bad blocks preventing vacuum, manually failover to promote the standby as new primary, then perform emergency vacuum freeze. After ensuring the new primary has frozen old transactions, switch load back.

Connection Exhaustion

PostgreSQL has a maximum connection limit (max_connections). When client connections exceed this limit, new connection requests are rejected. Typical symptoms include applications unable to connect with errors like FATAL: remaining connection slots are reserved for non-replication superuser connections or too many clients already. This indicates regular connection slots are exhausted, leaving only slots reserved for superusers or replication.

Diagnosis

Connection exhaustion typically results from massive concurrent client requests. You can review current active sessions through PGCAT Instance / PGCAT Database / PGCAT Locks to determine what queries are filling the system for further action. Pay special attention to numerous Idle in Transaction connections and long-running transactions (and slow queries).

Resolution

Kill queries: For exhaustion blocking business operations, immediately use pg_terminate_backend(pid) for emergency relief. For connection pool users, adjust pool size parameters and reload to reduce database-level connections.

You can also use pg edit-config to increase max_connections, but this parameter requires database restart to take effect.

etcd Quota Exhaustion

etcd quota exhaustion causes PG high availability control plane failure, preventing configuration changes. Versions between Pigsty v2.0.0 - v2.5.1 are affected by default.

Diagnosis

Pigsty uses etcd as distributed configuration storage (DCS) for high availability. etcd has a storage quota (default ~2GB). When etcd storage reaches the quota limit, etcd refuses write operations with error “etcdserver: mvcc: database space exceeded”. In this state, Patroni cannot write heartbeats or update configurations to etcd, causing cluster management failure.

Resolution

Pigsty v2.6.0 adds auto-compaction configuration for deployed etcd. If you only use it for PG high availability leases, regular use cases won’t encounter this issue.

Defective Storage Engines

Currently, TimescaleDB’s experimental Hypercore storage engine has proven defects, with documented cases of VACUUM failing to reclaim XIDs causing wraparound failures. Users of this feature should migrate promptly to PostgreSQL native tables or TimescaleDB’s default engine.

Details: PG New Storage Engine Failure Case

4 - Data Loss Recovery

Accidental Data Deletion

For small-batch DELETE operations performed in error, consider using the pg_surgery extension for in-place surgical recovery.

If the deleted data has already been reclaimed by VACUUM, follow the general data loss recovery workflow.

Accidental Object Deletion

When DROP/DELETE operations are performed in error, follow this workflow to determine the recovery approach:

1. Verify if the data can be recovered through business systems or other data sources. If possible, recover directly from the business side.
2. Check for delayed replica availability. If available, advance the delayed replica to the point before deletion and query the data for recovery.
3. If data is confirmed deleted, verify backup coverage for the deletion timepoint. If covered, initiate PITR.
4. Determine whether to perform in-place PITR rollback on the entire cluster, replay on a new server, or use a replica for replay, then execute the recovery strategy.

Accidental Cluster Deletion

In cases where an entire database cluster is accidentally deleted, such as mistakenly executing the pgsql-rm.yml playbook: Unless you explicitly specified pg_rm_backup: false beforehand, backups will typically be deleted along with the database cluster.