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