都 6202 年了, 还在用 MySQL 吗?

一个简单的 UTF-8 编码, 在 MySQL 中竟然还存在多个字节长度不同的版本。utf8 和 utf8mb4 这个问题存在多年, 早就应该具备的基础功能, 却拖到 MySQL 8.0 才姗姗来迟, 甚至还被当作重大更新大肆宣传。

与此同时, PostgreSQL 早就已经支持完整的 4 字节 UTF-8 编码, 并且默认使用, 不需要等到遇到编码问题之后, 再去学习 UTF-8 的 3 字节和 4 字节编码区别。

我从 PostgreSQL 13 一直使用到了 PostgreSQL 18, 是时候整理一下 PostgreSQL 的安装、用户权限管理以及数据库备份恢复笔记了。

安装 PostgreSQL 18

通过 PostgreSQL 官方 PGDG 软件仓库安装 PostgreSQL 18

禁用系统仓库 PostgreSQL

Rocky 9 在 AppStream 仓库中提供了由 Rocky Linux 官方维护的 PostgreSQL 模块, 默认版本为 13, 同时也提供了 15、16、18 等版本供用户选择。

与 PostgreSQL 官方 PGDG 仓库相比, Rocky Linux 系统仓库的软件包由不同团队维护, 安装目录也有所不同, 并且不支持同时安装多个 PostgreSQL 主版本。

首先, 禁用系统自带的 PostgreSQL 模块, 避免与后续安装的 PGDG 软件包发生冲突

yum module disable postgresql -y

查看当前可用的 PostgreSQL 模块

yum module list postgresql

Rocky Linux 9 - AppStream
Name               Stream        Profiles                  Summary
postgresql         15 [x]        client, server [d]        PostgreSQL server and client module
postgresql         16 [x]        client, server [d]        PostgreSQL server and client module
postgresql         18 [x]        client, server [d]        PostgreSQL server and client module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

添加官方 PGDG 仓库

安装 PostgreSQL 官方 PGDG 仓库, 并禁用不需要的 PostgreSQL 版本仓库

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum config-manager --set-disabled pgdg14 pgdg15 pgdg16 pgdg17

更新软件包缓存

yum makecache

安装 PostgreSQL 18

安装 PostgreSQL 18 Server 软件包

yum install postgresql18-server -y

初始化数据库

安装完成后, 执行初始化命令, 创建 PostgreSQL 数据库集群

/usr/pgsql-18/bin/postgresql-18-setup initdb

启动 PostgreSQL 服务

设置 PostgreSQL 服务开机自动启动, 并立即启动服务

systemctl enable --now postgresql-18
systemctl status postgresql-18

设置超级用户密码

PostgreSQL 安装完成后, 系统会默认创建一个名为 postgres 的系统用户, 同时创建对应的 PostgreSQL 超级用户。

首先, 切换到 postgres 系统用户

[root@server ~]# su - postgres

进入 PostgreSQL 命令行

[postgres@server ~]$ psql
psql (18.6)
Type "help" for help.

使用 \password 命令设置超级用户密码, 按照提示输入两次新密码。

这里不直接在 SQL 中填写密码, 可以避免明文密码出现在命令历史记录中。

postgres=# \password postgres

设置完成后, 退出 PostgreSQL 命令行

postgres=# \q

创建数据库备份目录

PostgreSQL 默认使用 postgres 系统用户运行, 其 Home 目录为 /var/lib/pgsql, 数据库集群的数据文件也默认存放在该目录下。为了避免备份文件与数据库数据混放, 建议单独创建一个备份目录。

可以通过以下命令查看当前目录

[postgres@server ~]$ pwd
/var/lib/pgsql

创建专门用于存放 PostgreSQL 数据库备份的目录

mkdir /home/postgres

将目录所有权设置为 postgres 用户和用户组, 确保 PostgreSQL 可以正常读写备份文件

chown -R postgres:postgres /home/postgres/

检查目录及权限

ll /home/postgres/

后续执行数据库备份和恢复操作时, 统一使用该目录存放备份文件。

创建应用程序专用账号

实际应用程序不应该直接使用 PostgreSQL 的 postgres 超级用户连接数据库。建议为每个应用程序创建独立的数据库账号, 并根据实际需求授予相应权限。

创建一个名为 acesheep 的应用程序专用账号, 并以 blog_db 作为示例数据库。

权限设计如下:

• postgres: 负责数据库管理, 创建和删除表、修改表结构、创建索引以及管理对象权限
• acesheep: 仅允许查询、插入、更新和删除数据, 不允许创建、删除或修改数据库对象, 也不能管理对象权限

创建用户并设置密码

首先, 切换到 PostgreSQL 管理员账号, 进入 PostgreSQL 命令行

su - postgres
psql

创建应用程序专用用户

CREATE USER acesheep WITH LOGIN;

使用 \password 命令设置用户密码。会提示输入两次新密码, 不要直接在 SQL 语句中填写密码, 可以防止明文密码出现在命令历史记录中。

\password acesheep

创建数据库并导入备份

由 postgres 管理员创建数据库, 数据库所有权仍然归 postgres 所有, 避免应用程序账号拥有数据库管理权限。

createdb -O postgres blog_db

如果需要从已有的 Custom 格式备份文件恢复数据库, 可以使用 pg_restore

pg_restore --no-owner --no-acl -d blog_db /home/postgres/blog_db_xxx.dump

其中:

• --no-owner: 忽略备份文件中原有的对象所有权, 恢复后的对象由当前执行恢复操作的用户拥有
• --no-acl: 忽略备份文件中原有的权限设置

这样可以避免原有数据库用户和权限设置影响当前数据库。

授予数据操作权限

数据库创建并完成数据恢复后, 需要为应用程序账号 acesheep 授予相应的数据操作权限。

首先, 连接到 blog_db 数据库

psql -d blog_db

授予数据库连接权限

首先撤销 acesheep 已有的数据库权限, 再单独授予连接权限

REVOKE ALL ON DATABASE blog_db FROM acesheep;
GRANT CONNECT ON DATABASE blog_db TO acesheep;

授予 Schema 访问权限

从 PostgreSQL 15 开始, 默认情况下, public Schema 不再向 PUBLIC 角色授予 CREATE 权限, 普通用户默认不能在其中创建数据库对象。

USAGE 允许用户访问 Schema 中的对象, 而 CREATE 权限允许用户在 Schema 中创建表、索引及其他对象。

为了明确限制应用程序账号的权限, 这里仍然显式撤销 acesheep 的 CREATE 权限, 并授予 USAGE 权限

REVOKE CREATE ON SCHEMA public FROM acesheep;
GRANT USAGE ON SCHEMA public TO acesheep;

授予现有数据表的 CRUD 权限

允许 acesheep 查询、插入、更新和删除现有数据表中的数据

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO acesheep;

授予现有序列的使用权限

序列权限主要用于支持 SERIAL 等字段自动生成 ID 时调用 nextval()

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA public
TO acesheep;

设置未来新建对象的默认权限

上面的授权只对当前已经存在的数据表和序列生效。

如果以后仍然由 postgres 管理员创建新表或序列, 需要额外设置默认权限, 否则 acesheep 将无法操作这些新创建的对象。

为未来新建的数据表授予 CRUD 权限

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO acesheep;

为未来新建的序列授予使用权限

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO acesheep;

注意: ALTER DEFAULT PRIVILEGES 只影响指定创建者未来创建的对象。这里指定的是 postgres, 因此如果使用其他数据库用户创建表或序列, 需要另外设置对应的默认权限。

至此, 应用程序专用账号的权限配置完成。

最终权限结构

acesheep 只负责应用程序的数据读写, 所有数据库结构变更和权限管理均由 postgres 管理员负责。

PostgreSQL 数据库备份与恢复

PostgreSQL 提供了 pg_dumpall、pg_dump 和 pg_restore 等数据库备份与恢复工具, 可以根据实际需求选择全量备份或单数据库备份。

所有数据库备份文件统一存放在 /home/postgres/ 目录中

全量数据库备份

使用 pg_dumpall 可以备份整个 PostgreSQL 实例中的所有数据库, 以及全局对象, 包括数据库用户、角色和权限等。

生成的 all_databases.sql 是 SQL 文本格式的备份文件。

执行全量备份

首先切换到 postgres 系统用户, 然后执行备份

su - postgres

pg_dumpall -U postgres > /home/postgres/all_databases.sql

恢复全量备份

恢复时, 使用 psql 执行备份文件中的 SQL 语句, 重新创建数据库、角色及相关对象

su - postgres

psql -U postgres -f /home/postgres/all_databases.sql

单数据库备份

如果只需要备份某一个数据库, 可以使用 pg_dump

以下提供两种等效的写法, 均使用 PostgreSQL 的 Custom 格式生成备份文件, 后续可以通过 pg_restore 进行恢复或选择性恢复数据库对象。

# pg_dump 将备份内容输出到标准输出, 由 Shell 的 > 重定向负责创建文件并写入备份数据
pg_dump -Fc blog_db > /home/postgres/blog_db_`date +%F_%H-%M-%S`.dump

# 通过 -f 参数指定备份文件, 由 pg_dump 自行创建文件并写入备份数据
pg_dump --format=c -d blog_db -f /home/postgres/blog_db_`date +%F_%H-%M-%S`.dump

查看备份文件

备份完成后, 可以查看备份目录, 确认备份文件是否成功生成

ll /home/postgres/

单数据库恢复

使用 pg_restore 可以恢复 Custom 格式的数据库备份

可选操作: 查看备份文件内容

在正式恢复之前, 可以先查看备份文件中包含哪些数据库对象。该命令只读取备份文件, 不会实际执行恢复操作。

pg_restore --list /home/postgres/blog_db_xxx.dump

首先创建需要恢复的数据库, 并将所有权指定为 postgres

createdb -O postgres blog_db

然后执行恢复

pg_restore --no-owner --no-acl -d blog_db /home/postgres/blog_db_xxx.dump

如果目标数据库已经存在, 并且希望先删除备份中对应的对象再恢复, 可以使用

pg_restore --clean --if-exists --no-owner --no-acl -d blog_db /home/postgres/blog_db_xxx.dump

其中:

• --clean: 恢复前删除备份中包含的数据库对象
• --if-exists: 删除对象时, 如果对象不存在则不报错
• --no-owner: 忽略备份文件中的原始对象所有权
• --no-acl: 忽略备份文件中的原始权限设置

需要注意, --clean 只会删除备份文件中涉及的对象, 并不会清空目标数据库中的所有其他对象。

如果希望完全重建数据库, 建议先删除旧数据库, 再重新创建数据库并执行恢复。

注意: 使用 --no-acl 恢复数据库时, 备份文件中的原有权限设置不会被恢复。如果数据库使用了独立的应用程序账号, 例如前文创建的 acesheep, 恢复完成后需要重新执行对应的权限授权命令, 确保应用程序可以正常访问数据库。