PostgreSQL Database Media Recovery


Summary
This document describes a set of options and commands that together provide for 
database recovery in the event of loss or corruption of one or more PostgreSQL database 
files.  This includes an on line backup facility pg_copy, and a set of configuration options 
that tell the PostgreSQL DBMS not to re-use recovery logs.

Lost or corrupt data is recovered by restoring a backup of the database and then rolling 
forward all database changes recorded in the recovery logs.  Hereafter PostgreSQL's 
recovery logs are referred to as xlogs as they reside in the pg_xlog directory.  Normally 
the PostgreSQL database manager uses a set of 16 mega-byte xlogs in a round robin 
fashion to record database change records.  After database checkpoints, log records for 
database changes may overwrite xlogs used earlier.  The various roll forward recovery 
options proposed hereafter, eliminate the overwriting of earlier log data. 

To perform roll forward recovery means you restore a backup of the database and you 
copy all archived xlogs taken since that backup into the pg_xlog directory.  Then you 
start the postmaster with the roll forward option and roll forward recovery occurs. 

Note that in this document database is used synonymously with database cluster.  Where 
a database cluster is the set of databases served by a particular instance of the Postmaster.

PG_COPY
The copy utility is primarily intended to work with the PostgreSQL roll forward recovery 
mechanism.  Otherwise, pg_dump gives a perfectly good on-line backup of a PostgreSQL 
database.

PG_COPY is described further in pgCopy.doc.

Roll Forward Recovery

Roll forward recovery replays the changes made to a database over time as recorded by 
the records in the recovery log (xlog).  In the event of a recovery from data loss or 
corruption all logs are rolled forward from first to last.  

The roll forward recovery mechanism can also be used to recover a database from the 
effects of a committed but errant transaction.  For example someone inadvertently deletes 
all the order rows instead of just the orders for customer 100.  If you know the time that 
the errant transaction occurred then you can specify to roll forward to a point in time just 
before the errant transaction occurred. 

Enabling Database Roll Forward Recovery 

You can enable roll forward recovery so that the xlogs are not re-used or so that the xlogs 
are duplicated.


Wal_file_reuse = [ true | false ]  The default is false.

Use this Postmaster configuration parameter to instruct the database manager to not re-
use xlog files.  

Prior to enabling this parameter you should have made a copy of the database cluster 
either with the pg_copy utility or copy all the files of the database cluster with an OS 
utility.  Note that a pg_dump file is not a sufficient backup for roll forward recovery.  The 
starting point for a roll forward recovery session must be the restoration of a physical 
copy of the database.  Once the database has been restored then you copy all xlogs filled 
since that backup into the pg_xlog directory.  Roll forward recovery then occurs by  
starting a postmaster pointed at the root of the restored database cluster.  The recovery 
process redoes all changes in the xlogs starting with the xlog specified in the checkpoint 
record stored in the pg_control file.

This parameter is intended as an option where the pg_xlog directory is mirrored by the 
system but the database is not.  The mirroring protects the xlogs and the xlogs in 
conjunction with a backup can be used to recover from database loss or corruption.  The 
other situation in which this option might be used is if xlogs are copied to another system 
as they are filled where they are roll forward into a "hot standby" database cluster.


 Note that if you don't use pg_copy  to backup the database then the postmaster for the 
database cluster must be stopped before backing up the cluster.

Wal_file_duplicate = <duplex_pg_xlog_directory_path>

Use this Postmaster configuration parameter to instruct the database manager to duplex 
the recovery log.  The specification of this option implies that wal_file_reuse is true.

Prior to enabling this parameter you should have made a copy of the database cluster 
either with the pg_copy utility or copy all the files of the database cluster with an OS 
utility.  Note that a pg_dump file is not a sufficient backup for roll forward recovery.  The 
starting point for a roll forward recovery session must be the restoration of a physical 
copy of the database and then a restore of all xlog files accumulated since the backup into 
the pg_xlog directory.  Roll forward recovery then occurs by starting a postmaster 
pointed at the root of the restored database cluster.

The xlog files in the pg_xlog directory are overwritten as usual but now a duplicate xlog 
is produced in the duplex_xlog_directory which is never over written.




Deleting xlogs

Since xlogs are not automatically re-used either in the duplex directory or the pg_xlog 
directory depending upon the configuration option chosen, when can old logs be deleted?

The following system function also can be used to determine which xlog files may be 
deleted.

Functions

Wal_file( <request_tye>)
Request_type := [ 'current' | 'last' | 'checkpoint']


Wal_file (current) returns the name of the log file currently being written to.
Wal_file(last) returns the name of the last log file filled.
Wal_file(checkpoint) returns the name of the file containing the current redo position.  
The current redo position is the position in the recovery log where crash recovery would 
start if the system were to crash now.  All logs prior to this one will not be needed to 
recover the database cluster and could be safely removed.  

Note that this new function will require a catalog version change.

Given a database where wal_file_reuse is set to false then the database super-user can use 
the following command to remove all xlog files prior to the specified file name.

ALTER SYSTEM PURGE WAL_FILES <log_name>
The database manager will remove all WAL_FILES less than or equal to log_name 
unless any of those logs are part of the current checkpoint interval.  Note that this 
command acts on both the primary pg_xlog directory and on the wal_file_duplicate 
directory if enabled.



Rolling Forward 

To roll forward recover a database, first restore a backup of the database.  Then restore 
into the pg_xlog directory all of the archived xlogs taken since this backup.  Then start 
the postmaster using the –D parameter to point the postmaster at the root directory of the 
restored database and specify roll_forward = yes.

To cause the postmaster to do a roll forward use the configuration parameter:
roll_forward = yes

This parameter is necessary so that when rolling forward from a restore of a database 
cluster that was properly closed the startup process knows to execute log redo even 
though the database cluster is not in a crashed state.

Once roll forward is completed the postmaster exits.  You can't use the roll_forward 
parameter to start up a postmaster to allow users to connect to the database.

roll_forward_until = <time>

This parameter causes the log redo to terminate with the first commit log record whose 
commit time is greater than or equal to <time>.  Where <time> is specified as 
yyyymmddhhmmss.  Note that all changes after this time for all databases in the cluster 
are lost.



Design

The majority of the work to implement a roll forward recovery mechanism is in the 
writing of the duplex logs.  A new process type is needed along with two new functions 
for writing and flushing and the duplex logs.
XLogWriter2

Duplexed xlogs should be kept on separate devices; otherwise it makes no sense to 
duplicate the logs.  The separate devices imply additional io bandwidth that a single xlog 
writer helper process cannot utilize since writes to the xlog files are synchronous.   
Another xlog writer helper process is needed to write the xlog buffers to the duplicate 
xlog directory.  If both the xlog writer and duplex logging is enabled for the database 
then the postmaster spawns the additional xlog writer helper process automatically.  

Note that there is still some additional overhead in duplicating xlogs that can't be 
eliminated.  The primary xlog sub-system just renames xlogs to keep their names 
advancing but re-uses the space.  The secondary xlog area cannot do this.  As new logs 
are needed 16 mega-byte segments must be allocated and pre-formatted with zeros.  The 
pre-formatting can use a buffered file handle and once all 16 mega-bytes have been 
written then the file handle is fsync'd.

The current xlog sub-system uses an array of <wal_buffers> size to cache recovery 
records being written to disk.  These same buffers will be used to write to the duplex 
directory.  The XlogCtl structure keeps track of the WAL logging sub-system.  It is 
allocated from shared memory.  Amongst other things it keeps track of the current 
write/flush position in the xlog.  Another structure will be added to the log control 
structure to keep track of the current write/flush position in the duplicate xlog.

It will be the job of a new procedure, XLogWrite2 to bring the write/flush position in the 
secondary log to the same offset as they are in the primary log.  XLogWrite2 will be 
called at the end of the XLogFlush procedure and after XLogWrite is called in the 
AdvanceXLInsertBuffer procedure.  The call in XLogFlush ensures that the secondary 
log will be flushed each time a transaction commits or the lazy commit timer expires.  
The call from AdvanceXLInsertBuffer ensures that no xlog buffers get overwritten before 
their contents are flushed to the duplicate log.

The duplicate log writer helper process calls XLogWrite2() every hundredth of a second.

Since the duplex log writer may be writing behind the primary xlog writer, in the event of 
a crash any lost duplex log writes must also be recovered during crash recovery.  Both the 
primary and secondary logs are read.  If the blocks don't compare then the primary log 
block is written to the secondary log.





Usage 

Two scenarios follow of how these tools might be used to implement a corrupted 
database recovery mechanism. 

Scenario 1

Start the postmaster using the Wal_file_duplicate configuration parameter.

Once a day pg_copy the database to some backup area.  Don't use the "KEEP LOGS" 
option, this will allow the pg_copy utility to delete all unnecessary logs in the duplex 
directory.
 
>From the backup area use your favorite backup tool to backup the backup directory to 
tape.  This tape can then be stored off-site to provide an off-site archive of the database.

If you shutdown the postmaster and use an os utility to backup the database; then once 
the backup is complete you can delete all of the xlog files in the duplicate directory.

To recover the using this scenario two options are possible.  

Option 1.

Copy  the logs in the duplex log area to the pg_xlog area of the backup area used by 
pg_copy.

Start the postmaster using the –D parameter to specify the backup area as the PGDATA 
directory and use the roll_forward = yes configuration parameter.

Option 2.

Restore the database from your tape copy of the database.

Copy the logs in the duplicate log directory to the pg_xlog directory of the restored 
database and start the postmaster with roll_forward option.

Scenario 2

Scenario two proposes a "hot standby database" using the roll forward recovery 
mechanism.

Copy the database to another system.

Start the postmaster with the wal_file_reuse = false configuration parameter setting.

Then every few minutes do:

 Last_log = `psql <db> -c "select wal_file('Last_log');"
    If "last_log" is different than the previous time through the duty cycle then do:

1) Copy all the logs in the pg_xlog directory which are less than or equal to      
"Last_log" to the backup system.

2) Psql –t <db> -c "ALTER SYSTEM PURGE XLOGS $Last_log ;"

3) On the backup system roll forward the new logs.
postmaster –c roll_forward=yes

  
Issues

1)	How to make sure a database and a set of xlogs go together for roll forward 
recovery.     If a set of xlogs which do not correspond to the database are used in 
roll forward recovery the user will get an error that says no valid primary or 
secondary checkpoint record could be found.

2)    Create/Drop Table/Index does not roll forward correctly.