automatic restore point

From: "Yotsunaga, Naoki" <yotsunaga(dot)naoki(at)jp(dot)fujitsu(dot)com>
To: Postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: automatic restore point
Date: 2018-06-26 01:17:31
Message-ID: 8E9126CB6CE2CD42962059AB0FBF7B0DBE3167@g01jpexmbkw23
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi, I'm a newbie to the hackers but I'd like to propose the "automatic restore point" feature.
This feature automatically create backup label just before making a huge change to DB. It's useful when this change is accidental case.

The following is a description of "automatic restore point".
When DBA's operation failure, for example DBA accidently drop table, the database is restored from the file system backup and recovered by using time or transaction ID. The transaction ID is identified from WAL.
But below are the following problems in using time or transaction ID.
・Need to memorize the time of failure operation.
(It is possible to identify the time from WAL. But it takes time and effort to identify the time.)
・Difficult to specify detail point.
-Transaction ID
・It takes time and effort to identify the transaction ID.

In order to solve the above problem,
I'd like propose a feature to implement automatic recording function of recovery point.

【Feature Description】
In PostgreSQL, there is a backup control function "pg_create_restore_point()".
User can create a named point for performing restore by using "pg_create_restore_point()".
And user can recover by using the named point.
So, execute "pg_create_restore_point()" automatically before executing the following command to create a point for performing restore(recovery point).
The name of recovery point is the date and time when the command was executed.
In this operation, target resource (database name, table name) and recovery point name are output as a message to PostgreSQL server log.

- Commands wherein this feature can be appended
・DELETE(Without WHERE clause)
  ・UPDATE(Without WHERE clause)
【How to use】
  1) When executing the above command, identify the command and recovery point name that matches the resource indicating the operation failure from the server log.
  ex)Message for executing TRUNCATE at 2018/6/1 12:30:30 (database name:testdb, table name:testtb)
  set recovery point. operation = 'truncate'
  database = 'testdb' relation = 'testtb' recovery_point_name = '2018-06-01-12:30:30'

2) Implement PostgreSQL document '25 .3.4.Recovering Using a Continuous Archive Backup.'
※Set "recovery_target_name = 'recovery_point name'" at recovery.conf.

【Setting file】
Set postgres.conf.
auto_create_restore_point = on # Switch on/off automatic recording function of recovery point. The default value is 'off'.

So what do you think about it? Do you think is it useful?

Also, when recovering with the current specification, tables other than the returned table also return to the state of the specified recovery point.
So, I’m looking for ways to recover only specific tables. Do you have any ideas?

Naoki Yotsunaga


Browse pgsql-hackers by date

  From Date Subject
Next Message David G. Johnston 2018-06-26 01:33:40 Re: automatic restore point
Previous Message Michael Paquier 2018-06-26 00:58:11 Re: Incorrect fsync handling in pg_basebackup's tar_finish