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.