4 ms·

It’s good to test PostgreSQL backups (or basically everything that is constantly doing reads and writes)... some pitfalls in my small experience with a RPi and

It’s good to test PostgreSQL backups (or basically everything that is constantly doing reads and writes)... some pitfalls in my small experience with a RPi and some cheap SD card: * If you backup the whole file system while the database is running, you may end up with an inconsistent (maybe unrecoverable?) backup. You need to use a file system with snapshots like ZFS or BTRFS, or use PostgreSQL’s own tools. * Similarly, if the underlying file system experiments corruption, it will also seem to keep working for a while before, and after that you may be left with a bad database and backup. You have to be able to restore multiple time points or ensure your backup is good.

DKnoll 8y ago
> * If you backup the whole file system while the database is running, you may end up with an inconsistent (maybe unrecoverable?) backup. You need to use a file system with snapshots like ZFS or BTRFS, or use PostgreSQL’s own tools. This is true for any sort of mounted database. Always dump the database separately from any FS backups. Another good idea (but not a replacement for backups) is to replicate the DB to a read-only slave which you can either make the master or recreate your master from.
- chousuke 8y ago
  You don't need a SQL dump to backup a PostgreSQL database. It has a mechanism (pg_start_backup) where it will only write WAL (journal) files and will not remove them while you use your filesystem backup (eg. rsync) as usual until you signal completion (pg_stop_backup), after which you also need to back up your WAL archive. This gives you everything you need to recover to any point between the start and stop of the backup operation, and it's guaranteed to be consistent assuming the data itself was copied successfully. This is because any inconsistent files modified during the copy will be recovered from the archived WAL files to attain a consistent state.

DKnoll 8y ago

> * If you backup the whole file system while the database is running, you may end up with an inconsistent (maybe unrecoverable?) backup. You need to use a file system with snapshots like ZFS or BTRFS, or use PostgreSQL’s own tools. This is true for any sort of mounted database. Always dump the database separately from any FS backups. Another good idea (but not a replacement for backups) is to replicate the DB to a read-only slave which you can either make the master or recreate your master from.

chousuke 8y ago
You don't need a SQL dump to backup a PostgreSQL database. It has a mechanism (pg_start_backup) where it will only write WAL (journal) files and will not remove them while you use your filesystem backup (eg. rsync) as usual until you signal completion (pg_stop_backup), after which you also need to back up your WAL archive. This gives you everything you need to recover to any point between the start and stop of the backup operation, and it's guaranteed to be consistent assuming the data itself was copied successfully. This is because any inconsistent files modified during the copy will be recovered from the archived WAL files to attain a consistent state.