Re: Database snapshots or clones for staging and testing.

On Thu, Jan 30, 2014 at 2:12 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:

> Hi all.
>
> I have the following scenario I want to accomplish.
>
> In order to test a new branch of code I want to create a snapshot of the
> live database into a testing database.
>

How do you do that? Running pg_basebackup live? What I do is have a
tarball created by pg_basebackup as part of the normal backup, and restore
from that and roll forward with the WAL archive to get my clone. That way
I can do it as often as I want without creating any load on production.

> The code will be deployed after that and it may run some migrations which
> will change the schema of the database. The code is then tested using both
> automated testing and user acceptance testing (this stage may take hours or
> perhaps even days). During that time the users can change the data.
>

If you can afford the extra storage, use this time to spin up yet another
copy and leave it idle until needed. If you used PITR to make the original
testing clone, just use the same point in time.

> After the branch is accepted by the users we would like to "reset" the
> database to the way it was before and perhaps test another branch.
>

Does it have to be truly the way it was before, or could it be a fresh copy
of the now-current prod, rather than of the "then-current" prod? A good
test should usually still be good as long as it starts from a valid
database, not requiring it to be the *same* valid database each time.
Unless you have found a bug and are now trying to verify that the fix
fixed it correctly.

> One obvious way to do this would be to do a backup/restore but as the
> database grows larger that process is taking too long.
>

How big is it? Perhaps some time optimizing the restoration time would be
well repaid. Although obviously at some size this becomes problematic no
matter how efficient the restore is.

> It would be great if we could do a streaming replica and then pause the
> replication, run our tests, and then reset the database to the point at
> which the replication was paused and restart the replication. Is that
> possible?
>

I think the way to do that would be to use some fancy filesystem feature
that does it for you.

Cheers,

Jeff