We are running our own Postgres server on AWS as well (since amazon RDS doesn't support read replicas yet)
In out case, simply having a streaming replication standby works - and we do our pg_dump from that -- or simply
snapshotthe machine and then promote the replica to master to use full data set in QA
I would have thought that shipping WAL file into S3 would have been problematic - I'd be interested in the size of the
dataset and the experiences you've had with that
Regards
Bill
Sent from my iPhone
> On Aug 14, 2014, at 12:17, "Andy Lau" <alau@infer.com> wrote:
>
> Hi everyone,
>
> I had a question about some best practices. Our situation is that we want to be able to clone a database server. Our
singledatabase server is hosted in AWS, we take EBS snapshots every so often, and upload our WAL logs to S3. We want to
beable to start a new server from a snapshot, replay the WAL logs to get to a specific point in time, then start using
thedatabase from there. The problem we ran into here was that this exact clone started uploading WAL logs to our S3
archive,mixing them up with the original WAL logs. Since this is effectively a branch off of the original DB, mixing up
thelogs is very bad. A solution here could be to just point clones to a different location in S3 so they won't collide,
butI was wondering if there were any best practices for doing this.
>
> Also would appreciate any advice on cloning DB servers in general. A few of our use cases include restoring to a
previousgood DB to experiment while keeping the production DB unaffected, and testing Postgres version upgrades (9.1 to
9.3).
>
> Thanks!
> -Andy