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

PostgreSQL error code 57P02 crash_shutdown indicates that the database server terminated abnormally, forcibly disconnecting all active client sessions. This typically occurs when the postmaster process receives a SIGQUIT signal, is killed by the OS-level OOM Killer, or crashes due to a hardware/storage failure. Unlike a graceful shutdown (57P01 admin_shutdown), a crash shutdown skips checkpointing and WAL flushing, requiring automatic crash recovery on the next startup.

Top 3 Causes

1. OOM (Out of Memory) Killer Termination

When the Linux kernel runs out of memory, it forcibly kills processes — and PostgreSQL's postmaster is often a prime target due to its large memory footprint. Overly generous work_mem settings combined with many concurrent connections are a common trigger.

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

-- Estimate maximum possible work_mem usage
SELECT
    current_setting('max_connections')::int AS max_conn,
    pg_size_pretty(
        current_setting('work_mem')::bigint *
        current_setting('max_connections')::bigint
    ) AS estimated_peak_work_mem;
Enter fullscreen mode Exit fullscreen mode 
# Check if OOM Killer fired
sudo dmesg | grep -i "oom\|killed"
Enter fullscreen mode Exit fullscreen mode

2. Immediate Shutdown via SIGQUIT or pg_ctl stop -m immediate

Running pg_ctl stop -m immediate or sending SIGQUIT directly to the postmaster causes PostgreSQL to exit without writing a clean checkpoint. This leaves the cluster in a "dirty" state, requiring WAL-based crash recovery on the next start.

# Avoid this in production unless absolutely necessary
pg_ctl stop -m immediate -D /var/lib/postgresql/data

# Prefer fast or smart shutdown instead
pg_ctl stop -m fast -D /var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode 
-- After restart, verify recovery completed and check for issues
SELECT pg_is_in_recovery();  -- Should return FALSE after full recovery

-- Check for checksum failures post-recovery
SELECT datname, checksum_failures, stats_reset
FROM pg_stat_database
WHERE checksum_failures > 0;
Enter fullscreen mode Exit fullscreen mode

3. Storage I/O Errors or Hardware Failure

Disk failures, RAID controller errors, or unstable SAN/NAS connections can cause PostgreSQL to panic and crash immediately. The logs will typically show fsync failed or could not write to file messages before the crash.

# Check disk health
sudo smartctl -a /dev/sda
sudo dmesg | grep -i "error\|i/o\|fail"

# Verify PostgreSQL data checksums (if enabled at initdb time)
pg_checksums --check -D /var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode 
-- Check archiver status after recovery
SELECT last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;

-- Run integrity check on indexes using amcheck
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(oid)
FROM pg_class
WHERE relkind = 'i'
  AND relnamespace NOT IN (
      SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'
  );
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Restart PostgreSQL — crash recovery (WAL replay) runs automatically on startup: 
   sudo systemctl restart postgresql
   # Monitor logs during recovery
   sudo tail -f /var/log/postgresql/postgresql-*.log
Enter fullscreen mode Exit fullscreen mode
  1. Refresh statistics after recovery: 
   ANALYZE VERBOSE;
   SELECT pg_stat_reset();
Enter fullscreen mode Exit fullscreen mode
  1. Protect postmaster from OOM Killer: 
   echo -1000 > /proc/$(head -1 /var/run/postgresql/*.pid)/oom_score_adj
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Tune memory settings conservatively and enable detailed logging:

-- Conservative memory configuration
ALTER SYSTEM SET work_mem = '4MB';           -- Start low, tune per query
ALTER SYSTEM SET shared_buffers = '256MB';   -- ~25% of RAM as a baseline

-- Enable crash-relevant logging
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_min_messages = 'WARNING';
ALTER SYSTEM SET log_line_prefix = '%t [%p] %u@%d ';
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

2. Enable WAL archiving and regular base backups for PITR:

-- Verify archiving is active
SHOW archive_mode;
SHOW archive_command;
SHOW wal_level;

-- Check archiver health
SELECT last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;
Enter fullscreen mode Exit fullscreen mode

Pair this with scheduled pg_basebackup runs and an off-site WAL archive so you can recover to any point in time after a crash.

Related Error Codes

Code Name Description
57P01 admin_shutdown Planned shutdown by DBA — graceful, no crash recovery needed
57P03 cannot_connect_now Server is still in crash recovery after 57P02
08006 connection_failure Network-level disconnect, symptoms similar to 57P02
XX000 internal_error Backend panic that can trigger a cascade into 57P02

📖 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)