DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

PostgreSQL 57P02 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 57P02: crash_shutdown — What It Means and How to Fix It

PostgreSQL error code 57P02 crash_shutdown occurs when the database server terminates abnormally and connected clients receive notification of that unexpected shutdown. Unlike a graceful shutdown (57P01), this error signals a serious infrastructure-level failure that may require immediate investigation and data integrity verification.

Top 3 Causes

1. OOM (Out of Memory) Killer Terminating PostgreSQL Processes

When Linux runs out of memory, the OOM Killer forcibly terminates the most memory-hungry processes — and PostgreSQL is a frequent target. Overly generous work_mem settings multiplied by max_connections can silently exhaust system RAM.

-- Check current memory-related settings
SHOW work_mem;
SHOW shared_buffers;
SHOW max_connections;

-- Identify memory-heavy active queries
SELECT pid,
       usename,
       state,
       left(query, 80) AS short_query,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
NULLS LAST;
Enter fullscreen mode Exit fullscreen mode 
-- Best practice: keep server-wide work_mem low,
-- raise it per session only when needed
SET work_mem = '512MB';
SELECT * FROM large_table ORDER BY heavy_column;
RESET work_mem;
Enter fullscreen mode Exit fullscreen mode

2. Hardware / Storage I/O Errors

Disk failures, RAID controller faults, or unstable SAN/NAS connections can prevent PostgreSQL from safely flushing WAL or data pages. PostgreSQL's defensive design causes it to self-crash (PANIC) rather than risk data corruption. Check your logs for preceding PANIC: could not write to file messages.

-- Check temp file usage (high temp I/O = storage pressure)
SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
ORDER BY temp_bytes DESC;

-- Verify data directory location for storage-level checks
SHOW data_directory;
Enter fullscreen mode Exit fullscreen mode 
-- After recovery, confirm table accessibility
SELECT schemaname,
       tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
Enter fullscreen mode Exit fullscreen mode

3. Forced Kill or Misconfigured Parameters

Running kill -9 on the postmaster or using pg_ctl stop -m immediate cuts the server without flushing WAL. Similarly, setting memory parameters (shared_buffers, huge_pages) beyond available system resources causes the server to crash at startup or during runtime.

-- After restart, verify server recovery was successful
SELECT pg_postmaster_start_time() AS started_at,
       now() - pg_postmaster_start_time() AS uptime;

-- Check WAL archiving status post-recovery
SELECT archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count
FROM pg_stat_archiver;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Restart and let crash recovery run automatically.

PostgreSQL replays WAL automatically on restart. Check the logs to confirm recovery completed cleanly.

sudo systemctl restart postgresql
sudo tail -f /var/log/postgresql/postgresql-*.log
Enter fullscreen mode Exit fullscreen mode

Step 2 — Run VACUUM and integrity checks after recovery.

-- Refresh table statistics and reclaim dead tuples
VACUUM ANALYZE;

-- Spot-check critical tables
SELECT count(*) FROM pg_class;
SELECT count(*) FROM pg_attribute;
Enter fullscreen mode Exit fullscreen mode

Step 3 — Tune memory to prevent recurrence.

-- Apply safer memory defaults
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET shared_buffers = '4GB';  -- ~25% of total RAM
ALTER SYSTEM SET max_connections = '200';
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Monitor memory and protect the postmaster from OOM Killer.

Lower work_mem to a safe global default and use session-level overrides only for batch queries. On Linux, adjust oom_score_adj for the PostgreSQL process and set up alerting with pg_stat_activity or Prometheus exporters to catch memory spikes before they become crashes.

2. Enable WAL archiving and test recovery regularly.

Configure WAL archiving and PITR (Point-In-Time Recovery) so that a crash never means data loss beyond the last checkpoint. Use pg_basebackup or pgBackRest for automated backups, and run a restore drill at least once a month to confirm your backups are actually usable.

-- Verify archiving is healthy
SHOW archive_mode;
SHOW archive_command;
SELECT * FROM pg_stat_archiver;
Enter fullscreen mode Exit fullscreen mode

Related Error Codes

Code Name Description
57P01 admin_shutdown Planned shutdown via pg_terminate_backend() or pg_ctl stop -m fast
08006 connection_failure Client cannot reconnect after crash
XX000 internal_error Internal PANIC often logged just before a crash_shutdown
53200 out_of_memory PostgreSQL-level memory allocation failure, a common crash precursor

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)