|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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.
・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.
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.
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?
|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|