2

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.

STF
  • 1,485
  • 3
  • 19
  • 36
Marcin Plebanek
  • 121
  • 1
  • 7
  • Can't you just drop the tables and/or schema and recreate them? Using a "restore database" option that requires downtime sure seem problematic in such context. – Aaron Sep 01 '17 at 14:06

4 Answers4

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;
clemens
  • 16,716
  • 11
  • 50
  • 65
0

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.

Sunil Singhal
  • 593
  • 3
  • 11
0

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.

Arkhena
  • 270
  • 2
  • 10
  • Seems great. Are all of these tools capable of restoring a datebase with open connections? – Marcin Plebanek Sep 02 '17 at 12:24
  • What do you mean ? Restoring databases while people are connected to it ? No, they can't as it's physical backups. Your PostgreSQL should be up and running but no one can be connected before you reach a point where data are consistent. – Arkhena Sep 04 '17 at 07:42
  • No, I just want to clear datebase and restore it with the server running. I don't care if user would be able to use it at this time. – Marcin Plebanek Sep 04 '17 at 07:55
  • It's a physical backup. So you have to stop the server to be abble to delete files. Normaly your backup tool will start it when needed though. – Arkhena Sep 04 '17 at 08:55
0

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.

Marcin Plebanek
  • 121
  • 1
  • 7