Rollback to a specific point using PostgreSQL

Question

Is there a way to roll back to a specific starting point. I am looking for something like this.

Start specific_path

Now after that another application related to SAME login will insert and delete data (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 a rollback to a specific_point to have a clean database in a known previous state.

I thought ROLLBACK TO SAVEPOINT foo; was the solution, but not unfortunately?

Any idea?

Configuration: PostgreSQL 8.4 / Windows XP

Hi

Answer 1

Two quick options:

• , , .
• -.

Answer 2

, . . , . - , . .

Answer 3

SAVEPOINT , .

- PITR, . , . "", , , , .

Answer 4

I assume this requirement is for testing.

The following alternative exists:

• At some 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 job.

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

  CREATE DATABASE mydb TEMPLATE mydb_template;