#!/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 : Add pooling rather than raising the limit # Source: https://jbtecwiz.com/support/lnx-web-postgres # # Run as : Root shell # Expect : 40 minutes # Risk : medium # Reversible : yes # # WHEN THIS IS THE RIGHT FIX # "sorry, too many clients already". # # HOW TO UNDO IT # ALTER SYSTEM RESET idle_in_transaction_session_timeout, then reload. # # 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 5" 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 " Add pooling rather than raising the limit" echo echo " Risk: medium Reversible 40 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 'See the limit and what is connected.' '' cmd 'sudo -u postgres psql -c '\''SHOW max_connections'\''' 'sudo -u postgres psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state"'; then sudo -u postgres psql -c 'SHOW max_connections' sudo -u postgres psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state" 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 'Look for connections idle in transaction -- these hold locks and are far worse than idle ones.' '"Idle in transaction" for minutes means the application opened a transaction and went away. It blocks vacuum and holds locks, and no amount of extra connections fixes the underlying bug.' cmd 'sudo -u postgres psql -c "SELECT pid, usename, state, now()-state_change AS age, LEFT(query,60) FROM pg_stat_activity WHERE state <> '\''idle'\'' ORDER BY age DESC LIMIT 20"'; then sudo -u postgres psql -c "SELECT pid, usename, state, now()-state_change AS age, LEFT(query,60) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 20" 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 'Terminate a stuck session only when you understand what it was doing.' '' cmd 'sudo -u postgres psql -c "SELECT pg_terminate_backend(12345)"'; then sudo -u postgres psql -c "SELECT pg_terminate_backend(12345)" if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 3 failed. The rest of the fix may depend on it." >&2 fi fi if step 4 'Install a connection pooler rather than raising max_connections. PostgreSQL uses a process per connection and does not scale the way a threaded server does.' 'Raising max_connections to several hundred costs memory per connection and increases contention. PgBouncer in transaction mode lets hundreds of application connections share a few dozen real ones, which is the standard answer.' cmd 'sudo apt install pgbouncer'; then sudo apt install pgbouncer if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 4 failed. The rest of the fix may depend on it." >&2 fi fi if step 5 'Set an idle transaction timeout so this cannot recur silently.' '' cmd 'sudo -u postgres psql -c "ALTER SYSTEM SET idle_in_transaction_session_timeout = '\''60s'\''"' 'sudo -u postgres psql -c '\''SELECT pg_reload_conf()'\'''; then sudo -u postgres psql -c "ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s'" sudo -u postgres psql -c 'SELECT pg_reload_conf()' if [ $? -ne 0 ]; then failed=$((failed+1)) echo " Step 5 failed. The rest of the fix may depend on it." >&2 fi fi rule " Confirm it worked" prose 'Connection counts stay low and no session sits idle in transaction.' if [ "$DRYRUN" = "0" ]; then sudo -u postgres psql -c "SELECT state,count(*) FROM pg_stat_activity GROUP BY state" 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: ALTER SYSTEM RESET idle_in_transaction_session_timeout, then reload.' rule