As title, in a recent study PG database, at the time of testing the backup recovery, have a few problems, also please the great god guide, the problem details are as follows:

Basic environment:
Postgresql 9.2 Windows 32-bit
Scene:
Database gx, gx there is a table below tb1, some of the data, the following figure

13:28:46 do backup, the basis of the database containing gx
"Delete database gx
Specify a time back to 13:32:10, the results are as follows, data no problem

Perform fully recovered, and no problem, the deleted database doesn't exist

Problem comes, when I return to 13:32:45 specified time points, then the expected results should be 13:32:44. The data in 202, gx database should also has not been deleted, but the configuration after recovery. After the conf, start the database, in pgAdmin, click on the gx database, prompt corresponding physical database file directory does not exist, similar to the below error:

Pg_log inside the log is as follows:

Tested many times, is the same problem, before recovery, database gx corresponding physical file directory exists, after starting PG service, the playback log, this directory will be deleted, but the inside of the watch pg_log log, there is no delete the database gx operation log, so the database should be exist, also exist physical file,

the database manually gx corresponding physical file directory in the base directory, don't do any other operations, the database is normal, the data is normal, the data in the tb1 just restore the 13:32:44. 202 that a

Guess: PG when the playback log preferred physical files deleted database, this should be a bug!!!!!!

The great spirit inspiration. Genuflect is begged the younger brother ~ ~ ~

CodePudding user response:

The first question in CSDN, the great god guide genuflect is begged ~ ~

CodePudding user response:

Test on version 9.5, the same problem, someone had a similar problem? Online to see examples of specified point in time recovery, are carried out within the table to add deleted, this I have tested, no problem, is not delete database example

CodePudding user response:

Is the online backup mode, steps are as follows:
Backup:
1, select PG_START_BACKUP (' XXX ');//start backup
2, select PG_SWITCH_XLOG ();//log switch
3, the backup file copy way data directory
4, the select PG_STOP_BACKUP ();//end backup
5, backup WAL archive log
Simulated disaster: stop service, delete the data directory
Restore:
1, stop the database service
2, the backup data directory copy to the original path
3, empty pg_xlog below log
4, create recovery. The conf, specify restore_command and recovery_target_time
5, start the service

Then, PG started his replay log

The final result as mentioned above, involves deleting database operations, data recovery is not correct