Blog About Contact

About the Author

Richard Nichols is an Australian software engineer with a passion for making things.

Follow him on twitter or subscribe by RSS or email.

Latest Post

Useful shell scripts: nightly postgresql backup

Published Sun, 11 Jan 2015

Here's a shell script which does a daily backup of a postgresql database and keeps a weeks worth of backups locally.

You can then rsync this folder over ssh from another server or something like that.

backupdb.sh

Run with ./backupdb.sh [databasename] - assuming you have access to the database from your user account.

#!/bin/bash
pg_dump -Fc $1 > /var/lib/pgsql/9.3/backups/$1_$(date +"%Y-%m-%d").dump
find /var/lib/pgsql/9.3/backups/* -mtime +7 -exec rm {} \;

This is a custom format archive which you can use multi-job concurrency to restore in parallel (useful for big databases), e.g.

pg_restore -d myrestoredb -j 8 mydb_2015-01-01.dump

Would restore the dump into myrestoredb with 8 concurrent processors.

Older Posts

Fixing the Home/End Key Behaviour on Mac OSX Wed, 30 Apr 2014
Something that always drives me mad when using OS-X is the way keyboard bindings for basic text editing are different to WindowsLinux PCs. For a developer...
Setting Up iptables for SSH/HTTP/HTTPS for new server install Mon, 7 Apr 2014
Whenever I set up a new cloud server I have to lookup the commands to set up iptables correctly for the three sevices I almost always have switched on - SSH,...
Creating and importing an external certificate to SQL Server Tue, 4 Mar 2014
This was kind of a pain to get working, so I'm logging the steps here. Certificates can be used in SQL Server to authenticate remote communication (e.g. for...
Getting nice font rendering in Netbeans 7 on Ubuntu 12.04 Wed, 21 Aug 2013
I recently switched to Ubuntu 12.04 for my work PC and I'm pretty happy with the results, except for terrible font rendering in Swing applications,...
Diagnosing transactions causing SQL Server's transaction log truncation to fail Mon, 12 Aug 2013
This is one of those "for my future reference" posts.To diagnose which transaction is causing SQL Server's transaction log truncation to fail, the...
Netbeans code template for SLF4J logging Mon, 29 Jul 2013
Here's a neat code template for Netbeans to add a static logger declaration to a class file. private static final Logger log ...
Can't change the font in Netbeans Output Window? Mon, 24 Jun 2013
I just spent 20 minutes trying to figure out why I couldn't change the font in Netbean's output window (which inexplicable uses Java's default system...
Dealing With Static Imports in Netbeans IDE Tue, 29 Jan 2013
Static imports can be quite a nice feature of the Java language for improving code readability and clarity, however they can be a bit of a pain to setup for use...