PostgreSQL – Approach for Live/Hot File Backup

backupdatabase-backuppostgresql

Forgive me for the ignorant question, but I see postgres has their WAL logs, and there are talks about using filesystem snapshots, and the WAL with the snapshots may or may not be sufficient backup/recover…I'm not traditionally a DBA/admin (I'm a developer), but reaching a point where looking to get better at supporting these needs.

Question: Can Postgres be setup on a 10GB or 100GB sized system to not use special backup software, but instead just use traditional filesystem backup software (filesystem snapshots?) and has a reasonable method of recovery using this method? (if sizing matters, would like to know)

Usecase 1: to avoid special backup approach when using Postgres and just use normal file system. No downtime, or < 5 seconds.

Usecase 2: When used with a hybrid ECM, such as Alfresco, where the file system contents (images) and the metadata (database) should always be backed up and restored in unison. No downtime, or < 5 seconds.

Please elaborate on areas I may not be asking, such as good/bad ideas or things to watch out for 🙂

(note, this is for local install on linux environments, if particular filesystem is needed for a strategy, that is fine).

TIA!

-D

Best Answer

Question: Can Postgres be setup on a 10GB or 100GB sized system to not use special backup software, but instead just use traditional filesystem backup software (filesystem snapshots?) and has a reasonable method of recovery using this method?

Yes, if the file system snapshots are atomic. This is extremely important. You must have an atomic snapshot, you can't just copy the data directory directly. The usual method is to take a snapshot with a SAN, logical volume manager, snapshot-capable file system, etc, and mount it on another path, then back that up. So you're using a pre- and post- backup script.

Here "atomic" is used in its computer science meaning, of indivisible, a single moment in time where everything is either before or after that moment. In the case of a snapshot, it means an instant in time, the state of the storage at that particular moment.

It is my understanding that Microsoft's Volume Shadow Copy Service (for Windows) is only atomic at the file level, so you cannot use backup systems that rely on it for consistency.

If you aren't really using a file system snapshot, you're just copying the data on the file system live, you can still do that but you must take extra steps. Per the documentation you can tell PostgreSQL the backup is taking place, and it will go into a no-overwrite mode that makes backups safe while it's running. However, to restore such a backup you require files that are written after the post-backup script calling pg_stop_backup() runs. The easiest way to make sure you have those files is to make sure WAL archiving is enabled; otherwise you'll need some extra scripting hooks in your backup system to append them to the backup.

Usecase 1: to avoid special backup approach when using Postgres and just use normal file system. No downtime, or < 5 seconds.

For this, just use pg_dump or pg_basebackup. Neither require downtime, and are simple.

Any decent backup system supports pre- and post-backup hooks that make this easy.

Usecase 1: to avoid special backup approach when using Postgres and just use normal file system. No downtime, or < 5 seconds.

For that you will want atomic snapshots, and you'll need to make sure the images are in the same snapshot as PostgreSQL.

Otherwise you risk inconsistencies where the filesystem and DB don't quite match.

Related Topic