2 doc/src/sgml/ref/pg_resetxlog.sgml
3 PostgreSQL documentation
6 <refentry id="APP-PGRESETXLOG">
8 <refentrytitle><application>pg_resetxlog</application></refentrytitle>
9 <manvolnum>1</manvolnum>
10 <refmiscinfo>Application</refmiscinfo>
14 <refname>pg_resetxlog</refname>
15 <refpurpose>reset the write-ahead log and other control information of a <productname>PostgreSQL</productname> database cluster</refpurpose>
18 <indexterm zone="app-pgresetxlog">
19 <primary>pg_resetxlog</primary>
24 <command>pg_resetxlog</command>
25 <arg choice="opt"><option>-f</option></arg>
26 <arg choice="opt"><option>-n</option></arg>
27 <arg choice="opt"><option>-o</option> <replaceable class="parameter">oid</replaceable></arg>
28 <arg choice="opt"><option>-x</option> <replaceable class="parameter">xid</replaceable></arg>
29 <arg choice="opt"><option>-e</option> <replaceable class="parameter">xid_epoch</replaceable></arg>
30 <arg choice="opt"><option>-m</option> <replaceable class="parameter">mxid</replaceable></arg>
31 <arg choice="opt"><option>-O</option> <replaceable class="parameter">mxoff</replaceable></arg>
32 <arg choice="opt"><option>-l</option> <replaceable class="parameter">timelineid</replaceable>,<replaceable class="parameter">fileid</replaceable>,<replaceable class="parameter">seg</replaceable></arg>
33 <arg choice="plain"><replaceable>datadir</replaceable></arg>
37 <refsect1 id="R1-APP-PGRESETXLOG-1">
38 <title>Description</title>
40 <command>pg_resetxlog</command> clears the write-ahead log (WAL) and
41 optionally resets some other control information stored in the
42 <filename>pg_control</> file. This function is sometimes needed
43 if these files have become corrupted. It should be used only as a
44 last resort, when the server will not start due to such corruption.
48 After running this command, it should be possible to start the server,
49 but bear in mind that the database might contain inconsistent data due to
50 partially-committed transactions. You should immediately dump your data,
51 run <command>initdb</>, and reload. After reload, check for
52 inconsistencies and repair as needed.
56 This utility can only be run by the user who installed the server, because
57 it requires read/write access to the data directory.
58 For safety reasons, you must specify the data directory on the command line.
59 <command>pg_resetxlog</command> does not use the environment variable
64 If <command>pg_resetxlog</command> complains that it cannot determine
65 valid data for <filename>pg_control</>, you can force it to proceed anyway
66 by specifying the <literal>-f</> (force) switch. In this case plausible
67 values will be substituted for the missing data. Most of the fields can be
68 expected to match, but manual assistance might be needed for the next OID,
69 next transaction ID and epoch, next multitransaction ID and offset, and
70 WAL starting address fields. These fields can be set using the switches
71 discussed below. If you are not able to determine correct values for all
72 these fields, <literal>-f</> can still be used, but
73 the recovered database must be treated with even more suspicion than
74 usual: an immediate dump and reload is imperative. <emphasis>Do not</>
75 execute any data-modifying operations in the database before you dump,
76 as any such action is likely to make the corruption worse.
80 The <literal>-o</>, <literal>-x</>, <literal>-e</>,
81 <literal>-m</>, <literal>-O</>,
83 switches allow the next OID, next transaction ID, next transaction ID's
84 epoch, next multitransaction ID, next multitransaction offset, and WAL
85 starting address values to be set manually. These are only needed when
86 <command>pg_resetxlog</command> is unable to determine appropriate values
87 by reading <filename>pg_control</>. Safe values can be determined as
93 A safe value for the next transaction ID (<literal>-x</>)
94 can be determined by looking for the numerically largest
95 file name in the directory <filename>pg_clog</> under the data directory,
97 and then multiplying by 1048576. Note that the file names are in
98 hexadecimal. It is usually easiest to specify the switch value in
99 hexadecimal too. For example, if <filename>0011</> is the largest entry
100 in <filename>pg_clog</>, <literal>-x 0x1200000</> will work (five
101 trailing zeroes provide the proper multiplier).
107 A safe value for the next multitransaction ID (<literal>-m</>)
108 can be determined by looking for the numerically largest
109 file name in the directory <filename>pg_multixact/offsets</> under the
110 data directory, adding one, and then multiplying by 65536. As above,
111 the file names are in hexadecimal, so the easiest way to do this is to
112 specify the switch value in hexadecimal and add four zeroes.
118 A safe value for the next multitransaction offset (<literal>-O</>)
119 can be determined by looking for the numerically largest
120 file name in the directory <filename>pg_multixact/members</> under the
121 data directory, adding one, and then multiplying by 65536. As above,
122 the file names are in hexadecimal, so the easiest way to do this is to
123 specify the switch value in hexadecimal and add four zeroes.
129 The WAL starting address (<literal>-l</>) should be
130 larger than any WAL segment file name currently existing in
131 the directory <filename>pg_xlog</> under the data directory.
132 These names are also in hexadecimal and have three parts. The first
133 part is the <quote>timeline ID</> and should usually be kept the same.
134 Do not choose a value larger than 255 (<literal>0xFF</>) for the third
135 part; instead increment the second part and reset the third part to 0.
136 For example, if <filename>00000001000000320000004A</> is the
137 largest entry in <filename>pg_xlog</>, <literal>-l 0x1,0x32,0x4B</> will
138 work; but if the largest entry is
139 <filename>000000010000003A000000FF</>, choose <literal>-l 0x1,0x3B,0x0</>
145 <command>pg_resetxlog</command> itself looks at the files in
146 <filename>pg_xlog</> and chooses a default <literal>-l</> setting
147 beyond the last existing file name. Therefore, manual adjustment of
148 <literal>-l</> should only be needed if you are aware of WAL segment
149 files that are not currently present in <filename>pg_xlog</>, such as
150 entries in an offline archive; or if the contents of
151 <filename>pg_xlog</> have been lost entirely.
158 There is no comparably easy way to determine a next OID that's beyond
159 the largest one in the database, but fortunately it is not critical to
160 get the next-OID setting right.
166 The transaction ID epoch is not actually stored anywhere in the database
167 except in the field that is set by <command>pg_resetxlog</command>,
168 so any value will work so far as the database itself is concerned.
169 You might need to adjust this value to ensure that replication
170 systems such as <application>Slony-I</> work correctly —
171 if so, an appropriate value should be obtainable from the state of
172 the downstream replicated database.
179 The <literal>-n</> (no operation) switch instructs
180 <command>pg_resetxlog</command> to print the values reconstructed from
181 <filename>pg_control</> and then exit without modifying anything.
182 This is mainly a debugging tool, but can be useful as a sanity check
183 before allowing <command>pg_resetxlog</command> to proceed for real.
187 The <literal>-V</> and <literal>--version</> options print
188 the <application>pg_resetxlog</application> version and exit. The
189 options <literal>-?</> and <literal>--help</> show supported arguments,
199 This command must not be used when the server is
200 running. <command>pg_resetxlog</command> will refuse to start up if
201 it finds a server lock file in the data directory. If the
202 server crashed then a lock file might have been left
203 behind; in that case you can remove the lock file to allow
204 <command>pg_resetxlog</command> to run. But before you do
205 so, make doubly certain that there is no server process still alive.