1 of 100

III. 系統管理

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

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

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

16. 以預編譯套件安裝

PostgreSQL 以預編譯套件的形式提供給當今大多數常見的操作系統。如果可用，這是為系統用戶推薦的安裝 PostgreSQL 的方式。以原始碼安裝的方式（請參閱）只推薦給開發 PostgreSQL 或擴充套件的朋友.

有關提供預編譯套件的平台更新列表，請參閱 PostgreSQL 網站上的下載頁面，網址為，按照個別平台的說明進行操作。

17. 以原始碼安裝

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

17.1. 簡要步驟

./configure
make
su
make install
adduser postgres
mkdir -p /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/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

詳細步驟於本章的其餘部分中說明。

17.2. 環境需求

一般來說，一個現代的 Unix 相容平台應該都能夠執行 PostgreSQL。在發佈時接受過特定測試的平台在下面的第 17.6 節中列出。在發行版的 doc 子目錄中，有幾個特定於平台的 FAQ 文檔，如果您遇到問題，您可能希望查閱。

The following software packages are required for building PostgreSQL:

GNU make version 3.81 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:
```
make --version
```
You need an ISO/ANSI C compiler (at least C99-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 and pg_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 sysconfig module. The minimum required version is Python 3.2.
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 https://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. OpenSSL is also required for random number generation on platforms that do not have /dev/urandom (except Windows). The minimum required version is 1.0.1.
You need Kerberos, OpenLDAP, and/or PAM, if you want to support authentication using those services.
You need LZ4, if you want to support compression of data with that method; see default_toast_compression and wal_compression.
You need Zstandard, if you want to support compression of data with that method; see wal_compression. The minimum required version is 1.4.0.
要編譯 PostgreSQL 文件，有一些獨特的要求；請參閱第 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:

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 lex and 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 https://www.gnu.org/prep/ftp for a list) or at ftp://ftp.gnu.org/gnu/.

Also check that you have sufficient disk space. You will need about 350 MB for the source tree during compilation and about 60 MB for the installation directory. An empty database cluster takes about 40 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 300 MB. Use the df command to check free disk space.

17.3. Getting The Source

The PostgreSQL source code for released versions can be obtained from the download section of our website: . Download the postgresql-version.tar.gz or postgresql-version.tar.bz2 file you're interested in, then unpack it:

This will create a directory postgresql-version under the current directory with the PostgreSQL sources. Change into that directory for the rest of the installation procedure.

Alternatively, you can use the Git version control system; see for more information.

17.4. 安裝流程

Configuration
The first step of the installation procedure is to configure the source tree for your system and choose the options you would like. This is done by running the configure script. For a default installation simply enter:
```
./configure
```
This script will run a number of tests to determine values for various system dependent variables and detect any quirks of your operating system, and finally will create several files in the build tree to record what it found.
You can also run configure in a directory outside the source tree, and then build there, if you want to keep the build directory separate from the original source files. This procedure is called a VPATH build. Here's how:
```
mkdir build_dir
cd build_dir
/path/to/source/tree/configure [options go here]
make
```
The default configuration will build the server and utilities, as well as all client applications and interfaces that require only a C compiler. All files will be installed under /usr/local/pgsql by default.
You can customize the build and installation process by supplying one or more command line options to configure. Typically you would customize the install location, or the set of optional features that are built. configure has a large number of options, which are described in Section 17.4.1.
Also, configure responds to certain environment variables, as described in Section 17.4.2. These provide additional ways to customize the configuration.
Build
To start the build, type either of:
```
make
make all
```
(Remember to use GNU make.) The build will take a few minutes depending on your hardware.
If you want to build everything that can be built, including the documentation (HTML and man pages), and the additional modules (contrib), type instead:
```
make world
```
If you want to build everything that can be built, including the additional modules (contrib), but without the documentation, type instead:
```
make world-bin
```
If you want to invoke the build from another makefile rather than manually, you must unset MAKELEVEL or set it to zero, for instance like this:
```
build-postgresql:
        $(MAKE) -C postgresql MAKELEVEL=0 all
```
Failure to do that can lead to strange error messages, typically about missing header files.
Regression Tests
If you want to test the newly built server before you install it, you can run the regression tests at this point. The regression tests are a test suite to verify that PostgreSQL runs on your machine in the way the developers expected it to. Type:
```
make check
```
(This won't work as root; do it as an unprivileged user.) See Chapter 33 for detailed information about interpreting the test results. You can repeat this test at any later time by issuing the same command.
Installing the Files
Note
If you are upgrading an existing system be sure to read Section 19.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.
If you built the world without the documentation above, type instead:
```
make install-world-bin
```
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.
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.

17.4.1. `configure` Options

configure's command line options are explained below. This list is not exhaustive (use ./configure --help to get one that is). The options not covered here are meant for advanced use-cases such as cross-compilation, and are documented in the standard Autoconf documentation.

17.4.1.1. Installation Locations

These options control where make install will put the files. The --prefix option is sufficient for most cases. If you have special needs, you can customize the installation subdirectories with the other options described in this section. Beware however that changing the relative locations of the different subdirectories may render the installation non-relocatable, meaning you won't be able to move it after installation. (The man and doc locations are not affected by this restriction.) For relocatable installs, you might want to use the --disable-rpath option described later.

--prefix=PREFIX

Install all files under the directory PREFIX instead of /usr/local/pgsql. The actual files will be installed into various subdirectories; no files will ever be installed directly into the PREFIX directory.

--exec-prefix=EXEC-PREFIX

You can install architecture-dependent files under a different prefix, EXEC-PREFIX, than what PREFIX was set to. This can be useful to share architecture-independent files between hosts. If you omit this, then EXEC-PREFIX is set equal to PREFIX and both architecture-dependent and independent files will be installed under the same tree, which is probably what you want.

--bindir=DIRECTORY

Specifies the directory for executable programs. The default is EXEC-PREFIX/bin, which normally means /usr/local/pgsql/bin.

--sysconfdir=DIRECTORY

Sets the directory for various configuration files, PREFIX/etc by default.

--libdir=DIRECTORY

Sets the location to install libraries and dynamically loadable modules. The default is EXEC-PREFIX/lib.

--includedir=DIRECTORY

Sets the directory for installing C and C++ header files. The default is PREFIX/include.

--datarootdir=DIRECTORY

Sets the root directory for various types of read-only data files. This only sets the default for some of the following options. The default is PREFIX/share.

--datadir=DIRECTORY

Sets the directory for read-only data files used by the installed programs. The default is DATAROOTDIR. Note that this has nothing to do with where your database files will be placed.

--localedir=DIRECTORY

Sets the directory for installing locale data, in particular message translation catalog files. The default is DATAROOTDIR/locale.

--mandir=DIRECTORY

The man pages that come with PostgreSQL will be installed under this directory, in their respective manx subdirectories. The default is DATAROOTDIR/man.

--docdir=DIRECTORY

Sets the root directory for installing documentation files, except “man” pages. This only sets the default for the following options. The default value for this option is DATAROOTDIR/doc/postgresql.

--htmldir=DIRECTORY

The HTML-formatted documentation for PostgreSQL will be installed under this directory. The default is DATAROOTDIR.

Note

Care has been taken to make it possible to install PostgreSQL into shared installation locations (such as /usr/local/include) without interfering with the namespace of the rest of the system. First, the string “/postgresql” is automatically appended to datadir, sysconfdir, and docdir, unless the fully expanded directory name already contains the string “postgres” or “pgsql”. For example, if you choose /usr/local as prefix, the documentation will be installed in /usr/local/doc/postgresql, but if the prefix is /opt/postgres, then it will be in /opt/postgres/doc. The public C header files of the client interfaces are installed into includedir and are namespace-clean. The internal header files and the server header files are installed into private directories under includedir. See the documentation of each interface for information about how to access its header files. Finally, a private subdirectory will also be created, if appropriate, under libdir for dynamically loadable modules.

17.4.1.2. PostgreSQL Features

The options described in this section enable building of various PostgreSQL features that are not built by default. Most of these are non-default only because they require additional software, as described in Section 17.2.

--enable-nls[=LANGUAGES]

Enables Native Language Support (NLS), that is, the ability to display a program's messages in a language other than English. LANGUAGES is an optional space-separated list of codes of the languages that you want supported, for example --enable-nls='de fr'. (The intersection between your list and the set of actually provided translations will be computed automatically.) If you do not specify a list, then all available translations are installed.

To use this option, you will need an implementation of the Gettext API.

--with-perl

Build the PL/Perl server-side language.

--with-python

Build the PL/Python server-side language.

--with-tcl

Build the PL/Tcl server-side language.

--with-tclconfig=DIRECTORY

Tcl installs the file tclConfig.sh, which contains configuration information needed to build modules interfacing to Tcl. This file is normally found automatically at a well-known location, but if you want to use a different version of Tcl you can specify the directory in which to look for tclConfig.sh.

--with-icu

Build with support for the ICU library, enabling use of ICU collation features (see Section 24.2). 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 nonempty strings in order to avoid use of pkg-config, for example, ICU_CFLAGS=' '.)

--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 for in your PATH. If that would not yield the desired program, 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 CXX environment variable).

--with-lz4

Build with LZ4 compression support.

--with-zstd

Build with Zstandard compression support.

--with-ssl=LIBRARY

Build with support for SSL (encrypted) connections. The only LIBRARY supported is openssl. 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-openssl

Obsolete equivalent of --with-ssl=openssl.

--with-gssapi

Build with support for GSSAPI authentication. On many systems, the GSSAPI system (usually a part of the Kerberos installation) 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-ldap

Build with LDAP support for authentication and connection parameter lookup (see Section 34.18 and Section 21.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-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-systemd

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

--with-bonjour

Build with support for Bonjour automatic service discovery. 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 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 libxml2, enabling SQL/XML support. Libxml2 version 2.6.23 or later is required for this feature.

To detect the required compiler and linker options, PostgreSQL will query pkg-config, if that is installed and knows about libxml2. Otherwise the program xml2-config, which is installed by libxml2, will be used if it is found. Use of pkg-config is preferred, because it can deal with multi-architecture installations better.

To use a libxml2 installation that is in an unusual location, you can set pkg-config-related environment variables (see its documentation), or set the environment variable XML2_CONFIG to point to the xml2-config program belonging to the libxml2 installation, or set the variables XML2_CFLAGS and XML2_LIBS. (If pkg-config is installed, then to override its idea of where libxml2 is you must either set XML2_CONFIG or set both XML2_CFLAGS and XML2_LIBS to nonempty strings.)

--with-libxslt

Build with libxslt, enabling the xml2 module to perform XSL transformations of XML. --with-libxml must be specified as well.

17.4.1.3. Anti-Features

The options described in this section allow disabling certain PostgreSQL features that are built by default, but which might need to be turned off if the required software or system features are not available. Using these options is not recommended unless really necessary.

--without-readline

Prevents use of the Readline library (and libedit as well). This option disables command-line editing and history in psql.

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

--without-zlib

Prevents use of the Zlib library. This disables support for compressed archives in pg_dump and pg_restore.

--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 very 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 PostgreSQL developers.

--disable-atomics

Disable use of CPU atomic operations. This option does nothing on platforms that lack such operations. On platforms that do have them, this will result in poor performance. This option is only useful for debugging or making performance comparisons.

--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. Use this only on platforms with deficient threading support.

17.4.1.4. Build Process Details

--with-includes=DIRECTORIES

DIRECTORIES is a colon-separated list of directories that will be added to the list the compiler searches for header files. If you have optional packages (such as GNU Readline) installed in a non-standard location, you have to use this option and probably also the corresponding --with-libraries option.

Example: --with-includes=/opt/gnu/include:/usr/sup/include.

--with-libraries=DIRECTORIES

DIRECTORIES is a colon-separated list of directories to search for libraries. You will probably have to use this option (and the corresponding --with-includes option) if you have packages installed in non-standard locations.

Example: --with-libraries=/opt/gnu/lib:/usr/sup/lib.

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

--with-extra-version=STRING

Append STRING to the PostgreSQL version number. You can use this, for example, to mark binaries built from unreleased Git snapshots or containing custom patches with an extra version string, such as a git describe identifier or a distribution package release number.

--disable-rpath

Do not mark PostgreSQL's executables to indicate that they should search for shared libraries in the installation's library directory (see --libdir). On most platforms, this marking uses an absolute path to the library directory, so that it will be unhelpful if you relocate the installation later. However, you will then need to provide some other way for the executables to find the shared libraries. Typically this requires configuring the operating system's dynamic linker to search the library directory; see Section 17.5.1 for more detail.

17.4.1.5. Miscellaneous

It's fairly common, particularly for test builds, to adjust the default port number with --with-pgport. The other options in this section are recommended only for advanced users.

--with-pgport=NUMBER

Set NUMBER as the default port number for server and clients. The default is 5432. The port can always be changed later on, but if you specify it here then both server and clients will have the same default compiled in, which can be very convenient. Usually the only good reason to select a non-default value is if you intend to run multiple PostgreSQL servers on the same machine.

--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 are building for a Windows environment, in which case it must be set to upper case POSTGRES.

--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 breaks on-disk database compatibility, meaning you cannot use pg_upgrade to upgrade to a build with a different segment size.

--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 breaks on-disk database compatibility, meaning you cannot use pg_upgrade to upgrade to a build with a different block size.

--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 breaks on-disk database compatibility, meaning you cannot use pg_upgrade to upgrade to a build with a different WAL block size.

17.4.1.6. Developer Options

Most of the options in this section are only of interest for developing or debugging PostgreSQL. They are not recommended for production builds, except for --enable-debug, which can be useful to enable detailed bug reports in the unlucky event that you encounter a bug. On platforms supporting DTrace, --enable-dtrace may also be reasonable to use in production.

When building an installation that will be used to develop code inside the server, it is recommended to use at least the options --enable-debug and --enable-cassert.

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

--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-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. See Section 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 containing profile data. This option is for use only with GCC and when doing development work.

--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 your 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". 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' ...

17.4.2. `configure` Environment Variables

In addition to the ordinary command-line options described above, configure responds to a number of environment variables. You can specify environment variables on the configure command line, for example:

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

In this usage an environment variable is little different from a command-line option. You can also set such variables beforehand:

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

This usage can be convenient because many programs' configuration scripts respond to these variables in similar ways.

The most commonly used of these environment variables are CC and CFLAGS. If you prefer a C compiler different from the one configure picks, you can set the 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.

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

CPP

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 program

LDFLAGS

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

Perl interpreter program. This will be used to determine the dependencies for building PL/Perl. The default is perl.

PYTHON

Python interpreter program. This will be used to determine the dependencies for building PL/Python. If this is not set, the following are probed in this order: python3 python.

TCLSH

Tcl interpreter program. This will be used to determine the dependencies for building PL/Tcl. If this is not set, the following are probed in this order: tclsh tcl tclsh8.6 tclsh86 tclsh8.5 tclsh85 tclsh8.4 tclsh84.

XML2_CONFIG

xml2-config program used to locate the libxml2 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 CFLAGS passed 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 of COPT are added to both the CFLAGS and LDFLAGS options set up by configure. For example, you could do

make COPT='-Werror'

export COPT='-Werror'
make

Note

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.

17.5. Post-Installation Setup

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

17.6. Supported Platforms

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.

17.7. 平台相關的注意事項

本節記錄了有關安裝 PostgreSQL 於其他特定平台的問題。請務必閱讀安裝說明，尤其是。另外，也請查閱關於迴歸測試結果的解釋。

此處未涵蓋的平台皆無法預想其可能的安裝問題。

17.7.1. AIX

您可以使用 GCC 或 IBM 內建編譯器 xlc 在 AIX 上編譯 PostgreSQL。

PostgreSQL 社群不再測試也不支援 AIX 7.1 之前的版本。

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

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

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

17.7.2. Cygwin

When building from source, proceed according to the Unix-style 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 variable MAX_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.

17.7.3. macOS

To build PostgreSQL from source on macOS, you will need to install Apple's command line developer tools, which can be done by issuing

(note that this will pop up a GUI dialog window for confirmation). You may or may not wish to also install Xcode.

On recent macOS releases, it's necessary to embed the “sysroot” path in the include switches used to find some system header files. This results in the outputs of the configure script varying depending on which SDK version was used during configure. That shouldn't pose any problem in simple scenarios, but if you are trying to do something like building an extension on a different machine than the server code was built on, you may need to force use of a different sysroot path. To do that, set PG_SYSROOT, for example

To find out the appropriate path on your machine, run

Note that building an extension using a different sysroot version than was used to build the core server is not really recommended; in the worst case it could result in hard-to-debug ABI inconsistencies.

You can also select a non-default sysroot path when configuring, by specifying PG_SYSROOT to configure:

This would primarily be useful to cross-compile for some other macOS version. There is no guarantee that the resulting executables will run on the current host.

To suppress the -isysroot options altogether, use

(any nonexistent pathname will work). This might be useful if you wish to build with a non-Apple compiler, but beware that that case is not tested or supported by the PostgreSQL developers.

macOS's “System Integrity Protection” (SIP) feature breaks make check, because it prevents passing the needed setting of DYLD_LIBRARY_PATH down to the executables being tested. You can work around that by doing make install before make check. Most PostgreSQL developers just turn off SIP, though.

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

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

17.7.5. Solaris

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

17.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. If you are using Sun's compiler, be careful not to select /usr/ucb/cc; use /opt/SUNWspro/bin/cc.

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

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

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. On the other hand, 32-bit code on the AMD64 CPU family is not native, so 32-bit code is significantly slower on that CPU family.

17.7.5.4. 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 to use DTrace.

18. 以原始碼在 Windows 上安裝

It is recommended that most users download the binary distribution for Windows, available as a graphical installer package from the PostgreSQL website at . 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 2022 and use the included compiler. It is also possible to build with the full Microsoft Visual C++ 2013 to 2022. 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.

使用 MinGW 或 Cygwin 的話，請以標準方式建置系統，參閱和的特定說明。要在這些環境中產生原生 64 位元的編輯執行檔，請使用 MinGW-w64 的工具。這些工具還可用於在其他主機（例如 Linux 和 macOS）上交叉編譯 32 位元和 64 位元 Windows 標的。但不建議將 Cygwin 用於運作正式線上伺服器，它應該只用於在無法建置的舊版本 Windows 上。官方預編譯安裝套件是使用 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.

18.1. Building with Visual C++ or the Microsoft Windows SDK

PostgreSQL can be built using the Visual C++ compiler suite from Microsoft. These compilers can be either from Visual Studio, Visual Studio Express or some versions of the Microsoft Windows SDK. If you do not already have a Visual Studio environment set up, the easiest ways are to use the compilers from Visual Studio 2022 or those in the Windows SDK 10, which are both free downloads from Microsoft.

Both 32-bit and 64-bit builds are possible with the Microsoft Compiler suite. 32-bit PostgreSQL builds are possible with Visual Studio 2013 to Visual Studio 2022, as well as standalone Windows SDK releases 8.1a to 10. 64-bit PostgreSQL builds are supported with Microsoft Windows SDK version 8.1a to 10 or Visual Studio 2013 and above. Compilation is supported down to Windows 7 and Windows Server 2008 R2 SP1 when building with Visual Studio 2013 to Visual Studio 2022.

The tools for building using Visual C++ or Platform SDK are in the src\tools\msvc directory. When building, make sure there are no tools from MinGW or Cygwin present in your system PATH. Also, make sure you have all the required Visual C++ tools available in the PATH. In Visual Studio, start the Visual Studio Command Prompt. If you wish to build a 64-bit version, you must use the 64-bit version of the command, and vice versa. Starting with Visual Studio 2017 this can be done from the command line using VsDevCmd.bat, see -help for the available options and their default values. vsvars32.bat is available in Visual Studio 2015 and earlier versions for the same purpose. From the Visual Studio Command Prompt, you can change the targeted CPU architecture, build type, and target OS by using the vcvarsall.bat command, e.g., vcvarsall.bat x64 10.0.10240.0 to target Windows 10 with a 64-bit release build. See -help for the other options of vcvarsall.bat. All commands should be run from the src\tools\msvc directory.

Before you build, you can create the file config.pl to reflect any configuration options you want to change, or the paths to any third party libraries to use. The complete configuration is determined by first reading and parsing the file config_default.pl, and then apply any changes from config.pl. For example, to specify the location of your Python installation, put the following in config.pl:

$config->{python} = 'c:\python310';

You only need to specify those parameters that are different from what's in config_default.pl.

If you need to set any other environment variables, create a file called buildenv.pl and put the required commands there. For example, to add the path for bison when it's not in the PATH, create a file containing:

$ENV{PATH}=$ENV{PATH} . ';c:\some\where\bison\bin';

To pass additional command line arguments to the Visual Studio build command (msbuild or vcbuild):

$ENV{MSBFLAGS}="/m";

18.1.1. Requirements

The following additional products are required to build PostgreSQL. Use the config.pl file to specify which directories the libraries are available in.

Microsoft Windows SDK

If your build environment doesn't ship with a supported version of the Microsoft Windows SDK it is recommended that you upgrade to the latest version (currently version 10), available for download from https://www.microsoft.com/download.

You must always include the Windows Headers and Libraries part of the SDK. If you install a Windows SDK including the Visual C++ Compilers, you don't need Visual Studio to build. Note that as of Version 8.0a the Windows SDK no longer ships with a complete command-line build environment.

ActiveState Perl

ActiveState Perl is required to run the build generation scripts. MinGW or Cygwin Perl will not work. It must also be present in the PATH. Binaries can be downloaded from https://www.activestate.com (Note: version 5.8.3 or later is required, the free Standard Distribution is sufficient).

The following additional products are not required to get started, but are required to build the complete package. Use the config.pl file to specify which directories the libraries are available in.

ActiveState TCL

Required for building PL/Tcl (Note: version 8.4 is required, the free Standard Distribution is sufficient).

Bison and Flex

Bison and Flex are required to build from Git, but not required when building from a release file. Only Bison 1.875 or versions 2.2 and later will work. Flex must be version 2.5.31 or later.

Both Bison and Flex are included in the msys tool suite, available from http://www.mingw.org/wiki/MSYS as part of the MinGW compiler suite.

You will need to add the directory containing flex.exe and bison.exe to the PATH environment variable in buildenv.pl unless they are already in PATH. In the case of MinGW, the directory is the \msys\1.0\bin subdirectory of your MinGW installation directory.

Note

The Bison distribution from GnuWin32 appears to have a bug that causes Bison to malfunction when installed in a directory with spaces in the name, such as the default location on English installations C:\Program Files\GnuWin32. Consider installing into C:\GnuWin32 or use the NTFS short name path to GnuWin32 in your PATH environment setting (e.g., C:\PROGRA~1\GnuWin32).

Diff

Diff is required to run the regression tests, and can be downloaded from http://gnuwin32.sourceforge.net.

Gettext

Gettext is required to build with NLS support, and can be downloaded from http://gnuwin32.sourceforge.net. Note that binaries, dependencies and developer files are all needed.

MIT Kerberos

Required for GSSAPI authentication support. MIT Kerberos can be downloaded from https://web.mit.edu/Kerberos/dist/index.html.

libxml2 and libxslt

Required for XML support. Binaries can be downloaded from https://zlatkovic.com/pub/libxml or source from http://xmlsoft.org. Note that libxml2 requires iconv, which is available from the same download location.

LZ4

Required for supporting LZ4 compression. Binaries and source can be downloaded from https://github.com/lz4/lz4/releases.

Zstandard

Required for supporting Zstandard compression. Binaries and source can be downloaded from https://github.com/facebook/zstd/releases.

OpenSSL

Required for SSL support. Binaries can be downloaded from https://slproweb.com/products/Win32OpenSSL.html or source from https://www.openssl.org.

ossp-uuid

Required for UUID-OSSP support (contrib only). Source can be downloaded from http://www.ossp.org/pkg/lib/uuid/.

Python

Required for building PL/Python. Binaries can be downloaded from https://www.python.org.

zlib

Required for compression support in pg_dump and pg_restore. Binaries can be downloaded from https://www.zlib.net.

18.1.2. Special Considerations for 64-Bit Windows

PostgreSQL will only build for the x64 architecture on 64-bit Windows, there is no support for Itanium processors.

Mixing 32- and 64-bit versions in the same build tree is not supported. The build system will automatically detect if it's running in a 32- or 64-bit environment, and build PostgreSQL accordingly. For this reason, it is important to start the correct command prompt before building.

To use a server-side third party library such as Python or OpenSSL, this library must also be 64-bit. There is no support for loading a 32-bit library in a 64-bit server. Several of the third party libraries that PostgreSQL supports may only be available in 32-bit versions, in which case they cannot be used with 64-bit PostgreSQL.

18.1.3. Building

To build all of PostgreSQL in release configuration (the default), run the command:

build

To build all of PostgreSQL in debug configuration, run the command:

build DEBUG

To build just a single project, for example psql, run the commands:

build psql
build DEBUG psql

To change the default build configuration to debug, put the following in the buildenv.pl file:

$ENV{CONFIG}="Debug";

It is also possible to build from inside the Visual Studio GUI. In this case, you need to run:

perl mkvcbuild.pl

from the command prompt, and then open the generated pgsql.sln (in the root directory of the source tree) in Visual Studio.

18.1.4. Cleaning and Installing

Most of the time, the automatic dependency tracking in Visual Studio will handle changed files. But if there have been large changes, you may need to clean the installation. To do this, simply run the clean.bat command, which will automatically clean out all generated files. You can also run it with the dist parameter, in which case it will behave like make distclean and remove the flex/bison output files as well.

By default, all files are written into a subdirectory of the debug or release directories. To install these files using the standard layout, and also generate the files required to initialize and use the database, run the command:

install c:\destination\directory

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

install c:\destination\directory client

18.1.5. Running the Regression Tests

To run the regression tests, make sure you have completed the build of all required parts first. Also, make sure that the DLLs required to load all parts of the system (such as the Perl and Python DLLs for the procedural languages) are present in the system path. If they are not, set it through the buildenv.pl file. To run the tests, run one of the following commands from the src\tools\msvc directory:

vcregress check
vcregress installcheck
vcregress plcheck
vcregress contribcheck
vcregress modulescheck
vcregress ecpgcheck
vcregress isolationcheck
vcregress bincheck
vcregress recoverycheck

To change the schedule used (default is parallel), append it to the command line like:

vcregress check serial

For more information about the regression tests, see Chapter 33.

Running the regression tests on client programs, with vcregress bincheck, or on recovery tests, with vcregress recoverycheck, requires an additional Perl module to be installed:

IPC::Run

As of this writing, IPC::Run is not included in the ActiveState Perl installation, nor in the ActiveState Perl Package Manager (PPM) library. To install, download the IPC-Run-<version>.tar.gz source archive from CPAN, at https://metacpan.org/release/IPC-Run, and uncompress. Edit the buildenv.pl file, and add a PERL5LIB variable to point to the lib subdirectory from the extracted archive. For example:

$ENV{PERL5LIB}=$ENV{PERL5LIB} . ';c:\IPC-Run-0.94\lib';

The TAP tests run with vcregress support the environment variables PROVE_TESTS, that is expanded automatically using the name patterns given, and PROVE_FLAGS. These can be set on a Windows terminal, before running vcregress:

set PROVE_FLAGS=--timer --jobs 2
set PROVE_TESTS=t/020*.pl t/010*.pl

It is also possible to set up those parameters in buildenv.pl:

$ENV{PROVE_FLAGS}='--timer --jobs 2'
$ENV{PROVE_TESTS}='t/020*.pl t/010*.pl'

Some of the TAP tests depend on a set of external commands that would optionally trigger tests related to them. Each one of those variables can be set or unset in buildenv.pl:

GZIP_PROGRAM

Path to a gzip command. The default is gzip, which will search for a command by that name in the configured PATH.

LZ4

Path to a lz4 command. The default is lz4, which will search for a command by that name in the configured PATH.

TAR

Path to a tar command. The default is tar, which will search for a command by that name in the configured PATH.

ZSTD

Path to a zstd command. The default is zstd, which will search for a command by that name in the configured PATH.

19. 服務配置與維運

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

19.1. PostgreSQL 使用者帳號

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

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

19.2. Creating a Database Cluster

在您可以做任何事情之前，您必須在磁碟中初始化一個資料庫儲存區域。我們稱之為數據庫叢集(Database Cluster，SQL 標準術語為 Catalog Cluster）。資料庫叢集是由正在運行的資料庫伺服器的單一個執行實例管理的資料庫集合。初始化後，資料庫叢集將包含一個名為 postgres 的資料庫，這是供工具程式、資料庫使用者和第三方應用程式所預設的資料庫。資料庫伺服器本身不需要 postgres 資料庫存在，但許多外部工具會假設它存在。初始化期間在每個叢集中所建置的另一個資料庫稱為 template1。顧名思義，這將作為後續建立的資料庫的樣板；它不應該用於實際的資料作業。（有關在叢集中建立新資料庫的說明，請參閱。）

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:

如果您使用 pg_ctl 來啟動和停止伺服器（請參閱），這相當直覺，因此 pg_ctl 將是您用於管理資料庫伺服器實例的唯一命令。

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 scram-sha-256 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.

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

19.2.2. File Systems

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

19.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 的某些特質，不過，管理遠端儲存服務的複雜性會仍發生在其他層級之中。

19.3. Starting the Database Server

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:

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:

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 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 reference page and below.

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

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:

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:
On Linux systems either add
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):
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:
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.

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:

A message like:

An error like:

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.

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.

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:

19.4. 核心資源管理

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

Name

Description

Values needed to run one PostgreSQL instance

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.

19.5. Shutting Down the Server

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

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 ) or send a SIGTERM signal to the child process associated with the session.

19.6. Upgrading a PostgreSQL Cluster

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.

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:
```
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.
Shut down the old server:
```
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:
```
/etc/rc.d/init.d/postgresql stop
```
See Chapter 18 for details about starting and stopping the server.
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:
```
mv /usr/local/pgsql /usr/local/pgsql.old
```
(Be sure to move the directory as a single unit so relative paths remain unchanged.)
Install the new version of PostgreSQL as outlined in Section 16.4.
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).
```
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
```
Restore your previous pg_hba.conf and any postgresql.conf modifications.
Start the database server, again using the special database user account:
```
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
```
Finally, restore your data from backup with:
```
/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.\

19.7. Preventing Server Spoofing

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 () 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 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 () and have SSL key and certificate files (). The TCP client must connect using sslmode=verify-ca or verify-full and have the appropriate root certificate file installed ().

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

19.9. Secure TCP/IP Connections with SSL

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

18.9.1. Basic Setup

With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter 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 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 and .

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

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

Then do:

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:

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

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

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:

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.10. Secure TCP/IP Connections with GSSAPI Encryption

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

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

20.2. File Locations

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 21). 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 21.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 in postgresql.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.

21.6. GSSAPI Authentication

GSSAPI is an industry-standard protocol for secure authentication defined in . PostgreSQL supports GSSAPI for authentication, communications encryption, or both. GSSAPI provides automatic authentication (single sign-on) for systems that support it. The authentication itself is secure. If GSSAPI encryption or SSL encryption is used, the data sent along the database connection will be encrypted; otherwise, it will not.

GSSAPI support has to be enabled when PostgreSQL is built; see for more information.

When GSSAPI uses Kerberos, it uses a standard service principal (authentication identity) name in the format servicename/hostname@realm. The principal name used by a particular installation is not encoded in the PostgreSQL server in any way; rather it is specified in the keytab file that the server reads to determine its identity. If multiple principals are listed in the keytab file, the server will accept any one of them. The server's realm name is the preferred realm specified in the Kerberos configuration file(s) accessible to the server.

When connecting, the client must know the principal name of the server it intends to connect to. The servicename part of the principal is ordinarily postgres, but another value can be selected via libpq's connection parameter. The hostname part is the fully qualified host name that libpq is told to connect to. The realm name is the preferred realm specified in the Kerberos configuration file(s) accessible to the client.

The client will also have a principal name for its own identity (and it must have a valid ticket for this principal). To use GSSAPI for authentication, the client principal must be associated with a PostgreSQL database user name. The pg_ident.conf configuration file can be used to map principals to user names; 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 mapping client principals to user names by just stripping 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 location of the server's keytab file is specified by the configuration parameter. For security reasons, it is recommended to use a separate keytab just for the PostgreSQL server rather than allowing the server to read the system keytab file. Make sure that your server keytab file is readable (and preferably only readable, not writable) by the PostgreSQL server account. (See also .)

The keytab file is generated using the Kerberos software; see the Kerberos documentation for details. The following example shows doing this using the kadmin tool of MIT-compatible Kerberos 5 implementations:

The following authentication options are supported for the GSSAPI authentication method:

include_realm

If set to 0, the realm name from the authenticated user principal is stripped off before being passed through the user name mapping (). This is discouraged and is primarily available for backwards compatibility, as it is not secure in multi-realm environments unless krb_realm is also used. It is recommended to leave include_realm set to the default (1) and to provide an explicit mapping in pg_ident.conf to convert principal names to PostgreSQL user names.

map

Allows mapping from client principals to database user names. See for details. For a GSSAPI/Kerberos principal, such as username@EXAMPLE.COM (or, less commonly, username/hostbased@EXAMPLE.COM), the user name used for mapping is username@EXAMPLE.COM (or username/hostbased@EXAMPLE.COM, respectively), unless include_realm has been set to 0, in which case username (or username/hostbased) is what is seen as the system user name when mapping.

krb_realm

Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever user name mapping is done.

In addition to these settings, which can be different for different pg_hba.conf entries, there is the server-wide configuration parameter. If that is set to true, client principals are matched to user map entries case-insensitively. krb_realm, if set, is also matched case-insensitively.

25.1. 例行性資料清理

PostgreSQL 資料庫需要定期維護，稱為資料庫清理(vacuum)。對於一裝的執行環境而言，透過 autovacuum 背景程序進行資料庫清理就足夠了，這在中有描述。您可能需要調整其中所描述的自動清除參數，以獲得您的情況的最佳結果。一些資料庫管理員希望用手動管理的 VACUUM 命令來補充或替換背景程序的活動，這些命令通常根據 cron 或 Task Scheduler 的腳本計劃執行。要正確設定手動管理的資料庫清理，了解接下來幾小節中討論的問題至關重要。依靠自動清理的管理員可能仍然希望瀏覽這些內容以幫助他們理解和調整自動清理。

25.1.1. 資料庫清理的基本概念

必須以 PostgreSQL 命令處理每個資料表，原因如下：

恢復或回收使用因更新或刪除資料列所佔用的磁碟空間。
更新 PostgreSQL 查詢計劃器使用的資料統計資訊。
更新可視性結構，這會增加的效率。
防止由於事務 ID 重覆或 multixact ID 重覆而失去非常舊的資料。

這些原因中的每一個都會要求執行不同頻率和範圍的 VACUUM 操作，如以下小節所述。

VACUUM 有兩種執行方式：標準 VACUUM 和 VACUUM FULL。VACUUM FULL 可以回收更多磁碟空間，但執行速度要慢得多。而且，VACUUM 的標準形式可以與線上資料庫同時運作。（SELECT、INSERT、UPDATE 和 DELETE 等指令將繼續正常工作，但在 VACUUM FULL 時，您將無法使用諸如 ALTER TABLE 之類的指令修改資料表的定義。）VACUUM FULL 需要獨占鎖定它正在處理的資料表，因此無法與其他資料表的使用同時進行。因此，一般來說，管理員應該努力使用一般的 VACUUM 而避免進行 VACUUM FULL。

VACUUM 會產生大量的 I/O流量，這會導致其他正在進行的連線效能較差。有一些配置參數可以調整以減少背景資料庫清理對效能的影響 - 參閱。

25.1.2. 回收磁碟空間

在 PostgreSQL 中，資料列的 UPDATE 或 DELETE 不會立即刪除該資料列的舊版本。這種方法對於獲得多版本平行控制（MVCC，參閱）的好處是必要的：資料列的版本不能被刪除，而其他事務仍然可以看到。但最終，過時或刪除的資料列版本不再讓任何交易感興趣。它佔用的空間必須被新的資料列重新使用以避免無限增長的磁碟空間需求。這就是透過執行 VACUUM 來完成的。

VACUUM 的標準作法是移除資料表和索引中過時的資料列版本，並標記可供將來重複使用的空間。但是，除非資料表末端的一個或多個頁面變為完全空閒並且可以輕鬆獲取排他資料表鎖定的特殊情況，否則它不會將空間還給作業系統。相比之下，VACUUM FULL 透過寫入完整新版本使其沒有空閒的空間來主動壓縮資料表。這最大限度地減少了資料表的大小，但可能需要很長時間。它還需要用於資料表新副本的額外磁碟空間，直到操作完成。

常態的資料庫清理通常目標是經常足夠地執行標準 VACUUM 以避免需要 VACUUM FULL。autovacuum 背景程序嘗試以這種方式工作，實際上永遠不會發出 VACUUM FULL。在這種方法中，這個想法並不是將資料表保持在最小尺寸，而是為了保持磁碟空間的穩定狀態使用：每個資料表都佔用相當於其最小尺寸的空間，再加上在 VACUUM 之間使用的空間很大，儘管可以使用 VACUUM FULL 將表縮回到最小大小並將磁碟空間還回到作業系統，但如果資料表將來會再次增長，則沒有多大意義。因此，適度頻繁的標準 VACUUM 運行比用於維護大量更新資料表的罕見 VACUUM FULL 運行更好。

有些管理者更喜歡自己安排資料庫清理作業，例如在負載較低時在夜間進行所有工作。按照固定的時間表進行資料庫清理作業的困難在於，如果資料表在更新活動中出現意外的峰值，則可能會變得臃腫到 VACUUM FULL 真的需要回收空間。使用自動清理背景程序緩解了這個問題，因為背景程序會根據更新活動動態調度清理作業。除非您有一個非常可預測的工作量，否則完全停用該背景程序是不明智的。一個可能的折衷辦法是設定背景程序的參數，以便它僅對異常繁重的更新活動作出反應，從而避免事情失控，而預定的 VACUUM 參數是能在典型的情況下完成大部分工作。

25.1.3. 更新規劃器統計資訊

autovacuum 背景程序（如果啟用的話）會在資料表內容發生相當的變化時自動發出 ANALYZE 指令。但是，管理員可能更喜歡依靠手動調度的 ANALYZE 操作，尤其是如果知道資料表上的更新活動不會影響「有興趣的」欄位的統計信息。背景程序嚴格按照插入或更新的資料列數的安排 ANALYZE；不過它並不知道這是否會導致有意義的統計變化。

與資料清理恢復空間一樣，頻繁更新統計數據對於大量更新的資料表比對很少更新的資料表更有用。但即使對於大量更新的資料表，如果資料的統計分佈變化不大，也可能不需要進行統計更新。一個簡單的經驗法則是考慮資料表中欄位的最小值和最大值的變化。例如，包含行更新時間的 timestamp 欄在插入和更新資料列時會不斷增加最大值；這樣的欄位可能需要更頻繁的統計更新，而不是包含網頁內容的網址欄位。URL 欄位可能會經常收到更新，但其內容的統計分佈可能變化比較慢。

可以在特定的資料表上執行 ANALYZE，甚至可以在資料表中特定的欄位上執行ANALYZE，因此如果應用程序需要，可以更靈活地更新某些統計資訊。然而，在實務上，通常最好僅分析整個資料庫，因為這是一種快速操作。ANALYZE 以資料表中資料列的隨機抽樣而不是讀取每一個資料列。

此外，預設情況下，有關 SELECT 函數的訊息有限。但是，如果建立使用函數呼叫的表示式索引，則會收集有關該函數的有用統計訊息，這可以極大地改進使用表示式索引的查詢計劃。

autovacuum 背景程序不會為外部資料表發出 ANALYZE 指令，因為它無法確定可能有用的頻率。如果您的查詢需要統計外部資料表的正確計劃，最好在適當的時間表上執行手動管理的 ANALYZE 指令。

25.1.4. 更新可見性映射表（Visibility Map）

25.1.5. 防止交易事務 ID 重覆

定期清理能解決問題的原因是 VACUUM 會將資料列標記為凍結，表明它們是由過去的事務插入的，以至於插入事務的影響肯定對所有目前和未來的事務都可見。使用 modulo-232 運算比較普通 XID。這意味著對於每個普通的 XID，有20億個「較舊」的 XID 和 20 個「較新」的 XID；另一種說法是普通的 XID 空間是圓形的，沒有端點。因此，一旦使用特定的普通 XID 建立了資料列版本，無論我們在談論哪種正常的 XID，資料列版本對於接下來的 20 億次交易看起來都是“過去的”。如果資料列版本在超過 20 億次交易後仍然存在，那麼它將來會突然出現。為了防止這種情況，PostgreSQL 保留了一個特殊的 XID，FrozenTransactionId，它不遵循正常的 XID 比較規則，並且總是被認為比每個普通的 XID 都舊。凍結資料列版本被視為插入 XID 是 FrozenTransactionId，因此它們對於所有正常事務而言似乎都是「過去」而不管繞回重覆的問題，因此這些資料列版本在刪除之前有效，無論多長時間都是。

在 9.4 之前的 PostgreSQL 版本中，透過實際用 FrozenTransactionId 替換資料列的插入 XID 來實現凍結，這在資料列的 xmin 系統欄位中是可見的。較新版本只設置一個指標，保留資料列的原始 xmin 以便進行可能的查證使用。但是，仍然可以在 9.4 之前版本的資料庫 pg_upgrade 中找到 xmin 等於 FrozenTransactionId（2）的資料列。

此外，系統目錄可能包含 xmin 等於 BootstrapTransactionId(1) 的資料列，表示它們是在 initdb 的第一階段插入的。與 FrozenTransactionId 一樣，此特殊 XID 被視為比每個普通 XID 更舊。

這意味著如果資料表沒有以其他方式進行清理，則每次 autovacuum_freeze_max_age 減去 vacuum_freeze_min_age 的事務數量時，將在其上執行 autovacuum。對於經常用於空間回收目的而被清理的資料表，這一點並不重要。但是，對於靜態資料表（包括接收插入但沒有更新或刪除的資料表），不需要清理進行空間回收，因此嘗試最大化非常大的靜態資料表上強制自動清理之間的間隔會很有用。顯然，可以透過增加 autovacuum_freeze_max_age 或減少 vacuum_freeze_min_age 來達到此目的。

vacuum_freeze_table_age 的有效最大值為 0.95 * autovacuum_freeze_max_age；高於此值的設定將被限制為最大值。高於 autovacuum_freeze_max_age 的值是沒有意義的，因為無論如何都會在該點觸發n防止交易重疊的自動清理，並且 0.95 乘數在此之前留下一些喘息空間來執行手動 VACUUM。根據經驗，vacuum_freeze_table_age 應設定為略低於 autovacuum_freeze_max_age 的值，留下足夠的間隙，以便在該間隙中執行由日常刪除和更新活動觸發定期的 VACUUM 或 autovacuum。將它設定得太近可能會導致防止交易重疊的自動清理，即使該資料表最近被清理以回收空間，而較低的值還是會導致更頻繁的積極清理。

增加 autovacuum_freeze_max_age（以及 vacuum_freeze_table_age）的唯一缺點是資料庫叢集的 pg_xact 和 pg_commit_ts 子目錄將佔用更多空間，因為它必須儲存提交狀態和（如果啟用了 track_commit_timestamp）所有事務的時間戳記回到 autovacuum_freeze_max_age horizon。提交狀態每個交易事務使用兩個位元，因此如果 autovacuum_freeze_max_age 設定為其最大允許值 20 億，則 pg_xact 可以增長到大約 0.5 GB，pg_commit_ts 可以增長到大約 20 GB，這與總資料庫大小相比這是微不足道的。建議將 autovacuum_freeze_max_age 設定為其最大允許值。否則，根據您願意允許 pg_xact 和 pg_commit_ts 儲存的內容進行設定。（一般情況下，2 億次交易，轉換為大約 50 MB 的 pg_xact 儲存空間和大約 2 GB 的pg_commit_ts 儲存空間。）

減少 vacuum_freeze_min_age 的一個缺點是它可能導致 VACUUM 進行無謂的工作：如果此後很快更新資料列（導致它獲取新的 XID），凍結資料列版本會浪費時間。因此，設定應該足夠大，以至於資料列不會被凍結，直到它們不再可能更新為止。

為了追踪資料庫中最早解凍的 XID 的值，VACUUM 將 XID 統計訊息儲存在系統資料表 pg_class 和 pg_database 中。特別是，資料表 pg_class 的 relfrozenxid 欄位包含該資料表的最後一個積極 VACUUM 使用的凍結截止 XID。由 XID 早於此截止 XID 的事務插入，則所有資料列都保證已被凍結。同理，資料庫的 pg_database 的 datfrozenxid 欄位是該資料庫中出現的未凍結 XID 的下限 - 它只是資料庫中每個資料表 relfrozenxid 的最小值。檢查此訊息的便捷方法是執行以下查詢：

age 欄位測量從截止 XID 到目前事務的 XID 的事務數。

VACUUM 通常僅掃描自上次清理以來已修改的頁面，但只有在掃描可能包含未凍結 XID 資料表的每個頁面時才能提升 relfrozenxid。當 relfrozenxid 超過 vacuum_freeze_table_agetransactions 時，或當使用 VACUUM 的 FREEZE 選項時，又或當所有尚未全部凍結的頁面碰巧需要清理以刪除過期資料列版本時，才會發生這種情況。當 VACUUM 掃描資料表中尚未全部凍結的每個頁面時，應將 age（relfrozenxid）設定為比 vacuum_freeze_min_age 設定略多一點的值（更多是自 VACUUM 啟動以來啟動的事務數量）。如果在達到 autovacuum_freeze_max_age 之前沒有在資料表上發出 relfrozenxid-advance 的 VACUUM，則很快將強制執行該資料表的 autovacuum。

如果由於某種原因 autovacuum 無法從資料表中清除舊的 XID，當資料庫最舊的 XID 從重疊點到達一千萬個事務時，系統將開始發出這樣的警告消息：

（應該按照提示的建議進行手動 VACUUM 解決問題；但請注意，VACUUM 必須由超級使用者執行，否則它將無法處理系統目錄，就無法推進資料庫的 datfrozenxid。）這些警告如果被忽略，系統將關閉並拒絕啟動任何新的事務，一旦剩下的事務 XID 在重疊前少於 100 萬：

25.1.5.1. Multixacts and Wraparound

Multixact ID 用於支援多個事務的資料列鎖定。由於 tuple 標頭中只有有限的空間來儲存鎖定訊息，因此只要有多個事務同時鎖定一個資料列，該訊息就會被編碼為“multiple transaction ID”或簡稱 Multixact ID。有關哪些事務 ID 包含在任何特定 multixact ID 中的訊息將單獨儲存在 pg_multixact 目錄中，並且只有 multixact ID 出現在 tuple 標頭中的 xmax 字串中。與事務 ID 一樣，multixact ID 實作為 32 位元計數器和相對應的儲存，所有這些都需要仔細的存續管理，儲存清理和環繞處理。有一個單獨的儲存區域，用於保存每個 multixact 中的成員列表，該列表也使用 32 位元計數器，必須進行管理。

無論是什麼原因導致積極的 VACUUM 掃描都能夠提升該資料表的值。最終，由於掃描了所有資料庫中的所有資料表並提升了其最舊的 multixact 值，因此可以移除舊的 multixacts 的磁碟儲存。

25.1.6. Autovacuum 背景程序

其 relfrozenxid 值大於 autovacuum_freeze_max_age 事務舊的資料表總是被清理（這也適用於那些已通過儲存參數修改了凍結最大年齡的資料表；請參閱下文）。否則，如果自上一個 VACUUM 以來廢棄的 tuple 數超過“清理閾值（vacuum threshold）”，則對該資料表進行清理。清理閾值的定義為：

對於分析，使用類似的條件：此閾值定義為：

與自上次 ANALYZE 以來插入、更新或刪除的 tuple 總數進行比較。

autovacuum 無法存取臨時資料表。因此，應透過直接執行 SQL 指令進行適當的清理和分析操作。

17.4. 安裝流程

Configuration
The first step of the installation procedure is to configure the source tree for your system and choose the options you would like. This is done by running the configure script. For a default installation simply enter:
```
./configure
```
This script will run a number of tests to determine values for various system dependent variables and detect any quirks of your operating system, and finally will create several files in the build tree to record what it found.
You can also run configure in a directory outside the source tree, and then build there, if you want to keep the build directory separate from the original source files. This procedure is called a VPATH build. Here's how:
```
mkdir build_dir
cd build_dir
/path/to/source/tree/configure [options go here]
make
```
The default configuration will build the server and utilities, as well as all client applications and interfaces that require only a C compiler. All files will be installed under /usr/local/pgsql by default.
You can customize the build and installation process by supplying one or more command line options to configure. Typically you would customize the install location, or the set of optional features that are built. configure has a large number of options, which are described in Section 17.4.1.
Also, configure responds to certain environment variables, as described in Section 17.4.2. These provide additional ways to customize the configuration.
Build
To start the build, type either of:
```
make
make all
```
(Remember to use GNU make.) The build will take a few minutes depending on your hardware.
If you want to build everything that can be built, including the documentation (HTML and man pages), and the additional modules (contrib), type instead:
```
make world
```
If you want to build everything that can be built, including the additional modules (contrib), but without the documentation, type instead:
```
make world-bin
```
If you want to invoke the build from another makefile rather than manually, you must unset MAKELEVEL or set it to zero, for instance like this:
```
build-postgresql:
        $(MAKE) -C postgresql MAKELEVEL=0 all
```
Failure to do that can lead to strange error messages, typically about missing header files.
Regression Tests
If you want to test the newly built server before you install it, you can run the regression tests at this point. The regression tests are a test suite to verify that PostgreSQL runs on your machine in the way the developers expected it to. Type:
```
make check
```
(This won't work as root; do it as an unprivileged user.) See Chapter 33 for detailed information about interpreting the test results. You can repeat this test at any later time by issuing the same command.
Installing the Files
Note
If you are upgrading an existing system be sure to read Section 19.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.
If you built the world without the documentation above, type instead:
```
make install-world-bin
```
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.
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.

17.4.1. `configure` Options

17.4.1.1. Installation Locations

--prefix=PREFIX

--exec-prefix=EXEC-PREFIX

--bindir=DIRECTORY

Specifies the directory for executable programs. The default is EXEC-PREFIX/bin, which normally means /usr/local/pgsql/bin.

--sysconfdir=DIRECTORY

Sets the directory for various configuration files, PREFIX/etc by default.

--libdir=DIRECTORY

Sets the location to install libraries and dynamically loadable modules. The default is EXEC-PREFIX/lib.

--includedir=DIRECTORY

Sets the directory for installing C and C++ header files. The default is PREFIX/include.

--datarootdir=DIRECTORY

Sets the root directory for various types of read-only data files. This only sets the default for some of the following options. The default is PREFIX/share.

--datadir=DIRECTORY

Sets the directory for read-only data files used by the installed programs. The default is DATAROOTDIR. Note that this has nothing to do with where your database files will be placed.

--localedir=DIRECTORY

Sets the directory for installing locale data, in particular message translation catalog files. The default is DATAROOTDIR/locale.

--mandir=DIRECTORY

The man pages that come with PostgreSQL will be installed under this directory, in their respective manx subdirectories. The default is DATAROOTDIR/man.

--docdir=DIRECTORY

--htmldir=DIRECTORY

The HTML-formatted documentation for PostgreSQL will be installed under this directory. The default is DATAROOTDIR.

Note

17.4.1.2. PostgreSQL Features

--enable-nls[=LANGUAGES]

To use this option, you will need an implementation of the Gettext API.

--with-perl

Build the PL/Perl server-side language.

--with-python

Build the PL/Python server-side language.

--with-tcl

Build the PL/Tcl server-side language.

--with-tclconfig=DIRECTORY

--with-icu

./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 nonempty strings in order to avoid use of pkg-config, for example, ICU_CFLAGS=' '.)

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

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

--with-lz4

Build with LZ4 compression support.

--with-zstd

Build with Zstandard compression support.

--with-ssl=LIBRARY

--with-openssl

Obsolete equivalent of --with-ssl=openssl.

--with-gssapi

--with-ldap

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

--with-bonjour

Build with support for Bonjour automatic service discovery. 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 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 libxml2, enabling SQL/XML support. Libxml2 version 2.6.23 or later is required for this feature.

--with-libxslt

Build with libxslt, enabling the xml2 module to perform XSL transformations of XML. --with-libxml must be specified as well.

17.4.1.3. Anti-Features

--without-readline

Prevents use of the Readline library (and libedit as well). This option disables command-line editing and history in psql.

--with-libedit-preferred

--without-zlib

Prevents use of the Zlib library. This disables support for compressed archives in pg_dump and pg_restore.

--disable-spinlocks

--disable-atomics

--disable-thread-safety

17.4.1.4. Build Process Details

--with-includes=DIRECTORIES

Example: --with-includes=/opt/gnu/include:/usr/sup/include.

--with-libraries=DIRECTORIES

Example: --with-libraries=/opt/gnu/lib:/usr/sup/lib.

--with-system-tzdata=DIRECTORY

--with-extra-version=STRING

--disable-rpath

17.4.1.5. Miscellaneous

It's fairly common, particularly for test builds, to adjust the default port number with --with-pgport. The other options in this section are recommended only for advanced users.

--with-pgport=NUMBER

--with-krb-srvnam=NAME

--with-segsize=SEGSIZE

--with-blocksize=BLOCKSIZE

--with-wal-blocksize=BLOCKSIZE

17.4.1.6. Developer Options

When building an installation that will be used to develop code inside the server, it is recommended to use at least the options --enable-debug and --enable-cassert.

--enable-debug

--enable-cassert

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

--enable-depend

--enable-coverage

--enable-profiling

--enable-dtrace

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

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

Using Sun's compiler:

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

17.4.2. `configure` Environment Variables

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

In this usage an environment variable is little different from a command-line option. You can also set such variables beforehand:

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

This usage can be convenient because many programs' configuration scripts respond to these variables in similar ways.

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

CPP

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 program

LDFLAGS

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

Perl interpreter program. This will be used to determine the dependencies for building PL/Perl. The default is perl.

PYTHON

Python interpreter program. This will be used to determine the dependencies for building PL/Python. If this is not set, the following are probed in this order: python3 python.

TCLSH

XML2_CONFIG

xml2-config program used to locate the libxml2 installation

make COPT='-Werror'

export COPT='-Werror'
make

Note

20.5. Write Ahead Log

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

指定檢查點完成的目標，佔檢查點之間總時間的一部分。預設值為 0.5。此參數只能在 postgresql.conf 檔案或伺服器命令列中設定。

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.

27.2. 日誌轉送備用伺服器 Log-Shipping Standby Servers

持續性歸檔可用於建構高可用性（HA）的叢集配置，其中一個或多個備用伺服器準備好在主伺服器發生故障時接管操作。此功能被廣泛稱為熱備份（warm standby）或日誌轉送(Log-Shipping)。

伺服器們是人為的相依，由主伺服器和備用伺服器協同工作以提供此功能。主伺服器以持續性歸檔模式運行，而每個備用伺服器以連續恢復模式運行，從主伺服器讀取 WAL 檔案。毌須更改資料庫的資料表即可啟用此功能，因此與其他一些複寫解決方案相比，它可以提供較低的管理成本。此配置對主伺服器的效能影響也相對較低。

直接將 WAL 記錄從一個資料庫伺服器移動到另一個資料庫伺服器通常被稱為日誌轉送。PostgreSQL 透過一次傳輸 WAL 記錄一個檔案（WAL 段落）來實現基於檔案的日誌轉送。WAL 檔案（16MB）可以在任何距離上輕鬆便宜地運輸，無論是相鄰系統，同一站點的另一個系統，還是地球另一端的其他系統。此技術所需的頻寬依主伺服器的事務速率而變化。基於記錄的日誌傳送更精細，並且通過網路連連逐步更改 WAL（請參閱第 26.2.5 節）。

應該注意的是，日誌輸送是非同步的，即 WAL 記錄在事務提交之後被傳送。因此，如果主伺服器遭受災難性故障，則存在資料遺失的可能性；尚未提交的交易將會失去。基於檔案的日誌轉送中的資料遺失的大小可以透過使用 archive_timeout 參數來限制，該參數可以設定低至數秒鐘。然而，這種低的設定將大大增加檔案傳送所需的頻寬。串流複寫（參閱第 26.2.5 節）允許更小的資料遺失大小。

回復的效率很高，一旦備用轉為主要，備用資料庫通常只需要幾分鐘即可完全可用。因此，這稱為熱備用配置，可提供高可用性。從歸檔的基本備份和回溯還原伺服器將花費相當長的時間，因此該技術僅提供災難恢復的解決方案，而不是高可用性。備用伺服器也可用於唯讀查詢，在這種情況下，它稱為熱備份伺服器。有關更多訊息，請參閱第 26.5 節。

26.2.1. 規畫

建立主伺服器和備用伺服器通常是好的規畫，使它們可以盡可能相似，至少從資料庫伺服器的角度來看。特別是，與資料表空間關聯的路徑名稱將在未修改的情況下傳遞。因此，如果使用此功能，主伺服器和備用伺服器必須具有相同的資料表空間的安裝路徑。請記住，如果在主伺服器上執行 CREATE TABLESPACE，則必須在執行命令之前在主伺服器和所有備用伺服器上建立所需的所有新安裝點。硬體不需要完全相同，但經驗上，維護兩個相同的系統會比在應用系統的生命週期內維護兩個不同的系統更容易。不過在硬體架構則必須相同 - 例如，從 32 位元到 64 位元系統的搭配則無法運作。

一般來說，無法在不同主要 PostgreSQL 版本的伺服器之間進行日誌傳送。PostgreSQL 全球開發團隊的原則是不要在次要版本升級期間更改磁碟格式，因此在主伺服器和備用伺服器上使用不同的次要版本可能會成功執行。但是，並沒有保證正式支持，建議您盡可能將主伺服器和備用伺服器保持在同一版本。更新到新的次要版本時，最安全的策略是先更新備用伺服器 - 新的次要版本更有可能從先前的次要版本讀取 WAL 檔案，反過來則不一定。

26.2.2. 備用伺服器作業

在備用模式下，伺服器連續套用從主要伺服器所接收的 WAL。備用伺服器可以透過 TCP 連線（串流複寫）從 WAL 歸檔（請參閱 restore_command）。備用伺服器也會嘗試恢復在備用集群的 pg_wal 目錄中能找到的任何 WAL。這通常發生在伺服器重新啟動之後，當備用資料庫再次重新執行在重新啟動之前從主服務器串流傳輸的 WAL 時，您也可以隨時手動將檔案複製到 pg_wal 以重新執行它們。

在啟動時，備用資料庫首先恢復存檔路徑中的所有可用的 WAL，然後呼叫 restore_command。一旦達到 WAL 可用的尾端並且 restore_command 失敗，它就會嘗試恢復 pg_wal 目錄中可用的任何WAL。如果失敗，並且已啟用串流複寫，則備用資料庫會嘗試連到主伺服器，並從 archive 或 pg_wal 中找到的最後一個有效記錄開始串流傳輸 WAL。如果失敗或未啟用串流複寫，或者稍後中斷連線，則備用資料庫將返回步驟 1 並嘗試再次從存檔中還原交易。pg_wal 和串流複寫的重試循環一直持續到伺服器停止或觸發故障轉移為止。

退出備用模式，當執行 pg_ctl promote 或找到觸發器檔案（trigger_file）時，伺服器將切換到正常操作。在故障轉移之前，將恢復存檔或 pg_wal 中立即可用的 WAL，但不會嘗試連線到主要伺服器。

26.2.3. Preparing the Master for Standby Servers

Set up continuous archiving on the primary to an archive directory accessible from the standby, as described in Section 25.3. The archive location should be accessible from the standby even when the master is down, i.e. it should reside on the standby server itself or another trusted server, not on the master server.

If you want to use streaming replication, set up authentication on the primary server to allow replication connections from the standby server(s); that is, create a role and provide a suitable entry or entries in pg_hba.conf with the database field set to replication. Also ensure max_wal_senders is set to a sufficiently large value in the configuration file of the primary server. If replication slots will be used, ensure that max_replication_slots is set sufficiently high as well.

Take a base backup as described in Section 25.3.2 to bootstrap the standby server.

26.2.4. Setting Up a Standby Server

To set up the standby server, restore the base backup taken from primary server (see Section 25.3.4). Create a recovery command file recovery.conf in the standby's cluster data directory, and turn on standby_mode. Set restore_command to a simple command to copy files from the WAL archive. If you plan to have multiple standby servers for high availability purposes, set recovery_target_timeline to latest, to make the standby server follow the timeline change that occurs at failover to another standby.

Note

Do not use pg_standby or similar tools with the built-in standby mode described here. restore_command should return immediately if the file does not exist; the server will retry the command again if necessary. See Section 26.4 for using tools like pg_standby.

If you want to use streaming replication, fill in primary_conninfo with a libpq connection string, including the host name (or IP address) and any additional details needed to connect to the primary server. If the primary needs a password for authentication, the password needs to be specified in primary_conninfo as well.

If you're setting up the standby server for high availability purposes, set up WAL archiving, connections and authentication like the primary server, because the standby server will work as a primary server after failover.

If you're using a WAL archive, its size can be minimized using the archive_cleanup_command parameter to remove files that are no longer required by the standby server. The pg_archivecleanup utility is designed specifically to be used with archive_cleanup_command in typical single-standby configurations, see pg_archivecleanup. Note however, that if you're using the archive for backup purposes, you need to retain files needed to recover from at least the latest base backup, even if they're no longer needed by the standby.

A simple example of a recovery.conf is:

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
restore_command = 'cp /path/to/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'

You can have any number of standby servers, but if you use streaming replication, make sure you set max_wal_senders high enough in the primary to allow them to be connected simultaneously.

26.2.5. Streaming Replication

Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they're generated, without waiting for the WAL file to be filled.

Streaming replication is asynchronous by default (see Section 26.2.8), in which case there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby. This delay is however much smaller than with file-based log shipping, typically under one second assuming the standby is powerful enough to keep up with the load. With streaming replication, archive_timeout is not required to reduce the data loss window.

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

To use streaming replication, set up a file-based log-shipping standby server as described in Section 26.2. The step that turns a file-based log-shipping standby into streaming replication standby is setting primary_conninfo setting in the recovery.conf file to point to the primary server. Set listen_addresses and authentication options (see pg_hba.conf) on the primary so that the standby server can connect to the replication pseudo-database on the primary server (see Section 26.2.5.1).

On systems that support the keepalive socket option, setting tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count helps the primary promptly notice a broken connection.

Set the maximum number of concurrent connections from the standby servers (see max_wal_senders for details).

When the standby is started and primary_conninfo is set correctly, the standby will connect to the primary after replaying all WAL files available in the archive. If the connection is established successfully, you will see a walreceiver process in the standby, and a corresponding walsender process in the primary.

26.2.5.1. Authentication

It is very important that the access privileges for replication be set up so that only trusted users can read the WAL stream, because it is easy to extract privileged information from it. Standby servers must authenticate to the primary as a superuser or an account that has the REPLICATION privilege. It is recommended to create a dedicated user account with REPLICATION and LOGIN privileges for replication. While REPLICATION privilege gives very high permissions, it does not allow the user to modify any data on the primary system, which the SUPERUSER privilege does.

Client authentication for replication is controlled by a pg_hba.conf record specifying replication in the database field. For example, if the standby is running on host IP 192.168.1.100and the account name for replication is foo, the administrator can add the following line to the pg_hba.conf file on the primary:

# Allow the user "foo" from host 192.168.1.100 to connect to the primary
# as a replication standby if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     foo             192.168.1.100/32        md5

The host name and port number of the primary, connection user name, and password are specified in the recovery.conf file. The password can also be set in the ~/.pgpass file on the standby (specify replication in the database field). For example, if the primary is running on host IP 192.168.1.50, port 5432, the account name for replication is foo, and the password is foopass, the administrator can add the following line to the recovery.conf file on the standby:

# The standby connects to the primary that is running on host 192.168.1.50
# and port 5432 as the user "foo" whose password is "foopass".
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'

26.2.5.2. Monitoring

An important health indicator of streaming replication is the amount of WAL records generated in the primary, but not yet applied in the standby. You can calculate this lag by comparing the current WAL write location on the primary with the last WAL location received by the standby. These locations can be retrieved using pg_current_wal_lsn on the primary and pg_last_wal_receive_lsn on the standby, respectively (see Table 9.79 and Table 9.80 for details). The last WAL receive location in the standby is also displayed in the process status of the WAL receiver process, displayed using the ps command (see Section 28.1 for details).

You can retrieve a list of WAL sender processes via the pg_stat_replication view. Large differences between pg_current_wal_lsn and the view's sent_lsn field might indicate that the master server is under heavy load, while differences between sent_lsn and pg_last_wal_receive_lsn on the standby might indicate network delay, or that the standby is under heavy load.

26.2.6. Replication Slots

Replication slots provide an automated way to ensure that the master does not remove WAL segments until they have been received by all standbys, and that the master does not remove rows which could cause a recovery conflict even when the standby is disconnected.

In lieu of using replication slots, it is possible to prevent the removal of old WAL segments using wal_keep_size, or by storing the segments in an archive using archive_command. However, these methods often result in retaining more WAL segments than required, whereas replication slots retain only the number of segments known to be needed. On the other hand, replication slots can retain so many WAL segments that they fill up the space allocated for pg_wal; max_slot_wal_keep_size limits the size of WAL files retained by replication slots.

Similarly, hot_standby_feedback and vacuum_defer_cleanup_age provide protection against relevant rows being removed by vacuum, but the former provides no protection during any time period when the standby is not connected, and the latter often needs to be set to a high value to provide adequate protection. Replication slots overcome these disadvantages.

26.2.6.1. Querying and manipulating replication slots

Each replication slot has a name, which can contain lower-case letters, numbers, and the underscore character.

Existing replication slots and their state can be seen in the pg_replication_slots view.

Slots can be created and dropped either via the streaming replication protocol (see Section 52.4) or via SQL functions (see Section 9.27.6).

26.2.6.2. Configuration Example

You can create a replication slot like this:

postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
  slot_name  | lsn
-------------+-----
 node_a_slot |

postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
  slot_name  | slot_type | active 
-------------+-----------+--------
 node_a_slot | physical  | f
(1 row)

To configure the standby to use this slot, primary_slot_name should be configured on the standby. Here is a simple example:

primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
primary_slot_name = 'node_a_slot'

26.2.7. Cascading Replication

The cascading replication feature allows a standby server to accept replication connections and stream WAL records to other standbys, acting as a relay. This can be used to reduce the number of direct connections to the master and also to minimize inter-site bandwidth overheads.

A standby acting as both a receiver and a sender is known as a cascading standby. Standbys that are more directly connected to the master are known as upstream servers, while those standby servers further away are downstream servers. Cascading replication does not place limits on the number or arrangement of downstream servers, though each standby connects to only one upstream server which eventually links to a single master/primary server.

A cascading standby sends not only WAL records received from the master but also those restored from the archive. So even if the replication connection in some upstream connection is terminated, streaming replication continues downstream for as long as new WAL records are available.

Cascading replication is currently asynchronous. Synchronous replication (see Section 26.2.8) settings have no effect on cascading replication at present.

Hot Standby feedback propagates upstream, whatever the cascaded arrangement.

If an upstream standby server is promoted to become new master, downstream servers will continue to stream from the new master if recovery_target_timeline is set to 'latest' (the default).

To use cascading replication, set up the cascading standby so that it can accept replication connections (that is, set max_wal_senders and hot_standby, and configure host-based authentication). You will also need to set primary_conninfo in the downstream standby to point to the cascading standby.

26.2.8. Synchronous Replication

PostgreSQL streaming replication is asynchronous by default. If the primary server crashes then some transactions that were committed may not have been replicated to the standby server, causing data loss. The amount of data loss is proportional to the replication delay at the time of failover.

Synchronous replication offers the ability to confirm that all changes made by a transaction have been transferred to one or more synchronous standby servers. This extends that standard level of durability offered by a transaction commit. This level of protection is referred to as 2-safe replication in computer science theory, and group-1-safe (group-safe and 1-safe) when synchronous_commit is set to remote_write.

When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the write-ahead log on disk of both the primary and standby server. The only possibility that data can be lost is if both the primary and the standby suffer crashes at the same time. This can provide a much higher level of durability, though only if the sysadmin is cautious about the placement and management of the two servers. Waiting for confirmation increases the user's confidence that the changes will not be lost in the event of server crashes but it also necessarily increases the response time for the requesting transaction. The minimum wait time is the round-trip time between primary to standby.

Read only transactions and transaction rollbacks need not wait for replies from standby servers. Subtransaction commits do not wait for responses from standby servers, only top-level commits. Long running actions such as data loading or index building do not wait until the very final commit message. All two-phase commit actions require commit waits, including both prepare and commit.

A synchronous standby can be a physical replication standby or a logical replication subscriber. It can also be any other physical or logical WAL replication stream consumer that knows how to send the appropriate feedback messages. Besides the built-in physical and logical replication systems, this includes special programs such as pg_receivewal and pg_recvlogicalas well as some third-party replication systems and custom programs. Check the respective documentation for details on synchronous replication support.

26.2.8.1. Basic Configuration

Once streaming replication has been configured, configuring synchronous replication requires only one additional configuration step: synchronous_standby_names must be set to a non-empty value. synchronous_commit must also be set to on, but since this is the default value, typically no change is required. (See Section 19.5.1 and Section 19.6.2.) This configuration will cause each commit to wait for confirmation that the standby has written the commit record to durable storage. synchronous_commit can be set by individual users, so it can be configured in the configuration file, for particular users or databases, or dynamically by applications, in order to control the durability guarantee on a per-transaction basis.

After a commit record has been written to disk on the primary, the WAL record is then sent to the standby. The standby sends reply messages each time a new batch of WAL data is written to disk, unless wal_receiver_status_interval is set to zero on the standby. In the case that synchronous_commit is set to remote_apply, the standby sends reply messages when the commit record is replayed, making the transaction visible. If the standby is chosen as a synchronous standby, according to the setting of synchronous_standby_names on the primary, the reply messages from that standby will be considered along with those from other synchronous standbys to decide when to release transactions waiting for confirmation that the commit record has been received. These parameters allow the administrator to specify which standby servers should be synchronous standbys. Note that the configuration of synchronous replication is mainly on the master. Named standbys must be directly connected to the master; the master knows nothing about downstream standby servers using cascaded replication.

Setting synchronous_commit to remote_write will cause each commit to wait for confirmation that the standby has received the commit record and written it out to its own operating system, but not for the data to be flushed to disk on the standby. This setting provides a weaker guarantee of durability than on does: the standby could lose the data in the event of an operating system crash, though not a PostgreSQL crash. However, it's a useful setting in practice because it can decrease the response time for the transaction. Data loss could only occur if both the primary and the standby crash and the database of the primary gets corrupted at the same time.

Setting synchronous_commit to remote_apply will cause each commit to wait until the current synchronous standbys report that they have replayed the transaction, making it visible to user queries. In simple cases, this allows for load balancing with causal consistency.

Users will stop waiting if a fast shutdown is requested. However, as when using asynchronous replication, the server will not fully shutdown until all outstanding WAL records are transferred to the currently connected standby servers.

26.2.8.2. Multiple Synchronous Standbys

Synchronous replication supports one or more synchronous standby servers; transactions will wait until all the standby servers which are considered as synchronous confirm receipt of their data. The number of synchronous standbys that transactions must wait for replies from is specified in synchronous_standby_names. This parameter also specifies a list of standby names and the method (FIRST and ANY) to choose synchronous standbys from the listed ones.

The method FIRST specifies a priority-based synchronous replication and makes transaction commits wait until their WAL records are replicated to the requested number of synchronous standbys chosen based on their priorities. The standbys whose names appear earlier in the list are given higher priority and will be considered as synchronous. Other standby servers appearing later in this list represent potential synchronous standbys. If any of the current synchronous standbys disconnects for whatever reason, it will be replaced immediately with the next-highest-priority standby.

An example of synchronous_standby_names for a priority-based multiple synchronous standbys is:

synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'

In this example, if four standby servers s1, s2, s3 and s4 are running, the two standbys s1 and s2 will be chosen as synchronous standbys because their names appear early in the list of standby names. s3 is a potential synchronous standby and will take over the role of synchronous standby when either of s1 or s2 fails. s4 is an asynchronous standby since its name is not in the list.

The method ANY specifies a quorum-based synchronous replication and makes transaction commits wait until their WAL records are replicated to at least the requested number of synchronous standbys in the list.

An example of synchronous_standby_names for a quorum-based multiple synchronous standbys is:

synchronous_standby_names = 'ANY 2 (s1, s2, s3)'

In this example, if four standby servers s1, s2, s3 and s4 are running, transaction commits will wait for replies from at least any two standbys of s1, s2 and s3. s4 is an asynchronous standby since its name is not in the list.

The synchronous states of standby servers can be viewed using the pg_stat_replication view.

26.2.8.3. Planning for Performance

Synchronous replication usually requires carefully planned and placed standby servers to ensure applications perform acceptably. Waiting doesn't utilize system resources, but transaction locks continue to be held until the transfer is confirmed. As a result, incautious use of synchronous replication will reduce performance for database applications because of increased response times and higher contention.

PostgreSQL allows the application developer to specify the durability level required via replication. This can be specified for the system overall, though it can also be specified for specific users or connections, or even individual transactions.

For example, an application workload might consist of: 10% of changes are important customer details, while 90% of changes are less important data that the business can more easily survive if it is lost, such as chat messages between users.

With synchronous replication options specified at the application level (on the primary) we can offer synchronous replication for the most important changes, without slowing down the bulk of the total workload. Application level options are an important and practical tool for allowing the benefits of synchronous replication for high performance applications.

You should consider that the network bandwidth must be higher than the rate of generation of WAL data.

26.2.8.4. Planning for High Availability

synchronous_standby_names specifies the number and names of synchronous standbys that transaction commits made when synchronous_commit is set to on, remote_apply or remote_write will wait for responses from. Such transaction commits may never be completed if any one of synchronous standbys should crash.

The best solution for high availability is to ensure you keep as many synchronous standbys as requested. This can be achieved by naming multiple potential synchronous standbys using synchronous_standby_names.

In a priority-based synchronous replication, the standbys whose names appear earlier in the list will be used as synchronous standbys. Standbys listed after these will take over the role of synchronous standby if one of current ones should fail.

In a quorum-based synchronous replication, all the standbys appearing in the list will be used as candidates for synchronous standbys. Even if one of them should fail, the other standbys will keep performing the role of candidates of synchronous standby.

When a standby first attaches to the primary, it will not yet be properly synchronized. This is described as catchup mode. Once the lag between standby and primary reaches zero for the first time we move to real-time streaming state. The catch-up duration may be long immediately after the standby has been created. If the standby is shut down, then the catch-up period will increase according to the length of time the standby has been down. The standby is only able to become a synchronous standby once it has reached streamingstate. This state can be viewed using the pg_stat_replication view.

If primary restarts while commits are waiting for acknowledgement, those waiting transactions will be marked fully committed once the primary database recovers. There is no way to be certain that all standbys have received all outstanding WAL data at time of the crash of the primary. Some transactions may not show as committed on the standby, even though they show as committed on the primary. The guarantee we offer is that the application will not receive explicit acknowledgement of the successful commit of a transaction until the WAL data is known to be safely received by all the synchronous standbys.

If you really cannot keep as many synchronous standbys as requested then you should decrease the number of synchronous standbys that transaction commits must wait for responses from in synchronous_standby_names (or disable it) and reload the configuration file on the primary server.

If the primary is isolated from remaining standby servers you should fail over to the best candidate of those other remaining standby servers.

If you need to re-create a standby server while transactions are waiting, make sure that the commands pg_start_backup() and pg_stop_backup() are run in a session with synchronous_commit = off, otherwise those requests will wait forever for the standby to appear.

26.2.9. Continuous archiving in standby

When continuous WAL archiving is used in a standby, there are two different scenarios: the WAL archive can be shared between the primary and the standby, or the standby can have its own WAL archive. When the standby has its own WAL archive, set archive_mode to always, and the standby will call the archive command for every WAL segment it receives, whether it's by restoring from the archive or by streaming replication. The shared archive can be handled similarly, but the archive_command must test if the file being archived exists already, and if the existing file has identical contents. This requires more care in the archive_command, as it must be careful to not overwrite an existing file with different contents, but return success if the exactly same file is archived twice. And all that must be done free of race conditions, if two servers attempt to archive the same file at the same time.

If archive_mode is set to on, the archiver is not enabled during recovery or standby mode. If the standby server is promoted, it will start archiving after the promotion, but will not archive any WAL it did not generate itself. To get a complete series of WAL files in the archive, you must ensure that all WAL is archived, before it reaches the standby. This is inherently true with file-based log shipping, as the standby can only restore files that are found in the archive, but not if streaming replication is enabled. When a server is not in recovery mode, there is no difference between on and always modes.

26.3. 持續封存及 Point-in-Time Recovery (PITR)

PostgreSQL 在執行過程中不斷地在叢集資料目錄的 pg_wal/ 子目錄中維護一個交易日誌（Write Ahead Log, WAL）。日誌記錄了對資料庫資料檔案所做的所有變更。該日誌主要用於意外災難還原的目的：如果系統意外損毁，則可以透過「重播」自上一個檢查點以來所建立的日誌項目來恢復資料庫的一致性。然而，日誌的存在使得可以使用第三種策略來備份數據庫：我們可以將檔案系統級備份與 WAL 檔案備份結合在一起。如果需要復原，我們將還原檔案系統備份，然後從備份的 WAL 檔案中重播以使系統進入當下的狀態。與前面所介紹的方法相比，這種方法的管理更為複雜，但具有一些明顯的好處：

我們不需要完美一致的檔案系統備份作為起點。備份中的任何內部不一致都將透過日誌重播進行糾正（這與損毁復原期間發生的變化沒有太大不同）。因此，我們不需要檔案系統的快照功能，而只需要 tar 或類似的封存工具。
由於我們可以結合無限長的 WAL 檔案序列進行重播，因此只需繼續封存 WAL 檔案就可以實現連續備份。這對於大型資料庫來說尤其具有價值，在大型資料庫中，經常性進行完整備份可能不太方便。
不必一直重複播放 WAL 項目。我們可以隨時停止重播，並獲得當時的資料庫快照。因此，此技術支持時間點還原：自從進行基本備份以來，可以隨時將資料庫還原到其狀態。
如果我們將一系列 WAL 檔案連續提供給另一台已載入了相同基本備份檔案的伺服器，則我們將擁有一個熱備份系統：在任何時候，我們都可以啟動第二台伺服器，而該伺服器將具有近乎最新的資料庫副本。

pg_dump 和 pg_dumpall 並不會產生檔案系統層級的備份，因此不能用於連續歸檔解決方案的一部分。這樣的備份是邏輯上的，並且沒有包含足夠的資訊供 WAL 重播使用。

與普通資料系統備份技術一樣，此方法只能支援還原整個資料庫叢集，而不支援部份還原。此外，它還需要大量的檔案儲存空間：基本備份可能會很龐大，繁忙的系統將產生成許多數 MegaByte 等級的 WAL 流量，必須對其進行封存。儘管如此，在許多需要高可靠性的情況下，它還是備份技術中的首選。

要使用連續歸檔（許多資料庫供應商也將其稱為「線上備份」）成功恢復，您需要連續的 WAL 歸檔序列，該序列至少可以延伸到備份的開始時間。因此，在開始第一次基本備份之前，應先設定並測試用於封存 WAL 檔案的程序。因此，我們首先討論封存 WAL 檔案的機制。

26.3.1. 設定 WAL 檔案封存

從抽象的意義上講，執行中的 PostgreSQL 系統會產生無限長的 WAL 記錄序列。系統從物理上將此序列劃分為 WAL 分段檔案，每個檔案通常為16MB（儘管分段大小可以在 initdb 期間變更）。分段檔案被賦予數字名稱，以反映它們在抽象的 WAL 序列中的位置。當不使用 WAL 歸檔時，系統通常只建立幾個分段檔案，然後透過將不再需要的分段檔案重新命名為較高的分段號號來「回收」它們。假設其內容在最後一個檢查點之前的分段檔案不再受關注時，即為可以回收。

歸檔處理 WAL 資料時，我們需要在每個分段檔案填滿後取得其內容，並將該資料保存在回收分段檔案以供重用之前的某個位置。根據應用程序和可用硬體的不同，可能有許多不同的「將資料保存到某處」的方式：我們可以將分段檔案複製到另一台主機上 NFS 掛載的目錄中，然後將它們寫入磁帶中（確保您擁有一種識別每個檔案的原始名稱的方法），或者將它們一起批次處理並燒錄到 CD 上，或者也可以完全燒錄所有資料。為了給資料庫管理者提供靈活性，PostgreSQL 嘗試不對如何完成歸檔做任何假設。相反地，PostgreSQL 讓管理者指定要執行的 shell 命令，以將完整的分段檔案複製到需要的位置。該命令可以像 cp 一樣簡單，也可以呼叫複雜的 shell 腳本—一切由你決定。

要啟用 WAL 歸檔機制，請將 wal_level 組態參數設定為 replica 或更高的等級，將 archive_mode 設定為 on，然後在 archive_command 組態參數中指定要使用的 shell 命令。實際上，這些設定始終會放置在 postgresql.conf 檔案中。在 archive_command 中，％p 替換為要存檔的檔案路徑名稱，而％f 僅替換為檔案名稱。（路徑名稱是相對於目前的工作目錄（即叢集的資料目錄）的。）如果需要在命令中嵌入實際的％字符，請使用 %%。最簡單的指令是：

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows

它將可歸檔的 WAL 分段檔案複製到目錄 /mnt/server/archivedir 中。（這是範例，而不是建議，並且可能不是所有平台都適用。）替換％p 和％f 參數後，實際執行的命令可能如下所示：

test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_wal/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065

將為每個要歸檔的新檔案產生一個類似的命令。

將以執行 PostgreSQL 伺服器的同一用戶的所有權執行 archive 命令。由於要歸檔的一系列 WAL 檔案實際上包含了資料庫中的所有內容，因此您將要確保已歸檔的資料受到保護，以免被窺探；例如，應該存檔到沒有同群組使用者，所有其他人都沒有讀取權限的目錄中。

重要的是，檔案封存指令只有在成功時才回傳零並且退出。結果為零時，PostgreSQL 將假設該檔案已成功封存，將會刪除或回收它。但是，回傳非零的狀態將會告訴 PostgreSQL 該檔案尚未封存。它將定期重試，直到成功為止。

通常應將 archive 指令設計為拒絕覆蓋任何先前存在的封存檔案。這是一種重要的安全設定，可以在管理員出錯（例如將兩個不同伺服器的輸出發送到同一封存目錄）時保持封存檔案的完整性。

仍然建議測試的封存指令以確保它確實不會覆蓋現有檔案，並且在這種情況下會回傳非零的結果。上面用於 Unix 的範例指令透過包含一個單獨的測試步驟來確保這一點。在某些 Unix 平台上，cp 具有諸如 -i 之類的選項，這些選項可用於更輕鬆地完成相同的操作，但是在不驗證是否回傳正確結束狀態的情況下，請不要依賴這些選項。（特別是，當使用 -i 並且目標檔案已經存在時，GNU cp 將回傳零，這並不是 PostgreSQL 所預期的行為。）

While designing your archiving setup, consider what will happen if the archive command fails repeatedly because some aspect requires operator intervention or the archive runs out of space. For example, this could occur if you write to tape without an autochanger; when the tape fills, nothing further can be archived until the tape is swapped. You should ensure that any error condition or request to a human operator is reported appropriately so that the situation can be resolved reasonably quickly. The pg_wal/ directory will continue to fill with WAL segment files until the situation is resolved. (If the file system containing pg_wal/ fills up, PostgreSQL will do a PANIC shutdown. No committed transactions will be lost, but the database will remain offline until you free some space.)

The speed of the archiving command is unimportant as long as it can keep up with the average rate at which your server generates WAL data. Normal operation continues even if the archiving process falls a little behind. If archiving falls significantly behind, this will increase the amount of data that would be lost in the event of a disaster. It will also mean that the pg_wal/ directory will contain large numbers of not-yet-archived segment files, which could eventually exceed available disk space. You are advised to monitor the archiving process to ensure that it is working as you intend.

In writing your archive command, you should assume that the file names to be archived can be up to 64 characters long and can contain any combination of ASCII letters, digits, and dots. It is not necessary to preserve the original relative path (%p) but it is necessary to preserve the file name (%f).

Note that although WAL archiving will allow you to restore any modifications made to the data in your PostgreSQL database, it will not restore changes made to configuration files (that is, postgresql.conf, pg_hba.conf and pg_ident.conf), since those are edited manually rather than through SQL operations. You might wish to keep the configuration files in a location that will be backed up by your regular file system backup procedures. See Section 19.2 for how to relocate the configuration files.

The archive command is only invoked on completed WAL segments. Hence, if your server generates only 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 put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

Also, you can force a segment switch manually with pg_switch_wal if you want to ensure that a just-finished transaction is archived as soon as possible. Other utility functions related to WAL management are listed in Table 9.84.

When wal_level is minimal some SQL commands are optimized to avoid WAL logging, as described in Section 14.4.7. If archiving or streaming replication were turned on during execution of one of these statements, WAL would not contain enough information for archive recovery. (Crash recovery is unaffected.) For this reason, wal_level can only be changed at server start. However, archive_command can be changed with a configuration file reload. If you wish to temporarily stop archiving, one way to do it is to set archive_command to the empty string (''). This will cause WAL files to accumulate in pg_wal/ until a working archive_command is re-established.

26.3.2. Making a Base Backup

The easiest way to perform a base backup is to use the pg_basebackup tool. It can create a base backup either as regular files or as a tar archive. If more flexibility than pg_basebackup can provide is required, you can also make a base backup using the low level API (see Section 25.3.3).

It is not necessary to be concerned about the amount of time it takes to make a base backup. However, if you normally run the server with full_page_writes disabled, you might notice a drop in performance while the backup runs since full_page_writes is effectively forced on during backup mode.

To make use of the backup, you will need to keep all the WAL segment files generated during and after the file system backup. To aid you in doing this, the base backup process creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need for the file system backup. For example, if the starting WAL file is 0000000100001234000055CD the backup history file will be named something like 0000000100001234000055CD.007C9330.backup. (The second part of the file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the file system backup and the WAL segment files used during the backup (as specified in the backup history file), all archived WAL segments with names numerically less are no longer needed to recover the file system backup and can be deleted. However, you should consider keeping several backup sets to be absolutely certain that you can recover your data.

The backup history file is just a small text file. It contains the label string you gave to pg_basebackup, as well as the starting and ending times and WAL segments of the backup. If you used the label to identify the associated dump file, then the archived history file is enough to tell you which dump file to restore.

Since you have to keep around all the archived WAL files back to your last base backup, the interval between base backups should usually be chosen based on how much storage you want to expend on archived WAL files. You should also consider how long you are prepared to spend recovering, if recovery should be necessary — the system will have to replay all those WAL segments, and that could take awhile if it has been a long time since the last base backup.

26.3.3. 使用低階 API 進行基本備份

使用低階 API 進行基本備份的程序比 pg_basebackup 方法需要更多的步驟，但是相對簡單。依次執行這些步驟，並在繼續進行下一步之前驗證步驟的成功是非常重要的。

可以以非排他性(Non-Exclusive)或排他性(Exclusive)方式進行低階的基礎備份。建議使用非排他性方法，不建議使用排他性方法，此方式將來會被捨棄。

25.3.3.1. 進行非排他性(Non-Exclusive)的低階備份

非排他性的低階備份是一種允許其他同時備份也正在運行的備份方式（使用相同備份 API 啟動的備份和使用 pg_basebackup 啟動的備份）。

確保已啟用 WAL 封存選項並且是在正常的狀態。
以具有運行 pg_start_backup 的權限的使用者（超級使用者，或者是已經被授權執行此函數的使用者）身份連線到伺服器（無論哪個資料庫），並執行以下指令：
```
SELECT pg_start_backup('label', false, false);
```
其中 label 是您要用來唯一識別此備份操作的任何字串。必須維持呼叫 pg_start_backup 的連線，直到備份結束，否則備份將會自動中止。
預設情況下，pg_start_backup 可能需要很長時間才能完成。這是因為它會執行一個檢查點(checkpoint)，並且該檢查點所需的 I/O 將進行相當長的一段時間，一般情況下是檢查點時間間隔的一半（請參閱配置參數 checkpoint_completion_target）。通常這就是您想要的，因為它最大程度地減少了對查詢處理的影響。如果要儘快開始備份，請將第二個參數更改為 true，這將使用儘可能多的 I/O 發出立即檢查點。第三個參數為 false 告訴 pg_start_backup 啟動非排他性的基礎備份。
Perform the backup, using any convenient file-system-backup tool such as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the database while you do this. See Section 25.3.3.3 for things to consider during this backup.
In the same connection as before, issue the command:
```
SELECT * FROM pg_stop_backup(false, true);
```
This terminates backup mode. On a primary, it also performs an automatic switch to the next WAL segment. On a standby, it is not possible to automatically switch WAL segments, so you may wish to run pg_switch_wal on the primary to perform a manual switch. The reason for the switch is to arrange for the last WAL segment file written during the backup interval to be ready to archive.
The pg_stop_backup will return one row with three values. The second of these fields should be written to a file named backup_label in the root directory of the backup. The third field should be written to a file named tablespace_map unless the field is empty. These files are vital to the backup working, and must be written without modification.
Once the WAL segment files active during the backup are archived, you are done. The file identified by pg_stop_backup's first return value is the last segment that is required to form a complete set of backup files. On a primary, if archive_mode is enabled and the wait_for_archive parameter is true, pg_stop_backup does not return until the last segment has been archived. On a standby, archive_mode must be always in order for pg_stop_backup to wait. Archiving of these files happens automatically since you have already configured archive_command. In most cases this happens quickly, but you are advised to monitor your archive system to ensure there are no delays. If the archive process has fallen behind because of failures of the archive command, it will keep retrying until the archive succeeds and the backup is complete. If you wish to place a time limit on the execution of pg_stop_backup, set an appropriate statement_timeout value, but make note that if pg_stop_backup terminates because of this your backup may not be valid.
If the backup process monitors and ensures that all WAL segment files required for the backup are successfully archived then the wait_for_archive parameter (which defaults to true) can be set to false to have pg_stop_backup return as soon as the stop backup record is written to the WAL. By default, pg_stop_backup will wait until all WAL has been archived, which can take some time. This option must be used with caution: if WAL archiving is not monitored correctly then the backup might not include all of the WAL files and will therefore be incomplete and not able to be restored.

26.3.3.2. Making An Exclusive Low-Level Backup

排他性的備份方法已經過時，應該避免使用。在 PostgreSQL 9.6 之前，這是唯一可用的低階方法，但是現在建議所有使用者升級其腳本以使用非排他性的備份。

排他性備份的流程與非排他性備份的流程基本相同，但是在幾個關鍵步驟上有所不同。這種類型的備份只能在主要資料庫上進行，不允許同時進行其他備份。此外，由於如下所述建立了備份標籤檔案，因此它可以阻止當機後主伺服器的自動重啟。另一方面，從備份或備用資料庫中刪除此檔案是一個常見的人為錯誤，它可能導致嚴重的資料損壞。如果必須使用此方法，則可以使用以下步驟。

Ensure that WAL archiving is enabled and working.
Connect to the server (it does not matter which database) as a user with rights to run pg_start_backup (superuser, or a user who has been granted EXECUTE on the function) and issue the command:
```
SELECT pg_start_backup('label');
```
where label is any string you want to use to uniquely identify this backup operation. pg_start_backup creates a backup label file, called backup_label, in the cluster directory with information about your backup, including the start time and label string. The function also creates a tablespace map file, called tablespace_map, in the cluster directory with information about tablespace symbolic links in pg_tblspc/ if one or more such link is present. Both files are critical to the integrity of the backup, should you need to restore from it.
By default, pg_start_backup can take a long time to finish. This is because it performs a checkpoint, and the I/O required for the checkpoint will be spread out over a significant period of time, by default half your inter-checkpoint interval (see the configuration parameter checkpoint_completion_target). This is usually what you want, because it minimizes the impact on query processing. If you want to start the backup as soon as possible, use:
```
SELECT pg_start_backup('label', true);
```
This forces the checkpoint to be done as quickly as possible.
Perform the backup, using any convenient file-system-backup tool such as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the database while you do this. See Section 25.3.3.3 for things to consider during this backup.
As noted above, if the server crashes during the backup it may not be possible to restart until the backup_label file has been manually deleted from the PGDATA directory. Note that it is very important to never remove the backup_label file when restoring a backup, because this will result in corruption. Confusion about when it is appropriate to remove this file is a common cause of data corruption when using this method; be very certain that you remove the file only on an existing master and never when building a standby or restoring a backup, even if you are building a standby that will subsequently be promoted to a new master.
Again connect to the database as a user with rights to run pg_stop_backup (superuser, or a user who has been granted EXECUTE on the function), and issue the command:
```
SELECT pg_stop_backup();
```
This function terminates backup mode and performs an automatic switch to the next WAL segment. The reason for the switch is to arrange for the last WAL segment written during the backup interval to be ready to archive.
Once the WAL segment files active during the backup are archived, you are done. The file identified by pg_stop_backup's result is the last segment that is required to form a complete set of backup files. If archive_mode is enabled, pg_stop_backup does not return until the last segment has been archived. Archiving of these files happens automatically since you have already configured archive_command. In most cases this happens quickly, but you are advised to monitor your archive system to ensure there are no delays. If the archive process has fallen behind because of failures of the archive command, it will keep retrying until the archive succeeds and the backup is complete.
When using exclusive backup mode, it is absolutely imperative to ensure that pg_stop_backup completes successfully at the end of the backup. Even if the backup itself fails, for example due to lack of disk space, failure to call pg_stop_backup will leave the server in backup mode indefinitely, causing future backups to fail and increasing the risk of a restart failure during the time that backup_label exists.

26.3.3.3. Backing Up The Data Directory

Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. When taking a base backup of an active database, this situation is normal and not an error. However, you need to ensure that you can distinguish complaints of this sort from real errors. For example, some versions of rsync return a separate exit code for “vanished source files”, and you can write a driver script to accept this exit code as a non-error case. Also, some versions of GNU tar return an error code indistinguishable from a fatal error if a file was truncated while tar was copying it. Fortunately, GNU tar versions 1.16 and later exit with 1 if a file was changed during the backup, and 2 for other errors. With GNU tar version 1.23 and later, you can use the warning options --warning=no-file-changed --warning=no-file-removed to hide the related warning messages.

Be certain that your backup includes all of the files under the database cluster directory (e.g., /usr/local/pgsql/data). If you are using tablespaces that do not reside underneath this directory, be careful to include them as well (and be sure that your backup archives symbolic links as links, otherwise the restore will corrupt your tablespaces).

You should, however, omit from the backup the files within the cluster's pg_wal/ subdirectory. This slight adjustment is worthwhile because it reduces the risk of mistakes when restoring. This is easy to arrange if pg_wal/ is a symbolic link pointing to someplace outside the cluster directory, which is a common setup anyway for performance reasons. You might also want to exclude postmaster.pid and postmaster.opts, which record information about the running postmaster, not about the postmaster which will eventually use this backup. (These files can confuse pg_ctl.)

It is often a good idea to also omit from the backup the files within the cluster's pg_replslot/ directory, so that replication slots that exist on the master do not become part of the backup. Otherwise, the subsequent use of the backup to create a standby may result in indefinite retention of WAL files on the standby, and possibly bloat on the master if hot standby feedback is enabled, because the clients that are using those replication slots will still be connecting to and updating the slots on the master, not the standby. Even if the backup is only intended for use in creating a new master, copying the replication slots isn't expected to be particularly useful, since the contents of those slots will likely be badly out of date by the time the new master comes on line.

The contents of the directories pg_dynshmem/, pg_notify/, pg_serial/, pg_snapshots/, pg_stat_tmp/, and pg_subtrans/ (but not the directories themselves) can be omitted from the backup as they will be initialized on postmaster startup. If stats_temp_directory is set and is under the data directory then the contents of that directory can also be omitted.

Any file or directory beginning with pgsql_tmp can be omitted from the backup. These files are removed on postmaster start and the directories will be recreated as needed.

pg_internal.init files can be omitted from the backup whenever a file of that name is found. These files contain relation cache data that is always rebuilt when recovering.

The backup label file includes the label string you gave to pg_start_backup, as well as the time at which pg_start_backup was run, and the name of the starting WAL file. In case of confusion it is therefore possible to look inside a backup file and determine exactly which backup session the dump file came from. The tablespace map file includes the symbolic link names as they exist in the directory pg_tblspc/ and the full path of each symbolic link. These files are not merely for your information; their presence and contents are critical to the proper operation of the system's recovery process.

It is also possible to make a backup while the server is stopped. In this case, you obviously cannot use pg_start_backup or pg_stop_backup, and you will therefore be left to your own devices to keep track of which backup is which and how far back the associated WAL files go. It is generally better to follow the continuous archiving procedure above.

26.3.4. Recovering Using a Continuous Archive Backup

好的，剛好最糟糕的事情發生了，這時候您需要使用備份來還原資料庫。步驟如下：

停止伺服器（如果正在執行的話）。
如果有足夠的空間，請將整個叢集資料目錄和所有資料表空間複製到一個暫存的路徑，以備之需。請注意，此預防措施需要你的系統上有足夠的可用空間來容納現有資料庫的兩個副本。如果沒有足夠的空間，則至少應保存叢集的 pg_wal 子目錄的內容，因為它可能包含在系統關閉之前尚未歸檔封存的交易日誌。
刪除叢集資料目錄下以及正在使用的所有資料表空間目錄下的所有現有檔案和子目錄。
從檔案系統備份中還原資料庫檔案。確保已授予正確的擁有者（資料庫系統使用者，而不是 root！）和正確的權限還原它們。如果有使用額外的資料表空間，則應驗證 pg_tblspc/ 中的符號連結是否也已正確還原。
刪除 pg_wal/ 中的所有檔案；這些來自檔案系統的備份，因此可能已過時而不是最新。如果您根本沒有備份 pg_wal/，那麼請以適當的權限重新建立它，請小心確保如果您之前已進行過額外配置，則應將其重新建立為符號連結。
如果您具有在步驟 2 中所保存的未封存 WAL 檔案，請將其複製到 pg_wal/ 之中。（最好複製它們，而不是移動它們，因為如果出現問題而必須重新開始的話，您仍然擁有未修改的檔案。）
在 postgresql.conf 中進行還原設定（請參閱第 19.5.4 節），並在叢集資料目錄中建立檔案 recovery.signal。您可能還需要臨時修改 pg_hba.conf，以防止一般使用者連線進來，直到您確定還原成功為止。
啟動伺服器。伺服器將進入還原模式，並繼續讀取所需的 WAL 檔案。如果還原由於外部錯誤而終止，則只需重啟伺服器即可繼續還原。還原過程完成後，伺服器將刪除 recovery.signal（以防止以後意外重新進入還原模式），然後開始正常的資料庫操作。
檢查資料庫的內容，以確保您已經還原到所需要的狀態。如果沒有，請回到步驟 1。如果一切正常，請透過將 pg_hba.conf 恢復為正常狀態來允許您的使用者進行連線。

所有這一切的關鍵部分是建立還原設定，該設定描述了您要如何還原以及還原應進行多長的時間。你絕對必須指定的一件事是 restore_command，它告訴 PostgreSQL 如何檢索已封存的 WAL 檔案。像 archive_command 一樣，這是一個 shell 指令字串。它可以包含 %f（依所需的日誌檔案的名稱代換）和 %p（將日誌檔案複製到的路徑名）代換。（路徑名是相對於目前的工作目錄（即叢集的資料目錄）的。）如果需要在指令中使用實際的 % 字元，請寫入 %%。最簡單的指令是：

restore_command = 'cp /mnt/server/archivedir/%f %p'

which will copy previously archived WAL segments from the directory /mnt/server/archivedir. Of course, you can use something much more complicated, perhaps even a shell script that requests the operator to mount an appropriate tape.

It is important that the command return nonzero exit status on failure. The command will be called requesting files that are not present in the archive; it must return nonzero when so asked. This is not an error condition. 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.

Not all of the requested files will be WAL segment files; you should also expect requests for files with a suffix of .history. Also be aware that the base name of the %p path will be different from %f; do not expect them to be interchangeable.

WAL segments that cannot be found in the archive will be sought in pg_wal/; this allows use of recent un-archived segments. However, segments that are available from the archive will be used in preference to files in pg_wal/.

Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time (or as close as possible given the available WAL segments). Therefore, a normal recovery will end with a “file not found” message, the exact text of the error message depending upon your choice of restore_command. You may also see an error message at the start of recovery for a file named something like 00000001.history. This is also normal and does not indicate a problem in simple recovery situations; see Section 25.3.5 for discussion.

If you want to recover to some previous point in time (say, right before the junior DBA dropped your main transaction table), just specify the required stopping point. You can specify the stop point, known as the “recovery target”, either by date/time, named restore point or by completion of a specific transaction ID. As of this writing only the date/time and named restore point options are very usable, since there are no tools to help you identify with any accuracy which transaction ID to use.

Note

The stop point must be after the ending time of the base backup, i.e., the end time of pg_stop_backup. You cannot use a base backup to recover to a time when that backup was in progress. (To recover to such a time, you must go back to your previous base backup and roll forward from there.)

If recovery finds corrupted WAL data, recovery will halt at that point and the server will not start. In such a case the recovery process could be re-run from the beginning, specifying a “recovery target” before the point of corruption so that recovery can complete normally. If recovery fails for an external reason, such as a system crash or if the WAL archive has become inaccessible, then the recovery can simply be restarted and it will restart almost from where it failed. Recovery restart works much like checkpointing in normal operation: the server periodically forces all its state to disk, and then updates the pg_control file to indicate that the already-processed WAL data need not be scanned again.

26.3.5. Timelines

將資料庫還原到先前時間點的能力會有一些複雜，類似於有關時間旅行和平行宇宙的科幻小說故事。例如，在資料庫的原始歷史記錄中，假設您在星期二晚上 5:15 PM 刪除了一個關鍵的資料表，但是直到星期三中午才意識到自己的錯誤。不用擔心，您可以取出備份，恢復到星期二晚上 5:14 的時間點，並開始運行。在資料庫宇宙的歷史記錄中，其實您從未刪除過資料表。但是，假設您後來又意識到這不是一個好主意，並且想回到原始歷史中的星期三上午。在資料庫執行期間，如果您覆蓋了一些 WAL 檔案，而這些檔案會造成你無法再回到你希望回到原來的時空。因此，為避免這種情況，您需要將時間點恢復後產生的一系列 WAL 記錄與原始資料庫歷史記錄中產生的 WAL 記錄檔案區分開來。

To deal with this problem, PostgreSQL has a notion of timelines. Whenever an archive recovery completes, a new timeline is created to identify the series of WAL records generated after that recovery. The timeline ID number is part of WAL segment file names so a new timeline does not overwrite the WAL data generated by previous timelines. It is in fact possible to archive many different timelines. While that might seem like a useless feature, it's often a lifesaver. Consider the situation where you aren't quite sure what point-in-time to recover to, and so have to do several point-in-time recoveries by trial and error until you find the best place to branch off from the old history. Without timelines this process would soon generate an unmanageable mess. With timelines, you can recover to any prior state, including states in timeline branches that you abandoned earlier.

Every time a new timeline is created, PostgreSQL creates a “timeline history” file that shows which timeline it branched off from and when. These history files are necessary to allow the system to pick the right WAL segment files when recovering from an archive that contains multiple timelines. Therefore, they are archived into the WAL archive area just like WAL segment files. The history files are just small text files, so it's cheap and appropriate to keep them around indefinitely (unlike the segment files which are large). You can, if you like, add comments to a history file to record your own notes about how and why this particular timeline was created. Such comments will be especially valuable when you have a thicket of different timelines as a result of experimentation.

The default behavior of recovery is to recover along the same timeline that was current when the base backup was taken. If you wish to recover into some child timeline (that is, you want to return to some state that was itself generated after a recovery attempt), you need to specify the target timeline ID in recovery_target_timeline. You cannot recover into timelines that branched off earlier than the base backup.

26.3.6. Tips and Examples

Some tips for configuring continuous archiving are given here.

26.3.6.1. Standalone Hot Backups

It is possible to use PostgreSQL's backup facilities to produce standalone hot backups. These are backups that cannot be used for point-in-time recovery, yet are typically much faster to backup and restore than pg_dump dumps. (They are also much larger than pg_dump dumps, so in some cases the speed advantage might be negated.)

As with base backups, the easiest way to produce a standalone hot backup is to use the pg_basebackup tool. If you include the -X parameter when calling it, all the write-ahead log required to use the backup will be included in the backup automatically, and no special action is required to restore the backup.

If more flexibility in copying the backup files is needed, a lower level process can be used for standalone hot backups as well. To prepare for low level standalone hot backups, make sure wal_level is set to replica or higher, archive_mode to on, and set up an archive_command that performs archiving only when a switch file exists. For example:

archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'

This command will perform archiving when /var/lib/pgsql/backup_in_progress exists, and otherwise silently return zero exit status (allowing PostgreSQL to recycle the unwanted WAL file).

With this preparation, a backup can be taken using a script like the following:

touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

The switch file /var/lib/pgsql/backup_in_progress is created first, enabling archiving of completed WAL files to occur. After the backup the switch file is removed. Archived WAL files are then added to the backup so that both base backup and all required WAL files are part of the same tar file. Please remember to add error handling to your backup scripts.

26.3.6.2. Compressed Archive Logs

如果需要考慮封存檔案的儲存空間，則可以使用 gzip 壓縮這些檔案：

archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'

然後，您將需要在還原過程中使用 gunzip：

restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'

26.3.6.3. Archive_command Scripts

Many people choose to use scripts to define their archive_command, so that their postgresql.conf entry looks very simple:

archive_command = 'local_backup_script.sh "%p" "%f"'

Using a separate script file is advisable any time you want to use more than a single command in the archiving process. This allows all complexity to be managed within the script, which can be written in a popular scripting language such as bash or perl.

Examples of requirements that might be solved within a script include:

Copying data to secure off-site data storage
Batching WAL files so that they are transferred every three hours, rather than one at a time
Interfacing with other backup and recovery software
Interfacing with monitoring software to report errors

使用 archive_command 腳本時，最好啟用 logging_collector。這樣的話，從腳本寫入 stderr 的所有訊息都會出現在資料庫伺服器記錄檔之中，從而使複雜的設定在異常時易於除錯。

26.3.7. Caveats

截至目前為止，連續歸檔技術(PITR)仍然存在著一些侷限性。這些可能會在未來的版本中改善：

如果在執行基礎備份時執行了 CREATE DATABASE 命令，然後在仍在進行基礎備份的同時修改了 CREATE DATABASE 所複製的樣版資料庫，則還原的時候很可能會使這些修改連帶影響到其所建立的資料庫之中。這當然不是希望發生的事。為了避免這種風險，最好在進行基礎1備份的同時不要修改任何樣版資料庫。
CREATE TABLESPACE 指令使用絕對路徑進行存放 WAL 記錄，因此重放交易時，將會以相同絕對路徑的資料表空間進行重放。如果正在其他主機上重放交易日誌，則這可能不是希望的的結果。即使在同一台主機上重放交易日誌，但是將日誌重放到新的資料目錄中，也可能很危險：重放仍將覆蓋原始資料表空間的內容。為了避免這種潛在的麻煩，最佳實作是在建立或刪除資料表空間之後進行新的基礎備份。

你還需要注意的是，一般而言 WAL 格式相當龐大，因為它包含許多磁碟頁面快照。這些頁面快照旨在支援災難復原，因為我們可能需要修復部分寫入的磁碟頁面。根據系統硬體和軟體環境的不同，部分寫入的風險可能很小，可以忽略，在這種情況下，您可以透過使用 full_page_writes 參數關閉頁面快照來顯著減少已歸檔日誌的總量。（在執行此操作之前，請先閱讀第 30 章中的說明和警告。）關閉頁面快照並不會阻礙將日誌用於 PITR 操作。未來的發展方向1是即使在啟用 full_page_writes 的情況下，也可以透過刪除不必要的頁面副本來壓縮已歸檔封存的 WAL 資料。同時，管理者可能希望透過儘可能增加檢查點(checkpoint)間隔參數來減少 WAL 中包含的頁面快照的數量。

III. 系統管理

16. 以預編譯套件安裝

17. 以原始碼安裝

17.1. 簡要步驟

17.2. 環境需求

17.3. Getting The Source

17.4. 安裝流程

Note

17.4.1. configure Options

17.4.1.1. Installation Locations

Note

17.4.1.2. PostgreSQL Features

17.4.1.3. Anti-Features

17.4.1.4. Build Process Details

17.4.1.5. Miscellaneous

17.4.1.6. Developer Options

17.4.2. configure Environment Variables

Note

17.5. Post-Installation Setup

16.5.1. Shared Libraries

16.5.2. Environment Variables

17.6. Supported Platforms

17.7. 平台相關的注意事項

17.7.1. AIX

17.7.1.1. Memory Management

17.7.2. Cygwin

17.7.3. macOS

17.7.4. MinGW/Native Windows

17.7.5. Solaris

17.7.5.1. Required Tools

17.7.5.2. Configure Complains About A Failed Test Program

17.7.5.3. Compiling For Optimal Performance

17.7.5.4. Using DTrace For Tracing PostgreSQL

18. 以原始碼在 Windows 上安裝

18.1. Building with Visual C++ or the Microsoft Windows SDK

18.1.1. Requirements

Note

18.1.2. Special Considerations for 64-Bit Windows

18.1.3. Building

18.1.4. Cleaning and Installing

18.1.5. Running the Regression Tests

19. 服務配置與維運

19.1. PostgreSQL 使用者帳號

19.2. Creating a Database Cluster

Tip

19.2.1. Use of Secondary File Systems

19.2.2. File Systems

19.2.2.1. NFS

19.3. Starting the Database Server

18.3.1. Server Start-up Failures

18.3.2. Client Connection Problems

19.4. 核心資源管理

18.4.1. Shared Memory and Semaphores

Note

18.4.2. systemd RemoveIPC

Caution

18.4.3. Resource Limits

18.4.4. Linux Memory Overcommit

Note

18.4.5. Linux Huge Pages

19.5. Shutting Down the Server

Important

19.6. Upgrading a PostgreSQL Cluster

18.6.1. Upgrading Data via pg_dumpall

18.6.2. Upgrading Data via pg_upgrade

18.6.3. Upgrading Data via Replication

19.7. Preventing Server Spoofing

19.9. Secure TCP/IP Connections with SSL

18.9.1. Basic Setup

18.9.2. OpenSSL Configuration

Note

18.9.3. Using Client Certificates

18.9.4. SSL Server File Usage

Table 18.2. SSL Server File Usage

18.9.5. Creating Certificates

19.10. Secure TCP/IP Connections with GSSAPI Encryption

18.10.1. Basic Setup

20.2. File Locations

data_directory (string)

config_file (string)

17.4.1. `configure` Options

17.4.2. `configure` Environment Variables

`data_directory` (`string`)

`config_file` (`string`)

`hba_file` (`string`)

`ident_file` (`string`)

`external_pid_file` (`string`)

`data_directory` (`string`)

`config_file` (`string`)

`hba_file` (`string`)

`ident_file` (`string`)

`external_pid_file` (`string`)