Table of Contents
PostgreSQL – recovery across timeline
Every time, dba perform incomplete recovery, PostgreSQL database creates a new timeline.Thus timeline is required to distinguish between WAL files genered before and after incomplete recovery. Let’s try to understand it with below mentioned scenario.
Database is running fine and a critical table was dropped with WAL 2 was active.
000000010000000000000001
000000010000000000000002 — Critcal table dropped
Now DBA will do incomplete recovery till WAL 1 or with specific time or lsn. Apart from database recovery i.e. recovery of lost table, it will result in a new timeline. Therefore, WAL generated now will be similar as mentioned below.
000000020000000000000001
000000020000000000000002
With newly generated timeline, database server can distinguish between WAL files generated across timeline. Similarly to incarnation in Oracle database, PostgreSQL have timeline.
What if we want to recover database to older timeline
As PostgreSQL server can identify WAL files generated across differnet timeline, thus it is possible to recover database to older timeline. Apart from reovery_time_time/lsn/name/xid, you need to set parameter recovery_target_timeline in recovery.conf
Sample recovery.conf
recovery_target_timeline = '1'
recovery_target_lsn = 'XXXXX'If you are preparing for PostgreSQL database interview , Please check useful questions here.
If you want to crack an interview for the PostgreSQL DBA/Database Engineer role, a demo interview will fortify your chances. Thus, Book an interview preparation session.
It’s really good. It’s clear my all doubt.👍