Re: [GENERAL] Backup, Vacuume scheduling tips? - Mailing list pgsql-general

I've been asked to post my collection of scripts for backups and
vacuuming and housekeeping to the list. Apologies for any errors or
inconsistencies. They are for a Linux system, RedHat 6.1, as it
happens, but I think they should work on most Linux systems.Apologies
for some of the lineation.

Backups:

NB, this is a server running under a light load with a fairly small
database (c. 20 MB per pg_dump), therefore backs up every two hours in
the daytime using cron if the database is not doing anything else.

moray is the postgres super user - obviously you could use postgres
instead. Super user must be set up with $PGUSER and $PGPASSWORD
environment variables.

The schema is also backed up separately once per day. The only
database I am interested in is called maint. I have a structure of
backup directories which should be self-explanatory:

$HOME/moray/maint/backup/longterm/
$HOME/moray/maint/backup/lastweek/
$HOME/moray/maint/backup/thisweek/
$HOME/moray/maint/backup/today/

The system does some processing so that only one backup per day is
recorded into the longterm hierarchy, and these are all tarred and
zipped to save disk space.

However, if you only needed to backup once a day or backed up to tape,
you could have everything a good deal simpler.

Suggestions for improvements welcome.

------------------------------------------
Moray.McConnachie@computing-services.oxford.ac.uk


bi-hourly backup script:
-----------------------------------------
#!/bin/bash
# is /etc/cron.bi-hourly/backupmaintdb
# set up by using /etc/crontab

if /root/bin/testforactivemaintconns.pl; then
    su moray -l -c "/usr/local/pgsql/bin/pg_dump maint >
/home/moray/maint/backup/today/maintdb.`date +%s`";
else echo "Cannot backup maintdb, something going not idle...";
fi
-----------------------


The script to see whether the database is busy
---------------------------------------------------
#!/usr/bin/perl -w
# is /root/bin/testforactivemaintconns.pl

my ($failure);
$failure=0;

open (PSPLEASE,"/bin/ps awx|grep postgres|") || die "cannot open ps";
#get current processes involving postgres

LUPE: while (<PSPLEASE>) {

    unless (/grep/)

#ignore grep process !
 unless (/idleBUTNOMORE/) {
#if you don't mind the backup running if the database has an idle
connection, then remove BUTNOMORE
     $failure=1;
     last LUPE;}
    }
}
close PSPLEASE;
exit $failure;
----------------------------------------


Daily script to sort out backups and housekeeping.
----------------------------------------------------------------------

#!/bin/bash
# is /etc/cron.daily/vacuum-and-backup-maintdb

#move all backup files from today hierarchy to this week hierarchy
cd /home/moray/maint/backup/today
mv maintdb.* ../thisweek/

#if no-one's using the database, start the process
if /root/bin/testforactivemaintconns.pl; then
# your standard postgresql stop script - mine is modification of
Redhat RPM's one
    /etc/rc.d/init.d/postgresql stop
# a script starting postgresql only allowing local connections
    /etc/rc.d/init.d/postgresqllocal start
# drop and recreate all indices - see below for script
# you may not have to do this, but i have had some index corruption
problems
    su moray -l -c "/home/moray/bin/restoreindices";
#vacuum database - see below for script
    su moray -l -c "/home/moray/bin/vacuummaintdatabase";
# dump database schema - all on one line
    su moray -l -c "/usr/local/pgsql/bin/pg_dump -s maint >
/home/moray/maint/backup/thisweek/maintdb.schema.`date +%s`";

# stop local postgresql service
    /etc/rc.d/init.d/postgresqllocal stop;
# start general postgresql service again
    /etc/rc.d/init.d/postgresql start;

else echo "Cannot process maintdb, something going not idle...";
fi
-----------


Local postgresql starter - rather redhat specific, but all you really
need is to start postmaster without the -i switch
----------------------------------------------------------------
#! /bin/sh
# postgresql This is the init script for starting up the PostgreSQL
#  server, locally only
# is /etc/rc.d/init.d/postgresqllocal
#
# chkconfig: 345 85 15
# description: Starts and stops the PostgreSQL backend daemon that
handles \
#        all database requests.
# processname: postmaster
# pidfile: /var/run/postmaster.pid
#

# Source function library.
. /etc/rc.d/init.d/functions

# Get config.
. /etc/sysconfig/network

# Check that networking is up.
# Pretty much need it for postmaster.
[ ${NETWORKING} = "no" ] && exit 0

[ -f /usr/local/pgsql/bin/postmaster ] || exit 0

# This script is slightly unusual in that the name of the daemon
(postmaster)
# is not the same as the name of the subsystem (postgresql)

# See how we were called.
case "$1" in
  start)
 echo -n "Starting postgresql service: "
 su postgres -c '/usr/local/pgsql/bin/postmaster -B
256 -S -D/usr/local/pgsql/data -o "-e -S 4096"'
 sleep 1
 pid=`pidof postmaster`
 echo "postmaster [$pid]... "
 touch /var/lock/subsys/postgresql
 echo $pid > /var/run/postmaster.pid
 echo
 ;;
  stop)
 echo -n "Stopping postgresql service: "
 killproc postmaster
 sleep 2
 rm -f /var/run/postmaster.pid
 rm -f /var/lock/subsys/postgresql
 echo
 ;;
  status)
 status postmaster
# status oplrqb
 ;;
  restart)
 $0 stop
 $0 start
 ;;
  *)
 echo "Usage: postgresql {start|stop|status|restart}"
 exit 1
esac

exit 0
----------------



Script for dropping and recreating all indices
----------------------------------------------

#!/bin/sh
# is /home/moray/bin/restoreindices
cd /tmp/
rm -f indices.*
# i find pg_dump with the s switch invaluable
pg_dump -s maint > indices
# remove all lines not to do with creating indices
perl -pi -e 'unless (/CREATE.*INDEX/) {s/.*//;chomp;}' indices
# process all create index lines to drop index lines
perl -pi.create -e 's/CREATE.*?INDEX(.*?\s)ON.*/DROP INDEX$1\;/i;'
indices
mv indices indices.drop
#drop all indices
psql -d maint -c '\i indices.drop'
# create all indices
psql -d maint -c '\i indices.create'
------


Vacuum script
-------------------
#!/bin/sh
# is /home/moray/bin/vacuummaintdatabase
# all on one line
psql -d maint -c "vacuum verbose analyze;" >>
/home/moray/maint/logs/vacuum.log.`date +%j` 2>&1
-----------

Weekly script for sorting out backup files
---------------------------------
#!/bin/bash
# is /etc/cron.weekly/sortoutmaintdbbackups.cron
cd /home/moray/maint/backup/lastweek
# list all files for the week before the one just gone and take the
latest one for each day
# tar it up and give it a filename that identifies it by week of year
# all on one line -
ls maintdb.* -1 |
/root/bin/maint.getonefileperdayfrombackups.calledbycron |
tar --create --file=../longterm/maint`date +%Y`wk`date
+%W`.tar.gz --gzip --files-from=-

# bundle up all the schema files too, but only one per day, so no
picking required
ls maintdb.schema.* -1 | tar --create --file=../longterm/schema`date
+%Y`wk`date +%W`.tar.gz --gzip --files-from=-

rm -f *
cd /home/moray/maint/backup/today
mv * ../thisweek/
cd /home/moray/maint/backup/thisweek/
mv * ../lastweek/
---------------------

Script for picking one filename from a list of STDIN, and picking the
latest modified.
----------------------
#!/usr/bin/perl
# is /root/bin/maint.getonefileperdayfrombackups.calledbycron
while (<>) {
    chomp;
    $mtime = (stat($_))[9];
    $key = int($mtime / 86400);
    $time = $mtime % 86400;
    if ($max{$key}->[0] < $time) {
        $max{$key} = [$time, $_];
    }
}
while (($key, $value) = each %max) {
    print "$value->[1]\n";
}
--------------