1 |
#!/bin/sh |
2 |
|
3 |
# This small script will create full backups of your database named like |
4 |
# [database name]-[date].sql |
5 |
# where date is day of current date. This will make one month worth of |
6 |
# backups. It will automatically backup *ALL* databases on system. |
7 |
|
8 |
# directory in which to dump backups |
9 |
dir=/data/sql-backup |
10 |
|
11 |
function do_dump () { |
12 |
echo -n `date +"%Y-%m-%d %H:%M:%S"` $db_name |
13 |
# you might want to comment this if you have different vacuuming policy |
14 |
psql -q -c "vacuum full analyze" $1 |
15 |
file=$dir/$1-`date +%d`.sql.gz |
16 |
/usr/bin/pg_dump -i $1 | gzip > $file |
17 |
gzip -l $file | tail -1 | sed 's/ */ /g' | cut -d" " -f-4 |
18 |
} |
19 |
|
20 |
psql -c "select datname from pg_database where not datistemplate order by datname" -A -t template1 | while read db_name |
21 |
do |
22 |
do_dump $db_name |
23 |
done |
24 |
|