简单配置建议

PostgreSQL 配置建议

数据库环境的管理需要来自不同学科的资源。数据库管理员 (DBA) 必须与系统和存储管理员密切合作。PostgreSQL 严重依赖主机操作系统 (OS) 进行存储管理。它没有 Oracle 用于存储管理的先进和复杂的功能。

这些建议是为了标准化和简化 PostgreSQL 数据库配置。

软件位置和所有权

PostgreSQL 软件在 Linux 上的常用位置是 /usr/local/pgsql，可执行文件、源代码和数据存在于各个子目录中。但是，PostgreSQL 是开源软件，无论是谁是发行者、打包者还是支持者，都会有他们关于软件放置位置和哪个帐户拥有软件的建议。

优先考虑操作系统提供的软件包，而不是在本地编译服务器。这些软件包经过开发以很好地集成到操作系统中，忽略使用它们意味着你必须重新完成创建软件包的工作。（这里的建议不一定与打包者的选项选择一致。在选择哪种方式时，坚持一致的集合。）

软件、数据库文件和服务器进程的所有者和组应该是 postgres:dba。UID 和 GID 必须与系统管理协商确定。

创建一个基本软件目标目录

  /opt/postgres

使用软件版本的第一个两位数字定义软件安装（以 9.0 为例）

  /opt/postgres/9.0

注意，使用版本号中第三位数字升级通常需要停止服务器、切换到新软件并重新启动服务器。但是，升级第一位或第二位数字需要升级所有数据文件。将软件版本分开有助于升级。

每个服务器单一集群和数据库

以下数据库对象在 PostgreSQL 中是集群范围的，每个集群只有一个数据库是可取的

• 配置文件
• WAL（在线和归档）文件
• 表空间
• 用户帐户和角色
• 服务器日志文件

过去常用的数据库对象分离方式是使用多个数据库。在单个数据库服务器内分离数据库对象的另一种更易于管理的方法是使用模式。

要在服务器内分离 PostgreSQL 集群，需要创建不同的数据区域和 IP 端口号。但是，Solaris 的区域和 FreeBSD 的 jails 等操作系统的虚拟化功能，或者像 Xen 和 KVM 这样的虚拟化程序使得在一个主机内创建多个集群变得不必要。建议每个虚拟化主机只拥有一个 PostgreSQL 集群。

文件系统布局

为了创建最灵活和最易于管理的环境，将各种数据库组件分离到各自的文件系统中。创建以下文件系统（挂载点）

/pgarchive	包含归档日志文件的 DB 归档位置。
/pgbackup	包含物理和逻辑备份的 DB 备份位置。对于逻辑备份 (pg_dump)，使用 EXPORTS 作为子目录。对于物理备份，使用 FULL{date}，作为子目录的约定。但是，物理备份可以使用文件系统快照处理。稍后会详细介绍。
/pgcluster	PostgreSQL 集群数据目录（PGDATA 环境变量）。这将包含 PostgreSQL 集群的所有配置文件和目录。注意：在线 WAL 文件将位于 /pgcluster/pg_xlog 中。
/pglog	服务器日志文件的位置。
/pgdata-system	数据库默认表空间的位置。这将用于数据库的创建。数据库目录信息将被存储。
/pgdata-temp	数据库默认临时表空间的位置。这对于临时排序信息是必要的。注意：如果未定义临时表空间，则将使用默认表空间内的 pgsql_tmp 目录。
/pgdata-app_tblspc	应用程序表空间。每个模式至少应该有两个表空间。一个用于表，一个用于索引。

PostgreSQL 没有为其表空间和数据库对象定义大小限制；预计操作系统将管理已使用设备的大小。这就是为什么建议为每个表空间创建一个单独的挂载点（文件系统）。这增加了复杂性，尤其是在将存储和操作系统管理与数据库管理分离的组织中。但是，这种复杂性远不及分离和隔离数据库对象的优势。

希望文件系统增长和管理以分布式管理的形式进行。DBA 将获得卷管理器中的一组磁盘组，然后根据需要划分文件系统。 ZFS 是一个具有委托管理功能的文件系统的示例。

服务器配置信息

使用“连续归档”进行时间点恢复 (PITR)。

  archive_mode = on
  archive_command = 'cp %p /pgarchive/%f'
  wal_level = 'archive'

设置服务器日志文件轮转。（7 天或 10MB，以先到者为准）

  log_directory = '/pgcluster/log'
  log_filename = 'postgresql-%Y%m%d_%H%M%S.log'
  log_rotation_age = 7d
  log_rotation_size = 10MB
  log_truncate_on_rotation = off
  log_line_prefix = '%t c%  '

在服务器日志文件中收集连接信息。

  log_connections = on
  log_disconnections = on

记录 DDL 事务。

  log_statement = 'ddl'

启用 SSL 通信。

  ssl = on
  ssl_ciphers = 'ALL'

删除默认 postgres 数据库或拒绝对其进行远程连接。

创建一个数据库来放置应用程序模式。删除 public 模式。

帐户管理

避免以数据库超级用户 postgres 的身份连接到数据库服务器。管理流程（如备份）很可能仍然使用 postgres 帐户；但是，用户和应用程序不应该使用。只允许本地连接到 postgres 数据库用户。注意：在 9.1 版本中，使用 pg_hba.conf 中的 local with auth-option peer 身份验证模型是最可取的。

为所有将直接连接到数据库的用户创建单独的帐户。DBA 需要超级用户权限，部署代表需要操作模式对象定义的权限，开发人员需要对应用程序对象的 select 权限来诊断生产问题。

在可能的情况下，使用集中式企业帐户（例如 LDAP）进行用户帐户身份验证。

创建与应用程序模式同义的帐户。避免连接到这些模式帐户。事实上，在可能的情况下，将帐户设置为 NOLOGIN。

当用户将对象定义部署到应用程序模式时，他们需要拥有相应的权限。授予这些用户应用程序模式的角色足以允许此活动。确保对于任何新创建的对象，所有权都设置为与模式匹配的帐户。

为了简化帐户管理，使用角色向用户授予权限，而不是直接授予权限。

一般来说，应用程序使用池化（共享）帐户连接到数据库。确保这些帐户只能从定义的应用程序服务器连接到数据库。用户不应该被允许使用这些池化帐户直接登录数据库。

物理数据库备份

要执行在线备份，数据库必须处于归档日志模式。请参阅 PostgreSQL 参考手册中的 连续归档和时间点恢复 章节。

使用像 ZFS 这样的高级文件系统，该文件系统具有快照/回滚功能，具有一些显著的优势。将数据库置于热备份模式，对构成数据库存储的文件系统进行快照，并将数据库移出备份模式，这比在备份过程中使用 tar 或 cpio 将所有数据文件复制到备用位置更可取。

在进行快照后，将数据文件复制到备用位置以安全保存仍然是一个选项；但是，数据库只在进行快照时处于热备份模式一小段时间。对于大多数恢复情况，使用在线备份（快照）而不是“从磁带拉取”。

文件系统委托管理是管理文件系统快照的一个优势。DBA 必须与系统和存储管理协调，以促进最佳实践。