Quick PostgreSQL Backup

I recently learned the hard way that brew update can break a PostgreSQL install. Googling around there seem to be a variety of problems and solutions to this scenario.

I was able to get back up and running by relinking my PostgreSQL installation. That seems too easy. I now live with the nagging doubt that something worse is waiting for me in my PostgreSQL install.

To quickly setup a backup process I use the command pg_dumpall in tandem with gzip. pg_dumpall creates a SQL file to recreate my databases and gzip compresses it.

In testing, a pg_dumpall produces a 937M file. Using it with gzip downsizes it to 137M.

Here is the command below. I’ve configured this in my cron jobs to happen every evening Monday through Friday.

pg_dumpall --clean | gzip -c > dev_db.psql.gz

Restoring from backup is just as easy.

gunzip dev_db.psql.gz
psql -f dev_db.psql postgres

Leave a Reply

Your email address will not be published. Required fields are marked *