Revision 20 (by dpavlin, 2006/01/27 11:48:19) fix mkdir with existing directory
 
#!/bin/sh

dir=/data/sql-backup

function do_dump () {
	echo -n `date +"%Y-%m-%d %H:%M:%S"` $db_name
	# WARNING: this might be overkill!
	psql -q -c "vacuum full analyze" $1
	test ! -d "$dir/$1" && ( mkdir "$dir/$1" || exit 1 )
	file=$dir/$1/`date +%d`.sql.gz
	/usr/bin/pg_dump -i $1 | gzip > $file
	gzip -l $file | tail -1 | sed 's/  */ /g' | cut -d" " -f-4
}

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
do
	#echo $db_name
	do_dump $db_name
done

## sync databases (poor man's replication)
#
#(
#	echo "BEGIN;"
#
#	echo "delete from open_positions;"
#	pg_dump --data-only --no-reconnect -t open_positions -U dpavlin --no-owner --inserts corp
#
#	echo "delete from open_position_locations;"
#	pg_dump --data-only --no-reconnect -t open_position_locations -U dpavlin --no-owner --inserts corp
#
#	echo "delete from open_positions_company;"
#	pg_dump --data-only --no-reconnect -t open_positions_company -U dpavlin --no-owner --inserts corp
#
#	echo "COMMIT;"
#) | psql -q -h erec.pliva.hr -U dpavlin erec