patch to fix retention in pg_backup_rotated.sh

Dear Contributor

We are using the pg_backup_rotated.sh script and noticed some minor bugs regarding retention, which was keeping more backups than configured.
We used the version of 18 October 2014 on https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux.
Here is a the patch that I wrote to fix the issue:

patch -p0 --ignore-whitespace pg_backup_rotated.sh <<"EOF"
--- pg_backup_rotated.sh.orig 2015-11-17 16:42:03.685678405 +0100
+++ pg_backup_rotated.sh 2016-03-31 15:24:43.468098102 +0200
@@ -95 +95 @@ function perform_backups()
- if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -n public | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
+ if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
@@ -123 +123 @@ function perform_backups()
- if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -n public | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
+ if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
@@ -134 +134 @@ function perform_backups()
- if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -n public -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
+ if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
@@ -145,0 +146 @@ function perform_backups()
+#chg by fsc
@@ -147 +147,0 @@ function perform_backups()
-
@@ -149 +149 @@ DAY_OF_MONTH=`date +%d`
-
+EXPIRED_DAYS=`expr $((($MONTHS_TO_KEEP * 31) - 1))`
@@ -153,2 +153 @@ then
- find $BACKUP_DIR -maxdepth 1 -name "*-monthly" -exec rm -rf '{}' ';'
-
+ find $BACKUP_DIR -maxdepth 1 -daystart -mtime +$EXPIRED_DAYS -name "*-monthly" -exec rm -rf '{}' ';'
@@ -156 +154,0 @@ then
-
@@ -161 +158,0 @@ fi
-
@@ -163,2 +160,5 @@ DAY_OF_WEEK=`date +%u` #1-7 (Monday-Sund
-EXPIRED_DAYS=`expr $((($WEEKS_TO_KEEP * 7) + 1))`
-
+#-mtime N means files whose age A in days satisfies N ≤ A < N+1 i.e. files modified between N and N+1 days ago.
+#-mtime -N means files whose age A in days satisfies A < N i.e. files modified less than N days ago.
+#-mtime +N means files whose age A in days satisfies N+1 ≤ A i.e. files modified at least N+1 days ago.
+#find . -mtime +1 # find files modified more than 48 hours ago ( 48h <= age )
+EXPIRED_DAYS=`expr $((($WEEKS_TO_KEEP * 7) - 1))`
@@ -168,2 +168 @@ then
- find $BACKUP_DIR -maxdepth 1 -mtime +$EXPIRED_DAYS -name "*-weekly" -exec rm -rf '{}' ';'
-
+ find $BACKUP_DIR -maxdepth 1 -daystart -mtime +$EXPIRED_DAYS -name "*-weekly" -exec rm -rf '{}' ';'
@@ -171 +169,0 @@ then
-
@@ -176 +173,0 @@ fi
-
@@ -178,2 +175,2 @@ fi
-find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'
-
+EXPIRED_DAYS=`expr $(($DAYS_TO_KEEP - 1))`
+find $BACKUP_DIR -maxdepth 1 -daystart -mtime +$EXPIRED_DAYS -name "*-daily" -exec rm -rf '{}' ';'
@@ -180,0 +178 @@ perform_backups "-daily"
+#endof
EOF

patch -p0 --ignore-whitespace pg_backup.config <<"EOF"
--- pg_backup.config.orig 2015-10-22 11:11:26.000000000 +0200
+++ pg_backup.config 2016-03-31 15:28:58.285656151 +0200
@@ -6,0 +7 @@
+#BACKUP_USER=readonly
@@ -13 +14 @@ HOSTNAME=localhost
-USERNAME=readonly
+USERNAME=postgres
@@ -17 +18,2 @@ USERNAME=readonly
-BACKUP_DIR=/backup/
+#BACKUP_DIR=/backup/
+BACKUP_DIR=/sftp-chroot/backup/
@@ -33,3 +34,0 @@ ENABLE_PLAIN_BACKUPS=yes
-# Which day to take the weekly backup from (1-7 = Monday-Sunday)
-DAY_OF_WEEK_TO_KEEP=5
-
@@ -37 +36,2 @@ DAY_OF_WEEK_TO_KEEP=5
-DAYS_TO_KEEP=7
+#DAYS_TO_KEEP=7
+DAYS_TO_KEEP=3
@@ -40 +40,4 @@ DAYS_TO_KEEP=7
-WEEKS_TO_KEEP=5
+#WEEKS_TO_KEEP=5
+WEEKS_TO_KEEP=3
+# Which day to take the weekly backup from (1-7 = Monday-Sunday)
+DAY_OF_WEEK_TO_KEEP=5
@@ -41,0 +45,6 @@ WEEKS_TO_KEEP=5
+# How many months to keep monthly backups
+#MONTHS_TO_KEEP=1
+MONTHS_TO_KEEP=2
+# Which day to take the monthly backup from (1-31)
+DAY_OF_MONTH_TO_KEEP=1
+
EOF

I am sending the patch here because I do not have the permission to edit the page https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux .

Best Regards
Francois Scheurer

--

EveryWare AG
François Scheurer
Senior Systems Engineer
Zurlindenstrasse 52a
CH-8003 Zürich

tel: +41 44 466 60 00
fax: +41 44 466 60 10
mail: francois(dot)scheurer(at)everyware(dot)ch
web: http://www.everyware.ch