1 <!-- doc/src/sgml/runtime.sgml -->
4 <title>Server Setup and Operation</title>
7 This chapter discusses how to set up and run the database server
8 and its interactions with the operating system.
11 <sect1 id="postgres-user">
12 <title>The <productname>PostgreSQL</productname> User Account</title>
15 <primary>postgres user</primary>
19 As with any server daemon that is accessible to the outside world,
20 it is advisable to run <productname>PostgreSQL</productname> under a
21 separate user account. This user account should only own the data
22 that is managed by the server, and should not be shared with other
23 daemons. (For example, using the user <literal>nobody</literal> is a bad
24 idea.) It is not advisable to install executables owned by this
25 user because compromised systems could then modify their own
30 To add a Unix user account to your system, look for a command
31 <command>useradd</command> or <command>adduser</command>. The user
32 name <systemitem>postgres</systemitem> is often used, and is assumed
33 throughout this book, but you can use another name if you like.
37 <sect1 id="creating-cluster">
38 <title>Creating a Database Cluster</title>
41 <primary>database cluster</primary>
45 <primary>data area</primary>
46 <see>database cluster</see>
50 Before you can do anything, you must initialize a database storage
51 area on disk. We call this a <firstterm>database cluster</firstterm>.
52 (<acronym>SQL</acronym> uses the term catalog cluster.) A
53 database cluster is a collection of databases that is managed by a
54 single instance of a running database server. After initialization, a
55 database cluster will contain a database named <literal>postgres</literal>,
56 which is meant as a default database for use by utilities, users and third
57 party applications. The database server itself does not require the
58 <literal>postgres</literal> database to exist, but many external utility
59 programs assume it exists. Another database created within each cluster
60 during initialization is called
61 <literal>template1</literal>. As the name suggests, this will be used
62 as a template for subsequently created databases; it should not be
63 used for actual work. (See <xref linkend="managing-databases"> for
64 information about creating new databases within a cluster.)
68 In file system terms, a database cluster will be a single directory
69 under which all data will be stored. We call this the <firstterm>data
70 directory</firstterm> or <firstterm>data area</firstterm>. It is
71 completely up to you where you choose to store your data. There is no
72 default, although locations such as
73 <filename>/usr/local/pgsql/data</filename> or
74 <filename>/var/lib/pgsql/data</filename> are popular. To initialize a
75 database cluster, use the command <xref
76 linkend="app-initdb">,<indexterm><primary>initdb</></> which is
77 installed with <productname>PostgreSQL</productname>. The desired
78 file system location of your database cluster is indicated by the
79 <option>-D</option> option, for example:
81 <prompt>$</> <userinput>initdb -D /usr/local/pgsql/data</userinput>
83 Note that you must execute this command while logged into the
84 <productname>PostgreSQL</productname> user account, which is
85 described in the previous section.
90 As an alternative to the <option>-D</option> option, you can set
91 the environment variable <envar>PGDATA</envar>.
92 <indexterm><primary><envar>PGDATA</envar></primary></indexterm>
97 Alternatively, you can run <command>initdb</command> via
98 the <xref linkend="app-pg-ctl">
99 program<indexterm><primary>pg_ctl</></> like so:
101 <prompt>$</> <userinput>pg_ctl -D /usr/local/pgsql/data initdb</userinput>
103 This may be more intuitive if you are
104 using <command>pg_ctl</command> for starting and stopping the
105 server (see <xref linkend="server-start">), so
106 that <command>pg_ctl</command> would be the sole command you use
107 for managing the database server instance.
111 <command>initdb</command> will attempt to create the directory you
112 specify if it does not already exist. It is likely that it will not
113 have the permission to do so (if you followed our advice and created
114 an unprivileged account). In that case you should create the
115 directory yourself (as root) and change the owner to be the
116 <productname>PostgreSQL</productname> user. Here is how this might
119 root# <userinput>mkdir /usr/local/pgsql/data</userinput>
120 root# <userinput>chown postgres /usr/local/pgsql/data</userinput>
121 root# <userinput>su postgres</userinput>
122 postgres$ <userinput>initdb -D /usr/local/pgsql/data</userinput>
127 <command>initdb</command> will refuse to run if the data directory
128 looks like it has already been initialized.</para>
131 Because the data directory contains all the data stored in the
132 database, it is essential that it be secured from unauthorized
133 access. <command>initdb</command> therefore revokes access
134 permissions from everyone but the
135 <productname>PostgreSQL</productname> user.
139 However, while the directory contents are secure, the default
140 client authentication setup allows any local user to connect to the
141 database and even become the database superuser. If you do not
142 trust other local users, we recommend you use one of
143 <command>initdb</command>'s <option>-W</option>, <option>--pwprompt</option>
144 or <option>--pwfile</option> options to assign a password to the
145 database superuser.<indexterm>
147 <secondary>of the superuser</>
149 Also, specify <option>-A md5</> or
150 <option>-A password</> so that the default <literal>trust</> authentication
151 mode is not used; or modify the generated <filename>pg_hba.conf</filename>
152 file after running <command>initdb</command>, but
153 <emphasis>before</> you start the server for the first time. (Other
154 reasonable approaches include using <literal>peer</literal> authentication
155 or file system permissions to restrict connections. See <xref
156 linkend="client-authentication"> for more information.)
160 <command>initdb</command> also initializes the default
161 locale<indexterm><primary>locale</></> for the database cluster.
162 Normally, it will just take the locale settings in the environment
163 and apply them to the initialized database. It is possible to
164 specify a different locale for the database; more information about
165 that can be found in <xref linkend="locale">. The default sort order used
166 within the particular database cluster is set by
167 <command>initdb</command>, and while you can create new databases using
168 different sort order, the order used in the template databases that initdb
169 creates cannot be changed without dropping and recreating them.
170 There is also a performance impact for using locales
171 other than <literal>C</> or <literal>POSIX</>. Therefore, it is
172 important to make this choice correctly the first time.
176 <command>initdb</command> also sets the default character set encoding
177 for the database cluster. Normally this should be chosen to match the
178 locale setting. For details see <xref linkend="multibyte">.
181 <sect2 id="creating-cluster-nfs">
182 <title>Network File Systems</title>
184 <indexterm zone="creating-cluster-nfs">
185 <primary>Network File Systems</primary>
187 <indexterm><primary><acronym>NFS</></><see>Network File Systems</></>
188 <indexterm><primary>Network Attached Storage (<acronym>NAS</>)</><see>Network File Systems</></>
191 Many installations create database clusters on network file systems.
192 Sometimes this is done directly via <acronym>NFS</>, or by using a
193 Network Attached Storage (<acronym>NAS</>) device that uses
194 <acronym>NFS</> internally. <productname>PostgreSQL</> does nothing
195 special for <acronym>NFS</> file systems, meaning it assumes
196 <acronym>NFS</> behaves exactly like locally-connected drives
197 (<acronym>DAS</>, Direct Attached Storage). If client and server
198 <acronym>NFS</> implementations have non-standard semantics, this can
199 cause reliability problems (see <ulink
200 url="http://www.time-travellers.org/shane/papers/NFS_considered_harmful.html"></ulink>).
201 Specifically, delayed (asynchronous) writes to the <acronym>NFS</>
202 server can cause reliability problems; if possible, mount
203 <acronym>NFS</> file systems synchronously (without caching) to avoid
204 this. Also, soft-mounting <acronym>NFS</> is not recommended.
205 (Storage Area Networks (<acronym>SAN</>) use a low-level
206 communication protocol rather than <acronym>NFS</>.)
213 <sect1 id="server-start">
214 <title>Starting the Database Server</title>
217 Before anyone can access the database, you must start the database
218 server. The database server program is called
219 <command>postgres</command>.<indexterm><primary>postgres</></>
220 The <command>postgres</command> program must know where to
221 find the data it is supposed to use. This is done with the
222 <option>-D</option> option. Thus, the simplest way to start the
225 $ <userinput>postgres -D /usr/local/pgsql/data</userinput>
227 which will leave the server running in the foreground. This must be
228 done while logged into the <productname>PostgreSQL</productname> user
229 account. Without <option>-D</option>, the server will try to use
230 the data directory named by the environment variable <envar>PGDATA</envar>.
231 If that variable is not provided either, it will fail.
235 Normally it is better to start <command>postgres</command> in the
236 background. For this, use the usual Unix shell syntax:
238 $ <userinput>postgres -D /usr/local/pgsql/data >logfile 2>&1 &</userinput>
240 It is important to store the server's <systemitem>stdout</> and
241 <systemitem>stderr</> output somewhere, as shown above. It will help
242 for auditing purposes and to diagnose problems. (See <xref
243 linkend="logfile-maintenance"> for a more thorough discussion of log
248 The <command>postgres</command> program also takes a number of other
249 command-line options. For more information, see the
250 <xref linkend="app-postgres"> reference page
251 and <xref linkend="runtime-config"> below.
255 This shell syntax can get tedious quickly. Therefore the wrapper
257 <xref linkend="app-pg-ctl"><indexterm><primary>pg_ctl</primary></indexterm>
258 is provided to simplify some tasks. For example:
260 pg_ctl start -l logfile
262 will start the server in the background and put the output into the
263 named log file. The <option>-D</option> option has the same meaning
264 here as for <command>postgres</command>. <command>pg_ctl</command>
265 is also capable of stopping the server.
269 Normally, you will want to start the database server when the
270 computer boots.<indexterm>
272 <secondary>starting the server during</>
274 Autostart scripts are operating-system-specific.
275 There are a few distributed with
276 <productname>PostgreSQL</productname> in the
277 <filename>contrib/start-scripts</> directory. Installing one will require
282 Different systems have different conventions for starting up daemons
283 at boot time. Many systems have a file
284 <filename>/etc/rc.local</filename> or
285 <filename>/etc/rc.d/rc.local</filename>. Others use
286 <filename>rc.d</> directories. Whatever you do, the server must be
287 run by the <productname>PostgreSQL</productname> user account
288 <emphasis>and not by root</emphasis> or any other user. Therefore you
289 probably should form your commands using <literal>su -c '...'
290 postgres</literal>. For example:
292 su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
297 Here are a few more operating-system-specific suggestions. (In each
298 case be sure to use the proper installation directory and user
299 name where we show generic values.)
304 For <productname>FreeBSD</productname>, look at the file
305 <filename>contrib/start-scripts/freebsd</filename> in the
306 <productname>PostgreSQL</productname> source distribution.
307 <indexterm><primary>FreeBSD</><secondary>start script</secondary></>
313 On <productname>OpenBSD</productname>, add the following lines
314 to the file <filename>/etc/rc.local</filename>:
315 <indexterm><primary>OpenBSD</><secondary>start script</secondary></>
317 if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
318 su - -c '/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s' postgres
319 echo -n ' postgresql'
327 On <productname>Linux</productname> systems either add
328 <indexterm><primary>Linux</><secondary>start script</secondary></>
330 /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
332 to <filename>/etc/rc.d/rc.local</filename> or look at the file
333 <filename>contrib/start-scripts/linux</filename> in the
334 <productname>PostgreSQL</productname> source distribution.
340 On <productname>NetBSD</productname>, either use the
341 <productname>FreeBSD</productname> or
342 <productname>Linux</productname> start scripts, depending on
343 preference. <indexterm><primary>NetBSD</><secondary>start script</secondary></>
349 On <productname>Solaris</productname>, create a file called
350 <filename>/etc/init.d/postgresql</filename> that contains
352 <indexterm><primary>Solaris</><secondary>start script</secondary></>
354 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
356 Then, create a symbolic link to it in <filename>/etc/rc3.d</> as
357 <filename>S99postgresql</>.
365 While the server is running, its
366 <acronym>PID</acronym> is stored in the file
367 <filename>postmaster.pid</filename> in the data directory. This is
368 used to prevent multiple server instances from
369 running in the same data directory and can also be used for
370 shutting down the server.
373 <sect2 id="server-start-failures">
374 <title>Server Start-up Failures</title>
377 There are several common reasons the server might fail to
378 start. Check the server's log file, or start it by hand (without
379 redirecting standard output or standard error) and see what error
380 messages appear. Below we explain some of the most common error
381 messages in more detail.
386 LOG: could not bind IPv4 socket: Address already in use
387 HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
388 FATAL: could not create TCP/IP listen socket
390 This usually means just what it suggests: you tried to start
391 another server on the same port where one is already running.
392 However, if the kernel error message is not <computeroutput>Address
393 already in use</computeroutput> or some variant of that, there might
394 be a different problem. For example, trying to start a server
395 on a reserved port number might draw something like:
397 $ <userinput>postgres -p 666</userinput>
398 LOG: could not bind IPv4 socket: Permission denied
399 HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
400 FATAL: could not create TCP/IP listen socket
407 FATAL: could not create shared memory segment: Invalid argument
408 DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).
410 probably means your kernel's limit on the size of shared memory is
411 smaller than the work area <productname>PostgreSQL</productname>
412 is trying to create (4011376640 bytes in this example). Or it could
413 mean that you do not have System-V-style shared memory support
414 configured into your kernel at all. As a temporary workaround, you
415 can try starting the server with a smaller-than-normal number of
416 buffers (<xref linkend="guc-shared-buffers">). You will eventually want
417 to reconfigure your kernel to increase the allowed shared memory
418 size. You might also see this message when trying to start multiple
419 servers on the same machine, if their total space requested
420 exceeds the kernel limit.
426 FATAL: could not create semaphores: No space left on device
427 DETAIL: Failed system call was semget(5440126, 17, 03600).
429 does <emphasis>not</emphasis> mean you've run out of disk
430 space. It means your kernel's limit on the number of <systemitem
431 class="osname">System V</> semaphores is smaller than the number
432 <productname>PostgreSQL</productname> wants to create. As above,
433 you might be able to work around the problem by starting the
434 server with a reduced number of allowed connections
435 (<xref linkend="guc-max-connections">), but you'll eventually want to
436 increase the kernel limit.
440 If you get an <quote>illegal system call</> error, it is likely that
441 shared memory or semaphores are not supported in your kernel at
442 all. In that case your only option is to reconfigure the kernel to
443 enable these features.
447 Details about configuring <systemitem class="osname">System V</>
448 <acronym>IPC</> facilities are given in <xref linkend="sysvipc">.
452 <sect2 id="client-connection-problems">
453 <title>Client Connection Problems</title>
456 Although the error conditions possible on the client side are quite
457 varied and application-dependent, a few of them might be directly
458 related to how the server was started. Conditions other than
459 those shown below should be documented with the respective client
465 psql: could not connect to server: Connection refused
466 Is the server running on host "server.joe.com" and accepting
467 TCP/IP connections on port 5432?
469 This is the generic <quote>I couldn't find a server to talk
470 to</quote> failure. It looks like the above when TCP/IP
471 communication is attempted. A common mistake is to forget to
472 configure the server to allow TCP/IP connections.
476 Alternatively, you'll get this when attempting Unix-domain socket
477 communication to a local server:
479 psql: could not connect to server: No such file or directory
480 Is the server running locally and accepting
481 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
486 The last line is useful in verifying that the client is trying to
487 connect to the right place. If there is in fact no server
488 running there, the kernel error message will typically be either
489 <computeroutput>Connection refused</computeroutput> or
490 <computeroutput>No such file or directory</computeroutput>, as
491 illustrated. (It is important to realize that
492 <computeroutput>Connection refused</computeroutput> in this context
493 does <emphasis>not</emphasis> mean that the server got your
494 connection request and rejected it. That case will produce a
495 different message, as shown in <xref
496 linkend="client-authentication-problems">.) Other error messages
497 such as <computeroutput>Connection timed out</computeroutput> might
498 indicate more fundamental problems, like lack of network
504 <sect1 id="kernel-resources">
505 <title>Managing Kernel Resources</title>
508 A large <productname>PostgreSQL</> installation can quickly exhaust
509 various operating system resource limits. (On some systems, the
510 factory defaults are so low that you don't even need a really
511 <quote>large</> installation.) If you have encountered this kind of
512 problem, keep reading.
516 <title>Shared Memory and Semaphores</title>
518 <indexterm zone="sysvipc">
519 <primary>shared memory</primary>
522 <indexterm zone="sysvipc">
523 <primary>semaphores</primary>
527 Shared memory and semaphores are collectively referred to as
528 <quote><systemitem class="osname">System V</>
529 <acronym>IPC</></quote> (together with message queues, which are not
530 relevant for <productname>PostgreSQL</>). Almost all modern
531 operating systems provide these features, but many of them don't have
532 them turned on or sufficiently sized by default, especially as
533 available RAM and the demands of database applications grow.
534 (On <systemitem class="osname">Windows</>,
535 <productname>PostgreSQL</> provides its own replacement
536 implementation of these facilities, so most of this section
541 The complete lack of these facilities is usually manifested by an
542 <errorname>Illegal system call</> error upon server start. In
543 that case there is no alternative but to reconfigure your
544 kernel. <productname>PostgreSQL</> won't work without them.
545 This situation is rare, however, among modern operating systems.
549 When <productname>PostgreSQL</> exceeds one of the various hard
550 <acronym>IPC</> limits, the server will refuse to start and
551 should leave an instructive error message describing the problem
552 and what to do about it. (See also <xref
553 linkend="server-start-failures">.) The relevant kernel
554 parameters are named consistently across different systems; <xref
555 linkend="sysvipc-parameters"> gives an overview. The methods to set
556 them, however, vary. Suggestions for some platforms are given below.
560 <table id="sysvipc-parameters">
561 <title><systemitem class="osname">System V</> <acronym>IPC</> Parameters</title>
567 <entry>Description</>
568 <entry>Reasonable values</>
574 <entry><varname>SHMMAX</></>
575 <entry>Maximum size of shared memory segment (bytes)</>
576 <entry>at least several megabytes (see text)</entry>
580 <entry><varname>SHMMIN</></>
581 <entry>Minimum size of shared memory segment (bytes)</>
586 <entry><varname>SHMALL</></>
587 <entry>Total amount of shared memory available (bytes or pages)</>
588 <entry>if bytes, same as <varname>SHMMAX</varname>; if pages, <literal>ceil(SHMMAX/PAGE_SIZE)</literal></>
592 <entry><varname>SHMSEG</></>
593 <entry>Maximum number of shared memory segments per process</>
594 <entry>only 1 segment is needed, but the default is much higher</>
598 <entry><varname>SHMMNI</></>
599 <entry>Maximum number of shared memory segments system-wide</>
600 <entry>like <varname>SHMSEG</> plus room for other applications</>
604 <entry><varname>SEMMNI</></>
605 <entry>Maximum number of semaphore identifiers (i.e., sets)</>
606 <entry>at least <literal>ceil((max_connections + autovacuum_max_workers) / 16)</literal></>
610 <entry><varname>SEMMNS</></>
611 <entry>Maximum number of semaphores system-wide</>
612 <entry><literal>ceil((max_connections + autovacuum_max_workers) / 16) * 17</literal> plus room for other applications</>
616 <entry><varname>SEMMSL</></>
617 <entry>Maximum number of semaphores per set</>
618 <entry>at least 17</>
622 <entry><varname>SEMMAP</></>
623 <entry>Number of entries in semaphore map</>
628 <entry><varname>SEMVMX</></>
629 <entry>Maximum value of semaphore</>
630 <entry>at least 1000 (The default is often 32767; do not change unless necessary)</>
639 <indexterm><primary>SHMMAX</primary></indexterm> The most important
640 shared memory parameter is <varname>SHMMAX</>, the maximum size, in
641 bytes, of a shared memory segment. If you get an error message from
642 <function>shmget</> like <quote>Invalid argument</>, it is
643 likely that this limit has been exceeded. The size of the required
644 shared memory segment varies depending on several
645 <productname>PostgreSQL</> configuration parameters, as shown in
646 <xref linkend="shared-memory-parameters">. (Any error message you might
647 get will include the exact size of the failed allocation request.)
648 You can, as a temporary solution, lower some of those settings to
649 avoid the failure. While it is possible to get
650 <productname>PostgreSQL</> to run with <varname>SHMMAX</> as small as
651 2 MB, you need considerably more for acceptable performance. Desirable
652 settings are in the hundreds of megabytes to a few gigabytes.
656 Some systems also have a limit on the total amount of shared memory in
657 the system (<varname>SHMALL</>). Make sure this is large enough
658 for <productname>PostgreSQL</> plus any other applications that
659 are using shared memory segments. Note that <varname>SHMALL</>
660 is measured in pages rather than bytes on many systems.
664 Less likely to cause problems is the minimum size for shared
665 memory segments (<varname>SHMMIN</>), which should be at most
666 approximately 500 kB for <productname>PostgreSQL</> (it is
667 usually just 1). The maximum number of segments system-wide
668 (<varname>SHMMNI</>) or per-process (<varname>SHMSEG</>) are unlikely
669 to cause a problem unless your system has them set to zero.
673 <productname>PostgreSQL</> uses one semaphore per allowed connection
674 (<xref linkend="guc-max-connections">) and allowed autovacuum worker
675 process (<xref linkend="guc-autovacuum-max-workers">), in sets of 16.
677 also contain a 17th semaphore which contains a <quote>magic
678 number</quote>, to detect collision with semaphore sets used by
679 other applications. The maximum number of semaphores in the system
680 is set by <varname>SEMMNS</>, which consequently must be at least
681 as high as <varname>max_connections</> plus
682 <varname>autovacuum_max_workers</>, plus one extra for each 16
683 allowed connections plus workers (see the formula in <xref
684 linkend="sysvipc-parameters">). The parameter <varname>SEMMNI</>
685 determines the limit on the number of semaphore sets that can
686 exist on the system at one time. Hence this parameter must be at
687 least <literal>ceil((max_connections + autovacuum_max_workers) / 16)</>.
689 of allowed connections is a temporary workaround for failures,
690 which are usually confusingly worded <quote>No space
691 left on device</>, from the function <function>semget</>.
695 In some cases it might also be necessary to increase
696 <varname>SEMMAP</> to be at least on the order of
697 <varname>SEMMNS</>. This parameter defines the size of the semaphore
698 resource map, in which each contiguous block of available semaphores
699 needs an entry. When a semaphore set is freed it is either added to
700 an existing entry that is adjacent to the freed block or it is
701 registered under a new map entry. If the map is full, the freed
702 semaphores get lost (until reboot). Fragmentation of the semaphore
703 space could over time lead to fewer available semaphores than there
708 The <varname>SEMMSL</> parameter, which determines how many
709 semaphores can be in a set, must be at least 17 for
710 <productname>PostgreSQL</>.
714 Various other settings related to <quote>semaphore undo</>, such as
715 <varname>SEMMNU</> and <varname>SEMUME</>, do not affect
716 <productname>PostgreSQL</>.
722 <term><systemitem class="osname">AIX</></term>
723 <indexterm><primary>AIX</><secondary>IPC configuration</></>
726 At least as of version 5.1, it should not be necessary to do
727 any special configuration for such parameters as
728 <varname>SHMMAX</varname>, as it appears this is configured to
729 allow all memory to be used as shared memory. That is the
730 sort of configuration commonly used for other databases such
731 as <application>DB/2</application>.</para>
733 <para> It might, however, be necessary to modify the global
734 <command>ulimit</command> information in
735 <filename>/etc/security/limits</filename>, as the default hard
736 limits for file sizes (<varname>fsize</varname>) and numbers of
737 files (<varname>nofiles</varname>) might be too low.
743 <term><systemitem class="osname">BSD/OS</></term>
744 <indexterm><primary>BSD/OS</><secondary>IPC configuration</></>
747 <title>Shared Memory</title>
749 By default, only 4 MB of shared memory is supported. Keep in
750 mind that shared memory is not pageable; it is locked in RAM.
751 To increase the amount of shared memory supported by your
752 system, add something like the following to your kernel configuration
755 options "SHMALL=8192"
756 options "SHMMAX=\(SHMALL*PAGE_SIZE\)"
758 <varname>SHMALL</> is measured in 4 kB pages, so a value of
759 1024 represents 4 MB of shared memory. Therefore the above increases
760 the maximum shared memory area to 32 MB.
761 For those running 4.3 or later, you will probably also need to increase
762 <varname>KERNEL_VIRTUAL_MB</> above the default <literal>248</>.
763 Once all changes have been made, recompile the kernel, and reboot.
768 <title>Semaphores</title>
770 You will probably want to increase the number of semaphores
771 as well; the default system total of 60 will only allow about
772 50 <productname>PostgreSQL</productname> connections. Set the
773 values you want in your kernel configuration file, e.g.:
785 <term><systemitem class="osname">FreeBSD</></term>
786 <indexterm><primary>FreeBSD</><secondary>IPC configuration</></>
789 The default settings are only suitable for small installations
790 (for example, default <varname>SHMMAX</varname> is 32
791 MB). Changes can be made via the <command>sysctl</command> or
792 <command>loader</command> interfaces. The following
793 parameters can be set using <command>sysctl</command>:
795 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.shmall=32768</userinput>
796 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.shmmax=134217728</userinput>
797 <prompt>$</prompt> <userinput>sysctl -w kern.ipc.semmap=256</userinput>
799 To have these settings persist over reboots, modify
800 <filename>/etc/sysctl.conf</filename>.
804 The remaining semaphore settings are read-only as far as
805 <command>sysctl</command> is concerned, but can be changed
806 before boot using the <command>loader</command> prompt:
808 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmni=256</userinput>
809 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmns=512</userinput>
810 <prompt>(loader)</prompt> <userinput>set kern.ipc.semmnu=256</userinput>
812 Similarly these can be saved between reboots in
813 <filename>/boot/loader.conf</filename>.
817 You might also want to configure your kernel to lock shared
818 memory into RAM and prevent it from being paged out to swap.
819 This can be accomplished using the <command>sysctl</command>
820 setting <literal>kern.ipc.shm_use_phys</literal>.
824 If running in FreeBSD jails by enabling <application>sysctl</>'s
825 <literal>security.jail.sysvipc_allowed</>, <application>postmaster</>s
826 running in different jails should be run by different operating system
827 users. This improves security because it prevents non-root users
828 from interfering with shared memory or semaphores in different jails,
829 and it allows the PostgreSQL IPC cleanup code to function properly.
830 (In FreeBSD 6.0 and later the IPC cleanup code does not properly detect
831 processes in other jails, preventing the running of postmasters on the
832 same port in different jails.)
836 <systemitem class="osname">FreeBSD</> versions before 4.0 work like
837 <systemitem class="osname">NetBSD</> and <systemitem class="osname">
838 OpenBSD</> (see below).
844 <term><systemitem class="osname">NetBSD</></term>
845 <term><systemitem class="osname">OpenBSD</></term>
846 <indexterm><primary>NetBSD</><secondary>IPC configuration</></>
847 <indexterm><primary>OpenBSD</><secondary>IPC configuration</></>
850 The options <varname>SYSVSHM</> and <varname>SYSVSEM</> need
851 to be enabled when the kernel is compiled. (They are by
852 default.) The maximum size of shared memory is determined by
853 the option <varname>SHMMAXPGS</> (in pages). The following
854 shows an example of how to set the various parameters on
855 <systemitem class="osname">NetBSD</>
856 (<systemitem class="osname">OpenBSD</> uses <literal>option</> instead):
859 options SHMMAXPGS=4096
871 You might also want to configure your kernel to lock shared
872 memory into RAM and prevent it from being paged out to swap.
873 This can be accomplished using the <command>sysctl</command>
874 setting <literal>kern.ipc.shm_use_phys</literal>.
881 <term><systemitem class="osname">HP-UX</></term>
882 <indexterm><primary>HP-UX</><secondary>IPC configuration</></>
885 The default settings tend to suffice for normal installations.
886 On <productname>HP-UX</> 10, the factory default for
887 <varname>SEMMNS</> is 128, which might be too low for larger
891 <acronym>IPC</> parameters can be set in the <application>System
892 Administration Manager</> (<acronym>SAM</>) under
893 <menuchoice><guimenu>Kernel
894 Configuration</><guimenuitem>Configurable Parameters</></>. Choose
895 <guibutton>Create A New Kernel</> when you're done.
902 <term><systemitem class="osname">Linux</></term>
903 <indexterm><primary>Linux</><secondary>IPC configuration</></>
906 The default maximum segment size is 32 MB, which is only adequate
907 for very small <productname>PostgreSQL</productname>
908 installations. The default maximum total size is 2097152
909 pages. A page is almost always 4096 bytes except in unusual
910 kernel configurations with <quote>huge pages</quote>
911 (use <literal>getconf PAGE_SIZE</literal> to verify). That
912 makes a default limit of 8 GB, which is often enough, but not
917 The shared memory size settings can be changed via the
918 <command>sysctl</command> interface. For example, to allow 16 GB:
920 <prompt>$</prompt> <userinput>sysctl -w kernel.shmmax=17179869184</userinput>
921 <prompt>$</prompt> <userinput>sysctl -w kernel.shmall=4194304</userinput>
923 In addition these settings can be preserved between reboots in
924 the file <filename>/etc/sysctl.conf</filename>. Doing that is
929 Ancient distributions might not have the <command>sysctl</command> program,
930 but equivalent changes can be made by manipulating the
931 <filename>/proc</filename> file system:
933 <prompt>$</prompt> <userinput>echo 17179869184 >/proc/sys/kernel/shmmax</userinput>
934 <prompt>$</prompt> <userinput>echo 4194304 >/proc/sys/kernel/shmall</userinput>
939 The remaining defaults are quite generously sized, and usually
940 do not require changes.
947 <term><systemitem class="osname">MacOS X</></term>
948 <indexterm><primary>MacOS X</><secondary>IPC configuration</></>
951 The recommended method for configuring shared memory in OS X
952 is to create a file named <filename>/etc/sysctl.conf</>,
953 containing variable assignments such as:
955 kern.sysv.shmmax=4194304
959 kern.sysv.shmall=1024
961 Note that in some OS X versions,
962 <emphasis>all five</> shared-memory parameters must be set in
963 <filename>/etc/sysctl.conf</>, else the values will be ignored.
967 Beware that recent releases of OS X ignore attempts to set
968 <varname>SHMMAX</> to a value that isn't an exact multiple of 4096.
972 <varname>SHMALL</> is measured in 4 kB pages on this platform.
976 In older OS X versions, you will need to reboot to have changes in the
977 shared memory parameters take effect. As of 10.5 it is possible to
978 change all but <varname>SHMMNI</> on the fly, using
979 <application>sysctl</>. But it's still best to set up your preferred
980 values via <filename>/etc/sysctl.conf</>, so that the values will be
985 The file <filename>/etc/sysctl.conf</> is only honored in OS X
986 10.3.9 and later. If you are running a previous 10.3.x release,
987 you must edit the file <filename>/etc/rc</>
988 and change the values in the following commands:
990 sysctl -w kern.sysv.shmmax
991 sysctl -w kern.sysv.shmmin
992 sysctl -w kern.sysv.shmmni
993 sysctl -w kern.sysv.shmseg
994 sysctl -w kern.sysv.shmall
997 <filename>/etc/rc</> is usually overwritten by OS X system updates,
998 so you should expect to have to redo these edits after each update.
1002 In OS X 10.2 and earlier, instead edit these commands in the file
1003 <filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>.
1010 <term><systemitem class="osname">SCO OpenServer</></term>
1011 <indexterm><primary>SCO OpenServer</><secondary>IPC configuration</></>
1014 In the default configuration, only 512 kB of shared memory per
1015 segment is allowed. To increase the setting, first change to the
1016 directory <filename>/etc/conf/cf.d</>. To display the current value of
1017 <varname>SHMMAX</>, run:
1019 ./configure -y SHMMAX
1021 To set a new value for <varname>SHMMAX</>, run:
1023 ./configure SHMMAX=<replaceable>value</>
1025 where <replaceable>value</> is the new value you want to use
1026 (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel:
1037 <term><systemitem class="osname">Solaris</> 2.6 to 2.9 (Solaris
1038 6 to Solaris 9)</term>
1039 <indexterm><primary>Solaris</><secondary>IPC configuration</></>
1042 The default maximum size of a shared memory segment is too low for
1043 <productname>PostgreSQL</>. The relevant settings can be changed in
1044 <filename>/etc/system</>, for example:
1046 set shmsys:shminfo_shmmax=0x2000000
1047 set shmsys:shminfo_shmmin=1
1048 set shmsys:shminfo_shmmni=256
1049 set shmsys:shminfo_shmseg=256
1051 set semsys:seminfo_semmap=256
1052 set semsys:seminfo_semmni=512
1053 set semsys:seminfo_semmns=512
1054 set semsys:seminfo_semmsl=32
1056 You need to reboot for the changes to take effect. See also
1057 <ulink url="http://sunsite.uakom.sk/sunworldonline/swol-09-1997/swol-09-insidesolaris.html"></ulink>
1058 for information on shared memory under older versions of Solaris.
1064 <term><systemitem class="osname">Solaris</> 2.10 (Solaris
1066 <term><systemitem class="osname">OpenSolaris</></term>
1069 In Solaris 10 and OpenSolaris, the default shared memory and
1070 semaphore settings are good enough for most
1071 <productname>PostgreSQL</> applications. Solaris now defaults
1072 to a <varname>SHMMAX</> of one-quarter of system <acronym>RAM</>. If
1073 you need to increase this in order to set shared memory settings
1074 slightly higher, you should use a project setting associated
1075 with the <literal>postgres</> user. For example, run the
1076 following as <literal>root</>:
1078 projadd -c "PostgreSQL DB User" -K "project.max-shm-memory=(privileged,8GB,deny)" -U postgres -G postgres user.postgres
1083 This command adds the <literal>user.postgres</> project and
1084 raises the shared memory maximum for the <literal>postgres</>
1085 user to 8GB, and takes effect the next time that user logs
1086 in, or when you restart <productname>PostgreSQL</> (not reload).
1087 The above assumes that <productname>PostgreSQL</> is run by
1088 the <literal>postgres</> user in the <literal>postgres</>
1089 group. No server reboot is required.
1093 Other recommended kernel setting changes for database servers which will
1094 have a large number of connections are:
1096 project.max-shm-ids=(priv,32768,deny)
1097 project.max-sem-ids=(priv,4096,deny)
1098 project.max-msg-ids=(priv,4096,deny)
1103 Additionally, if you are running <productname>PostgreSQL</>
1104 inside a zone, you may need to raise the zone resource usage
1105 limits as well. See "Chapter2: Projects and Tasks" in the
1106 <citetitle>Solaris 10 System Administrator's Guide</> for more
1107 information on <literal>projects</> and <command>prctl</>.
1114 <term><systemitem class="osname">UnixWare</></term>
1115 <indexterm><primary>UnixWare</><secondary>IPC configuration</></>
1118 On <productname>UnixWare</> 7, the maximum size for shared
1119 memory segments is only 512 kB in the default configuration.
1120 To display the current value of <varname>SHMMAX</>, run:
1122 /etc/conf/bin/idtune -g SHMMAX
1124 which displays the current, default, minimum, and maximum
1125 values. To set a new value for <varname>SHMMAX</>,
1128 /etc/conf/bin/idtune SHMMAX <replaceable>value</>
1130 where <replaceable>value</> is the new value you want to use
1131 (in bytes). After setting <varname>SHMMAX</>, rebuild the
1134 /etc/conf/bin/idbuild -B
1144 <table id="shared-memory-parameters">
1145 <title><productname>PostgreSQL</productname> Shared Memory Usage</>
1151 <entry>Approximate shared memory bytes required (as of 8.3)</>
1157 <entry>Connections</>
1158 <entry>(1800 + 270 * <xref
1159 linkend="guc-max-locks-per-transaction">) * <xref
1160 linkend="guc-max-connections"></entry>
1164 <entry>Autovacuum workers</>
1165 <entry>(1800 + 270 * <xref
1166 linkend="guc-max-locks-per-transaction">) * <xref
1167 linkend="guc-autovacuum-max-workers"></entry>
1171 <entry>Prepared transactions</>
1172 <entry>(770 + 270 * <xref
1173 linkend="guc-max-locks-per-transaction">) * <xref linkend="guc-max-prepared-transactions"></entry>
1177 <entry>Shared disk buffers</>
1178 <entry>(<xref linkend="guc-block-size"> + 208) * <xref linkend="guc-shared-buffers"></entry>
1182 <entry>WAL buffers</>
1183 <entry>(<xref linkend="guc-wal-block-size"> + 8) * <xref linkend="guc-wal-buffers"></entry>
1187 <entry>Fixed space requirements</>
1188 <entry>770 kB</entry>
1197 <title>Resource Limits</title>
1200 Unix-like operating systems enforce various kinds of resource limits
1201 that might interfere with the operation of your
1202 <productname>PostgreSQL</productname> server. Of particular
1203 importance are limits on the number of processes per user, the
1204 number of open files per process, and the amount of memory available
1205 to each process. Each of these have a <quote>hard</quote> and a
1206 <quote>soft</quote> limit. The soft limit is what actually counts
1207 but it can be changed by the user up to the hard limit. The hard
1208 limit can only be changed by the root user. The system call
1209 <function>setrlimit</function> is responsible for setting these
1210 parameters. The shell's built-in command <command>ulimit</command>
1211 (Bourne shells) or <command>limit</command> (<application>csh</>) is
1212 used to control the resource limits from the command line. On
1213 BSD-derived systems the file <filename>/etc/login.conf</filename>
1214 controls the various resource limits set during login. See the
1215 operating system documentation for details. The relevant
1216 parameters are <varname>maxproc</varname>,
1217 <varname>openfiles</varname>, and <varname>datasize</varname>. For
1222 :datasize-cur=256M:\
1224 :openfiles-cur=256:\
1227 (<literal>-cur</literal> is the soft limit. Append
1228 <literal>-max</literal> to set the hard limit.)
1232 Kernels can also have system-wide limits on some resources.
1236 On <productname>Linux</productname>
1237 <filename>/proc/sys/fs/file-max</filename> determines the
1238 maximum number of open files that the kernel will support. It can
1239 be changed by writing a different number into the file or by
1240 adding an assignment in <filename>/etc/sysctl.conf</filename>.
1241 The maximum limit of files per process is fixed at the time the
1242 kernel is compiled; see
1243 <filename>/usr/src/linux/Documentation/proc.txt</filename> for
1251 The <productname>PostgreSQL</productname> server uses one process
1252 per connection so you should provide for at least as many processes
1253 as allowed connections, in addition to what you need for the rest
1254 of your system. This is usually not a problem but if you run
1255 several servers on one machine things might get tight.
1259 The factory default limit on open files is often set to
1260 <quote>socially friendly</quote> values that allow many users to
1261 coexist on a machine without using an inappropriate fraction of
1262 the system resources. If you run many servers on a machine this
1263 is perhaps what you want, but on dedicated servers you might want to
1268 On the other side of the coin, some systems allow individual
1269 processes to open large numbers of files; if more than a few
1270 processes do so then the system-wide limit can easily be exceeded.
1271 If you find this happening, and you do not want to alter the
1272 system-wide limit, you can set <productname>PostgreSQL</>'s <xref
1273 linkend="guc-max-files-per-process"> configuration parameter to
1274 limit the consumption of open files.
1278 <sect2 id="linux-memory-overcommit">
1279 <title>Linux Memory Overcommit</title>
1282 In Linux 2.4 and later, the default virtual memory behavior is not
1283 optimal for <productname>PostgreSQL</productname>. Because of the
1284 way that the kernel implements memory overcommit, the kernel might
1285 terminate the <productname>PostgreSQL</productname> server (the
1286 master server process) if the memory demands of
1287 another process cause the system to run out of virtual memory.
1291 If this happens, you will see a kernel message that looks like
1292 this (consult your system documentation and configuration on where
1293 to look for such a message):
1295 Out of Memory: Killed process 12345 (postgres).
1297 This indicates that the <filename>postgres</filename> process
1298 has been terminated due to memory pressure.
1299 Although existing database connections will continue to function
1300 normally, no new connections will be accepted. To recover,
1301 <productname>PostgreSQL</productname> will need to be restarted.
1305 One way to avoid this problem is to run
1306 <productname>PostgreSQL</productname> on a machine where you can
1307 be sure that other processes will not run the machine out of
1308 memory. If memory is tight, increasing the swap space of the
1309 operating system can help avoid the problem, because the
1310 out-of-memory (OOM) killer is invoked only when physical memory and
1311 swap space are exhausted.
1315 On Linux 2.6 and later, it is possible to modify the
1316 kernel's behavior so that it will not <quote>overcommit</> memory.
1317 Although this setting will not prevent the <ulink
1318 url="http://lwn.net/Articles/104179/">OOM killer</> from being invoked
1319 altogether, it will lower the chances significantly and will therefore
1320 lead to more robust system behavior. This is done by selecting strict
1321 overcommit mode via <command>sysctl</command>:
1323 sysctl -w vm.overcommit_memory=2
1325 or placing an equivalent entry in <filename>/etc/sysctl.conf</>.
1326 You might also wish to modify the related setting
1327 <varname>vm.overcommit_ratio</>. For details see the kernel documentation
1328 file <filename>Documentation/vm/overcommit-accounting</>.
1332 Another approach, which can be used with or without altering
1333 <varname>vm.overcommit_memory</>, is to set the process-specific
1334 <varname>oom_adj</> value for the postmaster process to <literal>-17</>,
1335 thereby guaranteeing it will not be targeted by the OOM killer. The
1336 simplest way to do this is to execute
1338 echo -17 > /proc/self/oom_adj
1340 in the postmaster's startup script just before invoking the postmaster.
1341 Note that this action must be done as root, or it will have no effect;
1342 so a root-owned startup script is the easiest place to do it. If you
1343 do this, you may also wish to build <productname>PostgreSQL</>
1344 with <literal>-DLINUX_OOM_ADJ=0</> added to <varname>CFLAGS</>.
1345 That will cause postmaster child processes to run with the normal
1346 <varname>oom_adj</> value of zero, so that the OOM killer can still
1347 target them at need.
1352 Some vendors' Linux 2.4 kernels are reported to have early versions
1353 of the 2.6 overcommit <command>sysctl</command> parameter. However, setting
1354 <literal>vm.overcommit_memory</> to 2
1355 on a 2.4 kernel that does not have the relevant code will make
1356 things worse, not better. It is recommended that you inspect
1357 the actual kernel source code (see the function
1358 <function>vm_enough_memory</> in the file <filename>mm/mmap.c</>)
1359 to verify what is supported in your kernel before you try this in a 2.4
1360 installation. The presence of the <filename>overcommit-accounting</>
1361 documentation file should <emphasis>not</> be taken as evidence that the
1362 feature is there. If in any doubt, consult a kernel expert or your
1370 <sect1 id="server-shutdown">
1371 <title>Shutting Down the Server</title>
1373 <indexterm zone="server-shutdown">
1374 <primary>shutdown</>
1378 There are several ways to shut down the database server. You control
1379 the type of shutdown by sending different signals to the master
1380 <command>postgres</command> process.
1384 <term><systemitem>SIGTERM</systemitem><indexterm><primary>SIGTERM</></></term>
1387 This is the <firstterm>Smart Shutdown</firstterm> mode.
1388 After receiving <systemitem>SIGTERM</systemitem>, the server
1389 disallows new connections, but lets existing sessions end their
1390 work normally. It shuts down only after all of the sessions terminate.
1391 If the server is in online backup mode, it additionally waits
1392 until online backup mode is no longer active. While backup mode is
1393 active, new connections will still be allowed, but only to superusers
1394 (this exception allows a superuser to connect to terminate
1395 online backup mode). If the server is in recovery when a smart
1396 shutdown is requested, recovery and streaming replication will be
1397 stopped only after all regular sessions have terminated.
1403 <term><systemitem>SIGINT</systemitem><indexterm><primary>SIGINT</></></term>
1406 This is the <firstterm>Fast Shutdown</firstterm> mode.
1407 The server disallows new connections and sends all existing
1408 server processes <systemitem>SIGTERM</systemitem>, which will cause them
1409 to abort their current transactions and exit promptly. It then
1410 waits for all server processes to exit and finally shuts down.
1411 If the server is in online backup mode, backup mode will be
1412 terminated, rendering the backup useless.
1418 <term><systemitem>SIGQUIT</systemitem><indexterm><primary>SIGQUIT</></></term>
1421 This is the <firstterm>Immediate Shutdown</firstterm> mode.
1422 The master <command>postgres</command> process will send a
1423 <systemitem>SIGQUIT</systemitem> to all child processes and exit
1424 immediately, without properly shutting itself down. The child processes
1425 likewise exit immediately upon receiving
1426 <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by
1427 replaying the WAL log) upon next start-up. This is recommended
1428 only in emergencies.
1436 The <xref linkend="app-pg-ctl"> program provides a convenient
1437 interface for sending these signals to shut down the server.
1438 Alternatively, you can send the signal directly using <command>kill</>
1439 on non-Windows systems.
1440 The <acronym>PID</> of the <command>postgres</command> process can be
1441 found using the <command>ps</command> program, or from the file
1442 <filename>postmaster.pid</filename> in the data directory. For
1443 example, to do a fast shutdown:
1445 $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput>
1451 It is best not to use <systemitem>SIGKILL</systemitem> to shut down
1452 the server. Doing so will prevent the server from releasing
1453 shared memory and semaphores, which might then have to be done
1454 manually before a new server can be started. Furthermore,
1455 <systemitem>SIGKILL</systemitem> kills the <command>postgres</command>
1456 process without letting it relay the signal to its subprocesses,
1457 so it will be necessary to kill the individual subprocesses by hand as
1463 To terminate an individual session while allowing other sessions to
1464 continue, use <function>pg_terminate_backend()</> (see <xref
1465 linkend="functions-admin-signal-table">) or send a
1466 <systemitem>SIGTERM</> signal to the child process associated with
1471 <sect1 id="upgrading">
1472 <title>Upgrading a <productname>PostgreSQL</> Cluster</title>
1474 <indexterm zone="upgrading">
1475 <primary>upgrading</primary>
1478 <indexterm zone="upgrading">
1479 <primary>version</primary>
1480 <secondary>compatibility</secondary>
1484 This section discusses how to upgrade your database data from one
1485 <productname>PostgreSQL</> release to a newer one.
1489 <productname>PostgreSQL</> major versions are represented by the
1490 first two digit groups of the version number, e.g., 8.4.
1491 <productname>PostgreSQL</> minor versions are represented by the
1492 third group of version digits, e.g., 8.4.2 is the second minor
1493 release of 8.4. Minor releases never change the internal storage
1494 format and are always compatible with earlier and later minor
1495 releases of the same major version number, e.g., 8.4.2 is compatible
1496 with 8.4, 8.4.1 and 8.4.6. To update between compatible versions,
1497 you simply replace the executables while the server is down and
1498 restart the server. The data directory remains unchanged —
1499 minor upgrades are that simple.
1503 For <emphasis>major</> releases of <productname>PostgreSQL</>, the
1504 internal data storage format is subject to change, thus complicating
1505 upgrades. The traditional method for moving data to a new major version
1506 is to dump and reload the database. Other methods are available,
1511 New major versions also typically introduce some user-visible
1512 incompatibilities, so application programming changes might be required.
1513 All user-visible changes are listed in the release notes (<xref
1514 linkend="release">); pay particular attention to the section
1515 labeled "Migration". If you are upgrading across several major
1516 versions, be sure to read the release notes for each intervening
1521 Cautious users will want to test their client applications on the new
1522 version before switching over fully; therefore, it's often a good idea to
1523 set up concurrent installations of old and new versions. When
1524 testing a <productname>PostgreSQL</> major upgrade, consider the
1525 following categories of possible changes:
1531 <term>Administration</term>
1534 The capabilities available for administrators to monitor and control
1535 the server often change and improve in each major release.
1544 Typically this includes new SQL command capabilities and not changes
1545 in behavior, unless specifically mentioned in the release notes.
1551 <term>Library API</term>
1554 Typically libraries like <application>libpq</> only add new
1555 functionality, again unless mentioned in the release notes.
1561 <term>System Catalogs</term>
1564 System catalog changes usually only affect database management tools.
1570 <term>Server C-language API</term>
1573 This involves changes in the backend function API, which is written
1574 in the C programming language. Such changes affect code that
1575 references backend functions deep inside the server.
1582 <sect2 id="upgrade-methods-pgdump">
1583 <title>Upgrading Data via <application>pg_dump</></title>
1586 To dump data from one major version of <productname>PostgreSQL</> and
1587 reload it in another, you must use <application>pg_dump</>; file system
1588 level backup methods will not work. (There are checks in place that prevent
1589 you from using a data directory with an incompatible version of
1590 <productname>PostgreSQL</productname>, so no great harm can be done by
1591 trying to start the wrong server version on a data directory.)
1595 It is recommended that you use the <application>pg_dump</> and
1596 <application>pg_dumpall</> programs from the newer version of
1597 <productname>PostgreSQL</>, to take advantage of enhancements
1598 that might have been made in these programs. Current releases of the
1599 dump programs can read data from any server version back to 7.0.
1603 These instructions assume that your existing installation is under the
1604 <filename>/usr/local/pgsql</> directory, and that the data area is in
1605 <filename>/usr/local/pgsql/data</>. Substitute your paths
1612 If making a backup, make sure that your database is not being updated.
1613 This does not affect the integrity of the backup, but the changed
1614 data would of course not be included. If necessary, edit the
1615 permissions in the file <filename>/usr/local/pgsql/data/pg_hba.conf</>
1616 (or equivalent) to disallow access from everyone except you.
1617 See <xref linkend="client-authentication"> for additional information on
1623 <primary>pg_dumpall</primary>
1624 <secondary>use during upgrade</secondary>
1627 To back up your database installation, type:
1629 <userinput>pg_dumpall > <replaceable>outputfile</></userinput>
1631 If you need to preserve OIDs (such as when using them as
1632 foreign keys), then use the <option>-o</option> option when running
1633 <application>pg_dumpall</>.
1637 To make the backup, you can use the <application>pg_dumpall</application>
1638 command from the version you are currently running. For best
1639 results, however, try to use the <application>pg_dumpall</application>
1640 command from <productname>PostgreSQL</productname> &version;,
1641 since this version contains bug fixes and improvements over older
1642 versions. While this advice might seem idiosyncratic since you
1643 haven't installed the new version yet, it is advisable to follow
1644 it if you plan to install the new version in parallel with the
1645 old version. In that case you can complete the installation
1646 normally and transfer the data later. This will also decrease
1653 Shut down the old server:
1655 <userinput>pg_ctl stop</>
1657 On systems that have <productname>PostgreSQL</> started at boot time,
1658 there is probably a start-up file that will accomplish the same thing. For
1659 example, on a <systemitem class="osname">Red Hat Linux</> system one
1660 might find that this works:
1662 <userinput>/etc/rc.d/init.d/postgresql stop</userinput>
1664 See <xref linkend="runtime"> for details about starting and
1665 stopping the server.
1671 If restoring from backup, rename or delete the old installation
1672 directory. It is a good idea to rename the directory, rather than
1673 delete it, in case you have trouble and need to revert to it. Keep
1674 in mind the directory might consume significant disk space. To rename
1675 the directory, use a command like this:
1677 <userinput>mv /usr/local/pgsql /usr/local/pgsql.old</>
1679 (Be sure to move the directory as a single unit so relative paths
1686 Install the new version of <productname>PostgreSQL</productname> as
1687 outlined in <![%standalone-include[the next section.]]>
1688 <![%standalone-ignore[<xref linkend="install-procedure">.]]>
1694 Create a new database cluster if needed. Remember that you must
1695 execute these commands while logged in to the special database user
1696 account (which you already have if you are upgrading).
1698 <userinput>/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data</>
1705 Restore your previous <filename>pg_hba.conf</> and any
1706 <filename>postgresql.conf</> modifications.
1712 Start the database server, again using the special database user
1715 <userinput>/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data</>
1722 Finally, restore your data from backup with:
1724 <userinput>/usr/local/pgsql/bin/psql -d postgres -f <replaceable>outputfile</></userinput>
1726 using the <emphasis>new</> <application>psql</>.
1732 The least downtime can be achieved by installing the new server in
1733 a different directory and running both the old and the new servers
1734 in parallel, on different ports. Then you can use something like:
1737 pg_dumpall -p 5432 | psql -d postgres -p 5433
1739 to transfer your data.
1744 <sect2 id="upgrading-methods-other">
1745 <title>Non-Dump Upgrade Methods</title>
1748 The <link linkend="pgupgrade">pg_upgrade</link> module allows an
1749 installation to be migrated in-place from one major
1750 <productname>PostgreSQL</> version to the next. Upgrades can be
1751 performed in minutes.
1755 It is also possible to use certain replication methods, such as
1756 <productname>Slony</>, to create a standby server with the updated version of
1757 <productname>PostgreSQL</>. This is possible because Slony supports
1758 replication between different major versions of
1759 <productname>PostgreSQL</>. The standby can be on the same computer or
1760 a different computer. Once it has synced up with the master server
1761 (running the older version of <productname>PostgreSQL</>), you can
1762 switch masters and make the standby the master and shut down the older
1763 database instance. Such a switch-over results in only several seconds
1764 of downtime for an upgrade.
1770 <sect1 id="preventing-server-spoofing">
1771 <title>Preventing Server Spoofing</title>
1773 <indexterm zone="preventing-server-spoofing">
1774 <primary>server spoofing</primary>
1778 While the server is running, it is not possible for a malicious user
1779 to take the place of the normal database server. However, when the
1780 server is down, it is possible for a local user to spoof the normal
1781 server by starting their own server. The spoof server could read
1782 passwords and queries sent by clients, but could not return any data
1783 because the <varname>PGDATA</> directory would still be secure because
1784 of directory permissions. Spoofing is possible because any user can
1785 start a database server; a client cannot identify an invalid server
1786 unless it is specially configured.
1790 The simplest way to prevent spoofing for <literal>local</>
1791 connections is to use a Unix domain socket directory (<xref
1792 linkend="guc-unix-socket-directory">) that has write permission only
1793 for a trusted local user. This prevents a malicious user from creating
1794 their own socket file in that directory. If you are concerned that
1795 some applications might still reference <filename>/tmp</> for the
1796 socket file and hence be vulnerable to spoofing, during operating system
1797 startup create a symbolic link <filename>/tmp/.s.PGSQL.5432</> that points
1798 to the relocated socket file. You also might need to modify your
1799 <filename>/tmp</> cleanup script to prevent removal of the symbolic link.
1803 To prevent spoofing on TCP connections, the best solution is to use
1804 SSL certificates and make sure that clients check the server's certificate.
1805 To do that, the server
1806 must be configured to accept only <literal>hostssl</> connections (<xref
1807 linkend="auth-pg-hba-conf">) and have SSL
1808 <filename>server.key</filename> (key) and
1809 <filename>server.crt</filename> (certificate) files (<xref
1810 linkend="ssl-tcp">). The TCP client must connect using
1811 <literal>sslmode=verify-ca</> or
1812 <literal>verify-full</> and have the appropriate root certificate
1813 file installed (<xref linkend="libpq-connect">).
1817 <sect1 id="encryption-options">
1818 <title>Encryption Options</title>
1820 <indexterm zone="encryption-options">
1821 <primary>encryption</primary>
1825 <productname>PostgreSQL</productname> offers encryption at several
1826 levels, and provides flexibility in protecting data from disclosure
1827 due to database server theft, unscrupulous administrators, and
1828 insecure networks. Encryption might also be required to secure
1829 sensitive data such as medical records or financial transactions.
1835 <term>Password Storage Encryption</term>
1839 By default, database user passwords are stored as MD5 hashes, so
1840 the administrator cannot determine the actual password assigned
1841 to the user. If MD5 encryption is used for client authentication,
1842 the unencrypted password is never even temporarily present on the
1843 server because the client MD5-encrypts it before being sent
1850 <term>Encryption For Specific Columns</term>
1854 The <xref linkend="pgcrypto"> module allows certain fields to be
1856 This is useful if only some of the data is sensitive.
1857 The client supplies the decryption key and the data is decrypted
1858 on the server and then sent to the client.
1862 The decrypted data and the decryption key are present on the
1863 server for a brief time while it is being decrypted and
1864 communicated between the client and server. This presents a brief
1865 moment where the data and keys can be intercepted by someone with
1866 complete access to the database server, such as the system
1873 <term>Data Partition Encryption</term>
1877 On Linux, encryption can be layered on top of a file system
1878 using a <quote>loopback device</quote>. This allows an entire
1879 file system partition to be encrypted on disk, and decrypted by the
1880 operating system. On FreeBSD, the equivalent facility is called
1881 GEOM Based Disk Encryption (<acronym>gbde</acronym>), and many
1882 other operating systems support this functionality, including Windows.
1886 This mechanism prevents unencrypted data from being read from the
1887 drives if the drives or the entire computer is stolen. This does
1888 not protect against attacks while the file system is mounted,
1889 because when mounted, the operating system provides an unencrypted
1890 view of the data. However, to mount the file system, you need some
1891 way for the encryption key to be passed to the operating system,
1892 and sometimes the key is stored somewhere on the host that mounts
1899 <term>Encrypting Passwords Across A Network</term>
1903 The <literal>MD5</> authentication method double-encrypts the
1904 password on the client before sending it to the server. It first
1905 MD5-encrypts it based on the user name, and then encrypts it
1906 based on a random salt sent by the server when the database
1907 connection was made. It is this double-encrypted value that is
1908 sent over the network to the server. Double-encryption not only
1909 prevents the password from being discovered, it also prevents
1910 another connection from using the same encrypted password to
1911 connect to the database server at a later time.
1917 <term>Encrypting Data Across A Network</term>
1921 SSL connections encrypt all data sent across the network: the
1922 password, the queries, and the data returned. The
1923 <filename>pg_hba.conf</> file allows administrators to specify
1924 which hosts can use non-encrypted connections (<literal>host</>)
1925 and which require SSL-encrypted connections
1926 (<literal>hostssl</>). Also, clients can specify that they
1927 connect to servers only via SSL. <application>Stunnel</> or
1928 <application>SSH</> can also be used to encrypt transmissions.
1934 <term>SSL Host Authentication</term>
1938 It is possible for both the client and server to provide SSL
1939 certificates to each other. It takes some extra configuration
1940 on each side, but this provides stronger verification of identity
1941 than the mere use of passwords. It prevents a computer from
1942 pretending to be the server just long enough to read the password
1943 sent by the client. It also helps prevent <quote>man in the middle</>
1944 attacks where a computer between the client and server pretends to
1945 be the server and reads and passes all data between the client and
1952 <term>Client-Side Encryption</term>
1956 If the system administrator for the server's machine cannot be trusted,
1958 for the client to encrypt the data; this way, unencrypted data
1959 never appears on the database server. Data is encrypted on the
1960 client before being sent to the server, and database results have
1961 to be decrypted on the client before being used.
1970 <sect1 id="ssl-tcp">
1971 <title>Secure TCP/IP Connections with SSL</title>
1973 <indexterm zone="ssl-tcp">
1974 <primary>SSL</primary>
1978 <productname>PostgreSQL</> has native support for using
1979 <acronym>SSL</> connections to encrypt client/server communications
1980 for increased security. This requires that
1981 <productname>OpenSSL</productname> is installed on both client and
1982 server systems and that support in <productname>PostgreSQL</> is
1983 enabled at build time (see <xref linkend="installation">).
1987 With <acronym>SSL</> support compiled in, the
1988 <productname>PostgreSQL</> server can be started with
1989 <acronym>SSL</> enabled by setting the parameter
1990 <xref linkend="guc-ssl"> to <literal>on</> in
1991 <filename>postgresql.conf</>. The server will listen for both normal
1992 and <acronym>SSL</> connections on the same TCP port, and will negotiate
1993 with any connecting client on whether to use <acronym>SSL</>. By
1994 default, this is at the client's option; see <xref
1995 linkend="auth-pg-hba-conf"> about how to set up the server to require
1996 use of <acronym>SSL</> for some or all connections.
2000 <productname>PostgreSQL</productname> reads the system-wide
2001 <productname>OpenSSL</productname> configuration file. By default, this
2002 file is named <filename>openssl.cnf</filename> and is located in the
2003 directory reported by <literal>openssl version -d</>.
2004 This default can be overridden by setting environment variable
2005 <envar>OPENSSL_CONF</envar> to the name of the desired configuration file.
2009 <productname>OpenSSL</productname> supports a wide range of ciphers
2010 and authentication algorithms, of varying strength. While a list of
2011 ciphers can be specified in the <productname>OpenSSL</productname>
2012 configuration file, you can specify ciphers specifically for use by
2013 the database server by modifying <xref linkend="guc-ssl-ciphers"> in
2014 <filename>postgresql.conf</>.
2019 It is possible to have authentication without encryption overhead by
2020 using <literal>NULL-SHA</> or <literal>NULL-MD5</> ciphers. However,
2021 a man-in-the-middle could read and pass communications between client
2022 and server. Also, encryption overhead is minimal compared to the
2023 overhead of authentication. For these reasons NULL ciphers are not
2029 To start in <acronym>SSL</> mode, the files <filename>server.crt</>
2030 and <filename>server.key</> must exist in the server's data directory.
2031 These files should contain the server certificate and private key,
2033 On Unix systems, the permissions on <filename>server.key</filename> must
2034 disallow any access to world or group; achieve this by the command
2035 <command>chmod 0600 server.key</command>.
2036 If the private key is protected with a passphrase, the
2037 server will prompt for the passphrase and will not start until it has
2042 In some cases, the server certificate might be signed by an
2043 <quote>intermediate</> certificate authority, rather than one that is
2044 directly trusted by clients. To use such a certificate, append the
2045 certificate of the signing authority to the <filename>server.crt</> file,
2046 then its parent authority's certificate, and so on up to a <quote>root</>
2047 authority that is trusted by the clients. The root certificate should
2048 be included in every case where <filename>server.crt</> contains more than
2052 <sect2 id="ssl-client-certificates">
2053 <title>Using Client Certificates</title>
2056 To require the client to supply a trusted certificate, place
2057 certificates of the certificate authorities (<acronym>CA</acronym>s)
2058 you trust in the file <filename>root.crt</filename> in the data
2059 directory, and set the <literal>clientcert</literal> parameter
2060 to 1 on the appropriate <literal>hostssl</> line(s) in
2061 <filename>pg_hba.conf</>.
2062 A certificate will then be requested from the client during
2063 SSL connection startup. (See <xref linkend="libpq-ssl"> for a
2064 description of how to set up certificates on the client.) The server will
2065 verify that the client's certificate is signed by one of the trusted
2066 certificate authorities. Certificate Revocation List (CRL) entries
2067 are also checked if the file <filename>root.crl</filename> exists.
2068 <!-- If this URL changes replace it with a URL to www.archive.org. -->
2070 url="http://h71000.www7.hp.com/DOC/83final/BA554_90007/ch04s02.html"></>
2071 for diagrams showing SSL certificate usage.)
2075 The <literal>clientcert</literal> option in <filename>pg_hba.conf</> is
2076 available for all authentication methods, but only for rows specified as
2077 <literal>hostssl</>. When <literal>clientcert</literal> is not specified
2078 or is set to 0, the server will still verify presented client
2079 certificates against <filename>root.crt</filename> if that file exists
2080 — but it will not insist that a client certificate be presented.
2084 Note that <filename>root.crt</filename> lists the top-level CAs that are
2085 considered trusted for signing client certificates. In principle it need
2086 not list the CA that signed the server's certificate, though in most cases
2087 that CA would also be trusted for client certificates.
2091 If you are setting up client certificates, you may wish to use
2092 the <literal>cert</> authentication method, so that the certificates
2093 control user authentication as well as providing connection security.
2094 See <xref linkend="auth-cert"> for details.
2098 <sect2 id="ssl-server-files">
2099 <title>SSL Server File Usage</title>
2102 <xref linkend="ssl-file-usage"> summarizes the files that are
2103 relevant to the SSL setup on the server.
2106 <table id="ssl-file-usage">
2107 <title>SSL Server File Usage</title>
2112 <entry>Contents</entry>
2113 <entry>Effect</entry>
2120 <entry><filename>$PGDATA/server.crt</></entry>
2121 <entry>server certificate</entry>
2122 <entry>sent to client to indicate server's identity</entry>
2126 <entry><filename>$PGDATA/server.key</></entry>
2127 <entry>server private key</entry>
2128 <entry>proves server certificate was sent by the owner; does not indicate
2129 certificate owner is trustworthy</entry>
2133 <entry><filename>$PGDATA/root.crt</></entry>
2134 <entry>trusted certificate authorities</entry>
2135 <entry>checks that client certificate is
2136 signed by a trusted certificate authority</entry>
2140 <entry><filename>$PGDATA/root.crl</></entry>
2141 <entry>certificates revoked by certificate authorities</entry>
2142 <entry>client certificate must not be on this list</entry>
2150 The files <filename>server.key</>, <filename>server.crt</>,
2151 <filename>root.crt</filename>, and <filename>root.crl</filename>
2152 are only examined during server start; so you must restart
2153 the server for changes in them to take effect.
2157 <sect2 id="ssl-certificate-creation">
2158 <title>Creating a Self-signed Certificate</title>
2161 To create a quick self-signed certificate for the server, use the
2162 following <productname>OpenSSL</productname> command:
2164 openssl req -new -text -out server.req
2166 Fill out the information that <application>openssl</> asks for. Make sure
2167 you enter the local host name as <quote>Common Name</>; the challenge
2168 password can be left blank. The program will generate a key that is
2169 passphrase protected; it will not accept a passphrase that is less
2170 than four characters long. To remove the passphrase (as you must if
2171 you want automatic start-up of the server), run the commands:
2173 openssl rsa -in privkey.pem -out server.key
2176 Enter the old passphrase to unlock the existing key. Now do:
2178 openssl req -x509 -in server.req -text -key server.key -out server.crt
2180 to turn the certificate into a self-signed certificate and to copy
2181 the key and certificate to where the server will look for them.
2184 chmod og-rwx server.key
2186 because the server will reject the file if its permissions are more
2188 For more details on how to create your server private key and
2189 certificate, refer to the <productname>OpenSSL</> documentation.
2193 A self-signed certificate can be used for testing, but a certificate
2194 signed by a certificate authority (<acronym>CA</>) (either one of the
2195 global <acronym>CAs</> or a local one) should be used in production
2196 so that clients can verify the server's identity. If all the clients
2197 are local to the organization, using a local <acronym>CA</> is
2205 <sect1 id="ssh-tunnels">
2206 <title>Secure TCP/IP Connections with <application>SSH</application> Tunnels</title>
2208 <indexterm zone="ssh-tunnels">
2209 <primary>ssh</primary>
2213 It is possible to use <application>SSH</application> to encrypt the network
2214 connection between clients and a
2215 <productname>PostgreSQL</productname> server. Done properly, this
2216 provides an adequately secure network connection, even for non-SSL-capable
2221 First make sure that an <application>SSH</application> server is
2222 running properly on the same machine as the
2223 <productname>PostgreSQL</productname> server and that you can log in using
2224 <command>ssh</command> as some user. Then you can establish a secure
2225 tunnel with a command like this from the client machine:
2227 ssh -L 63333:localhost:5432 joe@foo.com
2229 The first number in the <option>-L</option> argument, 63333, is the
2230 port number of your end of the tunnel; it can be any unused port.
2231 (IANA reserves ports 49152 through 65535 for private use.) The
2232 second number, 5432, is the remote end of the tunnel: the port
2233 number your server is using. The name or IP address between the
2234 port numbers is the host with the database server you are going to
2235 connect to, as seen from the host you are logging in to, which
2236 is <literal>foo.com</literal> in this example. In order to connect
2237 to the database server using this tunnel, you connect to port 63333
2238 on the local machine:
2240 psql -h localhost -p 63333 postgres
2242 To the database server it will then look as though you are really
2243 user <literal>joe</literal> on host <literal>foo.com</literal>
2244 connecting to <literal>localhost</literal> in that context, and it
2245 will use whatever authentication procedure was configured for
2246 connections from this user and host. Note that the server will not
2247 think the connection is SSL-encrypted, since in fact it is not
2248 encrypted between the
2249 <application>SSH</application> server and the
2250 <productname>PostgreSQL</productname> server. This should not pose any
2251 extra security risk as long as they are on the same machine.
2256 tunnel setup to succeed you must be allowed to connect via
2257 <command>ssh</command> as <literal>joe@foo.com</literal>, just
2258 as if you had attempted to use <command>ssh</command> to create a
2263 You could also have set up the port forwarding as
2265 ssh -L 63333:foo.com:5432 joe@foo.com
2267 but then the database server will see the connection as coming in
2268 on its <literal>foo.com</literal> interface, which is not opened by
2269 the default setting <literal>listen_addresses =
2270 'localhost'</literal>. This is usually not what you want.
2274 If you have to <quote>hop</quote> to the database server via some
2275 login host, one possible setup could look like this:
2277 ssh -L 63333:db.foo.com:5432 joe@shell.foo.com
2279 Note that this way the connection
2280 from <literal>shell.foo.com</literal>
2281 to <literal>db.foo.com</literal> will not be encrypted by the SSH
2283 SSH offers quite a few configuration possibilities when the network
2284 is restricted in various ways. Please refer to the SSH
2285 documentation for details.
2290 Several other applications exist that can provide secure tunnels using
2291 a procedure similar in concept to the one just described.
OSDN Git Service
