Backup and Restore

Okay, I'll admit it. A few years ago I lost two months' worth of development work when my hard drive crashed. I had not backed up my source code. That was a painful lesson. Fortunately, software is always better the second time you create it. That is not true for most data. Imagine losing two months' worth of customer transactions.

Database backups are critically important. Some types of data can be re-created, but it's usually easier to load an archive tape than to remanufacture lost data.

You may already have a backup plan in place for archiving filesystem data. That may not be a good solution for backing up data hosted in a PostgreSQL database. For one thing, you must shut down the database server if you choose to archive the filesystem.

PostgreSQL provides a set of utilities that you can use to archive and restore individual tables or entire databases. You can use these utilities on a live server (that is, you don't have to shut down the database first). Using the pg_dump and pg_dumpall utilities, you can also compress archive data on-the-fly .

Chapter 19 shows you how to use the pg_dump and pg_dumpall utilities and how to recover data from an archive.

Time for another confession. Not too long ago, I needed to recover some code from an archive that had been created the previous night. (Yes, I did something stupid, and the easiest way to undo it was to restore from backup.) I was surprised to find that, even though an archive had been made the previous night, I could not read from the tape because I had been using the wrong commands to create the archives. It's not enough to have a good backup plan ”test your restore procedures as well.