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  ]  [  -o oid  ]  [  -x xid  ]  [  -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 may 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 anyway 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 man-
       ual assistance may be needed for the next  OID,  next  transaction  ID,
       next multitransaction ID and offset, WAL starting address, and database
       locale fields.  The first five of these can be set using  the  switches
       discussed  below.  pg_resetxlog's own environment is the source for its
       guess at the locale fields; take care that LANG and so forth match  the
       environment  that  initdb was run in.  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, -m, -O, and -l switches allow the next OID,  next  transac-
       tion  ID,  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 may be determined as follows:

       o A safe value for the next transaction ID (-x) may  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).

       o A safe value for the next multitransaction ID (-m) may 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 hexadec-
         imal and add four zeroes.

       o A  safe value for the next multitransaction offset (-O) may be deter-
         mined by looking for the numerically largest file name in the  direc-
         tory  pg_multixact/members  under the data directory, adding one, and
         then multiplying by 65536. As above, the file names are in  hexadeci-
         mal,  so the easiest way to do this is to specify the switch value in
         hexadecimal and add four zeroes.

       o The WAL starting address (-l) should be larger  than  any  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 exam-
         ple, 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.

       o 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 -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 may 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 may 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
       postmaster nor any backend server process still alive.

Application                       2005-11-05                   PG_RESETXLOG(1)