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

1. 組態設定

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

./configure

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

mkdir build_dir
cd build_dir
/path/to/source/tree/configure [options go here]
make

預設配置將編譯伺服器和工具程式，以及僅需要 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

Build with support for LLVM based JIT compilation (see Chapter 32). This requires the LLVM library to be installed. The minimum required version of LLVM is currently 3.9.

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

./configure ... --with-llvm LLVM_CONFIG='/path/to/llvm/bin/llvm-config'

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:

./configure ... --with-icu ICU_CFLAGS='-I/some/where/include' ICU_LIBS='-L/some/where/lib -licui18n -licuuc -licudata'

(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

Build with LDAP support for authentication and connection parameter lookup (see Section 34.17 and Section 20.10 for more information). On Unix, this requires the OpenLDAP package to be installed. On Windows, the default WinLDAP library is used. configure will check for the required header files and libraries to make sure that your OpenLDAP installation is sufficient before proceeding.

--with-systemd

Build with support for systemd service notifications. This improves integration if the server binary is started under systemd but has no impact otherwise; see Section 18.3 for more information. libsystemd and the associated header files need to be installed to be able to use this option.

--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

Build the uuid-ossp module (which provides functions to generate UUIDs), using the specified UUID library. LIBRARY must be one of:

• 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

• ossp to use the OSSP UUID library

--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

Use libxslt when building the xml2 module. xml2 relies on this library to perform XSL transformations of XML.

--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

Allow the build to succeed even if PostgreSQL has no support for strong random numbers on the platform. A source of random numbers is needed for some authentication protocols, as well as some routines in the pgcrypto module. --disable-strong-random disables functionality that requires cryptographically strong random numbers, and substitutes a weak pseudo-random-number-generator for the generation of authentication salt values and query cancel keys. It may make authentication less secure.

--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

If using GCC, all programs and libraries are compiled with code coverage testing instrumentation. When run, they generate files in the build directory with code coverage metrics. SeeSection 33.5 for more information. This option is for use only with GCC and when doing development work.

--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

Compiles PostgreSQL with support for the dynamic tracing tool DTrace. See Section 28.5 for more information.

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:

./configure CC='gcc -m64' --enable-dtrace DTRACEFLAGS='-64' ...

Using Sun's compiler:

./configure CC='/opt/SUNWspro/bin/cc -xtarget=native64' --enable-dtrace DTRACEFLAGS='-64' ...

--enable-tap-tests

Enable tests using the Perl TAP tools. This requires a Perl installation and the Perl module IPC::Run. See Section 33.4 for more information.

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:

./configure CC=/opt/bin/gcc CFLAGS='-O2 -pipe'

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

Full path name of the Python interpreter. This will be used to determine the dependencies for building PL/Python. Also, whether Python 2 or 3 is specified here (or otherwise implicitly chosen) determines which variant of the PL/Python language becomes available. See Section 46.1 for more information.

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

make COPT='-Werror'

or

export COPT='-Werror'
make

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. 編譯

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

make
make all

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

All of PostgreSQL successfully made. Ready to install.

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

make world

顯示的最後一行應該是：

PostgreSQL, contrib, and documentation successfully made. Ready to install.

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

build-postgresql:
        $(MAKE) -C postgresql MAKELEVEL=0 all

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

3. 迴歸測試

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

make check

（這不能以 root 身份運行；請以非特權用戶身份執行。）有關解釋測試結果的詳細訊息，請參閱第 33 章。您可以在之後透過相同的命令重複此測試。

4. Installing the Files

Note

If you are upgrading an existing system be sure to read Section 18.6, which has instructions about upgrading a cluster.

To install PostgreSQL enter:

make install

This will install files into the directories that were specified in Step 1. Make sure that you have appropriate permissions to write into that area. Normally you need to do this step as root. Alternatively, you can create the target directories in advance and arrange for appropriate permissions to be granted.

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

make install-docs

If you built the world above, type instead:

make install-world

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:

make -C src/bin install
make -C src/include install
make -C src/interfaces install
make -C doc install

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.

./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.

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.

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):

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH

or in csh or tcsh:

setenv LD_LIBRARY_PATH /usr/local/pgsql/lib

Replace /usr/local/pgsql/lib with whatever you set --libdir to in Step 1. 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 http://xahlee.org/UnixResource_dir/_/ldpath.html.

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:

psql: error in loading shared libraries
libpq.so.2.1: cannot open shared object file: No such file or directory

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

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

/sbin/ldconfig /usr/local/pgsql/lib

(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:

/sbin/ldconfig -m /usr/local/pgsql/lib

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

16.5.2. Environment Variables

If you installed into /usr/local/pgsql or some other location that is not searched for programs by default, you should add /usr/local/pgsql/bin (or whatever you set --bindir to in Step 1) into your PATH. Strictly speaking, this is not necessary, but it will make the use of PostgreSQL much more convenient.

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):

PATH=/usr/local/pgsql/bin:$PATH
export PATH

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

set path = ( /usr/local/pgsql/bin $path )

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:

MANPATH=/usr/local/pgsql/share/man:$MANPATH
export MANPATH

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.

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

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

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

This section documents additional platform-specific issues regarding the installation and setup of PostgreSQL. Be sure to read the installation instructions, and in particular as well. Also, check 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:
• 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:

  Replace this with:

  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:

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

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=0xn_0000000, 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:0xn_0000000" 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

16.7.2. Cygwin

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:

  (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

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:

for HP's C compiler, or

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

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.

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.,

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

to read

(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

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

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

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

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

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

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.

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.

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 . 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 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 <>. If you are interested in porting PostgreSQL to a new platform, <> is the appropriate place to discuss that.

Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster. (The SQL standard uses the term catalog cluster.) A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require the postgres database to exist, but many external utility programs assume it exists. Another database created within each cluster during initialization is called template1. As the name suggests, this will be used as a template for subsequently created databases; it should not be used for actual work. (See for information about creating new databases within a cluster.)

In file system terms, a database cluster is a single directory under which all data will be stored. We call this the data directory or data area. It is completely up to you where you choose to store your data. There is no default, although locations such as /usr/local/pgsql/data or /var/lib/pgsql/data are popular. To initialize a database cluster, use the command , which is installed with PostgreSQL. The desired file system location of your database cluster is indicated by the -D option, for example:

Note that you must execute this command while logged into the PostgreSQL user account, which is described in the previous section.

Tip

As an alternative to the -D option, you can set the environment variable PGDATA.

Alternatively, you can run initdb via the program like so:

This may be more intuitive if you are using pg_ctl for starting and stopping the server (see ), so that pg_ctl would be the sole command you use for managing the database server instance.

initdb will attempt to create the directory you specify if it does not already exist. Of course, this will fail if initdb does not have permissions to write in the parent directory. It's generally recommendable that the PostgreSQL user own not just the data directory but its parent directory as well, so that this should not be a problem. If the desired parent directory doesn't exist either, you will need to create it first, using root privileges if the grandparent directory isn't writable. So the process might look like this:

initdb will refuse to run if the data directory exists and already contains files; this is to prevent accidentally overwriting an existing installation.

Because the data directory contains all the data stored in the database, it is essential that it be secured from unauthorized access. initdb therefore revokes access permissions from everyone but the PostgreSQL user, and optionally, group. Group access, when enabled, is read-only. This allows an unprivileged user in the same group as the cluster owner to take a backup of the cluster data or perform other operations that only require read access.

Note that enabling or disabling group access on an existing cluster requires the cluster to be shut down and the appropriate mode to be set on all directories and files before restarting PostgreSQL. Otherwise, a mix of modes might exist in the data directory. For clusters that allow access only by the owner, the appropriate modes are 0700 for directories and 0600 for files. For clusters that also allow reads by the group, the appropriate modes are 0750 for directories and 0640 for files.

However, while the directory contents are secure, the default client authentication setup allows any local user to connect to the database and even become the database superuser. If you do not trust other local users, we recommend you use one of initdb's -W, --pwprompt or --pwfile options to assign a password to the database superuser. Also, specify -A md5 or -A password so that the default trust authentication mode is not used; or modify the generated pg_hba.conf file after running initdb, but before you start the server for the first time. (Other reasonable approaches include using peer authentication or file system permissions to restrict connections. See for more information.)

Non-C and non-POSIX locales rely on the operating system's collation library for character set ordering. This controls the ordering of keys stored in indexes. For this reason, a cluster cannot switch to an incompatible collation library version, either through snapshot restore, binary streaming replication, a different operating system, or an operating system upgrade.

18.2.1. Use of Secondary File Systems

Many installations create their database clusters on file systems (volumes) other than the machine's “root” volume. If you choose to do this, it is not advisable to try to use the secondary volume's topmost directory (mount point) as the data directory. Best practice is to create a directory within the mount-point directory that is owned by the PostgreSQL user, and then create the data directory within that. This avoids permissions problems, particularly for operations such as pg_upgrade, and it also ensures clean failures if the secondary volume is taken offline.

18.2.2. File Systems

一般來說，任何具備 POSIX 標準的檔案系統都可以用於 PostgreSQL。 由於各種原因，使用者可能會使用不同的檔案系統，包括供應商支援、效能和熟悉程度。經驗上來說，在所有其他條件都相同的情況下，不應該僅因為切換檔案系統或進行次要的檔案系統配置變更，而期待效能或行為有明顯的改變。

18.2.2.1. NFS

可以使用 NFS 檔案系統來儲存 PostgreSQL 資料目錄。PostgreSQL 對 NFS 檔案系統並沒有任何特殊的要求，這意味著它假設 NFS 的行為與本地連接的磁碟完全相同。PostgreSQL 不使用已知在NFS上具有非標準行為的任何功能，例如檔案鎖定。

將 NFS 與 PostgreSQL 一起使用時，唯一確定要求是使用 hard 選項安裝檔案系統。使用 hard 選項，如果出現網路問題，NFS 程序可以無限期「hang」（暫停），因此此配置將需要仔細的監控。如果出現網路問題，soft 選項會中斷系統呼，但是 PostgreSQL 不會重複以此方式中斷的系統呼叫，因此任何此類中斷都將導致回報 I/O 錯誤。

不必要使用同步（sync）掛載選項。 async 選項的行為就足夠了，因為 PostgreSQL 會在適當的時機發出 fsync 呼叫來強制緩衝寫入。（這類似於它在本機檔案系統上的工作方式。）但是，強烈建議在存在該檔案的系統（主要是 Linux）上的 NFS 伺服器上使用 sync export 選項。否則，實際上不能保證 NFS 用戶端上的 fsync 或等效檔案可以到達伺服器上的永久儲存，這可能導致損壞，類似於在關閉參數 fsync 的情況下提供服務。這些掛載和輸出選項的預設設定在不同的供應商和版本之間略所不同，因此建議在任何情況下都需要進行檢查並且明確指定它們的內容，以避免任何誤解。

在某些情況下，可以透過 NFS 或更低等級的通訊協定（例如 iSCSI）存取外部儲存產品。在後者，儲存裝置為 block device，可以在其上建立任何可用的檔案系統。這種方法可能使 DBA 不必處理 NFS 的某些特質，不過，管理遠端儲存服務的複雜性會仍發生在其他層級之中。

Before anyone can access the database, you must start the database server. The database server program is called postgres. The postgres program must know where to find the data it is supposed to use. This is done with the -D option. Thus, the simplest way to start the server is:

$ postgres -D /usr/local/pgsql/data

which will leave the server running in the foreground. This must be done while logged into the PostgreSQL user account. Without -D, the server will try to use the data directory named by the environment variable PGDATA. If that variable is not provided either, it will fail.

Normally it is better to start postgres in the background. For this, use the usual Unix shell syntax:

$ postgres -D /usr/local/pgsql/data >logfile 2>&1 &

It is important to store the server's stdout and stderr output somewhere, as shown above. It will help for auditing purposes and to diagnose problems. (See Section 24.3 for a more thorough discussion of log file handling.)

The postgres program also takes a number of other command-line options. For more information, see the postgres reference page and Chapter 19 below.

This shell syntax can get tedious quickly. Therefore the wrapper program pg_ctl is provided to simplify some tasks. For example:

pg_ctl start -l logfile

will start the server in the background and put the output into the named log file. The -D option has the same meaning here as for postgres. pg_ctl is also capable of stopping the server.

Normally, you will want to start the database server when the computer boots. Autostart scripts are operating-system-specific. There are a few distributed with PostgreSQL in the contrib/start-scripts directory. Installing one will require root privileges.

Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use init.d or rc.d directories. Whatever you do, the server must be run by the PostgreSQL user account and not by root or any other user. Therefore you probably should form your commands using su postgres -c '...'. For example:

su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'

Here are a few more operating-system-specific suggestions. (In each case be sure to use the proper installation directory and user name where we show generic values.)

• For FreeBSD, look at the file contrib/start-scripts/freebsd in the PostgreSQL source distribution.

• On OpenBSD, add the following lines to the file /etc/rc.local:

  Copy

  if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then
      su -l postgres -c '/usr/local/pgsql/bin/pg_ctl start -s -l /var/postgresql/log -D /usr/local/pgsql/data'
      echo -n ' postgresql'
  fi

• On Linux systems either add

  Copy

  /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

  to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the PostgreSQL source distribution.

  When using systemd, you can use the following service unit file (e.g., at /etc/systemd/system/postgresql.service):

  Copy

  [Unit]
  Description=PostgreSQL database server
  Documentation=man:postgres(1)
  
  [Service]
  Type=notify
  User=postgres
  ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
  ExecReload=/bin/kill -HUP $MAINPID
  KillMode=mixed
  KillSignal=SIGINT
  TimeoutSec=0
  
  [Install]
  WantedBy=multi-user.target

  Using Type=notify requires that the server binary was built with configure --with-systemd.

  Consider carefully the timeout setting. systemd has a default timeout of 90 seconds as of this writing and will kill a process that does not notify readiness within that time. But a PostgreSQL server that might have to perform crash recovery at startup could take much longer to become ready. The suggested value of 0 disables the timeout logic.

• On NetBSD, use either the FreeBSD or Linux start scripts, depending on preference.

• On Solaris, create a file called /etc/init.d/postgresql that contains the following line:

  Copy

  su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"

  Then, create a symbolic link to it in /etc/rc3.d as S99postgresql.

While the server is running, its PID is stored in the file postmaster.pid in the data directory. This is used to prevent multiple server instances from running in the same data directory and can also be used for shutting down the server.

18.3.1. Server Start-up Failures

There are several common reasons the server might fail to start. Check the server's log file, or start it by hand (without redirecting standard output or standard error) and see what error messages appear. Below we explain some of the most common error messages in more detail.

LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets

This usually means just what it suggests: you tried to start another server on the same port where one is already running. However, if the kernel error message is not Address already in use or some variant of that, there might be a different problem. For example, trying to start a server on a reserved port number might draw something like:

$ postgres -p 666
LOG:  could not bind IPv4 address "127.0.0.1": Permission denied
HINT:  Is another postmaster already running on port 666? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets

A message like:

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5440001, size=4011376640, 03600).

probably means your kernel's limit on the size of shared memory is smaller than the work area PostgreSQL is trying to create (4011376640 bytes in this example). Or it could mean that you do not have System-V-style shared memory support configured into your kernel at all. As a temporary workaround, you can try starting the server with a smaller-than-normal number of buffers (shared_buffers). You will eventually want to reconfigure your kernel to increase the allowed shared memory size. You might also see this message when trying to start multiple servers on the same machine, if their total space requested exceeds the kernel limit.

An error like:

FATAL:  could not create semaphores: No space left on device
DETAIL:  Failed system call was semget(5440126, 17, 03600).

does not mean you've run out of disk space. It means your kernel's limit on the number of System V semaphores is smaller than the number PostgreSQL wants to create. As above, you might be able to work around the problem by starting the server with a reduced number of allowed connections (max_connections), but you'll eventually want to increase the kernel limit.

If you get an “illegal system call” error, it is likely that shared memory or semaphores are not supported in your kernel at all. In that case your only option is to reconfigure the kernel to enable these features.

Details about configuring System V IPC facilities are given in Section 18.4.1.

18.3.2. Client Connection Problems

Although the error conditions possible on the client side are quite varied and application-dependent, a few of them might be directly related to how the server was started. Conditions other than those shown below should be documented with the respective client application.

psql: could not connect to server: Connection refused
        Is the server running on host "server.joe.com" and accepting
        TCP/IP connections on port 5432?

This is the generic “I couldn't find a server to talk to” failure. It looks like the above when TCP/IP communication is attempted. A common mistake is to forget to configure the server to allow TCP/IP connections.

Alternatively, you'll get this when attempting Unix-domain socket communication to a local server:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

The last line is useful in verifying that the client is trying to connect to the right place. If there is in fact no server running there, the kernel error message will typically be either Connection refused or No such file or directory, as illustrated. (It is important to realize that Connection refused in this context does not mean that the server got your connection request and rejected it. That case will produce a different message, as shown in Section 20.15.) Other error messages such as Connection timed out might indicate more fundamental problems, like lack of network connectivity.

PostgreSQL offers encryption at several levels, and provides flexibility in protecting data from disclosure due to database server theft, unscrupulous administrators, and insecure networks. Encryption might also be required to secure sensitive data such as medical records or financial transactions.

Password Encryption

Database user passwords are stored as hashes (determined by the setting password_encryption), so the administrator cannot determine the actual password assigned to the user. If SCRAM or MD5 encryption is used for client authentication, the unencrypted password is never even temporarily present on the server because the client encrypts it before being sent across the network. SCRAM is preferred, because it is an Internet standard and is more secure than the PostgreSQL-specific MD5 authentication protocol.

Encryption For Specific Columns

The pgcrypto module allows certain fields to be stored encrypted. This is useful if only some of the data is sensitive. The client supplies the decryption key and the data is decrypted on the server and then sent to the client.

The decrypted data and the decryption key are present on the server for a brief time while it is being decrypted and communicated between the client and server. This presents a brief moment where the data and keys can be intercepted by someone with complete access to the database server, such as the system administrator.

Data Partition Encryption

Storage encryption can be performed at the file system level or the block level. Linux file system encryption options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block level or full disk encryption options include dm-crypt + LUKS on Linux and GEOM modules geli and gbde on FreeBSD. Many other operating systems support this functionality, including Windows.

This mechanism prevents unencrypted data from being read from the drives if the drives or the entire computer is stolen. This does not protect against attacks while the file system is mounted, because when mounted, the operating system provides an unencrypted view of the data. However, to mount the file system, you need some way for the encryption key to be passed to the operating system, and sometimes the key is stored somewhere on the host that mounts the disk.

Encrypting Data Across A Network

SSL connections encrypt all data sent across the network: the password, the queries, and the data returned. The pg_hba.conf file allows administrators to specify which hosts can use non-encrypted connections (host) and which require SSL-encrypted connections (hostssl). Also, clients can specify that they connect to servers only via SSL.

GSSAPI-encrypted connections encrypt all data sent across the network, including queries and data returned. (No password is sent across the network.) The pg_hba.conf file allows administrators to specify which hosts can use non-encrypted connections (host) and which require GSSAPI-encrypted connections (hostgssenc). Also, clients can specify that they connect to servers only on GSSAPI-encrypted connections (gssencmode=require).

Stunnel or SSH can also be used to encrypt transmissions.

SSL Host Authentication

It is possible for both the client and server to provide SSL certificates to each other. It takes some extra configuration on each side, but this provides stronger verification of identity than the mere use of passwords. It prevents a computer from pretending to be the server just long enough to read the password sent by the client. It also helps prevent “man in the middle” attacks where a computer between the client and server pretends to be the server and reads and passes all data between the client and server.

Client-Side Encryption

If the system administrator for the server's machine cannot be trusted, it is necessary for the client to encrypt the data; this way, unencrypted data never appears on the database server. Data is encrypted on the client before being sent to the server, and database results have to be decrypted on the client before being used.

There are several ways to shut down the database server. You control the type of shutdown by sending different signals to the master postgres process.SIGTERM

This is the Smart Shutdown mode. After receiving SIGTERM, the server disallows new connections, but lets existing sessions end their work normally. It shuts down only after all of the sessions terminate. If the server is in online backup mode, it additionally waits until online backup mode is no longer active. While backup mode is active, new connections will still be allowed, but only to superusers (this exception allows a superuser to connect to terminate online backup mode). If the server is in recovery when a smart shutdown is requested, recovery and streaming replication will be stopped only after all regular sessions have terminated.SIGINT

This is the Fast Shutdown mode. The server disallows new connections and sends all existing server processes SIGTERM, which will cause them to abort their current transactions and exit promptly. It then waits for all server processes to exit and finally shuts down. If the server is in online backup mode, backup mode will be terminated, rendering the backup useless.SIGQUIT

This is the Immediate Shutdown mode. The server will send SIGQUIT to all child processes and wait for them to terminate. If any do not terminate within 5 seconds, they will be sent SIGKILL. The master server process exits as soon as all child processes have exited, without doing normal database shutdown processing. This will lead to recovery (by replaying the WAL log) upon next start-up. This is recommended only in emergencies.

The pg_ctl program provides a convenient interface for sending these signals to shut down the server. Alternatively, you can send the signal directly using kill on non-Windows systems. The PID of the postgres process can be found using the ps program, or from the file postmaster.pid in the data directory. For example, to do a fast shutdown:

$ kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`

Important

It is best not to use SIGKILL to shut down the server. Doing so will prevent the server from releasing shared memory and semaphores. Furthermore, SIGKILL kills the postgres process without letting it relay the signal to its subprocesses, so it might be necessary to kill the individual subprocesses by hand as well.

To terminate an individual session while allowing other sessions to continue, use pg_terminate_backend() (see Table 9.83) or send a SIGTERM signal to the child process associated with the session.

The PostgreSQL 10.5 sources can be obtained from the download section of our website: https://www.postgresql.org/download/. 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:

gunzip postgresql-10.5.tar.gz
tar xf postgresql-10.5.tar

(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 Appendix I.

It is possible to use SSH to encrypt the network connection between clients and a PostgreSQL server. Done properly, this provides an adequately secure network connection, even for non-SSL-capable clients.

First make sure that an SSH server is running properly on the same machine as the PostgreSQL server and that you can log in using ssh as some user. Then you can establish a secure tunnel with a command like this from the client machine:

ssh -L 63333:localhost:5432 joe@foo.com

The first number in the -L argument, 63333, is the port number of your end of the tunnel; it can be any unused port. (IANA reserves ports 49152 through 65535 for private use.) The second number, 5432, is the remote end of the tunnel: the port number your server is using. The name or IP address between the port numbers is the host with the database server you are going to connect to, as seen from the host you are logging in to, which is foo.com in this example. In order to connect to the database server using this tunnel, you connect to port 63333 on the local machine:

psql -h localhost -p 63333 postgres

To the database server it will then look as though you are really user joe on host foo.com connecting to localhost in that context, and it will use whatever authentication procedure was configured for connections from this user and host. Note that the server will not think the connection is SSL-encrypted, since in fact it is not encrypted between the SSH server and the PostgreSQL server. This should not pose any extra security risk as long as they are on the same machine.

In order for the tunnel setup to succeed you must be allowed to connect via ssh as joe@foo.com, just as if you had attempted to use ssh to create a terminal session.

You could also have set up the port forwarding as

ssh -L 63333:foo.com:5432 joe@foo.com

but then the database server will see the connection as coming in on its foo.com interface, which is not opened by the default setting listen_addresses = 'localhost'. This is usually not what you want.

If you have to “hop” to the database server via some login host, one possible setup could look like this:

ssh -L 63333:db.foo.com:5432 joe@shell.foo.com

Note that this way the connection from shell.foo.com to db.foo.com will not be encrypted by the SSH tunnel. SSH offers quite a few configuration possibilities when the network is restricted in various ways. Please refer to the SSH documentation for details.

Tip

Several other applications exist that can provide secure tunnels using a procedure similar in concept to the one just described.

This section discusses how to upgrade your database data from one PostgreSQL release to a newer one.

Current PostgreSQL version numbers consist of a major and a minor version number. For example, in the version number 10.1, the 10 is the major version number and the 1 is the minor version number, meaning this would be the first minor release of the major release 10. For releases before PostgreSQL version 10.0, version numbers consist of three numbers, for example, 9.5.3. In those cases, the major version consists of the first two digit groups of the version number, e.g., 9.5, and the minor version is the third number, e.g., 3, meaning this would be the third minor release of the major release 9.5.

Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6. To update between compatible versions, you simply replace the executables while the server is down and restart the server. The data directory remains unchanged — minor upgrades are that simple.

For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades. The traditional method for moving data to a new major version is to dump and reload the database, though this can be slow. A faster method is pg_upgrade. Replication methods are also available, as discussed below.

New major versions also typically introduce some user-visible incompatibilities, so application programming changes might be required. All user-visible changes are listed in the release notes (Appendix E); pay particular attention to the section labeled "Migration". If you are upgrading across several major versions, be sure to read the release notes for each intervening version.

Cautious users will want to test their client applications on the new version before switching over fully; therefore, it's often a good idea to set up concurrent installations of old and new versions. When testing a PostgreSQL major upgrade, consider the following categories of possible changes:Administration

The capabilities available for administrators to monitor and control the server often change and improve in each major release.SQL

Typically this includes new SQL command capabilities and not changes in behavior, unless specifically mentioned in the release notes.Library API

Typically libraries like libpq only add new functionality, again unless mentioned in the release notes.System Catalogs

System catalog changes usually only affect database management tools.Server C-language API

This involves changes in the backend function API, which is written in the C programming language. Such changes affect code that references backend functions deep inside the server.

18.6.1. Upgrading Data via pg_dumpall

One upgrade method is to dump data from one major version of PostgreSQL and reload it in another — to do this, you must use a logical backup tool like pg_dumpall; file system level backup methods will not work. (There are checks in place that prevent you from using a data directory with an incompatible version of PostgreSQL, so no great harm can be done by trying to start the wrong server version on a data directory.)

It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of enhancements that might have been made in these programs. Current releases of the dump programs can read data from any server version back to 7.0.

These instructions assume that your existing installation is under the /usr/local/pgsql directory, and that the data area is in /usr/local/pgsql/data. Substitute your paths appropriately.

1. If making a backup, make sure that your database is not being updated. This does not affect the integrity of the backup, but the changed data would of course not be included. If necessary, edit the permissions in the file /usr/local/pgsql/data/pg_hba.conf (or equivalent) to disallow access from everyone except you. See Chapter 20 for additional information on access control.

   To back up your database installation, type:

   Copy

   pg_dumpall > outputfile

   To make the backup, you can use the pg_dumpall command from the version you are currently running; see Section 25.1.2 for more details. For best results, however, try to use the pg_dumpall command from PostgreSQL 12.2, since this version contains bug fixes and improvements over older versions. While this advice might seem idiosyncratic since you haven't installed the new version yet, it is advisable to follow it if you plan to install the new version in parallel with the old version. In that case you can complete the installation normally and transfer the data later. This will also decrease the downtime.

2. Shut down the old server:

   Copy

   pg_ctl stop

   On systems that have PostgreSQL started at boot time, there is probably a start-up file that will accomplish the same thing. For example, on a Red Hat Linux system one might find that this works:

   Copy

   /etc/rc.d/init.d/postgresql stop

   See Chapter 18 for details about starting and stopping the server.

3. If restoring from backup, rename or delete the old installation directory if it is not version-specific. It is a good idea to rename the directory, rather than delete it, in case you have trouble and need to revert to it. Keep in mind the directory might consume significant disk space. To rename the directory, use a command like this:

   Copy

   mv /usr/local/pgsql /usr/local/pgsql.old

   (Be sure to move the directory as a single unit so relative paths remain unchanged.)

4. Install the new version of PostgreSQL as outlined in Section 16.4.

5. Create a new database cluster if needed. Remember that you must execute these commands while logged in to the special database user account (which you already have if you are upgrading).

   Copy

   /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

6. Restore your previous pg_hba.conf and any postgresql.conf modifications.

7. Start the database server, again using the special database user account:

   Copy

   /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

8. Finally, restore your data from backup with:

   Copy

   /usr/local/pgsql/bin/psql -d postgres -f outputfile

   using the new psql.

The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like:

pg_dumpall -p 5432 | psql -d postgres -p 5433

to transfer your data.

18.6.2. Upgrading Data via pg_upgrade

The pg_upgrade module allows an installation to be migrated in-place from one major PostgreSQL version to another. Upgrades can be performed in minutes, particularly with --link mode. It requires steps similar to pg_dumpall above, e.g. starting/stopping the server, running initdb. The pg_upgrade documentation outlines the necessary steps.

18.6.3. Upgrading Data via Replication

It is also possible to use logical replication methods to create a standby server with the updated version of PostgreSQL. This is possible because logical replication supports replication between different major versions of PostgreSQL. The standby can be on the same computer or a different computer. Once it has synced up with the master server (running the older version of PostgreSQL), you can switch masters and make the standby the master and shut down the older database instance. Such a switch-over results in only several seconds of downtime for an upgrade.

This method of upgrading can be performed using the built-in logical replication facilities as well as using external logical replication systems such as pglogical, Slony, Londiste, and Bucardo.

PostgreSQL also has native support for using GSSAPI to encrypt client/server communications for increased security. Support requires that a GSSAPI implementation (such as MIT krb5) is installed on both client and server systems, and that support in PostgreSQL is enabled at build time (see Chapter 16).

18.10.1. Basic Setup

The PostgreSQL server will listen for both normal and GSSAPI-encrypted connections on the same TCP port, and will negotiate with any connecting client on whether to use GSSAPI for encryption (and for authentication). By default, this decision is up to the client (which means it can be downgraded by an attacker); see Section 20.1 about setting up the server to require the use of GSSAPI for some or all connections.

Other than configuration of the negotiation behavior, GSSAPI encryption requires no setup beyond that which is necessary for GSSAPI authentication. (For more information on configuring that, see Section 20.6.)

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.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.9 節。

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 索引掃描回傳集合大小的軟上限。詳情請參閱第 66.5 節。

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

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 /tmp cleanup 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, either use SSL certificates and make sure that clients check the server's certificate, or use GSSAPI encryption (or both, if they're on separate connections).

To prevent spoofing with SSL, 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).

To prevent spoofing with GSSAPI, the server must be configured to accept only hostgssenc connections (Section 20.1) and use gss authentication with them. The TCP client must connect using gssencmode=require.

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

19.6.1. 發送伺服器（Sender Server）

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

max_wal_senders (integer)

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

max_replication_slots (integer)

指定伺服器可以支援的最大複寫槽數（請參閱第 26.2.6 節）。預設值為 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）

可以將要複寫資料發送到一個或多個備用伺服器，在主要伺服器上設定這些參數。請注意，除了這些參數之外，還必須在主要伺服器上正確設定 wal_level，可以選擇啟用 WAL 歸檔（參閱第 19.5.3 節）。備用伺服器上這些參數的值是無意義的，儘管您可能希望將它們設定在那裡以預備備用資料庫成為主要伺服器的可能性。

synchronous_standby_names (string)

指定可支援同步複寫的備用伺服器列表，如第 26.2.8 節中所述。 將有一個或多個線上同步的備用資料庫；在這些備用伺服器確認收到其資料後，將允許等待提交的事務繼續進行。同步備用資料庫將是其名稱出現在此列表中的那些，並且即時以串流傳輸資料（如 pg_stat_replication 檢視表中的串流傳輸狀態所示）。指定多個同步備用資料庫可以達到非常高的可用性並防止資料遺失。

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

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

[FIRST] num_sync ( standby_name [, ...] )
ANY num_sync ( standby_name [, ...] )
standby_name [, ...]

其中 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 都不區分大小寫，無論是否為雙引號。

如果此處未指定同步的備用伺服器名稱，則不啟用同步複寫，事務提交就不會等待複寫。這是預設配置。即使啟用了同步複寫，也可以將單個事務設定為不等待複寫，方法是將 synchronous_commit 參數設定為 local 或 off。

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

vacuum_defer_cleanup_age (integer)

指定 VACUUM 和 HOT 更新將延遲清除過期資料列版本的事務數。預設值為 0 事務，這意味著可以盡快刪除過期資料列的版本。也就是說，只要它們不再對任何開放的事務是可見的。您可能希望在支持熱備用伺服器的主要服務器上將其設定為非零值，如第 26.5 節中所述。這樣可以讓備用資料庫上的查詢有更多時間完成，而不會因過早清理資料列而導致衝突。但是，由於該值是根據主要服務器上所發生的寫入事務的數量來衡量的，因此很難預測備用查詢可用多少額外的寬限時間。 此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

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

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

19.6.3. Standby Servers

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

hot_standby (boolean)

指定是否可以在回復期間連線和執行查詢，如第 26.5 節中所述。預設值為 on。 此參數只能在伺服器啟動時設定。它僅在歸檔回復或備機模式下有效。

max_standby_archive_delay (integer)

當 Hot Standby 處於啟用狀態時，此參數確定備用伺服器在取消與即將套用的 WAL 項目衝突的備用查詢之前應等待的時間，如第 26.5.2 節中所述。當從 WAL 歸檔中讀取 WAL 資料時，max_standby_archive_delay 適用（因此不是當下的）。預設值為 30 秒。如果未指定，則單位為毫秒。值 -1 時允許備用資料庫永遠等待衝突查詢完成。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

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

max_standby_streaming_delay (integer)

當 Hot Standby 處於啓用狀態時，此參數決定備用伺服器在取消與即將套用的 WAL 項目衝突的備用查詢之前應等待的時間，如第 26.5.2 節中所述。當透過串流複寫接收 WAL 資料時，套用max_standby_streaming_delay。預設值為 30 秒。如果未指定，則單位為毫秒。值 -1 時允許備用資料庫永遠等待衝突查詢完成。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

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

wal_receiver_status_interval (integer)

Specifies the minimum frequency for the WAL receiver process on the standby to send information about replication progress to the primary or upstream standby, where it can be seen using the pg_stat_replication view. The standby will report the last write-ahead log location it has written, the last position it has flushed to disk, and the last position it has applied. This parameter's value is the maximum interval, in seconds, between reports. Updates are sent each time the write or flush positions change, or at least as often as specified by this parameter. Thus, the apply position may lag slightly behind the true position. Setting this parameter to zero disables status updates completely. This parameter can only be set in the postgresql.conf file or on the server command line. The default value is 10 seconds.

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.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.

deadlock_timeout (integer)

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

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

max_locks_per_transaction (integer)

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

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

max_pred_locks_per_transaction (integer)

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

max_pred_locks_per_relation (integer)

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

max_pred_locks_per_page (integer)

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

19.13.1. Previous PostgreSQL Versions

19.13.2. Platform and Client Compatibility

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.

SeeSection 4.1.6for more information.

quote_all_identifiers

(

boolean

)

When the database generates SQL, force all identifiers to be quoted, even if they are not (currently) keywords. This will affect the output ofEXPLAINas well as the results of functions likepg_get_viewdef. See also the--quote-all-identifiersoption ofpg_dumpandpg_dumpall.

standard_conforming_strings

(

boolean

)

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning inPostgreSQL9.1, the default ison(prior releases defaulted tooff). Applications can check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if an application desires backslashes to be treated as escape characters.

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.

Refer toSection 9.2for related information.

This feature was designed to allow parameters not normally known to PostgreSQL to be added by add-on modules (such as procedural languages). This allows extension modules to be configured in the standard ways.

Custom options have two-part names: an extension name, then a dot, then the parameter name proper, much like qualified names in SQL. An example is plpgsql.variable_conflict.

Because custom options may need to be set in processes that have not loaded the relevant extension module, PostgreSQL will accept a setting for any two-part parameter name. Such variables are treated as placeholders and have no function until the module that defines them is loaded. When an extension module is loaded, it will add its variable definitions, convert any placeholder values according to those definitions, and issue warnings for any unrecognized placeholders that begin with its extension name.

exit_on_error (boolean)

If on, any error will terminate the current session. By default, this is set to off, so that only FATAL errors will terminate the session.

restart_after_crash (boolean)

When set to on, which is the default, PostgreSQL will automatically reinitialize after a backend crash. Leaving this value set to on is normally the best way to maximize the availability of the database. However, in some circumstances, such as when PostgreSQL is being invoked by clusterware, it may be useful to disable the restart so that the clusterware can gain control and take any actions it deems appropriate.

data_sync_retry (boolean)

When set to off, which is the default, PostgreSQL will raise a PANIC-level error on failure to flush modified data files to the file system. This causes the database server to crash. This parameter can only be set at server start.

On some operating systems, the status of data in the kernel's page cache is unknown after a write-back failure. In some cases it might have been entirely forgotten, making it unsafe to retry; the second attempt may be reported as successful, when in fact the data has been lost. In these circumstances, the only way to avoid data loss is to recover from the WAL after any failure is reported, preferably after investigating the root cause of the failure and replacing any faulty hardware.

If set to on, PostgreSQL will instead report an error but continue to run so that the data flushing operation can be retried in a later checkpoint. Only set it to on after investigating the operating system's treatment of buffered data in case of write-back failure.

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time (see Chapter 16).

18.9.1. Basic Setup

With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client's option; see Section 20.1 about how to set up the server to require use of SSL for some or all connections.

To start in SSL mode, files containing the server certificate and private key must exist. By default, these files are expected to be named server.crt and server.key, respectively, in the server's data directory, but other names and locations can be specified using the configuration parameters ssl_cert_file and ssl_key_file.

On Unix systems, the permissions on server.key must disallow any access to world or group; achieve this by the command chmod 0600 server.key. Alternatively, the file can be owned by root and have group read access (that is, 0640 permissions). That setup is intended for installations where certificate and key files are managed by the operating system. The user under which the PostgreSQL server runs should then be made a member of the group that has access to those certificate and key files.

If the data directory allows group read access then certificate files may need to be located outside of the data directory in order to conform to the security requirements outlined above. Generally, group access is enabled to allow an unprivileged user to backup the database, and in that case the backup software will not be able to read the certificate files and will likely error.

If the private key is protected with a passphrase, the server will prompt for the passphrase and will not start until it has been entered. Using a passphrase by default disables the ability to change the server's SSL configuration without a server restart, but see ssl_passphrase_command_supports_reload. Furthermore, passphrase-protected private keys cannot be used at all on Windows.

The first certificate in server.crt must be the server's certificate because it must match the server's private key. The certificates of “intermediate” certificate authorities can also be appended to the file. Doing this avoids the necessity of storing intermediate certificates on clients, assuming the root and intermediate certificates were created with v3_ca extensions. This allows easier expiration of intermediate certificates.

It is not necessary to add the root certificate to server.crt. Instead, clients must have the root certificate of the server's certificate chain.

18.9.2. OpenSSL Configuration

PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.

OpenSSL supports a wide range of ciphers and authentication algorithms, of varying strength. While a list of ciphers can be specified in the OpenSSL configuration file, you can specify ciphers specifically for use by the database server by modifying ssl_ciphers in postgresql.conf.

Note

It is possible to have authentication without encryption overhead by using NULL-SHA or NULL-MD5 ciphers. However, a man-in-the-middle could read and pass communications between client and server. Also, encryption overhead is minimal compared to the overhead of authentication. For these reasons NULL ciphers are not recommended.

18.9.3. Using Client Certificates

To require the client to supply a trusted certificate, place certificates of the root certificate authorities (CAs) you trust in a file in the data directory, set the parameter ssl_ca_file in postgresql.conf to the new file name, and add the authentication option clientcert=verify-ca or clientcert=verify-full to the appropriate hostssl line(s) in pg_hba.conf. A certificate will then be requested from the client during SSL connection startup. (See Section 33.18 for a description of how to set up certificates on the client.)

For a hostssl entry with clientcert=verify-ca, the server will verify that the client's certificate is signed by one of the trusted certificate authorities. If clientcert=verify-full is specified, the server will not only verify the certificate chain, but it will also check whether the username or its mapping matches the cn (Common Name) of the provided certificate. Note that certificate chain validation is always ensured when the cert authentication method is used (see Section 20.12).

Intermediate certificates that chain up to existing root certificates can also appear in the ssl_ca_file file if you wish to avoid storing them on clients (assuming the root and intermediate certificates were created with v3_ca extensions). Certificate Revocation List (CRL) entries are also checked if the parameter ssl_crl_file is set. (See http://h41379.www4.hpe.com/doc/83final/ba554_90007/ch04s02.html for diagrams showing SSL certificate usage.)

The clientcert authentication option is available for all authentication methods, but only in pg_hba.conf lines specified as hostssl. When clientcert is not specified or is set to no-verify, the server will still verify any presented client certificates against its CA file, if one is configured — but it will not insist that a client certificate be presented.

There are two approaches to enforce that users provide a certificate during login.

The first approach makes use of the cert authentication method for hostssl entries in pg_hba.conf, such that the certificate itself is used for authentication while also providing ssl connection security. See Section 20.12 for details. (It is not necessary to specify any clientcert options explicitly when using the cert authentication method.) In this case, the cn (Common Name) provided in the certificate is checked against the user name or an applicable mapping.

The second approach combines any authentication method for hostssl entries with the verification of client certificates by setting the clientcert authentication option to verify-ca or verify-full. The former option only enforces that the certificate is valid, while the latter also ensures that the cn (Common Name) in the certificate matches the user name or an applicable mapping.

18.9.4. SSL Server File Usage

Table 18.2 summarizes the files that are relevant to the SSL setup on the server. (The shown file names are default names. The locally configured names could be different.)

Table 18.2. SSL Server File Usage

The server reads these files at server start and whenever the server configuration is reloaded. On Windows systems, they are also re-read whenever a new backend process is spawned for a new client connection.

If an error in these files is detected at server start, the server will refuse to start. But if an error is detected during a configuration reload, the files are ignored and the old SSL configuration continues to be used. On Windows systems, if an error in these files is detected at backend start, that backend will be unable to establish an SSL connection. In all these cases, the error condition is reported in the server log.

18.9.5. Creating Certificates

To create a simple self-signed certificate for the server, valid for 365 days, use the following OpenSSL command, replacing dbhost.yourdomain.com with the server's host name:

openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

Then do:

chmod og-rwx server.key

because the server will reject the file if its permissions are more liberal than this. For more details on how to create your server private key and certificate, refer to the OpenSSL documentation.

While a self-signed certificate can be used for testing, a certificate signed by a certificate authority (CA) (usually an enterprise-wide root CA) should be used in production.

To create a server certificate whose identity can be validated by clients, first create a certificate signing request (CSR) and a public/private key file:

openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key

Then, sign the request with the key to create a root certificate authority (using the default OpenSSL configuration file location on Linux):

openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

Finally, create a server certificate signed by the new root certificate authority:

openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key

openssl x509 -req -in server.csr -text -days 365 \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt

server.crt and server.key should be stored on the server, and root.crt should be stored on the client so the client can verify that the server's leaf certificate was signed by its trusted root certificate. root.key should be stored offline for use in creating future certificates.

It is also possible to create a chain of trust that includes intermediate certificates:

# root
openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

# intermediate
openssl req -new -nodes -text -out intermediate.csr \
  -keyout intermediate.key -subj "/CN=intermediate.yourdomain.com"
chmod og-rwx intermediate.key
openssl x509 -req -in intermediate.csr -text -days 1825 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out intermediate.crt

# leaf
openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 \
  -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \
  -out server.crt

server.crt and intermediate.crt should be concatenated into a certificate file bundle and stored on the server. server.key should also be stored on the server. root.crt should be stored on the client so the client can verify that the server's leaf certificate was signed by a chain of certificates linked to its trusted root certificate. root.key and intermediate.key should be stored offline for use in creating future certificates.

19.8.1. 記錄在哪裡

log_destination (string)

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

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

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

stderr log/postgresql.log
csvlog log/postgresql.csv

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

注意

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

local0.*    /var/log/postgresql

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

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

logging_collector (boolean)

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

注意

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

注意

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

log_directory (string)

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

log_filename (string)

當啟用 logging_collector 時，此參數設定建立的日誌檔案的檔案名稱。該值被視為 strftime 模式，因此 ％-escapes 可用於指定隨時間變化的檔案名稱。（請注意，如果有任何時區相關的 ％-escapes，計算將在由 log_timezone 指定的區域中完成。）支援的 ％-escapes 與 Open Group 的 strftime 規範中列出的類似。請注意，系統的 strftime 並未直接使用，因此特定於平台的（非標準）延伸功能不起作用。預設值是 postgresql-％Y-％m-％d_％H％M％S.log。

如果您指定的檔案名稱不含跳脫符號，則應該計劃使用日誌覆寫程序來避免最後存滿整個磁碟。在 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將語句訊息連接到之後的執行時間訊息。

表格 19.1 說明了 PostgreSQL 使用的訊息嚴重性等級。如果將日誌記錄輸出發送到 syslog 或 Windows 的事件日誌，則嚴重性等級將按表格中所示進行轉換。

Table 19.1. Message Severity Levels

19.8.3. 要記錄什麼

application_name (string)

application_name 可以是少於 NAMEDATALEN 個字元的任何字串（標準版本中為 64 個字元）。它通常由應用程序在連線到伺服器時設定。此名稱將顯示在 pg_stat_activity 檢視表中，並包含在 CSV 日誌項目中。它還可以透過 log_line_prefix 參數包含在日常日誌項目中。application_name 中只能使用可列印的 ASCII 字元。其他字元將替換為問號（？）。

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

這些參數可以發出各種除錯輸出。設定後，它們將輸出産生的語法解析樹，查詢重寫程序輸出或每個已執行查詢的執行計劃。這些訊息以 LOG 訊息等級發出，因此預設情況下它們將顯示在伺服器日誌中，但不會發送到客戶端。您可以透過調整 client_min_messages 或 log_min_messages 來變更它。這些參數預設是關閉的。

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_min_duration_statement 設定為零之間的區別在於，超出 log_min_duration_statement 會強制記錄查詢的語句，但此選項不會。因此，如果啟用了 log_duration 且 log_min_duration_statement 具有正值，則會記錄所有持續時間，但僅包含超過閾值的語句的查詢語句。此行為對於在高負載環境中收集統計訊息非常有用。

log_error_verbosity (enum)

控制記錄的每條訊息在伺服器日誌中寫入的詳細訊息量。有效值為 TERSE，DEFAULT 和 VERBOSE，每個都向顯示的訊息加上更多內容。TERSE 排除記錄DETAIL，HINT，QUERY 和 CONTEXT 錯誤訊息。VERBOSE 輸出包括 SQLSTATE 錯誤代碼（另請參閱附錄 A）以及産生錯誤的原始檔案名稱，函數名稱和行號。只有超級使用者才能更改此設定。

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 産生連線指標，請使用以下查詢：

SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
       to_hex(pid)
FROM pg_stat_activity;

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

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

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

log_line_prefix = '%m [%p] %q%u@%d/%a '

log_lock_waits (boolean)

控制連線等待時間超過 deadlock_timeout 時是否產生日誌訊息。這對於確定鎖定等待是否導致性能較差很有用。預設是關閉的。只有超級使用者可以變更此設定。

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)

讓每個複寫指令都記錄在伺服器日誌中。有關複寫指令的更多訊息，請參閱第 52.4 節。預設值為 off。只有超級使用者才能變更此設定。

log_temp_files (integer)

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

log_timezone (string)

設定用於在伺服器日誌中寫入的時間戳記的時區。與 TimeZone 不同，此值是叢集範圍的，因此所有連線都將一致地報告時間戳記。內建的預設值是 GMT，但這通常在 postgresql.conf 中會再設定過；initdb 將在那裡安裝與其系統環境相對應的設定。有關更多訊息，請參閱第 8.5.3 節。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

19.8.4. 使用 CSV 格式輸出記錄

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

CREATE TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  PRIMARY KEY (session_id, session_line_num)
);

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

COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;

您需要做一些事情來簡化匯入 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）

這些設定控制如何修改伺服器程序的程序標題。程序標題通常使用如 ps 或 Windows 上的 Process Explorer 查看。詳情請參閱第 28.1 節。

cluster_name (string)

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

update_process_title (boolean)

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

For additional information on tuning these settings, see Section 29.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, logical adds 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 29.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 29.1. This parameter can only be set in the postgresql.conf file or on the server command line.

full_page_writes (boolean)

啟用此參數後，PostgreSQL 伺服器會在檢查點之後對該頁面的首次修改期間將每個磁碟頁面的全部內容寫入 WAL。這是必要的，因為在作業系統當機期間正在進行的頁面寫入可能僅部分完成，從而導致包含新舊資料混合在磁碟頁面之中。通常在 WAL 中所儲存的資料列層級更改資料不足以在當機後還原期間完全還原此類頁面。儲存完整的頁面映像可確保還原正確的頁面，但是這樣做的代價是增加了必須寫入 WAL 的資料量。 （由於 WAL 重放總是從檢查點開始，因此在檢查點之後每頁的第一次更改期間執行此操作就足夠了。也因此，減少全頁寫入成本的一種方法是增加檢查點間隔參數。）

停用此參數可加快正常操作的速度，但在系統故障後可能會導致不可恢復的資料損壞或未知的資料損壞。風險與關閉 fsync 相似，儘管較小，但應僅根據針對該參數建議的相同情況將其關閉。

禁用此參數不會影響使用 WAL 歸檔進行時間點還原作業（PITR）（請參閱第 25.3 節）。

該參數只能在 postgresql.conf 檔案或伺服器命令列中設定。預設為 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. If this value is specified without units, it is taken as WAL blocks, that is XLOG_BLCKSZ bytes, typically 8kB. 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, in time terms. After flushing WAL the writer sleeps for the length of time given by wal_writer_delay, unless woken up sooner by an asynchronously committing transaction. If the last flush happened less than wal_writer_delay ago and less than wal_writer_flush_after worth of WAL has been produced since, then WAL is only written to the operating system, not flushed to disk. If this value is specified without units, it is taken as milliseconds. 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, in volume terms. If the last flush happened less than wal_writer_delay ago and less than wal_writer_flush_after worth of WAL has 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 always flushed immediately. If this value is specified without units, it is taken as WAL blocks, that is XLOG_BLCKSZ bytes, typically 8kB. The default is 1MB. This parameter can only be set in the postgresql.conf file or on the server command line.

commit_delay (integer)

Setting commit_delay adds a time delay 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 the commit_delay 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. If this value is specified without units, it is taken as microseconds. 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)

自動 WAL 檢查點之間的最長時間。如果指定的值不帶單位，則以秒為單位。有效範圍是 30 秒至 1 天。預設值為五分鐘（5 分鐘）。增大此參數可能會增加當機回復所需的時間。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

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 this amount of data has 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. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. 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 WAL segment files happen closer together than this amount of time (which suggests that max_wal_size ought to be raised). If this value is specified without units, it is taken as seconds. 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)

使 WAL 增長到自動 WAL 檢查點之間的最大大小。這是一個軟限制。在特殊情況下，例如重度負載，失敗的 archive_command 或較高的 wal_keep_segments 設定，WAL 大小可能會超過 max_wal_size。如果指定的該值不帶單位，則以 MegaByte 為單位。預設值為1 GB。增大此參數可能會增加當機回復所需的時間。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

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. If this value is specified without units, it is taken as megabytes. 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 amount of time has 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. If this value is specified without units, it is taken as seconds. This parameter can only be set in the postgresql.conf file or on the server command line.

19.5.4. Archive Recovery

This section describes the settings that apply only for the duration of the recovery. They must be reset for any subsequent recovery you wish to perform.

“Recovery” covers using the server as a standby or for executing a targeted recovery. Typically, standby mode would be used to provide high availability and/or read scalability, whereas a targeted recovery is used to recover from data loss.

To start the server in standby mode, create a file called standby.signal in the data directory. The server will enter recovery and will not stop recovery when the end of archived WAL is reached, but will keep trying to continue recovery by connecting to the sending server as specified by the primary_conninfo setting and/or by fetching new WAL segments using restore_command. For this mode, the parameters from this section and Section 19.6.3 are of interest. Parameters from Section 19.5.5 will also be applied but are typically not useful in this mode.

To start the server in targeted recovery mode, create a file called recovery.signal in the data directory. If both standby.signal and recovery.signal files are created, standby mode takes precedence. Targeted recovery mode ends when the archived WAL is fully replayed, or when recovery_target is reached. In this mode, the parameters from both this section and Section 19.5.5 will be used.

restore_command (string)

The local shell command to execute to retrieve an archived segment of the WAL file series. This parameter is required for archive recovery, but optional for streaming replication. Any %f in the string is replaced by the name of the file to retrieve from the archive, and any %p is replaced by the copy destination path name on the server. (The path name is relative to the current working directory, i.e., the cluster's data directory.) Any %r is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow a restore to be restartable, so this information can be used to truncate the archive to just the minimum required to support restarting from the current restore. %r is typically only used by warm-standby configurations (see Section 26.2). Write %% to embed an actual % character.

It is important for the command to return a zero exit status only if it succeeds. The command will be asked for file names that are not present in the archive; it must return nonzero when so asked. Examples:

restore_command = 'cp /mnt/server/archivedir/%f "%p"'
restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'  # Windows

An exception is that if the command was terminated by a signal (other than SIGTERM, which is used as part of a database server shutdown) or an error by the shell (such as command not found), then recovery will abort and the server will not start up.

This parameter can only be set at server start.

archive_cleanup_command (string)

This optional parameter specifies a shell command that will be executed at every restartpoint. The purpose of archive_cleanup_command is to provide a mechanism for cleaning up old archived WAL files that are no longer needed by the standby server. Any %r is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow a restore to be restartable, and so all files earlier than %r may be safely removed. This information can be used to truncate the archive to just the minimum required to support restart from the current restore. The pg_archivecleanup module is often used in archive_cleanup_command for single-standby configurations, for example:

archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r'

Note however that if multiple standby servers are restoring from the same archive directory, you will need to ensure that you do not delete WAL files until they are no longer needed by any of the servers. archive_cleanup_command would typically be used in a warm-standby configuration (see Section 26.2). Write %% to embed an actual % character in the command.

If the command returns a nonzero exit status then a warning log message will be written. An exception is that if the command was terminated by a signal or an error by the shell (such as command not found), a fatal error will be raised.

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

recovery_end_command (string)

This parameter specifies a shell command that will be executed once only at the end of recovery. This parameter is optional. The purpose of the recovery_end_command is to provide a mechanism for cleanup following replication or recovery. Any %r is replaced by the name of the file containing the last valid restart point, like in archive_cleanup_command.

If the command returns a nonzero exit status then a warning log message will be written and the database will proceed to start up anyway. An exception is that if the command was terminated by a signal or an error by the shell (such as command not found), the database will not proceed with startup.

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

19.5.5. Recovery Target

By default, recovery will recover to the end of the WAL log. The following parameters can be used to specify an earlier stopping point. At most one of recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, or recovery_target_xid can be used; if more than one of these is specified in the configuration file, an error will be raised. These parameters can only be set at server start.

recovery_target = 'immediate'

This parameter specifies that recovery should end as soon as a consistent state is reached, i.e. as early as possible. When restoring from an online backup, this means the point where taking the backup ended.

Technically, this is a string parameter, but 'immediate' is currently the only allowed value.

recovery_target_name (string)

This parameter specifies the named restore point (created with pg_create_restore_point()) to which recovery will proceed.

recovery_target_time (timestamp)

This parameter specifies the time stamp up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive.

recovery_target_xid (string)

This parameter specifies the transaction ID up to which recovery will proceed. Keep in mind that while transaction IDs are assigned sequentially at transaction start, transactions can complete in a different numeric order. The transactions that will be recovered are those that committed before (and optionally including) the specified one. The precise stopping point is also influenced by recovery_target_inclusive.

recovery_target_lsn (pg_lsn)

This parameter specifies the LSN of the write-ahead log location up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive. This parameter is parsed using the system data type pg_lsn.

The following options further specify the recovery target, and affect what happens when the target is reached:

recovery_target_inclusive (boolean)

Specifies whether to stop just after the specified recovery target (on), or just before the recovery target (off). Applies when recovery_target_lsn, recovery_target_time, or recovery_target_xid is specified. This setting controls whether transactions having exactly the target WAL location (LSN), commit time, or transaction ID, respectively, will be included in the recovery. Default is on.

recovery_target_timeline (string)

Specifies recovering into a particular timeline. The value can be a numeric timeline ID or a special value. The value current recovers along the same timeline that was current when the base backup was taken. The value latest recovers to the latest timeline found in the archive, which is useful in a standby server. latest is the default.

You usually only need to set this parameter in complex re-recovery situations, where you need to return to a state that itself was reached after a point-in-time recovery. See Section 25.3.5 for discussion.

recovery_target_action (enum)

Specifies what action the server should take once the recovery target is reached. The default is pause, which means recovery will be paused. promote means the recovery process will finish and the server will start to accept connections. Finally shutdown will stop the server after reaching the recovery target.

The intended use of the pause setting is to allow queries to be executed against the database to check if this recovery target is the most desirable point for recovery. The paused state can be resumed by using pg_wal_replay_resume() (see Table 9.86), which then causes recovery to end. If this recovery target is not the desired stopping point, then shut down the server, change the recovery target settings to a later target and restart to continue recovery.

The shutdown setting is useful to have the instance ready at the exact replay point desired. The instance will still be able to replay more WAL records (and in fact will have to replay WAL records since the last checkpoint next time it is started).

Note that because recovery.signal will not be removed when recovery_target_action is set to shutdown, any subsequent start will end with immediate shutdown unless the configuration is changed or the recovery.signal file is removed manually.

This setting has no effect if no recovery target is set. If hot_standby is not enabled, a setting of pause will act the same as shutdown.

這些設定控制自動資料清理（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.3.1. 連線設定

listen_addresses (string)

指定伺服器監聽用戶端應用程序連線的 TCP/IP 位址。該值採用逗號分隔的主機名稱或數字 IP 位址列表的形式。特殊項目「*」對應於所有可用的 IP。項目 0.0.0.0 允許監聽所有 IPv4 位址，還有「::」允許監聽所有 IPv6 位址。如果列表為空，則伺服器根本不監聽任何 IP 接口，在這種情況下，就只能使用 Unix-domain socket 來連接它。預設值是 localhost，它只允許進行本地 TCP/IP loopback 連線。儘管用戶端身份驗證（）允許對誰可以存取伺服器進行細維的控制，但 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，只有寫入權限很重要，所以設定還是撤消讀取或執行權限都沒有意義。）

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

此參數在某些系統上無關緊要，特別是從 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_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)

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)

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 (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.9.1. 查詢和索引統計收集器

這些參數控制伺服器端的統計數據收集功能。啟用統計數據收集後，可以透過 pg_stat 和 pg_statio 系列系統檢視表取得相關的資料。有關更多資訊，請參閱。

track_activities (boolean)

啟用收集有關每個連線的當下執行命令的資訊以及該命令開始執行的時間的資訊。預設情況下，此參數是開啓的。請注意，即使啟用此功能，也不是所有使用者都可以取用，而只有超級使用者和擁有該連線的使用者可以檢視這些數據，因此它不會有安全風險。僅超級使用者可以變更此設定。

track_activity_query_size (integer)

為 pg_stat_activity.query 欄位指定保留的字元數，以追踪每個連線查詢當下執行的指令字串。預設值為1024。只能在伺服器啟動時設定此參數。

track_counts (boolean)

啟用有關資料庫活動的統計資訊收集。預設情況下，此參數是啟用的，因為 autovacuum 背景程序需要收集資訊。僅超級使用者可以變更此設定。

track_io_timing (boolean)

啟用資料庫 I/O 呼叫的計時。此參數預設情況下是處於關閉狀態，因為它將重複查詢作業系統當下的時間，這可能會導致某些平台上的大量運算成本。您可以使用 工具來測量系統計時的成本。I/O 時序資訊會顯示在 中，使用 BUFFERS 選項時在 的輸出中以及 中顯示。僅超級使用者可以變更改此設定。

track_functions (enum)

啟用對函數呼叫計數和使用時間的追踪。指定 pl 僅追踪程序語言函數，all 則表示也追踪 SQL 和 C 語言函數。預設值為 none，這將停用函數統計資訊追踪。僅超級使用者可以變更此設定。

注意 不管此設定如何，都不會追踪足夠簡單以「inline」到呼叫查詢中的 SQL 語言函數。

stats_temp_directory (string)

設定用於儲存臨時統計數據的目錄。這可以是相對於資料目錄的路徑，也可以是絕對路徑。預設值為 pg_stat_tmp。將其指向基於 RAM 的檔案系統可以降低物理性 I/O 的要求，使得效能提升。只能在 postgresql.conf 檔案或伺服器命令列中設定此參數。

19.9.2. 統計監控

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

對於每個查詢，將相對應模組的效能統計數據輸出到伺服器日誌。這是一個粗略的分析工具，類似於 Unix getrusage() 作業系統的工具。log_statement_stats 總計整個查詢語句過程的統計數據，而其他的設定是每個查詢模組的統計數據。log_statement_stats 不能與任何其他模組選項同時啟用。預設情況下，所有這些選項都是停用的。只有超級使用者可以變更這些設定。

19.7.1. 規劃方法配置

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

enable_material (boolean)

啟用或停用查詢計劃程序對實作的使用。完全抑制實作是不可能的，但是關閉此變數會阻止計劃程序插入實體化的節點，除非真的需要它。預設為開啓。

enable_mergejoin (boolean)

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

enable_nestloop (boolean)

啟用或停用查詢計劃程序使用 nested-loop join 計劃。完全抑制 nested-loop join 是不可能的，但如果有其他可用方法，則關閉此變數會阻止規劃器使用它。預設為開啓。

enable_parallel_append (boolean)

Enables or disables the query planner's use of parallel-aware append plan types. The default is on.

enable_parallel_hash (boolean)

Enables or disables the query planner's use of hash-join plan types with parallel hash. Has no effect if hash-join plans are not also enabled. The default is on.

enable_partition_pruning (boolean)

enable_partitionwise_join (boolean)

Enables or disables the query planner's use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions. Partitionwise join currently applies only when the join conditions include all the partition keys, which must be of the same data type and have exactly matching sets of child partitions. Because partitionwise join planning can use significantly more CPU time and memory during planning, the default is off.

enable_partitionwise_aggregate (boolean)

Enables or disables the query planner's use of partitionwise grouping or aggregation, which allows grouping or aggregation on a partitioned tables performed separately for each partition. If the GROUP BY clause does not include the partition keys, only partial aggregation can be performed on a per-partition basis, and finalization must be performed later. Because partitionwise grouping or aggregation can use significantly more CPU time and memory during planning, the default is off.

enable_seqscan (boolean)

啟用或停用查詢計劃程序使用循序掃描計劃類型。完全抑制循序掃描是不可能的，但如果有其他方法可用，則關閉此變數會阻止計劃程序使用。預設為開啓。

enable_sort (boolean)

啟用或停用查詢計劃程序使用明確的排序步驟。完全抑制明確排序是不可能的，但如果有其他可用方法，則關閉此變數會阻止計劃程序使用。預設為開啓。

enable_tidscan (boolean)

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

19.7.2. 規劃程序成本常數

本節中描述的成本變數是以比例來使用的。只有它們的相對值很重要，因此按相同因子放大或縮小它們將不會讓規劃程式的選擇有所變化。預設情況下，這些成本變數基於連續頁面讀取的成本；也就是說，seq_page_cost 通常設定為 1.0，其他成本變數是相對參考其設定的。 但是，如果您願意，可以使用不同的比例，例如特定主機上的實際執行時間（以毫秒為單位）。

注意 不幸的是，並沒有明確定義的方法來決定成本變數的理想值。它們最好被視為特定安裝環境可能接受的所有查詢組合的平均值。這意味著僅僅根據一些實驗來改變它們都不是真正的最佳。

seq_page_cost (floating point)

random_page_cost (floating point)

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

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)

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:

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.

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)

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.

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.

當用戶端應用程序連線到資料庫伺服器時，它將指定要連線的 PostgreSQL 資料庫使用者名稱，這與以特定使用者身份登入到 Unix 伺服器的方式大致相同。在 SQL 環境中，有效的資料庫使用者名確定資料庫物件的存取權限 - 有關詳細訊息，請參閱。因此，限制哪些資料庫使用者可以進行連線是非常重要的。

注意

正如第 21 章所描述的，PostgreSQL 實際上是以「角色」的角度來管理權限的。在本章中，我們一直使用資料庫使用者來表示「具有 LOGIN 權限的角色」。

身份驗證是資料庫伺服器建立用戶端身份的過程，延伸確認用戶端應用程序（或執行用戶端應用程序的使用者）是否被允許以請求的資料庫使用者名稱進行連線。

PostgreSQL 提供了許多不同的用戶端身份驗證方法。用於驗證特定用戶端連線的方法可以根據（用戶端）主機位址、資料庫名稱和使用者名稱進行驗證。

PostgreSQL 資料庫使用者名稱在邏輯上與運行服務器的作業系統的使用者名稱是分開的。如果特定伺服器的所有用戶在伺服器的機器上也有帳戶，那麼分配與其作業系統用戶名搭配的資料庫用戶名是有意義的。但是，接受遠端連線的伺服器可能有許多沒有本地作業系統帳戶的資料庫用戶，在這種情況下，資料庫用戶名和作業系統用戶名之間不需要有所關連。

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 ) is influenced by block_size. See for information.

data_checksums (boolean)

Reports whether data checksums are enabled for this cluster. See 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 for more information. This value is determined when a database is created.

lc_ctype (string)

Reports the locale that determines character classifications. See 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)

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)

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 configuration parameter.) The ident map file contains lines of the general form:

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 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

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, calling the SQL function pg_reload_conf(), or using 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 is shown in . 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

For convenience there are also single letter command-line option switches available for some parameters. They are described in . Some of these options exist for historical reasons, and their presence as a single-letter option does not necessarily indicate an endorsement to use the option heavily.

Table 19.2. Short Option Key

此身份驗證方法使用 SSL 用戶端憑證進行身份驗證。因此，它僅適用於 SSL 連線。使用此身份驗證方法時，伺服器將要求用戶端提供有效可信任的憑證。不會有密碼提示發送給用戶端。憑證的 cn（Common Name）屬性將與請求連線的資料庫使用者名稱進行比較，如果符合，則允許登錄。使用者名稱對應可用於允許 cn 與資料庫使用者名稱不同。

SSL 憑證身份驗證支援以下配置選項：

map

允許在系統使用者名稱和資料庫使用者名稱之間進行對應。相關詳細資訊，請參閱。

在指定憑證認證的 pg_hba.conf 記錄中，憑證選項 clientcert 被假設為 verify-ca 或 verify-full，由於此方法需要用戶端憑證，因此無法將其關閉。 cert 方法增加了基本 clientcert 憑證有效性測試的方法是檢查 cn 屬性是否與資料庫使用者名稱相符。

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 . 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 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 and , e.g., CREATE ROLE foo WITH LOGIN 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 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 md5 and 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.

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 . 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 . 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 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 and , 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.

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.

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.

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:

The following configuration options are supported for GSSAPI:include_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.

The following configuration options are supported for SSPI:include_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

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

The following configuration options are supported for ident:map

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 X_and 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

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

This authentication method operates similarly to password except that it uses LDAP as the password verification method. LDAP is used only to validate the user name/password pairs. Therefore the user must already exist in the database before LDAP can be used for authentication.

LDAP authentication can operate in two modes. In the first mode, which we will call the simple bind mode, the server will bind to the distinguished name constructed as prefix usernamesuffix. Typically, the prefix parameter is used to specify cn=, or DOMAIN\ in an Active Directory environment. suffix is used to specify the remaining part of the DN in a non-Active Directory environment.

In the second mode, which we will call the search+bind mode, the server first binds to the LDAP directory with a fixed user name and password, specified with ldapbinddn and ldapbindpasswd, and performs a search for the user trying to log in to the database. If no user and password is configured, an anonymous bind will be attempted to the directory. The search will be performed over the subtree at ldapbasedn, and will try to do an exact match of the attribute specified in ldapsearchattribute. Once the user has been found in this search, the server disconnects and re-binds to the directory as this user, using the password specified by the client, to verify that the login is correct. This mode is the same as that used by LDAP authentication schemes in other software, such as Apache mod_authnz_ldap and pam_ldap. This method allows for significantly more flexibility in where the user objects are located in the directory, but will cause two separate connections to the LDAP server to be made.

The following configuration options are used in both modes:ldapserver

Names or IP addresses of LDAP servers to connect to. Multiple servers may be specified, separated by spaces.ldapport

Port number on LDAP server to connect to. If no port is specified, the LDAP library's default port setting will be used.ldaptls

Set to 1 to make the connection between PostgreSQL and the LDAP server use TLS encryption. Note that this only encrypts the traffic to the LDAP server — the connection to the client will still be unencrypted unless SSL is used.

The following options are used in simple bind mode only:ldapprefix

String to prepend to the user name when forming the DN to bind as, when doing simple bind authentication.ldapsuffix

String to append to the user name when forming the DN to bind as, when doing simple bind authentication.

The following options are used in search+bind mode only:ldapbasedn

Root DN to begin the search for the user in, when doing search+bind authentication.ldapbinddn

DN of user to bind to the directory with to perform the search when doing search+bind authentication.ldapbindpasswd

Password for user to bind to the directory with to perform the search when doing search+bind authentication.ldapsearchattribute

Attribute to match against the user name in the search when doing search+bind authentication. If no attribute is specified, the uid attribute will be used.ldapurl

An RFC 4516 LDAP URL. This is an alternative way to write some of the other LDAP options in a more compact and standard form. The format is

scope must be one of base, one, sub, typically the latter. Only one attribute is used, and some other components of standard LDAP URLs such as filters and extensions are not supported.

For non-anonymous binds, ldapbinddn and ldapbindpasswd must be specified as separate options.

To use encrypted LDAP connections, the ldaptls option has to be used in addition to ldapurl. The ldaps URL scheme (direct SSL connection) is not supported.

LDAP URLs are currently only supported with OpenLDAP, not on Windows.

It is an error to mix configuration options for simple bind with options for search+bind.

Here is an example for a simple-bind LDAP configuration:

When a connection to the database server as database user someuser is requested, PostgreSQL will attempt to bind to the LDAP server using the DN cn=someuser, dc=example, dc=netand the password provided by the client. If that connection succeeds, the database access is granted.

Here is an example for a search+bind configuration:

When a connection to the database server as database user someuser is requested, PostgreSQL will attempt to bind anonymously (since ldapbinddn was not specified) to the LDAP server, perform a search for (uid=someuser) under the specified base DN. If an entry is found, it will then attempt to bind using that found information and the password supplied by the client. If that second connection succeeds, the database access is granted.

Here is the same search+bind configuration written as a URL:

Some other software that supports authentication against LDAP uses the same URL format, so it will be easier to share the configuration.

Tip

Since LDAP often uses commas and spaces to separate the different parts of a DN, it is often necessary to use double-quoted parameter values when configuring LDAP options, as shown in the examples.

20.3.8. RADIUS Authentication

This authentication method operates similarly to password except that it uses RADIUS as the password verification method. RADIUS is used only to validate the user name/password pairs. Therefore the user must already exist in the database before RADIUS can be used for authentication.

When using RADIUS authentication, an Access Request message will be sent to the configured RADIUS server. This request will be of type Authenticate Only, and include parameters for user name, password (encrypted) and NAS Identifier. The request will be encrypted using a secret shared with the server. The RADIUS server will respond to this server with either Access Accept or Access Reject. There is no support for RADIUS accounting.

Multiple RADIUS servers can be specified, in which case they will be tried sequentially. If a negative response is received from a server, the authentication will fail. If no response is received, the next server in the list will be tried. To specify multiple servers, put the names within quotes and separate the server names with a comma. If multiple servers are specified, all other RADIUS options can also be given as a comma separate list, to apply individual values to each server. They can also be specified as a single value, in which case this value will apply to all servers.

The following configuration options are supported for RADIUS:radiusservers

The name or IP addresses of the RADIUS servers to connect to. This parameter is required.radiussecrets

The shared secrets used when talking securely to the RADIUS server. This must have exactly the same value on the PostgreSQL and RADIUS servers. It is recommended that this be a string of at least 16 characters. This parameter is required.

Note

The encryption vector used will only be cryptographically strong if PostgreSQL is built with support for OpenSSL. In other cases, the transmission to the RADIUS server should only be considered obfuscated, not secured, and external security measures should be applied if necessary.radiusports

The port number on the RADIUS servers to connect to. If no port is specified, the default port 1812 will be used.radiusidentifiers

The string used as NAS Identifier in the RADIUS requests. This parameter can be used as a second parameter identifying for example which database user the user is attempting to authenticate as, which can be used for policy matching on the RADIUS server. If no identifier is specified, the default postgresql will be used.

20.3.9. Certificate Authentication

This authentication method uses SSL client certificates to perform authentication. It is therefore only available for SSL connections. When using this authentication method, the server will require that the client provide a valid, trusted certificate. No password prompt will be sent to the client. The cn (Common Name) attribute of the certificate will be compared to the requested database user name, and if they match the login will be allowed. User name mapping can be used to allow cn to be different from the database user name.

The following configuration options are supported for SSL certificate authentication:map

In a pg_hba.conf record specifying certificate authentication, the authentication option clientcert is assumed to be 1, and it cannot be turned off since a client certificate is necessary for this method. What the cert method adds to the basic clientcert certificate validity test is a check that the cn attribute matches the database user name.

20.3.10. PAM Authentication

The following configuration options are supported for PAM:pamservice

PAM service name.pam_use_hostname

Determines whether the remote IP address or the host name is provided to PAM modules through the PAM_RHOST item. By default, the IP address is used. Set this option to 1 to use the resolved host name instead. Host name resolution can lead to login delays. (Most PAM configurations don't use this information, so it is only necessary to consider this setting if a PAM configuration was specifically created to make use of it.)

Note

If PAM is set up to read /etc/shadow, authentication will fail because the PostgreSQL server is started by a non-root user. However, this is not an issue when PAM is configured to use LDAP or other authentication methods.

20.3.11. BSD Authentication

This authentication method operates similarly to password except that it uses BSD Authentication to verify the password. BSD Authentication is used only to validate user name/password pairs. Therefore the user's role must already exist in the database before BSD Authentication can be used for authentication. The BSD Authentication framework is currently only available on OpenBSD.

BSD Authentication in PostgreSQL uses the auth-postgresql login type and authenticates with the postgresql login class if that's defined in login.conf. By default that login class does not exist, and PostgreSQL will use the default login class.

Note

To use BSD Authentication, the PostgreSQL user account (that is, the operating system user running the server) must first be added to the auth group. The auth group exists by default on OpenBSD systems.

用戶端身份驗證由組態檔案控制，組態檔案通常名稱為 pg_hba.conf，並儲存在資料庫叢集的資料目錄中。 （HBA 代表 host-based authentication。）當 initdb 初始化資料目錄時，將安裝預設的 pg_hba.conf 檔案。但是，可以將身份驗證組態檔案放在其他路徑；請參閱 組態參數。

pg_hba.conf 檔案的一般格式是一組記錄，每行一個。空白行將被忽略，# comment 字元後面的任何文字都將被忽略。記錄不能跨行。記錄由許多段落組成，這些段落由空格或 tab 分隔。如果段落的值用了雙引號，則段落可以包含空格。在資料庫，使用者或位址段落（例如，all 或 replication）中括起其中一個關鍵字會使該字失去其特殊含義，並且只是將資料庫，使用者或主機與該名稱相匹配。

每條記錄指定連線類型，用戶端 IP 位址範圍（如果與連線類型相關）、資料庫名稱、使用者名稱以及符合這些參數的連線身份驗證方法。具有符合的連線類型、用戶端位址、要求的資料庫和使用者名稱的第一個記錄用於執行身份驗證。沒有“fall-through”或“replication”：如果選擇了一條記錄而認證失敗，就不再考慮後續記錄。如果沒有記錄匹配，則拒絕存取。

記錄可以是下面的七種格式之一

段落的含義如下：

local

此記錄搭配使用 Unix-domain socket 的連線嘗試。如果沒有此類型的記錄，則不允許使用 Unix-domain socket 連線。

host

此記錄用於使用 TCP/IP 進行的連線嘗試。主機記錄使用 SSL 或非 SSL 連線嘗試.

重要 除非使用 組態參數的適當值啟動伺服器，否則將無法進行遠端 TCP/IP 連線，因為預設行為是僅在 localhost 上監聽 TCP/IP 連線。

hostssl

此記錄會套用於使用 TCP/IP 進行的連線嘗試，但僅限於使用 SSL 加密進行連線時。

hostnossl

此記錄類型與 hostssl 具有相反的行為；它僅套用於透過 TCP/IP 且不使用 SSL 的連線嘗試。

database

指定此記錄所要求搭配的資料庫名稱。值 all 使其搭配所有資料庫。如果請求的資料庫與請求的使用者具有相同的名稱，則可以用 sameuser 值來指定。值 samerole 指定所請求的使用者必須是與請求的資料庫同名的角色成員。 （ samegroup 是一個過時但仍然被接受的 samerole 別名。）超級使用者不被認為是同一角色的成員，除非他們直接或間接地明確地成為角色的成員，而不僅僅是作為超級使用者。值 replication 指定在請求 physical replication 連線時的記錄搭配（請注意，複寫連線不指定任何特定資料庫）。否則，這是特定 PostgreSQL 資料庫的名稱。可以透過用逗號分隔它們來設定多個資料庫名稱，也可以透過在檔案名稱前加上 @ 來指定包含資料庫名稱的額外檔案。

user

指定此記錄所限制的資料庫使用者名稱。all 表示所有使用者都適用。否則，它就是是特定資料庫使用者的名稱，要就是帶有 + 的群組名稱。（回想一下，PostgreSQL 中的使用者和群組之間並沒有真正的差別； + 標記實際上表示「符合直接或間接地成為該角色成員的任何角色」，而沒有 + 標記的名稱僅適用該特定角色。 ）為此，只有超級使用者直接或間接明確地是角色的成員，而不僅僅是憑藉超級使用者，才將其視為角色的成員。可以使用逗號分隔多個使用者名稱。透過在檔案名稱前面加上 @ 來指定包含使用者名稱的獨立設定檔案。

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.)

以點（.）開頭的主機名稱表示與實際主機名稱的後段比對。因此，.example.com 與 foo.example.com 比較是相符的（不僅限於 example.com）。

當在 pg_hba.conf 中指定了主機名稱時，您應該確保名稱解析足夠快。設定本地名稱解析暫存（例如 nscd）可能是有幫助的。另外，您可能希望啟用配置參數 log_hostname 來查看用戶端的主機名稱，而不是日誌中的 IP 位址。

此欄位僅適用於 host、hostssl 和 hostnossl 規則項目。

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

trust

reject

無條件地拒絕連線。這對於「過濾」群組網路中的某些主機很有用。例如拒絕阻止特定主機的連接，而更前面的規則則允許特定網路中的其餘主機進行連線。

scram-sha-256

md5

password

gss

sspi

ident

peer

ldap

radius

cert

pam

bsd

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.

@ 語法結構包含的檔案會被入為名稱列表，可以用空格或逗號分隔。就像在 pg_hba.conf 中一樣，註釋由 ＃ 引入，並且允許巢狀式的 @ 結構。除非 @ 之後的檔案名稱是絕對路徑，否則它將被視為相對於包含引用檔案的目錄。

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.

Example 20.1. Example pg_hba.conf Entries

The following parameters are intended for work on the PostgreSQL source code, and in some cases to assist with recovery of severely damaged databases. There should be no reason to use them on a production database. As such, they have been excluded from the sample postgresql.conf file. Note that many of these parameters require special source compilation flags to work at all.

allow_system_table_mods (boolean)

Allows modification of the structure of system tables. This is used by initdb. This parameter can only be set at server start.

ignore_system_indexes (boolean)

Ignore system indexes when reading system tables (but still update the indexes when modifying the tables). This is useful when recovering from damaged system indexes. This parameter cannot be changed after session start.

post_auth_delay (integer)

The amount of time to delay when a new server process is started, after it conducts the authentication procedure. This is intended to give developers an opportunity to attach to the server process with a debugger. If this value is specified without units, it is taken as seconds. A value of zero (the default) disables the delay. This parameter cannot be changed after session start.

pre_auth_delay (integer)

The amount of time to delay just after a new server process is forked, before it conducts the authentication procedure. This is intended to give developers an opportunity to attach to the server process with a debugger to trace down misbehavior in authentication. If this value is specified without units, it is taken as seconds. A value of zero (the default) disables the delay. This parameter can only be set in the postgresql.conf file or on the server command line.

trace_notify (boolean)

Generates a great amount of debugging output for the LISTEN and NOTIFY commands. or must be DEBUG1 or lower to send this output to the client or server logs, respectively.

trace_recovery_messages (enum)

Enables logging of recovery-related debugging output that otherwise would not be logged. This parameter allows the user to override the normal setting of , but only for specific messages. This is intended for use in debugging Hot Standby. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, and LOG. The default, LOG, does not affect logging decisions at all. The other values cause recovery-related debug messages of that priority or higher to be logged as though they had LOG priority; for common settings of log_min_messages this results in unconditionally sending them to the server log. This parameter can only be set in the postgresql.conf file or on the server command line.

trace_sort (boolean)

If on, emit information about resource usage during sort operations. This parameter is only available if the TRACE_SORT macro was defined when PostgreSQL was compiled. (However, TRACE_SORT is currently defined by default.)

trace_locks (boolean)

If on, emit information about lock usage. Information dumped includes the type of lock operation, the type of lock and the unique identifier of the object being locked or unlocked. Also included are bit masks for the lock types already granted on this object as well as for the lock types awaited on this object. For each lock type a count of the number of granted locks and waiting locks is also dumped as well as the totals. An example of the log file output is shown here:

Details of the structure being dumped may be found in src/include/storage/lock.h.