Debian Codegraph @

doc/src/sgml/runtime.sgml

raw
1 <!-- doc/src/sgml/runtime.sgml -->
2
3 <chapter id="runtime">
4 <title>Server Setup and Operation</title>
5
6 <para>
7 This chapter discusses how to set up and run the database server,
8 and its interactions with the operating system.
9 </para>
10
11 <para>
12 The directions in this chapter assume that you are working with
13 plain <productname>PostgreSQL</productname> without any additional
14 infrastructure, for example a copy that you built from source
15 according to the directions in the preceding chapters.
16 If you are working with a pre-packaged or vendor-supplied
17 version of <productname>PostgreSQL</productname>, it is likely that
18 the packager has made special provisions for installing and starting
19 the database server according to your system's conventions.
20 Consult the package-level documentation for details.
21 </para>
22
23 <sect1 id="postgres-user">
24 <title>The <productname>PostgreSQL</productname> User Account</title>
25
26 <indexterm>
27 <primary>postgres user</primary>
28 </indexterm>
29
30 <para>
31 As with any server daemon that is accessible to the outside world,
32 it is advisable to run <productname>PostgreSQL</productname> under a
33 separate user account. This user account should only own the data
34 that is managed by the server, and should not be shared with other
35 daemons. (For example, using the user <literal>nobody</literal> is a bad
36 idea.) In particular, it is advisable that this user account not own
37 the <productname>PostgreSQL</productname> executable files, to ensure
38 that a compromised server process could not modify those executables.
39 </para>
40
41 <para>
42 Pre-packaged versions of <productname>PostgreSQL</productname> will
43 typically create a suitable user account automatically during
44 package installation.
45 </para>
46
47 <para>
48 To add a Unix user account to your system, look for a command
49 <command>useradd</command> or <command>adduser</command>. The user
50 name <systemitem>postgres</systemitem> is often used, and is assumed
51 throughout this book, but you can use another name if you like.
52 </para>
53 </sect1>
54
55 <sect1 id="creating-cluster">
56 <title>Creating a Database Cluster</title>
57
58 <indexterm>
59 <primary>database cluster</primary>
60 </indexterm>
61
62 <indexterm>
63 <primary>data area</primary>
64 <see>database cluster</see>
65 </indexterm>
66
67 <para>
68 Before you can do anything, you must initialize a database storage
69 area on disk. We call this a <firstterm>database cluster</firstterm>.
70 (The <acronym>SQL</acronym> standard uses the term catalog cluster.) A
71 database cluster is a collection of databases that is managed by a
72 single instance of a running database server. After initialization, a
73 database cluster will contain a database named <literal>postgres</literal>,
74 which is meant as a default database for use by utilities, users and third
75 party applications. The database server itself does not require the
76 <literal>postgres</literal> database to exist, but many external utility
77 programs assume it exists. There are two more databases created within
78 each cluster during initialization, named <literal>template1</literal>
79 and <literal>template0</literal>. As the names suggest, these will be
80 used as templates for subsequently-created databases; they should not be
81 used for actual work. (See <xref linkend="managing-databases"/> for
82 information about creating new databases within a cluster.)
83 </para>
84
85 <para>
86 In file system terms, a database cluster is a single directory
87 under which all data will be stored. We call this the <firstterm>data
88 directory</firstterm> or <firstterm>data area</firstterm>. It is
89 completely up to you where you choose to store your data. There is no
90 default, although locations such as
91 <filename>/usr/local/pgsql/data</filename> or
92 <filename>/var/lib/pgsql/data</filename> are popular.
93 The data directory must be initialized before being used, using the program
94 <xref linkend="app-initdb"/><indexterm><primary>initdb</primary></indexterm>
95 which is installed with <productname>PostgreSQL</productname>.
96 </para>
97
98 <para>
99 If you are using a pre-packaged version
100 of <productname>PostgreSQL</productname>, it may well have a specific
101 convention for where to place the data directory, and it may also
102 provide a script for creating the data directory. In that case you
103 should use that script in preference to
104 running <command>initdb</command> directly.
105 Consult the package-level documentation for details.
106 </para>
107
108 <para>
109 To initialize a database cluster manually,
110 run <command>initdb</command> and specify the desired
111 file system location of the database cluster with the
112 <option>-D</option> option, for example:
113 <screen>
114 <prompt>$</prompt> <userinput>initdb -D /usr/local/pgsql/data</userinput>
115 </screen>
116 Note that you must execute this command while logged into the
117 <productname>PostgreSQL</productname> user account, which is
118 described in the previous section.
119 </para>
120
121 <tip>
122 <para>
123 As an alternative to the <option>-D</option> option, you can set
124 the environment variable <envar>PGDATA</envar>.
125 <indexterm><primary><envar>PGDATA</envar></primary></indexterm>
126 </para>
127 </tip>
128
129 <para>
130 Alternatively, you can run <command>initdb</command> via
131 the <xref linkend="app-pg-ctl"/>
132 program<indexterm><primary>pg_ctl</primary></indexterm> like so:
133 <screen>
134 <prompt>$</prompt> <userinput>pg_ctl -D /usr/local/pgsql/data initdb</userinput>
135 </screen>
136 This may be more intuitive if you are
137 using <command>pg_ctl</command> for starting and stopping the
138 server (see <xref linkend="server-start"/>), so
139 that <command>pg_ctl</command> would be the sole command you use
140 for managing the database server instance.
141 </para>
142
143 <para>
144 <command>initdb</command> will attempt to create the directory you
145 specify if it does not already exist. Of course, this will fail if
146 <command>initdb</command> does not have permissions to write in the
147 parent directory. It's generally recommendable that the
148 <productname>PostgreSQL</productname> user own not just the data
149 directory but its parent directory as well, so that this should not
150 be a problem. If the desired parent directory doesn't exist either,
151 you will need to create it first, using root privileges if the
152 grandparent directory isn't writable. So the process might look
153 like this:
154 <screen>
155 root# <userinput>mkdir /usr/local/pgsql</userinput>
156 root# <userinput>chown postgres /usr/local/pgsql</userinput>
157 root# <userinput>su postgres</userinput>
158 postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
159 </screen>
160 </para>
161
162 <para>
163 <command>initdb</command> will refuse to run if the data directory
164 exists and already contains files; this is to prevent accidentally
165 overwriting an existing installation.
166 </para>
167
168 <para>
169 Because the data directory contains all the data stored in the
170 database, it is essential that it be secured from unauthorized
171 access. <command>initdb</command> therefore revokes access
172 permissions from everyone but the
173 <productname>PostgreSQL</productname> user, and optionally, group.
174 Group access, when enabled, is read-only. This allows an unprivileged
175 user in the same group as the cluster owner to take a backup of the
176 cluster data or perform other operations that only require read access.
177 </para>
178
179 <para>
180 Note that enabling or disabling group access on an existing cluster requires
181 the cluster to be shut down and the appropriate mode to be set on all
182 directories and files before restarting
183 <productname>PostgreSQL</productname>. Otherwise, a mix of modes might
184 exist in the data directory. For clusters that allow access only by the
185 owner, the appropriate modes are <literal>0700</literal> for directories
186 and <literal>0600</literal> for files. For clusters that also allow
187 reads by the group, the appropriate modes are <literal>0750</literal>
188 for directories and <literal>0640</literal> for files.
189 </para>
190
191 <para>
192 However, while the directory contents are secure, the default
193 client authentication setup allows any local user to connect to the
194 database and even become the database superuser. If you do not
195 trust other local users, we recommend you use one of
196 <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option>
197 or <option>--pwfile</option> options to assign a password to the
198 database superuser.<indexterm>
199 <primary>password</primary>
200 <secondary>of the superuser</secondary>
201 </indexterm>
202 Also, specify <option>-A scram-sha-256</option>
203 so that the default <literal>trust</literal> authentication
204 mode is not used; or modify the generated <filename>pg_hba.conf</filename>
205 file after running <command>initdb</command>, but
206 <emphasis>before</emphasis> you start the server for the first time. (Other
207 reasonable approaches include using <literal>peer</literal> authentication
208 or file system permissions to restrict connections. See <xref
209 linkend="client-authentication"/> for more information.)
210 </para>
211
212 <para>
213 <command>initdb</command> also initializes the default
214 locale<indexterm><primary>locale</primary></indexterm> for the database cluster.
215 Normally, it will just take the locale settings in the environment
216 and apply them to the initialized database. It is possible to
217 specify a different locale for the database; more information about
218 that can be found in <xref linkend="locale"/>. The default sort order used
219 within the particular database cluster is set by
220 <command>initdb</command>, and while you can create new databases using
221 different sort order, the order used in the template databases that initdb
222 creates cannot be changed without dropping and recreating them.
223 There is also a performance impact for using locales
224 other than <literal>C</literal> or <literal>POSIX</literal>. Therefore, it is
225 important to make this choice correctly the first time.
226 </para>
227
228 <para>
229 <command>initdb</command> also sets the default character set encoding
230 for the database cluster. Normally this should be chosen to match the
231 locale setting. For details see <xref linkend="multibyte"/>.
232 </para>
233
234 <para>
235 Non-<literal>C</literal> and non-<literal>POSIX</literal> locales rely on the
236 operating system's collation library for character set ordering.
237 This controls the ordering of keys stored in indexes. For this reason,
238 a cluster cannot switch to an incompatible collation library version,
239 either through snapshot restore, binary streaming replication, a
240 different operating system, or an operating system upgrade.
241 </para>
242
243 <sect2 id="creating-cluster-mount-points">
244 <title>Use of Secondary File Systems</title>
245
246 <indexterm zone="creating-cluster-mount-points">
247 <primary>file system mount points</primary>
248 </indexterm>
249
250 <para>
251 Many installations create their database clusters on file systems
252 (volumes) other than the machine's <quote>root</quote> volume. If you
253 choose to do this, it is not advisable to try to use the secondary
254 volume's topmost directory (mount point) as the data directory.
255 Best practice is to create a directory within the mount-point
256 directory that is owned by the <productname>PostgreSQL</productname>
257 user, and then create the data directory within that. This avoids
258 permissions problems, particularly for operations such
259 as <application>pg_upgrade</application>, and it also ensures clean failures if
260 the secondary volume is taken offline.
261 </para>
262
263 </sect2>
264
265 <sect2 id="creating-cluster-filesystem">
266 <title>File Systems</title>
267
268 <para>
269 Generally, any file system with POSIX semantics can be used for
270 PostgreSQL. Users prefer different file systems for a variety of reasons,
271 including vendor support, performance, and familiarity. Experience
272 suggests that, all other things being equal, one should not expect major
273 performance or behavior changes merely from switching file systems or
274 making minor file system configuration changes.
275 </para>
276
277 <sect3 id="creating-cluster-nfs">
278 <title>NFS</title>
279
280 <indexterm zone="creating-cluster-nfs">
281 <primary>NFS</primary>
282 </indexterm>
283
284 <para>
285 It is possible to use an <acronym>NFS</acronym> file system for storing
286 the <productname>PostgreSQL</productname> data directory.
287 <productname>PostgreSQL</productname> does nothing special for
288 <acronym>NFS</acronym> file systems, meaning it assumes
289 <acronym>NFS</acronym> behaves exactly like locally-connected drives.
290 <productname>PostgreSQL</productname> does not use any functionality that
291 is known to have nonstandard behavior on <acronym>NFS</acronym>, such as
292 file locking.
293 </para>
294
295 <para>
296 The only firm requirement for using <acronym>NFS</acronym> with
297 <productname>PostgreSQL</productname> is that the file system is mounted
298 using the <literal>hard</literal> option. With the
299 <literal>hard</literal> option, processes can <quote>hang</quote>
300 indefinitely if there are network problems, so this configuration will
301 require a careful monitoring setup. The <literal>soft</literal> option
302 will interrupt system calls in case of network problems, but
303 <productname>PostgreSQL</productname> will not repeat system calls
304 interrupted in this way, so any such interruption will result in an I/O
305 error being reported.
306 </para>
307
308 <para>
309 It is not necessary to use the <literal>sync</literal> mount option. The
310 behavior of the <literal>async</literal> option is sufficient, since
311 <productname>PostgreSQL</productname> issues <literal>fsync</literal>
312 calls at appropriate times to flush the write caches. (This is analogous
313 to how it works on a local file system.) However, it is strongly
314 recommended to use the <literal>sync</literal> export option on the NFS
315 <emphasis>server</emphasis> on systems where it exists (mainly Linux).
316 Otherwise, an <literal>fsync</literal> or equivalent on the NFS client is
317 not actually guaranteed to reach permanent storage on the server, which
318 could cause corruption similar to running with the parameter <xref
319 linkend="guc-fsync"/> off. The defaults of these mount and export
320 options differ between vendors and versions, so it is recommended to
321 check and perhaps specify them explicitly in any case to avoid any
322 ambiguity.
323 </para>
324
325 <para>
326 In some cases, an external storage product can be accessed either via NFS
327 or a lower-level protocol such as iSCSI. In the latter case, the storage
328 appears as a block device and any available file system can be created on
329 it. That approach might relieve the DBA from having to deal with some of
330 the idiosyncrasies of NFS, but of course the complexity of managing
331 remote storage then happens at other levels.
332 </para>
333 </sect3>
334 </sect2>
335
336 </sect1>
337
338 <sect1 id="server-start">
339 <title>Starting the Database Server</title>
340
341 <para>
342 Before anyone can access the database, you must start the database
343 server. The database server program is called
344 <command>postgres</command>.<indexterm><primary>postgres</primary></indexterm>
345 </para>
346
347 <para>
348 If you are using a pre-packaged version
349 of <productname>PostgreSQL</productname>, it almost certainly includes
350 provisions for running the server as a background task according to the
351 conventions of your operating system. Using the package's
352 infrastructure to start the server will be much less work than figuring
353 out how to do this yourself. Consult the package-level documentation
354 for details.
355 </para>
356
357 <para>
358 The bare-bones way to start the server manually is just to invoke
359 <command>postgres</command> directly, specifying the location of the
360 data directory with the <option>-D</option> option, for example:
361 <screen>
362 $ <userinput>postgres -D /usr/local/pgsql/data</userinput>
363 </screen>
364 which will leave the server running in the foreground. This must be
365 done while logged into the <productname>PostgreSQL</productname> user
366 account. Without <option>-D</option>, the server will try to use
367 the data directory named by the environment variable <envar>PGDATA</envar>.
368 If that variable is not provided either, it will fail.
369 </para>
370
371 <para>
372 Normally it is better to start <command>postgres</command> in the
373 background. For this, use the usual Unix shell syntax:
374 <screen>
375 $ <userinput>postgres -D /usr/local/pgsql/data &gt;logfile 2&gt;&amp;1 &amp;</userinput>
376 </screen>
377 It is important to store the server's <systemitem>stdout</systemitem> and
378 <systemitem>stderr</systemitem> output somewhere, as shown above. It will help
379 for auditing purposes and to diagnose problems. (See <xref
380 linkend="logfile-maintenance"/> for a more thorough discussion of log
381 file handling.)
382 </para>
383
384 <para>
385 The <command>postgres</command> program also takes a number of other
386 command-line options. For more information, see the
387 <xref linkend="app-postgres"/> reference page
388 and <xref linkend="runtime-config"/> below.
389 </para>
390
391 <para>
392 This shell syntax can get tedious quickly. Therefore the wrapper
393 program
394 <xref linkend="app-pg-ctl"/><indexterm><primary>pg_ctl</primary></indexterm>
395 is provided to simplify some tasks. For example:
396 <programlisting>
397 pg_ctl start -l logfile
398 </programlisting>
399 will start the server in the background and put the output into the
400 named log file. The <option>-D</option> option has the same meaning
401 here as for <command>postgres</command>. <command>pg_ctl</command>
402 is also capable of stopping the server.
403 </para>
404
405 <para>
406 Normally, you will want to start the database server when the
407 computer boots.<indexterm>
408 <primary>booting</primary>
409 <secondary>starting the server during</secondary>
410 </indexterm>
411 Autostart scripts are operating-system-specific.
412 There are a few example scripts distributed with
413 <productname>PostgreSQL</productname> in the
414 <filename>contrib/start-scripts</filename> directory. Installing one will require
415 root privileges.
416 </para>
417
418 <para>
419 Different systems have different conventions for starting up daemons
420 at boot time. Many systems have a file
421 <filename>/etc/rc.local</filename> or
422 <filename>/etc/rc.d/rc.local</filename>. Others use <filename>init.d</filename> or
423 <filename>rc.d</filename> directories. Whatever you do, the server must be
424 run by the <productname>PostgreSQL</productname> user account
425 <emphasis>and not by root</emphasis> or any other user. Therefore you
426 probably should form your commands using
427 <literal>su postgres -c '...'</literal>. For example:
428 <programlisting>
429 su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'
430 </programlisting>
431 </para>
432
433 <para>
434 Here are a few more operating-system-specific suggestions. (In each
435 case be sure to use the proper installation directory and user
436 name where we show generic values.)
437
438 <itemizedlist>
439 <listitem>
440 <para>
441 For <productname>FreeBSD</productname>, look at the file
442 <filename>contrib/start-scripts/freebsd</filename> in the
443 <productname>PostgreSQL</productname> source distribution.
444 <indexterm><primary>FreeBSD</primary><secondary>start script</secondary></indexterm>
445 </para>
446 </listitem>
447
448 <listitem>
449 <para>
450 On <productname>OpenBSD</productname>, add the following lines
451 to the file <filename>/etc/rc.local</filename>:
452 <indexterm><primary>OpenBSD</primary><secondary>start script</secondary></indexterm>
453 <programlisting>
454 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
455 su -l postgres -c '/usr/local/pgsql/bin/pg_ctl start -s -l /var/postgresql/log -D /usr/local/pgsql/data'
456 echo -n ' postgresql'
457 fi
458 </programlisting>
459 </para>
460 </listitem>
461
462 <listitem>
463 <para>
464 On <productname>Linux</productname> systems either add
465 <indexterm><primary>Linux</primary><secondary>start script</secondary></indexterm>
466 <programlisting>
467 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
468 </programlisting>
469 to <filename>/etc/rc.d/rc.local</filename>
470 or <filename>/etc/rc.local</filename> or look at the file
471 <filename>contrib/start-scripts/linux</filename> in the
472 <productname>PostgreSQL</productname> source distribution.
473 </para>
474
475 <para>
476 When using <application>systemd</application>, you can use the following
477 service unit file (e.g.,
478 at <filename>/etc/systemd/system/postgresql.service</filename>):<indexterm><primary>systemd</primary></indexterm>
479 <programlisting>
480 [Unit]
481 Description=PostgreSQL database server
482 Documentation=man:postgres(1)
483 After=network-online.target
484 Wants=network-online.target
485
486 [Service]
487 Type=notify
488 User=postgres
489 ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
490 ExecReload=/bin/kill -HUP $MAINPID
491 KillMode=mixed
492 KillSignal=SIGINT
493 TimeoutSec=infinity
494
495 [Install]
496 WantedBy=multi-user.target
497 </programlisting>
498 Using <literal>Type=notify</literal> requires that the server binary was
499 built with <literal>configure --with-systemd</literal>.
500 </para>
501
502 <para>
503 Consider carefully the timeout
504 setting. <application>systemd</application> has a default timeout of 90
505 seconds as of this writing and will kill a process that does not report
506 readiness within that time. But a <productname>PostgreSQL</productname>
507 server that might have to perform crash recovery at startup could take
508 much longer to become ready. The suggested value
509 of <literal>infinity</literal> disables the timeout logic.
510 </para>
511 </listitem>
512
513 <listitem>
514 <para>
515 On <productname>NetBSD</productname>, use either the
516 <productname>FreeBSD</productname> or
517 <productname>Linux</productname> start scripts, depending on
518 preference.
519 <indexterm><primary>NetBSD</primary><secondary>start script</secondary></indexterm>
520 </para>
521 </listitem>
522
523 <listitem>
524 <para>
525 On <productname>Solaris</productname>, create a file called
526 <filename>/etc/init.d/postgresql</filename> that contains
527 the following line:
528 <indexterm><primary>Solaris</primary><secondary>start script</secondary></indexterm>
529 <programlisting>
530 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
531 </programlisting>
532 Then, create a symbolic link to it in <filename>/etc/rc3.d</filename> as
533 <filename>S99postgresql</filename>.
534 </para>
535 </listitem>
536 </itemizedlist>
537
538 </para>
539
540 <para>
541 While the server is running, its
542 <acronym>PID</acronym> is stored in the file
543 <filename>postmaster.pid</filename> in the data directory. This is
544 used to prevent multiple server instances from
545 running in the same data directory and can also be used for
546 shutting down the server.
547 </para>
548
549 <sect2 id="server-start-failures">
550 <title>Server Start-up Failures</title>
551
552 <para>
553 There are several common reasons the server might fail to
554 start. Check the server's log file, or start it by hand (without
555 redirecting standard output or standard error) and see what error
556 messages appear. Below we explain some of the most common error
557 messages in more detail.
558 </para>
559
560 <para>
561 <screen>
562 LOG: could not bind IPv4 address "127.0.0.1": Address already in use
563 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
564 FATAL: could not create any TCP/IP sockets
565 </screen>
566 This usually means just what it suggests: you tried to start
567 another server on the same port where one is already running.
568 However, if the kernel error message is not <computeroutput>Address
569 already in use</computeroutput> or some variant of that, there might
570 be a different problem. For example, trying to start a server
571 on a reserved port number might draw something like:
572 <screen>
573 $ <userinput>postgres -p 666</userinput>
574 LOG: could not bind IPv4 address "127.0.0.1": Permission denied
575 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
576 FATAL: could not create any TCP/IP sockets
577 </screen>
578 </para>
579
580 <para>
581 A message like:
582 <screen>
583 FATAL: could not create shared memory segment: Invalid argument
584 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
585 </screen>
586 probably means your kernel's limit on the size of shared memory is
587 smaller than the work area <productname>PostgreSQL</productname>
588 is trying to create (4011376640 bytes in this example).
589 This is only likely to happen if you have set <literal>shared_memory_type</literal>
590 to <literal>sysv</literal>. In that case, you
591 can try starting the server with a smaller-than-normal number of
592 buffers (<xref linkend="guc-shared-buffers"/>), or
593 reconfigure your kernel to increase the allowed shared memory
594 size. You might also see this message when trying to start multiple
595 servers on the same machine, if their total space requested
596 exceeds the kernel limit.
597 </para>
598
599 <para>
600 An error like:
601 <screen>
602 FATAL: could not create semaphores: No space left on device
603 DETAIL: Failed system call was semget(5440126, 17, 03600).
604 </screen>
605 does <emphasis>not</emphasis> mean you've run out of disk
606 space. It means your kernel's limit on the number of <systemitem
607 class="osname">System V</systemitem> semaphores is smaller than the number
608 <productname>PostgreSQL</productname> wants to create. As above,
609 you might be able to work around the problem by starting the
610 server with a reduced number of allowed connections
611 (<xref linkend="guc-max-connections"/>), but you'll eventually want to
612 increase the kernel limit.
613 </para>
614
615 <para>
616 Details about configuring <systemitem class="osname">System V</systemitem>
617 <acronym>IPC</acronym> facilities are given in <xref linkend="sysvipc"/>.
618 </para>
619 </sect2>
620
621 <sect2 id="client-connection-problems">
622 <title>Client Connection Problems</title>
623
624 <para>
625 Although the error conditions possible on the client side are quite
626 varied and application-dependent, a few of them might be directly
627 related to how the server was started. Conditions other than
628 those shown below should be documented with the respective client
629 application.
630 </para>
631
632 <para>
633 <screen>
634 psql: error: connection to server at "server.joe.com" (123.123.123.123), port 5432 failed: Connection refused
635 Is the server running on that host and accepting TCP/IP connections?
636 </screen>
637 This is the generic <quote>I couldn't find a server to talk
638 to</quote> failure. It looks like the above when TCP/IP
639 communication is attempted. A common mistake is to forget to
640 configure <xref linkend="guc-listen-addresses"/> so that the server
641 accepts remote TCP connections.
642 </para>
643
644 <para>
645 Alternatively, you might get this when attempting Unix-domain socket
646 communication to a local server:
647 <screen>
648 psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
649 Is the server running locally and accepting connections on that socket?
650 </screen>
651 If the server is indeed running, check that the client's idea of the
652 socket path (here <literal>/tmp</literal>) agrees with the server's
653 <xref linkend="guc-unix-socket-directories"/> setting.
654 </para>
655
656 <para>
657 A connection failure message always shows the server address or socket
658 path name, which is useful in verifying that the client is trying to
659 connect to the right place. If there is in fact no server
660 listening there, the kernel error message will typically be either
661 <computeroutput>Connection refused</computeroutput> or
662 <computeroutput>No such file or directory</computeroutput>, as
663 illustrated. (It is important to realize that
664 <computeroutput>Connection refused</computeroutput> in this context
665 does <emphasis>not</emphasis> mean that the server got your
666 connection request and rejected it. That case will produce a
667 different message, as shown in <xref
668 linkend="client-authentication-problems"/>.) Other error messages
669 such as <computeroutput>Connection timed out</computeroutput> might
670 indicate more fundamental problems, like lack of network
671 connectivity, or a firewall blocking the connection.
672 </para>
673 </sect2>
674 </sect1>
675
676 <sect1 id="kernel-resources">
677 <title>Managing Kernel Resources</title>
678
679 <para>
680 <productname>PostgreSQL</productname> can sometimes exhaust various operating system
681 resource limits, especially when multiple copies of the server are running
682 on the same system, or in very large installations. This section explains
683 the kernel resources used by <productname>PostgreSQL</productname> and the steps you
684 can take to resolve problems related to kernel resource consumption.
685 </para>
686
687 <sect2 id="sysvipc">
688 <title>Shared Memory and Semaphores</title>
689
690 <indexterm zone="sysvipc">
691 <primary>shared memory</primary>
692 </indexterm>
693
694 <indexterm zone="sysvipc">
695 <primary>semaphores</primary>
696 </indexterm>
697
698 <para>
699 <productname>PostgreSQL</productname> requires the operating system to provide
700 inter-process communication (<acronym>IPC</acronym>) features, specifically
701 shared memory and semaphores. Unix-derived systems typically provide
702 <quote><systemitem class="osname">System V</systemitem></quote> <acronym>IPC</acronym>,
703 <quote><systemitem class="osname">POSIX</systemitem></quote> <acronym>IPC</acronym>, or both.
704 <systemitem class="osname">Windows</systemitem> has its own implementation of
705 these features and is not discussed here.
706 </para>
707
708 <para>
709 By default, <productname>PostgreSQL</productname> allocates
710 a very small amount of System V shared memory, as well as a much larger
711 amount of anonymous <function>mmap</function> shared memory.
712 Alternatively, a single large System V shared memory region can be used
713 (see <xref linkend="guc-shared-memory-type"/>).
714
715 In addition a significant number of semaphores, which can be either
716 System V or POSIX style, are created at server startup. Currently,
717 POSIX semaphores are used on Linux and FreeBSD systems while other
718 platforms use System V semaphores.
719 </para>
720
721 <para>
722 System V <acronym>IPC</acronym> features are typically constrained by
723 system-wide allocation limits.
724 When <productname>PostgreSQL</productname> exceeds one of these limits,
725 the server will refuse to start and
726 should leave an instructive error message describing the problem
727 and what to do about it. (See also <xref
728 linkend="server-start-failures"/>.) The relevant kernel
729 parameters are named consistently across different systems; <xref
730 linkend="sysvipc-parameters"/> gives an overview. The methods to set
731 them, however, vary. Suggestions for some platforms are given below.
732 </para>
733
734 <table id="sysvipc-parameters">
735 <title><systemitem class="osname">System V</systemitem> <acronym>IPC</acronym> Parameters</title>
736
737 <tgroup cols="3">
738 <colspec colname="col1" colwidth="1*"/>
739 <colspec colname="col2" colwidth="3*"/>
740 <colspec colname="col3" colwidth="3*"/>
741 <thead>
742 <row>
743 <entry>Name</entry>
744 <entry>Description</entry>
745 <entry>Values needed to run one <productname>PostgreSQL</productname> instance</entry>
746 </row>
747 </thead>
748
749 <tbody>
750 <row>
751 <entry><varname>SHMMAX</varname></entry>
752 <entry>Maximum size of shared memory segment (bytes)</entry>
753 <entry>at least 1kB, but the default is usually much higher</entry>
754 </row>
755
756 <row>
757 <entry><varname>SHMMIN</varname></entry>
758 <entry>Minimum size of shared memory segment (bytes)</entry>
759 <entry>1</entry>
760 </row>
761
762 <row>
763 <entry><varname>SHMALL</varname></entry>
764 <entry>Total amount of shared memory available (bytes or pages)</entry>
765 <entry>same as <varname>SHMMAX</varname> if bytes,
766 or <literal>ceil(SHMMAX/PAGE_SIZE)</literal> if pages,
767 plus room for other applications</entry>
768 </row>
769
770 <row>
771 <entry><varname>SHMSEG</varname></entry>
772 <entry>Maximum number of shared memory segments per process</entry>
773 <entry>only 1 segment is needed, but the default is much higher</entry>
774 </row>
775
776 <row>
777 <entry><varname>SHMMNI</varname></entry>
778 <entry>Maximum number of shared memory segments system-wide</entry>
779 <entry>like <varname>SHMSEG</varname> plus room for other applications</entry>
780 </row>
781
782 <row>
783 <entry><varname>SEMMNI</varname></entry>
784 <entry>Maximum number of semaphore identifiers (i.e., sets)</entry>
785 <entry>at least <literal>ceil(num_os_semaphores / 16)</literal> plus room for other applications</entry>
786 </row>
787
788 <row>
789 <entry><varname>SEMMNS</varname></entry>
790 <entry>Maximum number of semaphores system-wide</entry>
791 <entry><literal>ceil(num_os_semaphores / 16) * 17</literal> plus room for other applications</entry>
792 </row>
793
794 <row>
795 <entry><varname>SEMMSL</varname></entry>
796 <entry>Maximum number of semaphores per set</entry>
797 <entry>at least 17</entry>
798 </row>
799
800 <row>
801 <entry><varname>SEMMAP</varname></entry>
802 <entry>Number of entries in semaphore map</entry>
803 <entry>see text</entry>
804 </row>
805
806 <row>
807 <entry><varname>SEMVMX</varname></entry>
808 <entry>Maximum value of semaphore</entry>
809 <entry>at least 1000 (The default is often 32767; do not change unless necessary)</entry>
810 </row>
811
812 </tbody>
813 </tgroup>
814 </table>
815
816 <para>
817 <productname>PostgreSQL</productname> requires a few bytes of System V shared memory
818 (typically 48 bytes, on 64-bit platforms) for each copy of the server.
819 On most modern operating systems, this amount can easily be allocated.
820 However, if you are running many copies of the server or you explicitly
821 configure the server to use large amounts of System V shared memory (see
822 <xref linkend="guc-shared-memory-type"/> and <xref
823 linkend="guc-dynamic-shared-memory-type"/>), it may be necessary to
824 increase <varname>SHMALL</varname>, which is the total amount of System V shared
825 memory system-wide. Note that <varname>SHMALL</varname> is measured in pages
826 rather than bytes on many systems.
827 </para>
828
829 <para>
830 Less likely to cause problems is the minimum size for shared
831 memory segments (<varname>SHMMIN</varname>), which should be at most
832 approximately 32 bytes for <productname>PostgreSQL</productname> (it is
833 usually just 1). The maximum number of segments system-wide
834 (<varname>SHMMNI</varname>) or per-process (<varname>SHMSEG</varname>) are unlikely
835 to cause a problem unless your system has them set to zero.
836 </para>
837
838 <para>
839 When using System V semaphores,
840 <productname>PostgreSQL</productname> uses one semaphore per allowed connection
841 (<xref linkend="guc-max-connections"/>), allowed autovacuum worker process
842 (<xref linkend="guc-autovacuum-worker-slots"/>), allowed WAL sender process
843 (<xref linkend="guc-max-wal-senders"/>), allowed background
844 process (<xref linkend="guc-max-worker-processes"/>), etc., in sets of 16.
845 The runtime-computed parameter <xref linkend="guc-num-os-semaphores"/>
846 reports the number of semaphores required. This parameter can be viewed
847 before starting the server with a <command>postgres</command> command like:
848 <programlisting>
849 $ <userinput>postgres -D $PGDATA -C num_os_semaphores</userinput>
850 </programlisting>
851 </para>
852
853 <para>
854 Each set of 16 semaphores will
855 also contain a 17th semaphore which contains a <quote>magic
856 number</quote>, to detect collision with semaphore sets used by
857 other applications. The maximum number of semaphores in the system
858 is set by <varname>SEMMNS</varname>, which consequently must be at least
859 as high as <literal>num_os_semaphores</literal> plus one extra for
860 each set of 16 required semaphores (see the formula in <xref
861 linkend="sysvipc-parameters"/>). The parameter <varname>SEMMNI</varname>
862 determines the limit on the number of semaphore sets that can
863 exist on the system at one time. Hence this parameter must be at
864 least <literal>ceil(num_os_semaphores / 16)</literal>.
865 Lowering the number
866 of allowed connections is a temporary workaround for failures,
867 which are usually confusingly worded <quote>No space
868 left on device</quote>, from the function <function>semget</function>.
869 </para>
870
871 <para>
872 In some cases it might also be necessary to increase
873 <varname>SEMMAP</varname> to be at least on the order of
874 <varname>SEMMNS</varname>. If the system has this parameter
875 (many do not), it defines the size of the semaphore
876 resource map, in which each contiguous block of available semaphores
877 needs an entry. When a semaphore set is freed it is either added to
878 an existing entry that is adjacent to the freed block or it is
879 registered under a new map entry. If the map is full, the freed
880 semaphores get lost (until reboot). Fragmentation of the semaphore
881 space could over time lead to fewer available semaphores than there
882 should be.
883 </para>
884
885 <para>
886 Various other settings related to <quote>semaphore undo</quote>, such as
887 <varname>SEMMNU</varname> and <varname>SEMUME</varname>, do not affect
888 <productname>PostgreSQL</productname>.
889 </para>
890
891 <para>
892 When using POSIX semaphores, the number of semaphores needed is the
893 same as for System V, that is one semaphore per allowed connection
894 (<xref linkend="guc-max-connections"/>), allowed autovacuum worker process
895 (<xref linkend="guc-autovacuum-worker-slots"/>), allowed WAL sender process
896 (<xref linkend="guc-max-wal-senders"/>), allowed background
897 process (<xref linkend="guc-max-worker-processes"/>), etc.
898 On the platforms where this option is preferred, there is no specific
899 kernel limit on the number of POSIX semaphores.
900 </para>
901
902
903 <variablelist>
904 <varlistentry>
905 <term><systemitem class="osname">FreeBSD</systemitem>
906 <indexterm><primary>FreeBSD</primary><secondary>IPC configuration</secondary></indexterm>
907 </term>
908 <listitem>
909 <para>
910 The default shared memory settings are usually good enough, unless
911 you have set <literal>shared_memory_type</literal> to <literal>sysv</literal>.
912 System V semaphores are not used on this platform.
913 </para>
914
915 <para>
916 The default IPC settings can be changed using
917 the <command>sysctl</command> or
918 <command>loader</command> interfaces. The following
919 parameters can be set using <command>sysctl</command>:
920 <screen>
921 <prompt>#</prompt> <userinput>sysctl kern.ipc.shmall=32768</userinput>
922 <prompt>#</prompt> <userinput>sysctl kern.ipc.shmmax=134217728</userinput>
923 </screen>
924 To make these settings persist over reboots, modify
925 <filename>/etc/sysctl.conf</filename>.
926 </para>
927
928 <para>
929 If you have set <literal>shared_memory_type</literal> to
930 <literal>sysv</literal>, you might also want to configure your kernel
931 to lock System V shared memory into RAM and prevent it from being paged
932 out to swap. This can be accomplished using the <command>sysctl</command>
933 setting <literal>kern.ipc.shm_use_phys</literal>.
934 </para>
935
936 <para>
937 If running in a FreeBSD jail, you should set its
938 <literal>sysvshm</literal> parameter to <literal>new</literal>, so that
939 it has its own separate System V shared memory namespace.
940 (Before FreeBSD 11.0, it was necessary to enable shared access to
941 the host's IPC namespace from jails, and take measures to avoid
942 collisions.)
943 </para>
944
945 </listitem>
946 </varlistentry>
947
948 <varlistentry>
949 <term><systemitem class="osname">NetBSD</systemitem>
950 <indexterm><primary>NetBSD</primary><secondary>IPC configuration</secondary></indexterm>
951 </term>
952 <listitem>
953 <para>
954 The default shared memory settings are usually good enough, unless
955 you have set <literal>shared_memory_type</literal> to <literal>sysv</literal>.
956 However, you will need to increase <literal>kern.ipc.semmni</literal>
957 and <literal>kern.ipc.semmns</literal>,
958 as <systemitem class="osname">NetBSD</systemitem>'s default settings
959 for these are unworkably small.
960 </para>
961
962 <para>
963 IPC parameters can be adjusted using <command>sysctl</command>,
964 for example:
965 <screen>
966 <prompt>#</prompt> <userinput>sysctl -w kern.ipc.semmni=100</userinput>
967 </screen>
968 To make these settings persist over reboots, modify
969 <filename>/etc/sysctl.conf</filename>.
970 </para>
971
972 <para>
973 If you have set <literal>shared_memory_type</literal> to
974 <literal>sysv</literal>, you might also want to configure your kernel
975 to lock System V shared memory into RAM and prevent it from being paged
976 out to swap. This can be accomplished using the <command>sysctl</command>
977 setting <literal>kern.ipc.shm_use_phys</literal>.
978 </para>
979 </listitem>
980 </varlistentry>
981
982 <varlistentry>
983 <term><systemitem class="osname">OpenBSD</systemitem>
984 <indexterm><primary>OpenBSD</primary><secondary>IPC configuration</secondary></indexterm>
985 </term>
986 <listitem>
987 <para>
988 The default shared memory settings are usually good enough, unless
989 you have set <literal>shared_memory_type</literal> to <literal>sysv</literal>.
990 However, you will need to
991 increase <literal>kern.seminfo.semmni</literal>
992 and <literal>kern.seminfo.semmns</literal>,
993 as <systemitem class="osname">OpenBSD</systemitem>'s default settings
994 for these are unworkably small.
995 </para>
996
997 <para>
998 IPC parameters can be adjusted using <command>sysctl</command>,
999 for example:
1000 <screen>
1001 <prompt>#</prompt> <userinput>sysctl kern.seminfo.semmni=100</userinput>
1002 </screen>
1003 To make these settings persist over reboots, modify
1004 <filename>/etc/sysctl.conf</filename>.
1005 </para>
1006
1007 </listitem>
1008 </varlistentry>
1009
1010 <varlistentry>
1011 <term><systemitem class="osname">Linux</systemitem>
1012 <indexterm><primary>Linux</primary><secondary>IPC configuration</secondary></indexterm>
1013 </term>
1014 <listitem>
1015 <para>
1016 The default shared memory settings are usually good enough, unless
1017 you have set <literal>shared_memory_type</literal> to <literal>sysv</literal>,
1018 and even then only on older kernel versions that shipped with low defaults.
1019 System V semaphores are not used on this platform.
1020 </para>
1021
1022 <para>
1023 The shared memory size settings can be changed via the
1024 <command>sysctl</command> interface. For example, to allow 16 GB:
1025 <screen>
1026 <prompt>$</prompt> <userinput>sysctl -w kernel.shmmax=17179869184</userinput>
1027 <prompt>$</prompt> <userinput>sysctl -w kernel.shmall=4194304</userinput>
1028 </screen>
1029 To make these settings persist over reboots, see
1030 <filename>/etc/sysctl.conf</filename>.
1031 </para>
1032
1033 </listitem>
1034 </varlistentry>
1035
1036
1037 <varlistentry>
1038 <term><systemitem class="osname">macOS</systemitem>
1039 <indexterm><primary>macOS</primary><secondary>IPC configuration</secondary></indexterm>
1040 </term>
1041 <listitem>
1042 <para>
1043 The default shared memory and semaphore settings are usually good enough, unless
1044 you have set <literal>shared_memory_type</literal> to <literal>sysv</literal>.
1045 </para>
1046 <para>
1047 The recommended method for configuring shared memory in macOS
1048 is to create a file named <filename>/etc/sysctl.conf</filename>,
1049 containing variable assignments such as:
1050 <programlisting>
1051 kern.sysv.shmmax=4194304
1052 kern.sysv.shmmin=1
1053 kern.sysv.shmmni=32
1054 kern.sysv.shmseg=8
1055 kern.sysv.shmall=1024
1056 </programlisting>
1057 Note that in some macOS versions,
1058 <emphasis>all five</emphasis> shared-memory parameters must be set in
1059 <filename>/etc/sysctl.conf</filename>, else the values will be ignored.
1060 </para>
1061
1062 <para>
1063 <varname>SHMMAX</varname> can only be set to a multiple of 4096.
1064 </para>
1065
1066 <para>
1067 <varname>SHMALL</varname> is measured in 4 kB pages on this platform.
1068 </para>
1069
1070 <para>
1071 It is possible to change all but <varname>SHMMNI</varname> on the fly, using
1072 <application>sysctl</application>. But it's still best to set up your preferred
1073 values via <filename>/etc/sysctl.conf</filename>, so that the values will be
1074 kept across reboots.
1075 </para>
1076
1077 </listitem>
1078 </varlistentry>
1079
1080 <varlistentry>
1081 <term><systemitem class="osname">Solaris</systemitem></term>
1082 <term><systemitem class="osname">illumos</systemitem></term>
1083 <listitem>
1084 <para>
1085 The default shared memory and semaphore settings are usually good enough for most
1086 <productname>PostgreSQL</productname> applications. Solaris defaults
1087 to a <varname>SHMMAX</varname> of one-quarter of system <acronym>RAM</acronym>.
1088 To further adjust this setting, use a project setting associated
1089 with the <literal>postgres</literal> user. For example, run the
1090 following as <literal>root</literal>:
1091 <programlisting>
1092 projadd -c "PostgreSQL DB User" -K "project.max-shm-memory=(privileged,8GB,deny)" -U postgres -G postgres user.postgres
1093 </programlisting>
1094 </para>
1095
1096 <para>
1097 This command adds the <literal>user.postgres</literal> project and
1098 sets the shared memory maximum for the <literal>postgres</literal>
1099 user to 8GB, and takes effect the next time that user logs
1100 in, or when you restart <productname>PostgreSQL</productname> (not reload).
1101 The above assumes that <productname>PostgreSQL</productname> is run by
1102 the <literal>postgres</literal> user in the <literal>postgres</literal>
1103 group. No server reboot is required.
1104 </para>
1105
1106 <para>
1107 Other recommended kernel setting changes for database servers which will
1108 have a large number of connections are:
1109 <programlisting>
1110 project.max-shm-ids=(priv,32768,deny)
1111 project.max-sem-ids=(priv,4096,deny)
1112 project.max-msg-ids=(priv,4096,deny)
1113 </programlisting>
1114 </para>
1115
1116 <para>
1117 Additionally, if you are running <productname>PostgreSQL</productname>
1118 inside a zone, you may need to raise the zone resource usage
1119 limits as well. See "Chapter2: Projects and Tasks" in the
1120 <citetitle>System Administrator's Guide</citetitle> for more
1121 information on <literal>projects</literal> and <command>prctl</command>.
1122 </para>
1123 </listitem>
1124 </varlistentry>
1125
1126 </variablelist>
1127
1128 </sect2>
1129
1130 <sect2 id="systemd-removeipc">
1131 <title>systemd RemoveIPC</title>
1132
1133 <indexterm>
1134 <primary>systemd</primary>
1135 <secondary>RemoveIPC</secondary>
1136 </indexterm>
1137
1138 <para>
1139 If <productname>systemd</productname> is in use, some care must be taken
1140 that IPC resources (including shared memory) are not prematurely
1141 removed by the operating system. This is especially of concern when
1142 installing PostgreSQL from source. Users of distribution packages of
1143 PostgreSQL are less likely to be affected, as
1144 the <literal>postgres</literal> user is then normally created as a system
1145 user.
1146 </para>
1147
1148 <para>
1149 The setting <literal>RemoveIPC</literal>
1150 in <filename>logind.conf</filename> controls whether IPC objects are
1151 removed when a user fully logs out. System users are exempt. This
1152 setting defaults to on in stock <productname>systemd</productname>, but
1153 some operating system distributions default it to off.
1154 </para>
1155
1156 <para>
1157 A typical observed effect when this setting is on is that shared memory
1158 objects used for parallel query execution are removed at apparently random
1159 times, leading to errors and warnings while attempting to open and remove
1160 them, like
1161 <screen>
1162 WARNING: could not remove shared memory segment "/PostgreSQL.1450751626": No such file or directory
1163 </screen>
1164 Different types of IPC objects (shared memory vs. semaphores, System V
1165 vs. POSIX) are treated slightly differently
1166 by <productname>systemd</productname>, so one might observe that some IPC
1167 resources are not removed in the same way as others. But it is not
1168 advisable to rely on these subtle differences.
1169 </para>
1170
1171 <para>
1172 A <quote>user logging out</quote> might happen as part of a maintenance
1173 job or manually when an administrator logs in as
1174 the <literal>postgres</literal> user or something similar, so it is hard
1175 to prevent in general.
1176 </para>
1177
1178 <para>
1179 What is a <quote>system user</quote> is determined
1180 at <productname>systemd</productname> compile time from
1181 the <symbol>SYS_UID_MAX</symbol> setting
1182 in <filename>/etc/login.defs</filename>.
1183 </para>
1184
1185 <para>
1186 Packaging and deployment scripts should be careful to create
1187 the <literal>postgres</literal> user as a system user by
1188 using <literal>useradd -r</literal>, <literal>adduser --system</literal>,
1189 or equivalent.
1190 </para>
1191
1192 <para>
1193 Alternatively, if the user account was created incorrectly or cannot be
1194 changed, it is recommended to set
1195 <programlisting>
1196 RemoveIPC=no
1197 </programlisting>
1198 in <filename>/etc/systemd/logind.conf</filename> or another appropriate
1199 configuration file.
1200 </para>
1201
1202 <caution>
1203 <para>
1204 At least one of these two things has to be ensured, or the PostgreSQL
1205 server will be very unreliable.
1206 </para>
1207 </caution>
1208 </sect2>
1209
1210 <sect2 id="kernel-resources-limits">
1211 <title>Resource Limits</title>
1212
1213 <para>
1214 Unix-like operating systems enforce various kinds of resource limits
1215 that might interfere with the operation of your
1216 <productname>PostgreSQL</productname> server. Of particular
1217 importance are limits on the number of processes per user, the
1218 number of open files per process, and the amount of memory available
1219 to each process. Each of these have a <quote>hard</quote> and a
1220 <quote>soft</quote> limit. The soft limit is what actually counts
1221 but it can be changed by the user up to the hard limit. The hard
1222 limit can only be changed by the root user. The system call
1223 <function>setrlimit</function> is responsible for setting these
1224 parameters. The shell's built-in command <command>ulimit</command>
1225 (Bourne shells) or <command>limit</command> (<application>csh</application>) is
1226 used to control the resource limits from the command line. On
1227 BSD-derived systems the file <filename>/etc/login.conf</filename>
1228 controls the various resource limits set during login. See the
1229 operating system documentation for details. The relevant
1230 parameters are <varname>maxproc</varname>,
1231 <varname>openfiles</varname>, and <varname>datasize</varname>. For
1232 example:
1233 <programlisting>
1234 default:\
1235 ...
1236 :datasize-cur=256M:\
1237 :maxproc-cur=256:\
1238 :openfiles-cur=256:\
1239 ...
1240 </programlisting>
1241 (<literal>-cur</literal> is the soft limit. Append
1242 <literal>-max</literal> to set the hard limit.)
1243 </para>
1244
1245 <para>
1246 Kernels can also have system-wide limits on some resources.
1247 <itemizedlist>
1248 <listitem>
1249 <para>
1250 On <productname>Linux</productname> the kernel parameter
1251 <varname>fs.file-max</varname> determines the maximum number of open
1252 files that the kernel will support. It can be changed with
1253 <literal>sysctl -w fs.file-max=<replaceable>N</replaceable></literal>.
1254 To make the setting persist across reboots, add an assignment
1255 in <filename>/etc/sysctl.conf</filename>.
1256 The maximum limit of files per process is fixed at the time the
1257 kernel is compiled; see
1258 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
1259 more information.
1260 </para>
1261 </listitem>
1262 </itemizedlist>
1263 </para>
1264
1265 <para>
1266 The <productname>PostgreSQL</productname> server uses one process
1267 per connection so you should provide for at least as many processes
1268 as allowed connections, in addition to what you need for the rest
1269 of your system. This is usually not a problem but if you run
1270 several servers on one machine things might get tight.
1271 </para>
1272
1273 <para>
1274 The factory default limit on open files is often set to
1275 <quote>socially friendly</quote> values that allow many users to
1276 coexist on a machine without using an inappropriate fraction of
1277 the system resources. If you run many servers on a machine this
1278 is perhaps what you want, but on dedicated servers you might want to
1279 raise this limit.
1280 </para>
1281
1282 <para>
1283 On the other side of the coin, some systems allow individual
1284 processes to open large numbers of files; if more than a few
1285 processes do so then the system-wide limit can easily be exceeded.
1286 If you find this happening, and you do not want to alter the
1287 system-wide limit, you can set <productname>PostgreSQL</productname>'s <xref
1288 linkend="guc-max-files-per-process"/> configuration parameter to
1289 limit the consumption of open files.
1290 </para>
1291
1292 <para>
1293 Another kernel limit that may be of concern when supporting large
1294 numbers of client connections is the maximum socket connection queue
1295 length. If more than that many connection requests arrive within a very
1296 short period, some may get rejected before the <productname>PostgreSQL</productname> server can service
1297 the requests, with those clients receiving unhelpful connection failure
1298 errors such as <quote>Resource temporarily unavailable</quote> or
1299 <quote>Connection refused</quote>. The default queue length limit is 128
1300 on many platforms. To raise it, adjust the appropriate kernel parameter
1301 via <application>sysctl</application>, then restart the <productname>PostgreSQL</productname> server.
1302 The parameter is variously named <varname>net.core.somaxconn</varname>
1303 on Linux, <varname>kern.ipc.soacceptqueue</varname> on newer FreeBSD,
1304 and <varname>kern.ipc.somaxconn</varname> on macOS and other BSD
1305 variants.
1306 </para>
1307 </sect2>
1308
1309 <sect2 id="linux-memory-overcommit">
1310 <title>Linux Memory Overcommit</title>
1311
1312 <indexterm>
1313 <primary>memory overcommit</primary>
1314 </indexterm>
1315
1316 <indexterm>
1317 <primary>OOM</primary>
1318 </indexterm>
1319
1320 <indexterm>
1321 <primary>overcommit</primary>
1322 </indexterm>
1323
1324 <para>
1325 The default virtual memory behavior on Linux is not
1326 optimal for <productname>PostgreSQL</productname>. Because of the
1327 way that the kernel implements memory overcommit, the kernel might
1328 terminate the <productname>PostgreSQL</productname> postmaster (the
1329 supervisor server process) if the memory demands of either
1330 <productname>PostgreSQL</productname> or another process cause the
1331 system to run out of virtual memory.
1332 </para>
1333
1334 <para>
1335 If this happens, you will see a kernel message that looks like
1336 this (consult your system documentation and configuration on where
1337 to look for such a message):
1338 <programlisting>
1339 Out of Memory: Killed process 12345 (postgres).
1340 </programlisting>
1341 This indicates that the <filename>postgres</filename> process
1342 has been terminated due to memory pressure.
1343 Although existing database connections will continue to function
1344 normally, no new connections will be accepted. To recover,
1345 <productname>PostgreSQL</productname> will need to be restarted.
1346 </para>
1347
1348 <para>
1349 One way to avoid this problem is to run
1350 <productname>PostgreSQL</productname> on a machine where you can
1351 be sure that other processes will not run the machine out of
1352 memory. If memory is tight, increasing the swap space of the
1353 operating system can help avoid the problem, because the
1354 out-of-memory (OOM) killer is invoked only when physical memory and
1355 swap space are exhausted.
1356 </para>
1357
1358 <para>
1359 If <productname>PostgreSQL</productname> itself is the cause of the
1360 system running out of memory, you can avoid the problem by changing
1361 your configuration. In some cases, it may help to lower memory-related
1362 configuration parameters, particularly
1363 <link linkend="guc-shared-buffers"><varname>shared_buffers</varname></link>,
1364 <link linkend="guc-work-mem"><varname>work_mem</varname></link>, and
1365 <link linkend="guc-hash-mem-multiplier"><varname>hash_mem_multiplier</varname></link>.
1366 In other cases, the problem may be caused by allowing too many
1367 connections to the database server itself. In many cases, it may
1368 be better to reduce
1369 <link linkend="guc-max-connections"><varname>max_connections</varname></link>
1370 and instead make use of external connection-pooling software.
1371 </para>
1372
1373 <para>
1374 It is possible to modify the
1375 kernel's behavior so that it will not <quote>overcommit</quote> memory.
1376 Although this setting will not prevent the <ulink
1377 url="https://lwn.net/Articles/104179/">OOM killer</ulink> from being invoked
1378 altogether, it will lower the chances significantly and will therefore
1379 lead to more robust system behavior. This is done by selecting strict
1380 overcommit mode via <command>sysctl</command>:
1381 <programlisting>
1382 sysctl -w vm.overcommit_memory=2
1383 </programlisting>
1384 or placing an equivalent entry in <filename>/etc/sysctl.conf</filename>.
1385 You might also wish to modify the related setting
1386 <varname>vm.overcommit_ratio</varname>. For details see the kernel documentation
1387 file <ulink url="https://www.kernel.org/doc/Documentation/vm/overcommit-accounting"></ulink>.
1388 </para>
1389
1390 <para>
1391 Another approach, which can be used with or without altering
1392 <varname>vm.overcommit_memory</varname>, is to set the process-specific
1393 <firstterm>OOM score adjustment</firstterm> value for the postmaster process to
1394 <literal>-1000</literal>, thereby guaranteeing it will not be targeted by the OOM
1395 killer. The simplest way to do this is to execute
1396 <programlisting>
1397 echo -1000 > /proc/self/oom_score_adj
1398 </programlisting>
1399 in the <productname>PostgreSQL</productname> startup script just before
1400 invoking <filename>postgres</filename>.
1401 Note that this action must be done as root, or it will have no effect;
1402 so a root-owned startup script is the easiest place to do it. If you
1403 do this, you should also set these environment variables in the startup
1404 script before invoking <filename>postgres</filename>:
1405 <programlisting>
1406 export PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
1407 export PG_OOM_ADJUST_VALUE=0
1408 </programlisting>
1409 These settings will cause postmaster child processes to run with the
1410 normal OOM score adjustment of zero, so that the OOM killer can still
1411 target them at need. You could use some other value for
1412 <envar>PG_OOM_ADJUST_VALUE</envar> if you want the child processes to run
1413 with some other OOM score adjustment. (<envar>PG_OOM_ADJUST_VALUE</envar>
1414 can also be omitted, in which case it defaults to zero.) If you do not
1415 set <envar>PG_OOM_ADJUST_FILE</envar>, the child processes will run with the
1416 same OOM score adjustment as the postmaster, which is unwise since the
1417 whole point is to ensure that the postmaster has a preferential setting.
1418 </para>
1419
1420 </sect2>
1421
1422 <sect2 id="linux-huge-pages">
1423 <title>Linux Huge Pages</title>
1424
1425 <para>
1426 Using huge pages reduces overhead when using large contiguous chunks of
1427 memory, as <productname>PostgreSQL</productname> does, particularly when
1428 using large values of <xref linkend="guc-shared-buffers"/>. To use this
1429 feature in <productname>PostgreSQL</productname> you need a kernel
1430 with <varname>CONFIG_HUGETLBFS=y</varname> and
1431 <varname>CONFIG_HUGETLB_PAGE=y</varname>. You will also have to configure
1432 the operating system to provide enough huge pages of the desired size.
1433 The runtime-computed parameter
1434 <xref linkend="guc-shared-memory-size-in-huge-pages"/> reports the number
1435 of huge pages required. This parameter can be viewed before starting the
1436 server with a <command>postgres</command> command like:
1437 <programlisting>
1438 $ <userinput>postgres -D $PGDATA -C shared_memory_size_in_huge_pages</userinput>
1439 3170
1440 $ <userinput>grep ^Hugepagesize /proc/meminfo</userinput>
1441 Hugepagesize: 2048 kB
1442 $ <userinput>ls /sys/kernel/mm/hugepages</userinput>
1443 hugepages-1048576kB hugepages-2048kB
1444 </programlisting>
1445
1446 In this example the default is 2MB, but you can also explicitly request
1447 either 2MB or 1GB with <xref linkend="guc-huge-page-size"/> to adapt
1448 the number of pages calculated by
1449 <varname>shared_memory_size_in_huge_pages</varname>.
1450
1451 While we need at least <literal>3170</literal> huge pages in this example,
1452 a larger setting would be appropriate if other programs on the machine
1453 also need huge pages.
1454 We can set this with:
1455 <programlisting>
1456 # <userinput>sysctl -w vm.nr_hugepages=3170</userinput>
1457 </programlisting>
1458 Don't forget to add this setting to <filename>/etc/sysctl.conf</filename>
1459 so that it is reapplied after reboots. For non-default huge page sizes,
1460 we can instead use:
1461 <programlisting>
1462 # <userinput>echo 3170 > /sys/kernel/mm/hugepages/hugepages-2048kB/nr_hugepages</userinput>
1463 </programlisting>
1464 It is also possible to provide these settings at boot time using
1465 kernel parameters such as <literal>hugepagesz=2M hugepages=3170</literal>.
1466 </para>
1467
1468 <para>
1469 Sometimes the kernel is not able to allocate the desired number of huge
1470 pages immediately due to fragmentation, so it might be necessary
1471 to repeat the command or to reboot. (Immediately after a reboot, most of
1472 the machine's memory should be available to convert into huge pages.)
1473 To verify the huge page allocation situation for a given size, use:
1474 <programlisting>
1475 $ <userinput>cat /sys/kernel/mm/hugepages/hugepages-2048kB/nr_hugepages</userinput>
1476 </programlisting>
1477 </para>
1478
1479 <para>
1480 It may also be necessary to give the database server's operating system
1481 user permission to use huge pages by setting
1482 <varname>vm.hugetlb_shm_group</varname> via <application>sysctl</application>, and/or
1483 give permission to lock memory with <command>ulimit -l</command>.
1484 </para>
1485
1486 <para>
1487 The default behavior for huge pages in
1488 <productname>PostgreSQL</productname> is to use them when possible, with
1489 the system's default huge page size, and
1490 to fall back to normal pages on failure. To enforce the use of huge
1491 pages, you can set <xref linkend="guc-huge-pages"/>
1492 to <literal>on</literal> in <filename>postgresql.conf</filename>.
1493 Note that with this setting <productname>PostgreSQL</productname> will fail to
1494 start if not enough huge pages are available.
1495 </para>
1496
1497 <para>
1498 For a detailed description of the <productname>Linux</productname> huge
1499 pages feature have a look
1500 at <ulink url="https://www.kernel.org/doc/Documentation/vm/hugetlbpage.txt"></ulink>.
1501 </para>
1502
1503 </sect2>
1504 </sect1>
1505
1506
1507 <sect1 id="server-shutdown">
1508 <title>Shutting Down the Server</title>
1509
1510 <indexterm zone="server-shutdown">
1511 <primary>shutdown</primary>
1512 </indexterm>
1513
1514 <para>
1515 There are several ways to shut down the database server.
1516 Under the hood, they all reduce to sending a signal to the supervisor
1517 <command>postgres</command> process.
1518 </para>
1519
1520 <para>
1521 If you are using a pre-packaged version
1522 of <productname>PostgreSQL</productname>, and you used its provisions
1523 for starting the server, then you should also use its provisions for
1524 stopping the server. Consult the package-level documentation for
1525 details.
1526 </para>
1527
1528 <para>
1529 When managing the server directly, you can control the type of shutdown
1530 by sending different signals to the <command>postgres</command>
1531 process:
1532
1533 <variablelist>
1534 <varlistentry>
1535 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</primary></indexterm></term>
1536 <listitem>
1537 <para>
1538 This is the <firstterm>Smart Shutdown</firstterm> mode.
1539 After receiving <systemitem>SIGTERM</systemitem>, the server
1540 disallows new connections, but lets existing sessions end their
1541 work normally. It shuts down only after all of the sessions terminate.
1542 If the server is in recovery when a smart
1543 shutdown is requested, recovery and streaming replication will be
1544 stopped only after all regular sessions have terminated.
1545 </para>
1546 </listitem>
1547 </varlistentry>
1548
1549 <varlistentry>
1550 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</primary></indexterm></term>
1551 <listitem>
1552 <para>
1553 This is the <firstterm>Fast Shutdown</firstterm> mode.
1554 The server disallows new connections and sends all existing
1555 server processes <systemitem>SIGTERM</systemitem>, which will cause them
1556 to abort their current transactions and exit promptly. It then
1557 waits for all server processes to exit and finally shuts down.
1558 </para>
1559 </listitem>
1560 </varlistentry>
1561
1562 <varlistentry>
1563 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</primary></indexterm></term>
1564 <listitem>
1565 <para>
1566 This is the <firstterm>Immediate Shutdown</firstterm> mode.
1567 The server will send <systemitem>SIGQUIT</systemitem> to all child
1568 processes and wait for them to terminate. If any do not terminate
1569 within 5 seconds, they will be sent <systemitem>SIGKILL</systemitem>.
1570 The supervisor server process exits as soon as all child processes have
1571 exited, without doing normal database shutdown processing.
1572 This will lead to recovery (by
1573 replaying the WAL log) upon next start-up. This is recommended
1574 only in emergencies.
1575 </para>
1576 </listitem>
1577 </varlistentry>
1578 </variablelist>
1579 </para>
1580
1581 <para>
1582 The <xref linkend="app-pg-ctl"/> program provides a convenient
1583 interface for sending these signals to shut down the server.
1584 Alternatively, you can send the signal directly using <command>kill</command>
1585 on non-Windows systems.
1586 The <acronym>PID</acronym> of the <command>postgres</command> process can be
1587 found using the <command>ps</command> program, or from the file
1588 <filename>postmaster.pid</filename> in the data directory. For
1589 example, to do a fast shutdown:
1590 <screen>
1591 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
1592 </screen>
1593 </para>
1594
1595 <important>
1596 <para>
1597 It is best not to use <systemitem>SIGKILL</systemitem> to shut down the
1598 server. Doing so will prevent the server from releasing shared memory and
1599 semaphores. Furthermore, <systemitem>SIGKILL</systemitem> kills
1600 the <command>postgres</command> process without letting it relay the
1601 signal to its subprocesses, so it might be necessary to kill the
1602 individual subprocesses by hand as well.
1603 </para>
1604 </important>
1605
1606 <para>
1607 To terminate an individual session while allowing other sessions to
1608 continue, use <function>pg_terminate_backend()</function> (see <xref
1609 linkend="functions-admin-signal-table"/>) or send a
1610 <systemitem>SIGTERM</systemitem> signal to the child process associated with
1611 the session.
1612 </para>
1613 </sect1>
1614
1615 <sect1 id="upgrading">
1616 <title>Upgrading a <productname>PostgreSQL</productname> Cluster</title>
1617
1618 <indexterm zone="upgrading">
1619 <primary>upgrading</primary>
1620 </indexterm>
1621
1622 <indexterm zone="upgrading">
1623 <primary>version</primary>
1624 <secondary>compatibility</secondary>
1625 </indexterm>
1626
1627 <para>
1628 This section discusses how to upgrade your database data from one
1629 <productname>PostgreSQL</productname> release to a newer one.
1630 </para>
1631
1632 <para>
1633 Current <productname>PostgreSQL</productname> version numbers consist of a
1634 major and a minor version number. For example, in the version number 10.1,
1635 the 10 is the major version number and the 1 is the minor version number,
1636 meaning this would be the first minor release of the major release 10. For
1637 releases before <productname>PostgreSQL</productname> version 10.0, version
1638 numbers consist of three numbers, for example, 9.5.3. In those cases, the
1639 major version consists of the first two digit groups of the version number,
1640 e.g., 9.5, and the minor version is the third number, e.g., 3, meaning this
1641 would be the third minor release of the major release 9.5.
1642 </para>
1643
1644 <para>
1645 Minor releases never change the internal storage format and are always
1646 compatible with earlier and later minor releases of the same major version
1647 number. For example, version 10.1 is compatible with version 10.0 and
1648 version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0,
1649 9.5.1, and 9.5.6. To update between compatible versions, you simply
1650 replace the executables while the server is down and restart the server.
1651 The data directory remains unchanged &mdash; minor upgrades are that
1652 simple.
1653 </para>
1654
1655 <para>
1656 For <emphasis>major</emphasis> releases of <productname>PostgreSQL</productname>, the
1657 internal data storage format is subject to change, thus complicating
1658 upgrades. The traditional method for moving data to a new major version
1659 is to dump and restore the database, though this can be slow. A
1660 faster method is <xref linkend="pgupgrade"/>. Replication methods are
1661 also available, as discussed below.
1662 (If you are using a pre-packaged version
1663 of <productname>PostgreSQL</productname>, it may provide scripts to
1664 assist with major version upgrades. Consult the package-level
1665 documentation for details.)
1666 </para>
1667
1668 <para>
1669 New major versions also typically introduce some user-visible
1670 incompatibilities, so application programming changes might be required.
1671 All user-visible changes are listed in the release notes (<xref
1672 linkend="release"/>); pay particular attention to the section
1673 labeled "Migration". Though you can upgrade from one major version
1674 to another without upgrading to intervening versions, you should read
1675 the major release notes of all intervening versions.
1676 </para>
1677
1678 <para>
1679 Cautious users will want to test their client applications on the new
1680 version before switching over fully; therefore, it's often a good idea to
1681 set up concurrent installations of old and new versions. When
1682 testing a <productname>PostgreSQL</productname> major upgrade, consider the
1683 following categories of possible changes:
1684 </para>
1685
1686 <variablelist>
1687
1688 <varlistentry>
1689 <term>Administration</term>
1690 <listitem>
1691 <para>
1692 The capabilities available for administrators to monitor and control
1693 the server often change and improve in each major release.
1694 </para>
1695 </listitem>
1696 </varlistentry>
1697
1698 <varlistentry>
1699 <term>SQL</term>
1700 <listitem>
1701 <para>
1702 Typically this includes new SQL command capabilities and not changes
1703 in behavior, unless specifically mentioned in the release notes.
1704 </para>
1705 </listitem>
1706 </varlistentry>
1707
1708 <varlistentry>
1709 <term>Library API</term>
1710 <listitem>
1711 <para>
1712 Typically libraries like <application>libpq</application> only add new
1713 functionality, again unless mentioned in the release notes.
1714 </para>
1715 </listitem>
1716 </varlistentry>
1717
1718 <varlistentry>
1719 <term>System Catalogs</term>
1720 <listitem>
1721 <para>
1722 System catalog changes usually only affect database management tools.
1723 </para>
1724 </listitem>
1725 </varlistentry>
1726
1727 <varlistentry>
1728 <term>Server C-language API</term>
1729 <listitem>
1730 <para>
1731 This involves changes in the backend function API, which is written
1732 in the C programming language. Such changes affect code that
1733 references backend functions deep inside the server.
1734 </para>
1735 </listitem>
1736 </varlistentry>
1737
1738 </variablelist>
1739
1740 <sect2 id="upgrading-via-pgdumpall">
1741 <title>Upgrading Data via <application>pg_dumpall</application></title>
1742
1743 <para>
1744 One upgrade method is to dump data from one major version of
1745 <productname>PostgreSQL</productname> and restore it in another &mdash; to do
1746 this, you must use a <emphasis>logical</emphasis> backup tool like
1747 <application>pg_dumpall</application>; file system
1748 level backup methods will not work. (There are checks in place that prevent
1749 you from using a data directory with an incompatible version of
1750 <productname>PostgreSQL</productname>, so no great harm can be done by
1751 trying to start the wrong server version on a data directory.)
1752 </para>
1753
1754 <para>
1755 It is recommended that you use the <application>pg_dump</application> and
1756 <application>pg_dumpall</application> programs from the <emphasis>newer</emphasis>
1757 version of
1758 <productname>PostgreSQL</productname>, to take advantage of enhancements
1759 that might have been made in these programs. Current releases of the
1760 dump programs can read data from any server version back to 9.2.
1761 </para>
1762
1763 <para>
1764 These instructions assume that your existing installation is under the
1765 <filename>/usr/local/pgsql</filename> directory, and that the data area is in
1766 <filename>/usr/local/pgsql/data</filename>. Substitute your paths
1767 appropriately.
1768 </para>
1769
1770 <procedure>
1771 <step>
1772 <para>
1773 If making a backup, make sure that your database is not being updated.
1774 This does not affect the integrity of the backup, but the changed
1775 data would of course not be included. If necessary, edit the
1776 permissions in the file <filename>/usr/local/pgsql/data/pg_hba.conf</filename>
1777 (or equivalent) to disallow access from everyone except you.
1778 See <xref linkend="client-authentication"/> for additional information on
1779 access control.
1780 </para>
1781
1782 <para>
1783 <indexterm>
1784 <primary>pg_dumpall</primary>
1785 <secondary>use during upgrade</secondary>
1786 </indexterm>
1787
1788 To back up your database installation, type:
1789 <screen>
1790 <userinput>pg_dumpall &gt; <replaceable>outputfile</replaceable></userinput>
1791 </screen>
1792 </para>
1793
1794 <para>
1795 To make the backup, you can use the <application>pg_dumpall</application>
1796 command from the version you are currently running; see <xref
1797 linkend="backup-dump-all"/> for more details. For best
1798 results, however, try to use the <application>pg_dumpall</application>
1799 command from <productname>PostgreSQL</productname> &version;,
1800 since this version contains bug fixes and improvements over older
1801 versions. While this advice might seem idiosyncratic since you
1802 haven't installed the new version yet, it is advisable to follow
1803 it if you plan to install the new version in parallel with the
1804 old version. In that case you can complete the installation
1805 normally and transfer the data later. This will also decrease
1806 the downtime.
1807 </para>
1808 </step>
1809
1810 <step>
1811 <para>
1812 Shut down the old server:
1813 <screen>
1814 <userinput>pg_ctl stop</userinput>
1815 </screen>
1816 On systems that have <productname>PostgreSQL</productname> started at boot time,
1817 there is probably a start-up file that will accomplish the same thing. For
1818 example, on a <systemitem class="osname">Red Hat Linux</systemitem> system one
1819 might find that this works:
1820 <screen>
1821 <userinput>/etc/rc.d/init.d/postgresql stop</userinput>
1822 </screen>
1823 See <xref linkend="runtime"/> for details about starting and
1824 stopping the server.
1825 </para>
1826 </step>
1827
1828 <step>
1829 <para>
1830 If restoring from backup, rename or delete the old installation
1831 directory if it is not version-specific. It is a good idea to
1832 rename the directory, rather than
1833 delete it, in case you have trouble and need to revert to it. Keep
1834 in mind the directory might consume significant disk space. To rename
1835 the directory, use a command like this:
1836 <screen>
1837 <userinput>mv /usr/local/pgsql /usr/local/pgsql.old</userinput>
1838 </screen>
1839 (Be sure to move the directory as a single unit so relative paths
1840 remain unchanged.)
1841 </para>
1842 </step>
1843
1844 <step>
1845 <para>
1846 Install the new version of <productname>PostgreSQL</productname> as
1847 outlined in <xref linkend="installation"/>.
1848 </para>
1849 </step>
1850
1851 <step>
1852 <para>
1853 Create a new database cluster if needed. Remember that you must
1854 execute these commands while logged in to the special database user
1855 account (which you already have if you are upgrading).
1856 <programlisting>
1857 <userinput>/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data</userinput>
1858 </programlisting>
1859 </para>
1860 </step>
1861
1862 <step>
1863 <para>
1864 Restore your previous <filename>pg_hba.conf</filename> and any
1865 <filename>postgresql.conf</filename> modifications.
1866 </para>
1867 </step>
1868
1869 <step>
1870 <para>
1871 Start the database server, again using the special database user
1872 account:
1873 <programlisting>
1874 <userinput>/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data</userinput>
1875 </programlisting>
1876 </para>
1877 </step>
1878
1879 <step>
1880 <para>
1881 Finally, restore your data from backup with:
1882 <screen>
1883 <userinput>/usr/local/pgsql/bin/psql -d postgres -f <replaceable>outputfile</replaceable></userinput>
1884 </screen>
1885 using the <emphasis>new</emphasis> <application>psql</application>.
1886 </para>
1887 </step>
1888 </procedure>
1889
1890 <para>
1891 The least downtime can be achieved by installing the new server in
1892 a different directory and running both the old and the new servers
1893 in parallel, on different ports. Then you can use something like:
1894
1895 <programlisting>
1896 pg_dumpall -p 5432 | psql -d postgres -p 5433
1897 </programlisting>
1898 to transfer your data.
1899 </para>
1900
1901 </sect2>
1902
1903 <sect2 id="upgrading-via-pg-upgrade">
1904 <title>Upgrading Data via <application>pg_upgrade</application></title>
1905
1906 <para>
1907 The <xref linkend="pgupgrade"/> module allows an installation to
1908 be migrated in-place from one major <productname>PostgreSQL</productname>
1909 version to another. Upgrades can be performed in minutes,
1910 particularly with <option>--link</option> mode. It requires steps similar to
1911 <application>pg_dumpall</application> above, e.g., starting/stopping the server,
1912 running <application>initdb</application>. The <application>pg_upgrade</application> <link
1913 linkend="pgupgrade">documentation</link> outlines the necessary steps.
1914 </para>
1915
1916 </sect2>
1917
1918 <sect2 id="upgrading-via-replication">
1919 <title>Upgrading Data via Replication</title>
1920
1921 <para>
1922 It is also possible to use logical replication methods to create a standby
1923 server with the updated version of <productname>PostgreSQL</productname>.
1924 This is possible because logical replication supports
1925 replication between different major versions of
1926 <productname>PostgreSQL</productname>. The standby can be on the same computer or
1927 a different computer. Once it has synced up with the primary server
1928 (running the older version of <productname>PostgreSQL</productname>), you can
1929 switch primaries and make the standby the primary and shut down the older
1930 database instance. Such a switch-over results in only several seconds
1931 of downtime for an upgrade.
1932 </para>
1933
1934 <para>
1935 This method of upgrading can be performed using the built-in logical
1936 replication facilities as well as using external logical replication
1937 systems such as <productname>pglogical</productname>,
1938 <productname>Slony</productname>, <productname>Londiste</productname>, and
1939 <productname>Bucardo</productname>.
1940 </para>
1941 </sect2>
1942 </sect1>
1943
1944 <sect1 id="preventing-server-spoofing">
1945 <title>Preventing Server Spoofing</title>
1946
1947 <indexterm zone="preventing-server-spoofing">
1948 <primary>server spoofing</primary>
1949 </indexterm>
1950
1951 <para>
1952 While the server is running, it is not possible for a malicious user
1953 to take the place of the normal database server. However, when the
1954 server is down, it is possible for a local user to spoof the normal
1955 server by starting their own server. The spoof server could read
1956 passwords and queries sent by clients, but could not return any data
1957 because the <varname>PGDATA</varname> directory would still be secure because
1958 of directory permissions. Spoofing is possible because any user can
1959 start a database server; a client cannot identify an invalid server
1960 unless it is specially configured.
1961 </para>
1962
1963 <para>
1964 One way to prevent spoofing of <literal>local</literal>
1965 connections is to use a Unix domain socket directory (<xref
1966 linkend="guc-unix-socket-directories"/>) that has write permission only
1967 for a trusted local user. This prevents a malicious user from creating
1968 their own socket file in that directory. If you are concerned that
1969 some applications might still reference <filename>/tmp</filename> for the
1970 socket file and hence be vulnerable to spoofing, during operating system
1971 startup create a symbolic link <filename>/tmp/.s.PGSQL.5432</filename> that points
1972 to the relocated socket file. You also might need to modify your
1973 <filename>/tmp</filename> cleanup script to prevent removal of the symbolic link.
1974 </para>
1975
1976 <para>
1977 Another option for <literal>local</literal> connections is for clients to use
1978 <link linkend="libpq-connect-requirepeer"><literal>requirepeer</literal></link>
1979 to specify the required owner of the server process connected to
1980 the socket.
1981 </para>
1982
1983 <para>
1984 To prevent spoofing on TCP connections, either use
1985 SSL certificates and make sure that clients check the server's certificate,
1986 or use GSSAPI encryption (or both, if they're on separate connections).
1987 </para>
1988
1989 <para>
1990 To prevent spoofing with SSL, the server
1991 must be configured to accept only <literal>hostssl</literal> connections (<xref
1992 linkend="auth-pg-hba-conf"/>) and have SSL key and certificate files
1993 (<xref linkend="ssl-tcp"/>). The TCP client must connect using
1994 <literal>sslmode=verify-ca</literal> or
1995 <literal>verify-full</literal> and have the appropriate root certificate
1996 file installed (<xref linkend="libq-ssl-certificates"/>). Alternatively the
1997 <link linkend="libpq-connect-sslrootcert">system CA pool</link>, as defined
1998 by the SSL implementation, can be used using <literal>sslrootcert=system</literal>; in
1999 this case, <literal>sslmode=verify-full</literal> is forced for safety, since
2000 it is generally trivial to obtain certificates which are signed by a public
2001 CA.
2002 </para>
2003
2004 <para>
2005 To prevent server spoofing from occurring when using
2006 <link linkend="auth-password">scram-sha-256</link> password authentication
2007 over a network, you should ensure that you connect to the server using SSL
2008 and with one of the anti-spoofing methods described in the previous
2009 paragraph. Additionally, the SCRAM implementation in
2010 <application>libpq</application> cannot protect the entire authentication
2011 exchange, but using the <literal>channel_binding=require</literal> connection
2012 parameter provides a mitigation against server spoofing. An attacker that
2013 uses a rogue server to intercept a SCRAM exchange can use offline analysis to
2014 potentially determine the hashed password from the client.
2015 </para>
2016
2017 <para>
2018 To prevent spoofing with GSSAPI, the server must be configured to accept
2019 only <literal>hostgssenc</literal> connections
2020 (<xref linkend="auth-pg-hba-conf"/>) and use <literal>gss</literal>
2021 authentication with them. The TCP client must connect
2022 using <literal>gssencmode=require</literal>.
2023 </para>
2024 </sect1>
2025
2026 <sect1 id="encryption-options">
2027 <title>Encryption Options</title>
2028
2029 <indexterm zone="encryption-options">
2030 <primary>encryption</primary>
2031 </indexterm>
2032
2033 <para>
2034 <productname>PostgreSQL</productname> offers encryption at several
2035 levels, and provides flexibility in protecting data from disclosure
2036 due to database server theft, unscrupulous administrators, and
2037 insecure networks. Encryption might also be required to secure
2038 sensitive data such as medical records or financial transactions.
2039 </para>
2040
2041 <variablelist>
2042
2043 <varlistentry>
2044 <term>Password Encryption</term>
2045 <listitem>
2046
2047 <para>
2048 Database user passwords are stored as hashes (determined by the setting
2049 <xref linkend="guc-password-encryption"/>), so the administrator cannot
2050 determine the actual password assigned to the user. If SCRAM or MD5
2051 encryption is used for client authentication, the unencrypted password is
2052 never even temporarily present on the server because the client encrypts
2053 it before being sent across the network. SCRAM is preferred, because it
2054 is an Internet standard and is more secure than the PostgreSQL-specific
2055 MD5 authentication protocol.
2056 </para>
2057
2058 <warning>
2059 <para>
2060 Support for MD5-encrypted passwords is deprecated and will be removed in
2061 a future release of <productname>PostgreSQL</productname>. Refer to
2062 <xref linkend="auth-password"/> for details about migrating to another
2063 password type.
2064 </para>
2065 </warning>
2066
2067 </listitem>
2068 </varlistentry>
2069
2070 <varlistentry>
2071 <term>Encryption For Specific Columns</term>
2072
2073 <listitem>
2074 <para>
2075 The <xref linkend="pgcrypto"/> module allows certain fields to be
2076 stored encrypted.
2077 This is useful if only some of the data is sensitive.
2078 The client supplies the decryption key and the data is decrypted
2079 on the server and then sent to the client.
2080 </para>
2081
2082 <para>
2083 The decrypted data and the decryption key are present on the
2084 server for a brief time while it is being decrypted and
2085 communicated between the client and server. This presents a brief
2086 moment where the data and keys can be intercepted by someone with
2087 complete access to the database server, such as the system
2088 administrator.
2089 </para>
2090 </listitem>
2091 </varlistentry>
2092
2093 <varlistentry>
2094 <term>Data Partition Encryption</term>
2095
2096 <listitem>
2097 <para>
2098 Storage encryption can be performed at the file system level or the
2099 block level. Linux file system encryption options include eCryptfs
2100 and EncFS, while FreeBSD uses PEFS. Block level or full disk
2101 encryption options include dm-crypt + LUKS on Linux and GEOM
2102 modules geli and gbde on FreeBSD. Many other operating systems
2103 support this functionality, including Windows.
2104 </para>
2105
2106 <para>
2107 This mechanism prevents unencrypted data from being read from the
2108 drives if the drives or the entire computer is stolen. This does
2109 not protect against attacks while the file system is mounted,
2110 because when mounted, the operating system provides an unencrypted
2111 view of the data. However, to mount the file system, you need some
2112 way for the encryption key to be passed to the operating system,
2113 and sometimes the key is stored somewhere on the host that mounts
2114 the disk.
2115 </para>
2116 </listitem>
2117 </varlistentry>
2118
2119 <varlistentry>
2120 <term>Encrypting Data Across A Network</term>
2121
2122 <listitem>
2123 <para>
2124 SSL connections encrypt all data sent across the network: the
2125 password, the queries, and the data returned. The
2126 <filename>pg_hba.conf</filename> file allows administrators to specify
2127 which hosts can use non-encrypted connections (<literal>host</literal>)
2128 and which require SSL-encrypted connections
2129 (<literal>hostssl</literal>). Also, clients can specify that they
2130 connect to servers only via SSL.
2131 </para>
2132
2133 <para>
2134 GSSAPI-encrypted connections encrypt all data sent across the network,
2135 including queries and data returned. (No password is sent across the
2136 network.) The <filename>pg_hba.conf</filename> file allows
2137 administrators to specify which hosts can use non-encrypted connections
2138 (<literal>host</literal>) and which require GSSAPI-encrypted connections
2139 (<literal>hostgssenc</literal>). Also, clients can specify that they
2140 connect to servers only on GSSAPI-encrypted connections
2141 (<literal>gssencmode=require</literal>).
2142 </para>
2143
2144 <para>
2145 <application>Stunnel</application> or
2146 <application>SSH</application> can also be used to encrypt
2147 transmissions.
2148 </para>
2149 </listitem>
2150 </varlistentry>
2151
2152 <varlistentry>
2153 <term>SSL Host Authentication</term>
2154
2155 <listitem>
2156 <para>
2157 It is possible for both the client and server to provide SSL
2158 certificates to each other. It takes some extra configuration
2159 on each side, but this provides stronger verification of identity
2160 than the mere use of passwords. It prevents a computer from
2161 pretending to be the server just long enough to read the password
2162 sent by the client. It also helps prevent <quote>man in the middle</quote>
2163 attacks where a computer between the client and server pretends to
2164 be the server and reads and passes all data between the client and
2165 server.
2166 </para>
2167 </listitem>
2168 </varlistentry>
2169
2170 <varlistentry>
2171 <term>Client-Side Encryption</term>
2172
2173 <listitem>
2174 <para>
2175 If the system administrator for the server's machine cannot be trusted,
2176 it is necessary
2177 for the client to encrypt the data; this way, unencrypted data
2178 never appears on the database server. Data is encrypted on the
2179 client before being sent to the server, and database results have
2180 to be decrypted on the client before being used.
2181 </para>
2182 </listitem>
2183 </varlistentry>
2184
2185 </variablelist>
2186
2187 </sect1>
2188
2189 <sect1 id="ssl-tcp">
2190 <title>Secure TCP/IP Connections with SSL</title>
2191
2192 <indexterm zone="ssl-tcp">
2193 <primary>SSL</primary>
2194 <secondary>TLS</secondary>
2195 </indexterm>
2196
2197 <para>
2198 <productname>PostgreSQL</productname> has native support for using
2199 <acronym>SSL</acronym> connections to encrypt client/server communications
2200 for increased security. This requires that
2201 <productname>OpenSSL</productname> is installed on both client and
2202 server systems and that support in <productname>PostgreSQL</productname> is
2203 enabled at build time (see <xref linkend="installation"/>).
2204 </para>
2205
2206 <para>
2207 The terms <acronym>SSL</acronym> and <acronym>TLS</acronym> are often used
2208 interchangeably to mean a secure encrypted connection using a
2209 <acronym>TLS</acronym> protocol. <acronym>SSL</acronym> protocols are the
2210 precursors to <acronym>TLS</acronym> protocols, and the term
2211 <acronym>SSL</acronym> is still used for encrypted connections even though
2212 <acronym>SSL</acronym> protocols are no longer supported.
2213 <acronym>SSL</acronym> is used interchangeably with <acronym>TLS</acronym>
2214 in <productname>PostgreSQL</productname>.
2215
2216 </para>
2217 <sect2 id="ssl-setup">
2218 <title>Basic Setup</title>
2219
2220 <para>
2221 With <acronym>SSL</acronym> support compiled in, the
2222 <productname>PostgreSQL</productname> server can be started with
2223 support for encrypted connections using <acronym>TLS</acronym> protocols
2224 enabled by setting the parameter
2225 <xref linkend="guc-ssl"/> to <literal>on</literal> in
2226 <filename>postgresql.conf</filename>. The server will listen for both normal
2227 and <acronym>SSL</acronym> connections on the same TCP port, and will negotiate
2228 with any connecting client on whether to use <acronym>SSL</acronym>. By
2229 default, this is at the client's option; see <xref
2230 linkend="auth-pg-hba-conf"/> about how to set up the server to require
2231 use of <acronym>SSL</acronym> for some or all connections.
2232 </para>
2233
2234 <para>
2235 To start in <acronym>SSL</acronym> mode, files containing the server certificate
2236 and private key must exist. By default, these files are expected to be
2237 named <filename>server.crt</filename> and <filename>server.key</filename>, respectively, in
2238 the server's data directory, but other names and locations can be specified
2239 using the configuration parameters <xref linkend="guc-ssl-cert-file"/>
2240 and <xref linkend="guc-ssl-key-file"/>.
2241 </para>
2242
2243 <para>
2244 On Unix systems, the permissions on <filename>server.key</filename> must
2245 disallow any access to world or group; achieve this by the command
2246 <command>chmod 0600 server.key</command>. Alternatively, the file can be
2247 owned by root and have group read access (that is, <literal>0640</literal>
2248 permissions). That setup is intended for installations where certificate
2249 and key files are managed by the operating system. The user under which
2250 the <productname>PostgreSQL</productname> server runs should then be made a
2251 member of the group that has access to those certificate and key files.
2252 </para>
2253
2254 <para>
2255 If the data directory allows group read access then certificate files may
2256 need to be located outside of the data directory in order to conform to the
2257 security requirements outlined above. Generally, group access is enabled
2258 to allow an unprivileged user to backup the database, and in that case the
2259 backup software will not be able to read the certificate files and will
2260 likely error.
2261 </para>
2262
2263 <para>
2264 If the private key is protected with a passphrase, the
2265 server will prompt for the passphrase and will not start until it has
2266 been entered.
2267 Using a passphrase by default disables the ability to change the server's
2268 SSL configuration without a server restart, but see <xref
2269 linkend="guc-ssl-passphrase-command-supports-reload"/>.
2270 Furthermore, passphrase-protected private keys cannot be used at all
2271 on Windows.
2272 </para>
2273
2274 <para>
2275 The first certificate in <filename>server.crt</filename> must be the
2276 server's certificate because it must match the server's private key.
2277 The certificates of <quote>intermediate</quote> certificate authorities
2278 can also be appended to the file. Doing this avoids the necessity of
2279 storing intermediate certificates on clients, assuming the root and
2280 intermediate certificates were created with <literal>v3_ca</literal>
2281 extensions. (This sets the certificate's basic constraint of
2282 <literal>CA</literal> to <literal>true</literal>.)
2283 This allows easier expiration of intermediate certificates.
2284 </para>
2285
2286 <para>
2287 It is not necessary to add the root certificate to
2288 <filename>server.crt</filename>. Instead, clients must have the root
2289 certificate of the server's certificate chain.
2290 </para>
2291 </sect2>
2292
2293 <sect2 id="ssl-openssl-config">
2294 <title>OpenSSL Configuration</title>
2295
2296 <para>
2297 <productname>PostgreSQL</productname> reads the system-wide
2298 <productname>OpenSSL</productname> configuration file. By default, this
2299 file is named <filename>openssl.cnf</filename> and is located in the
2300 directory reported by <literal>openssl version -d</literal>.
2301 This default can be overridden by setting environment variable
2302 <envar>OPENSSL_CONF</envar> to the name of the desired configuration file.
2303 </para>
2304
2305 <para>
2306 <productname>OpenSSL</productname> supports a wide range of ciphers
2307 and authentication algorithms, of varying strength. While a list of
2308 ciphers can be specified in the <productname>OpenSSL</productname>
2309 configuration file, you can specify ciphers specifically for use by
2310 the database server by modifying <xref linkend="guc-ssl-ciphers"/> in
2311 <filename>postgresql.conf</filename>.
2312 </para>
2313
2314 <note>
2315 <para>
2316 It is possible to have authentication without encryption overhead by
2317 using <literal>NULL-SHA</literal> or <literal>NULL-MD5</literal> ciphers. However,
2318 a man-in-the-middle could read and pass communications between client
2319 and server. Also, encryption overhead is minimal compared to the
2320 overhead of authentication. For these reasons NULL ciphers are not
2321 recommended.
2322 </para>
2323 </note>
2324 </sect2>
2325
2326 <sect2 id="ssl-client-certificates">
2327 <title>Using Client Certificates</title>
2328
2329 <para>
2330 To require the client to supply a trusted certificate,
2331 place certificates of the root certificate authorities
2332 (<acronym>CA</acronym>s) you trust in a file in the data
2333 directory, set the parameter <xref linkend="guc-ssl-ca-file"/> in
2334 <filename>postgresql.conf</filename> to the new file name, and add the
2335 authentication option <literal>clientcert=verify-ca</literal> or
2336 <literal>clientcert=verify-full</literal> to the appropriate
2337 <literal>hostssl</literal> line(s) in <filename>pg_hba.conf</filename>.
2338 A certificate will then be requested from the client during SSL
2339 connection startup. (See <xref linkend="libpq-ssl"/> for a description
2340 of how to set up certificates on the client.)
2341 </para>
2342
2343 <para>
2344 For a <literal>hostssl</literal> entry with
2345 <literal>clientcert=verify-ca</literal>, the server will verify
2346 that the client's certificate is signed by one of the trusted
2347 certificate authorities. If <literal>clientcert=verify-full</literal>
2348 is specified, the server will not only verify the certificate
2349 chain, but it will also check whether the username or its mapping
2350 matches the <literal>cn</literal> (Common Name) of the provided certificate.
2351 Note that certificate chain validation is always ensured when the
2352 <literal>cert</literal> authentication method is used
2353 (see <xref linkend="auth-cert"/>).
2354 </para>
2355
2356 <para>
2357 Intermediate certificates that chain up to existing root certificates
2358 can also appear in the <xref linkend="guc-ssl-ca-file"/> file if
2359 you wish to avoid storing them on clients (assuming the root and
2360 intermediate certificates were created with <literal>v3_ca</literal>
2361 extensions). Certificate Revocation List (CRL) entries are also
2362 checked if the parameter <xref linkend="guc-ssl-crl-file"/> or
2363 <xref linkend="guc-ssl-crl-dir"/> is set.
2364 </para>
2365
2366 <para>
2367 The <literal>clientcert</literal> authentication option is available for
2368 all authentication methods, but only in <filename>pg_hba.conf</filename> lines
2369 specified as <literal>hostssl</literal>. When <literal>clientcert</literal> is
2370 not specified, the server verifies the client certificate against its CA
2371 file only if a client certificate is presented and the CA is configured.
2372 </para>
2373
2374 <para>
2375 There are two approaches to enforce that users provide a certificate during login.
2376 </para>
2377
2378 <para>
2379 The first approach makes use of the <literal>cert</literal> authentication
2380 method for <literal>hostssl</literal> entries in <filename>pg_hba.conf</filename>,
2381 such that the certificate itself is used for authentication while also
2382 providing ssl connection security. See <xref linkend="auth-cert"/> for details.
2383 (It is not necessary to specify any <literal>clientcert</literal> options
2384 explicitly when using the <literal>cert</literal> authentication method.)
2385 In this case, the <literal>cn</literal> (Common Name) provided in
2386 the certificate is checked against the user name or an applicable mapping.
2387 </para>
2388
2389 <para>
2390 The second approach combines any authentication method for <literal>hostssl</literal>
2391 entries with the verification of client certificates by setting the
2392 <literal>clientcert</literal> authentication option to <literal>verify-ca</literal>
2393 or <literal>verify-full</literal>. The former option only enforces that
2394 the certificate is valid, while the latter also ensures that the
2395 <literal>cn</literal> (Common Name) in the certificate matches
2396 the user name or an applicable mapping.
2397 </para>
2398 </sect2>
2399
2400 <sect2 id="ssl-server-files">
2401 <title>SSL Server File Usage</title>
2402
2403 <para>
2404 <xref linkend="ssl-file-usage"/> summarizes the files that are
2405 relevant to the SSL setup on the server. (The shown file names are default
2406 names. The locally configured names could be different.)
2407 </para>
2408
2409 <table id="ssl-file-usage">
2410 <title>SSL Server File Usage</title>
2411 <tgroup cols="3">
2412 <thead>
2413 <row>
2414 <entry>File</entry>
2415 <entry>Contents</entry>
2416 <entry>Effect</entry>
2417 </row>
2418 </thead>
2419
2420 <tbody>
2421
2422 <row>
2423 <entry><xref linkend="guc-ssl-cert-file"/> (<filename>$PGDATA/server.crt</filename>)</entry>
2424 <entry>server certificate</entry>
2425 <entry>sent to client to indicate server's identity</entry>
2426 </row>
2427
2428 <row>
2429 <entry><xref linkend="guc-ssl-key-file"/> (<filename>$PGDATA/server.key</filename>)</entry>
2430 <entry>server private key</entry>
2431 <entry>proves server certificate was sent by the owner; does not indicate
2432 certificate owner is trustworthy</entry>
2433 </row>
2434
2435 <row>
2436 <entry><xref linkend="guc-ssl-ca-file"/></entry>
2437 <entry>trusted certificate authorities</entry>
2438 <entry>checks that client certificate is
2439 signed by a trusted certificate authority</entry>
2440 </row>
2441
2442 <row>
2443 <entry><xref linkend="guc-ssl-crl-file"/></entry>
2444 <entry>certificates revoked by certificate authorities</entry>
2445 <entry>client certificate must not be on this list</entry>
2446 </row>
2447
2448 </tbody>
2449 </tgroup>
2450 </table>
2451
2452 <para>
2453 The server reads these files at server start and whenever the server
2454 configuration is reloaded. On <systemitem class="osname">Windows</systemitem>
2455 systems, they are also re-read whenever a new backend process is spawned
2456 for a new client connection.
2457 </para>
2458
2459 <para>
2460 If an error in these files is detected at server start, the server will
2461 refuse to start. But if an error is detected during a configuration
2462 reload, the files are ignored and the old SSL configuration continues to
2463 be used. On <systemitem class="osname">Windows</systemitem> systems, if an error in
2464 these files is detected at backend start, that backend will be unable to
2465 establish an SSL connection. In all these cases, the error condition is
2466 reported in the server log.
2467 </para>
2468 </sect2>
2469
2470 <sect2 id="ssl-certificate-creation">
2471 <title>Creating Certificates</title>
2472
2473 <para>
2474 To create a simple self-signed certificate for the server, valid for 365
2475 days, use the following <productname>OpenSSL</productname> command,
2476 replacing <replaceable>dbhost.yourdomain.com</replaceable> with the
2477 server's host name:
2478 <programlisting>
2479 openssl req -new -x509 -days 365 -nodes -text -out server.crt \
2480 -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
2481 </programlisting>
2482 Then do:
2483 <programlisting>
2484 chmod og-rwx server.key
2485 </programlisting>
2486 because the server will reject the file if its permissions are more
2487 liberal than this.
2488 For more details on how to create your server private key and
2489 certificate, refer to the <productname>OpenSSL</productname> documentation.
2490 </para>
2491
2492 <para>
2493 While a self-signed certificate can be used for testing, a certificate
2494 signed by a certificate authority (<acronym>CA</acronym>) (usually an
2495 enterprise-wide root <acronym>CA</acronym>) should be used in production.
2496 </para>
2497
2498 <para>
2499 To create a server certificate whose identity can be validated
2500 by clients, first create a certificate signing request
2501 (<acronym>CSR</acronym>) and a public/private key file:
2502 <programlisting>
2503 openssl req -new -nodes -text -out root.csr \
2504 -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
2505 chmod og-rwx root.key
2506 </programlisting>
2507 Then, sign the request with the key to create a root certificate
2508 authority (using the default <productname>OpenSSL</productname>
2509 configuration file location on <productname>Linux</productname>):
2510 <programlisting>
2511 openssl x509 -req -in root.csr -text -days 3650 \
2512 -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
2513 -signkey root.key -out root.crt
2514 </programlisting>
2515 Finally, create a server certificate signed by the new root certificate
2516 authority:
2517 <programlisting>
2518 openssl req -new -nodes -text -out server.csr \
2519 -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
2520 chmod og-rwx server.key
2521
2522 openssl x509 -req -in server.csr -text -days 365 \
2523 -CA root.crt -CAkey root.key -CAcreateserial \
2524 -out server.crt
2525 </programlisting>
2526 <filename>server.crt</filename> and <filename>server.key</filename>
2527 should be stored on the server, and <filename>root.crt</filename> should
2528 be stored on the client so the client can verify that the server's leaf
2529 certificate was signed by its trusted root certificate.
2530 <filename>root.key</filename> should be stored offline for use in
2531 creating future certificates.
2532 </para>
2533
2534 <para>
2535 It is also possible to create a chain of trust that includes
2536 intermediate certificates:
2537 <programlisting>
2538 # root
2539 openssl req -new -nodes -text -out root.csr \
2540 -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
2541 chmod og-rwx root.key
2542 openssl x509 -req -in root.csr -text -days 3650 \
2543 -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
2544 -signkey root.key -out root.crt
2545
2546 # intermediate
2547 openssl req -new -nodes -text -out intermediate.csr \
2548 -keyout intermediate.key -subj "/CN=<replaceable>intermediate.yourdomain.com</replaceable>"
2549 chmod og-rwx intermediate.key
2550 openssl x509 -req -in intermediate.csr -text -days 1825 \
2551 -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
2552 -CA root.crt -CAkey root.key -CAcreateserial \
2553 -out intermediate.crt
2554
2555 # leaf
2556 openssl req -new -nodes -text -out server.csr \
2557 -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
2558 chmod og-rwx server.key
2559 openssl x509 -req -in server.csr -text -days 365 \
2560 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \
2561 -out server.crt
2562 </programlisting>
2563 <filename>server.crt</filename> and
2564 <filename>intermediate.crt</filename> should be concatenated
2565 into a certificate file bundle and stored on the server.
2566 <filename>server.key</filename> should also be stored on the server.
2567 <filename>root.crt</filename> should be stored on the client so
2568 the client can verify that the server's leaf certificate was signed
2569 by a chain of certificates linked to its trusted root certificate.
2570 <filename>root.key</filename> and <filename>intermediate.key</filename>
2571 should be stored offline for use in creating future certificates.
2572 </para>
2573 </sect2>
2574
2575 </sect1>
2576
2577 <sect1 id="gssapi-enc">
2578 <title>Secure TCP/IP Connections with GSSAPI Encryption</title>
2579
2580 <indexterm zone="gssapi-enc">
2581 <primary>gssapi</primary>
2582 </indexterm>
2583
2584 <para>
2585 <productname>PostgreSQL</productname> also has native support for
2586 using <acronym>GSSAPI</acronym> to encrypt client/server communications for
2587 increased security. Support requires that a <acronym>GSSAPI</acronym>
2588 implementation (such as MIT Kerberos) is installed on both client and server
2589 systems, and that support in <productname>PostgreSQL</productname> is
2590 enabled at build time (see <xref linkend="installation"/>).
2591 </para>
2592
2593 <sect2 id="gssapi-setup">
2594 <title>Basic Setup</title>
2595
2596 <para>
2597 The <productname>PostgreSQL</productname> server will listen for both
2598 normal and <acronym>GSSAPI</acronym>-encrypted connections on the same TCP
2599 port, and will negotiate with any connecting client whether to
2600 use <acronym>GSSAPI</acronym> for encryption (and for authentication). By
2601 default, this decision is up to the client (which means it can be
2602 downgraded by an attacker); see <xref linkend="auth-pg-hba-conf"/> about
2603 setting up the server to require the use of <acronym>GSSAPI</acronym> for
2604 some or all connections.
2605 </para>
2606
2607 <para>
2608 When using <acronym>GSSAPI</acronym> for encryption, it is common to
2609 use <acronym>GSSAPI</acronym> for authentication as well, since the
2610 underlying mechanism will determine both client and server identities
2611 (according to the <acronym>GSSAPI</acronym> implementation) in any
2612 case. But this is not required;
2613 another <productname>PostgreSQL</productname> authentication method
2614 can be chosen to perform additional verification.
2615 </para>
2616
2617 <para>
2618 Other than configuration of the negotiation
2619 behavior, <acronym>GSSAPI</acronym> encryption requires no setup beyond
2620 that which is necessary for GSSAPI authentication. (For more information
2621 on configuring that, see <xref linkend="gssapi-auth"/>.)
2622 </para>
2623 </sect2>
2624 </sect1>
2625
2626 <sect1 id="ssh-tunnels">
2627 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
2628
2629 <indexterm zone="ssh-tunnels">
2630 <primary>ssh</primary>
2631 </indexterm>
2632
2633 <para>
2634 It is possible to use <application>SSH</application> to encrypt the network
2635 connection between clients and a
2636 <productname>PostgreSQL</productname> server. Done properly, this
2637 provides an adequately secure network connection, even for non-SSL-capable
2638 clients.
2639 </para>
2640
2641 <para>
2642 First make sure that an <application>SSH</application> server is
2643 running properly on the same machine as the
2644 <productname>PostgreSQL</productname> server and that you can log in using
2645 <command>ssh</command> as some user; you then can establish a
2646 secure tunnel to the remote server. A secure tunnel listens on a
2647 local port and forwards all traffic to a port on the remote machine.
2648 Traffic sent to the remote port can arrive on its
2649 <literal>localhost</literal> address, or different bind
2650 address if desired; it does not appear as coming from your
2651 local machine. This command creates a secure tunnel from the client
2652 machine to the remote machine <literal>foo.com</literal>:
2653 <programlisting>
2654 ssh -L 63333:localhost:5432 [email protected]
2655 </programlisting>
2656 The first number in the <option>-L</option> argument, 63333, is the
2657 local port number of the tunnel; it can be any unused port. (IANA
2658 reserves ports 49152 through 65535 for private use.) The name or IP
2659 address after this is the remote bind address you are connecting to,
2660 i.e., <literal>localhost</literal>, which is the default. The second
2661 number, 5432, is the remote end of the tunnel, e.g., the port number
2662 your database server is using. In order to connect to the database
2663 server using this tunnel, you connect to port 63333 on the local
2664 machine:
2665 <programlisting>
2666 psql -h localhost -p 63333 postgres
2667 </programlisting>
2668 To the database server it will then look as though you are
2669 user <literal>joe</literal> on host <literal>foo.com</literal>
2670 connecting to the <literal>localhost</literal> bind address, and it
2671 will use whatever authentication procedure was configured for
2672 connections by that user to that bind address. Note that the server will not
2673 think the connection is SSL-encrypted, since in fact it is not
2674 encrypted between the
2675 <application>SSH</application> server and the
2676 <productname>PostgreSQL</productname> server. This should not pose any
2677 extra security risk because they are on the same machine.
2678 </para>
2679
2680 <para>
2681 In order for the
2682 tunnel setup to succeed you must be allowed to connect via
2683 <command>ssh</command> as <literal>[email protected]</literal>, just
2684 as if you had attempted to use <command>ssh</command> to create a
2685 terminal session.
2686 </para>
2687
2688 <para>
2689 You could also have set up port forwarding as
2690 <programlisting>
2691 ssh -L 63333:foo.com:5432 [email protected]
2692 </programlisting>
2693 but then the database server will see the connection as coming in
2694 on its <literal>foo.com</literal> bind address, which is not opened by
2695 the default setting <literal>listen_addresses =
2696 'localhost'</literal>. This is usually not what you want.
2697 </para>
2698
2699 <para>
2700 If you have to <quote>hop</quote> to the database server via some
2701 login host, one possible setup could look like this:
2702 <programlisting>
2703 ssh -L 63333:db.foo.com:5432 [email protected]
2704 </programlisting>
2705 Note that this way the connection
2706 from <literal>shell.foo.com</literal>
2707 to <literal>db.foo.com</literal> will not be encrypted by the SSH
2708 tunnel.
2709 SSH offers quite a few configuration possibilities when the network
2710 is restricted in various ways. Please refer to the SSH
2711 documentation for details.
2712 </para>
2713
2714 <tip>
2715 <para>
2716 Several other applications exist that can provide secure tunnels using
2717 a procedure similar in concept to the one just described.
2718 </para>
2719 </tip>
2720
2721 </sect1>
2722
2723 <sect1 id="event-log-registration">
2724 <title>Registering <application>Event Log</application> on <systemitem
2725 class="osname">Windows</systemitem></title>
2726
2727 <indexterm zone="event-log-registration">
2728 <primary>event log</primary>
2729 <secondary>event log</secondary>
2730 </indexterm>
2731
2732 <para>
2733 To register a <systemitem class="osname">Windows</systemitem>
2734 <application>event log</application> library with the operating system,
2735 issue this command:
2736 <screen>
2737 <userinput>regsvr32 <replaceable>pgsql_library_directory</replaceable>/pgevent.dll</userinput>
2738 </screen>
2739 This creates registry entries used by the event viewer, under the default
2740 event source named <literal>PostgreSQL</literal>.
2741 </para>
2742
2743 <para>
2744 To specify a different event source name (see
2745 <xref linkend="guc-event-source"/>), use the <literal>/n</literal>
2746 and <literal>/i</literal> options:
2747 <screen>
2748 <userinput>regsvr32 /n /i:<replaceable>event_source_name</replaceable> <replaceable>pgsql_library_directory</replaceable>/pgevent.dll</userinput>
2749 </screen>
2750 </para>
2751
2752 <para>
2753 To unregister the <application>event log</application> library from
2754 the operating system, issue this command:
2755 <screen>
2756 <userinput>regsvr32 /u [/i:<replaceable>event_source_name</replaceable>] <replaceable>pgsql_library_directory</replaceable>/pgevent.dll</userinput>
2757 </screen>
2758 </para>
2759
2760 <note>
2761 <para>
2762 To enable event logging in the database server, modify
2763 <xref linkend="guc-log-destination"/> to include
2764 <literal>eventlog</literal> in <filename>postgresql.conf</filename>.
2765 </para>
2766 </note>
2767 </sect1>
2768
2769 </chapter>