Thread: Database snapshots or clones for staging and testing.
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. 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. 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.
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. 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?
Another option would be the try and leverage PITR. Create a checkpoint, run the migrations do your tests, roll back to everything to the start. This does seem possible to me although of course I am still stuck with the backup restore problem.
Anything I missed? Surely there is a super clever trick I am missing here.
On 01/30/2014 02:12 PM, Tim Uckun 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. 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. 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. > > 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. 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? > > Another option would be the try and leverage PITR. Create a checkpoint, > run the migrations do your tests, roll back to everything to the start. > This does seem possible to me although of course I am still stuck with > the backup restore problem. > > Anything I missed? Surely there is a super clever trick I am missing here. Well since you mention snapshots, one way I have seen it done on this list is to use a file system that supports snapshots and use that. -- Adrian Klaver adrian.klaver@gmail.com
Hello,
For backup restore solution, one could create a snapshot and use it as a template for later use. For example, if the changes can not be revoked, and something wrong went with the tests; creating a a database form a template is much faster than backup and restore.
Regards
On Thursday, January 30, 2014 11:22 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 01/30/2014 02:12 PM, Tim Uckun 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. 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. 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.
>
> 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. 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?
>
> Another option would be the try and leverage PITR. Create a checkpoint,
> run the migrations do your tests, roll back to everything to the start.
> This does seem possible to me although of course I am still stuck with
> the backup restore problem.
>
> Anything I missed? Surely there is a super clever trick I am missing here.
Well since you mention snapshots, one way I have seen it done on this
list is to use a file system that supports snapshots and use that.
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> 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. 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. 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.
>
> 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. 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?
>
> Another option would be the try and leverage PITR. Create a checkpoint,
> run the migrations do your tests, roll back to everything to the start.
> This does seem possible to me although of course I am still stuck with
> the backup restore problem.
>
> Anything I missed? Surely there is a super clever trick I am missing here.
Well since you mention snapshots, one way I have seen it done on this
list is to use a file system that supports snapshots and use that.
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 30, 2014 at 2:12 PM, Tim Uckun <timuckun@gmail.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