PostgreSQL Recovery Without Backups: A 2.5 TB Cluster Case Study
A 2.5 TB PostgreSQL cluster from Postgres Professional ran for three years on a virtual machine without backups. The disk, configured without space reservation, exceeded the host's physical size, expanding via copy-on-write. Ten active databases, including 1C, crashed after loading a new infobase: WAL logs filled the hypervisor disk, and checkpoints failed to complete.
Administrators manually deleted WAL files and attempted to stop the postmaster in fast mode, but unsuccessfully. A forced SIGKILL stop led to data corruption due to massive shared_buffers. Starting via pg_resetwal disabled WAL recovery; simple queries worked, but complex ones did not.
Diagnostics and Initial Patches
Using the crash_info utility (available in the free Postgres Professional build) via the SIGRTMIN+6 signal, we identified a hang on _bt_moveright in a btree index of the system catalog. We enabled the ignore_system_indexes parameter or applied a patch to bypass system indexes. REINDEX SYSTEM did not complete overnight.
pg_dump hung while retrieving the table list due to missing indexes. The --data-only parameter did not help: the utility requested extended statistics. A patch for pg_dump excluded unnecessary queries to the system catalog.
Data Export and Consequences
After 24 hours, data from the key database was exported to a new cluster. A bug report submitted to the PostgreSQL community with a patch (for versions 13–17) highlighted pg_dump issues with corrupted indexes. The community refused to classify this as a bug: recovery without backups is not a priority.
The client purchased enterprise support from Postgres Professional. An alternative to pg_dump was COPY via a table list from 1C (50,000 tables in the database), generated by a script without accessing the system catalog.
Key Administrator Errors
- Lack of backups and replicas.
- Manual deletion of WAL logs.
- Stopping postmaster with SIGKILL instead of SIGQUIT.
- Ignoring disk reservation recommendations.
- Reliance on 'guides' without verifying backups.
- Incorrect choice of pg_dump without accounting for catalog damage.
What Matters
- Without backups (dumps, replicas), full recovery of a 2.5 TB cluster is impossible.
- ignore_system_indexes allows bypassing corrupted system indexes.
- crash_info is useful for state dumps without console access.
- pg_dump is vulnerable to catalog damage: use --data-only with patches or COPY.
- SIGKILLing postmaster destroys data when shared_buffers is large.
Lessons for DBAs
A 1C cluster requires WAL cleanup, disk space monitoring, and replication. Huge shared_buffers masks problems until catastrophe strikes. For 50,000 tables, generate COPY scripts from 1C metadata.
— Editorial Team
No comments yet.