pg_resetxlog — reset the write-ahead log and other control information of a PostgreSQL
clears the write-ahead log (WAL) and optionally resets some other control informa-
tion stored in the
ﬁle. This function is sometimes needed if these ﬁles have become
corrupted. It shouldbe usedonly as a last resort, when the server willnotstartdue to suchcorruption.
After runningthis command, it should be possibletostartthe server, butbear in mind thatthedatabase
might contain inconsistent data due to partially-committed transactions. You should immediately
dumpyour data, run
,and reload. After reload, check for inconsistencies andrepair 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.
does not use the environment variable
complains that it cannot determine valid data for
,you can force it to
proceed anyway by specifying the
(force) option. In this case plausible values will be substituted
for the missing data. Most of the ﬁelds 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 ﬁelds. These ﬁelds can be set using the options discussed below. If you are not
able to determine correct values for all these ﬁelds,
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.
options allow the next OID, next transaction ID, next transaction
ID’s epoch, next and oldest multitransaction ID, next multitransaction offset, and WAL starting ad-
dress values to be set manually. These are only needed when
is unable to determine
appropriate values by reading
.Safe values can be determined as follows:
Asafe value for the next transaction ID (
)can be determined by looking for the numerically
largest ﬁle name in the directory
under the data directory, adding one, and then multi-
plying by 1048576. Note that the ﬁle names are in hexadecimal. It is usually easiest to specify
the option value in hexadecimal too. For example, if
is the largest entry in
will work (ﬁve trailing zeroes provide the proper multiplier).
Asafe value for the next multitransaction ID (ﬁrst part of
)can be determined by looking for the
numerically largest ﬁle name in the directory
under the data directory,
adding one, and thenmultiplyingby 65536. Conversely, asafe value for the oldest multitransaction
ID (second part of
)can be determined by looking for the numerically smallest ﬁle name in
the same directory and multiplying by 65536. As above, the ﬁle names are in hexadecimal, so the
easiest way to do this is to specify the option value in hexadecimal and append four zeroes.
Asafe value for the next multitransaction offset (
)can be determined by looking for the numer-
icallylargest ﬁle name in the directory
under the data directory, adding
one, and then multiplying by 52352. As above, the ﬁle names are in hexadecimal. There is no
simple recipe such as the ones above of appending zeroes.
The WAL starting address (
) should be larger than any WAL segment ﬁle name currently
existing in the directory
under the data directory. These names are also in hexadecimal
and have three parts. The ﬁrst part is the “timeline ID” and should usually be kept the same.
For example, if
is the largest entry in
itself looksat theﬁles in
the last existing ﬁle name. Therefore, manual adjustment of
should only be needed if you
are aware of WAL segment ﬁles that are not currently present in
,such as entries in
an ofﬂine archive; or if the contents of
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 ﬁeld that is
,soanyvalue 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.
(no operation) option instructs
to print the values reconstructed from
and values about to be changed, and then exit without modifying anything. This is
mainly a debugging tool, but can be useful as a sanity check before allowing
proceed for real.
options print the pg_resetxlog version and exit. The options
showsupported arguments, and exit.
This command must not be used when the server is running.
will refuse to start up if
it ﬁnds a server lock ﬁle in the data directory. If the server crashed then a lock ﬁle might have been
left behind; in that case you can remove the lock ﬁle to allow
to run. But before you
do so, make doubly certain that there is no server process still alive.
postgres — PostgreSQL database server
is the PostgreSQL database server. In order for a client application to access a database it
connects (over a network or locally) to a running
starts a separate server process to handle the connection.
instance always manages the data of exactly one database cluster. A database cluster
is a collection of databases that is stored at a common ﬁle system location (the “data area”). More
instance can run on a system at one time, so long as they use different data areas
and different communication ports (see below). When
starts it needs to know the location
of the data area. The location mustbe speciﬁedby the
option or the
there is no default. Typically,
points directlyto the data area directorycreated by initdb.
Other possible ﬁle layouts are discussed in Section 18.2.
starts in the foreground and prints logmessages to the standarderror stream. In
should be started as a background process, perhaps at boot time.
command can also be called in single-user mode. The primary use for this mode is
during bootstrapping byinitdb. Sometimes itis usedfor debuggingor disaster recovery;note that run-
ning a single-user server is not truly suitable for debugging the server, since no realistic interprocess
communication and locking will happen. When invoked in single-user mode from the shell, the user
can enter queries and the results will be printed to the screen, but in a form that is more useful for
developers than end users. In the single-user mode, the session user will be set to the user with ID 1,
and implicit superuser powers are granted to this user. This user does not actually have to exist, so
the single-user mode can be used to manually recover from certain kinds of accidental damage to the
accepts the following command-line arguments. For a detailed discussion of the options
consult Chapter 18. Youcan save typing mostof theseoptions by setting upa conﬁguration ﬁle. Some
(safe) options canalsobe setfrom theconnectingclient in anapplication-dependent waytoapplyonly
for that session. For example, if the environment variable
is set, then libpq-based clients
will pass that string to the server, which will interpret it as
Enables run-time assertion checks, which is a debugging aid to detect programming mistakes.
This option is only available if assertions were enabled when PostgreSQL was compiled. If so,
the default is on.
Sets the number of shared buffers for use by the server processes. The default value of this
parameter is chosen automatically by initdb. Specifying this option is equivalent to setting the
shared_buffers conﬁguration parameter.
Sets a named run-time parameter. The conﬁguration parameters supported by PostgreSQL are
described in Chapter 18. Most of the other command line options are in fact short forms of such
can appear multiple times to set multiple parameters.
Prints the value of the namedrun-timeparameter, andexits. (Seethe
This can be used on a running server, and returns values from
any parameters supplied in this invocation. It does not reﬂect parameters supplied when the
cluster was started.
This option is meant for other programs that interact with a server instance, such as pg_ctl, to
queryconﬁgurationparameter values. User-facing applications should instead use SHOW or the
Sets the debug level. The higher this value is set, the more debugging output is written to the
server log. Values are from 1 to 5. It is also possible to pass
for a speciﬁc session, which
will prevent the server log level of the parent
process from being propagated to this
Speciﬁes the ﬁle system locationof the database conﬁgurationﬁles. See Section 18.2 for details.
Sets the default date style to “European”, that is
ordering of input date ﬁelds. This also
causes the day to be printed before the month in certain date output formats. See Section 8.5 for
calls for improved performance, at the risk of data corruption in the event of a
system crash. Specifying this option is equivalentto disabling the fsync conﬁgurationparameter.
Read the detailed documentation before using this!
Speciﬁes the IP host name or address on which
is to listen for TCP/IP connections
from client applications. The value can also be a comma-separated list of addresses, or
specify listening on all available interfaces. An empty value speciﬁes not listening on any IP
addresses, inwhich case only Unix-domainsockets canbe used toconnecttothe server. Defaults
to listening onlyon localhost. Specifying this option is equivalent to setting the listen_addresses
Allows remote clients to connect via TCP/IP (Internet domain) connections. Without this option,
only local connections are accepted. This option is equivalent to setting
This option is deprecated since it does not allow access to the full functionality of
listen_addresses. It’s usually better to set
Speciﬁes the directory of the Unix-domain socket on which
is to listen for connec-
tions from client applications. The value can also be a comma-separated list of directories. An
empty value speciﬁes not listening on any Unix-domain sockets, in which case only TCP/IP
sockets can be usedto connect to the server. The default value is normally
,but that can be
changedat buildtime. Specifying this option is equivalent to setting the unix_socket_directories
Enables secure connections using SSL. PostgreSQL must have been compiled with support for
SSL for this option to be available. For more information on using SSL, refer to Section 17.9.
Sets themaximum number of client connections thatthis server will accept. The default value of
this parameter is chosen automatically by initdb. Specifying this option is equivalent to setting
the max_connections conﬁguration parameter.
The command-line-style options speciﬁed in
are passed to all server processes
started by this
process. If the option string contains any spaces, the entire string must
The use of this optionis obsolete; all command-line options for server processes canbe speciﬁed
Speciﬁes the TCP/IP port or local Unix domain socket ﬁle extension on which
listen for connections from client applications. Defaults tothe value of the
variable, or if
is not set, then defaults to the value established during compilation (nor-
mally 5432). If you specify a port other than the default port, then all client applications must
specify the same port using either command-line options or
Print time information and other statistics at the end of each command. This is useful for bench-
marking or for use in tuning the number of buffers.
Speciﬁes the amount of memory to be used by internal sorts and hashes before resorting to
temporary disk ﬁles. See the description of the
conﬁguration parameter in Section
Print the postgres version and exit.
Documents you may be interested
Documents you may be interested