How to fix FATAL: the database system is in recovery mode in PostgreSQL

# On Linux, logs are typically here:
tail -f /var/log/postgresql/postgresql-*.log

# Or check systemd journal
sudo journalctl -u postgresql -f

# Look for messages like:
# LOG: starting crash recovery
# LOG: redo starts at ...
# LOG: redo done at ...
# LOG: MultiXact member wraparound protections are now enabled
# LOG: database system is ready to accept read-only connections

# Or for standby mode:
# LOG: entering standby mode
# LOG: consistent recovery state reached
# LOG: database system is ready to accept read-only connections

-- Check if recovery is still in progress
SELECT pg_is_in_recovery();
-- Returns: true (still recovering) or false (recovery complete, ready to write)

-- On a standby, this will always return true
-- On a primary that finished recovery, this returns false

# Watch PostgreSQL logs in real-time
tail -f /var/log/postgresql/postgresql-*.log | grep -E "redo|recovery|ready"

# Monitor with ps to see crash recovery status
watch 'ps aux | grep postgres | grep -v grep'

-- Check if this is a standby (read-only) or primary server
SELECT pg_is_in_recovery();
-- standby: returns true
-- primary: returns false (after recovery completes)

-- For PostgreSQL 10+, use this for more details:
SELECT
  pg_is_wal_replay_paused() AS replay_paused,
  CASE WHEN pg_is_in_recovery() THEN 'STANDBY' ELSE 'PRIMARY' END AS server_role,
  pg_last_xlog_receive_location() AS receive_location,
  pg_last_xlog_replay_location() AS replay_location;

# To promote a standby to primary:
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/14/main

# Or from SQL (PostgreSQL 10+):
# SELECT pg_promote();

# Check WAL directory
ls -lh /var/lib/postgresql/14/main/pg_wal/

# Look for files like: 00000001000000000000000C
# Each file is normally 16MB

# If you see very small files or gaps in the sequence, it indicates corruption

# Check PostgreSQL logs for WAL-related errors:
grep -i "wal|redo|corrupt|invalid" /var/log/postgresql/postgresql-*.log

# Check if you have archived WAL files for recovery
ls -lh /path/to/wal-archive/

# Check available disk space
df -h /var/lib/postgresql/

# Check if the data directory is full
du -sh /var/lib/postgresql/14/main/

# Typical requirement: at least 10-20% free space of database size

sudo systemctl stop postgresql

# Remove old WAL files (if properly archived)
rm /var/lib/postgresql/14/main/pg_wal/0000000*[01]

# Or delete old logs
rm /var/log/postgresql/*.log.1

sudo systemctl start postgresql

# Check current recovery process
ps aux | grep postgres | grep -v grep

# Graceful shutdown (waits for recovery to finish or kill long queries)
sudo systemctl stop postgresql
# or
sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m smart

# Wait for graceful shutdown
sleep 10

# Start PostgreSQL again
sudo systemctl start postgresql

# Monitor logs
tail -f /var/log/postgresql/postgresql-*.log

# AVOID THIS - will cause recovery on next startup:
pg_ctl stop -m immediate

# Use these instead:
pg_ctl stop -m smart   # Waits for clients to disconnect
pg_ctl stop -m fast    # Disconnects clients (still cleaner than immediate)

# Wait for PostgreSQL to be ready
while ! sudo -u postgres psql -c "SELECT 1" > /dev/null 2>&1; do
  echo "Waiting for PostgreSQL to become available..."
  sleep 5
done

echo "PostgreSQL is now accepting connections!"

-- Once connected, verify recovery is complete
SELECT pg_is_in_recovery() AS in_recovery;
-- Should return: false (for primary) or true (for standby)

-- Check connection status
SELECT datname, usename, state FROM pg_stat_activity LIMIT 5;

-- Verify database integrity (after recovery completes)
REINDEX DATABASE postgres;

-- Check for any remaining replication lag (if standby)
SELECT slot_name, slot_type, restart_lsn FROM pg_replication_slots;

# Test application connectivity
psql -U postgres -d postgres -c "SELECT version();"

# Verify data integrity with a simple query
psql -U postgres -d postgres -c "SELECT count(*) FROM information_schema.tables;"