跳转至

PostgreSQL 服务器搭建指南

PostgreSQL 是一个开源的、企业级的对象关系数据库系统(ORDBMS),能够在集群中管理大量数据,强调可靠性、可扩展性和功能稳定等特性。

1 安装 PostgreSQL

可以通过如下命令安装 PostgreSQL 和相关工具:

sudo dnf install postgresql-server

查看安装的 PostgreSQL 系列包:

rpm -qa | grep postgresql

2 初始化 PostgreSQL

2.1 初始化数据库

在初始化数据库之前,需先通过 systemctl status postgresql.service 查看服务状态是否为 enabled。 若非 enabled,请使用以下命令启用 PostgreSQL 服务:

sudo systemctl enable postgresql.service

然后初始化 PostgreSQL 数据库:

sudo postgresql-setup --initdb

2.2 启动 PostgreSQL 服务

使用以下命令启动 PostgreSQL 服务:

sudo systemctl start postgresql.service

2.3 查看 PostgreSQL 状态

运行以下命令以检查 PostgreSQL 服务是否正在运行:

sudo systemctl status postgresql.service

如果安装启动成功,您将看到类似于以下内容的输出:

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; v>
     Active: active (running) since Fri 2023-05-26 11:18:25 CST; 3s ago
    Process: 575849 ExecStartPre=/usr/libexec/postgresql-check-db-dir postg>
   Main PID: 575851 (postmaster)
      Tasks: 8 (limit: 9169)
     Memory: 16.4M
        CPU: 50ms
     CGroup: /system.slice/postgresql.service
             ├─575851 /usr/bin/postmaster -D /var/lib/pgsql/data
             ├─575852 "postgres: logger "
             ├─575854 "postgres: checkpointer "
             ├─575855 "postgres: background writer "
             ├─575856 "postgres: walwriter "
             ├─575857 "postgres: autovacuum launcher "
             ├─575858 "postgres: stats collector "
             └─575859 "postgres: logical replication launcher " 

3 配置 PostgreSQL

PostgreSQL 的数据和配置文件,默认位于 /var/lib/pgsql/data/ 路径。

配置文件主要有:

  • postgresql.conf : 用于设置数据库集群参数;
  • pg_hba.conf : 用于为 PostgreSQL 数据库配置客户端身份验证。您可以在这里设置各种身份验证方法、使用的 IP 地址范围等;
  • postgresql.auto.conf : 保存基本的PostgreSQL设置,类似于 postgresql.conf . 但是,此文件受服务器控制,由 ALTER SYSTEM 编辑,不能手动编辑
  • pg_ident.conf : 用于为 PostgreSQL 定义用户名称映射。这允许将操作系统中的用户(或一组用户)映射到 PostgreSQL 中的用户;

3.1 数据库集群参数配置

postgresql.conf 文件中,常见参数介绍如下:

  • 监听地址 (listen_addresses) - 默认情况下,PostgreSQL 只在本地进行监听。为了让其监听所有可用的 IP 地址,修改如下:
listen_addresses = '*'
  • 端口 (port) - 若要更改服务器监听的端口(默认是 5432),设置如下:
port = 5432
  • 连接数 (max_connections) - 您可以修改此参数以允许的最大连接数。根据可用资源和应用程序需求进行设置:
max_connections = 100
  • 共享缓冲区 (shared_buffers) - 此参数设置 PostgreSQL 使用的共享内存量。这将影响缓存大小,推荐设置为物理内存的 25%:
shared_buffers = 2GB

3.2 客户端身份验证配置

pg_hba.conf 文件控制客户端身份认证。这些设置是按行组织,以规则的形式呈现。通常,规则分为四个字段:

  • 连接类型 (connection type) - 表明规则适用于何种类型的连接:本地(local)或主机(host)。
  • 数据库 (database) - 规定哪些数据库应用此规则。可以为"all"、"sameuser" 或逗号分隔的数据库名称列表。
  • 用户 (user) - 规定哪些用户应用此规则。可以为"all" 或逗号分隔的用户名列表。
  • 身份验证方法 (authentication method) - 说明身份验证的细节。例如,可以使用 "trust"(免密)、 "md5"(密码)或 "peer"(基于操作系统账户)等方法。

如果要允许特定 IP 地址范围中的所有客户端连接到服务器并使用 md5 加密,则可以使用以下设置:

host    all             all             192.168.0.0/24            md5

3.3 用户映射配置(可选)

pg_ident.conf 配置文件用于为 PostgreSQL 定义用户名称映射。这允许将操作系统中的用户(或一组用户)映射到 PostgreSQL 中的用户。这对于受到限制的环境和增强安全性的场景很有用。

该文件由多个映射组成,每个映射都有一个映射名称。每个映射包含一个或多个映射规则。

映射规则由以下字段组成:

  • 映射名称 (map name) - 唯一名称,用于识别身份验证规则。
  • 系统用户名 (system username) - 操作系统中的用户
  • PostgreSQL 用户名 (PostgreSQL username) - PostgreSQL 中映射到操作系统用户的用户

例如,要将操作系统中名为 "osuser" 的用户映射到 PostgreSQL 中名为 "pguser" 的用户,请在 pg_ident.conf 文件中添加以下映射:

mapname      system_username     pg_username
example_map  osuser              pguser

然后,为了应用这个映射,您需要在 pg_hba.conf 中进行相应调整。将身份验证方法更改为 "ident" 并设置 "map" 选项:

local    dbname      pguser      ident map=example_map

3.4 配置生效

确保修改后保存文件,然后重新启动 PostgreSQL 服务以使更改生效:

sudo systemctl restart postgresql.service

4 使用 PostgreSQL

4.1 创建用户和数据库

切换到 postgres 用户:

sudo su - postgres

输入 psql 打开 PostgreSQL 交互终端:

psql (14.3)
Type "help" for help.

然后可键入 \conninfo 查看详细信息:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

通过运行以下命令创建一个新用户 test_user, 其密码为 mypasswd,赋予其 CREATEROLE CREATEDB权限:

postgres=# CREATE USER test_user WITH PASSWORD 'mypasswd' CREATEROLE CREATEDB;

然后创建一个名为 test_db 的数据库:

   CREATE DATABASE test_db;

退出交互终端:

\q

退出 postgres 用户:

exit

4.2 配置远程连接和密码验证

打开 /var/lib/pgsql/data/pg_hba.conf文件,在文件末尾添加以下行以允许通过密码验证来接受外部连接:

host    all             all             0.0.0.0/0               scram-sha-256

然后编辑 /var/lib/pgsql/data/postgresql.conf 文件,以便从任何地址都可以连接数据库:

#listen_addresses = 'localhost'
#password_encryption = scram-sha-256     # scram-sha-256 or md5

取消注释并将其更改为:

listen_addresses = '*'
password_encryption = scram-sha-256     # scram-sha-256 or md5

重新启动 PostgreSQL 服务,使配置生效

sudo systemctl restart postgresql.service

4.3 登录数据库

通过以下命令,使用 test_user 登录到 test_db 数据库:

psql -U test_user -h 127.0.0.1 -d test_db

输入用户密码并按 Enter。如果一切正常,您应该看到如下输出:

Password for user test_user: 
psql (14.3)
Type "help" for help.

键入 \conninfo可看到以下输出:

test_db-> \conninfo
You are connected to database "test_db" as user "test_user" on host "127.0.0.1" at port "5432".

5 加密 PostgreSQL

通常 PostgreSQL 服务器的连接并未经过加密,存在一定安全风险。为了使数据库传输更安全可靠,您可以为 PostgreSQL 服务器配启用TLS 加密。

5.1 生成证书和私钥

首先,需要生成一份服务器证书和私钥,可以使用自签名证书或购买商业证书。 这里为示范,故生成并采用自签名证书:

  1. 首先,确保已安装了 OpenSSL。如果尚未安装,请使用以下命令进行安装:
sudo dnf install openssl
  1. 接下来,进入默认 Postgres 数据目录:
cd /var/lib/pgsql/data
  1. 生成服务器私钥:
sudo openssl genrsa -out server.key 2048
  1. 生成自签名证书:
sudo openssl req -new -x509 -key server.key -out server.crt -days 3650 -subj "/CN=<your_domain>"

注意:将 <your_domain> 替换为您的服务器域名。如果没有域名,可以使用服务器的 IP 地址。

  1. 为证书和私钥设置正确的所有者和权限,使其私钥只能由所有者读取:
sudo chown postgres:postgres /var/lib/pgsql/data/server.{key,crt}
sudo chmod 0400 /var/lib/pgsql/data/server.key

5.2 配置 PostgreSQL 服务器使用 TLS 加密

现在我们已经有了证书和私钥,下一步是为 PostgreSQL 服务器启用 TLS / SSL。

编辑 /var/lib/pgsql/data/postgresql.conf 文件,配置以下参数:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

这将启用 SSL,并确保 PostgreSQL 使用先前创建的证书和私钥文件。

5.3 配置客户端连接的 TLS 加密验证

然后为客户端连接配置 TLS 加密验证,编辑 /var/lib/pgsql/data/pg_hba.conf 文件,为所需的用户和数据库将所有 host 条目更改为 hostssl。例如:

hostssl    all             all             0.0.0.0/0            scram-sha-256

也可以针对指定数据库和用户配置 TLS 加密:

hostssl test_db test_user   127.0.0.1/32        scram-sha-256

最后,重新启动 PostgreSQL 服务,以使更改生效:

sudo systemctl restart postgresql

现在,PostgreSQL 服务器上的连接应已启用 TLS 加密。

5.4 验证 TLS 加密

执行以下指令并键入密码,以 ·test_user 身份登录 test_db

psql -U test_user -h 127.0.0.1 -d test_db

可看到如下 TLS 相关打印信息:

Password for user test_user: 
psql (14.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

登录后,也可输入 \conninfo ,查看到 protocol: TLSv1.3 即表明开启成功:

test_db=> \conninfo
You are connected to database "test_db" as user "test_user" on host "127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

6 备份 PostgreSQL

定期备份数据库是非常重要的,这将使您能够在发生数据丢失或损坏时恢复数据。 在本章中,将介绍两种 PostgreSQL 备份方法:SQL 文件备份和物理文件备份。

6.1 SQL 文件备份 (使用 pg_dump)

pg_dump 是一种备份 PostgreSQL 数据库的实用工具,它将数据库架构和数据生成为 SQL 脚本文件。在需要恢复数据库时,可以运行这个 SQL 脚本文件。SQL 转储可以不受机器架构、服务器版本限制,运行期间不会阻止数据库的正常运转。

以下是使用 pg_dump 备份数据库的方法:

以有访问权限的用户身份进行登录:

su - postgres

使用 pg_dump 备份数据库。将 <your_database> 替换为要备份的数据库名称,将 backup.sql 替换为要创建的备份文件名:

pg_dump <your_database> > backup.sql

现在在当前目录下会有一个名为 backup.sql 的 SQL 文件,其中包含了数据库的完整备份。

pg_dump 类似, pg_dumpall 也可备份数据。只不过前者用于单个数据库备份,后者可用于数据库集群备份:

pg_dumpall  > backupall.sql

6.2 物理文件备份

物理文件备份是直接备份 PostgreSQL 数据库数据目录的整个文件结构,包括所有的表、索引和事务日志。这种方法在恢复时可能更快,但在迁移过程中可能较为复杂。以下是执行物理文件备份的方法:

  1. 停止 PostgreSQL 服务以确保所有事务已写入磁盘:
sudo systemctl stop postgresql
  1. 备份数据目录。将 /path/to/backup_folder 替换为备份最终存储的目录:
sudo cp -R /var/lib/pgsql/data /path/to/backup_folder
  1. 重新启动 PostgreSQL 服务:
sudo systemctl start postgresql

现在您已经获得了一个名为 data 的目录,其中包含物理文件备份。

6.3 恢复备份

要恢复 SQL 文件备份,只需运行该 SQL 脚本。首先以超级用户或数据库管理员身份,创建一个新的空数据库。将 <your_new_database> 替换为新数据库的名称:

createdb <your_new_database>

然后,运行以下命令以恢复数据库。将 <your_new_database> 替换为已创建的新数据库名称,将 backup.sql 替换为备份文件名:

psql <your_new_database> < backup.sql

若要恢复物理文件备份,请确保首先停止 PostgreSQL 服务,删除或移动数据目录,然后将备份文件复制回数据目录。接着重新启动 PostgreSQL 服务。

备份和恢复是数据库管理员的重要职责。确保您根据业务需求和策略定期创建备份。在执行任何有风险的操作(如升级、迁移等)之前,请务必备份您的数据。

7 参考

PostgreSQL 官方文档:https://www.postgresql.org/docs/14/server-start.html