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 — 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