🐘 pgfine

用于帮助进行postgresql数据库模式更新、迁移和版本控制的CLI工具。

pgfine的目标是尽可能提供声明式的项目结构

• 所有数据库对象都有相应的创建脚本。
• 迁移脚本只需要用于更新数据全对象 - 表。

安装

从 crates.io

cargo install pgfine

从 仓库

git clone https://gitlab.com/mrsk/pgfine
cargo install --path ./pgfine

创建新项目

• 选择一个版本控制的目录。
• 创建git忽略的 env-local-db-0.sh （例如）文件如下

# All variables are mandatory to avoid mixed environments.
# Connection strings: https://postgresql.ac.cn/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
# no need to provide sslmode parameter.

# credentials to connect to target db to perform updates and migrations.
# role and database will be created if missing (using admin connection).
export PGFINE_CONNECTION_STRING="..."

# credentials for creating a new database  refereced in the above connection string (usually postgres db with user postgres).
export PGFINE_ADMIN_CONNECTION_STRING="..."

# path pointing to pgfine project, a good choice would be "./pgfine"
export PGFINE_DIR="./pgfine"

# role prefix to make them unique per database.
# if your plan is to have a single db per postgresql instance you can set it to "" and forget it.
# role names should be referenced like "{pgfine_role_prefix}role_name" in all the scripts.
# if you plan to use global roles you should create them manualy or in ./pgfine/create/ scripts
export PGFINE_ROLE_PREFIX="prod_"

# path to root certificate. No tls mode will be attempted if this is set to an empty string.
# https://postgresql.ac.cn/docs/current/ssl-tcp.html
export PGFINE_ROOT_CERT=""

使用 pgfine 时需要激活环境变量。您可以通过运行以下命令来完成此操作

source env-local-db-0.sh

运行以下命令初始化 pgfine 项目

pgfine init

这将创建用于存储所有pgfine项目数据的目录

./pgfine/
├── create
│   ├── 00-create-role.sql
│   └── 01-create-database.sql
├── drop
│   ├── 00-drop-database.sql
│   └── 01-drop-role.sql
├── functions
├── migrations
├── roles
├── tables
├── schemas
│   └── public.sql
├── constraints
├── triggers
├── policies
├── extensions
├── types
└── views

如果需要，修改新创建的 ./pgfine/create/*.sql 和 ./pgfine/drop/*.sql 脚本。

创建数据库

• 如果需要，修改 ./pgfine/create/* 脚本。
• 设置环境和运行

pgfine migrate

将创建两个额外的表

• pgfine_objects：包含管理pgfine对象及其哈希值的列表。

      create table if not exists pgfine_objects (
          po_id text primary key,
          po_md5 text,
          po_script text,
          po_path text,
          po_depends_on text[],
          po_required_by text[]
      );
  

• pgfine_migrations：包含已执行迁移的列表。选择最大值应显示数据库的当前状态。第一个迁移将作为空字符串插入。

      create table if not exists pgfine_migrations (
          pm_id text primary key
      );
  

修改数据库

• 将 ./pgfine/**/*.sql 中的任何更改应用于数据库对象。
• 应通过 ./pgfine/migrations/* 脚本额外实现表更改。
• 运行

pgfine migrate

• 测试您的新鲜数据库。
• 将所有文件提交到版本控制。

以下表格总结了您应该交付更改的方式（auto表示只需修改相应的对象脚本即可）

在更新期间，策略被删除（如果需要）的短时间内可能会出现安全问题。一旦应用更新到单个事务中（需要进一步调查），这应该得到解决。

迁移脚本

仅通过删除表并创建新表无法应用表更改，而不丢失数据。因此，必须使用迁移脚本来提供这些更改。pgfine项目中的表脚本必须表示应用所有迁移脚本后的最新版本的对象。

脚本位于 ./pgfine/migrations/。在更新所有其他数据库对象之前，这些脚本将按字母顺序执行。

如果您的迁移依赖于其他数据库对象（例如与函数关联的新表列），建议在迁移脚本中创建这些对象（如果不存在）。这是为了避免在旧版本数据库中提到的对象尚未存在时出现问题。在未来，将开发模式验证过程以显示哪些迁移脚本已损坏。

回滚

• 从之前的提交恢复数据库对象脚本
• 如果涉及更改表，则创建新的迁移脚本。
• 以相同的方式应用更改

pgfine migrate

数据库对象

数据库对象包括

• 表
• 视图
• 触发器
• 约束
• 策略
• 函数
• 角色
• 模式
• 扩展
• 类型

数据库对象的文件名必须遵循特定格式

• 表：./pgfine/tables/<schema>.<name>.sql
• 视图：./pgfine/views/<schema>.<name>.sql
• 函数：./pgfine/functions/<schema>.<name>.sql
• 触发器：./pgfine/triggers/<schema>.<table>.<name>.sql
• 约束：./pgfine/constraints/<schema>.<table>.<name>.sql
• 策略：./pgfine/policies/<schema>.<table>.<name>.sql
• 角色：./pgfine/roles/<name>.sql
• 模式：./pgfine/schemas/<name>.sql
• 扩展：./pgfine/extensions/<name>.sql
• 类型：./pgfine/types/<schema>.<name>.sql

每个文件都包含创建该对象的脚本。

通过删除对象并创建新对象来执行更新。

删除脚本由对象类型和对象名称生成。表永远不会自动删除 - 必须使用迁移脚本或手动进行删除/更新。

表

示例 ./pgfine/tables/public.table0.sql

create table table0 (
    id bigserial primary key
);

-- create indexes
-- create constraints
-- create rules
-- create triggers

表约束和索引可以与表一起存储。但是，要修改它们，您将不得不编写迁移脚本。

如果您有循环外键依赖项，应将这些约束定义在单独的 ./pgfine/constraints/ 文件中，以打破循环。

视图

示例 ./pgfine/views/public.view0.sql

-- it is recommended to include "or replace", otherwise it will be dropped and created again each time changes are made.
create or replace view view0 as
select t0.id
from table0 t0
join table1 t1 on t1.id = t0.id

-- create indexes maybe

函数

在更新过程中，所有被覆盖的函数都将被删除（如果已修改）并重新创建。

在删除和创建函数时，可能会应用一些默认权限。在函数脚本中，您可能希望添加额外的语句来更改默认权限

revoke execute on function some_function from public;

约束

示例 ./pgfine/constraints/public.table1.t0_id_fk.sql

alter table table1
add constraint t0_id_fk foreign key (t0_id) references table1 (id);

策略

示例 ./pgfine/policies/public.table1.policy1.sql

create policy policy1
on public.table1;

策略脚本不应针对特定角色。角色分配应在角色脚本中通过更改给定策略来完成。

角色

示例 ./pgfine/roles/role0.sql

create role {pgfine_role_prefix}role0;
grant usage on schema schema0 to {pgfine_role_prefix}role0;

所有权限分配应在角色脚本中完成。在执行 pgfine migrate 时，将始终删除角色对象并重新创建。这是为了避免在其他对象重新创建时分配默认权限。

命令

pgfine init

• 在路径 PGFINE_DIR 初始化 pgfine 项目。

pgfine migrate

如果数据库缺失

• 执行 ./pgfine/create/ 脚本来创建角色和数据库（使用管理员连接）。
• 创建 pgfine 表。
• 创建 pgfine 项目中定义的所有数据库对象。

如果数据库存在

• 尝试通过比较 pgfine_objects 表和项目内容来删除所有脏对象。
• 尝试创建所有缺失的对象。

pgfine 删除--否-笑话数据库

• 强制删除 pgfine_objects 表中找到的所有角色。
• 强制删除项目中找到的所有角色。
• 执行 ./pgfine/drop/ 脚本以删除角色和数据库（使用管理员连接）。

pgfine 删除--否-笑话对象

• 遍历项目中定义的所有对象以及 pgfine_objects 表，并将它们删除。
• 截断 pgfine_objects 和 pgfine_migrations 表。
• 可以提供其他标志 --drop-public-schema，--drop-pgfine-tables。

假设

• 密码、数据库名和角色只能包含字母数字字符和下划线。
• 使用简单全词搜索，利用文件名信息跟踪对象之间的依赖关系，假设默认 public 模式。
• 触发器、约束和政策被认为不是其他对象所需的（始终安全删除）。
• 假设 ./pgfine/migrations/ 目录中的每个新文件都是按字母顺序递增的。
• 空字符串是第一个迁移的名称（如果没有迁移则插入）
• {pgfine_role_prefix} 文本不应用于其他目的，例如在脚本中使用数据库-角色前缀。
• 对于模式、类型和扩展对象，不进行 md5 比较，更改应使用迁移脚本进行。如果脚本被删除，将尝试删除它们。
• 默认删除数据库脚本假定使用 postgres v13，如果您使用较低版本，则应添加删除连接的脚本。

替代方案

在当前阶段，pgfine 不是世界上最好的东西。您也可能想查看这些替代方案

• refinery
• flyway
• diesel.rs
• dbmigrate
• 等等...

破坏性变更

1 -> 2

• 对象类型现在是对象 ID 的一部分。

迁移步骤

• 使您的数据库保持最新。通过运行 pgfine migrate 来实现。
• 更新 pgfine
• 删除表 pgfine_objects 和 pgfine_migrations
• 运行 pgfine migrate

2.0.0 版本后的计划

• 验证对象是否自引用
• 在应用所有其他更新之前，验证表模式当哈希值发生变化时（通过创建单独的 DB 并比较？）
• PGFINE_DROP_MODE 变量用于保护生产环境
• 示例项目在 ./example/
• 文档 https://documentation.divio.com/ https://jacobian.org/series/great-documentation/
• ./pgfine/initial/ 在创建数据库之后执行
• ./pgfine/final/ 在创建数据库对象之后执行
• 如果可能，在单个事务中执行操作
• 可配置的搜索模式
• 使执行顺序确定
• 在解决依赖关系时忽略脚本中的注释
• 支持稳定的 rust
• 从现有数据库生成项目
• 为表所需的函数提供解决方案？
• 用户定义的删除脚本
• 尝试在不依赖的情况下进行删除
• 删除所有具有相同名称的函数