CODEWITHSUNDEEP
postgresql
mada54
mada54

Reputation: 41

Rollback to a specific moment with PostgreSQL

Is there a way to rollback to a specific starting point. Im looking for something like this.

Start specific_point;

Now after this, an other application connected with the SAME login will insert & delete datas (webservices with crud operations) for about 2 minutes doing tests. Each webservice call is declared as a transaction with Spring Ws.

After that i want to rollback to the specific_point to have a clean database to a known previous state.

I was thinking that ROLLBACK TO SAVEPOINT foo; was the solution but not unfortunately?

Any idea ?

Configuration: PostgreSQL 8.4 / windows XP

Regards

Upvotes: 4

Views: 2657

Answers (4)

Laurenz Albe
Laurenz Albe

Reputation: 246483

I assume that a requirement like that is for testing purposes.

There is the following alternative:

  • At the specific point, disconnect all users from the database and clone it:

    CREATE DATABASE mydb_template
   TEMPLATE mydb
   ALLOW_CONNECTIONS FALSE
   IS_TEMPLATE TRUE;

  • Then let the tests do their work.

  • To restore the database, disconnect everybody from it and run

    CREATE DATABASE mydb TEMPLATE mydb_template;

Upvotes: 0

Magnus Hagander
Magnus Hagander

Reputation: 25098

SAVEPOINTs only work inside one transaction, which means one connection.

You can build something off PITR, which lets you do a restore to a specific point in time. You'll need a base backup to work from, and the archived log files, but that lets you roll forward to a specific point in time. It's not as "convenient" as what you seem to be looking for, but it works.

Upvotes: 0

h0tw1r3
h0tw1r3

Reputation: 6818

Two quick options:

  1. Create a temporary database using the primary database as a template, drop the temporary database when finished testing.
  2. Table Log might be helpful in implementing something.

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 127086

This is only possible if all your applications share the same database connection. They may not create their own connection. You could do this with a connection pool, just allow one connection. If someone can create it's own connection, game over. Different connections can not share the same transaction.

Upvotes: 0

Related Questions