Re: [PATCH] Patch to compute Max LSN of Data Pages

>> 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(at)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.

This patch is now marked Returned with Feedback in the CF, but I see no
on-list feedback. Did some review happen?

On Saturday, November 10, 2012 10:19 PM Noah Misch wrote:
> This patch is now marked Returned with Feedback in the CF, but I see no
> on-list feedback. Did some review happen?

No review happened for this patch.
It has returned due to slight confusion thinking that this is same as:
Patch for option in pg_resetxlog for restore from WAL files (https://commitfest.postgresql.org/action/patch_view?id=897 ) for which Heikki has given some comments.

Any suggestions?

With Regards,
Amit Kapila.

Amit kapila wrote:
>
> On Saturday, November 10, 2012 10:19 PM Noah Misch wrote:
> > This patch is now marked Returned with Feedback in the CF, but I see no
> > on-list feedback. Did some review happen?
>
> No review happened for this patch.
> It has returned due to slight confusion thinking that this is same as:
> Patch for option in pg_resetxlog for restore from WAL files (https://commitfest.postgresql.org/action/patch_view?id=897 ) for which Heikki has given some comments.

Oops, sorry, my mistake. Please reopen it as needing review. I will
move all the open patches to CF3, unless someone beats me to it. I
probably won't be able to get anything done tomorrow, so if somebody has
a boring Sunday I would appreciate the help.

--

On Sun, Nov 11, 2012 at 02:18:11AM -0300, Alvaro Herrera wrote:
> Amit kapila wrote:
> > On Saturday, November 10, 2012 10:19 PM Noah Misch wrote:
> > > This patch is now marked Returned with Feedback in the CF, but I see no
> > > on-list feedback. Did some review happen?
> >
> > No review happened for this patch.
> > It has returned due to slight confusion thinking that this is same as:
> > Patch for option in pg_resetxlog for restore from WAL files (https://commitfest.postgresql.org/action/patch_view?id=897 ) for which Heikki has given some comments.
>
> Oops, sorry, my mistake. Please reopen it as needing review.

Done.

> I will
> move all the open patches to CF3, unless someone beats me to it. I
> probably won't be able to get anything done tomorrow, so if somebody has
> a boring Sunday I would appreciate the help.

Likewise.

On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>>> 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

I wonder if we shouldn't make this a separate utility, rather than
something that is part of pg_resetxlog. Anyone have a thought on that
topic?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas escribió:
> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:

> >> 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
>
> I wonder if we shouldn't make this a separate utility, rather than
> something that is part of pg_resetxlog. Anyone have a thought on that
> topic?

That thought did cross my mind too.

--

Noah Misch wrote:
> On Sun, Nov 11, 2012 at 02:18:11AM -0300, Alvaro Herrera wrote:

> > I will
> > move all the open patches to CF3, unless someone beats me to it. I
> > probably won't be able to get anything done tomorrow, so if somebody has
> > a boring Sunday I would appreciate the help.
>
> Likewise.

Many thanks. I have closed the CF (just 3 days before the next one
starts, which is somewhat depressing).

--

On Monday, November 12, 2012 9:56 PM Alvaro Herrera wrote:
Robert Haas escribió:
> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:

>> >> 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
>
>> I wonder if we shouldn't make this a separate utility, rather than
>> something that is part of pg_resetxlog. Anyone have a thought on that
>> topic?

> That thought did cross my mind too.

One of the reasons for keeping it with pg_resetxlog, is that this was proposed as a solution for scenario's where user's db has become corrupt and now he
want to start it. So to do it he can find the max LSN and set the same using pg_resetxlog, it will avoid the further corruption of database after it got started.
If we keep it a separate utility then user needs to first run this utility to find max LSN and then use pg_resetxlog to achieve the same. I don't see a big problem in that
but may be it would have been better if there are other usecases for it.

However it might be used for other purpose also which I am not able to think.

Do you have any particular reasons for having it a separate utility?

With Regards,
Amit Kapila.

On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Monday, November 12, 2012 9:56 PM Alvaro Herrera wrote:
> Robert Haas escribió:
>> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>
>>> >> 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
>>
>>> I wonder if we shouldn't make this a separate utility, rather than
>>> something that is part of pg_resetxlog. Anyone have a thought on that
>>> topic?
>
>> That thought did cross my mind too.
>
> One of the reasons for keeping it with pg_resetxlog, is that this was proposed as a solution for scenario's where user's db has become corrupt and now he
> want to start it. So to do it he can find the max LSN and set the same using pg_resetxlog, it will avoid the further corruption of database after it got started.
> If we keep it a separate utility then user needs to first run this utility to find max LSN and then use pg_resetxlog to achieve the same. I don't see a big problem in that
> but may be it would have been better if there are other usecases for it.

We might be able to use this utility to decide whether we need to take
a fresh backup from the master onto the standby, to start old master
as new standby after failover.

When starting new standby after failover, any data page in the standby must
not precede the master. Otherwise, the standby cannot catch up with the master
consistently. But, the master might write the data page corresponding to
the WAL which has not been replicated to the standby yet. So, if
failover happens
before that WAL has been replicated, the data page in old master would precede
new master (i.e., old standby), and in this case the backup is required. OTOH,
if maximum LSN in data page in the standby is less than the master, the backup
is not required.

Without this utility, it's difficult to calculate the maximum LSN of
data page, so
basically we needed to take a backup when starting the standby. In the future,
thanks to this utility, we can calculate the maximum LSN, and can skip a backup
if that LSN is less than the master (i.e., last applied LSN, IOW,
timeline switch LSN).

Regards,

--
Fujii Masao

On Tue, Nov 13, 2012 at 11:46 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> Without this utility, it's difficult to calculate the maximum LSN of
> data page, so
> basically we needed to take a backup when starting the standby. In the future,
> thanks to this utility, we can calculate the maximum LSN, and can skip a backup
> if that LSN is less than the master (i.e., last applied LSN, IOW,
> timeline switch LSN).

Doesn't the minimum recovery point give us that?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tuesday, November 13, 2012 10:17 PM Fujii Masao wrote:
> On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> > On Monday, November 12, 2012 9:56 PM Alvaro Herrera wrote:
> > Robert Haas escribió:
> >> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> >
> >>> >> 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

> >>> I wonder if we shouldn't make this a separate utility, rather than
> >>> something that is part of pg_resetxlog. Anyone have a thought on
> that
> >>> topic?
> >
> >> That thought did cross my mind too.
> >
> We might be able to use this utility to decide whether we need to take
> a fresh backup from the master onto the standby, to start old master
> as new standby after failover.
>
> When starting new standby after failover, any data page in the standby
> must
> not precede the master. Otherwise, the standby cannot catch up with the
> master
> consistently. But, the master might write the data page corresponding to
> the WAL which has not been replicated to the standby yet. So, if
> failover happens
> before that WAL has been replicated, the data page in old master would
> precede
> new master (i.e., old standby), and in this case the backup is required.
> OTOH,
> if maximum LSN in data page in the standby is less than the master, the
> backup
> is not required.

When new standby will start the replication (RequestXLogStreaming()), it
will
send the startpoint, so won't in above scenario that startpoint will be
ahead of new master
(or new master won't have that LSN) and replication will not be
eastablished?

So now user may not be able to decide whether he needs to do incremental or
full backup from new master,
is this the case you are trying to point?

> Without this utility, it's difficult to calculate the maximum LSN of
> data page, so
> basically we needed to take a backup when starting the standby. In the
> future,
> thanks to this utility, we can calculate the maximum LSN, and can skip a
> backup
> if that LSN is less than the master (i.e., last applied LSN, IOW,
> timeline switch LSN).

With Regards,
Amit Kapila.

On Wed, Nov 14, 2012 at 5:53 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Nov 13, 2012 at 11:46 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> Without this utility, it's difficult to calculate the maximum LSN of
>> data page, so
>> basically we needed to take a backup when starting the standby. In the future,
>> thanks to this utility, we can calculate the maximum LSN, and can skip a backup
>> if that LSN is less than the master (i.e., last applied LSN, IOW,
>> timeline switch LSN).
>
> Doesn't the minimum recovery point give us that?

Yes, but only in the standby. The master doesn't record the minimum recovery
point at all. So, when we start the pre-master as new standby after failover,
we need this utility to know that LSN. Or we need to change the master so that
it records the minimum recovery point like the standby.

BTW, it might be useful to introduce new replication option that makes the data
page fush wait for its corresponding WAL to be replicated. By using this option,
we can ensure that any data page in the master always precede the standby.

Regards,

--
Fujii Masao

On Wed, Nov 14, 2012 at 5:35 PM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Tuesday, November 13, 2012 10:17 PM Fujii Masao wrote:
>> On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
>> wrote:
>> > On Monday, November 12, 2012 9:56 PM Alvaro Herrera wrote:
>> > Robert Haas escribió:
>> >> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
>> wrote:
>> >
>> >>> >> 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
>
>> >>> I wonder if we shouldn't make this a separate utility, rather than
>> >>> something that is part of pg_resetxlog. Anyone have a thought on
>> that
>> >>> topic?
>> >
>> >> That thought did cross my mind too.
>> >
>> We might be able to use this utility to decide whether we need to take
>> a fresh backup from the master onto the standby, to start old master
>> as new standby after failover.
>>
>> When starting new standby after failover, any data page in the standby
>> must
>> not precede the master. Otherwise, the standby cannot catch up with the
>> master
>> consistently. But, the master might write the data page corresponding to
>> the WAL which has not been replicated to the standby yet. So, if
>> failover happens
>> before that WAL has been replicated, the data page in old master would
>> precede
>> new master (i.e., old standby), and in this case the backup is required.
>> OTOH,
>> if maximum LSN in data page in the standby is less than the master, the
>> backup
>> is not required.
>
> When new standby will start the replication (RequestXLogStreaming()), it
> will
> send the startpoint, so won't in above scenario that startpoint will be
> ahead of new master
> (or new master won't have that LSN) and replication will not be
> eastablished?

The startpoint is the heading LSN of the WAL file including the latest
checkpoint record. Yes, there can be the case where the startpoint is
ahead of new master. In this case, replication would fail to be established
because of lack of requested WAL file. OTOH, there can be the case
where new master has already been ahead of the startpoint.

> So now user may not be able to decide whether he needs to do incremental or
> full backup from new master,
> is this the case you are trying to point?

Sorry, I could not parse this comment. Could you elaborate your concern again?

Regards,

--
Fujii Masao

On Wednesday, November 14, 2012 9:12 PM Fujii Masao wrote:
> On Wed, Nov 14, 2012 at 5:35 PM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> > On Tuesday, November 13, 2012 10:17 PM Fujii Masao wrote:
> >> On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
> >> wrote:
> >> > On Monday, November 12, 2012 9:56 PM Alvaro Herrera wrote:
> >> > Robert Haas escribió:
> >> >> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila
> <amit(dot)kapila(at)huawei(dot)com>
> >> wrote:
> >> >
> >> >>> >> 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
> >
> >> >>> I wonder if we shouldn't make this a separate utility, rather
> than
> >> >>> something that is part of pg_resetxlog. Anyone have a thought on
> >> that
> >> >>> topic?
> >> >
> >> >> That thought did cross my mind too.
> >> >
> >> We might be able to use this utility to decide whether we need to
> take
> >> a fresh backup from the master onto the standby, to start old master
> >> as new standby after failover.
> >>
> >> When starting new standby after failover, any data page in the
> standby
> >> must
> >> not precede the master. Otherwise, the standby cannot catch up with
> the
> >> master
> >> consistently. But, the master might write the data page corresponding
> to
> >> the WAL which has not been replicated to the standby yet. So, if
> >> failover happens
> >> before that WAL has been replicated, the data page in old master
> would
> >> precede
> >> new master (i.e., old standby), and in this case the backup is
> required.
> >> OTOH,
> >> if maximum LSN in data page in the standby is less than the master,
> the
> >> backup
> >> is not required.
> >
> > When new standby will start the replication (RequestXLogStreaming()),
> it
> > will
> > send the startpoint, so won't in above scenario that startpoint will
> be
> > ahead of new master
> > (or new master won't have that LSN) and replication will not be
> > eastablished?
>
> The startpoint is the heading LSN of the WAL file including the latest
> checkpoint record. Yes, there can be the case where the startpoint is
> ahead of new master. In this case, replication would fail to be
> established
> because of lack of requested WAL file.

Now user can use this utility to decide if new-standby has max LSN greater
than max LSN of new-master he needs to use fullback-up on new-standby. Is my
understanding right?

>OTOH, there can be the case
> where new master has already been ahead of the startpoint.

But in this case, there is no need for this utility. Right?

> > So now user may not be able to decide whether he needs to do
> incremental or
> > full backup from new master,
> > is this the case you are trying to point?
>
> Sorry, I could not parse this comment. Could you elaborate your concern
> again?

I wanted to understand the usecase mentioned by you for this utility.
As far as I can understand is that it will be used to decide that on
new-standby (old-master) whether a full backup is needed from
New-master(old-standby).
And that situation can occur when new-standby has startpoint LSN greater
than new-master?

With Regards,
Amit Kapila.

On Thu, Nov 15, 2012 at 12:55 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> Now user can use this utility to decide if new-standby has max LSN greater
> than max LSN of new-master he needs to use fullback-up on new-standby. Is my
> understanding right?

No. The maximum LSN of data pages in new-standby should be compared with
the last replayed LSN (IOW, the last valid LSN of previous timeline)
of new-master.

>>OTOH, there can be the case
>> where new master has already been ahead of the startpoint.
>
>
> But in this case, there is no need for this utility. Right?

No. The above comparison is required in this case.

>
>> > So now user may not be able to decide whether he needs to do
>> incremental or
>> > full backup from new master,
>> > is this the case you are trying to point?
>>
>> Sorry, I could not parse this comment. Could you elaborate your concern
>> again?
>
> I wanted to understand the usecase mentioned by you for this utility.
> As far as I can understand is that it will be used to decide that on
> new-standby (old-master) whether a full backup is needed from
> New-master(old-standby).

Yes.

> And that situation can occur when new-standby has startpoint LSN greater
> than new-master?

Whether the backup is required has nothing to do with the startpoint.
The backup is required when the data page in old-master precedes
the last applied LSN in old-standby (i.e., new-master) at the moment
of the failover. Without the backup, there is no way to revert the data
which is ahead of new-master.

Regards,

--
Fujii Masao

On Wednesday, November 14, 2012 10:19 PM Fujii Masao wrote:
> On Thu, Nov 15, 2012 at 12:55 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> > Now user can use this utility to decide if new-standby has max LSN
> greater
>
> > And that situation can occur when new-standby has startpoint LSN
> greater
> > than new-master?
>
> Whether the backup is required has nothing to do with the startpoint.
> The backup is required when the data page in old-master precedes
> the last applied LSN in old-standby (i.e., new-master) at the moment
> of the failover. Without the backup, there is no way to revert the data
> which is ahead of new-master.

Okay.
So as Robert and Alvaro suggested to have it separate utility rather than
having options in pg_resetxlog to print MAX LSN seems to be quite
appropriate.
I am planning to update the patch to make it a separate utility as
pg_computemaxlsn with options same as what I have proposed for pg_resetxlog
to print MAX LSN.
So considering it a separate utility there can be 2 options:
a. have a utility in contrib.
b. have a utility in bin similar to pg_resetxlog.

What is the best place to have it?

With Regards,
Amit Kapila.

On Thu, Nov 15, 2012 at 12:08 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> Okay.
> So as Robert and Alvaro suggested to have it separate utility rather than
> having options in pg_resetxlog to print MAX LSN seems to be quite
> appropriate.
> I am planning to update the patch to make it a separate utility as
> pg_computemaxlsn with options same as what I have proposed for pg_resetxlog
> to print MAX LSN.
> So considering it a separate utility there can be 2 options:
> a. have a utility in contrib.
> b. have a utility in bin similar to pg_resetxlog

I guess I'd vote for contrib, but I wouldn't be crushed if it went the
other way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thursday, November 15, 2012 7:30 PM Robert Haas wrote:
On Thu, Nov 15, 2012 at 12:08 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>> Okay.
>> So as Robert and Alvaro suggested to have it separate utility rather than
>> having options in pg_resetxlog to print MAX LSN seems to be quite
>> appropriate.
>> I am planning to update the patch to make it a separate utility as
>> pg_computemaxlsn with options same as what I have proposed for pg_resetxlog
>> to print MAX LSN.
>> So considering it a separate utility there can be 2 options:
>> a. have a utility in contrib.
>> b. have a utility in bin similar to pg_resetxlog

> I guess I'd vote for contrib, but I wouldn't be crushed if it went the
> other way.

Updated test cases and patch to have separate utility in contrib for pg_computemaxlsn are attached with this mail.

With Regards,
Amit Kapila.