3

We're serving a Django/Postgres site running on a VM hypervisor. We're now trying to figure out our back up strategy and have two probable options:

  1. Back up the DB directly using pg_dump
  2. Back up the VM directly by copying the VM image

I'm with the latter as I think, I could simply back up everything that has to do with the site. I'm not sure whether I have to shut down the VM for this though.

What is a better and more recommended way of backing up a DB? Are there any reasons for not using the VM backup?

Thanks

Mridang Agarwalla
  • 43,201
  • 71
  • 221
  • 382

3 Answers3

3

The question basically boils down to, can you consider a hot copy of PostgreSQL's data files a backup?

The answer is: not really. PostgreSQL tries very hard through the use of WAL to ensure that its files are in a consistent state all the time and that it can survive a power failure, but starting it up from a copy of these files puts PostgreSQL into recovery mode. If the backup happened at the wrong second and PostgreSQL can't recover from the state of these files, your backup is useless. You don't want your backup/restore mechanism to depend on the recovery mechanism (unless you're dealing with "crash only" software, which PostgreSQL is not).

The probability of PostgreSQL not being able to recover from these files is not high, but it's not zero either. The probability of PostgreSQL not being able to load an SQL dump that it made, on the other hand, is zero. I prefer backup choices with lower probabilities of failure. pg_dump was designed for doing backups.

Daniel Lyons
  • 22,421
  • 2
  • 50
  • 77
2

PostgreSQL recommends using pg_dump for backups, as a file system (or VM) backup requires the database to be shut down (and has other drawbacks):

http://www.postgresql.org/docs/8.1/static/backup-file.html

Edit: Also, a pg_dump backup will be significantly smaller than a filesystem dump of the same database.

Alex Howansky
  • 50,515
  • 8
  • 78
  • 98
  • 3
    Note that those instructions are for 8.1, which aren't necessarily up to date. There are other ways to do it these days (or at least easier ways) - you should look at the 9.0 documentation at http://www.postgresql.org/docs/9.0/static/backup.html – Magnus Hagander Aug 29 '11 at 17:12
0

There is an additional option. With PostgreSQL you can make an online backup that allows you to snapshot the file system and maintain consistency. You can see details here: http://www.postgresql.org/docs/9.0/static/continuous-archiving.html

We use this exact method for making backups when we run PostgreSQL in a VM.

Aaron Thul
  • 1