>>> 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
This mail contains doc patch Option-1 and test cases.
Below are test scenarios corresponding to which testcases are in
Test_find_max_lsn_from_datafiles
Scenario-1:
Validation of the maximum LSN number & in data base directory
Steps:
1. Start the server, create table, insert some records into the table.
2. Shutdown the server in normal mode.
3. ./pg_resetxlog -P data to find the maximum LSN number and validate with the
current pg_xlog directory and in pg_control file
Expected behavior:
Displayed maximum LSN number should to same as in pg_control file &
WAL segment number displayed (fileid, segnum) should be same current file in
pg_xlog directory.
Scenario-2:
Validation of the maximum LSN number & in specific directory
Steps:
1. Start the server, create table, insert some records into the table.
2. Shutdown the server in normal mode.
3. ./pg_resetxlog -p base/1/12557/ data
Expected behavior:
Displayed maximum LSN number should to same as in pg_control file &
WAL segment number displayed (fileid, segnum) should be same current file in
pg_xlog directory.
Scenario-3:
Validation of the maximum LSN number & in specific file
Steps:
1. Start the server, create table, insert some records into the table.
2. Shutdown the server in normal mode.
3. ./pg_resetxlog -p base/1/12557/16384 data
Expected behavior:
Displayed maximum LSN number should to same as in pg_control file &
WAL segment number displayed (fileid, segnum) should be same current file in
pg_xlog directory.
With Regards,
Amit Kapila.
diff --git a/doc/src/sgml/ref/pg_resetxlog.sgml
b/doc/src/sgml/ref/pg_resetxlog.sgml
index 27b9ab41..b5c6b3c 100644
--- a/doc/src/sgml/ref/pg_resetxlog.sgml
+++ b/doc/src/sgml/ref/pg_resetxlog.sgml
@@ -30,6 +30,8 @@ PostgreSQL documentation
<arg choice="opt"><option>-m</option> <replaceable
class="parameter">mxid</replaceable></arg>
<arg choice="opt"><option>-O</option> <replaceable
class="parameter">mxoff</replaceable></arg>
<arg choice="opt"><option>-l</option> <replaceable
class="parameter">timelineid</replaceable>,<replaceable
class="parameter">fileid</replaceable>,<replaceable
class="parameter">seg</replaceable></arg>
+ <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>
@@ -78,7 +80,7 @@ PostgreSQL documentation
<para>
The <option>-o</>, <option>-x</>, <option>-e</>,
- <option>-m</>, <option>-O</>,
+ <option>-m</>, <option>-O</>, <option>-P</>, <option>-p</>,
and <option>-l</>
options allow the next OID, next transaction ID, next transaction ID's
epoch, next multitransaction ID, next multitransaction offset, and WAL
@@ -135,6 +137,16 @@ PostgreSQL documentation
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 the
<option>-P</>
+ 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
@@ -145,6 +157,11 @@ PostgreSQL documentation
entries in an offline archive; or if the contents of
<filename>pg_xlog</> have been lost entirely.
</para>
+
+ <para>
+ <option>-p <filename>file-name | folder-name</></> file-name or
floder-name
+ should be absolute path, or relative from data directory.
+ </para>
</note>
</listitem>
-- 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_resetxlog -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_resetxlog -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_resetxlog -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
