Are there any possible issues I may be overlooking if I stick to using solely file system-level backups?
Yes - but not the ones you're thinking of. So long as you do the file-system level copy right that's safe, it's reliance on physical backups that's the risk.
In writing this, I noticed that the chapter on filesystem level backups needs to be updated to point users at pg_basebackup
and pg_start_backup()
. While technically part of streaming replication and PITR these tools are just ways to make safe, consistent file system level copies, and should be referenced in that part of the docs.
Doing it safely
Per the documentation for PostgreSQL file system level backup and making a base backup it is quite safe to take a filesystem level copy so long as you follow the rules given there, namely doing one of:
Stopping the server before the backup and leaving it shut down until the backup finishes;
Using pg_basebackup
;
Using pg_start_backup()
and pg_stop_backup()
and copy the files generated by pg_stop_backup()
; or
Using an atomic file system snapshot and copy from the snapshot, in which case nothing can be writing to it because it's a snapshot.
You can also use pg_basebackup -X stream
, which is my preference. It uses the replication protocol to do a filesystem level copy, taking care of pg_start_backup()
etc for you.
Physical backups have the major advantage of being usable as the base for Point-in-time recovery.
The snapshot case is safe because it's just like a crash. There's no writing going on, and the database state is captured at a particular moment in time. The write-ahead logs contain all committed transaction data, so anything not yet flushed to the heap is replayed from WAL during recovery when the DB first starts up. It's just like starting up after a crash. You only need pg_start_backup()
and friends if you're copying a live database directory that's still being written to while you copy it; a snapshot avoids this.
Note that relying on snapshots is only safe if the snapshot is actually atomic, i.e. it captures the file-system state at a single instant in time. It's also only safe if there's exactly one volume/filesystem involved - you can't use two snapshots of two separate filesystems to make a backup, they won't be from the same instant in time. If you're using tablespaces, snapshot backups are unsafe for that reason - but pg_basebackup
or pg_start_backup()
, rsync, pg_stop_backup()
is still safe.
That means that if your database filesystem is (say) four EBS volumes in an md
raid array, or you have one for pg_xlog
and one for the rest of the db, you can't use EBS snapshots to take a consistent backup. If everything is one one EBS volume, an EBS snapshot is safe.
You can also stop PostgreSQL before running the backup and start it up after. If you're one of the lucky people who can afford backup downtime windows, well, that's cool. Personally I prefer to hot backup anyway.
Risks
The real issue to be concerned about is that when you take a physical backup, you're copying the database structure unchecked and unverified. If there's undetected corruption, you might well have backups that are a lot less useful than you thought. Personally I would be using logical dumps as well.
A useful compromise can be to start up your filesystem level backup once you've made the copy, then do a pg_dump
from the filesystem level backup. This ensures it's readable, and gives you a logical copy. If your logical dump fails, your automation should be emailing you and screaming for help, because it suggests your physical copy might be damaged too.
BTW, I wrote a bunch on avoiding data loss / corruption issues on my old blog a while ago - see Avoiding PostgreSQL database corruption.