Man Pages

pg_resetxlog(1) - phpMan pg_resetxlog(1) - phpMan

Command: man perldoc info search(apropos)  


PG_RESETXLOG(1)         PostgreSQL Server Applications         PG_RESETXLOG(1)



NAME
       pg_resetxlog - reset the write-ahead log and other control information of a PostgreSQL database cluster

SYNOPSIS
       pg_resetxlog  [  -f  ]   [  -n ]  [ -ooid  ]  [ -x xid  ]  [ -e xid_epoch  ]  [ -m mxid  ]  [ -O mxoff  ]  [ -l
       timelineid,fileid,seg  ]  datadir

DESCRIPTION
       pg_resetxlog clears the write-ahead log (WAL) and optionally resets some other control  information  stored  in
       the  pg_control file. This function is sometimes needed if these files have become corrupted. It should be used
       only as a last resort, when the server will not start due to such corruption.

       After running this command, it should be possible to start the server, but bear in mind that the database might
       contain  inconsistent  data due to partially-committed transactions. You should immediately dump your data, run
       initdb, and reload. After reload, check for inconsistencies and repair as needed.

       This utility can only be run by the user who installed the server, because it requires read/write access to the
       data  directory.   For  safety  reasons, you must specify the data directory on the command line.  pg_resetxlog
       does not use the environment variable PGDATA.

       If pg_resetxlog complains that it cannot determine valid data for pg_control, you can force it to proceed  any-
       way  by  specifying  the  -f  (force) switch. In this case plausible values will be substituted for the missing
       data. Most of the fields can be expected to match, but manual assistance might be needed for the next OID, next
       transaction  ID  and  epoch, next multitransaction ID and offset, and WAL starting address fields. These fields
       can be set using the switches discussed below. If you are not able to determine correct values  for  all  these
       fields,  -f  can still be used, but the recovered database must be treated with even more suspicion than usual:
       an immediate dump and reload is imperative. Do not execute any data-modifying operations in the database before
       you dump, as any such action is likely to make the corruption worse.

       The  -o,  -x, -e, -m, -O, and -l switches allow the next OID, next transaction ID, next transaction ID's epoch,
       next multitransaction ID, next multitransaction offset, and WAL starting address values  to  be  set  manually.
       These  are  only needed when pg_resetxlog is unable to determine appropriate values by reading pg_control. Safe
       values can be determined as follows:

       ? A safe value for the next transaction ID (-x) can be determined by looking for the numerically  largest  file
         name  in  the  directory  pg_clog under the data directory, adding one, and then multiplying by 1048576. Note
         that the file names are in hexadecimal. It is usually easiest to specify the switch value in hexadecimal too.
         For  example,  if  0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes provide
         the proper multiplier).

       ? A safe value for the next multitransaction ID (-m) can be determined by looking for the  numerically  largest
         file name in the directory pg_multixact/offsets under the data directory, adding one, and then multiplying by
         65536. As above, the file names are in hexadecimal, so the easiest way to do this is to  specify  the  switch
         value in hexadecimal and add four zeroes.

       ? A  safe  value  for  the  next  multitransaction offset (-O) can be determined by looking for the numerically
         largest file name in the directory pg_multixact/members under the data directory, adding one, and then multi-
         plying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the
         switch value in hexadecimal and add four zeroes.

       ? The WAL starting address (-l) should be larger than any WAL segment  file  name  currently  existing  in  the
         directory  pg_xlog  under  the data directory.  These names are also in hexadecimal and have three parts. The
         first part is the ''timeline ID'' and should usually be kept the same.  Do not choose a value larger than 255
         (0xFF)  for the third part; instead increment the second part and reset the third part to 0.  For example, if
         00000001000000320000004A is the largest entry in pg_xlog, -l 0x1,0x32,0x4B will  work;  but  if  the  largest
         entry is 000000010000003A000000FF, choose -l 0x1,0x3B,0x0 or more.

         Note:  pg_resetxlog  itself  looks  at  the files in pg_xlog and chooses a default -l setting beyond the last
         existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL  segment
         files that are not currently present in pg_xlog, such as entries in an offline archive; or if the contents of
         pg_xlog have been lost entirely.


       ? There is no comparably easy way to determine a next OID that's beyond the largest one in  the  database,  but
         fortunately it is not critical to get the next-OID setting right.

       ? The  transaction  ID epoch is not actually stored anywhere in the database except in the field that is set by
         pg_resetxlog, so any value will work so far as the database itself is concerned.  You might  need  to  adjust
         this  value  to  ensure that replication systems such as Slony-I work correctly -- if so, an appropriate value
         should be obtainable from the state of the downstream replicated database.


       The -n (no operation) switch instructs pg_resetxlog to print the values reconstructed from pg_control and  then
       exit  without  modifying anything.  This is mainly a debugging tool, but can be useful as a sanity check before
       allowing pg_resetxlog to proceed for real.

NOTES
       This command must not be used when the server is running. pg_resetxlog will refuse to start up if  it  finds  a
       server  lock file in the data directory. If the server crashed then a lock file might have been left behind; in
       that case you can remove the lock file to allow pg_resetxlog to run. But before you do so, make doubly  certain
       that there is no server process still alive.



Application                       2014-02-17                   PG_RESETXLOG(1)