第十八章 数据库服务端设置及操作

本章介绍如何设置和运行数据库服务，及其与操作系统的交互。

18.1 PostgreSQL用户账号

建议使用单独的操作系统账号运行PostgreSQL。最好该账号非PostgreSQL可执行文件的所有者。

 

使用useradd或adduser在unix系统添加用户。默认使用postgres。

18.2 创建数据库群集

首先，需要在磁盘上初始化数据库存储区域。此即为数据库群集（database cluster）。数据库群集为由运行的数据库服务单个实例管理的多个数据库的集合。初始化之后，数据库群集会包含一个postgres数据库；一个template1数据库（即作为新创建数据库模板的数据库）。有关在数据库群集中创建数据库的信息，请参见第22章。

 

在文件系统层面，数据库群集就是存储所有数据的一个路径。此即为数据路径（data directory）或数据区域（data area）。没有默认值，但一般存在/usr/local/pgsql/data或/var/lib/pgsql/data下。数据路径在使用前必须先使用 initdb进行初始化。

 

使用 initdb加-D选项指定数据库群集路径，对数据库群集手动进行初始化：

也可通过pg_ctl运行initdb：

initdb首先会创建指定的数据目录（如果不存在）。如果目录已存在且里面已经有文件，则initdb会拒绝执行。可在initdb的时候使用-W，–pwprompt或者–pwfile选项为超级用户指定密码。指定-A md5或者-A password可不使用默认的trust认证模式；或在执行完initdb后但启动数据库服务之前修改pg_hba.conf文件（更多限制连接的方式，请参见第20章）。 

18.2.1 使用二级文件系统

不建议使用挂载点作为数据库数据目录，建议在挂载点之下至少再创建一个子路径作为数据路径。

18.2.2 文件系统

通常，任何使用POSIX的文件系统均适用于PostgreSQL。

18.2.2.1 NFS

可以使用NFS作为PostgreSQL的数据目录。没什么特别，但有一点，在挂在的时候，必须使用hard选项。

18.3 启动数据库服务

数据库服务进程名称为postgres。

 

启动数据库服务最简单的方式是使用参数-D（指定数据目录）调用postgres，例如：

后台启动进程：

开机自启动脚本可参见contrib/start-scripts。

18.3.1 数据库服务启动失败

数据库服务启动失败的原因可能有几个常见原因。例如：

当然，也有可能使用了保留端口：

或者：

错误信息：

并不是指磁盘空间不足了。而是对于system V semaphores的内核限制过低了。折中办法是降低max_connections。但终究还是要调大操作系统内核限制的。

18.3.2 客户端连接问题

虽然客户端问题千奇百怪，但有那么几个，却与数据库服务的启动方式有关。

而错误：

数据库服务未启动（并不是数据库服务接收到连接然后拒绝了，此种情况请参见第20.15节）。

18.4 管理内核资源

PostgreSQL有时会耗尽各种操作系统限制的资源，特别是当多个数据库服务运行在同一个服务器上的时候。本节介绍PostgreSQL使用的内核资源以及处理与内核资源消耗有关错误的步骤。

18.4.1. Shared Memory and Semaphores

PostgreSQL需要操作系统提供内部进程通信（IPC：inter-process communication）特性，特别是shared memory（共享内存）和semaphores（信号量）。类Unix系统需要提供“System V” IPC，“POSIX” IPC或两者均提供。Windows在此不讨论。

 

表18.1列出相关内核参数，并针对某些平台给出建议值：

SEMMNS必须大于max_connections+autovacuum_max_workers+max_wal_senders+max_worker_processes+1。

 

Linux

默认共享内存设置一般足够。可通过sysctl调整共享内存大小。例如，设置16GB：

18.4.2. systemd RemoveIPC

 如果正在使用systemd，则需要注意对IPC资源删除的问题（从源码安装的时候需格外注意）。 logind.conf文件中的RemoveIPC控制当用户退出时是否完全删除IPC对象。默认为on，但有些系统默认为off。若设置为on，有时候会发现，用于并行查询执行的共享内存对象在明显随机的时间被删除，从而在尝试打开和删除它们时导致错误和警告，例如：

所以建议在创建用户的时候，使用useradd -r或adduser –system。如果用户已创建且无法修改，则建议在/etc/systemd/logind.conf中设置：

18.4.3 资源限制

类Unix系统可能会有一些干扰PostgreSQL服务的资源限制。比较重要的有：每个用户可用进程数、每个进程可打开文件数、每个进程可用内存等。每个限制均有软限制和硬限制。软限制即为限制，但可以由用户调整最大到硬限制；而硬限制只能由root用户调整。

 

PostgreSQL每个连接使用一个进程。

18.4.4  Linux内存过量使用

Linux上默认的虚拟内存使用机制对PostgreSQL不太友好。因为当内存使用过高时，可能会直接终止PostgreSQL的postmaster（pg的主进程）进程。证据：

虽然PostgreSQL现有连接不会断掉，但是不能接受新连接了。只有重启PostgreSQL才可解决。

 

规避该问题的方法：

提高swap；

调低PostgreSQL的参数，shared_buffers，work_mem，hash_mem_multiplier；调低max_connections并使用连接池软件；

可以设置postmaster，即使OOM，也不killpostmaster：

18.4.5 Linux大页

在使用大块连续内存时使用大页面可减少开销，尤其是在使用大shared_buffers时。要在PostgreSQL中使用此功能，需要内核设置CONFIG_HUGETLBFS=y以及CONFIG_HUGETLB_PAGE=y。还需要调整内核设置vm.nr_hugepages。要预估所需的大页数，请在未启用大页的情况下启动PostgreSQL，并使用/proc文件系统检查postmaster的匿名共享内存段大小以及系统的大页大小：

还需通过sysctl设置vm.hugetlb_shm_group给PostgreSQL用户使用大页的权限和/或使用ulimit -l给用户授予锁定内存的权限。

 

默认PostgreSQL会在需要时才使用大页，可以通过设置postgresql.conf中的huge_pages=on来强制使用大页。但如果设置后，大页不够，数据库服务则会启动失败。

18.5  关闭数据库服务

关闭数据库服务有多种方式，不够底层来说，都是给postgres进程发送相应信号。

 

可通过给postgres发送不同信号，控制关闭模式：

SIGTERM

    smart shutdown模式。拒绝新连接，保留已有会话，仅当所有会话退出才会关闭数据库服务。

SIGINT

    fast shutdown模式。拒绝新连接，给已有连接发送SIGTERM（中断当前事务，并退出）信号。如果正在备份，那么备份会退出，备份文件也就没用了。

SIGQUIT

    immediate shutdown模式。给所以进程发送信号SIGQUIT并等待进程中断，如果5秒内未中断，则直接SIGKILL。类似于断电，进程不会进行数据库常规关闭，所以在启动时会进行恢复（通过重演WAL日志）。此模式尽量不用。

 

不要直接kill PostgreSQL进程，应使用pg_terminate_backend()或SIGTERM。

18.6 升级PostgreSQL群集

本节介绍如何对PostgreSQL数据进行版本升级。

 

版本号介绍：10.1，主版本是10，小版本是1，即第10主版本的第一个小版本；10以前的，有三个数，例如9.5.3，则主版本是9.5，小版本是3。

 

小版本不会更改内部存储格式，所以大版本内的小版本都是兼容的。例如，10.1与10.0和10.6都兼容；同样，9.5.3与9.5.0和9.5.6都兼容。兼容版本之间的升级，仅需在数据库服务关闭后替换掉可执行文件，然后启动服务即可。数据目录不变。

 

对于大版本，数据存储格式一般会改变，所以需要升级。一般是将原有数据导出，然后导入新的数据库。不过，使用pg_upgrade会更快。

 

大版本升级，应用程序可能也需要在语法等方面进行修改。参见版本注意事项的“Migration”部分。新版本可能存在以下方面的更改：

管理

    大版本间，监控及控制服务一般会修改和优化；

SQL

    新的SQL兼容；

Library API

    一般是新功能的添加；

系统视图

    一般影响数据库管理工具的系统视图会变更；

Server C-language API

    使用C写的后台函数API的更改。

18.6.1 使用pg_dumpall升级数据

升级的其中一个方式是：将原版本的数据导出，然后导入到新版本。这个工作，需要使用逻辑备份工具，例如pg_dumpall；操作系统级别的备份方式不可用。

步骤：

1.停应用（无数据写入），备份旧版本的数据。

2.停旧版本数据库。

3.如果从备份恢复，那么先将旧版本数据目录备份。

4.安装新版本；

5.如果需要的话，创建新的数据库群集。

6.恢复之前版本中pg_hba.conf和postgresql.conf中的修改；

7.启动新版本的数据库服务：

8.最后，恢复数据：

18.6.2 使用pg_upgrade升级数据

步骤类似于使用pg_dumpall，更多信息参见pg_upgrade。

18.6.3 通过复制升级数据

也可以通过逻辑复制方式，创建一个新版本的备库。这可以极大降低停机时间。可使用自带的逻辑复制功能，也可以使用pglogical、Slony、Londiste和Bucardo。

18.7 防止服务欺骗

在数据库服务运行的时候，其他用户无法替代正常的数据库服务。但是在数据库服务关闭时，本地用户却可以通过启动他们自己的服务来欺骗使用原服务的连接。伪服务能够接收到客户端发送的密码和查询，但因为权限问题，却不会返回任何数据。因为所有用户都可以启动数据服务，所以服务欺骗是可能的，如果不进行特殊配置，那么客户端并不会识别出哪个是伪服务。

 

其中一个方法是使用unix_socket_directories。

还可使用SSL或/和GSSAPI加密。

18.8 加密选项

PostgreSQL提供多级别的加密认证。

密码加密

    以hash存储用户密码（password_encryption），所有管理员看不到明文。如果客户端使用了SCRAM或MD5加密，那么在通过网络传输前，密码即已加密。建议使用SCRAM。

列加密

    使用pgcrypto。

数据分区加密

    可在文件系统级别或块级别进行存储加密。

网络传输加密

    SSL、GSSAPI加密连接。

SSL主机认证

    服务端及客户端之间可进行SSL认证。

客户端加密

    数据在发送到服务端之间，在客户端就进行了加密。

18.9 使用SSL的TCP/IP安全连接

PostgreSQL对SSL连接以加密客户端/服务端通信提供原生支持。不过需要在客户端及服务端安装OpenSSL，并在PostgreSQL构建时启用该特性（参见第16章）。

18.9.1 基础设置

构建时若启用了SSL支持，则可以通过将postgresql.conf文件中的ssl设置为on启用SSL。服务端可在同一端口监听普通和SSL连接，并可指定对客户端的SSL使用。默认，此为客户端的选项，参见第20.1节。

 

如以SSL模式启动，则必须存在服务端认证及私钥文件。这些文件默认存在与服务端的数据目录下，名称分别为server.crt和server.key。也可使用ssl_cert_file和ssl_key_file参数自定义。

 

server.crt中的第一行，必须是服务端的密钥。

18.9.2 OpenSSL配置

PostgreSQL读取操作系统级别的OpenSSL配置文件。默认名称为openssl.conf，路径可通过openssl version -d查看。可通过设置环境变量OPENSSL_CONF自定义OpenSSL的配置文件路径及名称。

&n

来源：丹心明月