4

I am trying to put together a means for resetting a (large, populated) postgreSQL database to a known state between integration tests.

I've been reading up on postgres's database recovery features, and it looks like it's possible to restore to a specific point in time. But the documents are aimed at recovering from a crash and losing as little data as possible in the process. It also seems to require shutting down the database to do any data recovery.

Is it possible to restore a postgreSQL database after a small number of changes (no more than 1 user would create in a few minutes of interaction with the app) to a state just before those stages, without having to stop and restart the database?

wildplasser
  • 43,142
  • 8
  • 66
  • 109
Mar
  • 7,765
  • 9
  • 48
  • 82
  • 1
    Consider looking outside Postgres for a solution. In file systems like `btrfs` or `zfs` you can create a [filesystem snapshot](http://www.linux.com/learn/tutorials/767683-how-to-create-and-manage-btrfs-snapshots-and-rollbacks-on-linux-part-2). So you could stop Postgres, roll the database directory back to the snapshot, and restart Postgres. – Andomar Feb 25 '15 at 22:02
  • You could use a specially prepared template for reconstructing your database. ("create database" is basically a `cp -rfp templateX newdb` , plus some catalog magic) – wildplasser Feb 26 '15 at 01:36
  • @wildplasser Can you elaborate on that? Preferably as an answer? – Mar Feb 26 '15 at 18:28

1 Answers1

0
/* This needs to be be executed only once
**
CREATE DATABASE my_template;
\connect my_template

CREATE TABLE one
        ( num SERIAL NOT NULL PRIMARY KEY
        , msg varchar
        );

INSERT INTO one(msg) VALUES('Hello world!' );

**
*/

    -- We need to connect to *some* database
    -- but *not* the one we are going to drop.
\connect my_template

        -- kill client sessions
-- SELECT pg_terminate_backend(sa.procpid) pre-9.0
SELECT pg_terminate_backend(sa.pid)     -- version >=9.0
FROM pg_stat_activity sa
WHERE datname IN ( 'cloned' )
-- AND usename NOT IN ( 'postgres' )
    ;


DROP DATABASE IF EXISTS cloned ;
CREATE DATABASE cloned 
    -- more options ...
    template=my_template;

\connect cloned

\d
   -- verify that table one actually exists and is populated
SELECT * FROM one;
  • The drop database cloned; only works if there are no connected sessions to this database
  • This can be handled by kicking them out first
  • The clients/applications should then reconnect.
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • How does this work cloning a database? Our database is about 125GB, so copying all that data between each individual test is not going to work. If it just stores changes post-clone and dropping the clone just deletes those changes, then this could work. – Mar Feb 26 '15 at 22:03
  • 1
    You didn't mention it was large. I assumed a development/test setup. Oops: you **did** mention it was large; in the first sentence. – wildplasser Feb 26 '15 at 22:39
  • If the database is too large to clone (yours is), and number of affected pages is sparse, you should try to get PITR working. I've never used it, but it still seems doable. – wildplasser Feb 26 '15 at 22:48
  • NB: I just cloned a ~3GB database in 1...2 minutes on a (good) desktop machine. So yours should complete in about 1/2 hour. That's probably too slow for you. – wildplasser Feb 26 '15 at 22:58