Any issues with filesystem-only backups on PostgreSQL?

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.

You can do a file system backup that is reliable if you fully shut down Postgres, THEN do the backup while Postgres is shut down. When the backup is finished, start Postgres.

If Postgres is running during the backup, all bets are off.

It is best to do the above in addition to a proper "within Postgres" db backup.