Database load times

With all the databases I run, I need a daily sanity check. I download a copy of each database and do a test load on my system at home. Today I timed that process:

$ time ~/bin/backup-supernews.sh
...
real    77m57.323s
user    12m52.610s
sys     0m56.136s

There are 11 such databases. The dump files range in size from 5K (globals.sql) to 2.8G (freshports.sql).

The script that does this is pretty simple, but has evolved over the years.

#!/bin/sh
#
# This file does a backup of each database on the nyi server.
# It relies upon a file on nyi to do the actual backup,
# then uses rsync to copy the files from nyi to here.
#

BACKUPDIR=${HOME}/backups/supernews/database-backup

IDENTITY_FILE=${HOME}/.ssh/id_dsa_no_passphrase
SERVER_TO_BACKUP=supernews.example.org

cd ${BACKUPDIR}/postgresql

YYYYMMDD=`eval date "+%Y.%m.%d"`
echo will tar things up into ${YYYYMMDD} format

FILES=`echo *.sql`
echo will be processing ${FILES}
# maybe there are no such files
if [ "x${FILES}" != 'x\*.sql' ]
then
   for i in $FILES
   do
#      mv ${i} archive
      tar -cvzf archive/`basename ${i} .sql`.${YYYYMMDD}.sql.tgz ${i}
   done
else
   echo no files found for processing
fi


cd ${BACKUPDIR}/mysql
FILES=`echo *.sql`

# maybe there are no such files
if [ "x${FILES}" != 'x\*.sql' ]
then
   for i in $FILES
   do
#      mv ${i} archive
      tar -cvzf archive/`basename ${i} .sql`.${YYYYMMDD}.sql.tgz ${i}
   done
fi

cd ${BACKUPDIR}


#
# dump the databases on the remote server
#
logger "dumping the databases on ${SERVER_TO_BACKUP} from `hostname`"
ssh -i ${IDENTITY_FILE} dan@${SERVER_TO_BACKUP} ~dan/bin/backup.sh

#
# use rsync to get local copies
#
/usr/local/bin/rsync -e "ssh -i ${IDENTITY_FILE}" --recursive -avz --stats --progress \
--exclude 'archive' dan@${SERVER_TO_BACKUP}:~/database-backup/ ${BACKUPDIR}

#
# now do a test of the backups to ensure they actually restore

~/bin/test-backups.sh ${BACKUPDIR}

That is the job that archives the old files and downloads the new ones.

The following script loads up that database and is referred to in the last line of the above script:

#!/bin/sh
#
# test the files we've recovered
#

BACKUPDIR=$1
GLOBALS="globals"

PSQL=/usr/local/bin/psql
CREATEDB=/usr/local/bin/createdb
DROPDB=/usr/local/bin/dropdb

cd ${BACKUPDIR}/postgresql

echo dropping test databases
FILES=`echo *.sql`
for i in $FILES
do
        DBNAME=`basename $i .sql`
        if [ ${DBNAME} != ${GLOBALS} ]
        then
                ${DROPDB} ${DBNAME}_test
        fi
done

echo loading GLOBALS
if [ -r "${GLOBALS}.sql" ]
then
        ${PSQL} postgres < ${GLOBALS}.sql
fi

echo creating the databases
for i in $FILES
do
        DBNAME=`basename $i .sql`
        if [ ${DBNAME} != ${GLOBALS} ]
        then
                ${CREATEDB} ${DBNAME}_test -E SQL_ASCII
                ${PSQL}     --quiet ${DBNAME}_test < ${DBNAME}.sql
        fi
done

The final part of the puzzle is the script run on the production server which dumps all the databases required:

#!/bin/sh

PGDUMP=/usr/local/bin/pg_dump
MYSQLDUMP=/usr/local/bin/mysqldump
BACKUPROOT=${HOME}/database-backup

cd ${BACKUPROOT}/postgresql

DATABASES="bsdcert fpphorum freshports.org fsphorum openx nagios \
pentabarf pentabarf_bsdcan pentabarf_pgcon postgres"
for database in ${DATABASES}
do
        echo dumping $database
        if [ -f ${database}.sql ]
        then
                mv ${database}.sql archive
        fi

        ${PGDUMP} ${database} > ${database}.sql

        if [ $? -ne 0 ]
        then
                echo backup of ${database} failed
        fi

done

#
# Now dump the globals
#

if [ -f globals.sql ]
then
        mv globals.sql archive
fi

/usr/local/bin/pg_dumpall -g  > globals.sql

if [ $? -ne 0 ]
then
        echo backup of globals failed
fi



DATABASES="cacti mysql"
cd ${BACKUPROOT}/mysql

for database in ${DATABASES}
do
    echo dumping $database
    if [ -f ${database}.sql ]
    then
        mv ${database}.sql archive
    fi
    ${MYSQLDUMP} -u root ${database} > ${database}.sql
    if [ $? -ne 0 ]
    then
       echo backup of ${database} failed
    fi
done

Any questions? :)