[PATCH] Patch to compute Max LSN of Data Pages - Mailing list pgsql-hackers

>> Based on the discussion and suggestions in this mail chain, following features can be implemented:
>>
>> 1. To compute the value of max LSN in data pages based on user input whether he wants it for an individual

>>   file,  a particular directory or whole database.
>>
>> 2a. To search the available WAL files for the latest checkpoint record and prints the value.
>> 2b. To search the available WAL files for the latest checkpoint record and recreates a pg_control file pointing at

>> that checkpoint.

>> I have kept both options to address different kind of corruption scenarios.

> I think I can see all of those things being potentially useful.  There
> are a couple of pending patches that will revise the WAL format
> slightly; not sure how much those are likely to interfere with any
> development you might do on (2) in the meantime.

Based on above conclusion, I have prepared a patch which implements Option-1

To find the value of max LSN in data pages based on user input whether he wants for
        - An individual file
        - A particular directory
        - Whole database

Corresponding pg_resetxlog options are as follows
  -p {file | dir}        print max LSN from specified file or directory path
  -P                         print max LSN from whole database

Note: in case of -p {file | dir} input path should be absolute path or relative from data base directory.

These options are useful when pg_control, WAL files and data files are missing or corrupted.
Using above options user can able to find the max LSN number and can be able to compute the next redo log sequence number.

Sample output:
postgres@linux:> pg_resetxlog -P /home/postgres/installation/bin/data
Maximum LSN found is: 73325448, WAL segment file name (fileid, seg): 0000000000000004

Design:
Based on user option display max LSN.
1. Finding max LSN in an individual file [pg_resetxlog option: -p file-name]
A. Open the given file and check for the number of blocks;
B. Read page header and validate; if valid find the max lsn number; if invalid log the page-id and filename and continue to next page.

2. Finding max LSN a folder (excluding sub directories) [pg_resetxlog option: -p folder-name]
    Note: Here we are not traversing through sub directories, as some times it may possible to have recursive loops because of soft links
Read all the file in the given folder using ReadDir function
        If file name / folder name start with pgsql_tmp ignore and continue to next.
        Find the max LSN in this file (refer 1. Finding max LSN in an individual file)

3. Finding max LSN for whole database [pg_resetxlog option: -P]
    A. Read the base directory
            Format: pgDataDirecoty/base/databaseid/*
               1. Skip the folder if name is equal to “0” or “1”; [skip template database]
2. Form the new folder name as and call the function written in [2. Finding max LSN a folder]
    B. Read the global directory        
        pgDataDirecoty/global
            Note: here need to exclude the files [pg_controldata, .. ] which are taken care in folder reading function.
    C. Read all table spaces
       Folder structure: pg_tblspc/table space id/<CONSTANT PG VERSION STRING>/Database ID/relfilenodes.
1. Read all table space names in pg_tblspc/*
                1.1. For each folder form the path as
                         pg_tblspc/tblspc-folder-name/<CONSTANT PG VERSION STRING>/
1.2. Read all the directories in pg_tblspc/table space id/<CONSTANT PG VERSION STRING>/*
                1.2.1. For each folder form the path as “pg_tblspc/ tblspc-folder-name /<CONSTANT-PG-VERSION STRING>/db-id-folder-name”

Comments/Objections?

With Regards,

Amit Kapila.