automatic restore point

Поиск
Список
Период
Сортировка
От Yotsunaga, Naoki
Тема automatic restore point
Дата
Msg-id 8E9126CB6CE2CD42962059AB0FBF7B0DBE3167@g01jpexmbkw23
обсуждение исходный текст
Ответы Re: automatic restore point  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: automatic restore point  (Michael Paquier <michael@paquier.xyz>)
RE: automatic restore point  ("Yotsunaga, Naoki" <yotsunaga.naoki@jp.fujitsu.com>)
Список 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
accidentalcase.
 

The following is a description of "automatic restore point".
【Background】
  When DBA's operation failure, for example DBA accidently drop table, the database is restored from the file system
backupand 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.
   -Time
   ・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
performingrestore(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
PostgreSQLserver log.
 
  
  - Commands wherein this feature can be appended  
   ・TRUNCATE
  ・DROP
   ・DELETE(Without WHERE clause)
  ・UPDATE(Without WHERE clause)
  ・COPY FROM
  
【How to use】
  1) When executing the above command, identify the command and recovery point name that matches the resource
indicatingthe 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
thespecified recovery point. 
 
So, I’m looking for ways to recover only specific tables. Do you have any ideas?

------
Naoki Yotsunaga




В списке pgsql-hackers по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: Incorrect fsync handling in pg_basebackup's tar_finish
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: automatic restore point