Ways to repair PostgreSQL control files using Pg_resetxlog

The PostgreSQL control file is named Pg_control under the $pgdata/global directory.

The following three sections of information are recorded in the control file:

1. Static information generated when INITDB:

Pg_control version number:922
Catalog version number:201204301
Database system identifier:5831753892046499175
Maximum Data Alignment:8
Database Block size:8192
Blocks per segment of large relation:131072
WAL Block size:16384
Bytes per WAL segment:16777216
Maximum Length of identifiers:64
Maximum columns in a index:32
Maximum size of a TOAST chunk:1996
Date/time type storage:64-bit integers
FLOAT4 argument Passing:by value
Float8 argument Passing:by value
2. Configuration information in postgresql.conf:
Current Wal_level Setting:hot_standby
Current Max_connections setting:1000
Current Max_prepared_xacts Setting:10
Current Max_locks_per_xact setting:64
3. Dynamic information of Write-ahead logging and checkpoint:
Latest Checkpoint location:96e8/5b000020
Prior Checkpoint Location:96e8/5a0c8cc0
Latest checkpoint ' s REDO location:96e8/5b000020
Latest checkpoint ' s timelineid:1
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/1183664222
Latest checkpoint ' s nextoid:309701
Latest checkpoint ' s nextmultixactid:1
Latest checkpoint ' s nextmultioffset:0
Latest checkpoint ' s oldestxid:1006759584
Latest checkpoint ' s Oldestxid ' s db:1
Latest checkpoint ' s oldestactivexid:0
Time of latest Checkpoint:fri 2013 07:44:19 AM CST
Minimum Recovery Ending location:0/0
Backup Start location:0/0
Backup End location:0/0
End-of-backup Record Required:no
The above information can be obtained from Pg_control using Pg_controldata:

Src/bin/pg_controldata/pg_controldata.c
* Pg_controldata
*
* Reads the data from $PGDATA/global/pg_control
If the control file $pgdata/global/pg_control corrupted or missing, the database will run an exception and cannot start.

How to fix it? The key is to restore Write-ahead logging and checkpoint dynamic information.

This information can be parsed from Pg_xlog, Pg_clog, pg_multixact files in these directories.

Pg_xlog file name resolution can be see, different segment size, naming a big difference, Pg_resetxlog Help file for 16MB segment size, if the other size, you need to recalculate the name:
http://blog.163.com/digoal@126/blog/static/1638770402012914112949546/

The next step is to introduce a way to reconstruct Pg_control using Pg_resetxlog.
The Pg_resetxlog function is as follows:

Src/bin/pg_resetxlog/pg_resetxlog.c
* PG_RESETXLOG.C
* A utility to "zero out" of the xlog when it ' s corrupt beyond recovery.
* Can also rebuild Pg_control if needed.
*
* The theory of operation is fairly simple:
* 1. Read the existing Pg_control (which would include the last
* Checkpoint record). If It is a old format then update to
* Current format.
* 2. If Pg_control is corrupt, attempt to intuit reasonable values,
* By scanning the old xlog if necessary.
* 3. Modify Pg_control to reflect a "shutdown" state with a checkpoint
* Record at the start of Xlog.
* 4. Flush the existing Xlog files and write a new segment with
* Just a checkpoint record in it. The new segment is positioned
* Just past the end of the ' The Old Xlog ', so ' existing LSNs in
* data pages would appear to is "in the past".
* This are all pretty straightforward except for the intuition part of
* Step 2 ...
Usage of Pg_resetxlog:

Ocz@db-172-16-3-150-> Pg_resetxlog--help
Pg_resetxlog resets the PostgreSQL transaction log.

Usage:
Pg_resetxlog [OPTION] ... DataDir

Options:
-e Xidepoch Set next transaction ID Epoch
-F Force update to IS done
-L TLI,FILE,SEG Force minimum WAL starting location for new transaction log
-M XID Set next multitransaction ID
-N no update, just show extracted control values (for testing)
-O OID Set next OID
-O offset set next multitransaction OFFSET
-V,--version output version information, then exit
-X XID Set Next transaction ID
-?,--help then Exit
Parameter specific meaning:

-L TIMELINEID,FILEID,SEG
The WAL starting address (-L) should is larger than any WAL segment file name currently existing in the
Directory Pg_xlog under the data directory. These are names are also in hexadecimal and have three. The
The "Timeline ID" and should usually is kept the same. Do not choose a value larger than 255
(0xFF) for the third part; Instead increment the second part and reset the third part to 0. For example, if
00000001000000320000004A is the largest entry in Pg_xlog,-l 0x1,0x32,0x4b would work; But if the largest
Entry is 000000010000003a000000ff, Choose-l 0x1,0x3b,0x0 or more.
Note
Pg_resetxlog itself looks at the files in Pg_xlog and chooses a default-l setting beyond the last
Existing file name. Therefore, manual adjustment of-l should only to needed if you are aware of WAL
Segment files that are not currently present in Pg_xlog, such as entries, offline; or if
The contents of Pg_xlog have been lost entirely.

-E Xidepoch
The transaction ID epoch is isn't actually stored anywhere in the database except in the field this is set by
Pg_resetxlog, so any value would work so far as the database itself is concerned. might need to adjust
This value to ensure is replication systems such as slony-i work correctly-if so, a appropriate value
Should is obtainable from the state of the downstream replicated database.

-X xid   
  A safe value for the next transaction ID (-X) can determined by looking for the Numerically largest file   
name in the directory Pg_clog under the data directory, adding one, and t Hen multiplying by 1048576. note   
That's file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal   
too. For example, if 0011 are the largest entry in Pg_clog,-X 0x1200000 would work (five trailing zeroes   
Provide the proper multiplier).    

-M xid   
  A safe value for the next multitransaction ID (-m) can is determined by looking fo R the numerically largest   
file name in the directory pg_multixact/offsets under the data directory, Adding one, and then multiplying   
by 65536. As above, the file names are in hexadecimal, and so the easiest way to does this are to specify the   
option value in hexadecimal and add four zeroes.   

-O OFFSET
A safe value for the next multitransaction offset (-O) can is determined by looking for the numerically
Largest file name in the directory pg_multixact/members under the data directory, adding one, and then
Multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to does this are to
Specify the option value in hexadecimal and add four zeroes.

-O OID
There is no comparably easy way to determine a next OID that's beyond the largest one in the database, but
Fortunately it isn't critical to get the next-oid setting right.

-N
No update, just show extracted control values (for testing)
-F
Force
The test steps are as follows (based on PostgreSQL 9.2.1):
1. Create a new test data and use a table with OIDs because the OID cannot be determined to see if there is an exception.
2. Close the database
3. Note The Pg_controldata information, easy to repair after the comparison
4. Delete $pgdata/global/pg_control
5. Open the database to observe the error output
6. Touch $PGDATA/global/pg_control
7. Use Pg_resetxlog to repair Pg_control
8. Write down the Pg_controldata information and compare it with the previous Pg_controldata output
9. Start the database
10. See if the test data is normal, new insert data
11. Close the database and write down the pg_controldata information to see what's changed.

Test process:
1. Test data

Digoal=> CREATE TABLE oid_test (ID int primary key) with OIDs;
Notice:create table/primary KEY'll CREATE implicit index "Oid_test_pkey" for TABLE "Oid_test"
CREATE TABLE
digoal=> INSERT INTO Oid_test select Generate_series (1,100000);
INSERT 0 100000
digoal=> Select min (OID), Max (OID) from Oid_test;
Min | Max
-------+--------
16397 | 116396
(1 row)
2. Close the data

Ocz@db-172-16-3-150-> pg_ctl stop-m Fast
Waiting for server to shut down ... done
Server stopped
3. Note The Pg_controldata information, easy to repair after the comparison

Ocz@db-172-16-3-150-> Pg_controldata
Pg_control version number:922
Catalog version number:201204301
Database system identifier:5832000131111550393
Database Cluster State:shut down
Pg_control last Modified:fri 2013 09:48:18 AM CST
Latest Checkpoint location:96e8/5f000020
Prior Checkpoint location:96e8/5ee5c698
Latest checkpoint ' s REDO location:96e8/5f000020
Latest checkpoint ' s timelineid:1
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/1183842312
Latest checkpoint ' s nextoid:116414
Latest checkpoint ' s nextmultixactid:65536
Latest checkpoint ' s nextmultioffset:65536
Latest checkpoint ' s oldestxid:1006759584
Latest checkpoint ' s Oldestxid ' s db:1
Latest checkpoint ' s oldestactivexid:0
Time of latest Checkpoint:fri 2013 09:48:18 AM CST
Minimum Recovery Ending location:0/0
Backup Start location:0/0
Backup End location:0/0
End-of-backup Record Required:no
Current Wal_level Setting:hot_standby
Current Max_connections setting:1000
Current Max_prepared_xacts Setting:10
Current Max_locks_per_xact setting:64
Maximum Data Alignment:8
Database Block size:8192
Blocks per segment of large relation:131072
WAL Block size:16384
Bytes per WAL segment:16777216
Maximum Length of identifiers:64
Maximum columns in a index:32
Maximum size of a TOAST chunk:1996
Date/time type storage:64-bit integers
FLOAT4 argument Passing:by value
Float8 argument Passing:by value
4. Delete $pgdata/global/pg_control

ocz@db-172-16-3-150-> RM $PGDATA/global/pg_control
Rm:remove regular file '/data05/ocz/pg_root/global/pg_control '? Y
5. Open the database to observe the error output

Ocz@db-172-16-3-150-> Pg_ctl Start
Server starting
Ocz@db-172-16-3-150-> postgres:could not find the database system
Expected to find it in the directory "/data05/ocz/pg_root",
But could not open file "/data05/ocz/pg_root/global/pg_control": No such file or directory
Next to FIX:

6. Touch $PGDATA/global/pg_control

Ocz@db-172-16-3-150-> Touch $PGDATA/global/pg_control
ocz@db-172-16-3-150-> chmod $PGDATA/global/pg_control
7. Use Pg_resetxlog to repair Pg_control

First determine the information for-L TIMELINEID,FILEID,SEG:

ocz@db-172-16-3-150-> CD $PGDATA/pg_xlog
Ocz@db-172-16-3-150-> LL
Total 65M
-RW-------1 OCZ OCZ 16M 09:39 00000001000096e80000005c
-RW-------1 OCZ OCZ 16M 09:39 00000001000096e80000005d
-RW-------1 OCZ OCZ 16M 09:48 00000001000096e80000005e
-RW-------1 OCZ OCZ 16M 09:48 00000001000096e80000005f
DRWX------2 OCZ OCZ 44K 09:48 archive_status
The-l TIMELINEID,FILEID,SEG data comes from three parts of the Pg_xlog file name, and occupies 8 16 decimal places, respectively.

The segment size is 16MB, so the end maximum is 0xFF.

Draw-L 0x1,0x96e8,0x60

Next, determine the information for-X XID

From Pg_clog

ocz@db-172-16-3-150-> CD $PGDATA/pg_clog
Ocz@db-172-16-3-150-> Ll-t|head-n 5
Total 43M
-RW-------1 OCZ OCZ 8.0K 11 09:48 0469
-RW-------1 OCZ OCZ 216K 10 21:00 0468
-RW-------1 OCZ OCZ 256K 10 12:56 0467
-RW-------1 OCZ OCZ 256K 10 09:35 0466
Take the maximum value plus 1 and multiply by 1048576.

Converting to 16 is equivalent to taking the maximum plus 1 and adding 5 0 to the end.

Get-X 0x046a00000

Next determine the information for-M XID

From Pg_multixact/offsets

ocz@db-172-16-3-150-> CD $PGDATA/pg_multixact/offsets
Ocz@db-172-16-3-150-> LL
Total 0
Take the maximum value plus 1 and multiply by 65536.

Converting to 16 is equivalent to taking the maximum plus 1 and adding 4 0 to the end.

Use 0 plus 1 if you don't have a file, then add 4 0 at the end

Get-M 0x10000

Next determine the information for-O offset

From Pg_multixact/members

ocz@db-172-16-3-150-> CD $PGDATA/pg_multixact/members
Ocz@db-172-16-3-150-> LL
Total 0
Take the maximum value plus 1 and multiply by 65536.

Converting to 16 is equivalent to taking the maximum plus 1 and adding 4 0 to the end.

Use 0 plus 1 if you don't have a file, then add 4 0 at the end

Get-O 0x10000

Finally, there are 2 indeterminate values:

-e Xidepoch, if there is a trigger-based data synchronization software using Slony or Londiste, then-e is of little significance, which is actually a conversion factor used when converting 32-bit XID to 64-bit XID.
The-O OID, the system automatically skips the allocated OID, automatically fault tolerance, such as when the OID is used by another program, and PG automatically generates the next OID and continues to judge availability. Know to be available.
You can ignore these two values first.

The implementation pg_resetxlog is as follows:

ocz@db-172-16-3-150-> pg_resetxlog-l 0x1,0x96e8,0x60-x 0x046a00000-m 0x10000-o 0x10000-f $PGDATA
Pg_resetxlog:pg_control exists but is broken or unknown version; Ignoring it
Transaction Log Reset
8. Write down the Pg_controldata information and compare it with the previous Pg_controldata output

Ocz@db-172-16-3-150-> Pg_controldata
Pg_control version number:922
Catalog version number:201204301
Database system identifier:5832008033851373032
Database Cluster State:shut down
Pg_control last Modified:fri 2013 10:09:44 AM CST
Latest Checkpoint location:96e8/60000020
Prior Checkpoint location:0/0
Latest checkpoint ' s REDO location:96e8/60000020
Latest checkpoint ' s timelineid:1
Latest checkpoint ' s Full_page_writes:off
Latest checkpoint ' s nextxid:0/1184890880
Latest checkpoint ' s nextoid:10000
Latest checkpoint ' s nextmultixactid:65536
Latest checkpoint ' s nextmultioffset:65536
Latest checkpoint ' s oldestxid:3479858176
Latest checkpoint ' s Oldestxid ' s db:0
Latest checkpoint ' s oldestactivexid:0
Time of latest Checkpoint:fri 2013 10:09:44 AM CST
Minimum Recovery Ending location:0/0
Backup Start location:0/0
Backup End location:0/0
End-of-backup Record Required:no
Current Wal_level Setting:minimal
Current Max_connections setting:100
Current Max_prepared_xacts setting:0
Current Max_locks_per_xact setting:64
Maximum Data Alignment:8
Database Block size:8192
Blocks per segment of large relation:131072
WAL Block size:16384
Bytes per WAL segment:16777216
Maximum Length of identifiers:64
Maximum columns in a index:32
Maximum size of a TOAST chunk:1996
Date/time type storage:64-bit integers
FLOAT4 argument Passing:by value
Float8 argument Passing:by value
Note the indeterminate-e Xidepoch and-O-OID information read from the control file after the repair is as follows:

Which is the initial value after Initdb.

Latest checkpoint ' s nextxid:0/1184890880:xidepoch=0
Latest checkpoint ' s nextoid:10000
The values that have changed before the repair of Pg_control are as follows:
Before fixing

Database system identifier:5832000131111550393
Pg_control last Modified:fri 2013 09:48:18 AM CST
Prior Checkpoint location:96e8/5ee5c698
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/1183842312
Latest checkpoint ' s nextoid:116414
Latest checkpoint ' s oldestxid:1006759584
Latest checkpoint ' s Oldestxid ' s db:1
Time of latest Checkpoint:fri 2013 09:48:18 AM CST
Current Wal_level Setting:hot_standby
Current Max_connections setting:1000
Current Max_prepared_xacts Setting:10
After repair

Database system identifier:5832008033851373032
Pg_control last Modified:fri 2013 10:09:44 AM CST
Prior Checkpoint location:0/0
Latest checkpoint ' s Full_page_writes:off
Latest checkpoint ' s nextxid:0/1184890880
Latest checkpoint ' s nextoid:10000
Latest checkpoint ' s oldestxid:3479858176
Latest checkpoint ' s Oldestxid ' s db:0
Time of latest Checkpoint:fri 2013 10:09:44 AM CST
Current Wal_level Setting:minimal
Current Max_connections setting:100
Current Max_prepared_xacts setting:0
9. Start the database

Ocz@db-172-16-3-150-> Pg_ctl Start
Server starting
ocz@db-172-16-3-150-> log:00000:loaded Library "Pg_stat_statements"
Location:load_libraries, miscinit.c:1249
10. See if the test data is normal, and then insert the new data

Ocz@db-172-16-3-150-> Psql digoal Digoal
Psql (9.2.1)
Type ' help ' for help.
digoal=> Select min (OID), Max (OID), COUNT (*) from oid_test;
Min | Max | Count
-------+--------+--------
16397 | 116396 | 100000
(1 row)
Data can be accessed normally.

New Insert Data:

digoal=> INSERT INTO Oid_test select Generate_series (100001,200000);
INSERT 0 100000
digoal=> Select min (OID), Max (OID), COUNT (*) from oid_test;
Min | Max | Count
-------+--------+--------
16384 | 116396 | 200000
(1 row)
Digoal=> Select oid,* from Oid_test where oid=16397;
OID | Id
-------+--------
16397 | 1
16397 | 100014
(2 rows)
Note that the OID has been duplicated, confirming the description in PostgreSQL that the OID does not ensure uniqueness.

11. Close the database and write down the pg_controldata information to see what's changed.

Ocz@db-172-16-3-150-> pg_ctl stop-m Fast
Waiting for server to shut down ... done
Server stopped
Ocz@db-172-16-3-150-> Pg_controldata
Pg_control version number:922
Catalog version number:201204301
Database system identifier:5832008033851373032
Database Cluster State:shut down
Pg_control last Modified:fri 2013 10:16:18 AM CST
Latest Checkpoint location:96e8/61000020
Prior Checkpoint location:96e8/60dff470
Latest checkpoint ' s REDO location:96e8/61000020
Latest checkpoint ' s timelineid:1
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/1184890883
Latest checkpoint ' s nextoid:116385
Latest checkpoint ' s nextmultixactid:65536
Latest checkpoint ' s nextmultioffset:65536
Latest checkpoint ' s oldestxid:1006759584
Latest checkpoint ' s Oldestxid ' s db:1
Latest checkpoint ' s oldestactivexid:0
Time of latest Checkpoint:fri 2013 10:16:18 AM CST
Minimum Recovery Ending location:0/0
Backup Start location:0/0
Backup End location:0/0
End-of-backup Record Required:no
Current Wal_level Setting:hot_standby
Current Max_connections setting:1000
Current Max_prepared_xacts Setting:10
Current Max_locks_per_xact setting:64
Maximum Data Alignment:8
Database Block size:8192
Blocks per segment of large relation:131072
WAL Block size:16384
Bytes per WAL segment:16777216
Maximum Length of identifiers:64
Maximum columns in a index:32
Maximum size of a TOAST chunk:1996
Date/time type storage:64-bit integers
FLOAT4 argument Passing:by value
Float8 argument Passing:by value
When you close the database and the control file that you just repaired, the information changes as follows:
Before opening the library:

Pg_control last Modified:fri 2013 10:09:44 AM CST
Latest Checkpoint location:96e8/60000020
Prior Checkpoint location:0/0
Latest checkpoint ' s REDO location:96e8/60000020
Latest checkpoint ' s Full_page_writes:off
Latest checkpoint ' s nextxid:0/1184890880
Latest checkpoint ' s nextoid:10000
Latest checkpoint ' s oldestxid:3479858176
Latest checkpoint ' s Oldestxid ' s db:0
Time of latest Checkpoint:fri 2013 10:09:44 AM CST
Current Wal_level Setting:minimal
Current Max_connections setting:100
Current Max_prepared_xacts setting:0
After Guanqu:

Pg_control last Modified:fri 2013 10:16:18 AM CST
Latest Checkpoint location:96e8/61000020
Prior Checkpoint location:96e8/60dff470
Latest checkpoint ' s REDO location:96e8/61000020
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/1184890883
Latest checkpoint ' s nextoid:116385
Latest checkpoint ' s oldestxid:1006759584
Latest checkpoint ' s Oldestxid ' s db:1
Time of latest Checkpoint:fri 2013 10:16:18 AM CST
Current Wal_level Setting:hot_standby
Current Max_connections setting:1000
Current Max_prepared_xacts Setting:10
Summary
1. After using Pg_resetxlog, first check data consistency, export data when necessary, use INITDB to create a new database, and then import.

2. If the control file is lost and there is no backup, pg_resetxlog you do not know what to fill in, but you can get the Pg_xlog directory redo location, or pg_resetxlog will guess some values, directly-f to generate control files, After you start the database, you may be able to use the txid_current () function to consistently consume XID to get consistent values because the data "disappears" as XID returns to the previous XID.