postgresql Point In Time Recovery
Enable archive setting
Use this command to create a base backup tar file.
Recovery work will start from the last check point of this base backup file.
For more backup methods, please refer to my post.
More data after backup
Emulate server crash
You can use any method to make this happens. Like shutdown your server by power off or
Backup latest unarchived
xlog, if you could.
Emulate data files loss.
Now it is time to restore database.
restore base file
What we need to do is just unzip and move all files into
Now if you start postgresql, you will find data restore to the point of base backup.
But now we need to recover till to most recent valid archieve state.
Setup recovery file
Create a file
recovery.conf under you
Since you have your archived WAL, if you start postgresql, database will recovering process till the
last valid segment that was
But if you want to recover to the last valid moment right before crash, maybe that latest segment has not yet been archived, so you need to copy all
pg_xlog files in original
$PGDATA folder, that is why I said it is better to backup
$PGDATA/pg_xlog even with you enabled
If recovery is finished, the
recovery.conf file will be renamed to
recovery.done to avoid further recovery.