Re: Best practices for cloning DB servers

Andy - I guess that uploading your WAL to S3 at least avoids the turmoil of running your database within a VPC that would definitely be an advantage.

I had imagined that replaying the WAL to get caught up from a baseline backup would be prohibitively slow versus simply snapshotting, but having the ability to go to a specific point in time hasn’t been a concern for us.

Thanks for the reply!
Bill

From: Andy Lau <alau(at)infer(dot)com<mailto:alau(at)infer(dot)com>>
Date: Tuesday, August 19, 2014 at 3:04 PM
To: Joseph Kregloh <jkregloh(at)sproutloud(dot)com<mailto:jkregloh(at)sproutloud(dot)com>>
Cc: WILLIAM MITCHELL <bill(at)publicrelay(dot)com<mailto:bill(at)publicrelay(dot)com>>, "pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>" <pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>>
Subject: Re: [GENERAL] Best practices for cloning DB servers

Thanks for the responses. Bill - We currently use wal-e to upload our WAL logs to S3. We actually don't keep our logs around for that long, so we don't have a problem with the size of our logs or snapshots.

I think we're going to go with our current solution, but during our process of cloning, point the restore command to the WAL log location of the source PostgreSQL, and point the archive command to a different location in S3. We already have a wrapper around wal-e that sets the S3 prefix, so it's easy to extend it to a separate wrapper for restore vs archive.

On Thu, Aug 14, 2014 at 9:08 PM, Joseph Kregloh <jkregloh(at)sproutloud(dot)com<mailto:jkregloh(at)sproutloud(dot)com>> wrote:
Why don't you try using Barman? It allows you to take snapshots and do PITR. Not to mention you can use it as it's intended purpose as a backup engine.

-Joseph

On Thu, Aug 14, 2014 at 1:53 PM, Bill Mitchell <bill(at)publicrelay(dot)com<mailto:bill(at)publicrelay(dot)com>> wrote:
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 snapshot the 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 data set and the experiences you've had with that

Regards
Bill

Sent from my iPhone

> On Aug 14, 2014, at 12:17, "Andy Lau" <alau(at)infer(dot)com<mailto:alau(at)infer(dot)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 single database server is hosted in AWS, we take EBS snapshots every so often, and upload our WAL logs to S3. We want to be able to start a new server from a snapshot, replay the WAL logs to get to a specific point in time, then start using the database 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 the logs is very bad. A solution here could be to just point clones to a different location in S3 so they won't collide, but I 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 previous good DB to experiment while keeping the production DB unaffected, and testing Postgres version upgrades (9.1 to 9.3).
>
> Thanks!
> -Andy

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general