1

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

3

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.

  • Thanks Craig, this is great! Do you know of some commonly used 'atomic snapshot' filesystems or volume managers on linux (and/or if this is a SAN-level feature, what SAN's support it?) – dhartford Feb 25 '14 at 14:00
  • @dhartford LVM2 does, as (IIRC) does Amazon's EBS volume store, so if you're using either of those you can use ext4 or whatever you want. At the file system level (i.e. not requiring volume manager support) ZFS does. BTRFS I think. Maybe XFS, I know it supports freezing, less sure about snapshots. No idea about which SAN models support it, I deal with that on a case by case basis with careful testing and the assumption they're buggy until proven otherwise. – Craig Ringer Feb 25 '14 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.