1#!/bin/sh
2#
3# Maintenance shell script to vacuum and backup database
4# Put this in /usr/local/etc/periodic/daily, and it will be run
5# every night
6#
7# Written by Palle Girgensohn <girgen@pingpong.net>
8#
9# In public domain, do what you like with it,
10# and use it at your own risk... :)
11#
12
13# Define these variables in either /etc/periodic.conf or
14# /etc/periodic.conf.local to override the default values.
15#
16# daily_pgsql_backup_enable="YES" # do backup of all databases
17# daily_pgsql_backup_enable="foo bar db1 db2" # only do backup of a limited selection of databases
18# daily_pgsql_vacuum_enable="YES" # do vacuum
19
20# If there is a global system configuration file, suck it in.
21#
22if [ -r /etc/defaults/periodic.conf ]
23then
24    . /etc/defaults/periodic.conf
25    source_periodic_confs
26fi
27
28: ${daily_pgsql_user:="%%PG_USER%%"}
29: ${daily_pgsql_port:=5432}
30: ${daily_pgsql_vacuum_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -qaz"}
31: ${daily_pgsql_pgdump_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -bF c"}
32: ${daily_pgsql_pgdumpall_globals_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port}"}
33# backupdir is relative to ~pgsql home directory unless it begins with a slash:
34: ${daily_pgsql_backupdir:="~${daily_pgsql_user}/backups"}
35: ${daily_pgsql_savedays:="7"}
36
37# allow '~' in directory name
38eval backupdir=${daily_pgsql_backupdir}
39
40rc=0
41
42pgsql_backup() {
43	# daily_pgsql_backupdir must be writeable by user %%PG_USER%%
44	# ~%%PG_USER%% is just that under normal circumstances,
45	# but this might not be where you want the backups...
46	if [ ! -d ${backupdir} ] ; then
47	    echo Creating ${backupdir}
48	    mkdir -m 700 ${backupdir}; chown ${daily_pgsql_user} ${backupdir}
49	fi
50
51	echo
52	echo "PostgreSQL backups"
53
54	# Protect the data
55	umask 077
56	rc=$?
57	now=`date "+%Y-%m-%dT%H:%M:%S"`
58	file=${daily_pgsql_backupdir}/pgglobals_${now}
59	su -l ${daily_pgsql_user} -c \
60		"umask 077; pg_dumpall -g ${daily_pgsql_pgdumpall_globals_args} | gzip -9 > ${file}.gz"
61
62	db=$1
63	while shift; do
64	    echo -n " $db"
65	    file=${backupdir}/pgdump_${db}_${now}
66	    su -l ${daily_pgsql_user} -c "umask 077; pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}"
67	    [ $? -gt 0 ] && rc=3
68		db=$1
69	done
70
71	if [ $rc -gt 0 ]; then
72	    echo
73	    echo "Errors were reported during backup."
74	fi
75
76	# cleaning up old data
77	find ${backupdir} \( -name 'pgdump_*' -o -name 'pgglobals_*' -o -name '*.dat.gz' -o -name 'toc.dat' \) \
78	    -a -mtime +${daily_pgsql_savedays} -delete
79	echo
80}
81
82case "$daily_pgsql_backup_enable" in
83    [Yy][Ee][Ss])
84	dbnames=`su -l ${daily_pgsql_user} -c "umask 077; psql -U ${daily_pgsql_user} -p ${daily_pgsql_port} -q -t -A -d template1 -c SELECT\ datname\ FROM\ pg_database\ WHERE\ datname!=\'template0\'"`
85	pgsql_backup $dbnames
86	;;
87
88	[Nn][Oo])
89	;;
90
91	"")
92	;;
93
94	*)
95	pgsql_backup $daily_pgsql_backup_enable
96	;;
97esac
98
99case "$daily_pgsql_vacuum_enable" in
100    [Yy][Ee][Ss])
101
102	echo
103	echo "PostgreSQL vacuum"
104	su -l ${daily_pgsql_user} -c "vacuumdb ${daily_pgsql_vacuum_args}"
105	if [ $? -gt 0 ]
106	then
107	    echo
108	    echo "Errors were reported during vacuum."
109	    rc=3
110	fi
111	;;
112esac
113
114exit $rc
115