Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Regarding pg_start_backup command, I should issue it then take snapshot and then issue pg_stop_backup. Right? –  codecool Dec 3 '12 at 6:58
    
@codecool No. You do not need to use pg_start_backup and pg_stop_backup if you're taking an atomic snapshot, since Pg is crash-safe. pg_start_backup if for when you're taking a non-atomic file-system level copy. –  Craig Ringer Dec 3 '12 at 11:57
    
Note that an EBS snapshot will only be atomic if you're snapshotting only one volume; there's no way to get a set of consistent snapshots across more than one EBS volume. If you're backing up across multiple volumes, that's where you need to do a file-level copy with pg_start_backup and pg_stop_backup instead. Read the manual carefully. –  Craig Ringer Dec 3 '12 at 12:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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