[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): 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.
Attachments:
pg_resetxlog_find_max_lsn_from_datafiles.patchtext/plain; name=pg_resetxlog_find_max_lsn_from_datafiles.patchDownload
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;
char path[MAXPGPATH];
+ bool print_max_lsn = false;
+ bool print_pgdata_max_lsn = false;
+ char *LsnSearchPath;
+ uint64 maxLSN = 0;
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_resetxlog"));
@@ -112,7 +133,7 @@ main(int argc, char *argv[])
}
- while ((c = getopt(argc, argv, "fl:m:no:O:x:e:")) != -1)
+ while ((c = getopt(argc, argv, "fl:m:no:O:x:e:p:P")) != -1)
{
switch (c)
{
@@ -209,6 +230,15 @@ main(int argc, char *argv[])
XLogFromFileName(optarg, &minXlogTli, &minXlogSegNo);
break;
+ case 'p':
+ print_max_lsn = true;
+ LsnSearchPath = optarg;
+ break;
+
+ case 'P':
+ print_pgdata_max_lsn = true;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -270,6 +300,55 @@ main(int argc, char *argv[])
exit(1);
}
+
+ if (print_max_lsn || print_pgdata_max_lsn)
+ {
+ XLogSegNo logSegNo = 0;
+
+ if (print_pgdata_max_lsn)
+ {
+ FindMaxLSNinPgData(&maxLSN);
+ }
+ else
+ {
+ struct stat fst;
+
+ if (stat(LsnSearchPath, &fst) < 0)
+ {
+ if (errno == ENOENT)
+ {
+ fprintf(stderr, _("%s: file or directory \"%s\" does not exists"),
+ progname, LsnSearchPath);
+ }
+ else
+ {
+ fprintf(stderr, _("%s: file or directory \"%s\" exists\n"
+ "\n"),
+ progname, LsnSearchPath);
+ }
+ exit(1);
+ }
+
+ if (S_ISDIR(fst.st_mode))
+ {
+ FindMaxLSNinDir(LsnSearchPath, &maxLSN);
+ }
+ else
+ {
+ FindMaxLSNinFile(LsnSearchPath, &maxLSN);
+ }
+ }
+
+ XLByteToSeg(maxLSN, logSegNo);
+
+ printf ("Maximum LSN found is: " UINT64_FORMAT ", "
+ "WAL segment file name (fileid,seg): %08X%08X\n",
+ maxLSN,
+ (uint32) ((logSegNo) / XLogSegmentsPerXLogId),
+ (uint32) ((logSegNo) % XLogSegmentsPerXLogId));
+ exit(0);
+ }
+
/*
* Attempt to read the existing pg_control file
*/
@@ -986,6 +1065,290 @@ WriteEmptyXLOG(void)
}
+/*
+ * PageHeaderIsValid: Check page is valid or not
+ */
+bool
+PageHeaderIsValid(PageHeader page)
+{
+ char *pagebytes;
+ int i;
+
+ /* Check normal case */
+ if (PageGetPageSize(page) == BLCKSZ &&
+ PageGetPageLayoutVersion(page) == PG_PAGE_LAYOUT_VERSION &&
+ (page->pd_flags & ~PD_VALID_FLAG_BITS) == 0 &&
+ page->pd_lower >= SizeOfPageHeaderData &&
+ page->pd_lower <= page->pd_upper &&
+ page->pd_upper <= page->pd_special &&
+ page->pd_special <= BLCKSZ &&
+ page->pd_special == MAXALIGN(page->pd_special))
+ return true;
+
+ /*
+ * Check all-zeroes till page header; this is used only to log the page
+ * details even we detect invalid page we will continue to nex pages
+ */
+ pagebytes = (char *) page;
+ for (i = 0; i < PAGEHDRSZ; i++)
+ {
+ if (pagebytes[i] != 0)
+ return false;
+ }
+ return true;
+}
+
+
+/*
+ * Read the maximum LSN number in the one of data file (relnode file).
+ *
+ */
+static void
+FindMaxLSNinFile(char *filename, XLogRecPtr *maxlsn)
+{
+ XLogRecPtr pagelsn;
+ off_t len,
+ seekpos;
+ uint32 nblocks,
+ blocknum;
+ char buffer[PAGEHDRSZ];
+ int nbytes;
+ int fd;
+
+ if ((fd = open(filename, O_RDONLY | PG_BINARY, 0)) < 0)
+ {
+ /*
+ * If file does not exist or or we can't read it. give error
+ */
+ fprintf(stderr, _("%s: could not open file \"%s\" for reading: %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ /* Calculate the number of pages in file */
+ len = lseek(fd, 0L, SEEK_END);
+ if (len < 0)
+ {
+ close(fd);
+ fprintf(stderr, _("%s: .. file \"%s\" for seeking: %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ nblocks = (len / BLCKSZ);
+ if (nblocks > RELSEG_SIZE)
+ {
+ /*
+ * In one relfilenode file length can't be more that RELSEG_SIZE
+ */
+ close(fd);
+ fprintf(stderr, _("%s: .. file \"%s\" legth is more than RELSEG_SIZE.\n"),
+ progname, filename);
+ return;
+ }
+
+ /*
+ * Read the only page header and validate; if we find invalid page log the
+ * details of page and continue to next page.
+ */
+ seekpos = 0;
+ for (blocknum = 0; blocknum < nblocks; blocknum++)
+ {
+ len = lseek(fd, seekpos, SEEK_SET);
+ if (len != seekpos)
+ {
+ close(fd);
+ fprintf(stderr, _("%s: could not seek to next page \"%s\": %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ nbytes = read(fd, buffer, PAGEHDRSZ);
+ if (nbytes < 0)
+ {
+ close(fd);
+ fprintf(stderr, _("%s: could not read file \"%s\": %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ if (PageHeaderIsValid((PageHeader) buffer))
+ {
+ pagelsn = PageGetLSN(buffer);
+ if (XLByteLE(*maxlsn, pagelsn))
+ {
+ *maxlsn = pagelsn;
+ }
+ }
+ else
+ {
+ /*
+ * If page is invalid log the error and continue
+ */
+ fprintf(stderr, _("%s: Invalid page found in file \"%s\" pagid:%d\n"),
+ progname, filename, blocknum);
+ }
+ seekpos += (off_t) BLCKSZ;
+ }
+
+ close(fd);
+ return;
+}
+
+/*
+ * Read the maximum LSN number in current directory; ignore sub directories.
+ */
+static void
+FindMaxLSNinDir(char *path, XLogRecPtr *maxlsn)
+{
+ DIR *dir;
+ struct dirent *de;
+ char pathbuf[MAXPGPATH];
+ struct stat statbuf;
+
+ dir = opendir(path);
+ if (NULL == dir)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, path, strerror(errno));
+ return;
+ }
+
+ while ((de = readdir(dir)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de->d_name, ".") == 0 || strcmp(de->d_name, "..") == 0)
+ continue;
+
+ /* Skip temporary files */
+ if (strncmp(de->d_name,
+ PG_TEMP_FILE_PREFIX,
+ strlen(PG_TEMP_FILE_PREFIX)) == 0)
+ continue;
+
+ /*
+ * Skip all the local/global temporary files, and read and read all
+ * reamining relfinenode files
+ */
+ if (!validateRelfilenodename(de->d_name))
+ continue;
+
+ snprintf(pathbuf, MAXPGPATH, "%s/%s", path, de->d_name);
+
+ if (stat(pathbuf, &statbuf) != 0)
+ {
+ if (errno != ENOENT)
+ {
+ fprintf(stderr, "could not stat file or directory \"%s\"",
+ pathbuf);
+ }
+ /* If the file went away while scanning, it's no error. */
+ continue;
+ }
+
+ /*
+ * Skip all directories
+ */
+ if (!S_ISDIR(statbuf.st_mode))
+ {
+ FindMaxLSNinFile(pathbuf, maxlsn);
+ }
+ }
+
+ closedir(dir);
+ return;
+}
+
+/*
+ * Read the maximum LSN number in the DATA directory.
+ */
+static void
+FindMaxLSNinPgData(XLogRecPtr *maxlsn)
+{
+ DIR *dir,
+ *dir2;
+ struct dirent *de,
+ *de2;
+ char pathbuf[MAXPGPATH],
+ pathbuf2[MAXPGPATH];
+
+ /*
+ * scan all the relfilenodes in global directory
+ */
+ FindMaxLSNinDir("global", maxlsn);
+
+ /*
+ * scan all the relfilenodes in base directory like base/<database-id>
+ */
+ dir = opendir("base");
+ if (NULL == dir)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, "base", strerror(errno));
+ return;
+ }
+
+ while ((de = readdir(dir)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de->d_name, ".") == 0 || strcmp(de->d_name, "..") == 0)
+ continue;
+
+ /* Skip template database */
+ if (strcmp(de->d_name, "0") == 0 || strcmp(de->d_name, "1") == 0)
+ continue;
+
+ snprintf(pathbuf, MAXPGPATH, "%s/%s", "base", de->d_name);
+
+ FindMaxLSNinDir(pathbuf, maxlsn);
+ }
+ closedir(dir);
+
+ /* Scan all tablespaces */
+ dir = opendir("pg_tblspc");
+ if (NULL == dir)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, "pg_tblspc", strerror(errno));
+ return;
+ }
+
+ while ((de = readdir(dir)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de->d_name, ".") == 0 || strcmp(de->d_name, "..") == 0)
+ continue;
+
+ /*
+ * Scan all directories in tablespace
+ * ./pg_tblspc/tablespaceid/TABLESPACE_VERSION_DIRECTORY/ *
+ */
+ snprintf(pathbuf, MAXPGPATH, "%s/%s/%s", "pg_tblspc", de->d_name, TABLESPACE_VERSION_DIRECTORY);
+ dir2 = opendir(pathbuf);
+ if (NULL == dir2)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, pathbuf, strerror(errno));
+ return;
+ }
+
+ while ((de2 = readdir(dir2)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de2->d_name, ".") == 0 || strcmp(de2->d_name, "..") == 0)
+ continue;
+
+ snprintf(pathbuf2, MAXPGPATH, "%s/%s", pathbuf, de2->d_name);
+
+ FindMaxLSNinDir(pathbuf2, maxlsn);
+ }
+ closedir(dir2);
+ }
+ closedir(dir);
+
+ return;
+}
+
static void
usage(void)
{
@@ -997,6 +1360,8 @@ usage(void)
printf(_(" -l xlogfile force minimum WAL starting location for new transaction log\n"));
printf(_(" -m XID set next multitransaction ID\n"));
printf(_(" -n no update, just show extracted control values (for testing)\n"));
+ printf(_(" -p {file | dir} print max LSN from specified file or directory path\n"));
+ printf(_(" -P print max LSN from whole database\n"));
printf(_(" -o OID set next OID\n"));
printf(_(" -O OFFSET set next multitransaction offset\n"));
printf(_(" -V, --version output version information, then exit\n"));
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.kapila@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 individualfile, 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 atthat 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.kapila@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.
--
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.kapila@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.
On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit.kapila@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.kapila@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
On Tue, Nov 13, 2012 at 11:46 AM, Fujii Masao <masao.fujii@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
On Tuesday, November 13, 2012 10:17 PM Fujii Masao wrote:
On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit.kapila@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.kapila@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 onthat
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@gmail.com> wrote:
On Tue, Nov 13, 2012 at 11:46 AM, Fujii Masao <masao.fujii@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
On Wed, Nov 14, 2012 at 5:35 PM, Amit Kapila <amit.kapila@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.kapila@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.kapila@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 onthat
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
Import Notes
Reply to msg id not found: 50a357f3.6724b60a.4e40.0da4SMTPIN_ADDED@mx.google.com
On Wednesday, November 14, 2012 9:12 PM Fujii Masao wrote:
On Wed, Nov 14, 2012 at 5:35 PM, Amit Kapila <amit.kapila@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.kapila@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.kapila@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 withthe
master
consistently. But, the master might write the data page correspondingto
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 masterwould
precede
new master (i.e., old standby), and in this case the backup isrequired.
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 willbe
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.kapila@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
Import Notes
Reply to msg id not found: 50a3bf0d.08f3440a.48b3.1394SMTPIN_ADDED@mx.google.com
On Wednesday, November 14, 2012 10:19 PM Fujii Masao wrote:
On Thu, Nov 15, 2012 at 12:55 AM, Amit Kapila <amit.kapila@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.
On Thu, Nov 15, 2012 at 12:08 AM, Amit Kapila <amit.kapila@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
Import Notes
Reply to msg id not found: 50a478dc.6748b60a.28bc.fffff7d5SMTPIN_ADDED@mx.google.com
On Thursday, November 15, 2012 7:30 PM Robert Haas wrote:
On Thu, Nov 15, 2012 at 12:08 AM, Amit Kapila <amit.kapila@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.
Attachments:
pg_computemaxlsn.patchapplication/octet-stream; name=pg_computemaxlsn.patchDownload
*** a/contrib/Makefile
--- b/contrib/Makefile
***************
*** 31,36 **** SUBDIRS = \
--- 31,37 ----
passwordcheck \
pg_archivecleanup \
pg_buffercache \
+ pg_computemaxlsn \
pg_freespacemap \
pg_standby \
pg_stat_statements \
*** /dev/null
--- b/contrib/pg_computemaxlsn/Makefile
***************
*** 0 ****
--- 1,22 ----
+ # contrib/pg_computemaxlsn/Makefile
+
+ PGFILEDESC = "pg_computemaxlsn - an utility to find max LSN from data pages"
+ PGAPPICON = win32
+
+ PROGRAM = pg_computemaxlsn
+ OBJS = pg_computemaxlsn.o $(WIN32RES)
+
+ PG_CPPFLAGS = -I$(srcdir)
+ PG_LIBS = $(libpq_pgport)
+
+
+ ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS := $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/pg_computemaxlsn
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
*** /dev/null
--- b/contrib/pg_computemaxlsn/pg_computemaxlsn.c
***************
*** 0 ****
--- 1,502 ----
+ /*-------------------------------------------------------------------------
+ *
+ * pg_computemaxlsn.c
+ * A utility to compute the maximum LSN in data pages
+ *
+ * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * contrib/pg_computemaxlsn/pg_computemaxlsn.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+ /*
+ * We have to use postgres.h not postgres_fe.h here, because there's so much
+ * backend-only stuff for reading data files we need. But we need a
+ * frontend-ish environment otherwise. Hence this ugly hack.
+ */
+ #define FRONTEND 1
+
+ #include "postgres.h"
+
+ #include <dirent.h>
+ #include <fcntl.h>
+ #include <locale.h>
+ #include <sys/stat.h>
+ #include <sys/time.h>
+ #include <time.h>
+ #include <unistd.h>
+ #ifdef HAVE_GETOPT_H
+ #include <getopt.h>
+ #endif
+
+ #include "access/xlog_internal.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;
+ static const char *progname;
+
+ static void FindMaxLSNinFile(char *filename, XLogRecPtr *maxlsn);
+ static void FindMaxLSNinDir(char *path, XLogRecPtr *maxlsn);
+ static void FindMaxLSNinPgData(XLogRecPtr *maxlsn);
+ static void usage(void);
+
+ int
+ main(int argc, char *argv[])
+ {
+ int c;
+ char *DataDir;
+ int fd;
+ char path[MAXPGPATH];
+ bool print_max_lsn = false;
+ bool print_pgdata_max_lsn = false;
+ char *LsnSearchPath = NULL;
+ uint64 maxLSN = 0;
+ XLogSegNo logSegNo = 0;
+
+ set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_computemaxlsn"));
+
+ progname = get_progname(argv[0]);
+
+ if (argc > 1)
+ {
+ if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
+ {
+ usage();
+ exit(0);
+ }
+ if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
+ {
+ puts("pg_computemaxlsn (PostgreSQL) " PG_VERSION);
+ exit(0);
+ }
+ }
+
+ while ((c = getopt(argc, argv, "p:P")) != -1)
+ {
+ switch (c)
+ {
+ case 'p':
+ print_max_lsn = true;
+ LsnSearchPath = optarg;
+ break;
+
+ case 'P':
+ print_pgdata_max_lsn = true;
+ break;
+
+ default:
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ }
+ }
+
+ if (optind == argc)
+ {
+ fprintf(stderr, _("%s: no data directory specified\n"), progname);
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ }
+
+ /*
+ * Don't allow pg_computemaxlsn to be run as root, to avoid overwriting the
+ * ownership of files in the data directory. We need only check for root
+ * -- any other user won't have sufficient permissions to modify files in
+ * the data directory.
+ */
+ #ifndef WIN32
+ if (geteuid() == 0)
+ {
+ fprintf(stderr, _("%s: cannot be executed by \"root\"\n"),
+ progname);
+ fprintf(stderr, _("You must run %s as the PostgreSQL superuser.\n"),
+ progname);
+ exit(1);
+ }
+ #endif
+
+ DataDir = argv[optind];
+
+ if (chdir(DataDir) < 0)
+ {
+ fprintf(stderr, _("%s: could not change directory to \"%s\": %s\n"),
+ progname, DataDir, strerror(errno));
+ exit(1);
+ }
+
+ /*
+ * Check for a postmaster lock file --- if there is one, refuse to
+ * proceed, on grounds we might be interfering with a live installation.
+ */
+ snprintf(path, MAXPGPATH, "postmaster.pid");
+
+ if ((fd = open(path, O_RDONLY, 0)) < 0)
+ {
+ if (errno != ENOENT)
+ {
+ fprintf(stderr, _("%s: could not open file \"%s\" for reading: %s\n"), progname, path, strerror(errno));
+ exit(1);
+ }
+ }
+ else
+ {
+ fprintf(stderr, _("%s: lock file \"%s\" exists\n"
+ "Is a server running? If not, delete the lock file and try again.\n"),
+ progname, path);
+ exit(1);
+ }
+
+ if (print_pgdata_max_lsn)
+ {
+ FindMaxLSNinPgData(&maxLSN);
+ }
+ else
+ {
+ struct stat fst;
+
+ if (stat(LsnSearchPath, &fst) < 0)
+ {
+ if (errno == ENOENT)
+ {
+ fprintf(stderr, _("%s: file or directory \"%s\" does not exists"),
+ progname, LsnSearchPath);
+ }
+ else
+ {
+ fprintf(stderr, _("%s: file or directory \"%s\" exists\n"
+ "\n"),
+ progname, LsnSearchPath);
+ }
+ exit(1);
+ }
+
+ if (S_ISDIR(fst.st_mode))
+ {
+ FindMaxLSNinDir(LsnSearchPath, &maxLSN);
+ }
+ else
+ {
+ FindMaxLSNinFile(LsnSearchPath, &maxLSN);
+ }
+ }
+
+ XLByteToSeg(maxLSN, logSegNo);
+
+ printf ("Maximum LSN found is: " UINT64_FORMAT ", "
+ "WAL segment file name (fileid,seg): %08X%08X\n",
+ maxLSN,
+ (uint32) ((logSegNo) / XLogSegmentsPerXLogId),
+ (uint32) ((logSegNo) % XLogSegmentsPerXLogId));
+
+ return 0;
+ }
+
+
+ /*
+ * PageHeaderIsValid: Check page is valid or not
+ */
+ bool
+ PageHeaderIsValid(PageHeader page)
+ {
+ char *pagebytes;
+ int i;
+
+ /* Check normal case */
+ if (PageGetPageSize(page) == BLCKSZ &&
+ PageGetPageLayoutVersion(page) == PG_PAGE_LAYOUT_VERSION &&
+ (page->pd_flags & ~PD_VALID_FLAG_BITS) == 0 &&
+ page->pd_lower >= SizeOfPageHeaderData &&
+ page->pd_lower <= page->pd_upper &&
+ page->pd_upper <= page->pd_special &&
+ page->pd_special <= BLCKSZ &&
+ page->pd_special == MAXALIGN(page->pd_special))
+ return true;
+
+ /*
+ * Check all-zeroes till page header; this is used only to log the page
+ * details even we detect invalid page we will continue to nex pages
+ */
+ pagebytes = (char *) page;
+ for (i = 0; i < PAGEHDRSZ; i++)
+ {
+ if (pagebytes[i] != 0)
+ return false;
+ }
+ return true;
+ }
+
+
+ /*
+ * Read the maximum LSN number in the one of data file (relnode file).
+ *
+ */
+ static void
+ FindMaxLSNinFile(char *filename, XLogRecPtr *maxlsn)
+ {
+ XLogRecPtr pagelsn;
+ off_t len,
+ seekpos;
+ uint32 nblocks,
+ blocknum;
+ char buffer[PAGEHDRSZ];
+ int nbytes;
+ int fd;
+
+ if ((fd = open(filename, O_RDONLY | PG_BINARY, 0)) < 0)
+ {
+ /*
+ * If file does not exist or or we can't read it. give error
+ */
+ fprintf(stderr, _("%s: could not open file \"%s\" for reading: %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ /* Calculate the number of pages in file */
+ len = lseek(fd, 0L, SEEK_END);
+ if (len < 0)
+ {
+ close(fd);
+ fprintf(stderr, _("%s: .. file \"%s\" for seeking: %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ nblocks = (len / BLCKSZ);
+ if (nblocks > RELSEG_SIZE)
+ {
+ /*
+ * In one relfilenode file length can't be more that RELSEG_SIZE
+ */
+ close(fd);
+ fprintf(stderr, _("%s: .. file \"%s\" legth is more than RELSEG_SIZE.\n"),
+ progname, filename);
+ return;
+ }
+
+ /*
+ * Read the only page header and validate; if we find invalid page log the
+ * details of page and continue to next page.
+ */
+ seekpos = 0;
+ for (blocknum = 0; blocknum < nblocks; blocknum++)
+ {
+ len = lseek(fd, seekpos, SEEK_SET);
+ if (len != seekpos)
+ {
+ close(fd);
+ fprintf(stderr, _("%s: could not seek to next page \"%s\": %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ nbytes = read(fd, buffer, PAGEHDRSZ);
+ if (nbytes < 0)
+ {
+ close(fd);
+ fprintf(stderr, _("%s: could not read file \"%s\": %s\n"),
+ progname, filename, strerror(errno));
+ return;
+ }
+
+ if (PageHeaderIsValid((PageHeader) buffer))
+ {
+ pagelsn = PageGetLSN(buffer);
+ if (XLByteLE(*maxlsn, pagelsn))
+ {
+ *maxlsn = pagelsn;
+ }
+ }
+ else
+ {
+ /*
+ * If page is invalid log the error and continue
+ */
+ fprintf(stderr, _("%s: Invalid page found in file \"%s\" pagid:%d\n"),
+ progname, filename, blocknum);
+ }
+ seekpos += (off_t) BLCKSZ;
+ }
+
+ close(fd);
+ return;
+ }
+
+ /*
+ * Read the maximum LSN number in current directory; ignore sub directories.
+ */
+ static void
+ FindMaxLSNinDir(char *path, XLogRecPtr *maxlsn)
+ {
+ DIR *dir;
+ struct dirent *de;
+ char pathbuf[MAXPGPATH];
+ struct stat statbuf;
+
+ dir = opendir(path);
+ if (NULL == dir)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, path, strerror(errno));
+ return;
+ }
+
+ while ((de = readdir(dir)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de->d_name, ".") == 0 || strcmp(de->d_name, "..") == 0)
+ continue;
+
+ /* Skip temporary files */
+ if (strncmp(de->d_name,
+ PG_TEMP_FILE_PREFIX,
+ strlen(PG_TEMP_FILE_PREFIX)) == 0)
+ continue;
+
+ /*
+ * Skip all the local/global temporary files, and read and read all
+ * reamining relfinenode files
+ */
+ if (!validateRelfilenodename(de->d_name))
+ continue;
+
+ snprintf(pathbuf, MAXPGPATH, "%s/%s", path, de->d_name);
+
+ if (stat(pathbuf, &statbuf) != 0)
+ {
+ if (errno != ENOENT)
+ {
+ fprintf(stderr, "could not stat file or directory \"%s\"",
+ pathbuf);
+ }
+ /* If the file went away while scanning, it's no error. */
+ continue;
+ }
+
+ /*
+ * Skip all directories
+ */
+ if (!S_ISDIR(statbuf.st_mode))
+ {
+ FindMaxLSNinFile(pathbuf, maxlsn);
+ }
+ }
+
+ closedir(dir);
+ return;
+ }
+
+ /*
+ * Read the maximum LSN number in the DATA directory.
+ */
+ static void
+ FindMaxLSNinPgData(XLogRecPtr *maxlsn)
+ {
+ DIR *dir,
+ *dir2;
+ struct dirent *de,
+ *de2;
+ char pathbuf[MAXPGPATH],
+ pathbuf2[MAXPGPATH];
+
+ /*
+ * scan all the relfilenodes in global directory
+ */
+ FindMaxLSNinDir("global", maxlsn);
+
+ /*
+ * scan all the relfilenodes in base directory like base/<database-id>
+ */
+ dir = opendir("base");
+ if (NULL == dir)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, "base", strerror(errno));
+ return;
+ }
+
+ while ((de = readdir(dir)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de->d_name, ".") == 0 || strcmp(de->d_name, "..") == 0)
+ continue;
+
+ /* Skip template database */
+ if (strcmp(de->d_name, "0") == 0 || strcmp(de->d_name, "1") == 0)
+ continue;
+
+ snprintf(pathbuf, MAXPGPATH, "%s/%s", "base", de->d_name);
+
+ FindMaxLSNinDir(pathbuf, maxlsn);
+ }
+ closedir(dir);
+
+ /* Scan all tablespaces */
+ dir = opendir("pg_tblspc");
+ if (NULL == dir)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, "pg_tblspc", strerror(errno));
+ return;
+ }
+
+ while ((de = readdir(dir)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de->d_name, ".") == 0 || strcmp(de->d_name, "..") == 0)
+ continue;
+
+ /*
+ * Scan all directories in tablespace
+ * ./pg_tblspc/tablespaceid/TABLESPACE_VERSION_DIRECTORY/ *
+ */
+ snprintf(pathbuf, MAXPGPATH, "%s/%s/%s", "pg_tblspc", de->d_name, TABLESPACE_VERSION_DIRECTORY);
+ dir2 = opendir(pathbuf);
+ if (NULL == dir2)
+ {
+ fprintf(stderr, _("%s: could not open directory \"%s\": %s\n"),
+ progname, pathbuf, strerror(errno));
+ return;
+ }
+
+ while ((de2 = readdir(dir2)) != NULL)
+ {
+ /* Skip special stuff */
+ if (strcmp(de2->d_name, ".") == 0 || strcmp(de2->d_name, "..") == 0)
+ continue;
+
+ snprintf(pathbuf2, MAXPGPATH, "%s/%s", pathbuf, de2->d_name);
+
+ FindMaxLSNinDir(pathbuf2, maxlsn);
+ }
+ closedir(dir2);
+ }
+ closedir(dir);
+
+ return;
+ }
+
+ static void
+ usage(void)
+ {
+ printf(_("%s compute the maximum LSN in PostgreSQL data pages.\n\n"), progname);
+ printf(_("Usage:\n %s [OPTION]... DATADIR\n\n"), progname);
+ printf(_("Options:\n"));
+ printf(_(" -p {file | dir} print max LSN from specified file or directory path\n"));
+ printf(_(" -P print max LSN from whole database\n"));
+ printf(_(" -?, --help show this help, then exit\n"));
+ printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
+ }
*** a/doc/src/sgml/ref/allfiles.sgml
--- b/doc/src/sgml/ref/allfiles.sgml
***************
*** 177,182 **** Complete list of usable sgml source files in this directory.
--- 177,183 ----
<!ENTITY pgDumpall SYSTEM "pg_dumpall.sgml">
<!ENTITY pgReceivexlog SYSTEM "pg_receivexlog.sgml">
<!ENTITY pgResetxlog SYSTEM "pg_resetxlog.sgml">
+ <!ENTITY pgCompuremaxlsn SYSTEM "pg_computemaxlsn.sgml">
<!ENTITY pgRestore SYSTEM "pg_restore.sgml">
<!ENTITY postgres SYSTEM "postgres-ref.sgml">
<!ENTITY postmaster SYSTEM "postmaster.sgml">
*** /dev/null
--- b/doc/src/sgml/ref/pg_computemaxlsn.sgml
***************
*** 0 ****
--- 1,74 ----
+ <!--
+ doc/src/sgml/ref/pg_computemaxlsn.sgml
+ PostgreSQL documentation
+ -->
+
+ <refentry id="APP-PGCOMPUTEMAXLSN">
+ <refmeta>
+ <refentrytitle><application>pg_computemaxlsn</application></refentrytitle>
+ <manvolnum>1</manvolnum>
+ <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>pg_computemaxlsn</refname>
+ <refpurpose>computes the maximum LSN in database of a <productname>PostgreSQL</productname> database cluster</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="app-pgcomputemaxlsn">
+ <primary>pg_computemaxlsn</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+ <cmdsynopsis>
+ <command>pg_computemaxlsn</command>
+ <arg choice="opt"><option>-P</option></arg>
+ <arg choice="opt"><option>-p</option> <replaceable class="parameter">file-name</replaceable> | <replaceable class="parameter">folder-name</replaceable></arg>
+ <arg choice="plain"><replaceable>datadir</replaceable></arg>
+ </cmdsynopsis>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-APP-PGCOMPUTEMAXLSN-1">
+ <title>Description</title>
+ <para>
+ <command>pg_computemaxlsn</command> computes maximun LSN from database pages.
+ </para>
+
+ <para>
+ This utility can only be run by the user who installed the server, because
+ it requires read/write access to the data directory.
+ For safety reasons, you must specify the data directory on the command line.
+ <command>pg_computemaxlsn</command> does not use the environment variable
+ <envar>PGDATA</>.
+ </para>
+
+ <para>
+ The <option>-P</> for computing maximum LSN from all the pages in data directory, and or for from
+ <option>-p <filename>file-name | folder-name</></> for computing maximun LSN from specific
+ file or folder (excluding sub-directories).
+ </para>
+
+ <para>
+ The <option>-V</> and <option>--version</> options print
+ the <application>pg_computemaxlsn</application> version and exit. The
+ options <option>-?</> and <option>--help</> show supported arguments,
+ and exit.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ This command must not be used when the server is
+ running. <command>pg_computemaxlsn</command> will refuse to start up if
+ it finds a server lock file in the data directory. If the
+ server crashed then a lock file might have been left
+ behind; in that case you can remove the lock file to allow
+ <command>pg_computemaxlsn</command> to run. But before you do
+ so, make doubly certain that there is no server process still alive.
+ </para>
+ </refsect1>
+
+ </refentry>
*** a/doc/src/sgml/ref/pg_resetxlog.sgml
--- b/doc/src/sgml/ref/pg_resetxlog.sgml
***************
*** 135,140 **** PostgreSQL documentation
--- 135,150 ----
largest entry in <filename>pg_xlog</>, use <literal>-l 00000001000000320000004B</> or higher.
</para>
+ <para>
+ If <command>pg_resetxlog</command> complains that it cannot determine
+ valid data for <filename>pg_control</>, and if you do not have or corrupted
+ WAL segment files in the directory <filename>pg_xlog</> under the data directory,
+ then to identify larger WAL segment file from data files we can use utility <command>pg_computemaxlsn</command>
+ with <option>-P</> option for finding maximum LSN from the data directory or
+ for from specific file or folder <option>-p <filename>file-name | folder-name</></>.
+ Once larger WAL segment file is found use <option>-l</> option for setting the value.
+ </para>
+
<note>
<para>
<command>pg_resetxlog</command> itself looks at the files in
*** a/doc/src/sgml/reference.sgml
--- b/doc/src/sgml/reference.sgml
***************
*** 248,253 ****
--- 248,254 ----
&pgControldata;
&pgCtl;
&pgResetxlog;
+ &pgCompuremaxlsn;
&postgres;
&postmaster;