'\" t .\" Title: postgres .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2024 .\" Manual: PostgreSQL 16.3 Documentation .\" Source: PostgreSQL 16.3 .\" Language: English .\" .TH "POSTGRES" "1" "2024" "PostgreSQL 16.3" "PostgreSQL 16.3 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" postgres \- PostgreSQL database server .SH "SYNOPSIS" .HP \w'\fBpostgres\fR\ 'u \fBpostgres\fR [\fIoption\fR...] .SH "DESCRIPTION" .PP \fBpostgres\fR 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 \fBpostgres\fR instance\&. The \fBpostgres\fR instance then starts a separate server process to handle the connection\&. .PP One \fBpostgres\fR 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 \(lqdata area\(rq)\&. More than one \fBpostgres\fR instance can run on a system at one time, so long as they use different data areas and different communication ports (see below)\&. When \fBpostgres\fR starts it needs to know the location of the data area\&. The location must be specified by the \fB\-D\fR option or the \fBPGDATA\fR environment variable; there is no default\&. Typically, \fB\-D\fR or \fBPGDATA\fR points directly to the data area directory created by \fBinitdb\fR(1)\&. Other possible file layouts are discussed in Section\ \&20.2\&. .PP By default \fBpostgres\fR starts in the foreground and prints log messages to the standard error stream\&. In practical applications \fBpostgres\fR should be started as a background process, perhaps at boot time\&. .PP The \fBpostgres\fR command can also be called in single\-user mode\&. The primary use for this mode is during bootstrapping by \fBinitdb\fR(1)\&. Sometimes it is used for debugging or disaster recovery; note that running 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\&. .SH "OPTIONS" .PP \fBpostgres\fR accepts the following command\-line arguments\&. For a detailed discussion of the options consult Chapter\ \&20\&. You can save typing most of these options by setting up a configuration file\&. Some (safe) options can also be set from the connecting client in an application\-dependent way to apply only for that session\&. For example, if the environment variable \fBPGOPTIONS\fR is set, then libpq\-based clients will pass that string to the server, which will interpret it as \fBpostgres\fR command\-line options\&. .SS "General Purpose" .PP \fB\-B \fR\fB\fInbuffers\fR\fR .RS 4 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\&. .RE .PP \fB\-c \fR\fB\fIname\fR\fR\fB=\fR\fB\fIvalue\fR\fR .RS 4 Sets a named run\-time parameter\&. The configuration parameters supported by PostgreSQL are described in Chapter\ \&20\&. Most of the other command line options are in fact short forms of such a parameter assignment\&. \fB\-c\fR can appear multiple times to set multiple parameters\&. .RE .PP \fB\-C \fR\fB\fIname\fR\fR .RS 4 Prints the value of the named run\-time parameter, and exits\&. (See the \fB\-c\fR option above for details\&.) This returns values from postgresql\&.conf, modified by any parameters supplied in this invocation\&. It does not reflect parameters supplied when the cluster was started\&. .sp This can be used on a running server for most parameters\&. However, the server must be shut down for some runtime\-computed parameters (e\&.g\&., shared_memory_size, shared_memory_size_in_huge_pages, and wal_segment_size)\&. .sp This option is meant for other programs that interact with a server instance, such as \fBpg_ctl\fR(1), to query configuration parameter values\&. User\-facing applications should instead use \fBSHOW\fR or the pg_settings view\&. .RE .PP \fB\-d \fR\fB\fIdebug\-level\fR\fR .RS 4 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 \fBpostgres\fR process from being propagated to this session\&. .RE .PP \fB\-D \fR\fB\fIdatadir\fR\fR .RS 4 Specifies the file system location of the database configuration files\&. See Section\ \&20.2 for details\&. .RE .PP \fB\-e\fR .RS 4 Sets the default date style to \(lqEuropean\(rq, 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\&. .RE .PP \fB\-F\fR .RS 4 Disables \fBfsync\fR calls for improved performance, at the risk of data corruption in the event of a system crash\&. Specifying this option is equivalent to disabling the fsync configuration parameter\&. Read the detailed documentation before using this! .RE .PP \fB\-h \fR\fB\fIhostname\fR\fR .RS 4 Specifies the IP host name or address on which \fBpostgres\fR 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, in which case only Unix\-domain sockets can be used to connect to the server\&. Defaults to listening only on localhost\&. Specifying this option is equivalent to setting the listen_addresses configuration parameter\&. .RE .PP \fB\-i\fR .RS 4 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 \fIlisten_addresses\fR to * in postgresql\&.conf or via \fB\-h\fR\&. .sp This option is deprecated since it does not allow access to the full functionality of listen_addresses\&. It\*(Aqs usually better to set \fIlisten_addresses\fR directly\&. .RE .PP \fB\-k \fR\fB\fIdirectory\fR\fR .RS 4 Specifies the directory of the Unix\-domain socket on which \fBpostgres\fR is to listen for connections 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 used to connect to the server\&. The default value is normally /tmp, but that can be changed at build time\&. Specifying this option is equivalent to setting the unix_socket_directories configuration parameter\&. .RE .PP \fB\-l\fR .RS 4 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\ \&19.9\&. .RE .PP \fB\-N \fR\fB\fImax\-connections\fR\fR .RS 4 Sets the maximum number of client connections that this 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\&. .RE .PP \fB\-p \fR\fB\fIport\fR\fR .RS 4 Specifies the TCP/IP port or local Unix domain socket file extension on which \fBpostgres\fR is to listen for connections from client applications\&. Defaults to the value of the \fBPGPORT\fR environment variable, or if \fBPGPORT\fR is not set, then defaults to the value established during compilation (normally 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 \fBPGPORT\fR\&. .RE .PP \fB\-s\fR .RS 4 Print time information and other statistics at the end of each command\&. This is useful for benchmarking or for use in tuning the number of buffers\&. .RE .PP \fB\-S\fR \fIwork\-mem\fR .RS 4 Specifies the base amount of memory to be used by sorts and hash tables before resorting to temporary disk files\&. See the description of the \fIwork_mem\fR configuration parameter in Section\ \&20.4.1\&. .RE .PP \fB\-V\fR .br \fB\-\-version\fR .RS 4 Print the postgres version and exit\&. .RE .PP \fB\-\-\fR\fB\fIname\fR\fR\fB=\fR\fB\fIvalue\fR\fR .RS 4 Sets a named run\-time parameter; a shorter form of \fB\-c\fR\&. .RE .PP \fB\-\-describe\-config\fR .RS 4 This option dumps out the server\*(Aqs internal configuration variables, descriptions, and defaults in tab\-delimited \fBCOPY\fR format\&. It is designed primarily for use by administration tools\&. .RE .PP \fB\-?\fR .br \fB\-\-help\fR .RS 4 Show help about postgres command line arguments, and exit\&. .RE .SS "Semi\-Internal Options" .PP The options described here are used mainly for debugging purposes, and in some cases to assist with recovery of severely damaged databases\&. There should be no reason to use them in a production database setup\&. They are listed here only for use by PostgreSQL system developers\&. Furthermore, these options might change or be removed in a future release without notice\&. .PP \fB\-f\fR { s | i | o | b | t | n | m | h } .RS 4 Forbids the use of particular scan and join methods: s and i disable sequential and index scans respectively, o, b and t disable index\-only scans, bitmap index scans, and TID scans respectively, while n, m, and h disable nested\-loop, merge and hash joins respectively\&. .sp Neither sequential scans nor nested\-loop joins can be disabled completely; the \-fs and \-fn options simply discourage the optimizer from using those plan types if it has any other alternative\&. .RE .PP \fB\-O\fR .RS 4 Allows the structure of system tables to be modified\&. This is used by \fBinitdb\fR\&. .RE .PP \fB\-P\fR .RS 4 Ignore system indexes when reading system tables, but still update the indexes when modifying the tables\&. This is useful when recovering from damaged system indexes\&. .RE .PP \fB\-t\fR pa[rser] | pl[anner] | e[xecutor] .RS 4 Print timing statistics for each query relating to each of the major system modules\&. This option cannot be used together with the \fB\-s\fR option\&. .RE .PP \fB\-T\fR .RS 4 This option is for debugging problems that cause a server process to die abnormally\&. The ordinary strategy in this situation is to notify all other server processes that they must terminate, by sending them SIGQUIT signals\&. With this option, SIGABRT will be sent instead, resulting in production of core dump files\&. .RE .PP \fB\-v\fR \fIprotocol\fR .RS 4 Specifies the version number of the frontend/backend protocol to be used for a particular session\&. This option is for internal use only\&. .RE .PP \fB\-W\fR \fIseconds\fR .RS 4 A delay of this many seconds occurs when a new server process is started, after it conducts the authentication procedure\&. This is intended to give an opportunity to attach to the server process with a debugger\&. .RE .SS "Options for Single\-User Mode" .PP The following options only apply to the single\-user mode (see Single-User Mode below)\&. .PP \fB\-\-single\fR .RS 4 Selects the single\-user mode\&. This must be the first argument on the command line\&. .RE .PP \fIdatabase\fR .RS 4 Specifies the name of the database to be accessed\&. This must be the last argument on the command line\&. If it is omitted it defaults to the user name\&. .RE .PP \fB\-E\fR .RS 4 Echo all commands to standard output before executing them\&. .RE .PP \fB\-j\fR .RS 4 Use semicolon followed by two newlines, rather than just newline, as the command entry terminator\&. .RE .PP \fB\-r\fR \fIfilename\fR .RS 4 Send all server log output to \fIfilename\fR\&. This option is only honored when supplied as a command\-line option\&. .RE .SH "ENVIRONMENT" .PP \fBPGCLIENTENCODING\fR .RS 4 Default character encoding used by clients\&. (The clients can override this individually\&.) This value can also be set in the configuration file\&. .RE .PP \fBPGDATA\fR .RS 4 Default data directory location .RE .PP \fBPGDATESTYLE\fR .RS 4 Default value of the DateStyle run\-time parameter\&. (The use of this environment variable is deprecated\&.) .RE .PP \fBPGPORT\fR .RS 4 Default port number (preferably set in the configuration file) .RE .SH "DIAGNOSTICS" .PP A failure message mentioning semget or shmget probably indicates you need to configure your kernel to provide adequate shared memory and semaphores\&. For more discussion see Section\ \&19.4\&. You might be able to postpone reconfiguring your kernel by decreasing shared_buffers to reduce the shared memory consumption of PostgreSQL, and/or by reducing max_connections to reduce the semaphore consumption\&. .PP A failure message suggesting that another server is already running should be checked carefully, for example by using the command .sp .if n \{\ .RS 4 .\} .nf $ \fBps ax | grep postgres\fR .fi .if n \{\ .RE .\} .sp or .sp .if n \{\ .RS 4 .\} .nf $ \fBps \-ef | grep postgres\fR .fi .if n \{\ .RE .\} .sp depending on your system\&. If you are certain that no conflicting server is running, you can remove the lock file mentioned in the message and try again\&. .PP A failure message indicating inability to bind to a port might indicate that that port is already in use by some non\-PostgreSQL process\&. You might also get this error if you terminate \fBpostgres\fR and immediately restart it using the same port; in this case, you must simply wait a few seconds until the operating system closes the port before trying again\&. Finally, you might get this error if you specify a port number that your operating system considers to be reserved\&. For example, many versions of Unix consider port numbers under 1024 to be \(lqtrusted\(rq and only permit the Unix superuser to access them\&. .SH "NOTES" .PP The utility command \fBpg_ctl\fR(1) can be used to start and shut down the \fBpostgres\fR server safely and comfortably\&. .PP If at all possible, \fIdo not\fR use SIGKILL to kill the main \fBpostgres\fR server\&. Doing so will prevent \fBpostgres\fR from freeing the system resources (e\&.g\&., shared memory and semaphores) that it holds before terminating\&. This might cause problems for starting a fresh \fBpostgres\fR run\&. .PP To terminate the \fBpostgres\fR server normally, the signals SIGTERM, SIGINT, or SIGQUIT can be used\&. The first will wait for all clients to terminate before quitting, the second will forcefully disconnect all clients, and the third will quit immediately without proper shutdown, resulting in a recovery run during restart\&. .PP The SIGHUP signal will reload the server configuration files\&. It is also possible to send SIGHUP to an individual server process, but that is usually not sensible\&. .PP To cancel a running query, send the SIGINT signal to the process running that command\&. To terminate a backend process cleanly, send SIGTERM to that process\&. See also \fBpg_cancel_backend\fR and \fBpg_terminate_backend\fR in Section\ \&9.27.2 for the SQL\-callable equivalents of these two actions\&. .PP The \fBpostgres\fR server uses SIGQUIT to tell subordinate server processes to terminate without normal cleanup\&. This signal \fIshould not\fR be used by users\&. It is also unwise to send SIGKILL to a server process \(em the main \fBpostgres\fR process will interpret this as a crash and will force all the sibling processes to quit as part of its standard crash\-recovery procedure\&. .SH "BUGS" .PP The \fB\-\-\fR options will not work on FreeBSD or OpenBSD\&. Use \fB\-c\fR instead\&. This is a bug in the affected operating systems; a future release of PostgreSQL will provide a workaround if this is not fixed\&. .SH "SINGLE\-USER MODE" .PP To start a single\-user mode server, use a command like .sp .if n \{\ .RS 4 .\} .nf \fBpostgres \-\-single \-D /usr/local/pgsql/data \fR\fB\fIother\-options\fR\fR\fB my_database\fR .fi .if n \{\ .RE .\} .sp Provide the correct path to the database directory with \fB\-D\fR, or make sure that the environment variable \fBPGDATA\fR is set\&. Also specify the name of the particular database you want to work in\&. .PP Normally, the single\-user mode server treats newline as the command entry terminator; there is no intelligence about semicolons, as there is in psql\&. To continue a command across multiple lines, you must type backslash just before each newline except the last one\&. The backslash and adjacent newline are both dropped from the input command\&. Note that this will happen even when within a string literal or comment\&. .PP But if you use the \fB\-j\fR command line switch, a single newline does not terminate command entry; instead, the sequence semicolon\-newline\-newline does\&. That is, type a semicolon immediately followed by a completely empty line\&. Backslash\-newline is not treated specially in this mode\&. Again, there is no intelligence about such a sequence appearing within a string literal or comment\&. .PP In either input mode, if you type a semicolon that is not just before or part of a command entry terminator, it is considered a command separator\&. When you do type a command entry terminator, the multiple statements you\*(Aqve entered will be executed as a single transaction\&. .PP To quit the session, type EOF (Control+D, usually)\&. If you\*(Aqve entered any text since the last command entry terminator, then EOF will be taken as a command entry terminator, and another EOF will be needed to exit\&. .PP Note that the single\-user mode server does not provide sophisticated line\-editing features (no command history, for example)\&. Single\-user mode also does not do any background processing, such as automatic checkpoints or replication\&. .SH "EXAMPLES" .PP To start \fBpostgres\fR in the background using default values, type: .sp .if n \{\ .RS 4 .\} .nf $ \fBnohup postgres >logfile 2>&1