published my offsite backup method for postgres

1 files changed, 191 insertions, 0 deletions

diff --git a/src/pg-backup.txt b/src/pg-backup.txt
new file mode 100644
index 0000000..e1b6b10
--- /dev/null
+++ b/src/pg-backup.txt
@@ -0,0 +1,191 @@
+When it comes to backups I'm paranoid and lazy. That means I need to automate the process of making redundant backups. 
+
+Pretty much everything to do with luxagraf lives in a single PostgreSQL database that gets backed up every night. To make sure I have plenty of copies of those backup files I download them to various other machines and servers around the web. That way I have copies of my database files on this server, another backup server, my local machine, several local backup hard drives, in Amazon S3 and Amazon Glacier. Yes, that's overkill, but it's all so ridiculously easy, why not? 
+
+Here's how I do it. 
+
+## Make Nightly Backups of PostgreSQL with `pg_dump` and `cron`
+
+The first step is to regularly dump your database. To do that PostgreSQL provides the handy `pg_dump` command. If you want a good overview of `pg_dump` check out the excellent [PostgreSQL manual]. Here's the basic syntax:
+
+~~~~console
+pg_dump -U user -hhostname database_name > backup_file.sql
+~~~~
+
+So, if you had a database named mydb and you wanted to back it up to a file that starts with the name of the database and then includes today's date, you could do something like this:
+
+~~~~console
+pg_dump -U user -hlocalhost mydb > mydb.`date '+%Y%m%d'`.sql
+~~~~
+
+That's pretty useful, but it's also potentially quite a big file. Thankfully we can just pipe the results to gzip to compress them:
+
+~~~~console
+pg_dump -U user -hlocalhost mydb | gzip -c > mydb.`date '+%Y%m%d'`.sql.gz
+~~~~
+
+That's pretty good. In fact for many scenarios that's all you'll need. Plug that into your cron file by typing `crontab -e` and adding this line to make a backup every night at midnight:
+
+~~~~bash
+0 0 * * * pg_dump -U user -hlocalhost mydb | gzip -c > mydb.`date '+%Y%m%d'`.sql
+~~~~
+
+For a long time that was all I did. But then I started running a few other apps that used PostgreSQL databases (like a version [Tiny Tiny RSS](https://tt-rss.org/gitlab/fox/tt-rss/wikis/home)), so I needed to have quite a few lines in there. Plus I wanted to perform a [VACUUM](http://www.postgresql.org/docs/current/static/sql-vacuum.html) on my main database every so often. So I whipped up a shell script. As you do. 
+
+Actually most of this I cobbled together from sources I've unfortunately lost track of since. Which is to say I didn't write this from scratch. Anyway here's the script I use:
+
+~~~~base
+#!/bin/bash
+#
+# Daily PostgreSQL maintenance: vacuuming and backuping.
+#
+##
+set -e
+for DB in $(psql -l -t -U postgres -hlocalhost |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]|postgres|\|'); do
+  echo "[`date`] Maintaining $DB"
+  echo 'VACUUM' | psql -U postgres -hlocalhost -d $DB
+  DUMP="/path/to/backup/dir/$DB.`date '+%Y%m%d'`.sql.gz"
+  pg_dump -U postgres -hlocalhost $DB | gzip -c > $DUMP
+  PREV="$DB.`date -d'1 day ago' '+%Y%m%d'`.sql.gz"
+  md5sum -b $DUMP > $DUMP.md5
+  if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then
+    rm $DUMP $DUMP.md5
+  fi
+done
+~~~~
+
+Copy that code and save it in a file named psqlback.sh. Then make it executable:
+
+~~~~console
+chmod u+x psqlback.sh
+~~~~
+
+Now before you run it, let's take a look at what's going on.
+
+First we're creating a loop so we can backup all our databases.
+
+~~~~bash
+for DB in $(psql -l -t -U postgres -hlocalhost |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]|postgres|\|'); do
+~~~~
+
+This looks complicated because we're using `awk` and `grep` to parse some output but basically all it's doing is querying PostgreSQL to get a list of all the databases (using the `postgres` user so we can access all of them). Then we pipe that to `awk` and `grep` to extract the name of each database and ignore a bunch of stuff we don't want. 
+
+Then we store the name of database in the variable `DB` for the duration of the loop. 
+
+Once we have the name of the database, the script outputs a basic logging message that says it's maintaining the database and then runs VACUUM. 
+
+The next two lines should look familiar:
+
+~~~~bash
+DUMP="/path/to/backup/dir/$DB.`date '+%Y%m%d'`.sql.gz"
+pg_dump -U postgres -hlocalhost $DB | gzip -c > $DUMP
+~~~~
+
+That's very similar to what we did above, I just stored the file path in a variable because it gets used again. The next thing we do is grab the file from yesterday:
+
+~~~~bash
+PREV="$DB.`date -d'1 day ago' '+%Y%m%d'`.sql.gz"
+~~~~
+
+Then we calculate the md5sum of our dump:
+
+~~~~bash
+md5sum -b $DUMP > $DUMP.md5
+~~~~
+
+The we compare that to yesterday's sum and if they're the same we delete our dump since we already have a copy.
+
+~~~~bash
+  if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then
+    rm $DUMP $DUMP.md5
+  fi
+~~~~
+
+Why? Well, there's no need to store a new backup if it matches the previous one exactly. Since sometimes nothing changes on this site for a few days, weeks, months even, this can save a good bit of space.
+
+Okay now that you know what it does, let's run it:
+
+~~~~console
+./psqlback.sh
+~~~~
+
+If everything went well it should have asked you for a password and then printed out a couple messages about maintaining various databases. That's all well and good for running it by hand, but who is going to put in the password when cron is the one running it?
+
+### Automate Your Backups with `cron`
+
+First let's set up cron to run this script every night around midnight. Open up crontab:
+
+~~~~console
+crontab -e
+~~~~
+
+Then add a line to call the script every night at 11:30PM:
+
+~~~~console
+30 23 * * * ./home/myuser/bin/psqlback.sh > psqlbak.log
+~~~~
+
+You'll need to adjust the path to match your server, but otherwise that's all you need (if you'd like to run it less frequently or at a different time, you can read up on the syntax in the cron manual).
+
+But what happens when we're not there to type in the password? Well, the script fails.
+
+There are a variety of ways we can get around this. In fact the [PostgreSQL docs](http://www.postgresql.org/docs/current/static/auth-methods.html) cover everything from LDAP auth to peer auth. The latter is actually quite useful, though a tad bit complicated. I generally use the easiest method -- a password file. The trick to making it work for cron jobs is to create a file in your user's home directory called `.pgpass`.
+
+Inside that file you can provide login credentials for any user on any port. The format looks like this:
+
+~~~~vim
+hostname:port:username:password
+~~~~
+
+You can use * as a wildcard if you need it. Here's what I use:
+
+~~~~vim
+localhost:*:*:postgres:passwordhere
+~~~~
+
+I hate storing a password in the plain text file, but I haven't found a better way to do this. 
+
+To be fair, assuming your server security is fine, the `.pgpass` method should be fine. Also note that Postgres will ignore this file if it has greater than 600 permissions (that is, user is the only one that can execute it. Let's change that so that:
+
+~~~~console
+chmod 600 .pgpass
+~~~~
+
+Now we're all set. Cron will run our script every night at 11:30 PM and we'll have a compressed backup file of all our PostgreSQL data.
+
+## Automatically Moving It Offsite
+
+Now we have our database backed up to a file. That's a start. That saves us if PostgreSQL does something wrong or somehow becomes corrupted. But we still have a single point of failure -- what if the whole server crashes and can't be recovered? We're screwed.
+
+To solve that problem we need to get our data off this server and store it somewhere else. 
+
+There's quite a few ways we could do this and I have done most of them. For example we could install [s3cmd](http://s3tools.org/s3cmd) and send them over to an Amazon S3 bucket. I actually do that, but it requires you pay for S3. In case you don't want to do that, I'm going to stick with something that's free -- Dropbox.
+
+Head over to the Dropbox site and follow their instructions for [installing Dropbox on a headless Linux server](https://www.dropbox.com/en/install?os=lnx). It's just one line of cut and pasting though you will need to authorize Dropbox with your account.
+
+**BUT WAIT**
+
+Before you authorize the server to use your account, well, don't. Go create a second account solely for this server. Do that, then authorize that new account for this server. 
+
+Now go back to your server and symlink the folder you put in the script above, into the Dropbox folder.
+
+~~~~console
+cd ~/Dropbox
+ln -s ~/path/to/pgbackup/directory .
+~~~~
+
+Then go back to Dropbox log in to the second account, find that database backup folder you just symlinked in and share it with your main Dropbox account. 
+
+This way, should something go wrong and the Dropbox folder on your server becomes compromised at least the bad guys only get your database backups and not the entirety of your documents folder or whatever might be in your normal Dropbox account. 
+
+Credit to [Dan Benjamin](http://hivelogic.com/), who's first person I heard mention this dual account idea.
+
+The main thing to note about this method is that you're limited to 2GB of storage (the max for a free Dropbox account). That's plenty of space in most cases. Luxagraf has been running for more than 10 years, stores massive amounts of geodata in PostgreSQL, along with close to 1000 posts of various kinds, and a full compressed DB dump is still only about 35MB. So I can store well over 60 days worth of backups, which is plenty for my purposes (in fact I only store about half that).
+
+So create your second account, link your server installation to that and then share that folder with your main Dropbox account. 
+
+The last thing I suggest you do, because Dropbox is not a backup service, but a syncing service, is **copy** the backup files out of the Dropbox folder on your local machine to somewhere else on that machine. Not move, but **copy**. So leave a copy in Dropbox and make a second copy on your local machine outside of the Dropbox folder.
+
+If you dislike Dropbox (I don't blame you, I no longer actually use it for anything other than this) there are other ways to accomplish the same thing. The already mentioned s3cmd could move your backups to Amazon S3, good old `scp` could move them to another server and of course you can always download them to your local machine using `scp` or `rsync` (or SFTP, but then that wouldn't be automated).
+
+Naturally I recommend you do all these things. I sync my nightly backups to my local machine with Dropbox and `scp` those to a storage server. Then I use s3cmd to send weekly backups to S3. That gives me three offsite backups which is enough even for my paranoid, digitally distrustful self.