Keeping Your PostgreSQL Data Save

You love your data, and want to keep it save. Which is why you store it in PostgreSQL. But have you thought about what happens when disaster strikes?

Transcript

1. Burning down the house: Keeping your PostgresQL Data safe
2. You Love your data
3. which is why you store it in a database
4. But…
5. What happens if your hardware fails?
7. What happens if Your software fails?
8. “At 9:45 EST a user triggered an unscoped deletion of all historical period records […]” Dead Man's Snitch Outage Post Mortem
9. Not if but when
10. choose a backup strategy
11. Pay attention to…
12. How much data can you afford to lose?
13. How much Down time can you afford?
14. Ways to back up your database
15. Have someone else do it ✔ It’s someone else’s problem now ❌ less control ❌ can cost a lot ❌ less flexibility
16. File-based backup methods ✔ Easy ✔ Widely available tools ❌ Requires PostgreSQL server to be shut down
17. $ pg_ctl stop $ rsync $PGDATA /path/to/backup $ pg_ctl start
18. pg_dump[all] ✔ Creates logical backup ✔ flexible ✔ no down time required ✔ minimal impact ❌ impractical for large databases
19. $ pg_dump \ --format=custom \ --exclude-table-data=stats \ --compress=9 \ --jobs=4 \ ${DB_NAME}.pg_dump $ # Repeat for all Databases
20. $ pg_restore \ --jobs=4 \ --dbname=${DB_NAME} \ /path/to/backup.pg_dump $ # Get a cup of coffee
21. hot standby ✔ Continuous ✔ use them to distribute read load ✔ good for fast failover ❌ only protects against hardware failure ❌ requires at least one more server
22. # In postgresql.conf: wal_level = hot_standby # Set these to something > 0 max_wal_senders = 5 wal_keep_segments = 64
23. $ pg_basebackup \ --host=localhost \ --username=replication_role --format=plain \ --xlog-method=stream \ --pgdata=${STANDBY_PGDATA} \ --progress
24. # postgresql.conf on standby: hot_standby = on hot_standby_feedback = on max_standby_streaming_delay = 10s
25. standby_mode = 'on' primary_conninfo = 'host=db-primary port=5432 user=replication_role sslmode=require' trigger_file = '/usr/local/pgsql/data/ primary.trigger'
26. $ pg_ctl start LOG: entering standby mode ... then some time later ... LOG: consistent recovery state reached LOG: database system is ready to accept read only connections
27. PITR-based backups ✔ continuous backup ✔ high recoverability ✔ cluster-based ❌ complex ❌ increased I/O ❌ needs lots of storage ❌ architecture- dependAnt
28. archive_command = 'cp %p /path/to/archive/%f'
29. $ pg_basebackup \ --host=localhost \ --username=replication_role --format=plain \ --pgdata=${BACKUP_PGDATA} \ --progress
30. # Restore/untar latest base backup # Create a recovery.conf: restore_command = 'cp /path/to/archive/%f %p' # Start PostgreSQL
31. Barman OmniPITR
32. 3rd party tools ✔ replicate between postgresql versions ✔ very flexible ✔ Paid support ❌ complicated ❌ setup & maintenance ❌ costs money
33. Recommendations
34. Daily pg_dump hot standby
35. store backups somewhere else
36. Test your backups
37. seriously, test them
38. postgresql.org/docs/current/
39. Back up your data (not just your database)
40. Thx! nuclearsquid.com / @nuclearsquid / github.com/cypher