Recovering From a Backup

It is relatively easy to create a backup strategy for your PostgreSQL database. It is much more difficult to actually use those backups. Every database administrator should take the time to restore those backups and test them to make sure they will work as planned. Here are some considerations to take into account as you create your backup strategy:

Your primary database is gone and you have to restore to your hot standby.

Streaming replication is wonderful and it can really save your data. What is your strategy though? Do you trigger your hot standby slave to become your master database? If so, what does that mean for your applications? Will you have to go through a number of servers and configure them to talk to the slave? This seems an ideal situation for PgPool-II but many have discovered that it triggers too many false positives.

The best course of action depends upon your environment. Some may opt to never trigger the hot standby but instead just create a database dump and reload it into the original master once it is rebuilt. It will require setting up your log shipping again, but is quick and simple to do. The only downside is that your database will be offline while you rebuild things. In some environments, that won't work while others allow for a certain amount of down time.

There are a number of ways you can use your hot standby and it is important to have a plan long before you need to use it. Trying to decide the best course of action during the emergency is the worst thing you can do. Practice using your hot standby servers so you know how to trigger them into becoming the master and that your application can use them.

You are using pg_dump and need to reload an entire database.

The pg_dump utility is great for recreating data when a user accidentally deletes a table. What happens when someone deletes an entire database? It is important to remember that pg_dump does not store the CREATE DATABASE commands in the dump file and so you will have to have them handy in the event you need to restore an entire database. Furthermore, when you run the .SQL file back through the psql utility, you will want to make sure you are connected to the right database and not the default one. Otherwise your application may be looking in the wrong place for its data.