Postgres Backup queries on AWS after reading Postgres docs

Question

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance.

So there are 2 ways to do it.

1. Create a dump using pg_dump.
2. File level backup with WAL archiving on. I will be using Snapshots so my use case falls in this category.

Now the question is do I need to freeze my filesystem before taking the backup? Postgresql docs suggest that WAL recovery does not get affected by an inconsistent snapshot. Does it make sense to do freeze just to be safe?

Should I take backup via both of above ways?

What sort of time period after which I should remove old WAL logs?

Docs also mention that WAL logs and data should be on the same filesystem to make backup consistent. In that case I am thinking to take backup of WAL logs separately from EBS snapshot and save it in S3 and some external place out of Amazon. Thoughts on this strategy?

Kindly mention if I am missing something.

Answer 1

EBS snapshots are atomic, they're a point-in-time. This means that for PostgreSQL, so long as all the datadir including WAL, and all tablespaces are in the same EBS volume, starting from a snapshot is equivalent to starting after an unclean shutdown (crash), which PostgreSQL is designed to do reliably every time.

You do not need to stop I/O to the volume to take an EBS snapshot if everything is in the same EBS volume.

You also don't need to stop I/O if you're using pg_start_backup and pg_stop_backup along with file-level copies, but you must capture all the WAL files up to the one generated after the end of pg_stop_backup. See the manual.

As for S3 storage, etc, check out barman; it can automate most of this for you and supports hook scripts you can use to put backups in S3.