Automate PostgreSQL VACUUM ANALYZE with Monitoring

Schedule

Setup

1. 1

   Create the maintenance script on your server

   Save the script below to a path such as /usr/local/bin/pg-vacuum.sh and make it executable with: chmod +x /usr/local/bin/pg-vacuum.sh. Fill in your DB connection details and the heartbeat token from step 3.

2. 2

   Test the script manually before scheduling

   Run the script as the system user that owns the PostgreSQL role, for example: sudo -u postgres /usr/local/bin/pg-vacuum.sh. Check for errors in the output and confirm vacuumdb exits with code 0.

3. 3

   Create a Heartbeat monitor in CronJobPro

   In CronJobPro, create a new Heartbeat monitor, set the expected period to 7 days, and set a grace period of 30 minutes. Copy the unique ping URL you receive, which will look like https://cronjobpro.com/ping/<token>. Paste this token into the HEARTBEAT_URL variable in the script.

4. 4

   Register the cron entry on your server

   Open the crontab for the postgres user with: sudo -u postgres crontab -e. Add the line: 0 3 * * 0 /usr/local/bin/pg-vacuum.sh >> /var/log/pg-vacuum.log 2>&1. This runs the script every Sunday at 3 AM and appends all output to a log file.

5. 5

   Configure alerts in CronJobPro

   Under your heartbeat monitor settings, add at least one alert channel such as email or Slack. CronJobPro will notify you if the ping URL is not called within the expected period plus the grace window, meaning the job missed its window or crashed before completing.

The script

bash

#!/usr/bin/env bash
# pg-vacuum.sh — weekly VACUUM ANALYZE for all databases
# Fill in your connection details and heartbeat token before deploying.

set -euo pipefail

# --- Configuration ---
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
# Space-separated list of databases to maintain. Use __all__ to iterate
# every database returned by psql.
DATABASES="myapp_production"
HEARTBEAT_URL="https://cronjobpro.com/ping/YOUR_TOKEN_HERE"
LOG_PREFIX="[pg-vacuum $(date '+%Y-%m-%d %H:%M:%S')]"

# --- Run VACUUM ANALYZE ---
echo "$LOG_PREFIX Starting VACUUM ANALYZE"

if [ "$DATABASES" = "__all__" ]; then
  DATABASES=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres \
    -Atc "SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true;")
fi

EXIT_CODE=0

for DB in $DATABASES; do
  echo "$LOG_PREFIX Running vacuumdb on database: $DB"
  if vacuumdb \
      --host="$DB_HOST" \
      --port="$DB_PORT" \
      --username="$DB_USER" \
      --analyze \
      --verbose \
      --dbname="$DB"; then
    echo "$LOG_PREFIX Completed successfully: $DB"
  else
    echo "$LOG_PREFIX ERROR: vacuumdb failed for database: $DB" >&2
    EXIT_CODE=1
  fi
done

# --- Report result to CronJobPro ---
if [ "$EXIT_CODE" -eq 0 ]; then
  echo "$LOG_PREFIX All databases processed. Sending heartbeat ping."
  curl --silent --max-time 10 --retry 3 "$HEARTBEAT_URL" \
    --output /dev/null
  echo "$LOG_PREFIX Heartbeat sent."
else
  echo "$LOG_PREFIX One or more databases failed. Sending failure ping." >&2
  curl --silent --max-time 10 --retry 3 "${HEARTBEAT_URL}/fail" \
    --output /dev/null
  exit 1
fi

Frequently asked questions

More recipes