# Failure SOP > Common failures and troubleshooting strategies --- 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 ` 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](https://g.pgsty.com/d/pgsql-persist) 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](https://mp.weixin.qq.com/s/LdZVVyOj4BA9C892I25lQw)