Running backup scripts overnight or more often is an easy thing to set up and will give you extensive peace of mind. Of course a more enterprise level solution would be warm standbys etc but for most databases, a simple cron job backup script will do the trick. The standard postgres data dump is as follows:
pg_dump DB_NAME > YOUR_FILE_NAME.sql
Which is pretty simple.
But how can we get this into a backup script which runs overnight.
1) Create a new file:
nano backup.py
2) Use something similar to the following.
#! /usr/bin/python
from time import gmtime, strftime
import subprocess
# Import the required things
DB_NAME = "your_database_name"
user = "postgres"
#start
print strftime("%Y-%m-%d-%H-%M-%S", gmtime()) + ":dump started"
time = str(strftime("%m-%d-%H-%M"))
#Run the pg_dump command to the right directory
dumper = """pg_dump -U postgres %s > /var/backup/%s%s.sql""" % (DB_NAME,DB_NAME,time)
subprocess.call(dumper,shell=True)
#end
print strftime("%Y-%m-%d-%H-%M-%S", gmtime()) + ":finished"
If you want to backup multiple databases just make a list and run the script around a for loop.
listofdbs = ['db1','db2']
for row in listofdbs:
print str...etc
This will name the output files in a logical manner, so you can leave the backup script running and as long as you don’t run out of disk space you’re all good. A good solution would be to have a deletion script running too which removes files older than, say, one week from your backup directory.
crontab -e
0 3 * * * /var/lib/zope/backup/backup.py
Related posts: