110
pg_resetxlog
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
][
-e xid_epoch
][
-m mxid
,
mxid
][
-O mxoff
][
-l
xlogfile
]
datadir
Description
pg_resetxlog
clears the write-ahead log (WAL) and optionally resets some other control informa-
tion stored in the
pg_control
file. This function is sometimes needed if these files 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
initdb
,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.
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) option. 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 options 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
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
pg_resetxlog
is unable to determine
appropriate values by reading
pg_control
.Safe values can be determined as follows:
•
Asafe 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 multi-
plying by 1048576. Note that the file names are in hexadecimal. It is usually easiest to specify
the option 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).
•
Asafe value for the next multitransaction ID (first part of
-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 thenmultiplyingby 65536. Conversely, asafe value for the oldest multitransaction
ID (second part of
-m
)can be determined by looking for the numerically smallest file name in
1753
84
pg_resetxlog
the same directory and multiplying by 65536. As above, the file 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 (
-O
)can be determined by looking for the numer-
icallylargest file name in the directory
pg_multixact/members
under the data directory, adding
one, and then multiplying by 52352. As above, the file names are in hexadecimal. There is no
simple recipe such as the ones above of appending 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.
For example, if
00000001000000320000004A
is the largest entry in
pg_xlog
, use
-l
00000001000000320000004B
or higher.
Note:
pg_resetxlog
itself looksat thefiles in
pg_xlog
andchooses adefault
-l
settingbeyond
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
,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.
The
-n
(no operation) option instructs
pg_resetxlog
to print the values reconstructed from
pg_control
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
pg_resetxlog
to
proceed for real.
The
-V
and
--version
options print the pg_resetxlog version and exit. The options
-?
and
--help
showsupported arguments, and exit.
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.
1754
67
postgres
Name
postgres — PostgreSQL database server
Synopsis
postgres
[
option
...]
Description
postgres
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
postgres
instance. The
postgres
instance then
starts a separate server process to handle the connection.
One
postgres
instance always manages the data of exactly one database cluster. A database cluster
is a collection of databases that is stored at a common file system location (the “data area”). More
than one
postgres
instance can run on a system at one time, so long as they use different data areas
and different communication ports (see below). When
postgres
starts it needs to know the location
of the data area. The location mustbe specifiedby the
-D
option or the
PGDATA
environment variable;
there is no default. Typically,
-D
or
PGDATA
points directlyto the data area directorycreated by initdb.
Other possible file layouts are discussed in Section 18.2.
By default
postgres
starts in the foreground and prints logmessages to the standarderror stream. In
practical applications
postgres
should be started as a background process, perhaps at boot time.
The
postgres
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
system catalogs.
Options
postgres
accepts the following command-line arguments. For a detailed discussion of the options
consult Chapter 18. Youcan save typing mostof theseoptions by setting upa configuration file. Some
(safe) options canalsobe setfrom theconnectingclient in anapplication-dependent waytoapplyonly
for that session. For example, if the environment variable
PGOPTIONS
is set, then libpq-based clients
will pass that string to the server, which will interpret it as
postgres
command-line options.
1755
72
postgres
General Purpose
-A 0|1
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.
-B
nbuffers
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 configuration parameter.
-c
name
=
value
Sets a named run-time parameter. The configuration parameters supported by PostgreSQL are
described in Chapter 18. Most of the other command line options are in fact short forms of such
aparameter assignment.
-c
can appear multiple times to set multiple parameters.
-C
name
Prints the value of the namedrun-timeparameter, andexits. (Seethe
-c
optionabovefor details.)
This can be used on a running server, and returns values from
postgresql.conf
,modified by
any parameters supplied in this invocation. It does not reflect 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
queryconfigurationparameter values. User-facing applications should instead use SHOW or the
pg_settings
view.
-d
debug-level
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
-d 0
for a specific session, which
will prevent the server log level of the parent
postgres
process from being propagated to this
session.
-D
datadir
Specifies the file system locationof the database configurationfiles. See Section 18.2 for details.
-e
Sets the default date style to “European”, that is
DMY
ordering of input date fields. This also
causes the day to be printed before the month in certain date output formats. See Section 8.5 for
more information.
-F
Disables
fsync
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 configurationparameter.
Read the detailed documentation before using this!
-h
hostname
Specifies the IP host name or address on which
postgres
is to listen for TCP/IP connections
from client applications. The value can also be a comma-separated list of addresses, or
*
to
specify listening on all available interfaces. An empty value specifies 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
configuration parameter.
1756
77
postgres
-i
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
listen_addresses
to
*
in
postgresql.conf
or via
-h
.
This option is deprecated since it does not allow access to the full functionality of
listen_addresses. It’s usually better to set
listen_addresses
directly.
-k
directory
Specifies the directory of the Unix-domain socket on which
postgres
is to listen for connec-
tions from client applications. The value can also be a comma-separated list of directories. An
empty value specifies 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
/tmp
,but that can be
changedat buildtime. Specifying this option is equivalent to setting the unix_socket_directories
configuration parameter.
-l
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.
-N
max-connections
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 configuration parameter.
-o
extra-options
The command-line-style options specified in
extra-options
are passed to all server processes
started by this
postgres
process. If the option string contains any spaces, the entire string must
be quoted.
The use of this optionis obsolete; all command-line options for server processes canbe specified
directly onthe
postgres
command line.
-p
port
Specifies the TCP/IP port or local Unix domain socket file extension on which
postgres
is to
listen for connections from client applications. Defaults tothe value of the
PGPORT
environment
variable, or if
PGPORT
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
PGPORT
.
-s
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.
-S work-mem
Specifies the amount of memory to be used by internal sorts and hashes before resorting to
temporary disk files. See the description of the
work_mem
configuration parameter in Section
18.4.1.
-V
--version
Print the postgres version and exit.
1757
Documents you may be interested
Documents you may be interested
