Rollback to a specific moment with PostgreSQL

Question

Hi, 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

Answer 1

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.

Answer 2

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.

Answer 3

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.