Test automation with postgresql connected application

Question

Hello there, I want to automate tests on my web application, and it uses postgresql. Does anyone know how define a restore point on a postgresql database and restore to an earlier state? I heard something about point in time recovery, but i dont if this is what i need.

Thanks in advance

Answer 1

PITR is meant for online backup scenarios, it's meant to create backups without disrupting operations and then being able to recover the data in case of disaster, not for testing applications. The recovery of the data is not online and it's rather complex.

I think the proper way to test is to have a database you can trash easily and restore from a standard backup and you can then repeat the tests by using standard backup/restore scripts in the automation. Failing that, another way to test it is to use transactions. Every test you will make will be wrapped in a transaction you rollback after the test is performed.

BEGIN;
SELECT ...
INSERT ...
UPDATE ...
DELETE ...

<Here go queries to check if everything is alright, setting the test result>
ROLLBACK