Merge lp:~barryprice/charms/trusty/postgresql/more_frequent_backups into lp:charms/trusty/postgresql

[Barry Price]

[Barry Price (barryprice)]

As stub points out, there will usually be at least two backup files per run, potentially more (basically, $number_of_databases +1 for globals).

So the pruning of old backups needs some work here, I'm setting this to WIP for now.

[lp:~barryprice/charms/trusty/postgresql/more_frequent_backups]

145. By Barry Price on 2016-01-26

Another attempt at getting the DB deletions correct

146. By Barry Price on 2016-01-26

Correct syntax

[Stuart Bishop (stub)]

Looks good.

review: Approve

[Review Queue (review-queue)]

This item has failed automated testing! Results available here http://juju-ci.vapour.ws:8080/job/charm-bundle-test-lxc/2273/

review: Needs Fixing (automated testing)

[Review Queue (review-queue)]

The results (PASS) are in and available here: http://juju-ci.vapour.ws:8080/job/charm-bundle-test-aws/2252/

review: Approve (automated testing)

=== modified file 'config.yaml'
--- config.yaml	2015-12-30 09:05:19 +0000
+++ config.yaml	2016-01-26 10:45:35 +0000
@@ -124,11 +124,11 @@
   backup_schedule:
     default: "13 4 * * *"
     type: string
-    description: Cron-formatted schedule for daily database backups.
+    description: Cron-formatted schedule for regular database backups.
   backup_retention_count:
     default: 7
     type: int
-    description: Number of daily backups to retain.
+    description: Number of backups to retain.
   nagios_context:
     default: "juju"
     type: string

=== modified file 'hooks/postgresql.py'
--- hooks/postgresql.py	2015-10-01 01:36:42 +0000
+++ hooks/postgresql.py	2016-01-26 10:45:35 +0000
@@ -132,6 +132,7 @@
     ver = version()
     return set(['postgresql-{}'.format(ver),
                 'postgresql-common', 'postgresql-client-common',
+                'run-one',
                 'postgresql-contrib-{}'.format(ver),
                 'postgresql-client-{}'.format(ver)])
 

=== modified file 'scripts/pgbackup.py'
--- scripts/pgbackup.py	2014-03-11 13:37:56 +0000
+++ scripts/pgbackup.py	2016-01-26 10:45:35 +0000
@@ -1,11 +1,11 @@
 #!/usr/bin/python
 
-# Copyright 2008-2014 Canonical Ltd.  All rights reserved.
+# Copyright 2008-2016 Canonical Ltd.  All rights reserved.
 
 """
 Backup one or more PostgreSQL databases.
 
-Suitable for use in crontab for daily backups.
+Suitable for use in crontab for regular backups.
 """
 
 __metaclass__ = type
@@ -24,9 +24,7 @@
 
 
 def main(options, databases):
-    #Need longer file names if this is used more than daily
-    #today = datetime.now().strftime('%Y%m%d_%H:%M:%S')
-    today = datetime.now().strftime('%Y%m%d')
+    today = datetime.now().strftime('%Y%m%d_%H%M%S')
 
     backup_dir = options.backup_dir
     rv = 0
@@ -80,6 +78,7 @@
         # If the file already exists, it is from an older dump today.
         # We don't know if it was successful or not, so abort on this
         # dump. Leave for operator intervention
+        # n.b. this is extremely unlikely with %H%M%S in the filename
         if os.path.exists(dest):
             log.error("%s already exists. Skipping." % dest)
             continue

=== modified file 'templates/pg_backup_job.tmpl'
--- templates/pg_backup_job.tmpl	2015-08-11 10:36:28 +0000
+++ templates/pg_backup_job.tmpl	2016-01-26 10:45:35 +0000
@@ -6,12 +6,12 @@
 export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
 
 usage () {
-    echo "usage: $0 <retain_days>"
+    echo "usage: $0 <retain_files>"
 }
 
-typeset -i DAYS=$1
+typeset -i FILES=$1
 shift
-if [ $DAYS -le 0 ]; then
+if [ $FILES -le 0 ]; then
     usage
     exit 1
 fi
@@ -27,14 +27,14 @@
 # Set umask so backups are automatically created without group and other
 umask 0077
 
-echo backups started `date`
+echo backups started $(date)
 
 export PGPORT=5432 
 export PGUSER=postgres
 
 DUMP_DIR={{backups_dir}}
 SCRIPTS={{scripts_dir}}
-TODAY=`date +'%Y%m%d'`
+TODAY=$(date +'%Y%m%d_%H%M%S')
 
 # Dump globals, which we often need to restore a database dump.
 pg_dumpall --globals-only --quote-all-identifiers \
@@ -81,9 +81,17 @@
 nice -n 19 ${SCRIPTS}/dump-pg-db --dir=${DUMP_DIR} --compression=postgres ${DATABASES}
 
 # Prune old backups:
-# - Delete anything older than $DAYS days
-find ${DUMP_DIR}/ -mtime +${DAYS} -delete
-
-echo backups completed `date`
+# - Delete anything beyond $FILES count of each backup
+
+# get name/size of the globals dump from $FILES runs ago
+THRESHOLD_GLOBALS_DUMP=$(find ${DUMP_DIR} -type f -name 'globals.*.sql' | sort | tail -${FILES}| head -1)
+TIME_NOW=$(date +%s)
+TIME_DUMP=$(date +%s -r $THRESHOLD_GLOBALS_DUMP)
+THRESHOLD_AGE=$(($TIME_NOW - $TIME_DUMP))
+THRESHOLD_MINS=$(($THRESHOLD_AGE / 60))
+THRESHOLD_MINS=$(($THRESHOLD_MINS + 1)) # always round up
+find ${DUMP_DIR} -type f -mmin +${THRESHOLD_MINS} -delete
+
+echo backups completed $(date)
 
 backup_success

=== modified file 'templates/postgres.cron.tmpl'
--- templates/postgres.cron.tmpl	2015-08-10 11:44:18 +0000
+++ templates/postgres.cron.tmpl	2016-01-26 10:45:35 +0000
@@ -4,10 +4,10 @@
 # Scheduled logical dump of the primary using pg_dump. We don't dump hot
 # standbys, as operations performed on the primary can cause queries on
 # the standby to be aborted.
-{{backup_schedule}} postgres {{scripts_dir}}/pg_backup_job {{backup_retention_count}}
+{{backup_schedule}} postgres run-one {{scripts_dir}}/pg_backup_job {{backup_retention_count}}
 {% endif -%}
 
 {% if is_master and wal_e_enabled and wal_e_backup_schedule -%}
 # Scheduled filesystem backup of the primary using wal-e.
-{{wal_e_backup_schedule}} postgres {{wal_e_backup_command}} && {{wal_e_prune_command}}
+{{wal_e_backup_schedule}} postgres run-one {{wal_e_backup_command}} && {{wal_e_prune_command}}
 {% endif -%}

=== modified file 'tests/test_postgresql.py'
--- tests/test_postgresql.py	2015-08-25 13:50:07 +0000
+++ tests/test_postgresql.py	2016-01-26 10:45:35 +0000
@@ -137,7 +137,7 @@
     def test_packages(self, version):
         version.return_value = '9.9'
         expected = set(['postgresql-9.9', 'postgresql-common',
-                        'postgresql-client-common',
+                        'postgresql-client-common', 'run-one',
                         'postgresql-contrib-9.9', 'postgresql-client-9.9'])
         self.assertSetEqual(postgresql.packages(), expected)