1<!--
2doc/src/sgml/ref/pg_resetwal.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="APP-PGRESETWAL">
7 <indexterm zone="app-pgresetwal">
8  <primary>pg_resetwal</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle><application>pg_resetwal</application></refentrytitle>
13  <manvolnum>1</manvolnum>
14  <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>pg_resetwal</refname>
19  <refpurpose>reset the write-ahead log and other control information of a <productname>PostgreSQL</productname> database cluster</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23  <cmdsynopsis>
24   <command>pg_resetwal</command>
25   <arg choice="opt"><option>-f</option></arg>
26   <arg choice="opt"><option>-n</option></arg>
27   <arg rep="repeat"><replaceable>option</replaceable></arg>
28   <arg choice="req"><arg choice="opt"><option>-D</option></arg> <replaceable class="parameter">datadir</replaceable></arg>
29  </cmdsynopsis>
30 </refsynopsisdiv>
31
32 <refsect1 id="R1-APP-PGRESETWAL-1">
33  <title>Description</title>
34  <para>
35   <command>pg_resetwal</command> clears the write-ahead log (WAL) and
36   optionally resets some other control information stored in the
37   <filename>pg_control</> file.  This function is sometimes needed
38   if these files have become corrupted.  It should be used only as a
39   last resort, when the server will not start due to such corruption.
40  </para>
41
42  <para>
43   After running this command, it should be possible to start the server,
44   but bear in mind that the database might contain inconsistent data due to
45   partially-committed transactions.  You should immediately dump your data,
46   run <command>initdb</>, and reload.  After reload, check for
47   inconsistencies and repair as needed.
48  </para>
49
50  <para>
51   This utility can only be run by the user who installed the server, because
52   it requires read/write access to the data directory.
53   For safety reasons, you must specify the data directory on the command line.
54   <command>pg_resetwal</command> does not use the environment variable
55   <envar>PGDATA</>.
56  </para>
57
58  <para>
59   If <command>pg_resetwal</command> complains that it cannot determine
60   valid data for <filename>pg_control</>, you can force it to proceed anyway
61   by specifying the <option>-f</> (force) option.  In this case plausible
62   values will be substituted for the missing data.  Most of the fields can be
63   expected to match, but manual assistance might be needed for the next OID,
64   next transaction ID and epoch, next multitransaction ID and offset, and
65   WAL starting address fields. These fields can be set using the options
66   discussed below. If you are not able to determine correct values for all
67   these fields, <option>-f</> can still be used, but
68   the recovered database must be treated with even more suspicion than
69   usual: an immediate dump and reload is imperative.  <emphasis>Do not</>
70   execute any data-modifying operations in the database before you dump,
71   as any such action is likely to make the corruption worse.
72  </para>
73 </refsect1>
74
75 <refsect1>
76  <title>Options</title>
77
78  <variablelist>
79   <varlistentry>
80    <term><option>-f</option></term>
81    <listitem>
82     <para>
83      Force <command>pg_resetwal</command> to proceed even if it cannot determine
84      valid data for <filename>pg_control</>, as explained above.
85     </para>
86    </listitem>
87   </varlistentry>
88
89   <varlistentry>
90    <term><option>-n</option></term>
91    <listitem>
92     <para>
93      The <option>-n</> (no operation) option instructs
94      <command>pg_resetwal</command> to print the values reconstructed from
95      <filename>pg_control</> and values about to be changed, and then exit
96      without modifying anything. This is mainly a debugging tool, but can be
97      useful as a sanity check before allowing <command>pg_resetwal</command>
98      to proceed for real.
99     </para>
100    </listitem>
101   </varlistentry>
102
103   <varlistentry>
104    <term><option>-V</option></term>
105    <term><option>--version</option></term>
106    <listitem><para>Display version information, then exit.</para></listitem>
107   </varlistentry>
108
109   <varlistentry>
110    <term><option>-?</option></term>
111    <term><option>--help</option></term>
112    <listitem><para>Show help, then exit.</para></listitem>
113   </varlistentry>
114  </variablelist>
115
116  <para>
117   The following options are only needed when
118   <command>pg_resetwal</command> is unable to determine appropriate values
119   by reading <filename>pg_control</>.  Safe values can be determined as
120   described below.  For values that take numeric arguments, hexadecimal
121   values can be specified by using the prefix <literal>0x</literal>.
122  </para>
123
124  <variablelist>
125   <varlistentry>
126    <term><option>-c</option> <replaceable class="parameter">xid</replaceable>,<replaceable class="parameter">xid</replaceable></term>
127    <listitem>
128     <para>
129      Manually set the oldest and newest transaction IDs for which the commit
130      time can be retrieved.
131     </para>
132
133     <para>
134      A safe value for the oldest transaction ID for which the commit time can
135      be retrieved (first part) can be determined by looking
136      for the numerically smallest file name in the directory
137      <filename>pg_commit_ts</> under the data directory.  Conversely, a safe
138      value for the newest transaction ID for which the commit time can be
139      retrieved (second part) can be determined by looking for the numerically
140      greatest file name in the same directory.  The file names are in
141      hexadecimal.
142     </para>
143    </listitem>
144   </varlistentry>
145
146   <varlistentry>
147    <term><option>-e</option> <replaceable class="parameter">xid_epoch</replaceable></term>
148    <listitem>
149     <para>
150      Manually set the next transaction ID's epoch.
151     </para>
152
153     <para>
154      The transaction ID epoch is not actually stored anywhere in the database
155      except in the field that is set by <command>pg_resetwal</command>,
156      so any value will work so far as the database itself is concerned.
157      You might need to adjust this value to ensure that replication
158      systems such as <application>Slony-I</> and
159      <application>Skytools</> work correctly &mdash;
160      if so, an appropriate value should be obtainable from the state of
161      the downstream replicated database.
162     </para>
163    </listitem>
164   </varlistentry>
165
166   <varlistentry>
167    <term><option>-l</option> <replaceable class="parameter">walfile</replaceable></term>
168    <listitem>
169     <para>
170      Manually set the WAL starting address.
171     </para>
172
173     <para>
174      The WAL starting address should be
175      larger than any WAL segment file name currently existing in
176      the directory <filename>pg_wal</> under the data directory.
177      These names are also in hexadecimal and have three parts.  The first
178      part is the <quote>timeline ID</> and should usually be kept the same.
179      For example, if <filename>00000001000000320000004A</> is the
180      largest entry in <filename>pg_wal</>, use <literal>-l 00000001000000320000004B</> or higher.
181     </para>
182
183     <note>
184      <para>
185       <command>pg_resetwal</command> itself looks at the files in
186       <filename>pg_wal</> and chooses a default <option>-l</> setting
187       beyond the last existing file name.  Therefore, manual adjustment of
188       <option>-l</> should only be needed if you are aware of WAL segment
189       files that are not currently present in <filename>pg_wal</>, such as
190       entries in an offline archive; or if the contents of
191       <filename>pg_wal</> have been lost entirely.
192      </para>
193     </note>
194    </listitem>
195   </varlistentry>
196
197   <varlistentry>
198    <term><option>-m</option> <replaceable class="parameter">mxid</replaceable>,<replaceable class="parameter">mxid</replaceable></term>
199    <listitem>
200     <para>
201      Manually set the next and oldest multitransaction ID.
202     </para>
203
204     <para>
205      A safe value for the next multitransaction ID (first part) can be
206      determined by looking for the numerically largest file name in the
207      directory <filename>pg_multixact/offsets</> under the data directory,
208      adding one, and then multiplying by 65536 (0x10000).  Conversely, a safe
209      value for the oldest multitransaction ID (second part of
210      <option>-m</>) can be determined by looking for the numerically smallest
211      file name in the same directory and multiplying by 65536.  The file
212      names are in hexadecimal, so the easiest way to do this is to specify
213      the option value in hexadecimal and append four zeroes.
214     </para>
215    </listitem>
216   </varlistentry>
217
218   <varlistentry>
219    <term><option>-o</option> <replaceable class="parameter">oid</replaceable></term>
220    <listitem>
221     <para>
222      Manually set the next OID.
223     </para>
224
225     <para>
226      There is no comparably easy way to determine a next OID that's beyond
227      the largest one in the database, but fortunately it is not critical to
228      get the next-OID setting right.
229     </para>
230    </listitem>
231   </varlistentry>
232
233   <varlistentry>
234    <term><option>-O</option> <replaceable class="parameter">mxoff</replaceable></term>
235    <listitem>
236     <para>
237      Manually set the next multitransaction offset.
238     </para>
239
240     <para>
241      A safe value can be determined by looking for the numerically largest
242      file name in the directory <filename>pg_multixact/members</> under the
243      data directory, adding one, and then multiplying by 52352 (0xCC80).
244      The file names are in hexadecimal.  There is no simple recipe such as
245      the ones for other options of appending zeroes.
246     </para>
247    </listitem>
248   </varlistentry>
249
250   <varlistentry>
251    <term><option>-u <replaceable class="parameter">xid</replaceable></option></term>
252    <listitem>
253     <para>
254      Manually set the oldest unfrozen transaction ID.
255     </para>
256
257     <para>
258      A safe value can be determined by looking for the numerically smallest
259      file name in the directory <filename>pg_xact</filename> under the data directory
260      and then multiplying by 1048576 (0x100000).  Note that the file names are in
261      hexadecimal.  It is usually easiest to specify the option value in
262      hexadecimal too. For example, if <filename>0007</filename> is the smallest entry
263      in <filename>pg_xact</filename>, <literal>-u 0x700000</literal> will work (five
264      trailing zeroes provide the proper multiplier).
265     </para>
266    </listitem>
267   </varlistentry>
268
269   <varlistentry>
270    <term><option>-x</option> <replaceable class="parameter">xid</replaceable></term>
271    <listitem>
272     <para>
273      Manually set the next transaction ID.
274     </para>
275
276     <para>
277      A safe value can be determined by looking for the numerically largest
278      file name in the directory <filename>pg_xact</> under the data directory,
279      adding one,
280      and then multiplying by 1048576 (0x100000).  Note that the file names are in
281      hexadecimal.  It is usually easiest to specify the option value in
282      hexadecimal too. For example, if <filename>0011</> is the largest entry
283      in <filename>pg_xact</>, <literal>-x 0x1200000</> will work (five
284      trailing zeroes provide the proper multiplier).
285     </para>
286    </listitem>
287   </varlistentry>
288  </variablelist>
289 </refsect1>
290
291 <refsect1>
292  <title>Notes</title>
293
294  <para>
295   This command must not be used when the server is
296   running.  <command>pg_resetwal</command> will refuse to start up if
297   it finds a server lock file in the data directory.  If the
298   server crashed then a lock file might have been left
299   behind; in that case you can remove the lock file to allow
300   <command>pg_resetwal</command> to run.  But before you do
301   so, make doubly certain that there is no server process still alive.
302  </para>
303
304  <para>
305   <command>pg_resetwal</command> works only with servers of the same
306   major version.
307  </para>
308 </refsect1>
309
310 <refsect1>
311  <title>See Also</title>
312
313  <simplelist type="inline">
314   <member><xref linkend="app-pgcontroldata"></member>
315  </simplelist>
316 </refsect1>
317</refentry>
318