0

In my company coworkers are creating several tests per day on database. After group of changes are performed that take few hours, they request to perform database backup of this test database, before they want to continue. In case something goes wrong with next group of changes (and time to time it does go wrong), we can restore database to pre-acident state and they repeat group of tests that are missing.

Now backup process takes around one hour. I would like to execute backup command and tell users they can start testing, despite backup process will last for an hour. We would spare one hour for each of backup process. Backup process is pretty reliable process and if something really goes wrong with restore/rollforward we can still go to one previous backup and do the restore/rollforward and apply all the missing tests.

On PostgreSQL database checkpoint is forced with backup command: pg_basebackup --checkpoint=fast ... This forces checkpoint (writes log file data to persistent data files) and then backup is executed. Backup contains data at the point of checkpoint and any other data executed by new transactions are ignored (if we want to dismiss archived logs).

We use Db2 v11.5.8.0 on Linux/x86-64. Is there any similar option to force checkpoint when on-line backup is performed? So I can restore/rollforward database to the state of beginning of backup?

I did a test. I created a file on Linux insert.sh. In this file some dummy table is created and every second new row is inserted.

db2 "connect to db01"
db2 "drop table data.tab1 if exists"
db2 "create table data.tab1 (id int generated always as identity, ts timestamp)"
while true
do
    db2 "insert into data.tab1 (ts) values (current_timestamp)"
    db2 "select max(id), max(ts) from data.tab1"
    sleep 1
done

In second window I execute backup database db01 online...

But when I create "restore database" and "rollforward" on new database and execute select max(id) from data.tab1 I get "1667", so 27 min after backup has started.

QUESTIONS:

  1. How to force checkpoint before backup is executed and then restore/rollforward to include only data at the beginning of backup process?
  2. Is there any other strategy we can use to accomplish the same result?
1
  • Have you considered taking an online backup each night, restore that and roll forward to whatever time you need? Jan 24 at 15:25

1 Answer 1

1

You can't restore from a backup image to a point in time before the backup completion time in db2.
What you can do is to take an incremental backup of the database or some set of tablespaces changed by your subsequent tests to restore from it to avoid lengthy rollforward after restoration from some previous full backup taken, say, much earlier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.