How to restore a database runtime

Question

I have a test database connected to a test server. I want to run set of selenium tests and I have to restore database after every test.

I made a backup with cli command "createdb" and I just drop the main table every time, but how can I restore database without turning the whole server off and on (can't use createdb with any open connections), as it would take hours or days to make a full set of tests?

I probably won't be given constant admin access to the server, unless it's necessary.

Answer 1

You can kill all connections vis SQL (see https://stackoverflow.com/a/5109190/2352344). Instead of dropping the whole database you can just remove the schema:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Answer 2

I think that instead of dropping the table, how about undoing or deleting the rows in the table. When you run the test, you know what entries will be made in the Table. With this information, just before the test terminates, invoke a script to delete the rows created due to running this test.

Answer 3

You can use a real tool for your backup/restore (Wal-E, barman or backrest). Particularly with backrest, you can do a diff restore where it restores only files that have changes.

Answer 4

I solved the problem by making a bash script that i run from java code.

String[] args = new String[]{"./script.sh"};
Process proc = new ProcessBuilder(args).start();
proc.waitFor();

script.sh:

#!/bin/bash

psql dbname -c "drop schema \"public\" cascade;"
psql dbname -c "create schema \"public\";"
psql dbname < "path/backupname"

I had to use script and not just make it arguments in args, probably becouse of the "<" sign. I found no flag replacement to it.