#!/usr/bin/env bash # # JbTecWiz Support Centre -- generated fix script # # Fault : PostgreSQL: "too many clients", pg_hba refusals and a database that will not start # Fix : Recover a cluster that will not start # Source: https://jbtecwiz.com/support/lnx-web-postgres # # Run as : Root shell # Expect : 60 minutes # Risk : high # Reversible : NO -- read the undo note below # # WHEN THIS IS THE RIGHT FIX # The service fails to start after a crash, a disk-full event or a power # loss. # # HOW TO UNDO IT # Restore /root/pgdata-backup.tar.gz over the data directory to return # to the pre-repair state. # # Walks the fix one step at a time and asks before each. Steps with no # command are yours to do -- it prints those and waits. DRYRUN=1 prints # without executing; UNATTENDED=1 does not ask. # # -------------------------------------------------------------------- # NO WARRANTY - USE AT YOUR OWN RISK # # This script is provided by JbTecWiz as-is and with no warranty of any # kind, express or implied. You run it entirely at your own risk. # # JbTecWiz accepts no liability for any loss or damage arising from its # use, including but not limited to data loss, downtime, or configuration # changes that turn out to be wrong for your system. # # You are responsible for reading this script before running it, for # satisfying yourself that it suits the machine in front of you, and for # having a working backup first. Some steps cannot be undone. # -------------------------------------------------------------------- set -uo pipefail DRYRUN="${DRYRUN:-0}" UNATTENDED="${UNATTENDED:-0}" failed=0 if [ "$(id -u)" -ne 0 ]; then echo " This fix is documented as needing root. Re-run with sudo." >&2 exit 3 fi rule() { printf "\n%s\n" "$(printf '-%.0s' $(seq 1 70))"; if [ $# -gt 0 ]; then echo "$1"; fi; } prose() { echo "$1" | fold -s -w 74 | sed "s/^/ /"; } # Returns 0 when the caller should run the command, 1 when it should not. # A manual step always returns 1 -- there is nothing for the caller to run. step() { # step [command lines...] local n="$1" dotext="$2" why="$3" mode="$4"; shift 4 rule " Step $n of 6" prose "$dotext" if [ -n "$why" ]; then echo; prose "$why"; fi if [ "$mode" = "manual" ]; then echo; echo " -> Do this yourself, then press Enter to carry on." if [ "$UNATTENDED" = "0" ] && [ "$DRYRUN" = "0" ]; then read -r _; fi return 1 fi echo; printf " %s\n" "$@"; echo if [ "$DRYRUN" = "1" ]; then echo " (dry run -- not executed)"; return 1; fi if [ "$UNATTENDED" = "0" ]; then read -r -p " Run this step? [Y]es / [S]kip / [Q]uit " a case "$a" in [Qq]*) echo " Stopped at your request."; exit 0 ;; [Ss]*) echo " Skipped."; return 1 ;; esac fi return 0 } rule echo " PostgreSQL: "too many clients", pg_hba refusals and a database that will not start" echo " Recover a cluster that will not start" echo echo " Risk: high NOT REVERSIBLE 60 minutes" echo prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.' rule echo if [ "$UNATTENDED" = "0" ] && [ "$DRYRUN" = "0" ]; then read -r -p " Ready? [y/N] " go case "$go" in [Yy]*) ;; *) echo " Nothing was changed."; exit 0;; esac fi if step 1 'Read the log. PostgreSQL states the reason clearly and the right action depends entirely on which reason it is.' '' cmd 'sudo journalctl -u postgresql@16-main -n 60 --no-pager' 'sudo tail -60 /var/log/postgresql/postgresql-16-main.log'; then sudo journalctl -u postgresql@16-main -n 60 --no-pager sudo tail -60 /var/log/postgresql/postgresql-16-main.log if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 1 failed. The rest of the fix may depend on it." >&2 fi fi if step 2 'If the disk is full, free space before anything else -- PostgreSQL cannot recover while it cannot write.' '' cmd 'df -h' 'sudo du -sh /var/lib/postgresql/16/main/pg_wal'; then df -h sudo du -sh /var/lib/postgresql/16/main/pg_wal if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 2 failed. The rest of the fix may depend on it." >&2 fi fi if step 3 'Take a full filesystem-level copy of the data directory before any repair attempt.' 'Everything below can make things worse. With this copy, a failed repair costs an hour; without it, it can cost the database.' cmd 'sudo systemctl stop postgresql' 'sudo tar -C /var/lib/postgresql/16 -czf /root/pgdata-backup.tar.gz main'; then sudo systemctl stop postgresql sudo tar -C /var/lib/postgresql/16 -czf /root/pgdata-backup.tar.gz main if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 3 failed. The rest of the fix may depend on it." >&2 fi fi step 4 'Do not run pg_resetwal as a first response. It discards transactions and produces a cluster that starts but may be inconsistent -- it is a last resort for a database with no backup, and the correct next step afterwards is to dump and reload into a fresh cluster.' 'This command appears in a lot of quick answers and it is genuinely dangerous. It makes a broken cluster start, which looks like success and can hide silent corruption for weeks.' manual || true if step 5 'Restore from backup if one exists -- that is faster and safer than any repair.' '' cmd 'sudo -u postgres pg_restore --list /backups/latest.dump | head'; then sudo -u postgres pg_restore --list /backups/latest.dump | head if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 5 failed. The rest of the fix may depend on it." >&2 fi fi if step 6 'If pg_resetwal is genuinely the only option, dump everything immediately afterwards and reload into a new cluster.' '' cmd 'sudo -u postgres pg_dumpall > /root/emergency-dump.sql'; then sudo -u postgres pg_dumpall > /root/emergency-dump.sql if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 6 failed. The rest of the fix may depend on it." >&2 fi fi rule " Confirm it worked" prose 'The cluster starts, and a full pg_dumpall completes without error -- which is the practical test of consistency.' if [ "$DRYRUN" = "0" ]; then sudo systemctl status postgresql --no-pager sudo -u postgres psql -c 'SELECT count(*) FROM pg_database' fi rule if [ "$failed" -gt 0 ]; then echo " Finished with $failed failed step(s)." echo " Read the full write-up at https://jbtecwiz.com/support/lnx-web-postgres" else echo " Finished." fi echo prose 'To undo: Restore /root/pgdata-backup.tar.gz over the data directory to return to the pre-repair state.' rule