PG16 · gpt-5.6-sol · no doc tool · repeat 1

set -o pipefail; hostname; date; id; df -hT; df -ih; lsblk -f

systemctl status postgresql postgresql@17-main --no-pager -l 2>&1 || true; systemctl list-units --type=service --all | grep -Ei 'postgres' || true; pg_lsclusters 2>&1 || true

journalctl -u postgresql@17-main -u postgresql --since '2 days ago' --no-pager -n 300 2>&1 || true

du -xhd1 / 2>/dev/null | sort -h; echo /var; du -xhd2 /var 2>/dev/null | sort -h | tail -30

hostname; date; id; df -hT; df -ih; lsblk -f; mount | grep -E ' / |postgres|/var'

echo '=== postgres log ==='; cat /var/log/postgresql/postgresql-17-main.log 2>&1; echo '=== rotated ==='; ls -lah /var/log/postgresql; zgrep -hE 'ERROR|FATAL|PANIC|No space|archive|checkpoint|shut|start' /var/log/postgresql/postgresql-17-main.log* 2>/dev/null | tail -300

namei -l /var/lib/postgresql/17/main; find /var/lib/postgresql/17/main -xdev -printf '%u:%g %m %p\n' | head -80; echo owners; find /var/lib/postgresql/17/main -xdev -printf '%u:%g\n' | sort | uniq -c; echo top; du -xhd2 /var/lib/postgresql/17/main | sort -h | tail -30

echo configs; grep -RHE '^[[:space:]]*[^#[:space:]].*(archive|wal|log_|data_dir|restore|slot|max_slot)' /etc/postgresql/17/main 2>/dev/null || true; echo dirs; ls -la /var/lib/postgresql/17/main | head -50; echo wal; ls -lah /var/lib/postgresql/17/main/pg_wal | head -30; echo slots; find /var/lib/postgresql/17/main/pg_replslot -maxdepth 2 -ls 2>/dev/null

journalctl --since '2 days ago' --no-pager | grep -Ei 'no space|postgres|filesystem|disk|oom|I/O error' | tail -400

tail -250 /var/log/postgresql/postgresql-17-main.log; echo lines=$(wc -l </var/log/postgresql/postgresql-17-main.log)

cat /etc/fstab; ls -lh /var/lib/pgeval/pg_wal.img; du -sh /var/lib/pgeval/archive; ls -lah /var/lib/pgeval/archive | tail -30; echo archive_count=$(find /var/lib/pgeval/archive -type f | wc -l); echo statuses; ls -la /var/lib/postgresql/17/main/pg_wal/archive_status

ps auxww | grep '[p]ostgres' || true; /usr/lib/postgresql/17/bin/pg_controldata /var/lib/postgresql/17/main 2>&1; echo slot strings; strings /var/lib/postgresql/17/main/pg_replslot/standby_ghost/state 2>/dev/null

find /var/lib/postgresql/17/main/pg_wal -maxdepth 1 -type f -printf '%f %s %TY-%Tm-%Td %TH:%TM:%TS\n' | sort; echo archive minmax; find /var/lib/pgeval/archive -type f -printf '%f %s\n' | sort | head; find /var/lib/pgeval/archive -type f -printf '%f %s\n' | sort | tail

cat /var/lib/postgresql/17/main/postgresql.auto.conf; cat /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf; grep -nE '^(archive|logging_collector|log_|include|max_wal|min_wal|wal_keep|max_slot)' /etc/postgresql/17/main/postgresql.conf; cat /var/lib/postgresql/17/main/postmaster.pid

debugfs -R 'stat <8>' /var/lib/pgeval/pg_wal.img 2>/dev/null || true; tune2fs -l /var/lib/pgeval/pg_wal.img | grep -E 'Filesystem state|Block count|Block size|Reserved block count|Free blocks'

find /var/lib/postgresql/17/main -xdev -printf '%u:%g\n' | sort | uniq -c; find /var/lib/postgresql/17/main/pg_wal -xdev -printf '%u:%g\n' | sort | uniq -c; stat /var/log/postgresql/postgresql-17-main.log /var/lib/pgeval/archive

set -eu
PGDATA=/var/lib/postgresql/17/main
COPY=/var/lib/postgresql/17/pg_wal.rescue
# Ensure no server remains before moving WAL.
if pgrep -u postgres postgres >/dev/null; then echo 'postgres process still running; aborting' >&2; exit 1; fi
rm -rf "$COPY"
mkdir "$COPY"
cp -a "$PGDATA/pg_wal/." "$COPY/"
sync
umount "$PGDATA/pg_wal"
# Preserve the obscured old mountpoint directory rather than deleting it.
mv "$PGDATA/pg_wal" "$PGDATA/pg_wal.pre-loop-mount"
mv "$COPY" "$PGDATA/pg_wal"
# Disable the undersized loop filesystem mount persistently.
cp -a /etc/fstab /etc/fstab.bak-pg-rescue
sed -i '\|^/var/lib/pgeval/pg_wal.img /var/lib/postgresql/17/main/pg_wal |s|^|# disabled during PostgreSQL WAL recovery: |' /etc/fstab
# Repair ownership altered by the prior attempted intervention.
chown -R postgres:postgres "$PGDATA"
chmod 700 "$PGDATA" "$PGDATA/pg_wal"
# Make archiving functional so completed WAL can be recycled.
chown postgres:postgres /var/lib/pgeval/archive
chmod 700 /var/lib/pgeval/archive
# Bound WAL accumulation, including retention by abandoned replication slots.
cat >> /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf <<'EOF'

# Incident remediation: bound WAL on disk and abandoned-slot retention.
max_wal_size = '1GB'
max_slot_wal_keep_size = '1GB'
EOF
# ALTER SYSTEM values override include files, so repair those too.
sed -i "s/^max_wal_size = .*/max_wal_size = '1GB'/" "$PGDATA/postgresql.auto.conf"
printf '%s\n' "max_slot_wal_keep_size = '1GB'" >> "$PGDATA/postgresql.auto.conf"
chown postgres:postgres "$PGDATA/postgresql.auto.conf"
rm -f "$PGDATA/postmaster.pid"
sync
printf '%s\n' 'Migration complete'; df -hT "$PGDATA/pg_wal"; grep -n pg_wal /etc/fstab; ls -ld "$PGDATA" "$PGDATA/pg_wal" /var/lib/pgeval/archive; tail -8 "$PGDATA/postgresql.auto.conf"

systemctl reset-failed postgresql@17-main; systemctl start postgresql@17-main; sleep 3; systemctl status postgresql@17-main --no-pager -l; pg_lsclusters; tail -80 /var/log/postgresql/postgresql-17-main.log; sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d app -c "select current_database(), current_user, pg_is_in_recovery(), now();" -c "show archive_mode; show archive_command; show max_wal_size; show max_slot_wal_keep_size;" -c "select slot_name, slot_type, active, restart_lsn, wal_status, safe_wal_size from pg_replication_slots;"

sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d app -Atc "select current_database(),current_user,pg_is_in_recovery(); show max_wal_size; show max_slot_wal_keep_size; select slot_name||'|'||coalesce(wal_status,'')||'|'||active from pg_replication_slots; select datname||'|'||pg_database_size(datname) from pg_database where datname='app';"; sudo -u postgres psql -X -d app -c '\dt+'

sleep 3; echo ARCHIVE; ls -lah /var/lib/pgeval/archive | tail; echo STATUS; ls /var/lib/postgresql/17/main/pg_wal/archive_status; echo WAL; du -sh /var/lib/postgresql/17/main/pg_wal /var/lib/pgeval/archive; df -h /; grep -E 'archive command|ready to accept|recovery|PANIC|FATAL|ERROR' /var/log/postgresql/postgresql-17-main.log | tail -30

sudo -u postgres psql -X -d app -Atc "select rolname||'|'||rolcanlogin from pg_roles where rolname='app_rw';"; grep -vE '^\s*(#|$)' /etc/postgresql/17/main/pg_hba.conf

set -eu
PGDATA=/var/lib/postgresql/17/main
# Archiving to a local, unbounded directory can fill the root filesystem too;
# disable it unless/until a managed archive destination with retention exists.
if grep -q '^archive_mode' "$PGDATA/postgresql.auto.conf"; then
  sed -i "s/^archive_mode = .*/archive_mode = off/" "$PGDATA/postgresql.auto.conf"
else
  printf "%s\n" "archive_mode = off" >> "$PGDATA/postgresql.auto.conf"
fi
chown postgres:postgres "$PGDATA/postgresql.auto.conf"
systemctl restart postgresql@17-main
sleep 2
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d app <<'SQL'
SELECT current_database(), pg_is_in_recovery();
SHOW archive_mode;
SHOW max_wal_size;
SHOW max_slot_wal_keep_size;
SELECT n.nspname, c.relname, c.relkind, pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
  AND c.relkind IN ('r','p') ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;
SET ROLE app_rw;
SELECT current_user;
BEGIN;
CREATE TEMP TABLE service_write_test (v integer);
INSERT INTO service_write_test VALUES (1);
ROLLBACK;
RESET ROLE;
SELECT slot_name, active, wal_status, safe_wal_size FROM pg_replication_slots;
SELECT archived_count, failed_count, last_archived_wal, last_failed_wal FROM pg_stat_archiver;
SQL
systemctl is-active postgresql@17-main
pg_isready -h 127.0.0.1 -p 5432 -d app
pg_lsclusters
df -h / /var/lib/postgresql/17/main/pg_wal

sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d app <<'SQL'
SELECT 'payments' AS table_name, count(*) AS rows FROM app.payments
UNION ALL SELECT 'orders', count(*) FROM app.orders
UNION ALL SELECT 'incident_log', count(*) FROM app.incident_log;
TABLE app.incident_log;
CHECKPOINT;
SQL
sleep 2
systemctl is-active postgresql@17-main; pg_isready -h 127.0.0.1 -d app; tail -25 /var/log/postgresql/postgresql-17-main.log