2
doc/src/sgml/ref/postgres-ref.sgml
3
PostgreSQL documentation
6
<refentry id="app-postgres">
8
<refentrytitle><application>postgres</application></refentrytitle>
9
<manvolnum>1</manvolnum>
10
<refmiscinfo>Application</refmiscinfo>
14
<refname>postgres</refname>
15
<refpurpose><productname>PostgreSQL</productname> database server</refpurpose>
18
<indexterm zone="app-postgres">
19
<primary>postgres</primary>
24
<command>postgres</command>
25
<arg rep="repeat"><replaceable>option</></arg>
30
<title>Description</title>
33
<command>postgres</command> is the
34
<productname>PostgreSQL</productname> database server. In order
35
for a client application to access a database it connects (over a
36
network or locally) to a running <command>postgres</command> instance.
37
The <command>postgres</command> instance then starts a separate server
38
process to handle the connection.
42
One <command>postgres</command> instance always manages the data of
43
exactly one database cluster. A database cluster is a collection
44
of databases that is stored at a common file system location (the
45
<quote>data area</quote>). More than one
46
<command>postgres</command> instance can run on a system at one
47
time, so long as they use different data areas and different
48
communication ports (see below). When
49
<command>postgres</command> starts it needs to know the location
50
of the data area. The location must be specified by the
51
<option>-D</option> option or the <envar>PGDATA</envar> environment
52
variable; there is no default. Typically, <option>-D</option> or
53
<envar>PGDATA</envar> points directly to the data area directory
54
created by <xref linkend="app-initdb">. Other possible file layouts are
55
discussed in <xref linkend="runtime-config-file-locations">.
59
By default <command>postgres</command> starts in the
60
foreground and prints log messages to the standard error stream. In
61
practical applications <command>postgres</command>
62
should be started as a background process, perhaps at boot time.
66
The <command>postgres</command> command can also be called in
67
single-user mode. The primary use for this mode is during
68
bootstrapping by <xref linkend="app-initdb">. Sometimes it is used
69
for debugging or disaster recovery; note that running a single-user
70
server is not truly suitable for debugging the server, since no
71
realistic interprocess communication and locking will happen.
72
When invoked in single-user
73
mode from the shell, the user can enter queries and the results
74
will be printed to the screen, but in a form that is more useful
75
for developers than end users. In the single-user mode,
76
the session user will be set to the user with ID 1, and implicit
77
superuser powers are granted to this user.
78
This user does not actually have to exist, so the single-user mode
79
can be used to manually recover from certain
80
kinds of accidental damage to the system catalogs.
84
<refsect1 id="app-postgres-options">
85
<title>Options</title>
88
<command>postgres</command> accepts the following command-line
89
arguments. For a detailed discussion of the options consult <xref
90
linkend="runtime-config">. You can save typing most of these
91
options by setting up a configuration file. Some (safe) options
92
can also be set from the connecting client in an
93
application-dependent way to apply only for that session. For
94
example, if the environment variable <envar>PGOPTIONS</envar> is
95
set, then <application>libpq</>-based clients will pass that
96
string to the server, which will interpret it as
97
<command>postgres</command> command-line options.
101
<title>General Purpose</title>
105
<term><option>-A 0|1</option></term>
108
Enables run-time assertion checks, which is a debugging aid to
109
detect programming mistakes. This option is only available if
110
assertions were enabled when <productname>PostgreSQL</> was
111
compiled. If so, the default is on.
117
<term><option>-B <replaceable class="parameter">nbuffers</replaceable></option></term>
120
Sets the number of shared buffers for use by the server
121
processes. The default value of this parameter is chosen
122
automatically by <application>initdb</application>.
123
Specifying this option is equivalent to setting the
124
<xref linkend="guc-shared-buffers"> configuration parameter.
130
<term><option>-c <replaceable>name</replaceable>=<replaceable>value</replaceable></option></term>
133
Sets a named run-time parameter. The configuration parameters
134
supported by <productname>PostgreSQL</productname> are
135
described in <xref linkend="runtime-config">. Most of the
136
other command line options are in fact short forms of such a
137
parameter assignment. <option>-c</> can appear multiple times
138
to set multiple parameters.
144
<term><option>-d <replaceable>debug-level</replaceable></option></term>
147
Sets the debug level. The higher this value is set, the more
148
debugging output is written to the server log. Values are
149
from 1 to 5. It is also possible to pass <literal>-d
150
0</literal> for a specific session, which will prevent the
151
server log level of the parent <command>postgres</> process from being
152
propagated to this session.
158
<term><option>-D <replaceable class="parameter">datadir</replaceable></option></term>
161
Specifies the file system location of the data directory or
162
configuration file(s). See
163
<xref linkend="runtime-config-file-locations"> for details.
169
<term><option>-e</option></term>
172
Sets the default date style to <quote>European</quote>, that is
173
<literal>DMY</> ordering of input date fields. This also causes
174
the day to be printed before the month in certain date output formats.
175
See <xref linkend="datatype-datetime"> for more information.
181
<term><option>-F</option></term>
184
Disables <function>fsync</function> calls for improved
185
performance, at the risk of data corruption in the event of a
186
system crash. Specifying this option is equivalent to
187
disabling the <xref linkend="guc-fsync"> configuration
188
parameter. Read the detailed documentation before using this!
194
<term><option>-h <replaceable class="parameter">hostname</replaceable></option></term>
197
Specifies the IP host name or address on which
198
<command>postgres</command> is to listen for TCP/IP
199
connections from client applications. The value can also be a
200
comma-separated list of addresses, or <literal>*</> to specify
201
listening on all available interfaces. An empty value
202
specifies not listening on any IP addresses, in which case
203
only Unix-domain sockets can be used to connect to the
204
server. Defaults to listening only on
205
<systemitem class="systemname">localhost</systemitem>.
206
Specifying this option is equivalent to setting the <xref
207
linkend="guc-listen-addresses"> configuration parameter.
213
<term><option>-i</option></term>
216
Allows remote clients to connect via TCP/IP (Internet domain)
217
connections. Without this option, only local connections are
218
accepted. This option is equivalent to setting
219
<varname>listen_addresses</> to <literal>*</> in
220
<filename>postgresql.conf</> or via <option>-h</>.
223
This option is deprecated since it does not allow access to the
224
full functionality of <xref linkend="guc-listen-addresses">.
225
It's usually better to set <varname>listen_addresses</> directly.
231
<term><option>-k <replaceable class="parameter">directory</replaceable></option></term>
234
Specifies the directory of the Unix-domain socket on which
235
<command>postgres</command> is to listen for
236
connections from client applications. The default is normally
237
<filename>/tmp</filename>, but can be changed at build time.
243
<term><option>-l</option></term>
246
Enables secure connections using <acronym>SSL</acronym>.
247
<productname>PostgreSQL</productname> must have been compiled with
248
support for <acronym>SSL</acronym> for this option to be
249
available. For more information on using <acronym>SSL</acronym>,
250
refer to <xref linkend="ssl-tcp">.
256
<term><option>-N <replaceable class="parameter">max-connections</replaceable></option></term>
259
Sets the maximum number of client connections that this
260
server will accept. The default value of this parameter is chosen
261
automatically by <application>initdb</application>.
262
Specifying this option is equivalent to setting the
263
<xref linkend="guc-max-connections"> configuration parameter.
269
<term><option>-o <replaceable class="parameter">extra-options</replaceable></option></term>
272
The command-line-style options specified in <replaceable
273
class="parameter">extra-options</replaceable> are passed to
274
all server processes started by this
275
<command>postgres</command> process. If the option string contains
276
any spaces, the entire string must be quoted.
280
The use of this option is obsolete; all command-line options
281
for server processes can be specified directly on the
282
<command>postgres</command> command line.
288
<term><option>-p <replaceable class="parameter">port</replaceable></option></term>
291
Specifies the TCP/IP port or local Unix domain socket file
292
extension on which <command>postgres</command>
293
is to listen for connections from client applications.
294
Defaults to the value of the <envar>PGPORT</envar> environment
295
variable, or if <envar>PGPORT</envar> is not set, then
296
defaults to the value established during compilation (normally
297
5432). If you specify a port other than the default port,
298
then all client applications must specify the same port using
299
either command-line options or <envar>PGPORT</envar>.
305
<term><option>-s</option></term>
308
Print time information and other statistics at the end of each command.
309
This is useful for benchmarking or for use in tuning the number of
316
<term><option>-S</option> <replaceable class="parameter">work-mem</replaceable></term>
319
Specifies the amount of memory to be used by internal sorts and hashes
320
before resorting to temporary disk files. See the description of the
321
<varname>work_mem</> configuration parameter in <xref
322
linkend="runtime-config-resource-memory">.
328
<term><option>--<replaceable>name</replaceable>=<replaceable>value</replaceable></option></term>
331
Sets a named run-time parameter; a shorter form of
338
<term><option>--describe-config</option></term>
341
This option dumps out the server's internal configuration variables,
342
descriptions, and defaults in tab-delimited <command>COPY</> format.
343
It is designed primarily for use by administration tools.
351
<title>Semi-internal Options</title>
354
The options described here are used
355
mainly for debugging purposes, and in some cases to assist with
356
recovery of severely damaged databases. There should be no reason
357
to use them in a production database setup. They are listed
358
here only for use by <productname>PostgreSQL</productname>
359
system developers. Furthermore, these options might
360
change or be removed in a future release without notice.
365
<term><option>-f</option> <literal>{ s | i | m | n | h }</literal></term>
368
Forbids the use of particular scan and join methods:
369
<literal>s</literal> and <literal>i</literal>
370
disable sequential and index scans respectively, while
371
<literal>n</literal>, <literal>m</literal>, and <literal>h</literal>
372
disable nested-loop, merge and hash joins respectively.
376
Neither sequential scans nor nested-loop joins can be disabled
377
completely; the <literal>-fs</literal> and
378
<literal>-fn</literal> options simply discourage the optimizer
379
from using those plan types if it has any other alternative.
385
<term><option>-n</option></term>
388
This option is for debugging problems that cause a server
389
process to die abnormally. The ordinary strategy in this
390
situation is to notify all other server processes that they
391
must terminate and then reinitialize the shared memory and
392
semaphores. This is because an errant server process could
393
have corrupted some shared state before terminating. This
394
option specifies that <command>postgres</command> will
395
not reinitialize shared data structures. A knowledgeable
396
system programmer can then use a debugger to examine shared
397
memory and semaphore state.
403
<term><option>-O</option></term>
406
Allows the structure of system tables to be modified. This is
407
used by <command>initdb</command>.
413
<term><option>-P</option></term>
416
Ignore system indexes when reading system tables, but still update
417
the indexes when modifying the tables. This is useful when
418
recovering from damaged system indexes.
424
<term><option>-t</option> <literal>pa[rser] | pl[anner] | e[xecutor]</literal></term>
427
Print timing statistics for each query relating to each of the
428
major system modules. This option cannot be used together
429
with the <option>-s</option> option.
435
<term><option>-T</option></term>
438
This option is for debugging problems that cause a server
439
process to die abnormally. The ordinary strategy in this
440
situation is to notify all other server processes that they
441
must terminate and then reinitialize the shared memory and
442
semaphores. This is because an errant server process could
443
have corrupted some shared state before terminating. This
444
option specifies that <command>postgres</command> will
445
stop all other server processes by sending the signal
446
<literal>SIGSTOP</literal>, but will not cause them to
447
terminate. This permits system programmers to collect core
448
dumps from all server processes by hand.
454
<term><option>-v</option> <replaceable class="parameter">protocol</replaceable></term>
457
Specifies the version number of the frontend/backend protocol
458
to be used for a particular session. This option is for
465
<term><option>-W</option> <replaceable class="parameter">seconds</replaceable></term>
468
A delay of this many seconds occurs when a new server process
469
is started, after it conducts the authentication procedure.
470
This is intended to give an opportunity to attach to the
471
server process with a debugger.
479
<title>Options for Single-User Mode</title>
482
The following options only apply to the single-user mode.
487
<term><option>--single</option></term>
490
Selects the single-user mode. This must be the first argument
497
<term><replaceable class="parameter">database</replaceable></term>
500
Specifies the name of the database to be accessed. This must be
501
the last argument on the command line. If it is
502
omitted it defaults to the user name.
508
<term><option>-E</option></term>
517
<term><option>-j</option></term>
520
Disables use of newline as a statement delimiter.
526
<term><option>-r</option> <replaceable class="parameter">filename</replaceable></term>
529
Send all server log output to <replaceable
530
class="parameter">filename</replaceable>. In normal multiuser
531
mode, this option is ignored, and <systemitem>stderr</> is
532
used by all processes.
541
<title>Environment</title>
545
<term><envar>PGCLIENTENCODING</envar></term>
549
Default character encoding used by clients. (The clients can
550
override this individually.) This value can also be set in the
557
<term><envar>PGDATA</envar></term>
561
Default data directory location
567
<term><envar>PGDATESTYLE</envar></term>
571
Default value of the <xref linkend="guc-datestyle"> run-time
572
parameter. (The use of this environment variable is deprecated.)
578
<term><envar>PGPORT</envar></term>
582
Default port number (preferably set in the configuration file)
588
<term><envar>TZ</envar></term>
601
<title>Diagnostics</title>
604
A failure message mentioning <literal>semget</> or
605
<literal>shmget</> probably indicates you need to configure your
606
kernel to provide adequate shared memory and semaphores. For more
607
discussion see <xref linkend="kernel-resources">. You might be able
608
to postpone reconfiguring your kernel by decreasing <xref
609
linkend="guc-shared-buffers"> to reduce the shared memory
610
consumption of <productname>PostgreSQL</>, and/or by reducing
611
<xref linkend="guc-max-connections"> to reduce the semaphore
616
A failure message suggesting that another server is already running
617
should be checked carefully, for example by using the command
619
<prompt>$</prompt> <userinput>ps ax | grep postgres</userinput>
623
<prompt>$</prompt> <userinput>ps -ef | grep postgres</userinput>
625
depending on your system. If you are certain that no conflicting
626
server is running, you can remove the lock file mentioned in the
627
message and try again.
631
A failure message indicating inability to bind to a port might
632
indicate that that port is already in use by some
633
non-<productname>PostgreSQL</productname> process. You might also
634
get this error if you terminate <command>postgres</command>
635
and immediately restart it using the same port; in this case, you
636
must simply wait a few seconds until the operating system closes
637
the port before trying again. Finally, you might get this error if
638
you specify a port number that your operating system considers to
639
be reserved. For example, many versions of Unix consider port
640
numbers under 1024 to be <quote>trusted</quote> and only permit
641
the Unix superuser to access them.
650
The utility command <xref linkend="app-pg-ctl"> can be used to
651
start and shut down the <command>postgres</command> server
652
safely and comfortably.
656
If at all possible, <emphasis>do not</emphasis> use
657
<literal>SIGKILL</literal> to kill the main
658
<command>postgres</command> server. Doing so will prevent
659
<command>postgres</command> from freeing the system
660
resources (e.g., shared memory and semaphores) that it holds before
661
terminating. This might cause problems for starting a fresh
662
<command>postgres</command> run.
666
To terminate the <command>postgres</command> server normally, the
667
signals <literal>SIGTERM</literal>, <literal>SIGINT</literal>, or
668
<literal>SIGQUIT</literal> can be used. The first will wait for
669
all clients to terminate before quitting, the second will
670
forcefully disconnect all clients, and the third will quit
671
immediately without proper shutdown, resulting in a recovery run
676
The <literal>SIGHUP</literal> signal will reload
677
the server configuration files. It is also possible to send
678
<literal>SIGHUP</literal> to an individual server process, but that
679
is usually not sensible.
683
To cancel a running query, send the <literal>SIGINT</literal> signal
684
to the process running that command.
688
The <command>postgres</command> server uses <literal>SIGTERM</literal>
689
to tell subordinate server processes to quit normally and
690
<literal>SIGQUIT</literal> to terminate without the normal cleanup.
691
These signals <emphasis>should not</emphasis> be used by users. It
692
is also unwise to send <literal>SIGKILL</literal> to a server
693
process — the main <command>postgres</command> process will
694
interpret this as a crash and will force all the sibling processes
695
to quit as part of its standard crash-recovery procedure.
699
<refsect1 id="app-postgres-bugs">
702
The <option>--</> options will not work on <systemitem
703
class="osname">FreeBSD</> or <systemitem class="osname">OpenBSD</>.
704
Use <option>-c</> instead. This is a bug in the affected operating
705
systems; a future release of <productname>PostgreSQL</productname>
706
will provide a workaround if this is not fixed.
714
To start a single-user mode server, use a command like
716
<userinput>postgres --single -D /usr/local/pgsql/data <replaceable>other-options</> my_database</userinput>
718
Provide the correct path to the database directory with <option>-D</>, or
719
make sure that the environment variable <envar>PGDATA</> is set.
720
Also specify the name of the particular database you want to work in.
724
Normally, the single-user mode server treats newline as the command
725
entry terminator; there is no intelligence about semicolons,
726
as there is in <application>psql</>. To continue a command
727
across multiple lines, you must type backslash just before each
728
newline except the last one.
732
But if you use the <option>-j</> command line switch, then newline does
733
not terminate command entry. In this case, the server will read the standard input
734
until the end-of-file (<acronym>EOF</>) marker, then
735
process the input as a single command string. Backslash-newline is not
736
treated specially in this case.
740
To quit the session, type <acronym>EOF</acronym>
741
(<keycombo action="simul"><keycap>Control</><keycap>D</></>, usually).
743
used <option>-j</>, two consecutive <acronym>EOF</>s are needed to exit.
747
Note that the single-user mode server does not provide sophisticated
748
line-editing features (no command history, for example).
749
Single-User mode also does not do any background processing, like
750
automatic checkpoints.
755
<refsect1 id="app-postgres-examples">
756
<title>Examples</title>
759
To start <command>postgres</command> in the background
760
using default values, type:
763
<prompt>$</prompt> <userinput>nohup postgres >logfile 2>&1 </dev/null &</userinput>
768
To start <command>postgres</command> with a specific
771
<prompt>$</prompt> <userinput>postgres -p 1234</userinput>
773
To connect to this server using <application>psql</>, specify this port with the -p option:
775
<prompt>$</prompt> <userinput>psql -p 1234</userinput>
777
or set the environment variable <envar>PGPORT</envar>:
779
<prompt>$</prompt> <userinput>export PGPORT=1234</userinput>
780
<prompt>$</prompt> <userinput>psql</userinput>
785
Named run-time parameters can be set in either of these styles:
787
<prompt>$</prompt> <userinput>postgres -c work_mem=1234</userinput>
788
<prompt>$</prompt> <userinput>postgres --work-mem=1234</userinput>
790
Either form overrides whatever setting might exist for
791
<varname>work_mem</> in <filename>postgresql.conf</>. Notice that
792
underscores in parameter names can be written as either underscore
793
or dash on the command line. Except for short-term experiments,
794
it's probably better practice to edit the setting in
795
<filename>postgresql.conf</> than to rely on a command-line switch
801
<title>See Also</title>
804
<xref linkend="app-initdb">,
805
<xref linkend="app-pg-ctl">
added
removed
doc/src/sgml/ref/postgres-ref.sgml
