PostgreSQL Log Reduction

Hourly cron script for reducing PostgreSQL log pressure while keeping a local history and an optional rsync queue.

Files

• postgres-log-reduction: cron-ready bash script.
• README.md: install and configuration notes.
• REPORT.md: implementation retrospective.

Directory Layout

The script manages one log root:

/pg/logs/
/pg/logs/to_rsync/
/pg/logs/history/

PostgreSQL should write logs into the top-level logs directory. The script moves stable files into history and hardlinks them into to_rsync, so the two managed copies share disk blocks.

Install

Copy the script into /etc/cron.hourly without a file extension:

sudo install -m 0755 postgres-log-reduction /etc/cron.hourly/postgres-log-reduction

Confirm cron accepts the script name:

run-parts --test /etc/cron.hourly

Configuration

Edit the header variables in postgres-log-reduction before installing.

LOG_ROOT="/pg/logs"
ENABLE_RSYNC=0
RSYNC_DEST="postgres-log-archive.example.com:/srv/postgres-logs/"
RSYNC_SSH_OPTS="-o BatchMode=yes -o ConnectTimeout=15"
HIGH_WATER_PERCENT=85
LOW_WATER_PERCENT=75
MIN_FILE_AGE_MINUTES=10
BATCH_NUMBER=5
VERBOSE=0
LOCK_FILE="/run/postgres-log-reduction.lock"

set -u makes the script fail on unset variables, which catches mistyped variable names early.

ENABLE_RSYNC=0 is intentional by default. Set it to 1 only after the remote destination and SSH access have been tested.

Behavior

Each run:

1. Creates logs, logs/to_rsync, and logs/history if missing.
2. Locks with flock so overlapping cron runs exit quietly.
3. Moves top-level log files older than MIN_FILE_AGE_MINUTES into history.
4. Hardlinks each moved file into to_rsync.
5. Optionally runs rsync -a --remove-source-files from to_rsync.
6. Prunes oldest managed file paths in batches when disk usage crosses the high-water mark.

Pruning deletes up to BATCH_NUMBER oldest paths from history and to_rsync per pass. This handles rsync queue files that were already removed after a successful transfer.

Logging

The script is quiet by default for cron. To test with output:

sudo /etc/cron.hourly/postgres-log-reduction -v

or:

VERBOSE=1 ./postgres-log-reduction

Testing Locally

Run a syntax check:

bash -n postgres-log-reduction

Run against a temporary directory:

tmp=$(mktemp -d)
mkdir -p "$tmp/logs"
printf 'example\n' > "$tmp/logs/postgresql.log"
touch -d '20 minutes ago' "$tmp/logs/postgresql.log"
LOG_ROOT="$tmp/logs" LOCK_FILE="$tmp/lock" VERBOSE=1 ./postgres-log-reduction
find "$tmp/logs" -type f -printf '%P %n\n' | sort

Expected result: the file appears in both history and to_rsync with link count 2.

Operational Notes

Use PostgreSQL log rotation so completed files become inactive. The script avoids very new files with MIN_FILE_AGE_MINUTES, but it cannot know whether PostgreSQL still has an old file handle open.

Keep HIGH_WATER_PERCENT higher than LOW_WATER_PERCENT. If both values are too close, pruning may run often without freeing much space.

If rsync is enabled, the remote directory should already exist and the cron user must have non-interactive SSH access.