PostgreSQL and Amazon EBS Snapshots?

Question

I found this article explaining how to run MySQL on Amazon EC2. It talks about using XFS as the filesystem and then leveraging EBS snapshots to create backups of the data. Does anyone know if I can do something similar using PostgreSQL? Are there changes to the SQL commands to FLUSH and LOCK the tables? Thanks!

Lounge

Answer 1

Yes, that should work fine. As long as your data (all tablespaces) and pg_xlog are on the same device, it should just work. No need for any flushes or locking.

Raulrausch

Can you explain why no flushes or locking are necessary? Isn't it possible to snapshot in the middle of a write and end up with corrupt data in the snapshot? – Doited

The standard point-in-time recovery technique for PostgreSQL uses the transaction logs to recover from file changes that occur during the backup. It's a completely standard PostgresSQL technique to back up the database directories while it's running. – Italianize

"However, a backup created in this way saves the database files in a state as if the database server was not properly shut down; therefore, when you start the database server on the backed-up data, it will think the previous server instance crashed and will replay the WAL log. This is not a problem; just be aware of it (and be sure to include the WAL files in your backup). You can perform a CHECKPOINT before taking the snapshot to reduce recovery time" – Anthill

postgresql.org/docs/current/backup-file.html – Anthill