本部分涵蓋了 PostgreSQL 資料庫管理員會感興趣的主題。這包括安裝軟體，設定和配置伺服器，管理使用者和資料庫以及維護任務。任何運行 PostgreSQL 伺服器的人，即使是個人使用，特別是在產品環境中，都應該熟悉本部分所涉及的主題。

這部分的資訊大致按照新使用者閱讀的順序排列。但是這些章節是獨立的，可以根據需求再單獨閱讀。這部分的內容以主題單位的敘述方式呈現。要查看某個特定指令的完整說明，請參閱第 VI 部分。

前幾章是為了在沒有必要知識的情況下可以理解而撰寫的，因此需要建立自有伺服器的新使用者可以使用這一部分開始探索。這部分的其餘部分是關於調教和管理；該內容假定讀者熟悉 PostgreSQL 資料庫系統的一般用法。建議讀者閱讀第 I 部分和第 II 部分以取得更多訊息。

A platform (that is, a CPU architecture and operating system combination) is considered supported by the PostgreSQL development community if the code contains provisions to work on that platform and it has recently been verified to build and pass its regression tests on that platform. Currently, most testing of platform compatibility is done automatically by test machines in the PostgreSQL Build Farm. If you are interested in using PostgreSQL on a platform that is not represented in the build farm, but on which the code works or can be made to work, you are strongly encouraged to set up a build farm member machine so that continued compatibility can be assured.

In general, PostgreSQL can be expected to work on these CPU architectures: x86, x86_64, IA64, PowerPC, PowerPC 64, S/390, S/390x, Sparc, Sparc 64, ARM, MIPS, MIPSEL, and PA-RISC. Code support exists for M68K, M32R, and VAX, but these architectures are not known to have been tested recently. It is often possible to build on an unsupported CPU type by configuring with --disable-spinlocks, but performance will be poor.

PostgreSQL can be expected to work on these operating systems: Linux (all recent distributions), Windows (Win2000 SP4 and later), FreeBSD, OpenBSD, NetBSD, macOS, AIX, HP/UX, and Solaris. Other Unix-like systems may also work but are not currently being tested. In most cases, all CPU architectures supported by a given operating system will work. Look in Section 16.7 below to see if there is information specific to your operating system, particularly if using an older system.

If you have installation problems on a platform that is known to be supported according to recent build farm results, please report it to <pgsql-bugs@postgresql.org>. If you are interested in porting PostgreSQL to a new platform, <pgsql-hackers@postgresql.org> is the appropriate place to discuss that.

./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

The long version is the rest of this chapter.

This section documents additional platform-specific issues regarding the installation and setup of PostgreSQL. Be sure to read the installation instructions, and in particular Section 16.2 as well. Also, check Chapter 32 regarding the interpretation of regression test results.

Platforms that are not covered here have no known platform-specific installation issues.

16.7.1. AIX

PostgreSQL works on AIX, but getting it installed properly can be challenging. AIX versions from 4.3.3 to 6.1 are considered supported. You can use GCC or the native IBM compiler xlc. In general, using recent versions of AIX and PostgreSQL helps. Check the build farm for up to date information about which versions of AIX are known to work.

The minimum recommended fix levels for supported AIX versions are:AIX 4.3.3

Maintenance Level 11 + post ML11 bundleAIX 5.1

Maintenance Level 9 + post ML9 bundleAIX 5.2

Technology Level 10 Service Pack 3AIX 5.3

Technology Level 7AIX 6.1

Base Level

To check your current fix level, use oslevel -r in AIX 4.3.3 to AIX 5.2 ML 7, or oslevel -s in later versions.

Use the following configure flags in addition to your own if you have installed Readline or libz in /usr/local: --with-includes=/usr/local/include --with-libraries=/usr/local/lib.

16.7.1.1. GCC Issues

On AIX 5.3, there have been some problems getting PostgreSQL to compile and run using GCC.

You will want to use a version of GCC subsequent to 3.3.2, particularly if you use a prepackaged version. We had good success with 4.0.1. Problems with earlier versions seem to have more to do with the way IBM packaged GCC than with actual issues with GCC, so that if you compile GCC yourself, you might well have success with an earlier version of GCC.

16.7.1.2. Unix-Domain Sockets Broken

AIX 5.3 has a problem where sockaddr_storage is not defined to be large enough. In version 5.3, IBM increased the size of sockaddr_un, the address structure for Unix-domain sockets, but did not correspondingly increase the size of sockaddr_storage. The result of this is that attempts to use Unix-domain sockets with PostgreSQL lead to libpq overflowing the data structure. TCP/IP connections work OK, but not Unix-domain sockets, which prevents the regression tests from working.

The problem was reported to IBM, and is recorded as bug report PMR29657. If you upgrade to maintenance level 5300-03 or later, that will include this fix. A quick workaround is to alter _SS_MAXSIZE to 1025 in /usr/include/sys/socket.h. In either case, recompile PostgreSQL once you have the corrected header file.

16.7.1.3. Internet Address Issues

PostgreSQL relies on the system's getaddrinfo function to parse IP addresses in listen_addresses, pg_hba.conf, etc. Older versions of AIX have assorted bugs in this function. If you have problems related to these settings, updating to the appropriate AIX fix level shown above should take care of it.

One user reports:

When implementing PostgreSQL version 8.1 on AIX 5.3, we periodically ran into problems where the statistics collector would “mysteriously” not come up successfully. This appears to be the result of unexpected behavior in the IPv6 implementation. It looks like PostgreSQL and IPv6 do not play very well together on AIX 5.3.

Any of the following actions “fix” the problem.

• Delete the IPv6 address for localhost:

  Copy

  (as root)
  # ifconfig lo0 inet6 ::1/0 delete

• Remove IPv6 from net services. The file /etc/netsvc.conf on AIX is roughly equivalent to /etc/nsswitch.conf on Solaris/Linux. The default, on AIX, is thus:

  Copy

  hosts=local,bind

  Replace this with:

  Copy

  hosts=local4,bind4

  to deactivate searching for IPv6 addresses.

Warning

This is really a workaround for problems relating to immaturity of IPv6 support, which improved visibly during the course of AIX 5.3 releases. It has worked with AIX version 5.3, but does not represent an elegant solution to the problem. It has been reported that this workaround is not only unnecessary, but causes problems on AIX 6.1, where IPv6 support has become more mature.

16.7.1.4. Memory Management

AIX can be somewhat peculiar with regards to the way it does memory management. You can have a server with many multiples of gigabytes of RAM free, but still get out of memory or address space errors when running applications. One example is loading of extensions failing with unusual errors. For example, running as the owner of the PostgreSQL installation:

=# CREATE EXTENSION plperl;
ERROR:  could not load library "/opt/dbs/pgsql/lib/plperl.so": A memory address is not in the address space for the process.

Running as a non-owner in the group possessing the PostgreSQL installation:

=# CREATE EXTENSION plperl;
ERROR:  could not load library "/opt/dbs/pgsql/lib/plperl.so": Bad address

Another example is out of memory errors in the PostgreSQL server logs, with every memory allocation near or greater than 256 MB failing.

The overall cause of all these problems is the default bittedness and memory model used by the server process. By default, all binaries built on AIX are 32-bit. This does not depend upon hardware type or kernel in use. These 32-bit processes are limited to 4 GB of memory laid out in 256 MB segments using one of a few models. The default allows for less than 256 MB in the heap as it shares a single segment with the stack.

In the case of the plperl example, above, check your umask and the permissions of the binaries in your PostgreSQL installation. The binaries involved in that example were 32-bit and installed as mode 750 instead of 755. Due to the permissions being set in this fashion, only the owner or a member of the possessing group can load the library. Since it isn't world-readable, the loader places the object into the process' heap instead of the shared library segments where it would otherwise be placed.

The “ideal” solution for this is to use a 64-bit build of PostgreSQL, but that is not always practical, because systems with 32-bit processors can build, but not run, 64-bit binaries.

If a 32-bit binary is desired, set LDR_CNTRL to MAXDATA=0xn0000000, where 1 <= n <= 8, before starting the PostgreSQL server, and try different values and postgresql.conf settings to find a configuration that works satisfactorily. This use of LDR_CNTRL tells AIX that you want the server to have MAXDATA bytes set aside for the heap, allocated in 256 MB segments. When you find a workable configuration, ldedit can be used to modify the binaries so that they default to using the desired heap size. PostgreSQL can also be rebuilt, passingconfigure LDFLAGS="-Wl,-bmaxdata:0xn0000000" to achieve the same effect.

For a 64-bit build, set OBJECT_MODE to 64 and pass CC="gcc -maix64" and LDFLAGS="-Wl,-bbigtoc" to configure. (Options for xlc might differ.) If you omit the export of OBJECT_MODE, your build may fail with linker errors. When OBJECT_MODE is set, it tells AIX's build utilities such as ar, as, and ld what type of objects to default to handling.

By default, overcommit of paging space can happen. While we have not seen this occur, AIX will kill processes when it runs out of memory and the overcommit is accessed. The closest to this that we have seen is fork failing because the system decided that there was not enough memory for another process. Like many other parts of AIX, the paging space allocation method and out-of-memory kill is configurable on a system- or process-wide basis if this becomes a problem.

References and Resources

“Large Program Support”. AIX Documentation: General Programming Concepts: Writing and Debugging Programs.

“Program Address Space Overview”. AIX Documentation: General Programming Concepts: Writing and Debugging Programs.

“Performance Overview of the Virtual Memory Manager (VMM)”. AIX Documentation: Performance Management Guide.

“Page Space Allocation”. AIX Documentation: Performance Management Guide.

“Paging-space thresholds tuning”. AIX Documentation: Performance Management Guide.

Developing and Porting C and C++ Applications on AIX. IBM Redbook.

16.7.2. Cygwin

PostgreSQL can be built using Cygwin, a Linux-like environment for Windows, but that method is inferior to the native Windows build (see Chapter 17) and running a server under Cygwin is no longer recommended.

When building from source, proceed according to the normal installation procedure (i.e., ./configure; make; etc.), noting the following-Cygwin specific differences:

• Set your path to use the Cygwin bin directory before the Windows utilities. This will help prevent problems with compilation.

• The adduser command is not supported; use the appropriate user management application on Windows NT, 2000, or XP. Otherwise, skip this step.

• The su command is not supported; use ssh to simulate su on Windows NT, 2000, or XP. Otherwise, skip this step.

• OpenSSL is not supported.

• Start cygserver for shared memory support. To do this, enter the command /usr/sbin/cygserver &. This program needs to be running anytime you start the PostgreSQL server or initialize a database cluster (initdb). The default cygserver configuration may need to be changed (e.g., increase SEMMNS) to prevent PostgreSQL from failing due to a lack of system resources.

• Building might fail on some systems where a locale other than C is in use. To fix this, set the locale to C by doing export LANG=C.utf8 before building, and then setting it back to the previous setting, after you have installed PostgreSQL.

• The parallel regression tests (make check) can generate spurious regression test failures due to overflowing the listen() backlog queue which causes connection refused errors or hangs. You can limit the number of connections using the make variableMAX_CONNECTIONS thus:

  Copy

  make MAX_CONNECTIONS=5 check

  (On some systems you can have up to about 10 simultaneous connections).

It is possible to install cygserver and the PostgreSQL server as Windows NT services. For information on how to do this, please refer to the README document included with the PostgreSQL binary package on Cygwin. It is installed in the directory /usr/share/doc/Cygwin.

16.7.3. HP-UX

PostgreSQL 7.3+ should work on Series 700/800 PA-RISC machines running HP-UX 10.X or 11.X, given appropriate system patch levels and build tools. At least one developer routinely tests on HP-UX 10.20, and we have reports of successful installations on HP-UX 11.00 and 11.11.

Aside from the PostgreSQL source distribution, you will need GNU make (HP's make will not do), and either GCC or HP's full ANSI C compiler. If you intend to build from Git sources rather than a distribution tarball, you will also need Flex (GNU lex) and Bison (GNU yacc). We also recommend making sure you are fairly up-to-date on HP patches. At a minimum, if you are building 64 bit binaries on HP-UX 11.11 you may need PHSS_30966 (11.11) or a successor patch otherwise initdb may hang:

PHSS_30966 s700_800 ld(1) and linker tools cumulative patch

On general principles you should be current on libc and ld/dld patches, as well as compiler patches if you are using HP's C compiler. See HP's support sites such as ftp://us-ffs.external.hp.com/ for free copies of their latest patches.

If you are building on a PA-RISC 2.0 machine and want to have 64-bit binaries using GCC, you must use a GCC 64-bit version.

If you are building on a PA-RISC 2.0 machine and want the compiled binaries to run on PA-RISC 1.1 machines you will need to specify +DAportable in CFLAGS.

If you are building on a HP-UX Itanium machine, you will need the latest HP ANSI C compiler with its dependent patch or successor patches:

PHSS_30848 s700_800 HP C Compiler (A.05.57) PHSS_30849 s700_800 u2comp/be/plugin library Patch

If you have both HP's C compiler and GCC's, then you might want to explicitly select the compiler to use when you run configure:

./configure CC=cc

for HP's C compiler, or

./configure CC=gcc

for GCC. If you omit this setting, then configure will pick gcc if it has a choice.

The default install target location is /usr/local/pgsql, which you might want to change to something under /opt. If so, use the --prefix switch to configure.

In the regression tests, there might be some low-order-digit differences in the geometry tests, which vary depending on which compiler and math library versions you use. Any other error is cause for suspicion.

16.7.4. MinGW/Native Windows

PostgreSQL for Windows can be built using MinGW, a Unix-like build environment for Microsoft operating systems, or using Microsoft's Visual C++ compiler suite. The MinGW build variant uses the normal build system described in this chapter; the Visual C++ build works completely differently and is described in Chapter 17. It is a fully native build and uses no additional software like MinGW. A ready-made installer is available on the main PostgreSQL web site.

The native Windows port requires a 32 or 64-bit version of Windows 2000 or later. Earlier operating systems do not have sufficient infrastructure (but Cygwin may be used on those). MinGW, the Unix-like build tools, and MSYS, a collection of Unix tools required to run shell scripts like configure, can be downloaded from http://www.mingw.org/. Neither is required to run the resulting binaries; they are needed only for creating the binaries.

To build 64 bit binaries using MinGW, install the 64 bit tool set from http://mingw-w64.sourceforge.net/, put its bin directory in the PATH, and run configure with the --host=x86_64-w64-mingw32 option.

After you have everything installed, it is suggested that you run psql under CMD.EXE, as the MSYS console has buffering issues.

16.7.4.1. Collecting Crash Dumps on Windows

If PostgreSQL on Windows crashes, it has the ability to generate minidumps that can be used to track down the cause for the crash, similar to core dumps on Unix. These dumps can be read using the Windows Debugger Tools or using Visual Studio. To enable the generation of dumps on Windows, create a subdirectory named crashdumps inside the cluster data directory. The dumps will then be written into this directory with a unique name based on the identifier of the crashing process and the current time of the crash.

16.7.5. Solaris

PostgreSQL is well-supported on Solaris. The more up to date your operating system, the fewer issues you will experience; details below.

16.7.5.1. Required Tools

You can build with either GCC or Sun's compiler suite. For better code optimization, Sun's compiler is strongly recommended on the SPARC architecture. We have heard reports of problems when using GCC 2.95.1; GCC 2.95.3 or later is recommended. If you are using Sun's compiler, be careful not to select /usr/ucb/cc; use /opt/SUNWspro/bin/cc.

You can download Sun Studio from http://www.oracle.com/technetwork/server-storage/solarisstudio/downloads/. Many of GNU tools are integrated into Solaris 10, or they are present on the Solaris companion CD. If you like packages for older version of Solaris, you can find these tools at http://www.sunfreeware.com. If you prefer sources, look at http://www.gnu.org/order/ftp.html.

16.7.5.2. configure Complains About a Failed Test Program

If configure complains about a failed test program, this is probably a case of the run-time linker being unable to find some library, probably libz, libreadline or some other non-standard library such as libssl. To point it to the right location, set the LDFLAGS environment variable on the configure command line, e.g.,

configure ... LDFLAGS="-R /usr/sfw/lib:/opt/sfw/lib:/usr/local/lib"

See the ld man page for more information.

16.7.5.3. 64-bit Build Sometimes Crashes

On Solaris 7 and older, the 64-bit version of libc has a buggy vsnprintf routine, which leads to erratic core dumps in PostgreSQL. The simplest known workaround is to force PostgreSQL to use its own version of vsnprintf rather than the library copy. To do this, after you run configure edit a file produced by configure: In src/Makefile.global, change the line

LIBOBJS =

to read

LIBOBJS = snprintf.o

(There might be other files already listed in this variable. Order does not matter.) Then build as usual.

16.7.5.4. Compiling for Optimal Performance

On the SPARC architecture, Sun Studio is strongly recommended for compilation. Try using the -xO5 optimization flag to generate significantly faster binaries. Do not use any flags that modify behavior of floating-point operations and errno processing (e.g., -fast). These flags could raise some nonstandard PostgreSQL behavior for example in the date/time computing.

If you do not have a reason to use 64-bit binaries on SPARC, prefer the 32-bit version. The 64-bit operations are slower and 64-bit binaries are slower than the 32-bit variants. And on other hand, 32-bit code on the AMD64 CPU family is not native, and that is why 32-bit code is significant slower on this CPU family.

16.7.5.5. Using DTrace for Tracing PostgreSQL

Yes, using DTrace is possible. See Section 28.5 for further information.

If you see the linking of the postgres executable abort with an error message like:

Undefined                       first referenced
 symbol                             in file
AbortTransaction                    utils/probes.o
CommitTransaction                   utils/probes.o
ld: fatal: Symbol referencing errors. No output written to postgres
collect2: ld returned 1 exit status
make: *** [postgres] Error 1

your DTrace installation is too old to handle probes in static functions. You need Solaris 10u4 or newer.

In general, a modern Unix-compatible platform should be able to run PostgreSQL. The platforms that had received specific testing at the time of release are listed in Section 16.6 below. In the doc subdirectory of the distribution there are several platform-specific FAQ documents you might wish to consult if you are having trouble.

The following software packages are required for building PostgreSQL:

• GNU make version 3.80 or newer is required; other make programs or older GNU make versions will not work. (GNU make is sometimes installed under the name gmake.) To test for GNU make enter:

  Copy

  make --version

• You need an ISO/ANSI C compiler (at least C89-compliant). Recent versions of GCC are recommended, but PostgreSQL is known to build using a wide variety of compilers from different vendors.

• tar is required to unpack the source distribution, in addition to either gzip or bzip2.

• The GNU Readline library is used by default. It allows psql (the PostgreSQL command line SQL interpreter) to remember each command you type, and allows you to use arrow keys to recall and edit previous commands. This is very helpful and is strongly recommended. If you don't want to use it then you must specify the --without-readline option to configure. As an alternative, you can often use the BSD-licensed libedit library, originally developed on NetBSD. The libedit library is GNU Readline-compatible and is used if libreadline is not found, or if --with-libedit-preferred is used as an option to configure. If you are using a package-based Linux distribution, be aware that you need both the readline and readline-devel packages, if those are separate in your distribution.

• The zlib compression library is used by default. If you don't want to use it then you must specify the --without-zlib option to configure. Using this option disables support for compressed archives in pg_dump andpg_restore.

The following packages are optional. They are not required in the default configuration, but they are needed when certain build options are enabled, as explained below:

• To build the server programming language PL/Perl you need a full Perl installation, including the libperl library and the header files. The minimum required version is Perl 5.8.3. Since PL/Perl will be a shared library, the libperl library must be a shared library also on most platforms. This appears to be the default in recent Perl versions, but it was not in earlier versions, and in any case it is the choice of whomever installed Perl at your site. configure will fail if building PL/Perl is selected but it cannot find a shared libperl. In that case, you will have to rebuild and install Perl manually to be able to build PL/Perl. During the configuration process for Perl, request a shared library.

  If you intend to make more than incidental use of PL/Perl, you should ensure that the Perl installation was built with the usemultiplicity option enabled (perl -V will show whether this is the case).

• To build the PL/Python server programming language, you need a Python installation with the header files and the distutils module. The minimum required version is Python 2.4. Python 3 is supported if it's version 3.1 or later; but see Section 45.1 when using Python 3.

  Since PL/Python will be a shared library, the libpython library must be a shared library also on most platforms. This is not the case in a default Python installation built from source, but a shared library is available in many operating system distributions. configure will fail if building PL/Python is selected but it cannot find a shared libpython. That might mean that you either have to install additional packages or rebuild (part of) your Python installation to provide this shared library. When building from source, run Python's configure with the --enable-shared flag.

• To build the PL/Tcl procedural language, you of course need a Tcl installation. The minimum required version is Tcl 8.4.

• To enable Native Language Support (NLS), that is, the ability to display a program's messages in a language other than English, you need an implementation of the Gettext API. Some operating systems have this built-in (e.g., Linux, NetBSD, Solaris), for other systems you can download an add-on package from http://www.gnu.org/software/gettext/. If you are using the Gettext implementation in the GNU C library then you will additionally need the GNU Gettext package for some utility programs. For any of the other implementations you will not need it.

• You need OpenSSL, if you want to support encrypted client connections. The minimum required version is 0.9.8.

• You need Kerberos, OpenLDAP, and/or PAM, if you want to support authentication using those services.

• To build the PostgreSQL documentation, there is a separate set of requirements; see Section J.2.

If you are building from a Git tree instead of using a released source package, or if you want to do server development, you also need the following packages:

• GNU Flex and Bison are needed to build from a Git checkout, or if you changed the actual scanner and parser definition files. If you need them, be sure to get Flex 2.5.31 or later and Bison 1.875 or later. Other lexand yacc programs cannot be used.

• Perl 5.8.3 or later is needed to build from a Git checkout, or if you changed the input files for any of the build steps that use Perl scripts. If building on Windows you will need Perl in any case. Perl is also required to run some test suites.

If you need to get a GNU package, you can find it at your local GNU mirror site (see http://www.gnu.org/order/ftp.html for a list) or at ftp://ftp.gnu.org/gnu/.

Also check that you have sufficient disk space. You will need about 100 MB for the source tree during compilation and about 20 MB for the installation directory. An empty database cluster takes about 35 MB; databases take about five times the amount of space that a flat text file with the same data would take. If you are going to run the regression tests you will temporarily need up to an extra 150 MB. Use the df command to check free disk space.

本章討論如何設定和運行資料庫伺服器及其與作業系統的互動。

與外部世界可存取的任何伺服器背景程序一樣，建議在單獨的使用者帳戶下運行 PostgreSQL。此使用者帳戶應僅擁有由伺服器管理的資料，不應與其他背景程序共享。（例如，使用使用者 nobody 就是個壞主意。）安裝此使用者所擁有的可執行檔案不可取，因為有漏洞的系統可以修改它們自己的可執行檔案。

要將 Unix 使用者帳號加到系統中，請查詢指令 useradd 或 adduser。使用者名稱 postgres 經常被使用，也在本使用手冊中被假定，但如果你想要，也可以使用其他名字。

本章介紹使用原始碼安裝 PostgreSQL。（如果您正在安裝預先封裝的發行版，例如 RPM 或 Debian 套件，請忽略本章並改為閱讀套件程序的說明。）

The PostgreSQL 10.5 sources can be obtained from the download section of our website: . You should get a file named postgresql-10.5.tar.gz or postgresql-10.5.tar.bz2. After you have obtained the file, unpack it:

(Use bunzip2 instead of gunzip if you have the .bz2 file.) This will create a directory postgresql-10.5 under the current directory with the PostgreSQL sources. Change into that directory for the rest of the installation procedure.

You can also get the source directly from the version control repository, see .

It is recommended that most users download the binary distribution for Windows, available as a graphical installer package from the PostgreSQL website. Building from source is only intended for people developing PostgreSQL or extensions.

There are several different ways of building PostgreSQL on Windows. The simplest way to build with Microsoft tools is to install Visual Studio Express 2017 for Windows Desktop and use the included compiler. It is also possible to build with the full Microsoft Visual C++ 2005 to 2017. In some cases that requires the installation of the Windows SDK in addition to the compiler.

It is also possible to build PostgreSQL using the GNU compiler tools provided by MinGW, or using Cygwin for older versions of Windows.

Building using MinGW or Cygwin uses the normal build system, see and the specific notes in and . To produce native 64 bit binaries in these environments, use the tools from MinGW-w64. These tools can also be used to cross-compile for 32 bit and 64 bit Windows targets on other hosts, such as Linux and macOS. Cygwin is not recommended for running a production server, and it should only be used for running on older versions of Windows where the native build does not work, such as Windows 98. The official binaries are built using Visual Studio.

Native builds of psql don't support command line editing. The Cygwin build does support command line editing, so it should be used where psql is needed for interactive use on Windows.

16.5.1. Shared Libraries

On some systems with shared libraries you need to tell the system how to find the newly installed shared libraries. The systems on which this is not necessary include FreeBSD, HP-UX, Linux, NetBSD, OpenBSD, and Solaris.

The method to set the shared library search path varies between platforms, but the most widely-used method is to set the environment variable LD_LIBRARY_PATH like so: In Bourne shells (sh, ksh, bash, zsh):

or in csh or tcsh:

Replace /usr/local/pgsql/lib with whatever you set --libdir to in . You should put these commands into a shell start-up file such as /etc/profile or ~/.bash_profile. Some good information about the caveats associated with this method can be found at .

On some systems it might be preferable to set the environment variable LD_RUN_PATH before building.

On Cygwin, put the library directory in the PATH or move the .dll files into the bin directory.

If in doubt, refer to the manual pages of your system (perhaps ld.so or rld). If you later get a message like:

then this step was necessary. Simply take care of it then.

If you are on Linux and you have root access, you can run:

(or equivalent directory) after installation to enable the run-time linker to find the shared libraries faster. Refer to the manual page of ldconfig for more information. On FreeBSD, NetBSD, and OpenBSD the command is:

instead. Other systems are not known to have an equivalent command.

16.5.2. Environment Variables

To do this, add the following to your shell start-up file, such as ~/.bash_profile (or /etc/profile, if you want it to affect all users):

If you are using csh or tcsh, then use this command:

To enable your system to find the man documentation, you need to add lines like the following to a shell start-up file unless you installed into a location that is searched by default:

The environment variables PGHOST and PGPORT specify to client applications the host and port of the database server, overriding the compiled-in defaults. If you are going to run client applications remotely then it is convenient if every user that plans to use the database sets PGHOST. This is not required, however; the settings can be communicated via command line options to most client programs.

While the server is running, it is not possible for a malicious user to take the place of the normal database server. However, when the server is down, it is possible for a local user to spoof the normal server by starting their own server. The spoof server could read passwords and queries sent by clients, but could not return any data because the PGDATA directory would still be secure because of directory permissions. Spoofing is possible because any user can start a database server; a client cannot identify an invalid server unless it is specially configured.

One way to prevent spoofing of local connections is to use a Unix domain socket directory (unix_socket_directories) that has write permission only for a trusted local user. This prevents a malicious user from creating their own socket file in that directory. If you are concerned that some applications might still reference /tmp for the socket file and hence be vulnerable to spoofing, during operating system startup create a symbolic link /tmp/.s.PGSQL.5432 that points to the relocated socket file. You also might need to modify your /tmpcleanup script to prevent removal of the symbolic link.

Another option for local connections is for clients to use requirepeer to specify the required owner of the server process connected to the socket.

To prevent spoofing on TCP connections, the best solution is to use SSL certificates and make sure that clients check the server's certificate. To do that, the server must be configured to accept only hostssl connections (Section 20.1) and have SSL key and certificate files (Section 18.9). The TCP client must connect using sslmode=verify-ca or verify-full and have the appropriate root certificate file installed (Section 33.18.1).

有許多設定參數會影響資料庫系統的行為。在本章的第一部分中，我們將介紹如何瞭解如何設定參數。接下來的部分將詳細討論每個參數。

PostgreSQL can sometimes exhaust various operating system resource limits, especially when multiple copies of the server are running on the same system, or in very large installations. This section explains the kernel resources used by PostgreSQL and the steps you can take to resolve problems related to kernel resource consumption.

18.4.1. Shared Memory and Semaphores

PostgreSQL requires the operating system to provide inter-process communication (IPC) features, specifically shared memory and semaphores. Unix-derived systems typically provide “System V” IPC, “POSIX” IPC, or both. Windows has its own implementation of these features and is not discussed here.

The complete lack of these facilities is usually manifested by an “Illegal system call” error upon server start. In that case there is no alternative but to reconfigure your kernel. PostgreSQL won't work without them. This situation is rare, however, among modern operating systems.

Upon starting the server, PostgreSQL normally allocates a very small amount of System V shared memory, as well as a much larger amount of POSIX (mmap) shared memory. In addition a significant number of semaphores, which can be either System V or POSIX style, are created at server startup. Currently, POSIX semaphores are used on Linux and FreeBSD systems while other platforms use System V semaphores.

Note

Prior to PostgreSQL 9.3, only System V shared memory was used, so the amount of System V shared memory required to start the server was much larger. If you are running an older version of the server, please consult the documentation for your server version.

System V IPC features are typically constrained by system-wide allocation limits. When PostgreSQL exceeds one of these limits, the server will refuse to start and should leave an instructive error message describing the problem and what to do about it. (See also .) The relevant kernel parameters are named consistently across different systems; gives an overview. The methods to set them, however, vary. Suggestions for some platforms are given below.

Table 18.1. System V IPC Parameters

PostgreSQL requires a few bytes of System V shared memory (typically 48 bytes, on 64-bit platforms) for each copy of the server. On most modern operating systems, this amount can easily be allocated. However, if you are running many copies of the server, or if other applications are also using System V shared memory, it may be necessary to increase SHMALL, which is the total amount of System V shared memory system-wide. Note that SHMALL is measured in pages rather than bytes on many systems.

Less likely to cause problems is the minimum size for shared memory segments (SHMMIN), which should be at most approximately 32 bytes for PostgreSQL (it is usually just 1). The maximum number of segments system-wide (SHMMNI) or per-process (SHMSEG) are unlikely to cause a problem unless your system has them set to zero.

In some cases it might also be necessary to increase SEMMAP to be at least on the order of SEMMNS. This parameter defines the size of the semaphore resource map, in which each contiguous block of available semaphores needs an entry. When a semaphore set is freed it is either added to an existing entry that is adjacent to the freed block or it is registered under a new map entry. If the map is full, the freed semaphores get lost (until reboot). Fragmentation of the semaphore space could over time lead to fewer available semaphores than there should be.

Various other settings related to “semaphore undo”, such as SEMMNU and SEMUME, do not affect PostgreSQL.

At least as of version 5.1, it should not be necessary to do any special configuration for such parameters as SHMMAX, as it appears this is configured to allow all memory to be used as shared memory. That is the sort of configuration commonly used for other databases such as DB/2.

It might, however, be necessary to modify the global ulimit information in /etc/security/limits, as the default hard limits for file sizes (fsize) and numbers of files (nofiles) might be too low.FreeBSD

The default settings can be changed using the sysctl or loader interfaces. The following parameters can be set using sysctl:

To make these settings persist over reboots, modify /etc/sysctl.conf.

These semaphore-related settings are read-only as far as sysctl is concerned, but can be set in /boot/loader.conf:

After modifying these values a reboot is required for the new settings to take effect. (Note: FreeBSD does not use SEMMAP. Older versions would accept but ignore a setting for kern.ipc.semmap; newer versions reject it altogether.)

You might also want to configure your kernel to lock shared memory into RAM and prevent it from being paged out to swap. This can be accomplished using the sysctl setting kern.ipc.shm_use_phys.

If running in FreeBSD jails by enabling sysctl's security.jail.sysvipc_allowed, postmasters running in different jails should be run by different operating system users. This improves security because it prevents non-root users from interfering with shared memory or semaphores in different jails, and it allows the PostgreSQL IPC cleanup code to function properly. (In FreeBSD 6.0 and later the IPC cleanup code does not properly detect processes in other jails, preventing the running of postmasters on the same port in different jails.)

FreeBSD versions before 4.0 work like OpenBSD (see below).NetBSD

In NetBSD 5.0 and later, IPC parameters can be adjusted using sysctl, for example:

To have these settings persist over reboots, modify /etc/sysctl.conf.

You might also want to configure your kernel to lock shared memory into RAM and prevent it from being paged out to swap. This can be accomplished using the sysctl setting kern.ipc.shm_use_phys.

NetBSD versions before 5.0 work like OpenBSD (see below), except that parameters should be set with the keyword options not option.OpenBSD

The options SYSVSHM and SYSVSEM need to be enabled when the kernel is compiled. (They are by default.) The maximum size of shared memory is determined by the option SHMMAXPGS (in pages). The following shows an example of how to set the various parameters:

You might also want to configure your kernel to lock shared memory into RAM and prevent it from being paged out to swap. This can be accomplished using the sysctl setting kern.ipc.shm_use_phys.HP-UX

The default settings tend to suffice for normal installations. On HP-UX 10, the factory default for SEMMNS is 128, which might be too low for larger database sites.

IPC parameters can be set in the System Administration Manager (SAM) under Kernel Configuration → Configurable Parameters. Choose Create A New Kernel when you're done.Linux

The default maximum segment size is 32 MB, and the default maximum total size is 2097152 pages. A page is almost always 4096 bytes except in unusual kernel configurations with “huge pages” (use getconf PAGE_SIZE to verify).

The shared memory size settings can be changed via the sysctl interface. For example, to allow 16 GB:

In addition these settings can be preserved between reboots in the file /etc/sysctl.conf. Doing that is highly recommended.

Ancient distributions might not have the sysctl program, but equivalent changes can be made by manipulating the /proc file system:

The remaining defaults are quite generously sized, and usually do not require changes.macOS

The recommended method for configuring shared memory in macOS is to create a file named /etc/sysctl.conf, containing variable assignments such as:

Note that in some macOS versions, all five shared-memory parameters must be set in /etc/sysctl.conf, else the values will be ignored.

Beware that recent releases of macOS ignore attempts to set SHMMAX to a value that isn't an exact multiple of 4096.

SHMALL is measured in 4 kB pages on this platform.

In older macOS versions, you will need to reboot to have changes in the shared memory parameters take effect. As of 10.5 it is possible to change all but SHMMNI on the fly, using sysctl. But it's still best to set up your preferred values via /etc/sysctl.conf, so that the values will be kept across reboots.

The file /etc/sysctl.conf is only honored in macOS 10.3.9 and later. If you are running a previous 10.3.x release, you must edit the file /etc/rc and change the values in the following commands:

Note that /etc/rc is usually overwritten by macOS system updates, so you should expect to have to redo these edits after each update.

In macOS 10.2 and earlier, instead edit these commands in the file /System/Library/StartupItems/SystemTuning/SystemTuning.Solaris 2.6 to 2.9 (Solaris 6 to Solaris 9)

The relevant settings can be changed in /etc/system, for example:

In Solaris 10 and later, and OpenSolaris, the default shared memory and semaphore settings are good enough for most PostgreSQL applications. Solaris now defaults to a SHMMAXof one-quarter of system RAM. To further adjust this setting, use a project setting associated with the postgres user. For example, run the following as root:

This command adds the user.postgres project and sets the shared memory maximum for the postgres user to 8GB, and takes effect the next time that user logs in, or when you restart PostgreSQL (not reload). The above assumes that PostgreSQL is run by the postgres user in the postgres group. No server reboot is required.

Other recommended kernel setting changes for database servers which will have a large number of connections are:

Additionally, if you are running PostgreSQL inside a zone, you may need to raise the zone resource usage limits as well. See "Chapter2: Projects and Tasks" in the System Administrator's Guide for more information on projects and prctl.

18.4.2. systemd RemoveIPC

If systemd is in use, some care must be taken that IPC resources (shared memory and semaphores) are not prematurely removed by the operating system. This is especially of concern when installing PostgreSQL from source. Users of distribution packages of PostgreSQL are less likely to be affected, as the postgres user is then normally created as a system user.

The setting RemoveIPC in logind.conf controls whether IPC objects are removed when a user fully logs out. System users are exempt. This setting defaults to on in stock systemd, but some operating system distributions default it to off.

A typical observed effect when this setting is on is that the semaphore objects used by a PostgreSQL server are removed at apparently random times, leading to the server crashing with log messages like

Different types of IPC objects (shared memory vs. semaphores, System V vs. POSIX) are treated slightly differently by systemd, so one might observe that some IPC resources are not removed in the same way as others. But it is not advisable to rely on these subtle differences.

A “user logging out” might happen as part of a maintenance job or manually when an administrator logs in as the postgres user or something similar, so it is hard to prevent in general.

What is a “system user” is determined at systemd compile time from the SYS_UID_MAX setting in /etc/login.defs.

Packaging and deployment scripts should be careful to create the postgres user as a system user by using useradd -r, adduser --system, or equivalent.

Alternatively, if the user account was created incorrectly or cannot be changed, it is recommended to set

in /etc/systemd/logind.conf or another appropriate configuration file.

Caution

At least one of these two things has to be ensured, or the PostgreSQL server will be very unreliable.

18.4.3. Resource Limits

Unix-like operating systems enforce various kinds of resource limits that might interfere with the operation of your PostgreSQL server. Of particular importance are limits on the number of processes per user, the number of open files per process, and the amount of memory available to each process. Each of these have a “hard” and a “soft” limit. The soft limit is what actually counts but it can be changed by the user up to the hard limit. The hard limit can only be changed by the root user. The system call setrlimit is responsible for setting these parameters. The shell's built-in command ulimit (Bourne shells) or limit (csh) is used to control the resource limits from the command line. On BSD-derived systems the file /etc/login.conf controls the various resource limits set during login. See the operating system documentation for details. The relevant parameters are maxproc, openfiles, and datasize. For example:

(-cur is the soft limit. Append -max to set the hard limit.)

Kernels can also have system-wide limits on some resources.

• On Linux /proc/sys/fs/file-max determines the maximum number of open files that the kernel will support. It can be changed by writing a different number into the file or by adding an assignment in /etc/sysctl.conf. The maximum limit of files per process is fixed at the time the kernel is compiled; see /usr/src/linux/Documentation/proc.txt for more information.

The PostgreSQL server uses one process per connection so you should provide for at least as many processes as allowed connections, in addition to what you need for the rest of your system. This is usually not a problem but if you run several servers on one machine things might get tight.

The factory default limit on open files is often set to “socially friendly” values that allow many users to coexist on a machine without using an inappropriate fraction of the system resources. If you run many servers on a machine this is perhaps what you want, but on dedicated servers you might want to raise this limit.

18.4.4. Linux Memory Overcommit

In Linux 2.4 and later, the default virtual memory behavior is not optimal for PostgreSQL. Because of the way that the kernel implements memory overcommit, the kernel might terminate the PostgreSQL postmaster (the master server process) if the memory demands of either PostgreSQL or another process cause the system to run out of virtual memory.

If this happens, you will see a kernel message that looks like this (consult your system documentation and configuration on where to look for such a message):

This indicates that the postgres process has been terminated due to memory pressure. Although existing database connections will continue to function normally, no new connections will be accepted. To recover, PostgreSQL will need to be restarted.

One way to avoid this problem is to run PostgreSQL on a machine where you can be sure that other processes will not run the machine out of memory. If memory is tight, increasing the swap space of the operating system can help avoid the problem, because the out-of-memory (OOM) killer is invoked only when physical memory and swap space are exhausted.

Another approach, which can be used with or without altering vm.overcommit_memory, is to set the process-specific OOM score adjustment value for the postmaster process to -1000, thereby guaranteeing it will not be targeted by the OOM killer. The simplest way to do this is to execute

in the postmaster's startup script just before invoking the postmaster. Note that this action must be done as root, or it will have no effect; so a root-owned startup script is the easiest place to do it. If you do this, you should also set these environment variables in the startup script before invoking the postmaster:

These settings will cause postmaster child processes to run with the normal OOM score adjustment of zero, so that the OOM killer can still target them at need. You could use some other value for PG_OOM_ADJUST_VALUE if you want the child processes to run with some other OOM score adjustment. (PG_OOM_ADJUST_VALUE can also be omitted, in which case it defaults to zero.) If you do not set PG_OOM_ADJUST_FILE, the child processes will run with the same OOM score adjustment as the postmaster, which is unwise since the whole point is to ensure that the postmaster has a preferential setting.

Older Linux kernels do not offer /proc/self/oom_score_adj, but may have a previous version of the same functionality called /proc/self/oom_adj. This works the same except the disable value is -17 not -1000.

Note

Some vendors' Linux 2.4 kernels are reported to have early versions of the 2.6 overcommit sysctl parameter. However, setting vm.overcommit_memory to 2 on a 2.4 kernel that does not have the relevant code will make things worse, not better. It is recommended that you inspect the actual kernel source code (see the function vm_enough_memory in the file mm/mmap.c) to verify what is supported in your kernel before you try this in a 2.4 installation. The presence of the overcommit-accounting documentation file should not be taken as evidence that the feature is there. If in any doubt, consult a kernel expert or your kernel vendor.

18.4.5. Linux Huge Pages

6490428 / 2048 gives approximately 3169.154, so in this example we need at least 3170 huge pages, which we can set with:

A larger setting would be appropriate if other programs on the machine also need huge pages. Don't forget to add this setting to /etc/sysctl.conf so that it will be reapplied after reboots.

Sometimes the kernel is not able to allocate the desired number of huge pages immediately, so it might be necessary to repeat the command or to reboot. (Immediately after a reboot, most of the machine's memory should be available to convert into huge pages.) To verify the huge page allocation situation, use:

It may also be necessary to give the database server's operating system user permission to use huge pages by setting vm.hugetlb_shm_group via sysctl, and/or give permission to lock memory with ulimit -l.

1. 組態設定

安裝過程的第一步是設定原始碼編譯時所需的選項。這是透過執行 configure 腳本完成的。對於預設安裝而言，只需輸入：

此腳本將執行許多測試以確定各種系統相關變數的值，並檢測操作業系統的所有特性，最後將在編譯樹中建立多個檔案以記錄它找到的內容。如果要將編譯目錄分開，也可以在原始碼以外的目錄中執行 configure。此過程也稱為 VPATH 編譯。可以這樣做：

預設配置將編譯伺服器和工具程式，以及僅需要 C 編譯器的所有用戶端應用程式和介存取介面。預設情況下，所有檔案都將安裝在 /usr/local/pgsql 下。

您可以透過提供以下一個或多個命令列選項來自訂編譯的過程：

--prefix=PREFIX

安裝所以檔案到到目錄 PREFIX 下而不是 /usr/local/pgsql。實際檔案將安裝到各個子目錄中；任何檔案都不會直接安裝到 PREFIX 目錄中。

如果您有特殊需求，還可以使用以下選項自訂各個子目錄。但是，如果保留這些預設值，則安裝結果將是可重新配置的，這意味著您可以在安裝後移動目錄。（man 和 doc 路徑不受此影響。）

對於可重新配置的安裝，您可能希望使用 configure 的 --disable-rpath 選項。此外，您需要告訴作業系統如何尋找共享函式庫。

--exec-prefix=EXEC-PREFIX

您可以在與 PREFIX 設定的前綴不同的前綴 EXEC-PREFIX 下安裝相依於系統結構的檔案。這對於在主機之間共享與系統結構無關的檔案非常有用。如果省略這一點，則 EXEC-PREFIX 設定為等於 PREFIX，並且相依於系統結構的檔案和獨立檔案都將安裝在同一個樹下，這可能就是您想要的。

--bindir=DIRECTORY

指定可執行程式的目錄。預設值為 EXEC-PREFIX/bin，通常為 /usr/local/pgsql/bin。

--sysconfdir=DIRECTORY

預設設定各種組態配置檔案的目錄，PREFIX/etc。

--libdir=DIRECTORY

設定安裝函式庫和動態模組的位置。預設值為 EXEC-PREFIX/lib。

--includedir=DIRECTORY

設定安裝 C 和 C++ 標頭檔案的目錄。預設值為 PREFIX/include。

--datarootdir=DIRECTORY

設定各種類型的唯讀資料檔案的根目錄。這僅設定以下某些選項的預設值。預設值為 PREFIX/share。

--datadir=DIRECTORY

設定安裝好的程式所使用的唯讀資料檔案目錄。預設值為 DATAROOTDIR。請注意，這與放置資料庫檔案的位置無關。

--localedir=DIRECTORY

設定用於安裝區域設定資料的目錄，像是訊息翻譯的目錄檔案。預設值為 DATAROOTDIR/locale。

--mandir=DIRECTORY

PostgreSQL 附帶的手冊頁面將安裝在此目錄下的各自 manx 子目錄中。預設值為 DATAROOTDIR/man。

--docdir=DIRECTORY

設定安裝文件檔案的根目錄，“man” 頁面除外。這僅設定以下選項的預設值。此選項的預設值為 DARAROOTDIR/doc/postgresql。

--htmldir=DIRECTORY

PostgreSQL 的 HTML 格式文件檔案將安裝在此目錄下。預設值為 DATAROOTDIR。

注意 可以將 PostgreSQL 安裝到共享安裝位置（例如 /usr/local/include），而不會干擾系統其餘部分的命名空間。首先，字串 “/postgresql” 會自動附加到 datadir，sysconfdir 和docdir，除非完全展開的目錄名已包含字串 “postgres” 或 “pgsql”。例如，如果選擇 /usr/local 作為前綴，則檔案將安裝在 /usr/local/doc/postgresql 中，但如果前綴為 /opt/postgres，則它將位於 /opt/postgres/doc 中。用戶端介面的公用 C 標頭檔案安裝在 includedir 中，並且命名空間是清楚的。內部標頭檔案和伺服器標頭檔案安裝在 includedir 下的私有目錄中。有關如何存取其標頭檔案的訊息，請參閱每個介面的文件檔案。最後，如果可以的話，還將在 libdir 下為可動態載入的模組建立一個私有的子目錄。

--with-extra-version=STRING

將 STRING 附加到 PostgreSQL 版本號。例如，您可以使用此標記來標記從未發布的 Git 快照所編譯的二進位檔案，或者包含帶有額外版本字串的自訂修補程式，例如 git describe 識別字或某個發行套裝的版本號碼。

--with-includes=DIRECTORIES

DIRECTORIES 是一個以冒號分隔的目錄列表，它們將加到編譯器搜尋標頭檔案的列表中。如果您在非標準路徑安裝了選擇性套件（例如GNU Readline），則必須使用此選項，並且可能還需要設定相對應的 --with-libraries 選項。

例如： --with-includes=/opt/gnu/include:/usr/sup/include

--with-libraries=DIRECTORIES

DIRECTORIES 是一個以冒號分隔的目錄列表，用於搜尋函式庫。如果您在非標準路徑安裝了某些軟體套件，則可能必須使用此選項（以及相對應的 --with-includes 選項）。

例如： --with-libraries=/opt/gnu/lib:/usr/sup/lib

--enable-nls[=LANGUAGES]

啟用內建語言支援（NLS），即以英語以外的語言顯示程式訊息的功能。 LANGUAGES 是您希望支援語言代碼的選擇性空格分隔列表，例如 --enable-nls ='de fr'。（將自動計算列表與實際提供的翻譯集之間的交集。）如果未指定列表，則會安裝所有可用的翻譯。

要使用此選項，您需要實作 Gettext API；如上所述。

--with-pgport=NUMBER

將 NUMBER 設定為伺服器和用戶端的預設連接埠號碼。預設值為 5432。之後可以隨時更改連接埠，但如果在此處指定端口，則伺服器和用戶端都將具有相同的預設編譯，這會非常方便。通常，選擇非預設值的唯一理由是，您打算在同一台機器上執行多個 PostgreSQL 伺服器。

--with-perl

編譯 PL / Perl 伺服器端語言。

--with-python

編譯 PL / Python 伺服器端語言。

--with-tcl

編譯 PL / Tcl 伺服器端語言。

--with-tclconfig=DIRECTORY

Tcl 安裝檔案 tclConfig.sh，其中包含編譯與 Tcl 介面模組所需的組態資訊。此檔案通常在一個眾所周知的路徑中自動找到，但如果您想使用不同版本的 Tcl，則可以指定搜尋它的目錄。

--with-gssapi

Build with support for GSSAPI authentication. On many systems, the GSSAPI (usually a part of the Kerberos installation) system is not installed in a location that is searched by default (e.g.,/usr/include, /usr/lib), so you must use the options --with-includes and --with-libraries in addition to this option. configure will check for the required header files and libraries to make sure that your GSSAPI installation is sufficient before proceeding.

--with-krb-srvnam=NAME

The default name of the Kerberos service principal used by GSSAPI. postgres is the default. There's usually no reason to change this unless you have a Windows environment, in which case it must be set to upper case POSTGRES.

--with-llvm

llvm-config will be used to find the required compilation options. llvm-config, and then llvm-config-$major-$minor for all supported versions, will be searched on PATH. If that would not yield the correct binary, use LLVM_CONFIG to specify a path to the correct llvm-config. For example

LLVM support requires a compatible clang compiler (specified, if necessary, using the CLANG environment variable), and a working C++ compiler (specified, if necessary, using the CXXenvironment variable).

--with-icu

Build with support for the ICU library. This requires the ICU4C package to be installed. The minimum required version of ICU4C is currently 4.2.

By default, pkg-config will be used to find the required compilation options. This is supported for ICU4C version 4.6 and later. For older versions, or if pkg-config is not available, the variables ICU_CFLAGS and ICU_LIBS can be specified to configure, like in this example:

(If ICU4C is in the default search path for the compiler, then you still need to specify a nonempty string in order to avoid use of pkg-config, for example, ICU_CFLAGS=' '.)

--with-openssl

Build with support for SSL (encrypted) connections. This requires the OpenSSL package to be installed. configure will check for the required header files and libraries to make sure that your OpenSSL installation is sufficient before proceeding.

--with-pam

Build with PAM (Pluggable Authentication Modules) support.

--with-bsd-auth

Build with BSD Authentication support. (The BSD Authentication framework is currently only available on OpenBSD.)

--with-ldap

--with-systemd

--without-readline

Prevents use of the Readline library (and libedit as well). This option disables command-line editing and history in psql, so it is not recommended.

--with-libedit-preferred

Favors the use of the BSD-licensed libedit library rather than GPL-licensed Readline. This option is significant only if you have both libraries installed; the default in that case is to use Readline.

--with-bonjour

Build with Bonjour support. This requires Bonjour support in your operating system. Recommended on macOS.

--with-uuid=LIBRARY

• bsd to use the UUID functions found in FreeBSD, NetBSD, and some other BSD-derived systems

• e2fs to use the UUID library created by the e2fsprogs project; this library is present in most Linux systems and in macOS, and can be obtained for other platforms as well

--with-ossp-uuid

Obsolete equivalent of --with-uuid=ossp.

--with-libxml

Build with libxml (enables SQL/XML support). Libxml version 2.6.23 or later is required for this feature.

Libxml installs a program xml2-config that can be used to detect the required compiler and linker options. PostgreSQL will use it automatically if found. To specify a libxml installation at an unusual location, you can either set the environment variable XML2_CONFIG to point to the xml2-config program belonging to the installation, or use the options --with-includes and --with-libraries.--with-libxslt

--disable-float4-byval

Disable passing float4 values “by value”, causing them to be passed “by reference” instead. This option costs performance, but may be needed for compatibility with old user-defined functions that are written in C and use the “version 0” calling convention. A better long-term solution is to update any such functions to use the “version 1” calling convention.

--disable-float8-byval

Disable passing float8 values “by value”, causing them to be passed “by reference” instead. This option costs performance, but may be needed for compatibility with old user-defined functions that are written in C and use the “version 0” calling convention. A better long-term solution is to update any such functions to use the “version 1” calling convention. Note that this option affects not only float8, but also int8 and some related types such as timestamp. On 32-bit platforms, --disable-float8-byval is the default and it is not allowed to select --enable-float8-byval.

--with-segsize=SEGSIZE

Set the segment size, in gigabytes. Large tables are divided into multiple operating-system files, each of size equal to the segment size. This avoids problems with file size limits that exist on many platforms. The default segment size, 1 gigabyte, is safe on all supported platforms. If your operating system has “largefile” support (which most do, nowadays), you can use a larger segment size. This can be helpful to reduce the number of file descriptors consumed when working with very large tables. But be careful not to select a value larger than is supported by your platform and the file systems you intend to use. Other tools you might wish to use, such as tar, could also set limits on the usable file size. It is recommended, though not absolutely required, that this value be a power of 2. Note that changing this value requires an initdb.

--with-blocksize=BLOCKSIZE

Set the block size, in kilobytes. This is the unit of storage and I/O within tables. The default, 8 kilobytes, is suitable for most situations; but other values may be useful in special cases. The value must be a power of 2 between 1 and 32 (kilobytes). Note that changing this value requires an initdb.

--with-wal-blocksize=BLOCKSIZE

Set the WAL block size, in kilobytes. This is the unit of storage and I/O within the WAL log. The default, 8 kilobytes, is suitable for most situations; but other values may be useful in special cases. The value must be a power of 2 between 1 and 64 (kilobytes). Note that changing this value requires an initdb.

--disable-spinlocks

Allow the build to succeed even if PostgreSQL has no CPU spinlock support for the platform. The lack of spinlock support will result in poor performance; therefore, this option should only be used if the build aborts and informs you that the platform lacks spinlock support. If this option is required to build PostgreSQL on your platform, please report the problem to the PostgreSQLdevelopers.

--disable-strong-random

--disable-thread-safety

Disable the thread-safety of client libraries. This prevents concurrent threads in libpq and ECPG programs from safely controlling their private connection handles.

--with-system-tzdata=DIRECTORY

PostgreSQL includes its own time zone database, which it requires for date and time operations. This time zone database is in fact compatible with the IANA time zone database provided by many operating systems such as FreeBSD, Linux, and Solaris, so it would be redundant to install it again. When this option is used, the system-supplied time zone database in DIRECTORY is used instead of the one included in the PostgreSQL source distribution. DIRECTORY must be specified as an absolute path. /usr/share/zoneinfo is a likely directory on some operating systems. Note that the installation routine will not detect mismatching or erroneous time zone data. If you use this option, you are advised to run the regression tests to verify that the time zone data you have pointed to works correctly with PostgreSQL.

This option is mainly aimed at binary package distributors who know their target operating system well. The main advantage of using this option is that the PostgreSQL package won't need to be upgraded whenever any of the many local daylight-saving time rules change. Another advantage is that PostgreSQL can be cross-compiled more straightforwardly if the time zone database files do not need to be built during the installation.

--without-zlib

Prevents use of the Zlib library. This disables support for compressed archives in pg_dump and pg_restore. This option is only intended for those rare systems where this library is not available.

--enable-debug

Compiles all programs and libraries with debugging symbols. This means that you can run the programs in a debugger to analyze problems. This enlarges the size of the installed executables considerably, and on non-GCC compilers it usually also disables compiler optimization, causing slowdowns. However, having the symbols available is extremely helpful for dealing with any problems that might arise. Currently, this option is recommended for production installations only if you use GCC. But you should always have it on if you are doing development work or running a beta version.

--enable-coverage

--enable-profiling

If using GCC, all programs and libraries are compiled so they can be profiled. On backend exit, a subdirectory will be created that contains the gmon.out file for use in profiling. This option is for use only with GCC and when doing development work.

--enable-cassert

Enables assertion checks in the server, which test for many “cannot happen” conditions. This is invaluable for code development purposes, but the tests can slow down the server significantly. Also, having the tests turned on won't necessarily enhance the stability of your server! The assertion checks are not categorized for severity, and so what might be a relatively harmless bug will still lead to server restarts if it triggers an assertion failure. This option is not recommended for production use, but you should have it on for development work or when running a beta version.

--enable-depend

Enables automatic dependency tracking. With this option, the makefiles are set up so that all affected object files will be rebuilt when any header file is changed. This is useful if you are doing development work, but is just wasted overhead if you intend only to compile once and install. At present, this option only works with GCC.

--enable-dtrace

To point to the dtrace program, the environment variable DTRACE can be set. This will often be necessary because dtrace is typically installed under /usr/sbin, which might not be in the path.

Extra command-line options for the dtrace program can be specified in the environment variable DTRACEFLAGS. On Solaris, to include DTrace support in a 64-bit binary, you must specify DTRACEFLAGS="-64" to configure. For example, using the GCC compiler:

Using Sun's compiler:

--enable-tap-tests

If you prefer a C compiler different from the one configure picks, you can set the environment variable CC to the program of your choice. By default, configure will pick gcc if available, else the platform's default (usually cc). Similarly, you can override the default compiler flags if needed with the CFLAGS variable.

You can specify environment variables on the configure command line, for example:

Here is a list of the significant variables that can be set in this manner:

BISON

Bison program

CC

C compiler

CFLAGS

options to pass to the C compiler

CLANG

path to clang program used to process source code for inlining when compiling with --with-llvmCPP

C preprocessor

CPPFLAGS

options to pass to the C preprocessor

CXX

C++ compiler

CXXFLAGS

options to pass to the C++ compiler

DTRACE

location of the dtrace program

DTRACEFLAGS

options to pass to the dtrace program

FLEX

Flex programLDFLAGS

options to use when linking either executables or shared libraries

LDFLAGS_EX

additional options for linking executables only

LDFLAGS_SL

additional options for linking shared libraries only

LLVM_CONFIG

llvm-config program used to locate the LLVM installation.

MSGFMT

msgfmt program for native language support

PERL

Full path name of the Perl interpreter. This will be used to determine the dependencies for building PL/Perl.

PYTHON

TCLSH

Full path name of the Tcl interpreter. This will be used to determine the dependencies for building PL/Tcl, and it will be substituted into Tcl scripts.

XML2_CONFIG

xml2-config program used to locate the libxml installation.

Sometimes it is useful to add compiler flags after-the-fact to the set that were chosen by configure. An important example is that gcc's -Werror option cannot be included in the CFLAGSpassed to configure, because it will break many of configure's built-in tests. To add such flags, include them in the COPT environment variable while running make. The contents ofCOPT are added to both the CFLAGS and LDFLAGS options set up by configure. For example, you could do

or

Note

When developing code inside the server, it is recommended to use the configure options --enable-cassert (which turns on many run-time error checks) and --enable-debug(which improves the usefulness of debugging tools).

If using GCC, it is best to build with an optimization level of at least -O1, because using no optimization (-O0) disables some important compiler warnings (such as the use of uninitialized variables). However, non-zero optimization levels can complicate debugging because stepping through compiled code will usually not match up one-to-one with source code lines. If you get confused while trying to debug optimized code, recompile the specific files of interest with -O0. An easy way to do this is by passing an option to make:

make PROFILE=-O0 file.o.

The COPT and PROFILE environment variables are actually handled identically by the PostgreSQL makefiles. Which to use is a matter of preference, but a common habit among developers is to use PROFILE for one-time flag adjustments, while COPT might be kept set all the time.

2. 編譯

要開始編譯，請輸入以下任一項：

（請使用GNU make。）編譯將花費一些時間，具體取決於您的硬體。顯示的最後一行應該是：

如果要編譯所有可編譯的內容，包括文件（HTML和手冊頁）以及其他模組（contrib），請輸入：

顯示的最後一行應該是：

如果要從另一個 makefile 而不是手動呼叫編譯，則必須取消設定 MAKELEVEL 或將其設定為零，例如：

如果不這樣做可能會導致奇怪的錯誤訊息，通常是缺少標頭檔案。

3. 迴歸測試

如果要在安裝之前測試新編譯的伺服器，則可以在此時執行迴歸測試。迴歸測試是一個測試套件，用於驗證 PostgreSQL 是否以開發人員期望的方式在您的主機上執行。輸入：

4. Installing the Files

Note

To install PostgreSQL enter:

To install the documentation (HTML and man pages), enter:

If you built the world above, type instead:

This also installs the documentation.

You can use make install-strip instead of make install to strip the executable files and libraries as they are installed. This will save some space. If you built with debugging support, stripping will effectively remove the debugging support, so it should only be done if debugging is no longer needed. install-strip tries to do a reasonable job saving space, but it does not have perfect knowledge of how to strip every unneeded byte from an executable file, so if you want to save all the disk space you possibly can, you will have to do manual work.

The standard installation provides all the header files needed for client application development as well as for server-side program development, such as custom functions or data types written in C. (Prior to PostgreSQL 8.0, a separate make install-all-headers command was needed for the latter, but this step has been folded into the standard install.)

Client-only installation: If you want to install only the client applications and interface libraries, then you can use these commands:

src/bin has a few binaries for server-only use, but they are small.

Uninstallation: To undo the installation use the command make uninstall. However, this will not remove any created directories.

Cleaning: After the installation you can free disk space by removing the built files from the source tree with the command make clean. This will preserve the files made by the configure program, so that you can rebuild everything with make later on. To reset the source tree to the state in which it was distributed, use make distclean. If you are going to build for several platforms within the same source tree you must do this and re-configure for each platform. (Alternatively, use a separate build tree for each platform, so that the source tree remains unmodified.)

If you perform a build and then discover that your configure options were wrong, or if you change anything that configure investigates (for example, software upgrades), then it's a good idea to do make distclean before reconfiguring and rebuilding. Without this, your changes in configuration choices might not propagate everywhere they need to.

19.4.1. 記憶體

shared_buffers (integer)

設定資料庫伺服器用於共享記憶體緩衝區的大小。預設值通常為 128 MB，但如果您的核心設定不支援（在 initdb 期間確定），則可能會更少。此設定必須至少為128 KB。（非預設值的 BLCKSZ 會改變最小值。）但是，通常需要高於最小值的設定才能獲得良好的性能。此參數只能在伺服器啟動時設定。

如果您擁有 1GB 或更多記憶體的專用資料庫伺服器，shared_buffers 的合理起始值是系統記憶體的 25％。有些工作負載甚至可以為 shared_buffers 設定更大的值，但由於PostgreSQL 依賴於作業系統緩衝區，因此，把 shared_buffers 分配 40％ 以上的記憶體大小不太可能比少量分配更好。shared_buffers 較大設定通常需要 max_wal_size 相對應的增加，以便分散在較長時間內寫入大量新資料或變更資料的過程。

在 RAM 小於 1GB 的系統上，更小比例是合適的，以便為作業系統留下足夠的空間。

huge_pages (enum)

啟用/停用大型記憶體頁面。有效值為 try（預設值），on 和 off。

目前，僅在 Linux 上支援此功能。設定為 try 時，在其他系統上會忽略該設定。

大型頁面的使用會使得頁面管理表更小，記憶體管理花費的 CPU 時間更少，從而提高了效能。有關更多詳細訊息，請參閱第 18.4.5 節。

設定 huge_pages 後，伺服器將嘗試使用大型頁面，但如果失敗則回退到使用正常分配。如果為 on，則若無法使用大型頁面將使伺服器無法啟動。 off 時，則不會使用大型頁面。

temp_buffers (integer)

設定每個資料庫連線使用的最大臨時緩衝區大小。這些是僅用於存取臨時資料表的連線本地緩衝區。預設值為 8MB。可以在單個連線中變更設定，但只能在連線中首次使用臨時資料表之前更改；後續嘗試更改該值將不會對該連線產生任何影響。

連線將根據需要分配臨時緩衝區，直到 temp_buffers 的上限。實際上不需要很多臨時緩衝區的連線中設定較大值的成本只是 temp_buffers 中每個增量的緩衝區描述指標，或大約 64 個位元組。但是，如果實際使用緩衝區，則會消耗額外的 8192 位元組（或者通常為 BLCKSZ 個位元組）。

max_prepared_transactions (integer)

設定可同時處於「prepared」狀態的最大交易事務數量（請參閱 PREPARE TRANSACTION）。將此參數設定為零（這是預設值）的話，會停用預備交易的功能。此參數只能在伺服器啟動時設定。

如果您不打算使用預備交易事務，則應將此參數設定為零以防止意外建立預備的交易事務。如果您正在使用預備的交易事務，那麼您可能希望 max_prepared_transactions 至少與 max_connections 一樣大，以便每個連線都可以至少有一個準備好的預備交易事務。

運行備用伺服器時，必須將此參數設定為與主服務器上相同或更高的值。 否則，查詢將不被允許在備用伺服器中。

work_mem (integer)

指定寫入暫存檔之前內部排序操作和雜湊表使用的記憶體大小。此值預設為 4 MB。請注意，對於複雜的查詢，可能會同時執行多個排序或雜湊作業；在開始將資料寫入暫存檔之前，每個操作都將被允許盡可能使用記憶體。此外，多個連線可以同時進行這些操作。因此，所使用的總記憶體量可能是 work_mem 值的許多倍；決定值時必須牢記此一事實。排序操作用於 ORDER BY，DISTINCT 和 merge JOIN。雜湊表用於 hash JOIN，hash aggregation 和 IN 子查詢處理。

maintenance_work_mem (integer)

指定維護操作要使用的最大記憶體大小，例如 VACUUM，CREATE INDEX 和ALTER TABLE ADD FOREIGN KEY。預設為 64 MB。由於資料庫連線一次只能執行其中一個操作，不會有多個同時運行，因此將此值設定為遠大於 work_mem 是安全的。較大的設定可能會提高清理和恢復資料庫回復的效能。

請注意，當 autovacuum 運行時，最多可以分配 autovacuum_max_workers 倍的記憶體，因此請注意不要將預設值設定得太高。透過單獨設定 autovacuum_work_mem 來控制它會有幫助。

replacement_sort_tuples (integer)

當要排序的 tuple 數小於此數時，排序將使用 replacement selection 而不是以 quicksort 産生其第一個輸出，這在記憶體受限的環境中可能很有用。在這種環境中，輸入到較大排序操作的 tuple 具有強大的物理到邏輯關連。請注意，這不包括具有反相關的輸入 tuple。替換選擇算法有可能産生一個不需要合併的長查詢，其中使用預設策略將導致必須合併以產生最終排序輸出的許多輸出資料列。這能更快地完成排序操作。

預設值為 150,000 個 tuple。請注意，較高的值通常不會更有效，並且可能適得其反，因為優先佇列對可用 CPU 緩衝區的大小很敏感，而預設策略使用快取的 oblivious algorithm 運行。此屬性允許預設排序策略自動且透明地有效使用可用的CPU 緩衝區。

將 maintenance_work_mem 設定為其預設值通常會防止工具程序命令的外部排序（例如，CREATE INDEX 用於建構 B-tree 索引的排序）使用選擇排序法，除非輸入tuple 非常大。

autovacuum_work_mem (integer)

指定每個 autovacuum 工作程序使用的最大記憶體。它預設為 -1，表示應該使用 maintenance_work_mem 的值。以其他方式執行時，此設定對 VACUUM 的行為沒有影響。

max_stack_depth (integer)

指定伺服器工作堆疊的最大安全深度。此參數的理想設定是核心強制執行的實際堆疊大小限制（由 ulimit -s 或其他等效設定），減去 1 MB 左右的安全範圍。需要安全額度，因為在伺服器的每個程序中都不會檢查堆疊深度，而是僅在關鍵的潛在遞迴程序（例如表示式求值）中檢查。預設設定是 2 MB，這是保守地小，不太可能冒崩潰的風險。但是，它可能太小而無法執行複雜的功能。只有超級使用者才能變更此設定。

將 max_stack_depth 設定為高於實際核心限制將意味著失控的遞迴函數可能導致單個後端程序崩潰。在 PostgreSQL 可以確定核心限制的平台上，伺服器不允許將此變數設定為不安全的值。但是，並非所有平台都有提供資訊，因此建議在選擇值時要小心。

dynamic_shared_memory_type (enum)

指定伺服器應使用的動態共享記憶體方法。可能的值是 posix（使用 shm_open 分配的 POSIX 共享記憶體），sysv（透過 shmget 分配的 System V 共享記憶體），windows（Windows 共享記憶體），mmap（使用儲存在資料目錄中的記憶體映射檔案來模擬共享記憶體） ），沒有（停用此功能）。並非所有平台都支援所有值；第一個受支援的選項是該平台的預設選項。通常不鼓勵使用 mmap 選項，這在任何平台上都不是預設選項，因為作業系統可能會將修改後的頁面重複寫回磁碟，從而增加系統 I/O 負載；但是，當 pg_dynshmem 目錄儲存在 RAM 磁碟上或其他共享記憶體裝置不可用時，它可能對除錯很有用。

19.4.2. 磁碟

temp_file_limit (integer)

指定程序可用於暫存檔的最大磁碟空間大小，例如排序和雜湊暫存檔，或持有游標的檔案。試圖超過此限制的交易將被取消。此值以 KB 為單位指定，-1（預設值）表示無限制。只有超級使用者可以變更改此設定。

此設定限制了給予 PostgreSQL 程序使用的所有暫存檔在任何時刻能使用的總空間。應該注意的是，用於臨時資料表的磁碟空間與在查詢執行過程中使用的暫存檔不同，並不會計入此限制。

19.4.3. 核心資源配置

max_files_per_process (integer)

設定每個伺服器子程序允許的同時最大開啓的檔案數。預設值是 1000 個檔案。如果核心可以確保每個程序的安全限制，則不必擔心此設定。但是在某些平台上（特別是大多數 BSD 系統），如果許多程序都嘗試開啓那麼多檔案，核心將允許單個程序打開比系統實際支援的更多的檔案。如果您發現自己看到“Too many open files”失敗，請嘗試減少此設定。此參數只能在伺服器啟動時設定。

19.4.4. 成本考量的 Vacuum 延遲

在執行 VACUUM 和 ANALYZE 指令期間，系統會維護一個內部計數器，用於追踪執行的各種 I/O 操作的估計成本。當累計成本達到極限（由 vacuum_cost_limit 指定）時，執行操作的過程將在 sleep_cost_delay 指定的短時間內休眠。然後它將重置計數器並繼續執行。

此功能的目的是允許管理員減少這些指令對同時間資料庫活動的 I/O 影響。在許多情況下，像 VACUUM 和 ANALYZE 這樣的維護指令很快完成就不重要；但是，這些指令又通常非常重要，不會嚴重干擾系統執行其他資料庫操作的能力。基於成本的清理延遲為管理員提供了實現這一目標的途徑。

對於手動發出的 VACUUM 指令，預設情況下會停用此功能。要啟用它，請將 vacuum_cost_delay 變數設定為非零值。

vacuum_cost_delay (integer)

超出成本限制時程序將休眠的時間長度（以毫秒為單位）。預設值為零，這會停用成本考量的清理延遲功能。正值可實現成本考量的清理。請注意，在許多系統上，睡眠延遲的有效分辨率為 10 毫秒；將 vacuum_cost_delay 設定為不是 10 的倍數的值可能與將其設定為 10 的下一個更高倍數具有相同的結果。

當使用成本考量的資料庫清理時，vacuum_cost_delay 的適當值通常非常小，可能是 10 或 20 毫秒。調整清理的資源消耗最好透過變更其他清理成本參數來完成。

vacuum_cost_page_hit (integer)

清除共享緩衝區中找到的緩衝區估計成本。它表示鎖定緩衝池，查詢共享雜湊表和掃描頁面內容的成本。預設值為 1。

vacuum_cost_page_miss (integer)

清除必須從磁碟讀取的緩衝區的估計成本。這表示鎖定緩衝池，查詢共享雜湊表，從磁碟讀取所需塊並掃描其內容的成本。預設值為 10。

vacuum_cost_page_dirty (integer)

清理修改先前清理的區塊時産生的估計成本。它表示將已修改區塊再次更新到磁碟所需的額外 I/O。預設值為 20。

vacuum_cost_limit (integer)

累積成本將導致清理程序進入睡眠狀態。預設值為 200。

注意

某些操作可能會持有關鍵的鎖定，因此應盡快完成。在此類操作期間不會發生成本考量的清理延遲。因此，成本可能會遠遠高於指定的限制。為了避免在這種情況下無意義的長延遲，實際延遲計算為 vacuum_cost_delay * cumulative_balance / vacuum_cost_limit，最大為 vacuum_cost_delay * 4。

19.4.5. 背景寫入程序

有一個單獨的伺服器程序稱為背景寫入程序，其功能是發起「dirty」（新的或修改的）共享緩衝區的寫入。 它會寫入共享緩衝區，因此處理使用者查詢的伺服器程序很少或永遠不需要等待寫入的發生。但是，背景寫入程序確實導致 I/O 負載的整體的淨增加，因為雖然每個檢查點間隔可能只會寫一次 repeatedly-dirtied 頁面，但背景寫入程序可能會發起多次寫入，因為它在同一時間間隔內被變更了。本小節中討論的參數可用於調整適於本地需求的行為。

bgwriter_delay (integer)

指定背景寫入程序的輪詢之間的延遲。在每一次輪詢中，寫入程序發出一些 dirty 緩衝區的寫入（可透過以下參數控制）。然後它睡眠 bgwriter_delay 毫秒，再重複。但是，當緩衝池中沒有 dirty 緩衝區時，無論 bgwriter_delay 如何，它都會進入更長的睡眠狀態。預設值為 200 毫秒。請注意，在許多系統上，睡眠延遲的有效分辨率為 10 毫秒；將 bgwriter_delay 設定為不是 10 的倍數可能與將其設定為 10 的下一個更高倍數具有相同的結果。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

bgwriter_lru_maxpages (integer)

在每一次輪詢中，背景寫入程序將寫入多個緩衝區。將此值設定為零將停用背景寫入。（請注意，由單獨的專用輔助程序管理的檢查點不受影響。）預設值為 100 個緩衝區。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

bgwriter_lru_multiplier (floating point)

每次輪詢寫入的 dirty 緩衝區數量取決於最近幾輪中伺服器程序所需的新緩衝區數。將最近的平均需求乘以 bgwriter_lru_multiplier，得出下一輪期間所需緩衝區數量的估計值。寫入 dirty 緩衝區，直到有許多乾淨，可再利用的緩衝區可用。（但是，每輪不會寫入超過 bgwriter_lru_maxpages 的緩衝區。）因此，1.0 的設定表示準確寫出預測需要的緩衝區數量的「Just in time」策略。較大的值為需求中的峰值提供了一些緩衝，而較小的值有意地使寫入由伺服器程序完成。預設值為 2.0。 此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

bgwriter_flush_after (integer)

只要背景寫入程序寫入了超過 bgwriter_flush_after 個位元組，就會嘗試強制作業系統向底層儲存系統發出這些寫入操作。這樣做會限制核心頁面緩衝區中的 dirty 資料量，減少在檢查點結束時發出 fsync 時停止的可能性，或者作業系統在背景以較大批次寫回資料的可能性。通常這會導致事務延遲大大減少，但也有一些情況，特別是工作負載大於 shared_buffers，但小於作業系統的頁面緩衝，其效能可能會降低。 此設定可能對某些平台沒有影響。有效範圍介於 0（停用強制寫回）和2MB之間。Linux 上的預設值為 512kB，其他地方為 0。（如果 BLCKSZ 不是8kB，則預設值和最大值會按比例縮放。）此參數只能在 postgresql.conf 檔案或匼服器命令列中設定。

bgwriter_lru_maxpages 和 bgwriter_lru_multiplier 設定較小值可以減少背景寫入程序造成的額外 I/O 負載，但使伺服器程序更有可能必須為自己發出寫入要求，可能造成交互查詢的延遟。

19.4.6. 非同步作業

effective_io_concurrency (integer)

設定 PostgreSQL 期望可以同時執行的磁碟 I/O 操作數。提高此值將增加任何單個 PostgreSQL 連線嘗試同時啟動的 I/O 操作數。允許的範圍是 1 到 1000，或者為零以停用非同步 I/O 要求的使用。目前，此設定僅影響 bitmap heap 掃描。

對於磁碟機而言，此設定一個很好的起點是包含用於資料庫的 RAID 0 分散或 RAID 1 鏡像的單獨磁碟數量。（對於 RAID 5，不應計算奇偶校驗磁碟。）但是，如果資料庫通常忙於在同時連線中發出多個查詢，則較低的值可能足以使磁碟陣列保持忙碌狀態。高於保持磁碟繁忙所需的值只會導致額外的 CPU 開銷。SSD和其他基於內存的儲存通常可以處理許多同時要求，因此最佳值可能是數百個。

非同步 I/O 取決於某些作業系統缺乏的有效 posix_fadvise 函數。如果該功能不存在，則將此參數設定為零以外的任何值將導致錯誤。而在某些作業系統（例如，Solaris）上，此功能存在但實際上並沒有做任何事情。

在受支援的系統上預設值為 1，否則為 0。透過設定同名的 tablespace 參數，可以為特定資料表空間中的資料表覆寫此值（請參閱 ALTER TABLESPACE）。

max_worker_processes (integer)

設定系統可以支援的最大背景程序數量。此參數只能在伺服器啟動時設定。預定值為 8。

執行備用伺服器時，必須將此參數設定為與主伺服器上相同或更高的值。否則，將不允許在備用伺服器中進行查詢。

變更此值時，請考慮同步調整 max_parallel_workers 和 max_parallel_workers_per_gather。

max_parallel_workers_per_gather (integer)

設定單個 Gather 或 Gather Merge 節點可以啟動的最大工作程序數量。同時工作程序取自 max_worker_processes 建立的程序池，由 max_parallel_workers 限制。請注意，請求的工作程序數量在執行時可能實際上不可用。如果發生這種情況，計劃將以比預期更少的工作程序運行，這可能是低效能的。預設值為 2。將此值設定為 0 將停用平行查詢執行。

請注意，平行查詢可能比非平行查詢消耗的資源要多得多，因為每個工作程序都是一個完全獨立的程序，與其他使用者連線對系統的影響大致相同。在為此設定選擇值時，以及在配置控制資源利用率的其他設定（例如work_mem）時，應考慮這一點。 諸如 work_mem 之類的資源限制被單獨應用於每個工作程序，這意味著所有程序的總利用率可能比通常用於任何單個程序的總利用率高得多。例如，使用 4 個工作程序的平行查詢可能會使用高達 5 倍的 CPU 時間、記憶體、I/O 頻寬等作為根本不使用工作程序的查詢。

有關平行查詢的更多訊息，請參閱第 15 章。

max_parallel_workers (integer)

設定系統可以支援平行查詢的最大工作程序數量。預設值為 8。增大或減小此值時，請考慮調整 max_parallel_workers_per_gather。另請注意，此值的設定高於 max_worker_processes 將不起作用，因為平行工作程序取自該設定所建立的工作程序池。

backend_flush_after (integer)

只要一個後端寫入了多個 backend_flush_after 字串，就會嘗試強制作業系統向底層儲存發出這些寫入操作。這樣做會限制核心頁面緩衝區中的非同步資料量，減少在檢查點結束時發出 fsync 時暫時停止的可能性，或者作業系統在後端以較大批量寫回資料的可能性。通常這會導致事務延遲大大減少，但也有一些情況，特別是工作負載大於shared_buffers，但小於作業系統的頁面暫存，其性能可能會降低。此設定可能對某些平台沒有影響。有效範圍介於 0（停用強制寫回）和 2MB 之間。預設值為 0，即沒有強制寫回。（如果 BLCKSZ 不是 8kB，則最大值與其成比例。）

old_snapshot_threshold (integer)

設定可以使用快照的最短時間，而不會在使用快照時發生快照過舊的錯誤。此參數只能在伺服器啟動時設定。

超過閾值，舊資料可能被清理。這可以幫助防止長時間使用的快照所面臨的資料膨脹。為了防止由於清理快照可能會顯示資料的錯誤結果，當快照早於此閾值時會産生錯誤，並且快照用於讀取自建構快照以來已修改的頁面。

值 -1 將停用此功能，並且是預設值。産品等級的有用值可能從少量幾小時到幾天不等。此設定將被強制為分鐘的顆粒度，並且僅允許小數字（例如 0 或 1 分鐘），因為它們有時可用於測試。雖然允許設定高達 60d，但請注意，在許多工作負載中，可能會在更短的時間範圍內發生極端資料膨脹或事務 ID 重覆。

啟用此功能後，關連末尾釋放的空間無法釋放到作業系統，因為這可能會刪除檢測快照過舊狀態所需的訊息。除非明確要求釋放（例如，使用 VACUUM FULL），否則分配給關連的所有空間仍與該關連相關聯，僅在該關連內重覆使用。

此設定不會嘗試保證在任何特定情況下都會産生錯誤。實際上，如果可以從已完成結果集合的游標産生正確的結果，即使引用資料表中的基礎資料列已被清理，也不會産生錯誤。有些資料表不能安全地儘早清理，因此不會受到此設定的影響，例如系統目錄。對於此類資料表，此設定既不會減少膨脹，也不會在掃描時產生快照過舊的錯誤。

要在作業系統註冊 Windows 事件日誌，請使用以下指令：

regsvr32 pgsql_library_directory/pgevent.dll

這將建立事件檢視器使用的註冊機碼項目，該項目由名為 PostgreSQL 的預設事件來源建立。

要指定不同的事件來源名稱（請參閱 event_source），請使用 /n 和 /i 選項：

regsvr32 /n /i:event_source_name pgsql_library_directory/pgevent.dll

要從作業系統註銷事件日誌，請使用以下指令：

regsvr32 /u [/i:event_source_name] pgsql_library_directory/pgevent.dll

注意

要在資料庫伺服器中啟用事件日誌記錄，請修改 postgresql.conf 中的 log_destination ，使其包含 eventlog。

19.1.1. Parameter Names and Values

All parameter names are case-insensitive. Every parameter takes a value of one of five types: boolean, string, integer, floating point, or enumerated (enum). The type determines the syntax for setting the parameter:

• Boolean: Values can be written as on, off, true, false, yes, no, 1, 0 (all case-insensitive) or any unambiguous prefix of one of these.

• String: In general, enclose the value in single quotes, doubling any single quotes within the value. Quotes can usually be omitted if the value is a simple number or identifier, however.

• Numeric (integer and floating point): A decimal point is permitted only for floating-point parameters. Do not use thousands separators. Quotes are not required.

• Numeric with Unit: Some numeric parameters have an implicit unit, because they describe quantities of memory or time. The unit might be kilobytes, blocks (typically eight kilobytes), milliseconds, seconds, or minutes. An unadorned numeric value for one of these settings will use the setting's default unit, which can be learned from pg_settings.unit. For convenience, settings can be given with a unit specified explicitly, for example '120 ms' for a time value, and they will be converted to whatever the parameter's actual unit is. Note that the value must be written as a string (with quotes) to use this feature. The unit name is case-sensitive, and there can be whitespace between the numeric value and the unit.

  • Valid memory units are kB (kilobytes), MB (megabytes), GB (gigabytes), and TB (terabytes). The multiplier for memory units is 1024, not 1000.

  • Valid time units are ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days).

• Enumerated: Enumerated-type parameters are written in the same way as string parameters, but are restricted to have one of a limited set of values. The values allowable for such a parameter can be found frompg_settings.enumvals. Enum parameter values are case-insensitive.

19.1.2. Parameter Interaction via the Configuration File

The most fundamental way to set these parameters is to edit the file postgresql.conf, which is normally kept in the data directory. A default copy is installed when the database cluster directory is initialized. An example of what this file might look like is:

# This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

One parameter is specified per line. The equal sign between name and value is optional. Whitespace is insignificant (except within a quoted parameter value) and blank lines are ignored. Hash marks (#) designate the remainder of the line as a comment. Parameter values that are not simple identifiers or numbers must be single-quoted. To embed a single quote in a parameter value, write either two quotes (preferred) or backslash-quote.

Parameters set in this way provide default values for the cluster. The settings seen by active sessions will be these values unless they are overridden. The following sections describe ways in which the administrator or user can override these defaults.

The configuration file is reread whenever the main server process receives a SIGHUP signal; this signal is most easily sent by running pg_ctl reload from the command line or by calling the SQL function pg_reload_conf(). The main server process also propagates this signal to all currently running server processes, so that existing sessions also adopt the new values (this will happen after they complete any currently-executing client command). Alternatively, you can send the signal to a single server process directly. Some parameters can only be set at server start; any changes to their entries in the configuration file will be ignored until the server is restarted. Invalid parameter settings in the configuration file are likewise ignored (but logged) during SIGHUP processing.

In addition to postgresql.conf, a PostgreSQL data directory contains a file postgresql.auto.conf, which has the same format as postgresql.conf but should never be edited manually. This file holds settings provided through the ALTER SYSTEM command. This file is automatically read whenever postgresql.conf is, and its settings take effect in the same way. Settings in postgresql.auto.conf override those in postgresql.conf.

The system view pg_file_settings can be helpful for pre-testing changes to the configuration file, or for diagnosing problems if a SIGHUP signal did not have the desired effects.

19.1.3. Parameter Interaction via SQL

PostgreSQL provides three SQL commands to establish configuration defaults. The already-mentioned ALTER SYSTEM command provides a SQL-accessible means of changing global defaults; it is functionally equivalent to editing postgresql.conf. In addition, there are two commands that allow setting of defaults on a per-database or per-role basis:

• The ALTER DATABASE command allows global settings to be overridden on a per-database basis.

• The ALTER ROLE command allows both global and per-database settings to be overridden with user-specific values.

Values set with ALTER DATABASE and ALTER ROLE are applied only when starting a fresh database session. They override values obtained from the configuration files or server command line, and constitute defaults for the rest of the session. Note that some settings cannot be changed after server start, and so cannot be set with these commands (or the ones listed below).

Once a client is connected to the database, PostgreSQL provides two additional SQL commands (and equivalent functions) to interact with session-local configuration settings:

• The SHOW command allows inspection of the current value of all parameters. The corresponding function is current_setting(setting_name text).

• The SET command allows modification of the current value of those parameters that can be set locally to a session; it has no effect on other sessions. The corresponding function is set_config(setting_name, new_value, is_local).

In addition, the system view pg_settings can be used to view and change session-local values:

• Querying this view is similar to using SHOW ALL but provides more detail. It is also more flexible, since it's possible to specify filter conditions or join against other relations.

• Using UPDATE on this view, specifically updating the setting column, is the equivalent of issuing SET commands. For example, the equivalent of

  Copy

  SET configuration_parameter TO DEFAULT;

  is:

  Copy

  UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

19.1.4. Parameter Interaction via the Shell

In addition to setting global defaults or attaching overrides at the database or role level, you can pass settings to PostgreSQL via shell facilities. Both the server and libpq client library accept parameter values via the shell.

• During server startup, parameter settings can be passed to the postgres command via the -c command-line parameter. For example,

  Copy

  postgres -c log_connections=yes -c log_destination='syslog'

  Settings provided in this way override those set via postgresql.conf or ALTER SYSTEM, so they cannot be changed globally without restarting the server.

• When starting a client session via libpq, parameter settings can be specified using the PGOPTIONS environment variable. Settings established in this way constitute defaults for the life of the session, but do not affect other sessions. For historical reasons, the format of PGOPTIONS is similar to that used when launching the postgres command; specifically, the -c flag must be specified. For example,

  Copy

  env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql

  Other clients and libraries might provide their own mechanisms, via the shell or otherwise, that allow the user to alter session settings without direct use of SQL commands.

19.1.5. Managing Configuration File Contents

PostgreSQL provides several features for breaking down complex postgresql.conf files into sub-files. These features are especially useful when managing multiple servers with related, but not identical, configurations.

In addition to individual parameter settings, the postgresql.conf file can contain include directives, which specify another file to read and process as if it were inserted into the configuration file at this point. This feature allows a configuration file to be divided into physically separate parts. Include directives simply look like:

include 'filename'

If the file name is not an absolute path, it is taken as relative to the directory containing the referencing configuration file. Inclusions can be nested.

There is also an include_if_exists directive, which acts the same as the include directive, except when the referenced file does not exist or cannot be read. A regular include will consider this an error condition, but include_if_exists merely logs a message and continues processing the referencing configuration file.

The postgresql.conf file can also contain include_dir directives, which specify an entire directory of configuration files to include. These look like

include_dir 'directory'

Non-absolute directory names are taken as relative to the directory containing the referencing configuration file. Within the specified directory, only non-directory files whose names end with the suffix .conf will be included. File names that start with the . character are also ignored, to prevent mistakes since such files are hidden on some platforms. Multiple files within an include directory are processed in file name order (according to C locale rules, i.e. numbers before letters, and uppercase letters before lowercase ones).

Include files or directories can be used to logically separate portions of the database configuration, rather than having a single large postgresql.conf file. Consider a company that has two database servers, each with a different amount of memory. There are likely elements of the configuration both will share, for things such as logging. But memory-related parameters on the server will vary between the two. And there might be server specific customizations, too. One way to manage this situation is to break the custom configuration changes for your site into three files. You could add this to the end of your postgresql.conf file to include them:

include 'shared.conf'
include 'memory.conf'
include 'server.conf'

All systems would have the same shared.conf. Each server with a particular amount of memory could share the same memory.conf; you might have one for all servers with 8GB of RAM, another for those having 16GB. And finallyserver.conf could have truly server-specific configuration information in it.

Another possibility is to create a configuration file directory and put this information into files there. For example, a conf.d directory could be referenced at the end of postgresql.conf:

include_dir 'conf.d'

Then you could name the files in the conf.d directory like this:

00shared.conf
01memory.conf
02server.conf

This naming convention establishes a clear order in which these files will be loaded. This is important because only the last setting encountered for a particular parameter while the server is reading configuration files will be used. In this example, something set in conf.d/02server.conf would override a value set in conf.d/01memory.conf.

You might instead use this approach to naming the files descriptively:

00shared.conf
01memory-8GB.conf
02server-foo.conf

This sort of arrangement gives a unique name for each configuration file variation. This can help eliminate ambiguity when several servers have their configurations all stored in one place, such as in a version control repository. (Storing database configuration files under version control is another good practice to consider.)

In addition to the postgresql.conf file already mentioned, PostgreSQL uses two other manually-edited configuration files, which control client authentication (their use is discussed in Chapter 20). By default, all three configuration files are stored in the database cluster's data directory. The parameters described in this section allow the configuration files to be placed elsewhere. (Doing so can ease administration. In particular it is often easier to ensure that the configuration files are properly backed-up when they are kept separate.)

data_directory (string)

Specifies the directory to use for data storage. This parameter can only be set at server start.

config_file (string)

Specifies the main server configuration file (customarily called postgresql.conf). This parameter can only be set on the postgres command line.

hba_file (string)

Specifies the configuration file for host-based authentication (customarily called pg_hba.conf). This parameter can only be set at server start.

ident_file (string)

Specifies the configuration file for user name mapping (customarily called pg_ident.conf). This parameter can only be set at server start. See also Section 20.2.

external_pid_file (string)

Specifies the name of an additional process-ID (PID) file that the server should create for use by server administration programs. This parameter can only be set at server start.

In a default installation, none of the above parameters are set explicitly. Instead, the data directory is specified by the -D command-line option or the PGDATA environment variable, and the configuration files are all found within the data directory.

If you wish to keep the configuration files elsewhere than the data directory, the postgres -D command-line option or PGDATA environment variable must point to the directory containing the configuration files, and the data_directory parameter must be set inpostgresql.conf (or on the command line) to show where the data directory is actually located. Notice that data_directory overrides -D and PGDATA for the location of the data directory, but not for the location of the configuration files.

If you wish, you can specify the configuration file names and locations individually using the parameters config_file, hba_file and/or ident_file. config_file can only be specified on the postgres command line, but the others can be set within the main configuration file. If all three parameters plus data_directory are explicitly set, then it is not necessary to specify -D or PGDATA.

When setting any of these parameters, a relative path will be interpreted with respect to the directory in which postgres is started.

19.3.1. 連線設定

listen_addresses (string)

指定伺服器監聽用戶端應用程序連線的 TCP/IP 位址。該值採用逗號分隔的主機名稱或數字 IP 位址列表的形式。特殊項目「*」對應於所有可用的 IP。項目 0.0.0.0 允許監聽所有 IPv4 位址，還有「::」允許監聽所有 IPv6 位址。如果列表為空，則伺服器根本不監聽任何 IP 接口，在這種情況下，就只能使用 Unix-domain socket 來連接它。預設值是 localhost，它只允許進行本地 TCP/IP loopback 連線。儘管用戶端身份驗證（第 20 章）允許對誰可以存取伺服器進行細維的控制，但 listen_addresses 控制哪些 IP 接受連線嘗試，這有助於防止在不安全的網路接口上重複發出惡意的連線請求。此參數只能在伺服器啟動時設定。

port (integer)

伺服器監聽的 TCP 連接埠；預設是 5432。請注意，相同的連接埠號號用於伺服器監聽的所有 IP 地址。此參數只能在伺服器啟動時設定。

max_connections (integer)

決定資料庫伺服器的最大同時連線數。預設值通常為 100 個連線，但如果您的核心設定不支援它（在 initdb 期間確定），則可能會更少。該參數只能在伺服器啟動時設定。

運行備用伺服器時，必須將此參數設定為與主服務器上相同或更高的值。 否則，查詢將不被允許在備用伺服器中使用。

superuser_reserved_connections (integer)

決定為 PostgreSQL 超級使用者連線保留的連線「插槽」的數量。最多 max_connections 連線可以同時活動。當活動同時連線的數量為 max_connections 減去 superuser_reserved_connections 以上時，新連線將僅接受超級使用者，並且不會接受新的複寫作業連線。

預設值是三個連線。該值必須小於 max_connections 的值。此參數只能在伺服器啟動時設定。

unix_socket_directories (string)

指定伺服器要監聽來自用戶端應用程序以 Unix-domain socket 連線的目錄。列出由逗號分隔的多個目錄可以建立多個 socket。項目之間的空白會被忽略；如果您需要在名稱中包含空格或逗號，請用雙引號括住目錄名稱。空值表示不監聽任何 Unix-domain socket，在這種情況下，只有 TCP/IP 協定可用於連線到服務器。預設值通常是 /tmp，但可以在編譯時變更。此參數只能在伺服器啟動時設定。

除了名為 .s.PGSQL.nnnn 的 socket 檔案本身之外，其中 nnnn 是伺服器的連接埠號號，將在每個 unix_socket_directories 目錄中建立一個名為 .s.PGSQL.nnnn.lock 的普通檔案。這兩個檔案都不應該手動刪除。

這個參數與 Windows 無關，它沒有 Unix-domain socket。

unix_socket_group (string)

設定 Unix-domain socket 的群組。（socket 的使用者始終是啟動伺服器的使用者。）結合參數 unix_socket_permissions，可以將其用作為 Unix-domain socket 的附加存取控制機制。預設情況下，這是空字符串，它使用服務器用戶的預設群組。此參數只能在伺服器啟動時設定。

這個參數與 Windows 無關，它沒有 Unix-domain socket。

unix_socket_permissions (integer)

設定 Unix-domain socket 的存取權限。Unix-domain socket 使用一般的 Unix 檔案系統權限設定。期望的參數值是以 chmod 和 umask 系統呼叫可接受的格式指定數字模式。（要使用習慣的八進制格式，數字必須以 0（零）開頭。）

預設權限是 0777，意味著任何人都可以進行連線。合理的選擇是 0770（僅使用者和其群組，另請參閱 unix_socket_group）和 0700（僅使用者本身）。（請注意，對於Unix-domain socket，只有寫入權限很重要，所以設定還是撤消讀取或執行權限都沒有意義。）

這種存取控制機制獨立於第 20 章中所描述的機制。

此參數只能在伺服器啟動時設定。

此參數在某些系統上無關緊要，特別是從 Solaris 10 開始的 Solaris，會完全忽略權限許可。在那裡，透過將 unix_socket_directories 指向具有僅限於所需的搜尋權限的目錄，就可以實現類似的效果。這個參數與 Windows 也是無關的，它沒有 Unix-domain socket。

bonjour (boolean)

透過 Bonjour 啟用伺服器存在的廣播。預設是關閉的。此參數只能在伺服器啟動時設定。

bonjour_name (string)

指定 Bonjour 的服務名稱。如果此參數設定為空字串''（這是預設值），則使用電腦名稱。 如果伺服器未使用 Bonjour 支援進行編譯，則此參數將被忽略。此參數只能在伺服器啟動時設定。

tcp_keepalives_idle (integer)

指定 TCP 在發送 Keepalive 訊息給用戶端之後保持連線的秒數。值為 0 時使用系統預設值。此參數僅在支援 TCP_KEEPIDLE 或等效網路選項的系統上以及在 Windows 上受到支援；在其他系統上，它必須是零。在透過 Unix-domain socket 的連線中，該參數將被忽略並始終為零。

注意

在 Windows 上，值為 0 會將此參數設定為2小時，因為 Windows 不提供讀取系統預設值的方法。

tcp_keepalives_interval (integer)

指定用戶端未回應的 TCP 保持活動訊息應重新傳輸的秒數。值為 0 時使用系統預設值。此參數僅在支援 TCP_KEEPINTVL 或等效網路選項的系統上以及在 Windows 上受到支援；在其他系統上，它必須是零。在透過 Unix-domain socket 的連線中，此參數將被忽略並始終為零。

注意

在 Windows 上，值為 0 會將此參數設定為 1 秒，因為 Windows 不提供讀取系統預設值的方法。

tcp_keepalives_count (integer)

指定在伺服器連線到用戶端之前可能已經失去的 TCP 保持連線的數量。值為 0 時使用系統預設值。此參數僅在支援 TCP_KEEPCNT 或等效網路選項的系統上受到支持；在其他系統上，它必須是零。在透過 Unix-domain socket 的連線中，此參數將被忽略並始終為零。

注意

此參數在 Windows 上不支援，並且必須為零。

19.3.2. 安全性與認證

authentication_timeout (integer)

以秒為單位設定用戶端身份驗證的最長時間。如果可能的用戶端在這段時間內還沒有完成認證協議，伺服器將會關閉連線。這可以防止掛起的用戶端無限期地佔用連線。預設值是一分鐘。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

ssl (boolean)

啟用 SSL 連線。使用前請先閱讀第 18.9 節。 此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設是關閉的。

ssl_ca_file (string)

指定包含 SSL 伺服器證書頒發機構（CA）的檔案名稱。相對路徑與資料目錄有關。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值為空，表示未載入 CA 檔案，並且不執行用戶端證書驗證。

在以前的 PostgreSQL 版本中，該檔案的名稱被硬性指定為 root.crt。

ssl_cert_file (string)

指定包含 SSL 伺服器證書的檔案名稱。相對路徑與資料目錄有關。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值是 server.crt。

ssl_crl_file (string)

指定包含 SSL 伺服器證書吊銷列表（CRL）的文件的名稱。相對路徑與資料目錄有關。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值為空，表示沒有加載 CRL 檔案。

在以前的 PostgreSQL 版本中，該檔案的名稱被硬性指定為 root.crl。

ssl_key_file (string)

指定包含 SSL 伺服器私鑰的檔案名稱。相對路徑與資料目錄有關。 此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值是 server.key。

ssl_ciphers (string)

指定允許在安全連線上使用的 SSL 密碼套件列表。有關此設定的語法和支援的列表，請參閱 OpenSSL 軟體套件中的密碼手冊文件。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值為 HIGH:MEDIUM:+3DES:!aNULL。這個預設通常是一個合理的設定，除非您有特定的安全要求。

預設值延伸說明：

HIGH

使用來自 HIGH group 的密碼套件（例如：AES，Camellia，3DES）

MEDIUM

使用來自 MEDIUM group 的密碼套件（例如：RC4，SEED）

+3DES

HIGH 的 OpenSSL 預設順序有問題，因為它的 3DES 高於 AES128。這是錯誤的，因為 3DES 比 AES128 提供較低的安全性，而且速度也更慢。+3DES 將所有其他高級和中級密碼重新排序。

!aNULL

停用不進行身份驗證的匿名密碼套件。這種密碼套件容易受到中間人攻擊，因此不應使用。

可用的密碼套件詳細訊息將因 OpenSSL 版本而異。使用命令 openssl ciphers -v'HIGH:MEDIUM:+3DES:!aNULL' 來查看當下安裝的 OpenSSL 版本細節。請注意，此列表在運行時基於伺服器密鑰型別進行過濾。

ssl_prefer_server_ciphers (boolean)

指定是否使用伺服器的 SSL 密碼設定，而不是用戶端的。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值是 true。

較舊的 PostgreSQL 版本並沒有此設定，始終使用用戶端的設定。此設定主要是為了與這些版本的相容性。使用伺服器的選項通常更好，因為伺服器更有可能做適當的配置。

ssl_ecdh_curve (string)

指定要在 ECDH 密鑰交換中使用的 curve 名稱。它需要所有連線的用戶端支援。它不需要與伺服器的 curve 鍵使用的相同。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設為 prime256v1。

最常用 curve 的 OpenSSL 名稱為：prime256v1（NIST P-256），secp384r1（NIST P-384），secp521r1（NIST P-521）。可用 curve 的完整列表可以使用 openssl ecparam -list_curves 指令列出。但並非所有的結果都可以在 TLS 中使用。

password_encryption (enum)

當在 CREATE ROLE 或 ALTER ROLE 中指定密碼時，此參數決定用於加密密碼的演算法。預設值是md5，它將密碼儲存為MD5 hash（on 也被接受，作為 md5 的別名）。將此參數設定為 scram-sha-256 時將使用 SCRAM-SHA-256 加密密碼。

請注意，較舊的用戶端可能缺少對 SCRAM 認證機制的支援，因此不適用於使用 SCRAM-SHA-256 加密的密碼。有關更多詳細訊息，請參閱第 20.3.2 節。

ssl_dh_params_file (string)

指定包含用於所謂的 ephemeral DH family 的 SSL 加密的 Diffie-Hellman 參數的檔案名稱。預設值為空，在這種情況下，使用預設編譯的 DH 參數。如果攻擊者設法破解眾所周知的編譯 DH 參數，則使用自行定義 DH 參數可以減少暴露的可能性。 您可以使用指令 openssl dhparam -out dhparams.pem 2048 建立您自己的DH參數檔案。

此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

krb_server_keyfile (string)

設定 Kerberos 伺服器密鑰檔案的位置。有關詳細訊息，請參閱第 20.3.3 節。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

krb_caseins_users (boolean)

設定是否應該區分大小寫地處理 GSSAPI 用戶名。預設是關閉的（區分大小寫）。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

db_user_namespace (boolean)

此參數啟用每個資料庫分別的使用者名稱。預設是關閉的。 此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

如果開啓的話，您應該將使用者建立為 username@dbname。當連線用戶端傳遞使用者名稱時，@和資料庫名稱將附加到使用者名稱中，並且該伺服器會查詢特定於資料庫的使用者名稱。請注意，當您在 SQL 環境中建立名稱包含 @ 的使用者時，您需要以引號括住使用者名稱。

啟用此參數後，您仍然可以建立普通的全域使用者。在用戶端指定使用者名稱時簡單追加 @，例如 joe@。在使用者名稱被伺服器查詢之前，@ 將被剝離。

db_user_namespace 會導致用戶端和伺服器的使用者名稱表示方式不同。身份驗證檢查始終使用伺服器的使用者名稱完成，因此必須為伺服器的使用者名稱配置身份驗證方法，而不是用戶端。而 md5 在用戶端和伺服器上均使用使用者名稱作為 salt，所以 md5 不能與 db_user_namespace 一起使用。

注意

此功能是一種臨時措施，到找到完整的解決方案的時候，這個選項將被刪除。

19.3.3. SSL

有關設定 SSL 的更多資訊，請參閱第 18.9 節。

ssl (boolean)

啟用 SSL 連線。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設為 off。

ssl_ca_file (string)

Specifies the name of the file containing the SSL server certificate authority (CA). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is empty, meaning no CA file is loaded, and client certificate verification is not performed.

ssl_cert_file (string)

Specifies the name of the file containing the SSL server certificate. Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is server.crt.

(string)

Specifies the name of the file containing the SSL server certificate revocation list (CRL). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is empty, meaning no CRL file is loaded.

ssl_key_file (string)

Specifies the name of the file containing the SSL server private key. Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is server.key.

ssl_ciphers (string)

Specifies a list of SSL cipher suites that are allowed to be used on secure connections. See the ciphers manual page in the OpenSSL package for the syntax of this setting and a list of supported values. This parameter can only be set in the postgresql.conf file or on the server command line. The default value is HIGH:MEDIUM:+3DES:!aNULL. The default is usually a reasonable choice unless you have specific security requirements.

Explanation of the default value:

HIGH

Cipher suites that use ciphers from HIGH group (e.g., AES, Camellia, 3DES)

MEDIUM

Cipher suites that use ciphers from MEDIUM group (e.g., RC4, SEED)

+3DES

The OpenSSL default order for HIGH is problematic because it orders 3DES higher than AES128. This is wrong because 3DES offers less security than AES128, and it is also much slower. +3DES reorders it after all other HIGH and MEDIUM ciphers.

!aNULL

Disables anonymous cipher suites that do no authentication. Such cipher suites are vulnerable to man-in-the-middle attacks and therefore should not be used.

Available cipher suite details will vary across OpenSSL versions. Use the command openssl ciphers -v 'HIGH:MEDIUM:+3DES:!aNULL' to see actual details for the currently installed OpenSSL version. Note that this list is filtered at run time based on the server key type

ssl_prefer_server_ciphers (boolean)

Specifies whether to use the server's SSL cipher preferences, rather than the client's. This parameter can only be set in the postgresql.conf file or on the server command line. The default is true.

Older PostgreSQL versions do not have this setting and always use the client's preferences. This setting is mainly for backward compatibility with those versions. Using the server's preferences is usually better because it is more likely that the server is appropriately configured.

ssl_ecdh_curve (string)

Specifies the name of the curve to use in ECDH key exchange. It needs to be supported by all clients that connect. It does not need to be the same curve used by the server's Elliptic Curve key. This parameter can only be set in the postgresql.conf file or on the server command line. The default is prime256v1.

OpenSSL names for the most common curves are: prime256v1 (NIST P-256), secp384r1 (NIST P-384), secp521r1 (NIST P-521). The full list of available curves can be shown with the command openssl ecparam -list_curves. Not all of them are usable in TLS though.

ssl_dh_params_file (string)

Specifies the name of the file containing Diffie-Hellman parameters used for so-called ephemeral DH family of SSL ciphers. The default is empty, in which case compiled-in default DH parameters used. Using custom DH parameters reduces the exposure if an attacker manages to crack the well-known compiled-in DH parameters. You can create your own DH parameters file with the command openssl dhparam -out dhparams.pem 2048.

This parameter can only be set in the postgresql.conf file or on the server command line.

ssl_passphrase_command (string)

Sets an external command to be invoked when a passphrase for decrypting an SSL file such as a private key needs to be obtained. By default, this parameter is empty, which means the built-in prompting mechanism is used.

The command must print the passphrase to the standard output and exit with code 0. In the parameter value, %p is replaced by a prompt string. (Write %% for a literal %.) Note that the prompt string will probably contain whitespace, so be sure to quote adequately. A single newline is stripped from the end of the output if present.

The command does not actually have to prompt the user for a passphrase. It can read it from a file, obtain it from a keychain facility, or similar. It is up to the user to make sure the chosen mechanism is adequately secure.

This parameter can only be set in the postgresql.conf file or on the server command line

ssl_passphrase_command_supports_reload (boolean)

This parameter determines whether the passphrase command set by ssl_passphrase_command will also be called during a configuration reload if a key file needs a passphrase. If this parameter is false (the default), then ssl_passphrase_command will be ignored during a reload and the SSL configuration will not be reloaded if a passphrase is needed. That setting is appropriate for a command that requires a TTY for prompting, which might not be available when the server is running. Setting this parameter to true might be appropriate if the passphrase is obtained from a file, for example.

This parameter can only be set in the postgresql.conf file or on the server command line.

19.11.1. 查詢語句的行為

search_path(string)

這個參數表示，當一個物件（資料表、資料型別、函數等）以未指定 schema 的簡單名稱引用時，其搜尋的路徑順序。當不同 schema 中有相同名稱的物件時，將採用搜尋路徑中第一個找到的物件。不在搜尋路徑中的任何 schema 中物件，就只能透過使用限定名稱來指定其 schema 來引用。

search_path 的內容必須是逗號分隔的 schema 名稱列表。任何非現有 schema 的名稱，或是使用者不具有 USAGE 權限的 schema，都將被忽略。

如果其中一個項目是特殊名稱 $user，則會使用 SESSION_USER 回傳的名稱作為 schema 名稱，確認該 schema 存在且使用者具有 USAGE 權限。 （如果沒有權限，$user 將被忽略。）

系統目錄 pg_catalog 一定會被搜尋，無論是否列在搜尋路徑中。如果列在搜尋路徑中了，那麼它將按照指定的順序被搜尋。 如果 pg_catalog 不在搜尋路徑中，那麼它將會優先被搜尋。

同樣地，目前連線的臨時資料表的schema，pg_temp_nnn，如果它存在的話，就一定會被搜尋。它可以透過使用別名 pg_temp 明確列在搜尋路徑中。如果沒有在搜尋路徑中列出的話，則優先搜尋（在 pg_catalog 之前）。但是，臨時 schema 只是搜索關連（資料表、view，序列等）和資料型別名稱。不會搜尋函數或運算子名稱。

建立物件時沒有指定特定的 schema，那麼它們將被放置在 search_path 中的第一個有效 schema 中。如果搜尋路徑為空，則會產生錯誤。

這個參數的預設值是 “$user”，public。此設定用來支援共享資料庫，沒有使用者具有私有 schema、所有共享使用 public、私人自有 schema ，以及以上情境的組合。其他的需求也可以透過更改預設的搜索路徑設置來達到，無論是全域或自有搜尋路徑。

搜尋路徑的目前內容可以使用 SQL 函數 current_schemas 來檢查（詳見 9.25 節）。這與檢查 search_path 的內容並不完全相同，因為 current_schemas 表示 search_path 中出現的項目是如何解析的。

有關 schema 處理的更多訊息，請參見第 5.8 節。

row_security(boolean)

此參數控制在資料列安全原則檢查時是否進行錯誤中斷。設定為 on 時，安全原則以正常方式運作。當設定為 off 時，除非查詢失敗，否則會至少符合一個原則。 預設值為 on。變更為 off 時，將會限制資料列的可視性，而可能造成不正確的結果；例如，pg_dump 就會變更其預設值。此參數對於可以繞過每個安全原則的角色，也就是對具有 BYPASSRLS 屬性的超級使用者和角色都不會產生影響。

有關於資料列安全原則的更多訊息，請參閱 CREATE POLICY。

default_tablespace(string)

此參數指的是在 CREATE 指令未明確指定資料表空間（tablespace）時用於建立的資料庫物件（資料表和索引）的預設資料表空間。

該值可以是資料表空間的名稱，也可以是使用空字串表示為目前資料庫的預設資料表空間。如果該值與不符合任何現有的資料表空間名稱時，PostgreSQL 將自動使用目前資料庫的預設資料表空間。如果指定了非預設的資料表空間，則使用者必須具有 CREATE 權限，否則建立的操作將會失敗。

這個參數不用於臨時資料表；對於臨時資料表來說，會參考 temp_tablespaces 參數。

建立資料庫時也不會使用這個參數。預設情況下，新的資料庫將複製的樣板資料庫，並繼承其資料表空間的設定。

有關於資料表空間的更多資訊，請參閱第 22.6 節。

temp_tablespaces(string)

此參數指定在 CREATE 指令未指定資料表空間時創立臨時物件（臨時資料表和臨時資料表的索引）的資料表空間。用於排序大量資料集的臨時檔案也在這些資料表空間中創立。

該內容是資料表空間名稱的列表。當列表中有多個名稱時，PostgreSQL 在每次建立臨時物件時都會隨機選擇一個列表成員；除非是在一個交易中，連續建立的臨時物件將會被放置在列表的後續資料表空間中。 如果列表的元素是空字串，PostgreSQL 將自動使用目前資料庫的預設資料表空間。

設定 temp_tablespaces 時，指定一個不存在的資料表空間會造成錯誤，因為指定一個使用者沒有 CREATE 權限的資料表空間。但是，使用先前設定的內容時，不存在的資料表空間將被忽略，使用者缺少 CREATE 權限的資料表空間也將被忽略。特別是，在使用 postgresql.conf 中設定的內容時，此規則適用。

預設值是一個空字串，這將會使用目前資料庫的預設資料空間中建立所有臨時物件。

另請參閱本頁的 default_tablespace。

check_function_bodies(boolean)

這個參數通常是啓用（on）的。如果把它關閉（off）的話，將在 CREATE FUNCTION 時關閉函數內容檢驗的措施。停用檢驗可避免檢驗過程的副作用，避免由於物件引用等問題所導致的誤報。例如以其他使用者載入函數之前，將此參數設置為 off；pg_dump 將會自動執行此操作。

default_transaction_isolation(enum)

每組 SQL 交易查詢都有一個隔離的等級，可以是「read uncommitted」、「read committed」、「repeatable read」或「serializable」。此參數控制每個新的交易產生時的預設隔離等級。預設是「read committed」。

請參閱第 13 章和 SET TRANSACTION 以取得更多訊息。

default_transaction_read_only(boolean)

一個唯讀的 SQL 交易不能更新非臨時的資料表。此參數控制每個新的交易的預設為唯讀狀態。預設是關閉（off）的（可讀／可寫）。

請參閱 SET TRANSACTION 以取得更多訊息。

default_transaction_deferrable(boolean)

以 serializable 的隔離等級執行時，可延遲的唯讀 SQL 交易可能會被延遲，稍後才允許繼續。但是，一旦開始執行，就不會產生確保可序列化所需的任何成本；所以序列化代碼將不會因為同步更新而強制中止，使得這個選項適合用於長時間運行的唯讀交易。

此參數控制每個新交易查詢的預設可延期狀態。它目前對讀寫交易或者低於 serializable 隔離等級的操作沒有影響。預設是關閉（off）的。

請參閱 SET TRANSACTION 以取得更多訊息。

session_replication_role(enum)

控制目前連線與複寫相關觸發器與規則。設定此參數需要超級使用者權限，會導致放棄任何先前快取的查詢計劃。可能的值是 origin（預設）、replica 和 local。 有關更多訊息，請參閱 ALTER TABLE。

statement_timeout(integer)

任何指令執行超過指定的時間時，就會中止其執行。時間單位為 millisecond（毫秒）。以伺服器接受到的時間起算。 如果 log_min_error_statement 設定為 ERROR 或更低的等級時，則超時的查詢語句將被記錄下來。設定值為零（預設值），將其關閉功能。

不建議在 postgresql.conf 中設定 statement_timeout，因為它會影響所有的連線。

lock_timeout(integer)

當你企圖鎖定資料表、索引、資料列或其他資料庫物件上時，任何等待超過指定的毫秒數的語句都會被強制中止。時間限制會分別適用於每次鎖定取得的嘗試。此限制適用於明確的鎖定請求（例如 LOCK TABLE 或 SELECT FOR UPDATE without NOWAIT）以及隱含的鎖定請求。如果將 log_min_error_statement 設定為 ERROR 或更低的等級時，則會記錄超時的語查詢句。設定值為零（預設值），將其關閉功能。

與 statement_timeout 不同，這個超時設定只會在等待鎖定的時候有作用。請注意，如果 statement_timeout 不為零，則將 lock_timeout 設定為相同或更大的值是毫無意義的，因為查詢語句超時總是會首先觸發。

不建議在 postgresql.conf 中設定 lock_timeout，因為這會影響所有的連線。

idle_in_transaction_session_timeout(integer)

如果空閒時間超過指定的持續時間時（以毫秒為單位）未完成的交易將會被終止。這會釋放該連線所持有的任何鎖定，並使連線可以重新使用；也只有 tuple 才能看到這個交易被清除。有關這方面的更多細節，請參閱第 24.1 節。

預設值 0 表停用此功能。

vacuum_freeze_table_age(integer)

如果資料表的 pg_class.relfrozenxid 欄位值已達到此設定的指定時間，VACUUM 將主動執行掃描。主動的掃描不同於一般的 VACUUM，因為它會訪問每個可能包含解開的 XID 或 MXID的頁面，而不僅僅是那些可能包含廢棄 tuple 的頁面。預設是 1.5 億筆交易。 儘管使用者可以設定的範圍為 0 到 20 億，但 VACUUM 將自動地將有效值限制為 autovacuum_freeze_max_age 的 95%，以便在啟動資料表的 anti-wraparound 自動清理之前，定期的手動 VACUUM 有機會運行。欲了解更多訊息，請參閱第 24.1.5 節。

vacuum_freeze_min_age(integer)

指定 VACUUM 是否決定在掃描資料表時凍結資料列版本的截止期限（交易中）。預設是5000萬交易。 儘管使用者可以設定此值為 0 到 10 億之間的任何值，但 VACUUM 將自動地將有效值限制為 autovacuum_freeze_max_age 值的一半，以便在強制自動清理之間沒有過短的不合理時間間隔。欲了解更多訊息，請參閱第 24.1.5 節。

vacuum_multixact_freeze_table_age(integer)

如果資料表的 pg_class.relminmxid 欄位值已達到此設定指定的時間，VACUUM 將主動執行掃描。主動的掃描不同於一般的 VACUUM，因為它會訪問每個可能包含解開的 XID 或 MXID 的頁面，而不僅僅是那些可能包含廢棄 tuple 的頁面。預設值是 1.5 億個交易。儘管使用者可以設定的範圍為 0 到 20 億，但 VACUUM 將自動地將有效值限制為 autovacuum_freeze_max_age的 95%，以便在啟動資料表的 anti-wraparound 自動清理之前，定期的手動 VACUUM 有機會運行。欲了解更多訊息，請參閱第 24.1.5 節。

vacuum_multixact_freeze_min_age(integer)

指定 VACUUM 在掃描資料表時是使用較新的 transaction ID 或是 multixact ID，來替換多個 multixact ID 的截斷年限（以 multixact 表示）。預設是500萬個 multixact。儘管使用者可以設定此值為 0 到 10 億之間的任何值，但 VACUUM 將自動地將有效值限制為 autovacuum_freeze_max_age 值的一半，以便在強制自動清理之間沒有過短的不合理時間間隔。欲了解更多訊息，請參閱 第 24.1.5.1 節。

bytea_output(enum)

設定預設的輸出格式型別為bytea。合法的設定值為 hex（預設）和 escape（傳統的 PostgreSQL 格式）。請參閱第 8.4 節取得更多資訊。無論這個設定如何，bytea 型別在輸入時，兩種格式都能接受。

xmlbinary(enum)

設定如何在 XML 中編碼二進位數值。例如，當 bytea 值被函數 xmlelement 或 xmlforest 轉換為XML時，就適用這個設定。可以使用的值是 base64 和 hex，都是在 XML Schema 標準中定義的。 預設值是 base64。有關 XML 相關函數的更多訊息，請參閱第 9.14 節。

實際上的選擇主要是習慣問題，僅受限於客戶端應用程式中的可能限制。這兩種方法都支援所有可能的值，儘管 hex 編碼會比 base64 編碼稍大。

xmloption(enum)

在 XML 和字串之間轉換時，設定是否隱含 DOCUMENT 或 CONTENT。請參閱 8.13 節的描述。有效值是 DOCUMENT 和 CONTENT。預設值是 CONTENT。

根據 SQL 標準，設定此選項的命令是

SET XML OPTION { DOCUMENT | CONTENT };

這個語法在 PostgreSQL 中也是可以使用的。

gin_pending_list_limit(integer)

設定啟用 fastupdate 時使用的 GIN 排程列表的最大空間。如果列表大於這個最大空間，則透過將其中的項目整批移動到主 GIN 資料結構來清除它。預設值是 4MB。透過更改索引的儲存參數，可以為單個 GIN 索引覆寫此設定。有關更多訊息，請參閱第 64.4.1 節和第 64.5 節。

19.11.2. 語系格式

DateStyle(string)

設定日期和時間內容的顯示格式，以及解釋模糊日期輸入的規則。由於歷史的因素，此參數包含兩個獨立的參數：輸出格式規範（ISO、Postgres、SQL 或 German）以及年/月/日次序（DMY、MDY 或 YMD）的輸入/輸出規範。它們可以單獨或一起設定。 關鍵字 Euro 和 European 是 DMY 的同義詞；關鍵字 US、NonEuro 和 NonEuropean 是 MDY 的同義詞。有關更多訊息，請參閱第 8.5 節。 內建的預設值是 ISO、MDY，但是 initdb 會以使用所選的 lc_time 語言環境相對應的設定來初始化設定內容。

IntervalStyle(enum)

設定間隔時間內容的顯示格式。設定為 sql_standard 時，將產生合於 SQL 標準的間隔時間的輸出。當 DateStyle 參數設定為 ISO 時，設定為 postgres（預設值）將會產生與 8.4 之前的 PostgreSQL 版本相容輸出。當 DateStyle 參數設定為 non-ISO 時，設定為 postgres_verbose 將生成與 8.4之前的 PostgreSQL 版本相容輸出。 設定為 iso_8601 時，將產生 ISO 8601 中 4.4.3.2 節裡所定義的時間間隔「格式與標誌符」相容的輸出。

Interval Style 參數也會影響模糊區間輸入的解釋。有關更多訊息，請參閱第 8.5.4 節。

TimeZone(string)

設定顯示和解釋時間戳記的時區。內建的預設值是 GMT，但通常會在 postgresql.conf 中被覆寫；initdb 將在安裝時取得其系統環境相對應的設定。 有關更多訊息，請參閱第 8.5.3 節。

timezone_abbreviations(string)

設定日期時間輸入能被伺服器接受的時區縮寫集合。預設是「Default」，這是一個在世界大部分地區都可以使用的集合；還有「Australia」和「India」，並且可以為特定定義安裝其他集合。 更多訊息詳見 B.3 節。

extra_float_digits(integer)

此參數調整顯示浮點數的位數，包括 float4、float8 和地理資料型別。參數值會被加到標準位數之中（FLT_DIG 或 DBL_DIG）。此值可以設定為 3，以包含部分有效數字；這對於需要精確回存浮點數資料特別有用。或者可以將其設定為負數來減少不需要的數字。請另參閱第 8.1.3 節。

client_encoding(string)

設定用戶端編碼（字元集）。預設是使用資料庫的編碼方式。在 23.3.1 節描述了 PostgreSQL 資料庫支援的字元集。

lc_messages(string)

設定訊息顯示的語言。可接受的值取決於系統；關於更多訊息，請參閱第 23.1 節。如果此參數設定為空字串（預設值），則該值將以系統相關的方式從伺服器的執行環境中繼承。

在某些系統上，此語言環境類別並不存在。設定這個參數仍然可以運作，但不會有任何影響。此外，也可能還沒有用於所需語言翻譯的訊息。在這種情況下，你會繼續看到英文訊息。

只有系統管理者可以更改此設定，因為它會影響發送到伺服器日誌以及用戶端的訊息，而不正確的值可能會影響伺服器日誌的可讀性。

lc_monetary(string)

設定用於格式化貨幣金額的區域配置，例如 to_char 系列函數。可接受的值取決於系統；關於更多訊息，請參閱第 23.1 節。如果此參數設定為空字串（預設值），則該值將以系統相關的方式從伺服器的執行環境中繼承。

lc_numeric(string)

設定用於格式化數字的區域配置，例如 to_char 系列函數。可接受的值取決於系統；關於更多訊息，請參閱第 23.1 節。如果此參數設定為空字串（預設值），則該值將以系統相關的方式從伺服器的執行環境中繼承。

lc_time(string)

設定用於格式化時間的區域配置，例如 to_char 系列函數。可接受的值取決於系統；關於更多訊息，請參閱第 23.1 節。如果此參數設定為空字串（預設值），則該值將以系統相關的方式從伺服器的執行環境中繼承。

default_text_search_config(string)

選擇全文檢索的設定，用於那些無法指定語系的全文檢索函數。 更多說明詳見第12章。內建的預設值為 pg_catalog.simple，但如果可以識別與該語言環境匹配的配置，則 initdb 將使用與所選 lc_ctype 語言環境相對應的設置來初始化配置設定。

19.11.3. 預載共享函式庫

有幾個設定可用於將共享函式庫預載到伺服器中，以便載入延伸功能並展現性能優勢。例如，設定 '$libdir / mylib' 能將 mylib.so（在某些平台上是 mylib.sl）從安裝的標準函式庫目錄中預載。這些設定之間的差異主要是控制在何時生效，以及需要哪些權限才能更改它們。

PostgreSQL 的程序語言庫可以用這種方式預載，通常語法是 '$libdir/plXXX'，其中 XXX 是 pgsql、perl、tcl 或 python。

只有專門用於 PostgreSQL 的共享函式庫才能以這種方式載入。每個支援 PostgreSQL 的函式庫都有一個「magic block」，它會被檢查以確保相容性。由於這個原因的關係，非 PostgreSQL 函式庫不能以這種方式載入。你可能可以使用作業系統的功能，例如 LD_PRELOAD。

一般來說，都需要詳閱該函式庫的文件，以獲得載入該函式庫推薦的方法.

local_preload_libraries(string)

此參數指定一個或多個要在連線啟動時預載的共享函式庫。它是逗號分隔的函式庫名稱列表，其中每個名稱都被以 LOAD 命令處理。 項目之間的空白都會被忽略；如果需要在名稱中包含空格或逗號，請用雙引號括住函式庫名稱。參數值僅在連線開始時生效。 後續更改都不起作用。如果未找到指定的函式庫，則連線嘗試將會失敗。

這個選項可以由任何使用者設定。因此，可以載入的函式庫僅限於出現在標準函式庫目錄的外掛目錄中的函式庫。 （資料庫管理員有責任確保在那裡只安裝了「安全的」函式庫。）local_preload_libraries 中的項目可以明確指定此目錄，例如 $libdir/plugins/mylib，或者只指定函式庫名稱 mylib 與 $libdir/plugins/mylib 具有相同的效果。

此功能的目的是允許非特權用戶將調教或性能測試函式庫加載到特定的連線中，而不需要明確的 LOAD 命令。為此，通常使用用戶端上的 PGOPTIONS 環境變數或透過使用 ALTER ROLE SET 來設定此參數。

但是，除非一個模組是專門設計用於非超級用戶的方式，否則這通常不適合使用。請參考使用 session_preload_libraries 參數。

session_preload_libraries(string)

此參數指定一個或多個要在連線啟動時預載的共享函式庫。它是逗號分隔的函式庫名稱列表，其中每個名稱都被以 LOAD 命令處理。. 項目之間的空白都會被忽略；如果需要在名稱中包含空格或逗號，請用雙引號括住函式庫名稱。參數值僅在連線開始時生效。 後續更改都不起作用。如果未找到指定的函式庫，則連線嘗試將會失敗。 只有超級使用者可以調整此參數。

此功能的目的是允許除錯或性能測試的函式庫載入到特定的連線中，而不需要指示明確的 LOAD 指令。例如，透過使用 ALTER ROLE SET 設定此參數，可以為指定用戶的所有連線啟用 auto_explain。此外，可以在不重新啟動服務的情況下更改此參數（但更改僅在啟動新的連線時生效），因此即使應用於所有連線，以這種方式增加新的模組也很容易。

與 shared_preload_libraries 不同，在連線啟動時載入函式庫時並沒有很大的效能優勢，相對於第一次使用時。 但是，使用連接池時會有一些優勢。

shared_preload_libraries(string)

此參數指定一個或多個要在伺服器啟動時預載的共享函式庫。它是逗號分隔的函式庫名稱列表，其中每個名稱都被以 LOAD 命令處理。. 項目之間的空白都會被忽略；如果需要在名稱中包含空格或逗號，請用雙引號括住函式庫名稱。參數值僅在伺服器啓動時生效。 後續更改都不起作用。如果未找到指定的函式庫，則連線嘗試將會失敗。

有些函式庫需要執行某些只能在 postmaster 啟動時才能執行的操作，例如分配共享記憶體，保留輕量級鎖定或啟動背景執行程序。 這些函式庫必須在伺服器啟動時通過此參數載入。有關詳細信息，請參閱各別函式庫的文件。

其他的函式庫也可以預先載入。通過預先載入共享函式庫，首次使用函式庫時可以減少啟動時間的成本。但是，啟動每個新伺服器服務的時間可能會略有增加，即使該服務從不使用該函式庫。因此，此參數僅適用於大多數連線中將使用的函式庫。另外，更改此參數需要重新啟動伺服器，因此這不適用於短期除錯事務的需求，請改為使用 session_preload_libraries。

注意

在Windows主機上，在伺服器啟動時預載函式庫不會減少啟動每個新伺服器服務所需的時間；每個伺服器服務程將重新加載所有預載函式庫。但是，shared_preload_libraries 仍然是有用的，在你的 Windows 主機的 postmaster 啓動時操作所需的函式庫。

19.11.4. 其他設定及其預設值

dynamic_library_path(string)

如果需要開啓一個可動態載入的模組，並且在 CREATE FUNCTION 或 LOAD 指令中使用沒有目錄名稱的模組檔案（即該名稱不包含斜線），系統將在此路徑中搜尋所需的檔案。

dynamic_library_path 的內容必須是由冒號（或在 Windows 上是分號）分隔的絕對路徑的列表。如果該列表項目以特殊字符串 $libdir 開頭，那麼編譯後的 PostgreSQL 函式庫目錄會被替換為 $libdir；這是安裝標準 PostgreSQL 發行版所提供的模組的路徑。（可以使用 pg_config --pkglibdir 查詢此目錄的路徑。）例如：

dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'

或者，在 Windows 環境中：

dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'

此參數的預設值是「$libdir」。如果此值設定為空字串，則將關閉自動路徑搜尋。

超級使用者可以在服務執行時更改此參數，但以這種方式完成的設定只會持續到用戶端連線結束，因此應將此方法保留用於開發階段使用。建議使用此參數的方式是在 postgresql.conf 設定檔中。

gin_fuzzy_search_limit(integer)

由 GIN 索引掃描回傳集合大小的軟上限。詳情請參閱第 64.5 節。

For additional information on tuning these settings, see Section 30.4.

19.5.1. Settings

wal_level (enum)

wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally, logicaladds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include:

But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so replica or higher must be used to enable WAL archiving (archive_mode) and streaming replication.

In logical level, the same information is logged as with replica, plus information needed to allow extracting logical change sets from the WAL. Using a level of logical will increase the WAL volume, particularly if many tables are configured for REPLICA IDENTITY FULL and many UPDATE and DELETE statements are executed.

In releases prior to 9.6, this parameter also allowed the values archive and hot_standby. These are still accepted but mapped to replica.

fsync (boolean)

If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see wal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

While turning off fsync is often a performance benefit, this can result in unrecoverable data corruption in the event of a power failure or system crash. Thus it is only advisable to turn off fsync if you can easily recreate your entire database from external data.

Examples of safe circumstances for turning off fsync include the initial loading of a new database cluster from a backup file, using a database cluster for processing a batch of data after which the database will be thrown away and recreated, or for a read-only database clone which gets recreated frequently and is not used for failover. High quality hardware alone is not a sufficient justification for turning off fsync.

For reliable recovery when changing fsync off to on, it is necessary to force all modified buffers in the kernel to durable storage. This can be done while the cluster is shutdown or while fsync is on by running initdb --sync-only, running sync, unmounting the file system, or rebooting the server.

In many situations, turning off synchronous_commit for noncritical transactions can provide much of the potential performance benefit of turning off fsync, without the attendant risks of data corruption.

fsync can only be set in the postgresql.conf file or on the server command line. If you turn this parameter off, also consider turning off full_page_writes.

synchronous_commit (enum)

Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a “success” indication to the client. Valid values are on,remote_apply, remote_write, local, and off. The default, and safe, setting is on. When off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. (The maximum delay is three times wal_writer_delay.) Unlike fsync, setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see Section 30.3.

If synchronous_standby_names is non-empty, this parameter also controls whether or not transaction commits will wait for their WAL records to be replicated to the standby server(s). When set to on, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to disk. This ensures the transaction will not be lost unless both the primary and all synchronous standbys suffer corruption of their database storage. When set to remote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s). When set to remote_write, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and written it out to their operating system. This setting is sufficient to ensure data preservation even if a standby instance of PostgreSQL were to crash, but not if the standby suffers an operating-system-level crash, since the data has not necessarily reached stable storage on the standby. Finally, the setting local causes commits to wait for local flush to disk, but not for replication. This is not usually desirable when synchronous replication is in use, but is provided for completeness.

If synchronous_standby_names is empty, the settings on, remote_apply, remote_write and local all provide the same synchronization level: transaction commits only wait for local flush to disk.

This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction.

wal_sync_method (enum)

Method used for forcing WAL updates out to disk. If fsync is off then this setting is irrelevant, since WAL file updates will not be forced out at all. Possible values are:

• open_datasync (write WAL files with open() option O_DSYNC)

• fdatasync (call fdatasync() at each commit)

• fsync (call fsync() at each commit)

• fsync_writethrough (call fsync() at each commit, forcing write-through of any disk write cache)

• open_sync (write WAL files with open() option O_SYNC)

The open_* options also use O_DIRECT if available. Not all of these choices are available on all platforms. The default is the first method in the above list that is supported by the platform, except that fdatasync is the default on Linux. The default is not necessarily ideal; it might be necessary to change this setting or other aspects of your system configuration in order to create a crash-safe configuration or achieve optimal performance. These aspects are discussed in Section 30.1. This parameter can only be set in the postgresql.conf file or on the server command line.

full_page_writes (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. This is needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at the price of increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Turning this parameter off speeds normal operation, but might lead to either unrecoverable data corruption, or silent data corruption, after a system failure. The risks are similar to turning off fsync, though smaller, and it should be turned off only based on the same circumstances recommended for that parameter.

Turning off this parameter does not affect use of WAL archiving for point-in-time recovery (PITR) (see Section 25.3).

This parameter can only be set in the postgresql.conf file or on the server command line. The default is on.

wal_log_hints (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint, even for non-critical modifications of so-called hint bits.

If data checksums are enabled, hint bit updates are always WAL-logged and this setting is ignored. You can use this setting to test how much extra WAL-logging would occur if your database had data checksums enabled.

This parameter can only be set at server start. The default value is off.

wal_compression (boolean)

When this parameter is on, the PostgreSQL server compresses a full page image written to WAL when full_page_writes is on or during a base backup. A compressed page image will be decompressed during WAL replay. The default value is off. Only superusers can change this setting.

Turning this parameter on can reduce the WAL volume without increasing the risk of unrecoverable data corruption, but at the cost of some extra CPU spent on the compression during WAL logging and on the decompression during WAL replay.

wal_buffers (integer)

The amount of shared memory used for WAL data that has not yet been written to disk. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers, but not less than 64kB nor more than the size of one WAL segment, typically 16MB. This value can be set manually if the automatic choice is too large or too small, but any positive value less than 32kB will be treated as 32kB. This parameter can only be set at server start.

The contents of the WAL buffers are written out to disk at every transaction commit, so extremely large values are unlikely to provide a significant benefit. However, setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once. The auto-tuning selected by the default setting of -1 should give reasonable results in most cases.

wal_writer_delay (integer)

Specifies how often the WAL writer flushes WAL. After flushing WAL it sleeps for wal_writer_delay milliseconds, unless woken up by an asynchronously committing transaction. If the last flush happened less than wal_writer_delay milliseconds ago and less than wal_writer_flush_after bytes of WAL have been produced since, then WAL is only written to the operating system, not flushed to disk. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting wal_writer_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.

wal_writer_flush_after (integer)

Specifies how often the WAL writer flushes WAL. If the last flush happened less than wal_writer_delay milliseconds ago and less than wal_writer_flush_after bytes of WAL have been produced since, then WAL is only written to the operating system, not flushed to disk. If wal_writer_flush_after is set to 0 then WAL data is flushed immediately. The default is 1MB. This parameter can only be set in the postgresql.conf file or on the server command line.

commit_delay (integer)

commit_delay adds a time delay, measured in microseconds, before a WAL flush is initiated. This can improve group commit throughput by allowing a larger number of transactions to commit via a single WAL flush, if system load is high enough that additional transactions become ready to commit within the given interval. However, it also increases latency by up to commit_delay microseconds for each WAL flush. Because the delay is just wasted if no other transactions become ready to commit, a delay is only performed if at least commit_siblings other transactions are active when a flush is about to be initiated. Also, no delays are performed if fsync is disabled. The default commit_delay is zero (no delay). Only superusers can change this setting.

In PostgreSQL releases prior to 9.3, commit_delay behaved differently and was much less effective: it affected only commits, rather than all WAL flushes, and waited for the entire configured delay even if the WAL flush was completed sooner. Beginning in PostgreSQL 9.3, the first process that becomes ready to flush waits for the configured interval, while subsequent processes wait only until the leader completes the flush operation.

commit_siblings (integer)

Minimum number of concurrent open transactions to require before performing the commit_delay delay. A larger value makes it more probable that at least one other transaction will become ready to commit during the delay interval. The default is five transactions.

19.5.2. Checkpoints

checkpoint_timeout (integer)

Maximum time between automatic WAL checkpoints, in seconds. The valid range is between 30 seconds and one day. The default is five minutes (5min). Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line.

checkpoint_completion_target (floating point)

Specifies the target of checkpoint completion, as a fraction of total time between checkpoints. The default is 0.5. This parameter can only be set in the postgresql.conf file or on the server command line.

checkpoint_flush_after (integer)

Whenever more than checkpoint_flush_after bytes have been written while performing a checkpoint, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likelihood of stalls when an fsync is issued at the end of the checkpoint, or when the OS writes data back in larger batches in the background. Often that will result in greatly reduced transaction latency, but there also are some cases, especially with workloads that are bigger than shared_buffers, but smaller than the OS's page cache, where performance might degrade. This setting may have no effect on some platforms. The valid range is between 0, which disables forced writeback, and 2MB. The default is 256kB on Linux, 0 elsewhere. (If BLCKSZ is not 8kB, the default and maximum values scale proportionally to it.) This parameter can only be set in the postgresql.conf file or on the server command line.

checkpoint_warning (integer)

Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happen closer together than this many seconds (which suggests that max_wal_size ought to be raised). The default is 30 seconds (30s). Zero disables the warning. No warnings will be generated if checkpoint_timeout is less than checkpoint_warning. This parameter can only be set in the postgresql.conf file or on the server command line.

max_wal_size (integer)

Maximum size to let the WAL grow to between automatic WAL checkpoints. This is a soft limit; WAL size can exceed max_wal_size under special circumstances, like under heavy load, a failing archive_command, or a high wal_keep_segments setting. The default is 1 GB. Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line.

min_wal_size (integer)

As long as WAL disk usage stays below this setting, old WAL files are always recycled for future use at a checkpoint, rather than removed. This can be used to ensure that enough WAL space is reserved to handle spikes in WAL usage, for example when running large batch jobs. The default is 80 MB. This parameter can only be set in the postgresql.conf file or on the server command line.

19.5.3. Archiving

archive_mode (enum)

When archive_mode is enabled, completed WAL segments are sent to archive storage by setting archive_command. In addition to off, to disable, there are two modes: on, and always. During normal operation, there is no difference between the two modes, but when set to always the WAL archiver is enabled also during archive recovery or standby mode. In always mode, all files restored from the archive or streamed with streaming replication will be archived (again). See Section 26.2.9 for details.

archive_mode and archive_command are separate variables so that archive_command can be changed without leaving archiving mode. This parameter can only be set at server start. archive_mode cannot be enabled when wal_level is set to minimal.

archive_command (string)

The local shell command to execute to archive a completed WAL file segment. Any %p in the string is replaced by the path name of the file to archive, and any %f is replaced by only the file name. (The path name is relative to the working directory of the server, i.e., the cluster's data directory.) Use %% to embed an actual % character in the command. It is important for the command to return a zero exit status only if it succeeds. For more information see Section 25.3.1.

This parameter can only be set in the postgresql.conf file or on the server command line. It is ignored unless archive_mode was enabled at server start. If archive_command is an empty string (the default) while archive_mode is enabled, WAL archiving is temporarily disabled, but the server continues to accumulate WAL segment files in the expectation that a command will soon be provided. Setting archive_command to a command that does nothing but return true, e.g. /bin/true (REM on Windows), effectively disables archiving, but also breaks the chain of WAL files needed for archive recovery, so it should only be used in unusual circumstances.

archive_timeout (integer)

The archive_command is only invoked for completed WAL segments. Hence, if your server generates little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To limit how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint (checkpoints are skipped if there is no database activity). Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. You should consider using streaming replication, instead of archiving, if you want data to be copied off the master server more quickly than that. This parameter can only be set in the postgresql.conf file or on the server command line.

區域設定支援是指某個應用程序，它提供有關字母、排序、數字格式等文化偏好。PostgreSQL 使用伺服器作業系統提供的標準 ISO C 和 POSIX 區域設定。有關其他訊息，請參閱作業系統文件。

23.1.1. 綜觀

使用 initdb 建立資料庫叢集時，將自動初始化語言環境支援。initdb 將預設使用其執行環境的語言環境設定初始化資料庫叢集。因此，如果您的作業系統已設定為使用資料庫叢集中所需的語言環境，那麼您毌須進行任何額外操作。如果要使用其他語言環境（或者您不確定系統設定的語言環境），可以透過指定 --locale 選項指示 initdb 確切使用哪個語言環境。例如：

initdb --locale=sv_SE

Unix 系統的這個範例將語言環境設定為瑞典語（SE）中的瑞典語（sv）。其他可能性可能包括 en_US（美國英語）和 fr_CA（加拿大法語）。如果可以將多個字元集用於語言環境，則規範可採用 language_territory.codeset 形式。例如，fr_BE.UTF-8 表示比利時（BE）中使用的法語（fr），具有 UTF-8 字元集編碼。

系統上可用的區域設定取決於作業系統供應商提供和安裝的內容。在大多數 Unix 系統上，指令 locale -a 將提供可用語言環境的列表。Windows 使用更詳細的區域設定名稱，例如 German_Germany 或 Swedish_Sweden.1252，但原則是相同的。

有時，混合來自多個語言環境的規則很有用，例如，使用英語校對規則，但使用西班牙語訊息。為了支援這一點，可以存在一組區域設定子類別，它們僅控制本地化規則的某些方面：

類別名稱轉換為 initdb 選項的名稱，以覆蓋特定類別的區域設定選項。例如，要將語言環境設定為加拿大法語，但使用美國規則格式化貨幣，請使用 initdb --locale = fr_CA --lc-monetary = en_US。

如果您希望系統的行為就像它沒有語言環境支援一樣，請使用特殊的語言環境名稱 C 或等效的 POSIX。

建立資料庫時，某些區域設定類別必須固定其值。 您可以對不同的資料庫使用不同的設定，但是一旦建立了資料庫，就無法再為該資料庫更改它們。LC_COLLATE 和 LC_CTYPE 是這些類別。它們會影響索引的排序順序，因此必須保持不變，否則文字欄位上的索引會損壞。（但是您可以使用排序規則來緩解此限制，如第 23.2 節中所述。）這些類別的預設值在執行 initdb 時確定，並且在建立新資料庫時使用這些值，除非在 CREATE DATABASE 指令中另行指定。

透過設定與語言環境類別同名的伺服器配置參數，可以隨時更改其他語言環境類別（有關詳細訊息，請參閱第 19.11.2 節）。initdb 選擇的值實際上只寫入配置文件 postgresql.conf，以在伺服器啟動時用作預設值。如果從 postgresql.conf 中刪除這些設定，則伺服器將從其執行環境繼承設定。

請注意，服務器的區域設定行為由伺服器看到的環境變數決定，而不是由任何用戶端的環境確定。因此，在啟動伺服器之前，請務必配置正確的區域設定。這樣做的結果是，如果用戶端和伺服器設定在不同的區域設定中，則訊息可能會以不同的語言顯示，具體取決於它們的來源。

注意 當我們談到從執行環境繼承語言環境時，這意味著在大多數作業系統上都有以下內容：對於給定的語言環境類別，比如排序規則，將按此順序查詢以下環境變數，直到找到一個設定：LC_ALL， LC_COLLATE（或對應於相應類別的變數），LANG。如果未設定這些環境變數，則語言環境預設為 C.

某些訊息的本地化函式庫還會查看環境變數 LANGUAGE，該變數將覆寫所有其他區域設定，以便設定訊息的語言。如有疑問，請參閱作業系統的文件，特別是有關 gettext 的文件。

要使訊息能夠轉換為用戶的偏好語言，必須在編譯時選擇 NLS（configure --enable-nls）。所有其他語言環境支援都是自動編譯的。

23.1.2. 操作行為

語系設定會影響以下的 SQL 功能：

• 使用 ORDER BY 或標準比較運算子對查詢中文字排序

• upper，lower 和 initcap 功能

• 樣式匹配運算子（LIKE，SIMILAR TO 和 POSIX 形式的正規表示式）；locales 透過字元類的正規表示式影響不區分大小寫的匹配和字元分類

• to_char 系列函數

• 索引可以與 LIKE 子句一起使用

在 PostgreSQL 中使用 C 或 POSIX 以外語言環境的缺點是對效能的影響。它會減慢字元處理速度並阻止 LIKE 使用普通索引。因此，最好只有在實際需要時才進行區域設定。

作為允許 PostgreSQL 在非 C 語言環境下使用具有 LIKE 子句索引的解決方法，存在多個自訂運算子類。允許建立一個執行嚴格的逐字元比較的索引，忽略區域設定的比較規則。有關更多訊息，請參閱第 11.9 節。另一種方法是使用 C collation 建立索引，如第 23.2 節中所述。

23.1.3. 問題

如果區域設定依上述說明操作卻不起作用的話，請檢查作業系統中的區域設定是否已正確配置。要檢查作業系統上安裝的語言環境，可以使用命令 locale -a（如果作業系統有提供的話）。

檢查 PostgreSQL 實際上是否正在使用您認為的語言環境。LC_COLLATE 和 LC_CTYPE 設定會在建立資料庫時確定，除非建立新的資料庫，否則無法變更。其他區域設定（包括 LC_MESSAGES 和 LC_MONETARY）最初由伺服器啟動的環境決定，但可以即時變更。您可以使用 SHOW 命令檢查當下有效的區域設定。

原始碼發行版中的目錄 src/test/locale 包含了 PostgreSQL 語言環境支援的測試套件。

當伺服器的訊息使用不同的語言時，透過解析錯誤訊息文字來處理伺服器端錯誤的用戶端應用程序顯然會出現問題。建議此類應用程序的作者使用錯誤代碼方案。

維護訊息翻譯目錄需要許多志願者的持續努力，他們希望看到 PostgreSQL 能夠順暢地說出他們喜歡的語言。如果您的語言訊息目前無法使用或未完全翻譯，我們將非常歡迎您的協助。如果您想幫助我們，請參閱第 54 章或寫信給開發人員的郵件列表。

19.9.1. Query and Index Statistics Collector

These parameters control server-wide statistics collection features. When statistics collection is enabled, the data that is produced can be accessed via the pg_stat and pg_statio family of system views. Refer to for more information.

track_activities (boolean)

Enables the collection of information on the currently executing command of each session, along with the time when that command began execution. This parameter is on by default. Note that even when enabled, this information is not visible to all users, only to superusers and the user owning the session being reported on, so it should not represent a security risk. Only superusers can change this setting.

track_activity_query_size (integer)

Specifies the number of bytes reserved to track the currently executing command for each active session, for the pg_stat_activity.query field. The default value is 1024. This parameter can only be set at server start.

track_counts (boolean)

Enables collection of statistics on database activity. This parameter is on by default, because the autovacuum daemon needs the collected information. Only superusers can change this setting.

track_io_timing (boolean)

Enables timing of database I/O calls. This parameter is off by default, because it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms. You can use the tool to measure the overhead of timing on your system. I/O timing information is displayed in , in the output of when the BUFFERS option is used, and by . Only superusers can change this setting.

track_functions (enum)

Enables tracking of function call counts and time used. Specify pl to track only procedural-language functions, all to also track SQL and C language functions. The default is none, which disables function statistics tracking. Only superusers can change this setting.

Note

SQL-language functions that are simple enough to be “inlined” into the calling query will not be tracked, regardless of this setting.

stats_temp_directory (string)

Sets the directory to store temporary statistics data in. This can be a path relative to the data directory or an absolute path. The default is pg_stat_tmp. Pointing this at a RAM-based file system will decrease physical I/O requirements and can lead to improved performance. This parameter can only be set in the postgresql.conf file or on the server command line.

19.9.2. Statistics Monitoring

log_statement_stats (boolean) log_parser_stats (boolean) log_planner_stats (boolean) log_executor_stats (boolean)

For each query, output performance statistics of the respective module to the server log. This is a crude profiling instrument, similar to the Unix getrusage() operating system facility. log_statement_stats reports total statement statistics, while the others report per-module statistics. log_statement_stats cannot be enabled together with any of the per-module options. All of these options are disabled by default. Only superusers can change these settings.

deadlock_timeout (integer)

這是查看是否存在交易鎖定鎖死情況之前，所等待的時間量（以毫秒為單位）。檢查鎖死是相對昂貴的，所以伺服器在每次等待鎖定時都不會執行這個動作。我們樂觀地認為鎖死在產品應用程式中並不常見，所以在檢查鎖死之前等待鎖定一段時間。增加此值可減少無謂的鎖死檢查所浪費的時間，但會減慢真正鎖死錯誤的回報速度。預設值是 1 秒，這可能是您實際需要的最小值。 在負載很重的伺服器上，您可能需要提升一些。理想情況下，此設定應該超過您典型的交易時間，以便提高在伺服器決定檢查鎖死之前鎖定就被解除的可能性。只有超級使用者可以變更此設定。

當設定 時，此參數還會確定在發出有關鎖定等待的日誌消息之前需要等待的時間長度。如果您試圖查看鎖定延遲，則可能需要設定比正常情況更短的 deadlock_timeout。

max_locks_per_transaction (integer)

共享鎖定資料表追踪 max_locks_per_transaction *（ + ）個物件（例如資料表）上的交易鎖定；因此，在任何時候都可以鎖定許多不同的物件。 此參數控制為每個交易事務分配的平均對象鎖數量; 只要所有交易的鎖定符合鎖定資料表，個別交易就可以鎖定更多的對象。 這不是可以鎖定的資料列數；該值是無限的。預設值 64 在歷史上證明是足夠的，但如果在單個交易事務中有許多不同資料表的查詢，則可能需要提高此值。例如有很多子資料表的父資料表的查詢。此參數只能在伺服器啟動時設定。

運行備用伺服器時，必須將此參數設定為與主服務器上相同或更高的值。 否則，查詢將不被允許在備用伺服器中。

max_pred_locks_per_transaction (integer)

共享的 predicate lock 資料表追踪 max_pred_locks_per_transaction *（ + ）個物件（例如資料表）上的交易鎖定；因此，在任何時候不會有比這個數字更多的物件被鎖定。此參數控制為每個交易事務分配的平均物件鎖定的數量；只要所有交易的鎖定符合鎖定資料表，個別交易就可以鎖定更多的物件。不是可以鎖定的資料列數；該值是無限的。預設值 64 通常在測試中足夠了，但如果您的用戶端在單個可序列化交易事務中觸及許多不同的資料表，您可能需要提高此值。此參數只能在伺服器啟動時設定。

max_pred_locks_per_relation (integer)

這可以控制在鎖定被提升為鎖定整個關連之前，單個關連的多少個 page 或 tuple 可以被 predicate-lock。大於或等於零的值表示絕對限制，而負值表示 除以此設定的絕對值。預設值是 -2，它保留了先前版本 PostgreSQL 的行為。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

max_pred_locks_per_page (integer)

這可以控制在將鎖定升級為覆蓋整個 page 之前，單個 page 上有多少資料列可以 predicate-locked。 預設值是 2。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

這些設定控制自動資料清理（autovacuum）功能的行為。有關更多訊息，請參閱。請注意，許多這些設定可以基於每個資料表進行調整；請參閱的說明。

autovacuum (boolean)

控制伺服器是否應該執行 autovacuum 啟動程序背景程序。這是預設開啟的；但是， 也必須啟用 autovacuum 工作。此參數只能在 postgresql.conf 檔案或伺服器命令行中設定；但是，可以透過變更資料表儲存參數來禁用單個資料表的自動清除。

請注意，即使禁用此參數，系統也會在必要時啟動自動清理過程以防止交易事務 ID 重覆。有關更多訊息，請參閱。

log_autovacuum_min_duration (integer)

如果 autovacuum 執行的每個操作至少運行了指定的毫秒數，則會被記錄下來。 將其設定為零會記錄所有自動清理操作。-1（預設值）禁用記錄自動清理操作。例如，如果將此設定為 250ms，則會記錄所有執行 250ms 或更長時間的自動清理和分析。另外，當此參數設定為除 -1 之外的任何值時，如果由於存在衝突鎖定而導致 autovacuum 操作被跳過，則會記錄一條記錄。啟用此參數可以有助於跟踪自動清理活動。此參數只能在 postgresql.conf 檔案或伺服器命令行中設定；但是可以透過變更資料表的儲存參數來覆寫單個資料表的設定。

autovacuum_max_workers (integer)

指定可能在任何時間運行的自動清理程序的最大數目（除了自動清理啟動程序）。預設值是 3。該參數只能在伺服器啟動時設定。

autovacuum_naptime (integer)

指定在任何資料庫上執行 autovacuum 之間的最小延遲。 在每一輪背景程序檢查資料庫並根據需要為該資料庫中的資料表發出 VACUUM 和 ANALYZE 命令。延遲以秒為單位進行測量，預設值為 1 分鐘（1分鐘）。該參數只能在 postgresql.conf 檔案或伺服器命令行中設定。

autovacuum_vacuum_threshold (integer)

指定在任何一個資料表中觸發 VACUUM 所需的更新或刪除 tuple 的最小數目。預設值是 50 個 tuple。此參數只能在 postgresql.conf 檔案或伺服器命令行中設定；但是可以透過變更資料儲存參數來覆寫單個資料表的設定。

autovacuum_analyze_threshold (integer)

指定在任何一個資料表中觸發 ANALYZE 所需的插入、更新或刪除的 tuple 的最小數目。預設值是 50 個 tuple。此參數只能在 postgresql.conf 檔案或伺服器命令行中設定；但是可以透過變更資料表儲存參數來覆寫單個資料表的設定。

autovacuum_vacuum_scale_factor (floating point)

決定觸發 VACUUM 時，指定要加到 autovacuum_vacuum_threshold 的資料表大小的比例。預設值是0.2（資料表大小的 20％）。此參數只能在 postgresql.conf 檔案或伺服器命令行中設定；但是可以透過變更資料表儲存參數來覆寫單個資料表的設定。

autovacuum_analyze_scale_factor (floating point)

指定在決定是否觸發 ANALYZE 時加到 autovacuum_analyze_threshold 的資料表大小的比例。預設值是 0.1（資料表大小的 10％）。此參數只能在 postgresql.conf 檔案或伺服器命令行中設定；但是可以透過變更資料表儲存參數來覆寫單個資料表的設定。

autovacuum_freeze_max_age (integer)

指定資料表的 pg_class.relfrozenxid 參數在 VACUUM 操作時被強制阻止資料表中的交易事務 ID 重覆之前可以達到的最大期限（在交易事務中）。請注意，系統將啟動 autovacuum 程序以防止重覆，即使禁用 autovacuum 時也會進行。

autovacuum_multixact_freeze_max_age (integer)

指定資料表的 pg_class.relminmxid 參數在 VACUUM 操作以防止資料表中的多個事務ID 重覆之前可以達到的最大時間（以 multixacts 表示）。請注意，系統將啟動 autovacuum 程序以防止重覆，即使禁用 autovacuum 也會進行。

autovacuum_vacuum_cost_delay (integer)

autovacuum_vacuum_cost_limit (integer)

19.8.1. 記錄在哪裡

log_destination (string)

PostgreSQL 支援多種記錄伺服器訊息的方法，包括 stderr、csvlog 和 syslog。在 Windows 上，支援 eventlog。 將此參數設定為用逗號分隔的所需日誌目標的列表。 預設情況下僅記錄到 stderr。此參數只能在 postgresql.conf 檔案或伺服器命令中設定。

如果 csvlog 包含在 log_destination 中的話，則日誌將以「逗號分隔」（CSV）格式輸出，便於將日誌載入到其他程序中。詳情請參閱。 必須啟用 才能産生 CSV 格式的日誌輸出。

如果包含 stderr 或 csvlog，則會建立 current_logfiles 檔案以記錄日誌記錄收集器和相關日誌記錄目標目前正在使用的日誌檔案的位置。這提供了一種便捷的方式來查詢目前資料庫實例正在使用的日誌。這裡有這個檔案內容的一個例子：

當一個新的日誌檔案被建立為循環的效果，並且重新載入 log_destination 時，會重新建立 current_logfiles。當 log_destination 中不包含 stderr 和 csvlog，並且日誌記錄收集器被停用時，它將被刪除。

注意

在大多數 Unix 系統上，您需要變更系統 syslog daemon 的配置，以便使用 log_destination 的 syslog 選項。PostgreSQL 可以登入到系統日誌工具 LOCAL0 到 LOCAL7（請參閱 ），但大多數平台上的預設 syslog 配置將放棄所有此類訊息。您需要加入如下的內容：

變更 syslog 背景程序的配置檔案以使其産生作用。

在 Windows 上，當您為 log_destination 使用 eventlog 選項時，應該向作業系統註冊事件來源及其函式庫，以便 Windows 事件查詢器可以清楚地顯示事件日誌消息。詳情請參閱。

logging_collector (boolean)

此參數啟用日誌收集器，這是一個後端的程序，用於攔截發送到 stderr 的日誌訊息並將其重新輸出到日誌檔案。這種方法通常比記錄到 syslog 更有用，因為某些類型的訊息可能不會出現在 syslog 輸出之中。（一個常見的案例是動態連結程序失敗訊息；另一個案例是由如 archive_command 等腳本産生的錯誤訊息。）此參數只能在伺服器啟動時設定。

注意

可以在不使用日誌收集器的情況下送到 stderr；日誌訊息將只發送到伺服器的 stderr 所指向的任何地方。但是，該方法僅適用於較低階的日誌程序，因為它不提供日誌檔案覆寫的簡便方法。另外，在某些不使用日誌收集器的平台上可能會導致日誌輸出遺失或出現亂碼，因為同時寫入同一日誌檔案的多個程序可能會覆蓋彼此的輸出。

注意

日誌記錄收集器旨在永不遺失訊息。這意味著，如果負載極高，則在收集器落後時嘗試發送其他日誌消息時，伺服器程序可能會被阻止繼續執行。相比之下，如果系統日誌不能寫入訊息，系統日誌更喜歡丟棄訊息，這意味著在這種情況下它可能無法記錄某些訊息，但不會阻塞系統的其餘部分。

log_directory (string)

當啟用 logging_collector 時，此參數確定將在其中建立日誌檔案的目錄。它可以被指定為絕對路徑，或相對於叢集的 data 目錄。該參數只能在 postgresql.conf 檔案或伺服器指令行中設定。預設值是 log。

log_filename (string)

如果您指定的檔案名稱不含跳脫符號，則應該計劃使用日誌覆寫程序來避免最後存滿整個磁碟。在 8.4 之前的版本中，如果不存在 ％ 跳脫符號，PostgreSQL 會追加新日誌檔案建立時間的紀元，但已經不再是這種情況了。

如果在 log_destination 中啟用 CSV 格式的輸出，則會將時間戳記檔案名稱附加.csv 以建立 CSV 格式輸出的檔案名稱。 （如果 log_filename 以 .log 結尾，則替換後綴。）

該參數只能在 postgresql.conf 檔案或伺服器指令中設定。

log_file_mode (integer)

在 Unix 系統上，此參數在啟用 logging_collector 時設定日誌檔案的權限。（在 Microsoft Windows 上，此參數將被忽略。）參數值預期為以 chmod 和 umask 系統呼叫接受的格式來指定的數字模式。（要使用習慣的八進制格式，數字必須以 0（零）開頭。）

預設權限為 0600，這意味著只有伺服器擁有者才能讀取或寫入日誌檔案。另一個常用的設定是 0640，允許擁有者組群的成員讀取文件。 但是請注意，要使用這種設定，您需要變更 log_directory 以將檔案儲存在叢集 data 目錄之外的某個位置。無論如何，使日誌檔案讓任何人都可讀是不明智的，因為它們可能包含敏感資料。

該參數只能在 postgresql.conf 檔案或伺服器指令中設定。

log_rotation_age (integer)

當啟用 logging_collector 時，此參數決定單個日誌檔案的最長生命週期。經過指定的分鐘後，會建立一個新的日誌檔案。設定為零以停用基於時間的新日誌檔案建立。該參數只能在 postgresql.conf 檔案或伺服器指令中設定。

log_rotation_size (integer)

當啟用 logging_collector 時，此參數決定單個日誌檔的大小上限。在超過上限的記錄被發送到日誌檔案後，將建立一個新的日誌檔案。設定為零以禁用基於大小的新日誌檔案創立。該參數只能在 postgresql.conf 檔案或伺服器指令中設定。

log_truncate_on_rotation (boolean)

當啟用 logging_collector 時，此參數將導致 PostgreSQL 分割（覆蓋）而不是追加到任何具有相同名稱的現有日誌檔案。 但是，分割只會在由於基於時間的覆寫而打開新檔案時發生，而不是在伺服器啟動或基於大小的覆寫情況進行。關閉時，預先存在的檔案將被附加到所有情況下。例如，將此設定與 log_filename（如 postgresql-％H.log）結合使用可産生 24 個小時日誌檔案，然後循環覆蓋它們。該參數只能在 postgresql.conf 檔案或伺服器指令中設定。

例如：要保留 7 天的日誌，每天一個名稱為 server_log.Mon，server_log.Tue 等的日誌檔案，並自動使用本週的日誌覆蓋上週的日誌，將 log_filename 設定為server_log.％a，將 log_truncate_on_rotation 設定為 on，並將 log_rotation_age 到 1440。

又例如：要保留 24 小時的日誌，每小時記錄一個日誌檔案，但是如果日誌檔案大小超過 1GB，則會盡快輪換，將 log_filename 設定為 server_log.％H％M，log_truncate_on_rotation 為 on，log_rotation_age 為 60，log_rotation_size 為1000000。在 log_filename 中包含 ％M 允許可能出現的任何大小驅動的旋轉，以選擇與小時的初始檔案名稱不同的檔案名稱。

syslog_facility (enum)

當啟用日誌記錄到 syslog 時，此參數確定要使用的系統日誌的「設施」。 您可以選擇 LOCAL0，LOCAL1，LOCAL2，LOCAL3，LOCAL4，LOCAL5，LOCAL6，LOCAL7；預設值是 LOCAL0。另請參閱系統的 syslog 背景程序的文件。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

syslog_ident (string)

當啟用日誌記錄到系統日誌時，此參數決定用於在系統日誌中識別 PostgreSQL 記錄的程序名稱。預設是 postgres。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

syslog_sequence_numbers (boolean)

當記錄到系統日誌並且這是啓用的（預設），那麼每筆記錄將以遞增的序列號碼（例如[2]）作為前置內容。這規避了「---最後一條記錄重複 N 次---」抑制了許多 syslog 實務上預設執行的操作。在更現代的 syslog 實作中，可以設定重複的記錄抑制（例如，rsyslog 中的 $RepeatedMsgReduction），所以這可能不是必要的。另外，如果你真的想要抑制重複的記錄，就可以關掉它。

此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

syslog_split_messages (boolean)

當啟用日誌記錄到 syslog 時，此參數決定記錄如何傳遞到系統日誌。啟用時（預設），記錄按行分割，使得行長在 1024 字元以下，這是傳統 syslog 實作的典型大小限制。關閉時，PostgreSQL 伺服器日誌記錄會按原樣傳遞到系統日誌服務，並由系統日誌服務來處理潛在的龐大記錄。

如果 syslog 最終記錄到文字檔案，那麼效果將是相同的，並且最好將設定保留為開啟狀態，因為大多數 syslog 實作無法處理大量記錄，或者需要專門設定以處理它們。但是，如果系統日誌最終寫入其他媒體，將記錄邏輯上地組合在一起可能是必要的或更有用的。

此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

event_source (string)

當啟用記錄到事件日誌時，此參數確定用於在記錄中識別 PostgreSQL 記錄的程序名稱。預設是 PostgreSQL。 此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

19.8.2. 何時要記錄

client_min_messages (enum)

控制將哪些訊息等級要發送到用戶端。有效的值為 DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、LOG、NOTICE、WARNING、ERROR、FATAL 和 PANIC。每個等級包括其後的所有等級。等級越低，發送的訊息越少。預設值為 NOTICE。請注意，LOG 在此處的排名與 log_min_messages 中的排序不同。

log_min_messages (enum)

控制將哪些訊息等級寫入伺服器日誌。有效的值為 DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING、ERROR、LOG、FATAL 和 PANIC。每個等級包括其後的所有等級。等級越低，發送到日誌的訊息越少。預設值為 WARNING。請注意，LOG 在此處的排序與 client_min_messages 中的排名不同。只有超級使用者才能變更此設定。

log_min_error_statement (enum)

將導致錯誤情況的 SQL 語句記錄在伺服器日誌中。當下的 SQL 語句包含在指定的嚴重性或更高等級的任何訊息日誌項目中。有效值為 DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING、ERROR、LOG、FATAL 和 PANIC。預設值為 ERROR，這意味著將會記錄 ERROR、LOG、FATL 或 PANIC。要有效地關閉失敗語句的日誌記錄，請將此參數設定為PANIC。只有超級使用者才能變更此設定。

log_min_duration_statement (integer)

如果語句執行達到指定的毫秒數，則會記錄每個已完成語句的執行時間。將此值設定為零將輸出所有語句的執行時間。減號（預設值）停用日誌記錄語句執行時間。例如，如果將其設定為 250ms，則將記錄執行 250ms 或更長時間的所有 SQL 語句。啟用此參數有助於在應用程序中追踪未優化的查詢。只有超級使用者才能變更此設定。

對於使用延伸查詢協議的用戶端，Parse、Bind 和 Execute 步驟的執行時間是獨立記錄的。

注意

將此選項與 log_statement 一起使用時，由於 log_statement 而記錄的語句文字將不會在執行時間日誌訊息中重複。如果您不使用 syslog，建議您使用 log_line_prefix 記錄 PID 或連線 ID，以便可以使用 PID 或連線 ID將語句訊息連接到之後的執行時間訊息。

Table 19.1. Message Severity Levels

19.8.3. 要記錄什麼

application_name (string)

debug_print_parse (boolean) debug_print_rewritten (boolean) debug_print_plan (boolean)

debug_pretty_print (boolean)

設定後，debug_pretty_print 會放進 debug_print_parse，debug_print_rewritten 或debug_print_plan 産生的訊息。與關閉時使用的緊湊格式相比，這會產生更多可讀但更長的輸出。它預設開啟。

log_checkpoints (boolean)

使檢查點和重新啟動點記錄在伺服器日誌中。日誌訊息中包含一些統計訊息，包括寫入的緩衝區數和寫入時間。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設為關閉。

log_connections (boolean)

導致記錄每個嘗試連線到伺服器，以及成功完成用戶端身份驗證。只有超級使用者才能在連線開始時變更此參數，之後在連線中無法更改。預設為關閉。

注意 某些用戶端程序（如 psql）在確定是否需要密碼時會嘗試連線兩次，因此重複的“connection received”訊息不一定表示存在問題。

log_disconnections (boolean)

導致連線終止會被記錄。日誌輸出提供類似於 log_connections 的訊息，以及連線的持續時間。只有超級使用者才能在連線開始時變更此參數，並且在連線中無法更改。預設為關閉。

log_duration (boolean)

記錄每個已完成語句的持續時間。預設為關閉。只有超級使用者才能變更此設定。

對於使用延伸查詢協議的用戶端，Parse、Bind 和 Execute 步驟的持續時間是獨立記錄的。

log_error_verbosity (enum)

log_hostname (boolean)

預設情況下，連線日誌訊息僅顯示連線主機的 IP 位址。打開此參數就會記錄主機名。請注意，根據您的主機名稱解析設定，這可能會造成不可忽視的效能損失。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

log_line_prefix (string)

這是一個 printf 樣式的字串，在每個日誌的開頭輸出。%字元開始「跳脫序列（escape sequence）」，它們會被狀態訊息替換，如下所述。 無法識別的跳脫字元會被忽略。其他字元將直接複製到日誌內容。某些跳脫字元只能由連線程序識別，並且將被背景程序（例如主伺服器程序）視為空。透過在 % 之後和選項之前指定數字文字，可以向左或向右對齊狀態訊息。負值會將狀態信息在右側填充空格以給予一個最小寬度，而正值將填充在左側。填充可用於增加日誌檔案中的可讀性。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值為'%m [%p]'，用於記錄時間戳記和程序 ID。

%c 跳脫字元輸出一個幾乎唯一的連線指標，兩個由點分隔的 4 位元組的十六進制數字（不帶前導零）組成。數字是流程開始時間和程序 ID，因此 %c 也可以用作輸出這些項目的節省空間的方式。例如，要從 pg_stat_activity 産生連線指標，請使用以下查詢：

小技巧 如果為 log_line_prefix 設定了非空值，則通常應將其最後一個字元設為空格，以便與日誌行的其餘部分進行視覺隔離。也可以使用標點符號。

小技巧 Syslog 會産生成自己的時間戳記和程序 ID 訊息，因此如果要輸出到 syslog，可能不希望包含這些跳脫字元。

小技巧 當包含僅在使用者或資料庫名稱等連線（後端）內容中可用的訊息時，%q 跳脫字元非常有用。例如：

log_lock_waits (boolean)

log_statement (enum)

控制記錄哪些 SQL 語句。有效值為 none（off），ddl，mod 和 all（所有語句）。 ddl 記錄所有資料定義語句，例如 CREATE，ALTER 和 DROP 語句。mod 記錄所有 ddl 語句，以及 INSERT，UPDATE，DELETE，TRUNCATE 和 COPY FROM 等資料修改語句。如果包含的指令屬於適合的類型，也會記錄 PREPARE，EXECUTE 和 EXPLAIN ANALYZE 語句。對於使用延伸查詢協議的用戶端，在收到 Execute 訊息時會發生日誌記錄，並且包含 Bind 參數的值（任何嵌入的單引號標記加倍）。

預設值為 none。只有超級使用者才能變更此設定。

注意 即使是 log_statement = all 設定也不會記錄包含簡單語法錯誤的語句，因為只有在完成基本分析以確定語句類型後才會發出日誌訊息。在延伸查詢協議的情況下，此設定同樣不記錄在執行階段之前失敗的語句（即，在解析分析或計劃期間）。將 log_min_error_statement 設定為 ERROR（或更低）以記錄此類語句。

log_replication_commands (boolean)

log_temp_files (integer)

控制臨時檔案名稱和大小的記錄。可以為排序，雜湊和臨時查詢結果建立臨時檔案。移除時，會為每個臨時檔案建立一個日誌項目。值為 0 時會記錄所有臨時檔案訊息，而正值僅記錄大小大於或等於指定 KB 的檔案。預設設定為 -1，停用此類日誌記錄。只有超級使用者才能變更此設定。

log_timezone (string)

19.8.4. 使用 CSV 格式輸出記錄

在 log_destination 列表中包含 csvlog 提供了將日誌檔案匯入資料庫資料表的便捷方法。此選項以逗號分隔（CSV）格式送出日誌資料，其中包含以下欄位：時間戳記，毫秒，使用者名稱，資料庫名稱，程序 ID，用戶端主機：連接埠號號，連線 ID，每個連線的行號，指令標記，連線開始時間，虛擬交易事務 ID，一般交易事務 ID，錯誤嚴重性，SQLSTATE 代碼，錯誤訊息，錯誤訊息的詳細訊息，提示，導致錯誤的內部查詢（如果有的話），其中錯誤位置的字串位置，錯誤內容，導致錯誤的使用者查詢（如果有的話，由 log_min_error_statement 啟用），其中錯誤位置的字元數，PostgreSQL 原始碼中的錯誤位置（如果 log_error_verbosity 設定為 verbose）和應用程序名稱。以下是用於儲存 CSV 格式日誌輸出的範例資料表定義：

要將日誌檔案匯入此資料表，請使用 COPY FROM 指令：

您需要做一些事情來簡化匯入 CSV 日誌檔案：

1. 設定 log_filename 和 log_rotation_age 使日誌檔案提供一致性，可預測的命名方案。這使您可以預測檔案名稱會是什麼，並知道單個日誌檔案何時完成而可以匯入。

2. 將 log_rotation_size 設定為 0 可停用基於大小的日誌輪轉，因為它會使日誌檔案名稱難以預測。

3. 將 log_truncate_on_rotation 設定為 on，以便舊的日誌資料不會與同一檔案中的新資料混合。

4. 上面的資料表定義包含主鍵規範。這有助於防止意外匯入兩次相同的訊息。COPY 指令一次提交它匯入的所有資料，因此任何錯誤都會導致整個匯入失敗。如果匯入部分日誌檔案，並在稍後再次匯入該檔案時，主鍵重覆將導致匯入失敗。請等到日誌完成關閉後再匯入。此過程還可以防止意外匯入尚未完全寫入的部分資料列，這也會導致 COPY 失敗。

19.8.5. 程序標題（Process Title）

cluster_name (string)

設定此叢集中所有伺服器程序的程序標題中顯示的叢集名稱。該名稱可以是任何少於 NAMEDATALEN 字元數的字串（標準版本中為 64 個字元）。cluster_name 值中只能使用可列印的 ASCII 字元。其他字元將被替換為問號（？）。如果此參數設定為空字串“”（這是預設值），則不顯示名稱。此參數只能在伺服器啟動時設定。

update_process_title (boolean)

每當伺服器收到新的 SQL 指令時，都可以更新程序標題。此設定在大多數平台上預設為開啟，不過在 Windows 上預設為關閉，因為在 Windows 上更新程序標題的開銷較大。只有超級使用者可以變更此設定。

這些設定控制內建的串流複寫功能行為（請參閱）。伺服器指的是主伺服務器或備用伺服器。主伺服器可以發送資料，而備用伺服器始終是複寫資料的接收者。當使用串聯複寫（請參閱）時，備用伺服器也可以是發送者和接收者。參數主要用於發送和備用伺服器，但某些參數僅在主伺服器上有意義。如果需要，設定是跨群集的，不會産生問題。

19.6.1. 發送伺服器（Sender Server）

可以在將資料複寫發送到一個或多個備用伺服器的任何伺服器上設定這些參數。主伺服器始終是發送伺服器，因此必須在主伺服器上設定這些參數。備用資料庫成為主資料庫後，這些參數的作用也不會改變。

max_wal_senders (integer)

指定來自備用伺服器或串流複寫備份用戶端的最大同時連線數（即同時運行的 WAL 發送程序的最大數量）。預設值為 10，0 表示停用複寫。WAL 發送方程序也計入連線總數，因此參數不能設定高於 。突然串流用戶端中斷連線可能會導致遺留連線插槽，直到達到超時。因此此參數應設定為略高於預期用戶端的最大數量，以便中斷連線的用戶端可以立即重新連線。此參數只能在伺服器啟動時設定。wal_level 必須設定為副本或更高版本才能允許來自備用伺服器的連線。

max_replication_slots (integer)

指定伺服器可以支援的最大複寫槽數（請參閱）。預設值為 10。此參數只能在伺服器啟動時設定。必須將 wal_level 設定為副本或更高版本才能使用複寫槽。將其設定為低於目前現有複寫插槽數的值將阻止伺服器啟動。

wal_keep_segments (integer)

指定保留在 pg_wal 目錄中的過時日誌段落檔案的最小數量，以防備用伺服器需要取得它們以進行串流複寫。每個段落段通常為 16 MB。如果連線到發送伺服器的備用伺服器落後於 wal_keep_segments 個段落以上，則發送伺服器可能會刪除備用資料庫仍需要的 WAL 段落，在這種情況下，複寫連線將會終止。因此，下游連線最終也會失敗。（但是，如果正在使用 WAL Archive，則備用伺服器可以透過從 Archive 中取得段落來進行回復。）

這僅設定 pg_wal 中保留的最小段落數量；系統可能需要為 WAL 存檔保留更多段落或從檢查點回復。如果 wal_keep_segments 為零（預設值），則系統不會為備用目的保留任何額外的段落，因此備用伺服器可用的舊 WAL 段落數是上一個檢查點的位置和WAL 歸檔狀態的函數。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

wal_sender_timeout (integer)

終止靜止狀態超過指定毫秒數的複寫連線。這對於發送伺服器檢測備用伺服器當機或網路斷線很有用。值為零會停用超時機制。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值為 60 秒。

track_commit_timestamp (boolean)

記錄事務的提交時間。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設值為 off。

19.6.2. 主要伺服器（Master Server）

synchronous_standby_names (string)

用於此目的的備用伺服器的名稱是以備用資料庫的 application_name 設定，在備用資料庫的連線資訊中設定。如果是物理性複寫的備用，則應在 recovery.conf 中的 primary_conninfo 設定中進行設定；預設是 walreceiver。對於邏輯性複寫，可以在訂閱的連線訊息中設定，並且預設為訂閱名稱。對於其他複寫的串流使用者，請查閱其文件。

此參數使用以下任一語法指定備用伺服器列表：

其中 num_sync 是交易事務需要等待回覆的同步備用數量，而 standby_name 是備用伺服器的名稱。FIRST 和 ANY 指定從列出的伺服器中選擇同步備用資料庫的方法。

關鍵字 FIRST 與 num_sync 合併使用，指定基於優先的同步複寫，讓事務提交等待，直到將其 WAL 記錄複寫到優先選擇的 num_sync 同步備用資料庫。例如，FIRST 3（s1，s2，s3，s4）的設定將使得每個提交等待從備用伺服器 s1，s2，s3 和 s4 中選擇的三個較優先的備用資料庫回覆。名稱在列表中較早出現的備用資料庫具有較高的優先等級，並被視為是同步的。此列表中稍後出現的其他備用伺服器代表潛在的同步備用資料庫。如果任何當下的同步備用資料庫因任何原因斷開連線，它將立即被替換為次高優先等級的備用資料庫。關鍵字 FIRST 是選用的。

關鍵字 ANY 與 num_sync 一起使用，指定需要仲裁的同步複寫，使事務提交等待，直到將其 WAL 記錄複寫到至少 num_sync 列出的備用資料庫。例如，ANY 3（s1，s2，s3，s4）的設定將使得每個提交在 s1，s2，s3 和 s4 的至少任何三個備用資料回覆時繼續進行。

FIRST 和 ANY 都不區分大小寫。 如果將這些關鍵字用作備用伺服器的名稱，則其 standby_name 必須使用雙引號。

第三種語法在 PostgreSQL 版本 9.6 之前使用，仍然受支援。它與 FIRST 和 num_sync 等於 1 的第一個語法相同。例如，FIRST 1（s1，s2）和 s1，s2 具有相同的含義：s1 或 s2 被選為同步的備用伺服器。

特殊符號 * 表示匹配任何備用名稱。

沒有其他機制來強制備用名稱的唯一性。如果重複的話，其中一個備用資料庫將被視為更優先的，但無法確切說是哪一個。

注意 每個 standby_name 都應具有有效 SQL 識別字的形式，除非是 *。如有必要，您可以使用雙引號。但請注意，standby_names 與備用 application name 都不區分大小寫，無論是否為雙引號。

此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

vacuum_defer_cleanup_age (integer)

您還應該考慮在備用伺服器上設定 hot_standby_feedback 作為使用此參數的替代方法。

這不會阻止已達到 old_snapshot_threshold 指定期間的過時資料列清除。

19.6.3. Standby Servers

這些設定控制要接收複寫資料的備用伺服器行為，與主伺服器上的設定是無關的。

hot_standby (boolean)

max_standby_archive_delay (integer)

請注意，max_standby_archive_delay 與取消前查詢可以執行的最長時間不同；相反地，它是允許套用任何一個 WAL 資料段的最大總時間。因此，如果一個查詢在 WAL 資料段中導致顯著延遲，則後續衝突查詢將具有更少的寬限時間。

max_standby_streaming_delay (integer)

請注意，max_standby_streaming_delay 與取消前查詢可以執行的最長時間不同；相反地，它是從主伺服器收到 WAL 資料後允許套用的最大總時間。因此，如果一個查詢導致顯著延遲，則後續衝突查詢將具有更少的寬限時間，直到備用伺服器再次趕上。

wal_receiver_status_interval (integer)

hot_standby_feedback (boolean)

Specifies whether or not a hot standby will send feedback to the primary or upstream standby about queries currently executing on the standby. This parameter can be used to eliminate query cancels caused by cleanup records, but can cause database bloat on the primary for some workloads. Feedback messages will not be sent more frequently than once per wal_receiver_status_interval. The default value is off. This parameter can only be set in the postgresql.conf file or on the server command line.

If cascaded replication is in use the feedback is passed upstream until it eventually reaches the primary. Standbys make no other use of feedback they receive other than to pass upstream.

This setting does not override the behavior of old_snapshot_threshold on the primary; a snapshot on the standby which exceeds the primary's age threshold can become invalid, resulting in cancellation of transactions on the standby. This is because old_snapshot_threshold is intended to provide an absolute limit on the time which dead rows can contribute to bloat, which would otherwise be violated because of the configuration of a standby.

wal_receiver_timeout (integer)

Terminate replication connections that are inactive longer than the specified number of milliseconds. This is useful for the receiving standby server to detect a primary node crash or network outage. A value of zero disables the timeout mechanism. This parameter can only be set in the postgresql.conf file or on the server command line. The default value is 60 seconds.

wal_retrieve_retry_interval (integer)

Specify how long the standby server should wait when WAL data is not available from any sources (streaming replication, local pg_wal or WAL archive) before retrying to retrieve WAL data. This parameter can only be set in the postgresql.conf file or on the server command line. The default value is 5 seconds. Units are milliseconds if not specified.

This parameter is useful in configurations where a node in recovery needs to control the amount of time to wait for new WAL data to be available. For example, in archive recovery, it is possible to make the recovery more responsive in the detection of a new WAL log file by reducing the value of this parameter. On a system with low WAL activity, increasing it reduces the amount of requests necessary to access WAL archives, something useful for example in cloud environments where the amount of times an infrastructure is accessed is taken into account.

19.6.4. Subscribers

These settings control the behavior of a logical replication subscriber. Their values on the publisher are irrelevant.

Note that wal_receiver_timeout, wal_receiver_status_interval and wal_retrieve_retry_interval configuration parameters affect the logical replication workers as well.

max_logical_replication_workers (int)

Specifies maximum number of logical replication workers. This includes both apply workers and table synchronization workers.

Logical replication workers are taken from the pool defined by max_worker_processes.

The default value is 4.

max_sync_workers_per_subscription (integer)

Maximum number of synchronization workers per subscription. This parameter controls the amount of parallelism of the initial data copy during the subscription initialization or when new tables are added.

Currently, there can be only one synchronization worker per table.

The synchronization workers are taken from the pool defined by max_logical_replication_workers.

The default value is 2.

19.13.1. Previous PostgreSQL Versions

array_nulls

(

boolean

)

This controls whether the array input parser recognizes unquotedNULLas specifying a null array element. By default, this ison, allowing array values containing null values to be entered. However,PostgreSQLversions before 8.2 did not support null values in arrays, and therefore would treatNULLas specifying a normal array element with the string value“NULL”. For backward compatibility with applications that require the old behavior, this variable can be turnedoff.

Note that it is possible to create array values containing null values even when this variable isoff.

backslash_quote

(

enum

)

This controls whether a quote mark can be represented by\'in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') butPostgreSQLhas historically also accepted\'. However, use of\'creates security risks because in some client character set encodings, there are multibyte characters in which the last byte is numerically equivalent to ASCII\. If client-side code does escaping incorrectly then a SQL-injection attack is possible. This risk can be prevented by making the server reject queries in which a quote mark appears to be escaped by a backslash. The allowed values ofbackslash_quoteareon(allow\'always),off(reject always), andsafe_encoding(allow only if client encoding does not allow ASCII\within a multibyte character).safe_encodingis the default setting.

Note that in a standard-conforming string literal,\just means\anyway. This parameter only affects the handling of non-standard-conforming literals, including escape string syntax (E'...').

default_with_oids

(

boolean

)

This controls whetherCREATE TABLEandCREATE TABLE ASinclude an OID column in newly-created tables, if neitherWITH OIDSnorWITHOUT OIDSis specified. It also determines whether OIDs will be included in tables created bySELECT INTO. The parameter isoffby default; inPostgreSQL8.0 and earlier, it wasonby default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specifyWITH OIDSwhen creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

escape_string_warning

(

boolean

)

When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('...'syntax) andstandard_conforming_stringsis off. The default ison.

Applications that wish to use backslash as escape should be modified to use escape string syntax (E'...'), because the default behavior of ordinary strings is now to treat backslash as an ordinary character, per SQL standard. This variable can be enabled to help locate code that needs to be changed.

lo_compat_privileges

(

boolean

)

InPostgreSQLreleases prior to 9.0, large objects did not have access privileges and were, therefore, always readable and writable by all users. Setting this variable toondisables the new privilege checks, for compatibility with prior releases. The default isoff. Only superusers can change this setting.

Setting this variable does not disable all security checks related to large objects — only those for which the default behavior has changed inPostgreSQL9.0. For example,lo_import()andlo_export()need superuser privileges regardless of this setting.

operator_precedence_warning

(

boolean

)

When on, the parser will emit a warning for any construct that might have changed meanings sincePostgreSQL9.4 as a result of changes in operator precedence. This is useful for auditing applications to see if precedence changes have broken anything; but it is not meant to be kept turned on in production, since it will warn about some perfectly valid, standard-compliant SQL code. The default isoff.

quote_all_identifiers

(

boolean

)

standard_conforming_strings

(

boolean

)

synchronize_seqscans

(

boolean

)

This allows sequential scans of large tables to synchronize with each other, so that concurrent scans read the same block at about the same time and hence share the I/O workload. When this is enabled, a scan might start in the middle of the table and then“wrap around”the end to cover all rows, so as to synchronize with the activity of scans already in progress. This can result in unpredictable changes in the row ordering returned by queries that have noORDER BYclause. Setting this parameter tooffensures the pre-8.3 behavior in which a sequential scan always starts from the beginning of the table. The default ison.

19.13.2. Platform and Client Compatibility

transform_null_equals

(

boolean

)

When on, expressions of the formexpr= NULL(orNULL =expr) are treated asexpr_IS NULL, that is, they return true ifexprevaluates to the null value, and false otherwise. The correct SQL-spec-compliant behavior ofexpr_= NULLis to always return null (unknown). Therefore this parameter defaults tooff.

However, filtered forms inMicrosoft Accessgenerate queries that appear to useexpr= NULLto test for null values, so if you use that interface to access the database you might want to turn this option on. Since expressions of the formexpr= NULLalways return the null value (using the SQL standard interpretation), they are not very useful and do not appear often in normal applications so this option does little harm in practice. But new users are frequently confused about the semantics of expressions involving null values, so this option is off by default.

Note that this option only affects the exact form= NULL, not other comparison operators or other expressions that are computationally equivalent to some expression involving the equals operator (such asIN). Thus, this option is not a general fix for bad programming.

在某些情況下，使用 指令或一系列單獨的重建步驟定期重建索引是值得的。

已完全為空的 B-tree 索引頁面將被回收以供重複使用。但是，仍然存在空間使用效率低的可能性：如果頁面上除了少數索引鍵之外的所有索引鍵都已被刪除，則頁面仍然會被分配。因此，最終刪除每個範圍中的大多數但不是所有鍵的使用模式將會發現空間使用率不佳。對於此類使用模式，建議定期重建索引。

非 B-tree 索引中膨脹的可能性尚未具有很好的研究。所以在使用任何非 B-tree 索引類型時，定期監視索引的磁碟大小是個好主意。

此外，對於 B-tree 索引，新建構的索引比多次更新的索引要快一些，因為邏輯上相鄰的頁面通常在新建構的索引中也是物理上相鄰的。（這種考慮不適用於非 B-tree 索引。）為了提高存取速度，定期重建索引會是值得的。

在所有情況下，REINDEX 都可以很安全，簡單地使用。但由於該指令需要獨占資料表鎖定，因此通常最好使用一系列建立和替換步驟來執行索引重建。使用 CONCURRENTLY 選項支援 的索引類型可以透過這種方式重新建立。如果成功並且結果索引有效，則可以使用 和 的組合將原始索引替換為新建構的索引。當索引用於強制唯一性或其他約束時，可能需要使用 將現有限制條件與新索引強制執行的限制條件交換。 在使用之前仔細檢查這種多步驟重建方法，因為對這些索引透過這種方式重新建立索引可能有些限制，並且必須處理錯誤。

19.7.1. 規劃方法配置

這些配置參數提供了影響查詢最佳化程序選擇的查詢計劃決策方法。如果最佳化程序為特定查詢選擇的預設計劃並非最佳，則臨時的解決方案是使用這些配置參數來強制最佳化程序選擇不同的計劃。提高最佳化程序選擇的計劃素質的有效方法包括了調整計劃程序成本常數（請參閱第 19.7.2 節），手動執行 ANALYZE，增加 default_statistics_target 配置參數的值，以及增加為特定欄位收集的統計訊息量，使用 ALTER TABLE SET STATISTICS。

enable_bitmapscan (boolean)

啟用或停用查詢計劃程序使用 bitmap 掃描計劃類型。預設為開啓。

enable_gathermerge (boolean)

啟用或停用查詢計劃程序使用 gather merge 計劃類型。預設為開啓。

enable_hashagg (boolean)

啟用或停用查詢計劃程序使用 hashed aggregation 計劃類型。預設為開啓。

enable_hashjoin (boolean)

啟用或停用查詢計劃程序使用 hash-join 計劃類型。預設為開啓。

enable_indexscan (boolean)

啟用或停用查詢計劃程序使用 index-scan 計劃類型。預設為開啓。

enable_indexonlyscan (boolean)

啟用或停用查詢計劃程序使用 index-only 掃描計劃類型（請參閱第 11.11 節）。預設為開啓。

enable_material (boolean)

啟用或停用查詢計劃程序對實作的使用。完全抑制實作是不可能的，但是關閉此變數會阻止計劃程序插入實體化的節點，除非真的需要它。預設為開啓。

enable_mergejoin (boolean)

啟用或停用查詢計劃程序使用 merge-join 計劃類型。預設為開啓。

enable_nestloop (boolean)

啟用或停用查詢計劃程序使用 nested-loop join 計劃。完全抑制 nested-loop join 是不可能的，但如果有其他可用方法，則關閉此變數會阻止規劃器使用它。預設為開啓。

enable_seqscan (boolean)

啟用或停用查詢計劃程序使用循序掃描計劃類型。完全抑制循序掃描是不可能的，但如果有其他方法可用，則關閉此變數會阻止計劃程序使用。預設為開啓。

enable_sort (boolean)

啟用或停用查詢計劃程序使用明確的排序步驟。完全抑制明確排序是不可能的，但如果有其他可用方法，則關閉此變數會阻止計劃程序使用。預設為開啓。

enable_tidscan (boolean)

啟用或停用查詢計劃程序使用 TID 掃描計劃類型。預設為開啓。

19.7.2. 規劃程序成本常數

本節中描述的成本變數是以比例來使用的。只有它們的相對值很重要，因此按相同因子放大或縮小它們將不會讓規劃程式的選擇有所變化。預設情況下，這些成本變數基於連續頁面讀取的成本；也就是說，seq_page_cost 通常設定為 1.0，其他成本變數是相對參考其設定的。 但是，如果您願意，可以使用不同的比例，例如特定主機上的實際執行時間（以毫秒為單位）。

注意 不幸的是，並沒有明確定義的方法來決定成本變數的理想值。它們最好被視為特定安裝環境可能接受的所有查詢組合的平均值。這意味著僅僅根據一些實驗來改變它們都不是真正的最佳。

seq_page_cost (floating point)

設定計劃程序對磁碟頁面讀取的成本估計，此成本是一系列連續讀取的一部分。預設值為 1.0。透過設定同名的 tablespace 參數，可以為特定資料表空間中的資料表和索引覆寫此值（請參閱 ALTER TABLESPACE）。

random_page_cost (floating point)

Sets the planner's estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0. This value can be overridden for tables and indexes in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLESPACE).

Reducing this value relative to seq_page_cost will cause the system to prefer index scans; raising it will make index scans look relatively more expensive. You can raise or lower both values together to change the importance of disk I/O costs relative to CPU costs, which are described by the following parameters.

Random access to mechanical disk storage is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to disk, such as indexed reads, are assumed to be in cache. The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached.

If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost.

Tip

Although the system will let you set random_page_cost to less than seq_page_cost, it is not physically sensible to do so. However, setting them equal makes sense if the database is entirely cached in RAM, since in that case there is no penalty for touching pages out of sequence. Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be.

cpu_tuple_cost (floating point)

設定計劃程序在查詢期間處理每個資料列的成本估算。預設值為 0.01。

cpu_index_tuple_cost (floating point)

設定計劃程序在索引掃描期間處理每個索引項目的成本估計。預設值為 0.005。

cpu_operator_cost (floating point)

設定計劃程序對查詢期間執行的每個運算子或函數的處理成本的估計。 預設值為 0.0025。

parallel_setup_cost (floating point)

設定計劃程序對啟動平行工作程序的成本估計。預設值為 1000。

parallel_tuple_cost (floating point)

設定計劃程序對從一個平行工作程序轉移到另一個程序的一個 tuple 的成本估算。預設值為 0.1。

min_parallel_table_scan_size (integer)

Sets the minimum amount of table data that must be scanned in order for a parallel scan to be considered. For a parallel sequential scan, the amount of table data scanned is always equal to the size of the table, but when indexes are used the amount of table data scanned will normally be less. The default is 8 megabytes (8MB).

min_parallel_index_scan_size (integer)

Sets the minimum amount of index data that must be scanned in order for a parallel scan to be considered. Note that a parallel index scan typically won't touch the entire index; it is the number of pages which the planner believes will actually be touched by the scan which is relevant. The default is 512 kilobytes (512kB).

effective_cache_size (integer)

Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. The default is 4 gigabytes (4GB).

19.7.3. Genetic Query Optimizer

The genetic query optimizer (GEQO) is an algorithm that does query planning using heuristic searching. This reduces planning time for complex queries (those joining many relations), at the cost of producing plans that are sometimes inferior to those found by the normal exhaustive-search algorithm. For more information see Chapter 59.

geqo (boolean)

Enables or disables genetic query optimization. This is on by default. It is usually best not to turn it off in production; the geqo_threshold variable provides more granular control of GEQO.

geqo_threshold (integer)

Use genetic query optimization to plan queries with at least this many FROM items involved. (Note that a FULL OUTER JOIN construct counts as only one FROM item.) The default is 12. For simpler queries it is usually best to use the regular, exhaustive-search planner, but for queries with many tables the exhaustive search takes too long, often longer than the penalty of executing a suboptimal plan. Thus, a threshold on the size of the query is a convenient way to manage use of GEQO.

geqo_effort (integer)

Controls the trade-off between planning time and query plan quality in GEQO. This variable must be an integer in the range from 1 to 10. The default value is five. Larger values increase the time spent doing query planning, but also increase the likelihood that an efficient query plan will be chosen.

geqo_effort doesn't actually do anything directly; it is only used to compute the default values for the other variables that influence GEQO behavior (described below). If you prefer, you can set the other parameters by hand instead.

geqo_pool_size (integer)

Controls the pool size used by GEQO, that is the number of individuals in the genetic population. It must be at least two, and useful values are typically 100 to 1000. If it is set to zero (the default setting) then a suitable value is chosen based on geqo_effort and the number of tables in the query.

geqo_generations (integer)

Controls the number of generations used by GEQO, that is the number of iterations of the algorithm. It must be at least one, and useful values are in the same range as the pool size. If it is set to zero (the default setting) then a suitable value is chosen based on geqo_pool_size.

geqo_selection_bias (floating point)

Controls the selection bias used by GEQO. The selection bias is the selective pressure within the population. Values can be from 1.50 to 2.00; the latter is the default.

geqo_seed (floating point)

Controls the initial value of the random number generator used by GEQO to select random paths through the join order search space. The value can range from zero (the default) to one. Varying the value changes the set of join paths explored, and may result in a better or worse best path being found.

19.7.4. Other Planner Options

default_statistics_target (integer)

Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time needed to do ANALYZE, but might improve the quality of the planner's estimates. The default is 100. For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.

constraint_exclusion (enum)

Controls the query planner's use of table constraints to optimize queries. The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with inheritance and partitioned tables to improve performance.

When this parameter allows it for a particular table, the planner compares query conditions with the table's CHECK constraints, and omits scanning tables for which the conditions contradict the constraints. For example:

CREATE TABLE parent(key integer, ...);
CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
...
SELECT * FROM parent WHERE key = 2400;

With constraint exclusion enabled, this SELECT will not scan child1000 at all, improving performance.

Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. If you have no partitioned tables you might prefer to turn it off entirely.

Refer to Section 5.10.4 for more information on using constraint exclusion and partitioning.

cursor_tuple_fraction (floating point)

Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. The default is 0.1. Smaller values of this setting bias the planner towards using “fast start” plans for cursors, which will retrieve the first few rows quickly while perhaps taking a long time to fetch all rows. Larger values put more emphasis on the total estimated time. At the maximum setting of 1.0, cursors are planned exactly like regular queries, considering only the total estimated time and not how soon the first rows might be delivered.

from_collapse_limit (integer)

The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but might yield inferior query plans. The default is eight. For more information see Section 14.3.

Setting this value to geqo_threshold or more may trigger use of the GEQO planner, resulting in non-optimal plans. See Section 19.7.3.

join_collapse_limit (integer)

The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result. Smaller values reduce planning time but might yield inferior query plans.

By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of explicit JOINs. Thus, the explicit join order specified in the query will be the actual order in which the relations are joined. Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly. For more information see Section 14.3.

Setting this value to geqo_threshold or more may trigger use of the GEQO planner, resulting in non-optimal plans. See Section 19.7.3.

force_parallel_mode (enum)

Allows the use of parallel queries for testing purposes even in cases where no performance benefit is expected. The allowed values of force_parallel_mode are off (use parallel mode only when it is expected to improve performance), on (force parallel query for all queries for which it is thought to be safe), and regress (like on, but with additional behavior changes as explained below).

More specifically, setting this value to on will add a Gather node to the top of any query plan for which this appears to be safe, so that the query runs inside of a parallel worker. Even when a parallel worker is not available or cannot be used, operations such as starting a subtransaction that would be prohibited in a parallel query context will be prohibited unless the planner believes that this will cause the query to fail. If failures or unexpected results occur when this option is set, some functions used by the query may need to be marked PARALLEL UNSAFE (or, possibly, PARALLEL RESTRICTED).

Setting this value to regress has all of the same effects as setting it to on plus some additional effects that are intended to facilitate automated regression testing. Normally, messages from a parallel worker include a context line indicating that, but a setting of regresssuppresses this line so that the output is the same as in non-parallel execution. Also, the Gather nodes added to plans by this setting are hidden in EXPLAIN output so that the output matches what would be obtained if this setting were turned off.

The following “parameters” are read-only, and are determined when PostgreSQL is compiled or when it is installed. As such, they have been excluded from the sample postgresql.conf file. These options report various aspects of PostgreSQL behavior that might be of interest to certain applications, particularly administrative front-ends.

block_size (integer)

Reports the size of a disk block. It is determined by the value of BLCKSZ when building the server. The default value is 8192 bytes. The meaning of some configuration variables (such as shared_buffers) is influenced by block_size. See Section 19.4 for information.

data_checksums (boolean)

Reports whether data checksums are enabled for this cluster. See data checksums for more information.

debug_assertions (boolean)

Reports whether PostgreSQL has been built with assertions enabled. That is the case if the macro USE_ASSERT_CHECKING is defined when PostgreSQL is built (accomplished e.g. by the configure option --enable-cassert). By default PostgreSQL is built without assertions.

integer_datetimes (boolean)

Reports whether PostgreSQL was built with support for 64-bit-integer dates and times. As of PostgreSQL 10, this is always on.

lc_collate (string)

Reports the locale in which sorting of textual data is done. See Section 23.1 for more information. This value is determined when a database is created.

lc_ctype (string)

Reports the locale that determines character classifications. See Section 23.1 for more information. This value is determined when a database is created. Ordinarily this will be the same as lc_collate, but for special applications it might be set differently.

max_function_args (integer)

Reports the maximum number of function arguments. It is determined by the value of FUNC_MAX_ARGS when building the server. The default value is 100 arguments.

max_identifier_length (integer)

Reports the maximum identifier length. It is determined as one less than the value of NAMEDATALEN when building the server. The default value of NAMEDATALEN is 64; therefore the default max_identifier_length is 63 bytes, which can be less than 63 characters when using multibyte encodings.

max_index_keys (integer)

Reports the maximum number of index keys. It is determined by the value of INDEX_MAX_KEYS when building the server. The default value is 32 keys.

segment_size (integer)

Reports the number of blocks (pages) that can be stored within a file segment. It is determined by the value of RELSEG_SIZE when building the server. The maximum size of a segment file in bytes is equal to segment_size multiplied by block_size; by default this is 1GB.

server_encoding (string)

Reports the database encoding (character set). It is determined when the database is created. Ordinarily, clients need only be concerned with the value of client_encoding.

server_version (string)

Reports the version number of the server. It is determined by the value of PG_VERSION when building the server.

server_version_num (integer)

Reports the version number of the server as an integer. It is determined by the value of PG_VERSION_NUM when building the server.

wal_block_size (integer)

Reports the size of a WAL disk block. It is determined by the value of XLOG_BLCKSZ when building the server. The default value is 8192 bytes.

wal_segment_size (integer)

Reports the number of blocks (pages) in a WAL segment file. The total size of a WAL segment file in bytes is equal to wal_segment_size multiplied by wal_block_size; by default this is 16MB. See Section 30.4 for more information.

當用戶端應用程序連線到資料庫伺服器時，它將指定要連線的 PostgreSQL 資料庫使用者名稱，這與以特定使用者身份登入到 Unix 伺服器的方式大致相同。在 SQL 環境中，有效的資料庫使用者名確定資料庫物件的存取權限 - 有關詳細訊息，請參閱第 21 章。因此，限制哪些資料庫使用者可以進行連線是非常重要的。

注意

正如第 21 章所描述的，PostgreSQL 實際上是以「角色」的角度來管理權限的。在本章中，我們一直使用資料庫使用者來表示「具有 LOGIN 權限的角色」。

身份驗證是資料庫伺服器建立用戶端身份的過程，延伸確認用戶端應用程序（或執行用戶端應用程序的使用者）是否被允許以請求的資料庫使用者名稱進行連線。

PostgreSQL 提供了許多不同的用戶端身份驗證方法。用於驗證特定用戶端連線的方法可以根據（用戶端）主機位址、資料庫名稱和使用者名稱進行驗證。

PostgreSQL 資料庫使用者名稱在邏輯上與運行服務器的作業系統的使用者名稱是分開的。如果特定伺服器的所有用戶在伺服器的機器上也有帳戶，那麼分配與其作業系統用戶名搭配的資料庫用戶名是有意義的。但是，接受遠端連線的伺服器可能有許多沒有本地作業系統帳戶的資料庫用戶，在這種情況下，資料庫用戶名和作業系統用戶名之間不需要有所關連。

When using an external authentication system such as Ident or GSSAPI, the name of the operating system user that initiated the connection might not be the same as the database user (role) that is to be used. In this case, a user name map can be applied to map the operating system user name to a database user. To use user name mapping, specify map=map-name in the options field in pg_hba.conf. This option is supported for all authentication methods that receive external user names. Since different mappings might be needed for different connections, the name of the map to be used is specified in the map-name parameter in pg_hba.conf to indicate which map to use for each individual connection.

User name maps are defined in the ident map file, which by default is named pg_ident.conf and is stored in the cluster's data directory. (It is possible to place the map file elsewhere, however; see the ident_file configuration parameter.) The ident map file contains lines of the general form:

map-name system-username database-username

Comments and whitespace are handled in the same way as in pg_hba.conf. The map-name is an arbitrary name that will be used to refer to this mapping in pg_hba.conf. The other two fields specify an operating system user name and a matching database user name. The same map-name can be used repeatedly to specify multiple user-mappings within a single map.

There is no restriction regarding how many database users a given operating system user can correspond to, nor vice versa. Thus, entries in a map should be thought of as meaning “this operating system user is allowed to connect as this database user”, rather than implying that they are equivalent. The connection will be allowed if there is any map entry that pairs the user name obtained from the external authentication system with the database user name that the user has requested to connect as.

If the system-username field starts with a slash (/), the remainder of the field is treated as a regular expression. (See Section 9.7.3.1 for details of PostgreSQL's regular expression syntax.) The regular expression can include a single capture, or parenthesized subexpression, which can then be referenced in the database-username field as \1 (backslash-one). This allows the mapping of multiple user names in a single line, which is particularly useful for simple syntax substitutions. For example, these entries

mymap   /^(.*)@mydomain\.com$      \1
mymap   /^(.*)@otherdomain\.com$   guest

will remove the domain part for users with system user names that end with @mydomain.com, and allow any user whose system name ends with @otherdomain.com to log in as guest.

Tip

Keep in mind that by default, a regular expression can match just part of a string. It's usually wise to use ^ and $, as shown in the above example, to force the match to be to the entire system user name.

The pg_ident.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file.

A pg_ident.conf file that could be used in conjunction with the pg_hba.conf file in Example 20.1 is shown in Example 20.2. In this example, anyone logged in to a machine on the 192.168 network that does not have the operating system user name bryanh, ann, or robert would not be granted access. Unix user robert would only be allowed access when he tries to connect as PostgreSQL user bob, not as robert or anyone else. ann would only be allowed to connect as ann. User bryanh would be allowed to connect as either bryanh or as guest1.

Example 20.2. An Example pg_ident.conf File

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

omicron         bryanh                  bryanh
omicron         ann                     ann
# bob has user name robert on these machines
omicron         robert                  bob
# bryanh can also connect as guest1
omicron         bryanh                  guest1

用戶端身份驗證由組態檔案控制，組態檔案通常名稱為 pg_hba.conf，並儲存在資料庫叢集的資料目錄中。 （HBA 代表 host-based authentication。）當 initdb 初始化資料目錄時，將安裝預設的 pg_hba.conf 檔案。但是，可以將身份驗證組態檔案放在其他路徑；請參閱 hba_file 組態參數。

pg_hba.conf 檔案的一般格式是一組記錄，每行一個。空白行將被忽略，# comment 字元後面的任何文字都將被忽略。記錄不能跨行。記錄由許多段落組成，這些段落由空格或 tab 分隔。如果段落的值用了雙引號，則段落可以包含空格。在資料庫，使用者或位址段落（例如，all 或 replication）中括起其中一個關鍵字會使該字失去其特殊含義，並且只是將資料庫，使用者或主機與該名稱相匹配。

每條記錄指定連線類型，用戶端 IP 位址範圍（如果與連線類型相關）、資料庫名稱、使用者名稱以及符合這些參數的連線身份驗證方法。具有符合的連線類型、用戶端位址、要求的資料庫和使用者名稱的第一個記錄用於執行身份驗證。沒有“fall-through”或“replication”：如果選擇了一條記錄而認證失敗，就不再考慮後續記錄。如果沒有記錄匹配，則拒絕存取。

記錄可以是下面的七種格式之一

local      database  user  auth-method  [auth-options]
host       database  user  address  auth-method  [auth-options]
hostssl    database  user  address  auth-method  [auth-options]
hostnossl  database  user  address  auth-method  [auth-options]
host       database  user  IP-address  IP-mask  auth-method  [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-options]

段落的含義如下：

local

此記錄搭配使用 Unix-domain socket 的連線嘗試。如果沒有此類型的記錄，則不允許使用 Unix-domain socket 連線。

host

此記錄用於使用 TCP/IP 進行的連線嘗試。主機記錄使用 SSL 或非 SSL 連線嘗試.

重要 除非使用 listen_addresses 組態參數的適當值啟動伺服器，否則將無法進行遠端 TCP/IP 連線，因為預設行為是僅在 localhost 上監聽 TCP/IP 連線。

hostssl

此記錄會套用於使用 TCP/IP 進行的連線嘗試，但僅限於使用 SSL 加密進行連線時。

要使用此選項，必須以 SSL 建置伺服器，也必須透過設定 ssl 組態參數來啟用 SSL（有關更多訊息，請參閱第 18.9 節）。否則，將會忽略 hostssl 記錄，除非是為了記錄不能與任何連線相符合的警告。

hostnossl

此記錄類型與 hostssl 具有相反的行為；它僅套用於透過 TCP/IP 且不使用 SSL 的連線嘗試。

database

指定此記錄所要求搭配的資料庫名稱。值 all 使其搭配所有資料庫。如果請求的資料庫與請求的使用者具有相同的名稱，則可以用 sameuser 值來指定。值 samerole 指定所請求的使用者必須是與請求的資料庫同名的角色成員。 （ samegroup 是一個過時但仍然被接受的 samerole 別名。）超級使用者不被認為是同一角色的成員，除非他們直接或間接地明確地成為角色的成員，而不僅僅是作為超級使用者。值 replication 指定在請求 physical replication 連線時的記錄搭配（請注意，複寫連線不指定任何特定資料庫）。否則，這是特定 PostgreSQL 資料庫的名稱。可以透過用逗號分隔它們來設定多個資料庫名稱，也可以透過在檔案名稱前加上 @ 來指定包含資料庫名稱的額外檔案。

user

Specifies which database user name(s) this record matches. The value all specifies that it matches all users. Otherwise, this is either the name of a specific database user, or a group name preceded by +. (Recall that there is no real distinction between users and groups in PostgreSQL; a + mark really means “match any of the roles that are directly or indirectly members of this role”, while a name without a + mark matches only that specific role.) For this purpose, a superuser is only considered to be a member of a role if they are explicitly a member of the role, directly or indirectly, and not just by virtue of being a superuser. Multiple user names can be supplied by separating them with commas. A separate file containing user names can be specified by preceding the file name with @.

address

Specifies the client machine address(es) that this record matches. This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length. The mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right of this should be zero in the given IP address. There must not be any white space between the IP address, the /, and the CIDR mask length.

Typical examples of an IPv4 address range specified this way are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. An IPv6 address range might look like ::1/128 for a single host (in this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96 for a small network. 0.0.0.0/0 represents all IPv4 addresses, and ::0/0 represents all IPv6 addresses. To specify a single host, use a mask length of 32 for IPv4 or 128 for IPv6. In a network address, do not omit trailing zeroes.

An entry given in IPv4 format will match only IPv4 connections, and an entry given in IPv6 format will match only IPv6 connections, even if the represented address is in the IPv4-in-IPv6 range. Note that entries in IPv6 format will be rejected if the system's C library does not have support for IPv6 addresses.

You can also write all to match any IP address, samehost to match any of the server's own IP addresses, or samenet to match any address in any subnet that the server is directly connected to.

If a host name is specified (anything that is not an IP address range or a special key word is treated as a host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match. (The host name that is used in pg_hba.conf should be the one that address-to-name resolution of the client's IP address returns, otherwise the line won't be matched. Some host name databases allow associating an IP address with multiple host names, but the operating system will only return one host name when asked to resolve an IP address.)

A host name specification that starts with a dot (.) matches a suffix of the actual host name. So .example.com would match foo.example.com (but not just example.com).

When host names are specified in pg_hba.conf, you should make sure that name resolution is reasonably fast. It can be of advantage to set up a local name resolution cache such as nscd. Also, you may wish to enable the configuration parameter log_hostname to see the client's host name instead of the IP address in the log.

This field only applies to host, hostssl, and hostnossl records.

Note

Users sometimes wonder why host names are handled in this seemingly complicated way, with two name resolutions including a reverse lookup of the client's IP address. This complicates use of the feature in case the client's reverse DNS entry is not set up or yields some undesirable host name. It is done primarily for efficiency: this way, a connection attempt requires at most two resolver lookups, one reverse and one forward. If there is a resolver problem with some address, it becomes only that client's problem. A hypothetical alternative implementation that only did forward lookups would have to resolve every host name mentioned in pg_hba.conf during every connection attempt. That could be quite slow if many names are listed. And if there is a resolver problem with one of the host names, it becomes everyone's problem.

Also, a reverse lookup is necessary to implement the suffix matching feature, because the actual client host name needs to be known in order to match it against the pattern.

Note that this behavior is consistent with other popular implementations of host name-based access control, such as the Apache HTTP Server and TCP Wrappers.

IP-address IP-mask

These two fields can be used as an alternative to the IP-address/mask-length notation. Instead of specifying the mask length, the actual mask is specified in a separate column. For example, 255.0.0.0 represents an IPv4 CIDR mask length of 8, and 255.255.255.255represents a CIDR mask length of 32.

These fields only apply to host, hostssl, and hostnossl records.

auth-method

Specifies the authentication method to use when a connection matches this record. The possible choices are summarized here; details are in Section 20.3.

trust

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 20.3.1 for details.

reject

Reject the connection unconditionally. This is useful for “filtering out” certain hosts from a group, for example a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect.

scram-sha-256

Perform SCRAM-SHA-256 authentication to verify the user's password. See Section 20.3.2 for details.

md5

Perform SCRAM-SHA-256 or MD5 authentication to verify the user's password. See Section 20.3.2 for details.

password

Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks. See Section 20.3.2 for details.

gss

Use GSSAPI to authenticate the user. This is only available for TCP/IP connections. See Section 20.3.3 for details.

sspi

Use SSPI to authenticate the user. This is only available on Windows. See Section 20.3.4 for details.

ident

Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead. See Section 20.3.5 for details.

peer

Obtain the client's operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections. See Section 20.3.6 for details.

ldap

Authenticate using an LDAP server. See Section 20.3.7 for details.

radius

Authenticate using a RADIUS server. See Section 20.3.8 for details.

cert

Authenticate using SSL client certificates. See Section 20.3.9 for details.

pam

Authenticate using the Pluggable Authentication Modules (PAM) service provided by the operating system. See Section 20.3.10 for details.

bsd

Authenticate using the BSD Authentication service provided by the operating system. See Section 20.3.11 for details.

auth-options

After the auth-method field, there can be field(s) of the form name=value that specify options for the authentication method. Details about which options are available for which authentication methods appear below.

In addition to the method-specific options listed below, there is one method-independent authentication option clientcert, which can be specified in any hostssl record. When set to 1, this option requires the client to present a valid (trusted) SSL certificate, in addition to the other requirements of the authentication method.

Files included by @ constructs are read as lists of names, which can be separated by either whitespace or commas. Comments are introduced by #, just as in pg_hba.conf, and nested @ constructs are allowed. Unless the file name following @ is an absolute path, it is taken to be relative to the directory containing the referencing file.

Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods. For example, one might wish to use trust authentication for local TCP/IP connections but require a password for remote TCP/IP connections. In this case a record specifying trust authentication for connections from 127.0.0.1 would appear before a record specifying password authentication for a wider range of allowed client IP addresses.

The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file.

Note

The preceding statement is not true on Microsoft Windows: there, any changes in the pg_hba.conf file are immediately applied by subsequent new connections.

The system view pg_hba_file_rules can be helpful for pre-testing changes to the pg_hba.conf file, or for diagnosing problems if loading of the file did not have the desired effects. Rows in the view with non-null error fields indicate problems in the corresponding lines of the file.

Tip

To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it's usually easier to control this by granting/revoking CONNECTprivilege than to put the rules in pg_hba.conf entries.

Some examples of pg_hba.conf entries are shown in Example 20.1. See the next section for details on the different authentication methods.

Example 20.1. Example pg_hba.conf Entries

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

# The same as the previous line, but using a separate netmask column
#
# TYPE  DATABASE        USER            IP-ADDRESS      IP-MASK             METHOD
host    all             all             127.0.0.1       255.255.255.255     trust

# The same over IPv6.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             ::1/128                 trust

# The same using a host name (would typically cover both IPv4 and IPv6).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             localhost               trust

# Allow any user from any host with IP address 192.168.93.x to connect
# to database "postgres" as the same user name that ident reports for
# the connection (typically the operating system user name).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.93.0/24         ident

# Allow any user from host 192.168.12.10 to connect to database
# "postgres" if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        scram-sha-256

# Allow any user from hosts in the example.com domain to connect to
# any database if the user's password is correctly supplied.
#
# Require SCRAM authentication for most users, but make an exception
# for user 'mike', who uses an older client that doesn't support SCRAM
# authentication.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             mike            .example.com            md5
host    all             all             .example.com            scram-sha-256

# In the absence of preceding "host" lines, these two lines will
# reject all connections from 192.168.54.1 (since that entry will be
# matched first), but allow GSSAPI connections from anywhere else
# on the Internet.  The zero mask causes no bits of the host IP
# address to be considered, so it matches any host.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.54.1/32         reject
host    all             all             0.0.0.0/0               gss

# Allow users from 192.168.x.x hosts to connect to any database, if
# they pass the ident check.  If, for example, ident says the user is
# "bryanh" and he requests to connect as PostgreSQL user "guest1", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "omicron" that says "bryanh" is allowed to connect as "guest1".
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.0.0/16          ident map=omicron

# If these are the only three lines for local connections, they will
# allow local users to connect only to their own databases (databases
# with the same name as their database user name) except for administrators
# and members of role "support", who can connect to all databases.  The file
# $PGDATA/admins contains a list of names of administrators.  Passwords
# are required in all cases.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
local   all             @admins                                 md5
local   all             +support                                md5

# The last two lines above can be combined into a single line:
local   all             @admins,+support                        md5

# The database column can also use lists and file names:
local   db1,db2,@demodbs  all                                   md5

Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database). To create a role use the CREATE ROLE SQL command:

CREATE ROLE name;

name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. (In practice, you will usually want to add additional options, such as LOGIN, to the command. More details appear below.) To remove an existing role, use the analogous DROP ROLE command:

DROP ROLE name;

For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:

createuser name
dropuser name

To determine the set of existing roles, examine the pg_roles system catalog, for example

SELECT rolname FROM pg_roles;

The psql program's \du meta-command is also useful for listing the existing roles.

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.

Every connection to the database server is made using the name of some particular role, and this role determines the initial access privileges for commands issued in that connection. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. For example, the psql program uses the -U command line option to indicate the role to connect as. Many applications assume the name of the current operating system user by default (including createuserand psql). Therefore it is often convenient to maintain a naming correspondence between roles and operating system users.

The set of database roles a given client connection can connect as is determined by the client authentication setup, as explained in Chapter 20. (Thus, a client is not limited to connect as the role matching its operating system user, just as a person's login name need not match his or her real name.) Since the role identity determines the set of privileges available to a connected client, it is important to carefully configure privileges when setting up a multiuser environment.

因為角色可以擁有資料庫物件，並且可以擁有存取其他物件的權限，所以移除角色通常不僅僅是快速 DROP USER 的問題。該角色擁有的任何物件必須先被移除或重新分配給其他使用者；而授予角色的任何權限也都必須被撤銷。

物件的所有權可以使用 ALTER 指令一次轉換，例如：

ALTER TABLE bobs_table OWNER TO alice;

或者，可以使用 REASSIGN OWNED 指令將要移除角色擁有的所有物件的所有權重新分配給另一個角色。由於 REASSIGN OWNED 無法存取其他資料庫中的物件，因此有必要在包含該角色擁有的物件的每個資料庫中執行它。（請注意，第一個這樣的 REASSIGN OWNED 將改變任何共享的資料庫間物件，即資料庫或資料表空間的所有權，這些資料庫或資料表空間由將被移除的角色所擁有）。

一旦任何有價值的物件已經轉移到新的所有者中，則可以使用 DROP OWNED 指令移弓除待移除角色擁有的任何剩餘物件。同樣，此指令無法存取其他資料庫中的物件，因此有必要在包含該角色擁有的物件的每個資料庫中執行它。此外，DROP OWNED 不會刪除整個資料庫或資料表空間，因此如果角色擁有尚未轉移到新所有者的任何資料庫或資料表空間，則必須手動執行此操作。

DROP OWNED 還負責為不屬於它的物件移除授予目標角色的所有權限。由於 REASSIGN OWNED 不會觸及這些物件，因此通常需要運行 REASSIGN OWNED 和 DROP OWNED（按此順序！）以完全移除要移除的角色的相依關係。

簡而言之，移除已用於擁有物件的角色的最一般的方式是：

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;

當並非所有擁有的物件都將被轉移到相同的繼任者使用者時，最好手動處理異常，然後執行上述步驟來清除。

如果在相依物件仍然存在的情況下嘗試 DROP ROLE，則會發出哪些物件需要重新分配或移除的訊息。

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

To set up a group role, first create the role:

CREATE ROLE name;

Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish.

Once the group role exists, you can add and remove members using the GRANT and REVOKE commands:

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

You can grant membership to other group roles, too (since there isn't really any distinction between group roles and non-group roles). The database will not let you set up circular membership loops. Also, it is not permitted to grant membership in a role to PUBLIC.

The members of a group role can use the privileges of the role in two ways. First, every member of a group can explicitly do SET ROLE to temporarily “become” the group role. In this state, the database session has access to the privileges of the group role rather than the original login role, and any database objects created are considered owned by the group role not the login role. Second, member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles. As an example, suppose we have done:

CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;

Immediately after connecting as role joe, a database session will have use of privileges granted directly to joe plus any privileges granted to admin, because joe “inherits” admin's privileges. However, privileges granted to wheel are not available, because even though joe is indirectly a member of wheel, the membership is via admin which has the NOINHERIT attribute. After:

SET ROLE admin;

the session would have use of only those privileges granted to admin, and not those granted to joe. After:

SET ROLE wheel;

the session would have use of only those privileges granted to wheel, and not those granted to either joe or admin. The original privilege state can be restored with any of:

SET ROLE joe;
SET ROLE NONE;
RESET ROLE;

Note

The SET ROLE command always allows selecting any role that the original login role is directly or indirectly a member of. Thus, in the above example, it is not necessary to become adminbefore becoming wheel.

Note

In the SQL standard, there is a clear distinction between users and roles, and users do not automatically inherit privileges while roles do. This behavior can be obtained in PostgreSQLby giving roles being used as SQL roles the INHERIT attribute, while giving roles being used as SQL users the NOINHERIT attribute. However, PostgreSQL defaults to giving all roles theINHERIT attribute, for backward compatibility with pre-8.1 releases in which users always had use of permissions granted to groups they were members of.

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute. Continuing the above example, we might choose to grant CREATEDB and CREATEROLE to the admin role. Then a session connecting as role joe would not have these privileges immediately, only after doing SET ROLE admin.

To destroy a group role, use DROP ROLE:

DROP ROLE name;

Any memberships in the group role are automatically revoked (but the member roles are not otherwise affected).

A database role can have a number of attributes that define its privileges and interact with the client authentication system.login privilege

Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can be considered the same as a “database user”. To create a role with login privilege, use either:

CREATE ROLE name LOGIN;
CREATE USER name;

(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not.)superuser status

A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser. To create a new database superuser, use CREATE ROLEname SUPERUSER. You must do this as a role that is already a superuser.database creation

A role must be explicitly given permission to create databases (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE name CREATEDB.role creation

A role must be explicitly given permission to create more roles (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE name CREATEROLE. A role with CREATEROLE privilege can alter and drop other roles, too, as well as grant or revoke membership in them. However, to create, alter, drop, or change membership of a superuser role, superuser status is required; CREATEROLE is insufficient for that.initiating replication

A role must explicitly be given permission to initiate streaming replication (except for superusers, since those bypass all permission checks). A role used for streaming replication must have LOGIN permission as well. To create such a role, use CREATE ROLE nameREPLICATION LOGIN.password

A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The password and md5 authentication methods make use of passwords. Database passwords are separate from operating system passwords. Specify a password upon role creation with CREATE ROLE name PASSWORD 'string'.

A role's attributes can be modified after creation with ALTER ROLE. See the reference pages for the CREATE ROLE and ALTER ROLE commands for details.

Tip

It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it.

A role can also have role-specific defaults for many of the run-time configuration settings described in Chapter 19. For example, if for some reason you want to disable index scans (hint: not a good idea) anytime you connect, you can use:

ALTER ROLE myname SET enable_indexscan TO off;

This will save the setting (but not set it immediately). In subsequent connections by this role it will appear as though SET enable_indexscan TO off had been executed just before the session started. You can still alter this setting during the session; it will only be the default. To remove a role-specific default setting, use ALTER ROLE rolename RESET varname. Note that role-specific defaults attached to roles without LOGIN privilege are fairly useless, since they will never be invoked.

Functions, triggers and row-level security policies allow users to insert code into the backend server that other users might execute unintentionally. Hence, these mechanisms permit users to “Trojan horse” others with relative ease. The strongest protection is tight control over who can define objects. Where that is infeasible, write queries referring only to objects having trusted owners. Remove from search_path the public schema and any other schemas that permit untrusted users to create objects.

Functions run inside the backend server process with the operating system permissions of the database server daemon. If the programming language used for the function allows unchecked memory accesses, it is possible to change the server's internal data structures. Hence, among many other things, such functions can circumvent any system access controls. Function languages that allow such access are considered “untrusted”, and PostgreSQL allows only superusers to create functions written in those languages.

The following subsections describe the authentication methods in more detail.

20.3.1. Trust Authentication

When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server.

trust authentication is appropriate and very convenient for local connections on a single-user workstation. It is usually not appropriate by itself on a multiuser machine. However, you might be able to use trust even on a multiuser machine, if you restrict access to the server's Unix-domain socket file using file-system permissions. To do this, set the unix_socket_permissions (and possibly unix_socket_group) configuration parameters as described in Section 19.3. Or you could set the unix_socket_directories configuration parameter to place the socket file in a suitably restricted directory.

Setting file-system permissions only helps for Unix-socket connections. Local TCP/IP connections are not restricted by file-system permissions. Therefore, if you want to use file-system permissions for local security, remove the host ... 127.0.0.1 ... line from pg_hba.conf, or change it to a non-trust authentication method.

trust authentication is only suitable for TCP/IP connections if you trust every user on every machine that is allowed to connect to the server by the pg_hba.conf lines that specify trust. It is seldom reasonable to use trust for any TCP/IP connections other than those from localhost (127.0.0.1).

20.3.2. Password Authentication

There are several password-based authentication methods. These methods operate similarly but differ in how the users' passwords are stored on the server and how the password provided by a client is sent across the connection.scram-sha-256

The method scram-sha-256 performs SCRAM-SHA-256 authentication, as described in RFC 7677. It is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is thought to be secure.

This is the most secure of the currently provided methods, but it is not supported by older client libraries.md5

The method md5 uses a custom less secure challenge-response mechanism. It prevents password sniffing and avoids storing passwords on the server in plain text but provides no protection if an attacker manages to steal the password hash from the server. Also, the MD5 hash algorithm is nowadays no longer considered secure against determined attacks.

The md5 method cannot be used with the db_user_namespace feature.

To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.password

The method password sends the password in clear-text and is therefore vulnerable to password “sniffing” attacks. It should always be avoided if possible. If the connection is protected by SSL encryption then password can be used safely, though. (Though SSL certificate authentication might be a better choice if one is depending on using SSL).

PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_authid system catalog. Passwords can be managed with the SQL commands CREATE USER and ALTER ROLE, e.g., CREATE USER foo WITH PASSWORD 'secret', or the psql command \password. If no password has been set up for a user, the stored password is null and password authentication will always fail for that user.

The availability of the different password-based authentication methods depends on how a user's password on the server is encrypted (or hashed, more accurately). This is controlled by the configuration parameter password_encryption at the time the password is set. If a password was encrypted using the scram-sha-256 setting, then it can be used for the authentication methods scram-sha-256 and password (but password transmission will be in plain text in the latter case). The authentication method specification md5 will automatically switch to using the scram-sha-256 method in this case, as explained above, so it will also work. If a password was encrypted using the md5 setting, then it can be used only for the md5and password authentication method specifications (again, with the password transmitted in plain text in the latter case). (Previous PostgreSQL releases supported storing the password on the server in plain text. This is no longer possible.) To check the currently stored password hashes, see the system catalog pg_authid.

To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256.

20.3.3. GSSAPI Authentication

GSSAPI is an industry-standard protocol for secure authentication defined in RFC 2743. PostgreSQL supports GSSAPI with Kerberos authentication according to RFC 1964. GSSAPIprovides automatic authentication (single sign-on) for systems that support it. The authentication itself is secure, but the data sent over the database connection will be sent unencrypted unless SSL is used.

GSSAPI support has to be enabled when PostgreSQL is built; see Chapter 16 for more information.

When GSSAPI uses Kerberos, it uses a standard principal in the format servicename/hostname@realm. The PostgreSQL server will accept any principal that is included in the keytab used by the server, but care needs to be taken to specify the correct principal details when making the connection from the client using the krbsrvname connection parameter. (See also Section 33.1.2.) The installation default can be changed from the default postgres at build time using ./configure --with-krb-srvnam=whatever. In most environments, this parameter never needs to be changed. Some Kerberos implementations might require a different service name, such as Microsoft Active Directory which requires the service name to be in upper case (POSTGRES).

hostname is the fully qualified host name of the server machine. The service principal's realm is the preferred realm of the server machine.

Client principals can be mapped to different PostgreSQL database user names with pg_ident.conf. For example, pgusername@realm could be mapped to just pgusername. Alternatively, you can use the full username@realm principal as the role name in PostgreSQL without any mapping.

PostgreSQL also supports a parameter to strip the realm from the principal. This method is supported for backwards compatibility and is strongly discouraged as it is then impossible to distinguish different users with the same user name but coming from different realms. To enable this, set include_realm to 0. For simple single-realm installations, doing that combined with setting the krb_realm parameter (which checks that the principal's realm matches exactly what is in the krb_realm parameter) is still secure; but this is a less capable approach compared to specifying an explicit mapping in pg_ident.conf.

Make sure that your server keytab file is readable (and preferably only readable, not writable) by the PostgreSQL server account. (See also Section 18.1.) The location of the key file is specified by the krb_server_keyfile configuration parameter. The default is /usr/local/pgsql/etc/krb5.keytab (or whatever directory was specified as sysconfdir at build time). For security reasons, it is recommended to use a separate keytab just for the PostgreSQL server rather than opening up permissions on the system keytab file.

The keytab file is generated by the Kerberos software; see the Kerberos documentation for details. The following example is for MIT-compatible Kerberos 5 implementations:

kadmin% ank -randkey postgres/server.my.domain.org
kadmin% ktadd -k krb5.keytab postgres/server.my.domain.org

When connecting to the database make sure you have a ticket for a principal matching the requested database user name. For example, for database user name fred, principalfred@EXAMPLE.COM would be able to connect. To also allow principal fred/users.example.com@EXAMPLE.COM, use a user name map, as described in Section 20.2.

The following configuration options are supported for GSSAPI:include_realm

If set to 0, the realm name from the authenticated user principal is stripped off before being passed through the user name mapping (Section 20.2). This is discouraged and is primarily available for backwards compatibility, as it is not secure in multi-realm environments unless krb_realm is also used. It is recommended to leave include_realm set to the default (1) and to provide an explicit mapping in pg_ident.conf to convert principal names to PostgreSQL user names.map

Allows for mapping between system and database user names. See Section 20.2 for details. For a GSSAPI/Kerberos principal, such as username@EXAMPLE.COM (or, less commonly,username/hostbased@EXAMPLE.COM), the user name used for mapping is username@EXAMPLE.COM (or username/hostbased@EXAMPLE.COM, respectively), unless include_realm has been set to 0, in which case username (or username/hostbased) is what is seen as the system user name when mapping.krb_realm

Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever user name mapping is done.

20.3.4. SSPI Authentication

SSPI is a Windows technology for secure authentication with single sign-on. PostgreSQL will use SSPI in negotiate mode, which will use Kerberos when possible and automatically fall back to NTLM in other cases. SSPI authentication only works when both server and client are running Windows, or, on non-Windows platforms, when GSSAPI is available.

When using Kerberos authentication, SSPI works the same way GSSAPI does; see Section 20.3.3 for details.

The following configuration options are supported for SSPI:include_realm

If set to 0, the realm name from the authenticated user principal is stripped off before being passed through the user name mapping (Section 20.2). This is discouraged and is primarily available for backwards compatibility, as it is not secure in multi-realm environments unless krb_realm is also used. It is recommended to leave include_realm set to the default (1) and to provide an explicit mapping in pg_ident.conf to convert principal names to PostgreSQL user names.compat_realm

If set to 1, the domain's SAM-compatible name (also known as the NetBIOS name) is used for the include_realm option. This is the default. If set to 0, the true realm name from the Kerberos user principal name is used.

Do not disable this option unless your server runs under a domain account (this includes virtual service accounts on a domain member system) and all clients authenticating through SSPI are also using domain accounts, or authentication will fail.upn_username

If this option is enabled along with compat_realm, the user name from the Kerberos UPN is used for authentication. If it is disabled (the default), the SAM-compatible user name is used. By default, these two names are identical for new user accounts.

Note that libpq uses the SAM-compatible name if no explicit user name is specified. If you use libpq or a driver based on it, you should leave this option disabled or explicitly specify user name in the connection string.map

Allows for mapping between system and database user names. See Section 20.2 for details. For a SSPI/Kerberos principal, such as username@EXAMPLE.COM (or, less commonly,username/hostbased@EXAMPLE.COM), the user name used for mapping is username@EXAMPLE.COM (or username/hostbased@EXAMPLE.COM, respectively), unless include_realm has been set to 0, in which case username (or username/hostbased) is what is seen as the system user name when mapping.krb_realm

Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever user name mapping is done.

20.3.5. Ident Authentication

The ident authentication method works by obtaining the client's operating system user name from an ident server and using it as the allowed database user name (with an optional user name mapping). This is only supported on TCP/IP connections.

Note

When ident is specified for a local (non-TCP/IP) connection, peer authentication (see Section 20.3.6) will be used instead.

The following configuration options are supported for ident:map

Allows for mapping between system and database user names. See Section 20.2 for details.

The “Identification Protocol” is described in RFC 1413. Virtually every Unix-like operating system ships with an ident server that listens on TCP port 113 by default. The basic functionality of an ident server is to answer questions like “What user initiated the connection that goes out of your port X and connects to my port Y?”. Since PostgreSQL knows both Xand Y when a physical connection is established, it can interrogate the ident server on the host of the connecting client and can theoretically determine the operating system user for any given connection.

The drawback of this procedure is that it depends on the integrity of the client: if the client machine is untrusted or compromised, an attacker could run just about any program on port 113 and return any user name they choose. This authentication method is therefore only appropriate for closed networks where each client machine is under tight control and where the database and system administrators operate in close contact. In other words, you must trust the machine running the ident server. Heed the warning:

Some ident servers have a nonstandard option that causes the returned user name to be encrypted, using a key that only the originating machine's administrator knows. This option must not be used when using the ident server with PostgreSQL, since PostgreSQL does not have any way to decrypt the returned string to determine the actual user name.

20.3.6. Peer Authentication

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

The following configuration options are supported for peer:map

Allows for mapping between system and database user names. See Section 20.2 for details.

Peer authentication is only available on operating systems providing the getpeereid() function, the SO_PEERCRED socket parameter, or similar mechanisms. Currently that includes Linux, most flavors of BSD including macOS, and Solaris.

20.3.7. LDAP Authentication