doc/src/sgml/ref/pg_resetxlog.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="APP-PGRESETXLOG">
   7  <refmeta>
   8   <refentrytitle id="APP-PGRESETXLOG-TITLE"><application>pg_resetxlog</application></refentrytitle>
   9   <manvolnum>1</manvolnum>
  10   <refmiscinfo>Application</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>pg_resetxlog</refname>
  15   <refpurpose>reset the write-ahead log and other control information of a <productname>PostgreSQL</productname> database cluster</refpurpose>
  16  </refnamediv>
  17
  18  <refsynopsisdiv>
  19   <cmdsynopsis>
  20    <command>pg_resetxlog</command>
  21    <arg>-f</arg>
  22    <arg>-n</arg>
  23    <arg>-o<replaceable class="parameter">oid</replaceable> </arg>
  24    <arg>-x <replaceable class="parameter">xid</replaceable> </arg>
  25    <arg>-e <replaceable class="parameter">xid_epoch</replaceable> </arg>
  26    <arg>-m <replaceable class="parameter">mxid</replaceable> </arg>
  27    <arg>-O <replaceable class="parameter">mxoff</replaceable> </arg>
  28    <arg>-l <replaceable class="parameter">timelineid</replaceable>,<replaceable class="parameter">fileid</replaceable>,<replaceable class="parameter">seg</replaceable> </arg>
  29    <arg choice="plain"><replaceable>datadir</replaceable></arg>
  30   </cmdsynopsis>
  31  </refsynopsisdiv>
  32
  33  <refsect1 id="R1-APP-PGRESETXLOG-1">
  34   <title>Description</title>
  35   <para>
  36    <command>pg_resetxlog</command> clears the write-ahead log (WAL) and
  37    optionally resets some other control information stored in the
  38    <filename>pg_control</> file.  This function is sometimes needed
  39    if these files have become corrupted.  It should be used only as a
  40    last resort, when the server will not start due to such corruption.
  41   </para>
  42
  43   <para>
  44    After running this command, it should be possible to start the server,
  45    but bear in mind that the database might contain inconsistent data due to
  46    partially-committed transactions.  You should immediately dump your data,
  47    run <command>initdb</>, and reload.  After reload, check for
  48    inconsistencies and repair as needed.
  49   </para>
  50
  51   <para>
  52    This utility can only be run by the user who installed the server, because
  53    it requires read/write access to the data directory.
  54    For safety reasons, you must specify the data directory on the command line.
  55    <command>pg_resetxlog</command> does not use the environment variable
  56    <envar>PGDATA</>.
  57   </para>
  58
  59   <para>
  60    If <command>pg_resetxlog</command> complains that it cannot determine
  61    valid data for <filename>pg_control</>, you can force it to proceed anyway
  62    by specifying the <literal>-f</> (force) switch.  In this case plausible
  63    values will be substituted for the missing data.  Most of the fields can be
  64    expected to match, but manual assistance might be needed for the next OID,
  65    next transaction ID and epoch, next multitransaction ID and offset, and
  66    WAL starting address fields. These fields can be set using the switches
  67    discussed below. If you are not able to determine correct values for all
  68    these fields, <literal>-f</> can still be used, but
  69    the recovered database must be treated with even more suspicion than
  70    usual: an immediate dump and reload is imperative.  <emphasis>Do not</>
  71    execute any data-modifying operations in the database before you dump,
  72    as any such action is likely to make the corruption worse.
  73   </para>
  74
  75   <para>
  76    The <literal>-o</>, <literal>-x</>, <literal>-e</>,
  77    <literal>-m</>, <literal>-O</>,
  78    and <literal>-l</>
  79    switches allow the next OID, next transaction ID, next transaction ID's
  80    epoch, next multitransaction ID, next multitransaction offset, and WAL
  81    starting address values to be set manually.  These are only needed when
  82    <command>pg_resetxlog</command> is unable to determine appropriate values
  83    by reading <filename>pg_control</>.  Safe values can be determined as
  84    follows:
  85
  86    <itemizedlist>
  87     <listitem>
  88      <para>
  89       A safe value for the next transaction ID (<literal>-x</>)
  90       can be determined by looking for the numerically largest
  91       file name in the directory <filename>pg_clog</> under the data directory,
  92       adding one,
  93       and then multiplying by 1048576.  Note that the file names are in
  94       hexadecimal.  It is usually easiest to specify the switch value in
  95       hexadecimal too. For example, if <filename>0011</> is the largest entry
  96       in <filename>pg_clog</>, <literal>-x 0x1200000</> will work (five
  97       trailing zeroes provide the proper multiplier).
  98      </para>
  99     </listitem>
 100
 101     <listitem>
 102      <para>
 103       A safe value for the next multitransaction ID (<literal>-m</>)
 104       can be determined by looking for the numerically largest
 105       file name in the directory <filename>pg_multixact/offsets</> under the
 106       data directory, adding one, and then multiplying by 65536.  As above,
 107       the file names are in hexadecimal, so the easiest way to do this is to
 108       specify the switch value in hexadecimal and add four zeroes.
 109      </para>
 110     </listitem>
 111
 112     <listitem>
 113      <para>
 114       A safe value for the next multitransaction offset (<literal>-O</>)
 115       can be determined by looking for the numerically largest
 116       file name in the directory <filename>pg_multixact/members</> under the
 117       data directory, adding one, and then multiplying by 65536.  As above,
 118       the file names are in hexadecimal, so the easiest way to do this is to
 119       specify the switch value in hexadecimal and add four zeroes.
 120      </para>
 121     </listitem>
 122
 123     <listitem>
 124      <para>
 125       The WAL starting address (<literal>-l</>) should be
 126       larger than any WAL segment file name currently existing in
 127       the directory <filename>pg_xlog</> under the data directory.
 128       These names are also in hexadecimal and have three parts.  The first
 129       part is the <quote>timeline ID</> and should usually be kept the same.
 130       Do not choose a value larger than 255 (<literal>0xFF</>) for the third
 131       part; instead increment the second part and reset the third part to 0.
 132       For example, if <filename>00000001000000320000004A</> is the
 133       largest entry in <filename>pg_xlog</>, <literal>-l 0x1,0x32,0x4B</> will
 134       work; but if the largest entry is
 135       <filename>000000010000003A000000FF</>, choose <literal>-l 0x1,0x3B,0x0</>
 136       or more.
 137      </para>
 138
 139      <note>
 140       <para>
 141        <command>pg_resetxlog</command> itself looks at the files in
 142        <filename>pg_xlog</> and chooses a default <literal>-l</> setting
 143        beyond the last existing file name.  Therefore, manual adjustment of
 144        <literal>-l</> should only be needed if you are aware of WAL segment
 145        files that are not currently present in <filename>pg_xlog</>, such as
 146        entries in an offline archive; or if the contents of
 147        <filename>pg_xlog</> have been lost entirely.
 148       </para>
 149      </note>
 150     </listitem>
 151
 152     <listitem>
 153      <para>
 154       There is no comparably easy way to determine a next OID that's beyond
 155       the largest one in the database, but fortunately it is not critical to
 156       get the next-OID setting right.
 157      </para>
 158     </listitem>
 159
 160     <listitem>
 161      <para>
 162       The transaction ID epoch is not actually stored anywhere in the database
 163       except in the field that is set by <command>pg_resetxlog</command>,
 164       so any value will work so far as the database itself is concerned.
 165       You might need to adjust this value to ensure that replication
 166       systems such as <application>Slony-I</> work correctly &mdash;
 167       if so, an appropriate value should be obtainable from the state of
 168       the downstream replicated database.
 169      </para>
 170     </listitem>
 171    </itemizedlist>
 172   </para>
 173
 174   <para>
 175    The <literal>-n</> (no operation) switch instructs
 176    <command>pg_resetxlog</command> to print the values reconstructed from
 177    <filename>pg_control</> and then exit without modifying anything.
 178    This is mainly a debugging tool, but can be useful as a sanity check
 179    before allowing <command>pg_resetxlog</command> to proceed for real.
 180   </para>
 181  </refsect1>
 182
 183  <refsect1>
 184   <title>Notes</title>
 185
 186   <para>
 187    This command must not be used when the server is
 188    running.  <command>pg_resetxlog</command> will refuse to start up if
 189    it finds a server lock file in the data directory.  If the
 190    server crashed then a lock file might have been left
 191    behind; in that case you can remove the lock file to allow
 192    <command>pg_resetxlog</command> to run.  But before you do
 193    so, make doubly certain that there is no server process still alive.
 194   </para>
 195  </refsect1>
 196
 197 </refentry>