We're running on EBS volumes on EC2. We're interested in leveraging
EBS snapshotting for backups. However, does this mean we'd need to
ensure our pg_xlog is on the same EBS volume as our data?
(I believe) the usual reasoning for separating pg_xlog onto a separate
volume is for performance. However, if they are on different volumes,
the snapshots may be out of sync.
Best Answer
Having the transaction log (pg_xlog) on separate storage can increase write performance on most RDBMS platforms. If yours is a (concurrent) write heavy application, you may see a performance benefit.
If the snapshots are not synchronous however, you could lose data from uncomitted transactions in the event of an unclean restart. I suppose in certain situations a pg_resetxlog could be required after a failure.
You could look into WAL archiving, or even techniques similar to ZFS snapshotting if EBS snapshots have the same effective purpose.