Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Thursday, November 15, 2012 7:30 PM Robert Haas wrote: On Thu, Nov 15, 2012 at 12:08 AM, Amit Kapila amit.kap...@huawei.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. pg_computemaxlsn.patch Description: pg_computemaxlsn.patch -- Test case 1 drop table if exists tbl; create table tbl(f1 int, f2 varchar(10), f3 float, f4 char(200)); insert into tbl values(1,'postgres',2.1,'test'); insert into tbl values(3,'bangalore',4.1,'test'); insert into tbl values(2,'kiran',3.1,'test'); insert into tbl values(4,'india',5.1,'test'); checkpoint; -- stop the server -- run the following command pg_computemaxlsn -P data -- Validate the LSN number and WAL fileid, segid currently running in pg_xlog folder. -- start the server and execute the following drop table tbl; -- Test case 2 drop table if exists tbl; create table tbl(f1 int, f2 varchar(10), f3 float, f4 char(200)); insert into tbl values(1,'postgres',2.1,'test'); insert into tbl values(3,'bangalore',4.1,'test'); insert into tbl values(2,'kiran',3.1,'test'); insert into tbl values(4,'india',5.1,'test'); checkpoint; -- stop the server -- run the following command pg_computemaxlsn -p base/12557 data -- Validate the LSN number and WAL fileid, segid currently running in pg_xlog folder. -- start the server and execute the following drop table tbl; -- Test case 3 drop table if exists tbl; create table tbl(f1 int, f2 varchar(10), f3 float, f4 char(200)); insert into tbl values(1,'postgres',2.1,'test'); insert into tbl values(3,'bangalore',4.1,'test'); insert into tbl values(2,'kiran',3.1,'test'); insert into tbl values(4,'india',5.1,'test'); --Find the relfile node select relfilenode from pg_class where relname='tbl'; checkpoint; -- stop the server -- run the following command with displayed refilenode pg_computemaxlsn -p base/12557/16384 data -- Validate the LSN number and WAL fileid, segid currently running in pg_xlog folder. -- start the server and execute the following drop table tbl; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Thu, Nov 15, 2012 at 12:08 AM, Amit Kapila amit.kap...@huawei.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Wed, Nov 14, 2012 at 5:53 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 13, 2012 at 11:46 AM, Fujii Masao masao.fu...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Wed, Nov 14, 2012 at 5:35 PM, Amit Kapila amit.kap...@huawei.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.kap...@huawei.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.kap...@huawei.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Wednesday, November 14, 2012 9:12 PM Fujii Masao wrote: On Wed, Nov 14, 2012 at 5:35 PM, Amit Kapila amit.kap...@huawei.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.kap...@huawei.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.kap...@huawei.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Thu, Nov 15, 2012 at 12:55 AM, Amit Kapila amit.kap...@huawei.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Wednesday, November 14, 2012 10:19 PM Fujii Masao wrote: On Thu, Nov 15, 2012 at 12:55 AM, Amit Kapila amit.kap...@huawei.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Tuesday, November 13, 2012 10:17 PM Fujii Masao wrote: On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila amit.kap...@huawei.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.kap...@huawei.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila amit.kap...@huawei.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.kap...@huawei.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Tue, Nov 13, 2012 at 11:46 AM, Fujii Masao masao.fu...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila amit.kap...@huawei.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
Robert Haas escribió: On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila amit.kap...@huawei.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. -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
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). -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
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.kap...@huawei.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
This patch is now marked Returned with Feedback in the CF, but I see no on-list feedback. Did some review happen? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to compute Max LSN of Data Pages
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. -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [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@linux: pg_resetxlog -P /home/postgres/installation/bin/data
Maximum LSN found is: 73325448, WAL segment file name (fileid, seg):
0004
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.
diff --git a/src/bin/pg_resetxlog/pg_resetxlog.c
b/src/bin/pg_resetxlog/pg_resetxlog.c
index d5d89ec..dcb62d1 100644
--- a/src/bin/pg_resetxlog/pg_resetxlog.c
+++ b/src/bin/pg_resetxlog/pg_resetxlog.c
@@ -54,6 +54,20 @@
#include access/xlog_internal.h
#include catalog/catversion.h
#include catalog/pg_control.h
+#include catalog/catalog.h
+#include storage/bufpage.h
+#include storage/fd.h
+
+
+/* Page header size */
+#define PAGEHDRSZ (sizeof(PageHeaderData))
+
+
+/*
+ * relfile nodename validation allow only file name start with digit
+ */
+#define validateRelfilenodename(name) ((name[0] = '0') (name[0] = '9'))
+
extern int optind;
extern char *optarg;
@@ -72,6 +86,9 @@ static void FindEndOfXLOG(void);
static void KillExistingXLOG(void);
static void KillExistingArchiveStatus(void);
static void WriteEmptyXLOG(void);
+static void FindMaxLSNinFile(char *filename, XLogRecPtr *maxlsn);
+static void FindMaxLSNinDir(char *path, XLogRecPtr *maxlsn);
+static void FindMaxLSNinPgData(XLogRecPtr *maxlsn);
static void usage(void);
@@ -92,6 +109,10 @@ main(int argc, char *argv[])
char *DataDir;
int fd;
charpath[MAXPGPATH];
+ boolprint_max_lsn = false;
+ boolprint_pgdata_max_lsn = false;
+
