PostgreSQL Maintenance Script

A simple yet powerful shell script to perform automated PostgreSQL maintenance across all databases.

The script executes VACUUM (ANALYZE) on all non-template databases and ensures consistent database performance and statistics.

---

🚀 Features

• Automatic VACUUM (ANALYZE) for all PostgreSQL databases
• Daily execution via /etc/cron.daily
• Self-updating script (optional)
• Configurable via .env
• Logging with retention
• Lockfile to prevent parallel execution
• Optional include/exclude database filtering

---

📦 Installation

Install the script directly via:

curl -fsSL https://git.onesystems.ch/System-Tools/PostgreSQL-Maintenance-Script/raw/branch/main/pg-maintenance.sh | sudo bash -s -- install

This will:

• Install the script to /opt/postgresql-maintenance
• Create a symlink in /etc/cron.daily
• Automatically run once per day

---

⚙️ Configuration

After the first run, a configuration file will be created:

/opt/postgresql-maintenance/.env

Example:

# Enable automatic script updates
ONLINE_UPDATE=true

# Exclude databases
EXCLUDE_DATABASES="template0 template1"

# Only include specific databases (optional)
INCLUDE_DATABASES=""

# VACUUM command
VACUUM_SQL="VACUUM (VERBOSE, ANALYZE);"

# Log retention (days)
LOG_RETENTION_DAYS=14

# Lockfile (optional)
LOCKFILE="/var/run/postgresql-maintenance.lock"

---

▶️ Usage

Run manually

/etc/cron.daily/postgresql-maintenance

Or directly

/opt/postgresql-maintenance/pg-maintenance.sh

---

📊 What it does

For each database:

• Connects as postgres
• Executes:

VACUUM (VERBOSE, ANALYZE);

• Logs success or failure

---

🧾 Logging

Logs are stored in:

/opt/postgresql-maintenance/logs/

Example:

pg-maintenance-20260412-031500.log

Old logs are automatically removed based on:

LOG_RETENTION_DAYS

---

🔄 Auto Update

If enabled:

ONLINE_UPDATE=true

The script will:

• Check for a new version
• Download it automatically
• Restart itself

---

⚠️ Requirements

• PostgreSQL installed
• psql available
• runuser available
• flock available
• User postgres exists

---

🧠 Notes

• No VACUUM FULL is used (avoids table locking)
• Safe for daily execution
• Designed for small to mid-sized environments

---

🛠 Troubleshooting

Script fails with permission issues

Run manually:

sudo -u postgres psql -c "SELECT version();"

Check logs

ls -lah /opt/postgresql-maintenance/logs/

---

📌 Recommended Usage

• Run daily (default)
• Monitor with Checkmk or similar
• Combine with proper autovacuum tuning

---

👨‍💻 Author

Michael Kleger OneSystems GmbH

---

📄 License

MIT License – free for commercial and private use.