#!/usr/bin/perl -w
# Nightly database maintenance script
#
# XXX: should probably do the pidfile thing while running to reduce chances
# of two copies clobbering each other.

use strict;
use warnings "all";
use DateTime;
use DBI;
use POSIX qw(strftime);
use Sys::Syslog qw(:DEFAULT);

openlog('dbmaintenance', '', 'local0') or die "Unable to openlog";

# Security: don't make world-readable backups.
umask 0077 or die "Unable to set proper umask: $!";

# Find out what databases are available.
my ($t1_dbh,$dbquery);
eval {
    $t1_dbh = DBI->connect('dbi:Pg(RaiseError=>1):dbname=template1','','')
            or die "Unable to connect to template0: $!";
    $dbquery = $t1_dbh->prepare(q{
        select    datname
        from      pg_database
    });
    $dbquery->execute();
};
if ($@) {
    syslog('err', 'Unable to get database list: %s', $@);
    exit(1);
}

# Loop through them.
while (my $tuple = $dbquery->fetchrow_hashref()) {
    my $database = $tuple->{datname};
    $database eq 'template0' and next; # don't touch template0.
    $database =~ /^(\w)+$/ or die "Invalid database name $database";

    # Run backups
    my $dest = "/var/lib/pgsql/backups/$database-"
        . strftime("%Y-%m-%d-%H-%M", gmtime);
    if (system('/usr/bin/pg_dump', '--format=c', '--blobs',
           "--file=$dest.tmp",
           $database) == 0) {
        if (!rename("$dest.tmp", $dest)) {
            syslog('err', "Unable to move %s into place: %s", $dest, $!);
        } else {
            syslog('info', "Successfully backed up database %s", $database);
        }
    } else {
        syslog('err', "Unable to backup database %s: %d", $database, $?);
    }

    # Vacuum/analyze
    eval {
        my $dbh = DBI->connect("dbi:Pg(RaiseError=>1):dbname=$database",'','')
                or die "Unable to connect to $database: $!";
        $dbh->do('vacuum analyze');
        $dbh->disconnect();
    };
    if ($@) {
        syslog('warning', "Unable to vacuum analyze database %s: %s", $database, $@);
    } else {
        syslog('info', "Successfully vacuum analyzed database %s", $database);
    }
}
$t1_dbh->disconnect();

# Now get rid of some old backups

my $n_removed = 0;
foreach my $backup (glob '/var/lib/pgsql/backups/*') {
    my ($year,$mon,$day,$hour,$min) = ($backup =~ m{^/var/lib/pgsql/backups/  # Dir
                                                     [^-]+-                   # Database name
                                                     (\d{4})-                 # Year
                                                     (\d{1,2})-               # Month
                                                     (\d{1,2})-               # Day
                                                     (\d{2})-                 # Hour
                                                     (\d{2})$                 # Minute
                                                   }x) or die "Illegal backup filename format: $!";
    my $date = DateTime->new(year       => $year,
                             month      => $mon,
                             day        => $day,
                             hour       => $hour,
                             minute     => $min,
                             second     => 0);
    my $now = DateTime->now;
    if (   $now->subtract_datetime($date)->in_units('months') == 0
        && $now->subtract_datetime($date)->in_units('days') < 7) {
        # Keep last seven days (including current day).
        syslog('debug', "Keeping %s (one-per-day within last week)", $backup);
    } elsif ($now->subtract_datetime($date)->in_units('months') < 1 && ($date->day % 7) == 1) {
        # Keep one a week for last month.
        # Pick that one such that the first of the month will one of the ones
        # kept; this minimizes duplication.
        syslog('debug', "Keeping %s (one-per-week within last month)", $backup);
    } elsif ($now->subtract_datetime($date)->in_units('months') < 4 && $date->day == 1) {
        # Keep start of last four months.
        syslog('debug', "Keeping %s (one-per-month within last 4 months)", $backup);
    } else {
        $n_removed ++;
        syslog('debug', "Removing %s", $backup);
        if (!unlink($backup)) {
            syslog('warning', "Unable to delete %s: %s", $backup, $!);
        }
    }
}

syslog('info', "Removed %d old database backups", $n_removed);