1POSTGRES(1) PostgreSQL Server Applications POSTGRES(1)
2
3
4
6 postgres - PostgreSQL database server
7
8
10 postgres [ option... ]
11
13 postgres is the PostgreSQL database server. In order for a client
14 application to access a database it connects (over a network or
15 locally) to a running postgres instance. The postgres instance then
16 starts a separate server process to handle the connection.
17
18 One postgres instance always manages the data of exactly one database
19 cluster. A database cluster is a collection of databases that is stored
20 at a common file system location (the ``data area''). More than one
21 postgres instance can run on a system at one time, so long as they use
22 different data areas and different communication ports (see below).
23 When postgres starts it needs to know the location of the data area.
24 The location must be specified by the -D option or the PGDATA environ‐
25 ment variable; there is no default. Typically, -D or PGDATA points
26 directly to the data area directory created by initdb(1). Other possi‐
27 ble file layouts are discussed in in the documentation.
28
29 By default postgres starts in the foreground and prints log messages to
30 the standard error stream. In practical applications postgres should be
31 started as a background process, perhaps at boot time.
32
33 The postgres command can also be called in single-user mode. The pri‐
34 mary use for this mode is during bootstrapping by initdb(1). Sometimes
35 it is used for debugging or disaster recovery (but note that running a
36 single-user server is not truly suitable for debugging the server,
37 since no realistic interprocess communication and locking will happen).
38 When invoked in single-user mode from the shell, the user can enter
39 queries and the results will be printed to the screen, but in a form
40 that is more useful for developers than end users. In the single-user
41 mode, the session user will be set to the user with ID 1, and implicit
42 superuser powers are granted to this user. This user does not actually
43 have to exist, so the single-user mode can be used to manually recover
44 from certain kinds of accidental damage to the system catalogs.
45
47 postgres accepts the following command-line arguments. For a detailed
48 discussion of the options consult in the documentation. You can save
49 typing most of these options by setting up a configuration file. Some
50 (safe) options can also be set from the connecting client in an appli‐
51 cation-dependent way to apply only for that session. For example, if
52 the environment variable PGOPTIONS is set, then libpq-based clients
53 will pass that string to the server, which will interpret it as post‐
54 gres command-line options.
55
56 GENERAL PURPOSE
57 -A 0|1 Enables run-time assertion checks, which is a debugging aid to
58 detect programming mistakes. This option is only available if
59 assertions were enabled when PostgreSQL was compiled. If so, the
60 default is on.
61
62 -B nbuffers
63 Sets the number of shared buffers for use by the server pro‐
64 cesses. The default value of this parameter is chosen automati‐
65 cally by initdb. Specifying this option is equivalent to set‐
66 ting the shared_buffers configuration parameter.
67
68 -c name=value
69 Sets a named run-time parameter. The configuration parameters
70 supported by PostgreSQL are described in in the documentation.
71 Most of the other command line options are in fact short forms
72 of such a parameter assignment. -c can appear multiple times to
73 set multiple parameters.
74
75 -d debug-level
76 Sets the debug level. The higher this value is set, the more
77 debugging output is written to the server log. Values are from 1
78 to 5. It is also possible to pass -d 0 for a specific session,
79 which will prevent the server log level of the parent postgres
80 process from being propagated to this session.
81
82 -D datadir
83 Specifies the file system location of the data directory or con‐
84 figuration file(s). See in the documentation for details.
85
86 -e Sets the default date style to ``European'', that is DMY order‐
87 ing of input date fields. This also causes the day to be printed
88 before the month in certain date output formats. See in the
89 documentation for more information.
90
91 -F Disables fsync calls for improved performance, at the risk of
92 data corruption in the event of a system crash. Specifying this
93 option is equivalent to disabling the fsync configuration param‐
94 eter. Read the detailed documentation before using this!
95
96 -h hostname
97 Specifies the IP host name or address on which postgres is to
98 listen for TCP/IP connections from client applications. The
99 value can also be a comma-separated list of addresses, or * to
100 specify listening on all available interfaces. An empty value
101 specifies not listening on any IP addresses, in which case only
102 Unix-domain sockets can be used to connect to the server.
103 Defaults to listening only on localhost. Specifying this option
104 is equivalent to setting the listen_addresses configuration
105 parameter.
106
107 -i Allows remote clients to connect via TCP/IP (Internet domain)
108 connections. Without this option, only local connections are
109 accepted. This option is equivalent to setting listen_addresses
110 to * in postgresql.conf or via -h.
111
112 This option is deprecated since it does not allow access to the
113 full functionality of listen_addresses. It's usually better to
114 set listen_addresses directly.
115
116 -k directory
117 Specifies the directory of the Unix-domain socket on which post‐
118 gres is to listen for connections from client applications. The
119 default is normally /tmp, but can be changed at build time.
120
121 -l Enables secure connections using SSL. PostgreSQL must have been
122 compiled with support for SSL for this option to be available.
123 For more information on using SSL, refer to in the documenta‐
124 tion.
125
126 -N max-connections
127 Sets the maximum number of client connections that this server
128 will accept. The default value of this parameter is chosen auto‐
129 matically by initdb. Specifying this option is equivalent to
130 setting the max_connections configuration parameter.
131
132 -o extra-options
133 The command-line-style options specified in extra-options are
134 passed to all server processes started by this postgres process.
135 If the option string contains any spaces, the entire string must
136 be quoted.
137
138 The use of this option is obsolete; all command-line options for
139 server processes can be specified directly on the postgres com‐
140 mand line.
141
142 -p port
143 Specifies the TCP/IP port or local Unix domain socket file
144 extension on which postgres is to listen for connections from
145 client applications. Defaults to the value of the PGPORT envi‐
146 ronment variable, or if PGPORT is not set, then defaults to the
147 value established during compilation (normally 5432). If you
148 specify a port other than the default port, then all client
149 applications must specify the same port using either command-
150 line options or PGPORT.
151
152 -s Print time information and other statistics at the end of each
153 command. This is useful for benchmarking or for use in tuning
154 the number of buffers.
155
156 -S work-mem
157 Specifies the amount of memory to be used by internal sorts and
158 hashes before resorting to temporary disk files. See the
159 description of the work_mem configuration parameter in in the
160 documentation.
161
162 --name=value
163 Sets a named run-time parameter; a shorter form of -c.
164
165 --describe-config
166 This option dumps out the server's internal configuration vari‐
167 ables, descriptions, and defaults in tab-delimited COPY format.
168 It is designed primarily for use by administration tools.
169
170 SEMI-INTERNAL OPTIONS
171 The options described here are used mainly for debugging purposes, and
172 in some cases to assist with recovery of severely damaged databases.
173 There should be no reason to use them in a production database setup.
174 They are listed here only for use by PostgreSQL system developers. Fur‐
175 thermore, these options might change or be removed in a future release
176 without notice.
177
178 -f { s | i | m | n | h }
179 Forbids the use of particular scan and join methods: s and i
180 disable sequential and index scans respectively, while n, m, and
181 h disable nested-loop, merge and hash joins respectively.
182
183 Neither sequential scans nor nested-loop joins can be disabled
184 completely; the -fs and -fn options simply discourage the opti‐
185 mizer from using those plan types if it has any other alterna‐
186 tive.
187
188 -n This option is for debugging problems that cause a server
189 process to die abnormally. The ordinary strategy in this situa‐
190 tion is to notify all other server processes that they must ter‐
191 minate and then reinitialize the shared memory and semaphores.
192 This is because an errant server process could have corrupted
193 some shared state before terminating. This option specifies that
194 postgres will not reinitialize shared data structures. A knowl‐
195 edgeable system programmer can then use a debugger to examine
196 shared memory and semaphore state.
197
198 -O Allows the structure of system tables to be modified. This is
199 used by initdb.
200
201 -P Ignore system indexes when reading system tables (but still
202 update the indexes when modifying the tables). This is useful
203 when recovering from damaged system indexes.
204
205 -t pa[rser] | pl[anner] | e[xecutor]
206 Print timing statistics for each query relating to each of the
207 major system modules. This option cannot be used together with
208 the -s option.
209
210 -T This option is for debugging problems that cause a server
211 process to die abnormally. The ordinary strategy in this situa‐
212 tion is to notify all other server processes that they must ter‐
213 minate and then reinitialize the shared memory and semaphores.
214 This is because an errant server process could have corrupted
215 some shared state before terminating. This option specifies that
216 postgres will stop all other server processes by sending the
217 signal SIGSTOP, but will not cause them to terminate. This per‐
218 mits system programmers to collect core dumps from all server
219 processes by hand.
220
221 -v protocol
222 Specifies the version number of the frontend/backend protocol to
223 be used for a particular session. This option is for internal
224 use only.
225
226 -W seconds
227 A delay of this many seconds occurs when a new server process is
228 started, after it conducts the authentication procedure. This
229 is intended to give an opportunity to attach to the server
230 process with a debugger.
231
232 OPTIONS FOR SINGLE-USER MODE
233 The following options only apply to the single-user mode.
234
235 --single
236 Selects the single-user mode. This must be the first argument on
237 the command line.
238
239 database
240 Specifies the name of the database to be accessed. This must be
241 the last argument on the command line. If it is omitted it
242 defaults to the user name.
243
244 -E Echo all commands.
245
246 -j Disables use of newline as a statement delimiter.
247
248 -r filename
249 Send all server log output to filename. In normal multiuser
250 mode, this option is ignored, and stderr is used by all pro‐
251 cesses.
252
254 PGCLIENTENCODING
255 Default character encoding used by clients. (The clients can
256 override this individually.) This value can also be set in the
257 configuration file.
258
259 PGDATA Default data directory location
260
261 PGDATESTYLE
262 Default value of the datestyle run-time parameter. (The use of
263 this environment variable is deprecated.)
264
265 PGPORT Default port (preferably set in the configuration file)
266
267 TZ Server time zone
268
270 A failure message mentioning semget or shmget probably indicates you
271 need to configure your kernel to provide adequate shared memory and
272 semaphores. For more discussion see in the documentation. You might be
273 able to postpone reconfiguring your kernel by decreasing shared_buffers
274 to reduce the shared memory consumption of PostgreSQL, and/or by reduc‐
275 ing max_connections to reduce the semaphore consumption.
276
277 A failure message suggesting that another server is already running
278 should be checked carefully, for example by using the command
279
280 $ ps ax | grep postgres
281
282 or
283
284 $ ps -ef | grep postgres
285
286 depending on your system. If you are certain that no conflicting server
287 is running, you can remove the lock file mentioned in the message and
288 try again.
289
290 A failure message indicating inability to bind to a port might indicate
291 that that port is already in use by some non-PostgreSQL process. You
292 might also get this error if you terminate postgres and immediately
293 restart it using the same port; in this case, you must simply wait a
294 few seconds until the operating system closes the port before trying
295 again. Finally, you might get this error if you specify a port number
296 that your operating system considers to be reserved. For example, many
297 versions of Unix consider port numbers under 1024 to be ``trusted'' and
298 only permit the Unix superuser to access them.
299
301 The utility command pg_ctl(1) can be used to start and shut down the
302 postgres server safely and comfortably.
303
304 If at all possible, do not use SIGKILL to kill the main postgres
305 server. Doing so will prevent postgres from freeing the system
306 resources (e.g., shared memory and semaphores) that it holds before
307 terminating. This might cause problems for starting a fresh postgres
308 run.
309
310 To terminate the postgres server normally, the signals SIGTERM, SIGINT,
311 or SIGQUIT can be used. The first will wait for all clients to termi‐
312 nate before quitting, the second will forcefully disconnect all
313 clients, and the third will quit immediately without proper shutdown,
314 resulting in a recovery run during restart.
315
316 The SIGHUP signal will reload the server configuration files. It is
317 also possible to send SIGHUP to an individual server process, but that
318 is usually not sensible.
319
320 To cancel a running query, send the SIGINT signal to the process run‐
321 ning that command.
322
323 The postgres server uses SIGTERM to tell subordinate server processes
324 to quit normally and SIGQUIT to terminate without the normal cleanup.
325 These signals should not be used by users. It is also unwise to send
326 SIGKILL to a server process — the main postgres process will interpret
327 this as a crash and will force all the sibling processes to quit as
328 part of its standard crash-recovery procedure.
329
331 The -- options will not work on FreeBSD or OpenBSD. Use -c instead.
332 This is a bug in the affected operating systems; a future release of
333 PostgreSQL will provide a workaround if this is not fixed.
334
336 To start a single-user mode server, use a command like
337
338 postgres --single -D /usr/local/pgsql/data other-options my_database
339
340 Provide the correct path to the database directory with -D, or make
341 sure that the environment variable PGDATA is set. Also specify the
342 name of the particular database you want to work in.
343
344 Normally, the single-user mode server treats newline as the command
345 entry terminator; there is no intelligence about semicolons, as there
346 is in psql. To continue a command across multiple lines, you must type
347 backslash just before each newline except the last one.
348
349 But if you use the -j command line switch, then newline does not termi‐
350 nate command entry. In this case, the server will read the standard
351 input until the end-of-file (EOF) marker, then process the input as a
352 single command string. Backslash-newline is not treated specially in
353 this case.
354
355 To quit the session, type EOF (Control+D, usually). If you've used -j,
356 two consecutive EOFs are needed to exit.
357
358 Note that the single-user mode server does not provide sophisticated
359 line-editing features (no command history, for example).
360
362 To start postgres in the background using default values, type:
363
364 $ nohup postgres >logfile 2>&1 </dev/null &
365
366
367 To start postgres with a specific port:
368
369 $ postgres -p 1234
370
371 This command will start up postgres communicating through the port
372 1234. In order to connect to this server using psql, you would need to
373 run it as
374
375 $ psql -p 1234
376
377 or set the environment variable PGPORT:
378
379 $ export PGPORT=1234
380 $ psql
381
382
383 Named run-time parameters can be set in either of these styles:
384
385 $ postgres -c work_mem=1234
386 $ postgres --work-mem=1234
387
388 Either form overrides whatever setting might exist for work_mem in
389 postgresql.conf. Notice that underscores in parameter names can be
390 written as either underscore or dash on the command line. Except for
391 short-term experiments, it's probably better practice to edit the set‐
392 ting in postgresql.conf than to rely on a command-line switch to set a
393 parameter.
394
396 initdb(1), pg_ctl(1)
397
398
399
400Application 2014-02-17 POSTGRES(1)