Test automation with postgresql app connected
I want to automate tests in my web application and it is using postgresql. Does anyone know how to define a restore point in the postgresql database and restore it to its previous state? I have heard something about time-to-moment recovery, but I don't want to if that's what I need.
Thank you in advance
source to share
First of all, don't do this in your production database.
The correct way (tm) is to use transactions for what they cost. In postgres, you can even nest them using savepoints (which you can rollback).
test=# create table foo (foo_id serial primary key, bar varchar);
NOTICE: CREATE TABLE will create implicit sequence "foo_foo_id_seq" for serial column "foo.foo_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
test=# begin; insert into foo (bar) values ('test');
BEGIN
INSERT 0 1
test=# savepoint sp1;
SAVEPOINT
test=# insert into foo (foo_id, bar) values (1, 'test');
ERROR: duplicate key value violates unique constraint "foo_pkey"
test=# rollback to sp1;
ROLLBACK
test=# select * from foo;
foo_id | bar
--------+------
1 | test
test=# -- note that you're still in a transaction
If that doesn't work (due to software limitations or other reasons), you can always keep multiple dumps of your database in files that you can easily restore; and / or have a script that automatically deletes your production database to your local test database.
Also, always remember to keep your schema changes in source control (or minimal some .sql files); makes it easy to update your production database after you've developed something new using a test database.
PITR is primarily intended for hot standby / backup.
source to share
PITR is designed for online backup scenarios, it is designed for non-disruptive backups and then disaster recovery, not application testing. Data recovery is offline and quite complex.
I believe the correct way to test is to have a database that you can easily dispose of and restore from a standard backup, and then you can repeat the tests using standard backup / restore scripts in automation. Otherwise, another way to test this is to use transactions. Every test you do will be wrapped in a transaction, which you roll back after the test has run.
BEGIN;
SELECT ...
INSERT ...
UPDATE ...
DELETE ...
<Here go queries to check if everything is alright, setting the test result>
ROLLBACK
source to share