应用数据库架构建议

在一个 PostgreSQL 数据库集群中，分离和命名空间对象的常用方法是通过管理数据库和架构数据定义.

建议创建一个包含多个命名架构的单一数据库。这与常见的（且较旧的）在“public”架构中创建多个数据库并存储对象的做法不同。此外，建议移除“public”架构。

以下是遵循此建议的一些优势：

可以从单个数据库连接访问跨架构对象。
授予架构访问权限是通过 GRANT 语句执行的，而不是通过重新配置 pg_hba.conf 文件。
架构是用于对象分离和命名空间的 ANSI 标准。
在一个服务器（PostgreSQL 集群）中仅管理一个数据库。

使用独立数据库方法的一个优势是连接到数据库的会话不能跨数据库边界。对象的隔离更完整。但是，如果希望更多地隔离对象（例如，不同的环境），建议创建一个新服务器。

注意：在数据库级别进行隔离可以与 db_user_namespace 配置参数结合使用，用于多租户托管。架构隔离再次成为在每个托管数据库中分离数据库组件的重要功能。

创建表空间

为每个应用程序创建一个合适的表空间集。这提供了将存储从一个应用程序物理分离到另一个应用程序的功能。它还可以提供在应用程序内分离存储的功能。对表和索引进行不同的存储将提高管理灵活性，并可能提供额外的性能。

PostgreSQL 期望主机操作系统提供设备管理。为每个表空间创建一个文件系统，以分离和控制表空间存储。以下是以 ZFS 为例，使用名为 pgdatapool 和 pgindexpool 的存储池。将为应用程序使用两个 10 GB 的表空间，一个用于表，一个用于索引。

Unix 命令

  zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data
  zfs set refquota=10G pgdatapool/app1_data
  zfs set refreference=10G pgdatapool/app1_data
  zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index
  zfs set refquota=10G pgindexpool/app1_index
  zfs set refreference=10G pgindexpool/app1_index

SQL 命令

  create tablespace app1_data location '/pgdata/app1_data';
  create tablespace app1_index location '/pgdata/app1_index';

创建账户和角色

PostgreSQL 数据库对象拥有一个账户（角色）。创建一个与将命名空间对象的架构同义的账户。此账户将用作对象的拥有者。出于安全目的，不允许任何人连接到拥有应用程序数据库对象的账户。此账户需要访问之前创建的表空间。创建应用程序对象时，确保所有权设置正确。

  create user app1 nologin;
  grant create on tablespace app1_data to app1;
  grant create on tablespace app1_index to app1;

通过使用角色，向用户或应用程序账户授予数据库对象权限。为应用程序创建一组适当的角色。为了简单起见，至少创建两个角色，一个用于应用程序的读/写权限，另一个用于查询权限。创建应用程序的数据库对象时，向角色执行相应的授权操作。

  create role app1_role;
  create role app1_query_role;

应用程序通常使用来自应用程序服务器的池化（共享）账户连接到数据库。限制对这些账户及其凭据的了解。如果可能，使用 pg_hba.conf 中的定义来确保这些账户只能从定义的应用程序服务器连接到数据库。

  create user app1_pool password 'app1_pool';
  grant app1_role to app1_pool;

创建应用程序架构

创建应用程序架构，并向应用程序用户和角色授予相应的权限。确保在定义的架构中创建应用程序对象，并设置相应的对象所有权。

  create schema app1 authorization app1;
  grant usage on schema app1 to app1_role;
  grant usage on schema app1 to app1_query_role;

免责声明

这些是经验丰富的 DBA 的建议。可以遵循或忽略它们。就个人而言，通过遵循这些管理应用程序架构的几个简单方法，我的工作变得更容易了。

应用数据库架构建议

目录