I looked at generating files with timestamps and then doing some calculations about cleaning up old files but that just seemed messy and I was sure that someone must have run into this type of situation before. Then I thought about log files and how they are backed up. It turns out the the standard Linux
logrotatecommand knows how to rename files and get rid of old ones automagically. It even has script functionality that allows you to run commands before and after the rotate happens. Sounds perfect. Here's what I did.
First I created a directory in my web apps directory to hold all the database information and backup files. I'm using PostgreSQL, but you could do the same thing for MySQL or even Oracle. Just change the names of the directories and commands to something more suitable for your environment.
$ mkdir /var/www/postgres
Next we have to create a status file for
logrotateto use. Since we will be running this as a non-root user we don't want to pollute the system status file with our non-log database backup stuff.
$ touch /var/www/postgres/logrotate.status
We also want to create a "fake" dump file so that
logrotatewill have something to start working with. If this file doesn't exist
logrotatewill refuse to rotate it. In this case we are backing up a Postgres database called "mydatabase".
$ touch /var/www/postgres/mydatabase.dump
Now we get to the heart of the matter; the logroate script itself. This tells
logrotateto keep 10 copies of our dump but don't compress it and dont copy it. It will also complain if there is no original file to backup and it will create a blank "dump" for next time.
# Dump PostgreSQL databases and prepare them for backup
test -x /usr/bin/pg_dump || exit 0
/usr/bin/pg_dump mydatabase -F c > /var/www/postgres/mydatabase.dump
One final step and we're done. The
pg_dumpcommand connects to the database as the user who runs it and the postgres user has access to all the databases by default (on Debian anyway). So running the
logrotatecommand as the postgres user allows us to backup any database on the system without a password. And in order to allow the postgres user access to our new setup we have to change the owner on the files in /var/www/postgres.
$ chown -R postgres:postgres /var/www/postgres
Now, whenever you want to generate a new database dump just run the following command. You can put this is a crontab or just run it by hand. Remember to use the
-fflag to force the backup to happen.
$/usr/sbin/logrotate -f /var/www/postgres/logrotate.pg_dump -s /var/www/postgres/logrotate.status