PostgreSQL 简介

PostgreSQL 是一款功能强大的开源关系型数据库管理系统，具有以下核心特性：

ACID 事务支持
完善的 SQL 标准兼容（支持窗口函数、CTE、JSON 等）
可扩展性（支持自定义函数、存储过程、扩展插件）
多种索引类型（B-tree, Hash, GiST, SP-GiST, GIN, BRIN）
并行查询和高级优化器
地理空间数据支持（PostGIS 扩展）

为什么选择 PostgreSQL？

企业级可靠性：支持高并发和复杂查询
灵活的数据模型：支持 JSONB 文档存储
活跃的社区支持：持续的功能更新和安全补丁
跨平台：支持 Linux/Windows/macOS 等多种操作系统

Flyway 简介

Flyway 是一款数据库版本控制工具，主要功能包括：

数据库迁移脚本的版本管理
自动执行 SQL 或 Java 编写的迁移脚本
支持回滚和修复操作
与 CI/CD 流程无缝集成
多数据库支持（PostgreSQL, MySQL, Oracle 等）

工作原理：

创建迁移脚本文件（V{版本号}__{描述}.sql）
Flyway 检查数据库元数据表（默认 flyway_schema_history）
按版本顺序执行未应用的迁移脚本
记录执行状态和校验和

项目中如何使用 Flyway 升级 PostgreSQL

1. 添加依赖（Gradle 示例）

Tip

这里复制了实际项目中所需的数据库操作的依赖包，可以仅关注 postgres 和 flyway 的依赖导入，其他的主要是用于数据库操作和处理的，可以忽略～

// libs.version.toml
[versions]
postgresVersion = "42.7.3"
exposedVersion = "0.41.1"
hikariVersion = "4.0.3"
flywayVersion = "11.0.0"

[libraries]
postgresql = { module = "org.postgresql:postgresql", version.ref = "postgresVersion" }
exposed-core = { module = "org.jetbrains.exposed:exposed-core", version.ref = "exposedVersion" }
exposed-dao = { module = "org.jetbrains.exposed:exposed-dao", version.ref = "exposedVersion" }
exposed-jdbc = { module = "org.jetbrains.exposed:exposed-jdbc", version.ref = "exposedVersion" }
exposed-java-time = { module = "org.jetbrains.exposed:exposed-java-time", version.ref = "exposedVersion" }
zaxxer-hikaricp = { module = "com.zaxxer:HikariCP", version.ref = "hikariVersion" }
flyway-core = { module = "org.flywaydb:flyway-core", version.ref = "flywayVersion" }
flyway-database-postgresql = { module = "org.flywaydb:flyway-database-postgresql", version.ref = "flywayVersion" }

// build.gradle.kts
dependencies {
    implementation(libs.postgresql)
    implementation(libs.exposed.core)
    implementation(libs.exposed.dao)
    implementation(libs.exposed.jdbc)
    implementation(libs.exposed.java.time)
    implementation(libs.zaxxer.hikaricp)
    implementation(libs.flyway.core)
    implementation(libs.flyway.database.postgresql)
}

2. 配置数据库连接

Note

仅供参考，其中的命名比较随意，实际生产环境中不要这么随意的取名～

# application.conf
flyway {
    url = "jdbc:postgresql://localhost:5432/mydb"
    user = "postgres"
    password = "secret"
    locations = ["classpath:db/migration"]
    validateOnMigrate = true
}

3. 创建迁移脚本

在 src/main/resources/db/migration 目录下创建：

V1__Create_user_table.sql
V2__Add_email_column.sql

示例迁移脚本：

创建用户表

-- V1__Create_user_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

为用户表新增email列

-- V2__Add_email_column.sql
ALTER TABLE users
ADD COLUMN email VARCHAR(100);

-- 添加唯一约束
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 添加非空约束（如果需要对已有数据添加非空约束，需要先设置默认值）
-- 对于已有数据，先更新为默认值
-- UPDATE users SET email = '[email protected]' WHERE email IS NULL;

-- 然后添加非空约束
-- ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 添加索引以提高查询性能
CREATE INDEX idx_users_email ON users(email);

-- 添加注释
COMMENT ON COLUMN users.email IS '用户电子邮箱地址';

Tip

可以根据实际需求调整上述脚本：

如果 email 列必须是非空的，取消相关注释
如果不需要唯一约束，可以删除该行
如果不需要索引，可以删除创建索引的语句
可以根据需要调整 VARCHAR 的长度

4. 执行迁移

val flyway = Flyway.configure()
    .dataSource(dataSource)
    .load()
flyway.migrate()

最佳实践

每个脚本保持原子性变更
生产环境启用 validateOnMigrate 校验
使用 timestamp 作为版本号前缀（如 V20240316_1001）
重要变更需包含回滚脚本（使用 UNDO 前缀）
通过 flyway repair 修复校验失败问题

总结

PostgreSQL 与 Flyway 的组合为数据库管理提供了完整的解决方案：

版本控制：清晰记录数据库变更历史
可重复部署：确保开发/测试/生产环境一致性
回滚能力：通过迁移脚本实现版本回退
团队协作：通过版本文件实现多人协同开发
审计跟踪：flyway_schema_history 记录完整执行日志

典型应用场景：

新功能上线时的数据库结构调整
数据迁移和初始化脚本管理
多环境数据库版本同步
持续集成中的自动化数据库更新

PostgreSQL和Flyway的使用小记