Keeping Your PostgreSQL Data Save

Slide 1

Slide 1 text

Burning down the house: Keeping your PostgresQL Data safe

Slide 2

Slide 2 text

You Love your data

Slide 3

Slide 3 text

which is why you store it in a database

Slide 4

Slide 4 text

But…

Slide 5

Slide 5 text

What happens if your hardware fails?

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

What happens if Your software fails?

Slide 8

Slide 8 text

“At 9:45 EST a user triggered an unscoped deletion of all historical period records […]” Dead Man's Snitch Outage Post Mortem

Slide 9

Slide 9 text

Not if but when

Slide 10

Slide 10 text

choose a backup strategy

Slide 11

Slide 11 text

Pay attention to…

Slide 12

Slide 12 text

How much data can you afford to lose?

Slide 13

Slide 13 text

How much Down time can you afford?

Slide 14

Slide 14 text

Ways to back up your database

Slide 15

Slide 15 text

Have someone else do it ✔ It’s someone else’s problem now ❌ less control ❌ can cost a lot ❌ less flexibility

Slide 16

Slide 16 text

File-based backup methods ✔ Easy ✔ Widely available tools ❌ Requires PostgreSQL server to be shut down

Slide 17

Slide 17 text

$ pg_ctl stop $ rsync $PGDATA /path/to/backup $ pg_ctl start

Slide 18

Slide 18 text

pg_dump[all] ✔ Creates logical backup ✔ flexible ✔ no down time required ✔ minimal impact ❌ impractical for large databases

Slide 19

Slide 19 text

$ pg_dump \ --format=custom \ --exclude-table-data=stats \ --compress=9 \ --jobs=4 \ ${DB_NAME}.pg_dump $ # Repeat for all Databases

Slide 20

Slide 20 text

$ pg_restore \ --jobs=4 \ --dbname=${DB_NAME} \ /path/to/backup.pg_dump $ # Get a cup of coffee

Slide 21

Slide 21 text

hot standby ✔ Continuous ✔ use them to distribute read load ✔ good for fast failover ❌ only protects against hardware failure ❌ requires at least one more server

Slide 22

Slide 22 text

# In postgresql.conf: wal_level = hot_standby # Set these to something > 0 max_wal_senders = 5 wal_keep_segments = 64

Slide 23

Slide 23 text

$ pg_basebackup \ --host=localhost \ --username=replication_role --format=plain \ --xlog-method=stream \ --pgdata=${STANDBY_PGDATA} \ --progress

Slide 24

Slide 24 text

# postgresql.conf on standby: hot_standby = on hot_standby_feedback = on max_standby_streaming_delay = 10s

Slide 25

Slide 25 text

standby_mode = 'on' primary_conninfo = 'host=db-primary port=5432 user=replication_role sslmode=require' trigger_file = '/usr/local/pgsql/data/ primary.trigger'

Slide 26

Slide 26 text

$ 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

Slide 27

Slide 27 text

PITR-based backups ✔ continuous backup ✔ high recoverability ✔ cluster-based ❌ complex ❌ increased I/O ❌ needs lots of storage ❌ architecture- dependAnt

Slide 28

Slide 28 text

archive_command = 'cp %p /path/to/archive/%f'

Slide 29

Slide 29 text

$ pg_basebackup \ --host=localhost \ --username=replication_role --format=plain \ --pgdata=${BACKUP_PGDATA} \ --progress

Slide 30

Slide 30 text

# Restore/untar latest base backup # Create a recovery.conf: restore_command = 'cp /path/to/archive/%f %p' # Start PostgreSQL

Slide 31

Slide 31 text

Barman OmniPITR

Slide 32

Slide 32 text

3rd party tools ✔ replicate between postgresql versions ✔ very flexible ✔ Paid support ❌ complicated ❌ setup & maintenance ❌ costs money