PostgreSQL: Backing up your data

Backups can be one of the most critical aspects of administering any database, and PostgreSQL is no exception. While the PostgreSQL database is very versatile and resilient, problems can happen. A power failure could occur at worst moment corrupting the database or the hard-drive could fail. You could also have problems with users, authorized or unauthorized, changing or destroying data, we hope by accident, but sometimes malicious people can do bad things when presented the opportunity.

The list really could be limitless, but if you're following ERP Best Practices, then you know the best way to protect yourself is to make regular backups of your database. The depth of the situation you want to safeguard against can dictate your requirements for how often, when, where you store your backups. The first thing is make regular backups. The second is to store them in a safe place. If you are storing your backups on the same server you run your database on it's possible you could lose your database and your backups if the drive fails.

At xTuple we provide a backup service as part of our XTN support service. Users of this service have their database backed up nightly and the file is transmitted to our servers giving you an off-site backup. We also keep a few days of backups in case one is bad and we have to go back in time. In addition each week we burn one of the backups to a CD for longer storage. I'm not going to delve into all the details here but would encourage you to ask about that service and seriously look into it.

You can easily do your own backups. You don't need our XTN backup service to have good backups, and I would encourage anyone to have their own backups even if they have our XTN backup service. You can never have enough backups when you need them.

Using the tools provided by the PostgreSQL database you can backup and restore database very easily. There are two different types of backups you can use: sql text backups and binary backups. Binary backups are convenient and let you do a couple different things when you load them back up however they are limited to backing up a single database and do not include users and passwords. The sql text backups allow you to backup your entire server including user and passwords but you cannot restore a single database or just the users unless you edit the file. Both of these files are created using the same pg_dump command depending on the options you specify. Both are restored using different commands. Another thing to consider is that binary backups can be restored using pgAdmin.

I use Linux command line tools, but these tools are available on both Mac and Windows as well. The pg_dump commands can be done with a cron entry on Linux and Mac while Windows will have to use some other method to run the command line tools on a regular basis. Here is an example of backing up a entire database including the users and restoring it to another clean server:

Backup:

pg_dumpall -U postgres -h old.server.com -p 5432 -f mybackup.sql

Restore:

psql -U postgres -h new.server.com -p 5432 -f mybackup.sql template1

In the above example the -U postgres says connect as the postgres user. The -h some.server.com is the host. The -p 5432 is the port. Finally the -f mybackup.sql is the name of the backup you want to write to or read from. In the restore we added template1 to the end. That is because psql needs a database name to connect to regardless of the fact that the restore will create and connect to other databases so we gave it the command template1 database which should be in all PostgreSQL database servers.

If you want to backup just a single database to a binary file and restore it to a different database name you use the following:

Backup:

pg_dump -U postgres -h old.server.com -p 5432 -F c -f mybackup.backup dbname

Restore:

createdb -U postgres -h new.server.com -p 5432 newdbname

pg_restore -U postgres -h new.server.com -p 5432 -d newdbname mybackup.backup

You'll notice a lot of the same options we used previously. Most notably the backup file we named .backup to distinguish between the sql text backups. We also added the option -F c to the pg_dump command which tells it we want a compressed binary backup. Finally we had to create the new database before we would restore into it.

These really do just scratch the surface. The biggest message I would like to get across is the importance of doing backups. Hopefully this has gotten you thinking about backups and how easy they could be to do. The PostgreSQL documentation has a great deal more information about the command line tools I have referenced. You can also use pgAdmin to do all of these tasks as well.