Line # Revision Author
1 17 dpavlin #!/bin/sh
2
3 dir=/data/sql-backup
4
5 function do_dump () {
6 echo -n `date +"%Y-%m-%d %H:%M:%S"` $db_name
7 19 dpavlin # WARNING: this might be overkill!
8 17 dpavlin psql -q -c "vacuum full analyze" $1
9 20 dpavlin test ! -d "$dir/$1" && ( mkdir "$dir/$1" || exit 1 )
10 17 dpavlin file=$dir/$1/`date +%d`.sql.gz
11 /usr/bin/pg_dump -i $1 | gzip > $file
12 gzip -l $file | tail -1 | sed 's/ */ /g' | cut -d" " -f-4
13 }
14
15 psql -c "select datname from pg_database where not datistemplate and datname not in ('phpOpenTracker','') order by datname" -A -t template1 | while read db_name
16 do
17 #echo $db_name
18 do_dump $db_name
19 done
20
21 ## sync databases (poor man's replication)
22 #
23 #(
24 # echo "BEGIN;"
25 #
26 # echo "delete from open_positions;"
27 # pg_dump --data-only --no-reconnect -t open_positions -U dpavlin --no-owner --inserts corp
28 #
29 # echo "delete from open_position_locations;"
30 # pg_dump --data-only --no-reconnect -t open_position_locations -U dpavlin --no-owner --inserts corp
31 #
32 # echo "delete from open_positions_company;"
33 # pg_dump --data-only --no-reconnect -t open_positions_company -U dpavlin --no-owner --inserts corp
34 #
35 # echo "COMMIT;"
36 #) | psql -q -h erec.pliva.hr -U dpavlin erec
37