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

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.

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.

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.

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;