@@ -1,4 +1,4 @@ |
-{attachment id=1059 float=right} This is a permutation of the original pg_backup script as written by speedboy_420. I have made changes to allow for environment variables to make backups of a remote host, change backup direct via ENV variable, as well as use bzip2 for all compression.
|
+{attachment id=1059 float=right} This is a permutation of the original pg_backup script as written by speedboy_420. I have made changes to allow for environment variables to make backups of a remote host, change backup directory via ENV variable, as well as use bzip2 for all compression. |
|
There is also a [https://gist.github.com/546865/117f1831b695bf5f0c8a60d093aa8f00dceca04c|github gist repo] for this file.
|
|
|
|
@@ -9,8 +9,17 @@ |
|
{code source="bash"}
|
0 3 * * * postgres export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
|
-0 4 * * * postgres export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
|
-{/code}
|
+0 4 * * * postgres export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1{/code} |
+ |
+If you want to clear out older backups, you can use these two entries in /etc/cron.d/backup |
+{code source="bash"} |
+# Remove backups older than 1 week except those made on the 9th, 19th, and 29th |
+5 4 * * * root find /bak/database/ -mtime +7 -name "*_db-*[123456780]-0[3-4]00*" -exec rm {} \; |
+# Remove backups older than 60 days except those made on the 9th of the month |
+5 4 * * * root find /bak/database/ -mtime +60 -name "*_db-*[12]9-0[3-4]00*" -exec rm {} \;{/code} |
+ |
+Restoring a database looks like:{code source="bash"} |
+bzcat /tmp/pg_db-localhost-foobar-backup-2011-03-14-0300.bz2 |nice pg_restore --verbose -U postgres -F t -d foobar{/code} |
|
{code source="bash"}
|
#!/bin/sh
|
|
|
@@ -276,9 +285,9 @@ |
db_connectivity() {
|
tmp=`echo "($exclusions)" | sed 's/\ /\|/g'`
|
if [ "$exclusions" = "" ]; then
|
- databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
|
+ databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "SELECT datname FROM pg_database WHERE datistemplate = false" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR` |
else
|
- databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
|
+ databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "SELECT datname FROM pg_database WHERE datistemplate = false" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR` |
fi
|
}
|
|
|
|
@@ -314,7 +323,7 @@ |
duration=`expr $finish_time - $start_time`
|
echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
|
done
|
- exit 1
|
+ exit 0 |
}
|
|
# Run backup and vacuum
|
|
|
@@ -335,7 +344,7 @@ |
duration=`expr $finish_time - $start_time`
|
echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
|
done
|
- exit 1
|
+ exit 0 |
}
|
|
# Run backup, vacuum and analyze
|
|
|
@@ -356,7 +365,7 @@ |
duration=`expr $finish_time - $start_time`
|
echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
|
done
|
- exit 1
|
+ exit 0 |
}
|
|
# Run vacuum
|
|
|
@@ -370,7 +379,7 @@ |
duration=`expr $finish_time - $start_time`
|
echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
|
done
|
- exit 1
|
+ exit 0 |
}
|
|
# Run vacuum and analyze
|
|
|
@@ -384,7 +393,7 @@ |
duration=`expr $finish_time - $start_time`
|
echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
|
done
|
- exit 1
|
+ exit 0 |
}
|
|
# Print information regarding available backups
|
|
|
@@ -406,7 +415,7 @@ |
done
|
echo ""
|
fi
|
- exit 1
|
+ exit 0 |
}
|
|
# Print configuration test
|
|
|
@@ -515,7 +524,7 @@ |
test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"
|
|
echo ""
|
- exit 1
|
+ exit 0 |
}
|
|
# Print help
|
|
|
@@ -535,7 +544,7 @@ |
echo " help, This message"
|
echo ""
|
echo "Report bugs to <speedboy_420 at hotmail dot com >."
|
- exit 1
|
+ exit 0 |
}
|
|
case "$1" in
|
|
|
@@ -544,14 +553,14 @@ |
db_connectivity
|
set_permissions
|
run_b
|
- exit 1
|
+ exit 0 |
;;
|
# Run backup and vacuum
|
bv)
|
db_connectivity
|
set_permissions
|
run_bv
|
- exit 1
|
+ exit 0 |
;;
|
|
# Run backup, vacuum and analyze
|
|
|
@@ -559,7 +568,7 @@ |
db_connectivity
|
set_permissions
|
run_bva
|
- exit 1
|
+ exit 0 |
;;
|
|
# Run vacuum
|
|
|
@@ -567,7 +576,7 @@ |
db_connectivity
|
set_permissions
|
run_v
|
- exit 1
|
+ exit 0 |
;;
|
|
# Run vacuum and analyze
|
|
|
@@ -575,14 +584,14 @@ |
db_connectivity
|
set_permissions
|
run_va
|
- exit 1
|
+ exit 0 |
;;
|
|
# Print info
|
info)
|
set_permissions
|
print_info
|
- exit 1
|
+ exit 0 |
;;
|
|
# Print configtest
|
|
|
@@ -590,13 +599,13 @@ |
db_connectivity
|
set_permissions
|
print_configtest
|
- exit 1
|
+ exit 0 |
;;
|
|
# Default
|
*)
|
print_help
|
- exit 1
|
+ exit 0 |
;;
|
esac
|
{/code} |
|
|