doc/src/sgml/ref/pg_resetwal.sgml

   1 <!--
   2 doc/src/sgml/ref/pg_resetwal.sgml
   3 PostgreSQL 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    <group choice="opt">
  26     <arg choice="plain"><option>-f</option></arg>
  27     <arg choice="plain"><option>--force</option></arg>
  28    </group>
  29    <group choice="opt">
  30     <arg choice="plain"><option>-n</option></arg>
  31     <arg choice="plain"><option>--dry-run</option></arg>
  32    </group>
  33    <arg rep="repeat"><replaceable>option</replaceable></arg>
  34    <group choice="plain">
  35     <group choice="opt">
  36      <arg choice="plain"><option>-D</option></arg>
  37      <arg choice="plain"><option>--pgdata</option></arg>
  38     </group>
  39     <replaceable class="parameter">datadir</replaceable>
  40    </group>
  41   </cmdsynopsis>
  42  </refsynopsisdiv>
  43
  44  <refsect1 id="r1-app-pgresetwal-1">
  45   <title>Description</title>
  46   <para>
  47    <command>pg_resetwal</command> clears the write-ahead log (WAL) and
  48    optionally resets some other control information stored in the
  49    <filename>pg_control</filename> file.  This function is sometimes needed
  50    if these files have become corrupted.  It should be used only as a
  51    last resort, when the server will not start due to such corruption.
  52   </para>
  53
  54   <para>
  55    Some options, such as <option>--wal-segsize</option> (see below), can also
  56    be used to modify certain global settings of a database cluster without the
  57    need to rerun <command>initdb</command>.  This can be done safely on an
  58    otherwise sound database cluster, if none of the dangerous modes mentioned
  59    below are used.
  60   </para>
  61
  62   <para>
  63    If <command>pg_resetwal</command> is used on a data directory where the
  64    server has been cleanly shut down and the control file is sound, then it
  65    will have no effect on the contents of the database system, except that no
  66    longer used WAL files are cleared away.  Any other use is potentially
  67    dangerous and must be done with great care.  <command>pg_resetwal</command>
  68    will require the <option>-f</option> (force) option to be specified before
  69    working on a data directory in an unclean shutdown state or with a
  70    corrupted control file.
  71   </para>
  72
  73   <para>
  74    After running this command on a data directory with corrupted WAL or a
  75    corrupted control file, it should be possible to start the server,
  76    but bear in mind that the database might contain inconsistent data due to
  77    partially-committed transactions.  You should immediately dump your data,
  78    run <command>initdb</command>, and restore.  After restore, check for
  79    inconsistencies and repair as needed.
  80   </para>
  81
  82   <para>
  83    If <command>pg_resetwal</command> complains that it cannot determine
  84    valid data for <filename>pg_control</filename>, you can force it to proceed anyway
  85    by specifying the <option>-f</option> (force) option.  In this case plausible
  86    values will be substituted for the missing data.  Most of the fields can be
  87    expected to match, but manual assistance might be needed for the next OID,
  88    next transaction ID and epoch, next multitransaction ID and offset, and
  89    WAL starting location fields. These fields can be set using the options
  90    discussed below. If you are not able to determine correct values for all
  91    these fields, <option>-f</option> can still be used, but
  92    the recovered database must be treated with even more suspicion than
  93    usual: an immediate dump and restore is imperative.  <emphasis>Do not</emphasis>
  94    execute any data-modifying operations in the database before you dump,
  95    as any such action is likely to make the corruption worse.
  96   </para>
  97
  98   <para>
  99    This utility can only be run by the user who installed the server, because
 100    it requires read/write access to the data directory.
 101   </para>
 102  </refsect1>
 103
 104  <refsect1>
 105   <title>Options</title>
 106
 107   <variablelist>
 108    <varlistentry>
 109     <term><replaceable class="parameter">datadir</replaceable></term>
 110     <term><option>-D <replaceable class="parameter">datadir</replaceable></option></term>
 111     <term><option>--pgdata=<replaceable class="parameter">datadir</replaceable></option></term>
 112     <listitem>
 113      <para>
 114       Specifies the location of the database directory.
 115       For safety reasons, you must specify the data directory on the command
 116       line.  <command>pg_resetwal</command> does not use the environment
 117       variable <envar>PGDATA</envar>.
 118      </para>
 119     </listitem>
 120    </varlistentry>
 121
 122    <varlistentry>
 123     <term><option>-f</option></term>
 124     <term><option>--force</option></term>
 125     <listitem>
 126      <para>
 127       Force <command>pg_resetwal</command> to proceed even in situations where
 128       it could be dangerous, as explained above.  Specifically, this option is
 129       required to proceed if the server had not been cleanly shut down or if
 130       <command>pg_resetwal</command> cannot determine valid data for
 131       <filename>pg_control</filename>.
 132      </para>
 133     </listitem>
 134    </varlistentry>
 135
 136    <varlistentry>
 137     <term><option>-n</option></term>
 138     <term><option>--dry-run</option></term>
 139     <listitem>
 140      <para>
 141       The <option>-n</option>/<option>--dry-run</option> option instructs
 142       <command>pg_resetwal</command> to print the values reconstructed from
 143       <filename>pg_control</filename> and values about to be changed, and then exit
 144       without modifying anything. This is mainly a debugging tool, but can be
 145       useful as a sanity check before allowing <command>pg_resetwal</command>
 146       to proceed for real.
 147      </para>
 148     </listitem>
 149    </varlistentry>
 150
 151    <varlistentry>
 152     <term><option>-V</option></term>
 153     <term><option>--version</option></term>
 154     <listitem><para>Display version information, then exit.</para></listitem>
 155    </varlistentry>
 156
 157    <varlistentry>
 158     <term><option>-?</option></term>
 159     <term><option>--help</option></term>
 160     <listitem><para>Show help, then exit.</para></listitem>
 161    </varlistentry>
 162   </variablelist>
 163
 164   <para>
 165    The following options are only needed when
 166    <command>pg_resetwal</command> is unable to determine appropriate values
 167    by reading <filename>pg_control</filename>.  Safe values can be determined as
 168    described below.  For values that take numeric arguments, hexadecimal
 169    values can be specified by using the prefix <literal>0x</literal>.  Note
 170    that these instructions only apply with the standard block size of 8 kB.
 171   </para>
 172
 173   <variablelist>
 174    <varlistentry>
 175     <term><option>-c <replaceable class="parameter">xid</replaceable>,<replaceable class="parameter">xid</replaceable></option></term>
 176     <term><option>--commit-timestamp-ids=<replaceable class="parameter">xid</replaceable>,<replaceable class="parameter">xid</replaceable></option></term>
 177     <listitem>
 178      <para>
 179       Manually set the oldest and newest transaction IDs for which the commit
 180       time can be retrieved.
 181      </para>
 182
 183      <para>
 184       A safe value for the oldest transaction ID for which the commit time can
 185       be retrieved (first part) can be determined by looking
 186       for the numerically smallest file name in the directory
 187       <filename>pg_commit_ts</filename> under the data directory.  Conversely, a safe
 188       value for the newest transaction ID for which the commit time can be
 189       retrieved (second part) can be determined by looking for the numerically
 190       greatest file name in the same directory.  The file names are in
 191       hexadecimal.
 192      </para>
 193       <!-- XXX: Should there be a multiplier, similar to the other options? -->
 194     </listitem>
 195    </varlistentry>
 196
 197    <varlistentry>
 198     <term><option>-e <replaceable class="parameter">xid_epoch</replaceable></option></term>
 199     <term><option>--epoch=<replaceable class="parameter">xid_epoch</replaceable></option></term>
 200     <listitem>
 201      <para>
 202       Manually set the next transaction ID's epoch.
 203      </para>
 204
 205      <para>
 206       The transaction ID epoch is not actually stored anywhere in the database
 207       except in the field that is set by <command>pg_resetwal</command>,
 208       so any value will work so far as the database itself is concerned.
 209       You might need to adjust this value to ensure that replication
 210       systems such as <application>Slony-I</application> and
 211       <application>Skytools</application> work correctly &mdash;
 212       if so, an appropriate value should be obtainable from the state of
 213       the downstream replicated database.
 214      </para>
 215     </listitem>
 216    </varlistentry>
 217
 218    <varlistentry>
 219     <term><option>-l <replaceable class="parameter">walfile</replaceable></option></term>
 220     <term><option>--next-wal-file=<replaceable class="parameter">walfile</replaceable></option></term>
 221     <listitem>
 222      <para>
 223       Manually set the WAL starting location by specifying the name of the
 224       next WAL segment file.
 225      </para>
 226
 227      <para>
 228       The name of next WAL segment file should be
 229       larger than any WAL segment file name currently existing in
 230       the directory <filename>pg_wal</filename> under the data directory.
 231       These names are also in hexadecimal and have three parts.  The first
 232       part is the <quote>timeline ID</quote> and should usually be kept the same.
 233       For example, if <filename>00000001000000320000004A</filename> is the
 234       largest entry in <filename>pg_wal</filename>, use <literal>-l 00000001000000320000004B</literal> or higher.
 235      </para>
 236
 237      <para>
 238       Note that when using nondefault WAL segment sizes, the numbers in the WAL
 239       file names are different from the LSNs that are reported by system
 240       functions and system views.  This option takes a WAL file name, not an
 241       LSN.
 242      </para>
 243
 244      <note>
 245       <para>
 246        <command>pg_resetwal</command> itself looks at the files in
 247        <filename>pg_wal</filename> and chooses a default <option>-l</option> setting
 248        beyond the last existing file name.  Therefore, manual adjustment of
 249        <option>-l</option> should only be needed if you are aware of WAL segment
 250        files that are not currently present in <filename>pg_wal</filename>, such as
 251        entries in an offline archive; or if the contents of
 252        <filename>pg_wal</filename> have been lost entirely.
 253       </para>
 254      </note>
 255     </listitem>
 256    </varlistentry>
 257
 258    <varlistentry>
 259     <term><option>-m <replaceable class="parameter">mxid</replaceable>,<replaceable class="parameter">mxid</replaceable></option></term>
 260     <term><option>--multixact-ids=<replaceable class="parameter">mxid</replaceable>,<replaceable class="parameter">mxid</replaceable></option></term>
 261     <listitem>
 262      <para>
 263       Manually set the next and oldest multitransaction ID.
 264      </para>
 265
 266      <para>
 267       A safe value for the next multitransaction ID (first part) can be
 268       determined by looking for the numerically largest file name in the
 269       directory <filename>pg_multixact/offsets</filename> under the data directory,
 270       adding one, and then multiplying by 65536 (0x10000).  Conversely, a safe
 271       value for the oldest multitransaction ID (second part of
 272       <option>-m</option>) can be determined by looking for the numerically smallest
 273       file name in the same directory and multiplying by 65536.  The file
 274       names are in hexadecimal, so the easiest way to do this is to specify
 275       the option value in hexadecimal and append four zeroes.
 276      </para>
 277      <!-- 65536 = SLRU_PAGES_PER_SEGMENT * BLCKSZ / sizeof(MultiXactOffset) -->
 278     </listitem>
 279    </varlistentry>
 280
 281    <varlistentry>
 282     <term><option>-o <replaceable class="parameter">oid</replaceable></option></term>
 283     <term><option>--next-oid=<replaceable class="parameter">oid</replaceable></option></term>
 284     <listitem>
 285      <para>
 286       Manually set the next OID.
 287      </para>
 288
 289      <para>
 290       There is no comparably easy way to determine a next OID that's beyond
 291       the largest one in the database, but fortunately it is not critical to
 292       get the next-OID setting right.
 293      </para>
 294     </listitem>
 295    </varlistentry>
 296
 297    <varlistentry>
 298     <term><option>-O <replaceable class="parameter">mxoff</replaceable></option></term>
 299     <term><option>--multixact-offset=<replaceable class="parameter">mxoff</replaceable></option></term>
 300     <listitem>
 301      <para>
 302       Manually set the next multitransaction offset.
 303      </para>
 304
 305      <para>
 306       A safe value can be determined by looking for the numerically largest
 307       file name in the directory <filename>pg_multixact/members</filename> under the
 308       data directory, adding one, and then multiplying by 52352 (0xCC80).
 309       The file names are in hexadecimal.  There is no simple recipe such as
 310       the ones for other options of appending zeroes.
 311      </para>
 312      <!-- 52352 = SLRU_PAGES_PER_SEGMENT * floor(BLCKSZ/20) * 4; see multixact.c -->
 313     </listitem>
 314    </varlistentry>
 315
 316    <varlistentry>
 317     <term><option>--wal-segsize=<replaceable class="parameter">wal_segment_size</replaceable></option></term>
 318     <listitem>
 319      <para>
 320       Set the new WAL segment size, in megabytes.  The value must be set to a
 321       power of 2 between 1 and 1024 (megabytes).  See the same option of <xref
 322       linkend="app-initdb"/> for more information.
 323      </para>
 324
 325      <para>
 326       This option can also be used to change the WAL segment size of an
 327       existing database cluster, avoiding the need to
 328       re-<command>initdb</command>.
 329      </para>
 330
 331      <note>
 332       <para>
 333        While <command>pg_resetwal</command> will set the WAL starting address
 334        beyond the latest existing WAL segment file, some segment size changes
 335        can cause previous WAL file names to be reused.  It is recommended to
 336        use <option>-l</option> together with this option to manually set the
 337        WAL starting address if WAL file name overlap will cause problems with
 338        your archiving strategy.
 339       </para>
 340      </note>
 341     </listitem>
 342    </varlistentry>
 343
 344    <varlistentry>
 345     <term><option>-u <replaceable class="parameter">xid</replaceable></option></term>
 346     <term><option>--oldest-transaction-id=<replaceable class="parameter">xid</replaceable></option></term>
 347     <listitem>
 348      <para>
 349       Manually set the oldest unfrozen transaction ID.
 350      </para>
 351
 352      <para>
 353       A safe value can be determined by looking for the numerically smallest
 354       file name in the directory <filename>pg_xact</filename> under the data directory
 355       and then multiplying by 1048576 (0x100000).  Note that the file names are in
 356       hexadecimal.  It is usually easiest to specify the option value in
 357       hexadecimal too. For example, if <filename>0007</filename> is the smallest entry
 358       in <filename>pg_xact</filename>, <literal>-u 0x700000</literal> will work (five
 359       trailing zeroes provide the proper multiplier).
 360      </para>
 361      <!-- 1048576 = SLRU_PAGES_PER_SEGMENT * BLCKSZ * CLOG_XACTS_PER_BYTE -->
 362     </listitem>
 363    </varlistentry>
 364
 365    <varlistentry>
 366     <term><option>-x <replaceable class="parameter">xid</replaceable></option></term>
 367     <term><option>--next-transaction-id=<replaceable class="parameter">xid</replaceable></option></term>
 368     <listitem>
 369      <para>
 370       Manually set the next transaction ID.
 371      </para>
 372
 373      <para>
 374       A safe value can be determined by looking for the numerically largest
 375       file name in the directory <filename>pg_xact</filename> under the data directory,
 376       adding one,
 377       and then multiplying by 1048576 (0x100000).  Note that the file names are in
 378       hexadecimal.  It is usually easiest to specify the option value in
 379       hexadecimal too. For example, if <filename>0011</filename> is the largest entry
 380       in <filename>pg_xact</filename>, <literal>-x 0x1200000</literal> will work (five
 381       trailing zeroes provide the proper multiplier).
 382      </para>
 383      <!-- 1048576 = SLRU_PAGES_PER_SEGMENT * BLCKSZ * CLOG_XACTS_PER_BYTE -->
 384     </listitem>
 385    </varlistentry>
 386   </variablelist>
 387  </refsect1>
 388
 389  <refsect1>
 390   <title>Environment</title>
 391
 392   <variablelist>
 393    <varlistentry>
 394     <term><envar>PG_COLOR</envar></term>
 395     <listitem>
 396      <para>
 397       Specifies whether to use color in diagnostic messages. Possible values
 398       are <literal>always</literal>, <literal>auto</literal> and
 399       <literal>never</literal>.
 400      </para>
 401     </listitem>
 402    </varlistentry>
 403   </variablelist>
 404  </refsect1>
 405
 406  <refsect1>
 407   <title>Notes</title>
 408
 409   <para>
 410    This command must not be used when the server is
 411    running.  <command>pg_resetwal</command> will refuse to start up if
 412    it finds a server lock file in the data directory.  If the
 413    server crashed then a lock file might have been left
 414    behind; in that case you can remove the lock file to allow
 415    <command>pg_resetwal</command> to run.  But before you do
 416    so, make doubly certain that there is no server process still alive.
 417   </para>
 418
 419   <para>
 420    <command>pg_resetwal</command> works only with servers of the same
 421    major version.
 422   </para>
 423  </refsect1>
 424
 425  <refsect1>
 426   <title>See Also</title>
 427
 428   <simplelist type="inline">
 429    <member><xref linkend="app-pgcontroldata"/></member>
 430   </simplelist>
 431  </refsect1>
 432 </refentry>