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.