迁移 kong 中的 PostgreSQL 数据库

背景

最近需要进行部署环境的迁移，原先 kong 使用的 PostgreSQL 数据库是几年前部署的了，一下子把命令全部忘光了。所以操作完成之后做个简单的记录。以下描述是基于 9.6 的，不适用于最新版本。

Postgres

原先 PostgreSQL 是使用 docker compose 部署的，是基于 https://github.com/DanielDent/docker-postgres-replication 进行修改的。可以使用 docker 快速启动一个包含主节点和从节点的 PostgreSQL 复制环境。几个关键的文件如下：

docker-compose.yml

通过环境变量配置数据库的用户名、密码、数据目录等信息，并设置了从节点的复制源为主节点。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
services:
  pg-master:
    image: 'xxx/postgres-replication:master'
    restart: 'always'
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'xxx'
      PGDATA: '/var/lib/postgresql/data/pgdata'
    volumes:
     - '/home/docker/var/lib/postgresql/data:/var/lib/postgresql/data'
    ports:
     - '5432:5432'

services:
  pg-slave:
    image: 'xxx/postgres-replication:master'
    restart: 'always'
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'xxx'
      PGDATA: '/var/lib/postgresql/data/pgdata'
      REPLICATE_FROM: 'xxx'
    volumes:
     - '/home/docker/var/lib/postgresql/data:/var/lib/postgresql/data'
    ports:
     - '5432:5432'

docker-entrypoint.sh

容器启动时的入口脚本，主要完成以下功能：

处理旧环境变量的兼容性问题。
根据 REPLICATE_FROM 环境变量判断是初始化主节点还是从节点：
- 主节点：使用 initdb 初始化数据库，设置数据库用户和密码，执行初始化 SQL 脚本。
- 从节点：等待主节点可用，使用 pg_basebackup 从主节点复制数据。

setup-replication.sh 根据 REPLICATE_FROM 环境变量配置主从节点的复制参数：

主节点：修改 postgresql.conf 文件，设置 wal_level、max_wal_senders 等参数。
从节点：创建 recovery.conf 文件，设置从节点的复制模式和连接主节点的信息。

迁移数据

先回顾完原先逻辑，就开始着手迁移了。我们可以选择物理迁移或者逻辑迁移。因为我们的 API 网关配置不会经常动，所以确保在数据不会变动的窗口下，直接采用逻辑备份会简单些。

新环境的机器准备好，然后把 Postgres 启动起来，新的主备都配置好；
在待迁移的主节点上执行逻辑备份；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 简单命令
pg_dump -d your_db > /backup/your_db.sql

pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit
***
Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

部分参数说明：

-Fc: 使用自定义压缩格式（支持并行恢复）
-Z 9: 最高压缩率（减少传输体积）
-j 4: 并行导出（加速大表备份）

传输备份文件到新机器，这个可以自己选择合适的方式（比如 scp/rsync/python simple httpserver）。
新环境机器创建新数据库。CREATE DATABASE your_db;
恢复数据库；

1
2
# 导入备份 或者使用 pg_restore
psql -U postgres -d your_db < your_db.sql

数据验证；
1. 数据一致性校验 选择关键的数据表，查看数据是否一致
2. 应用连通性测试 新建备用应用，修改应用配置指向新库，查看是否正常
3. 确认新的机器的主备复制正常

PostgreSQL 常见命令

在使用时，请注意适配的版本。

1 数据库连接与基本信息

命令/操作	说明	示例
连接数据库	指定用户、数据库、主机和端口	`psql -U postgres -d mydb -h 127.0.0.1 -p 5432`
退出交互界面	退出 `psql` 命令行	`\q`
查看版本	显示 PostgreSQL 版本	`SELECT version();`
切换数据库	连接到其他数据库	`\c new_database`
查看所有数据库	列出所有数据库	`\l` 或 `SELECT datname FROM pg_database;`

2 用户与权限管理

命令/操作	说明	示例
创建用户	创建新用户	`CREATE USER dev_user WITH PASSWORD 'password';`
修改用户密码	更改用户密码	`ALTER USER dev_user WITH PASSWORD 'new_password';`
授予权限	授予用户数据库权限	`GRANT ALL PRIVILEGES ON DATABASE mydb TO dev_user;`
撤销权限	移除用户权限	`REVOKE SELECT ON mytable FROM dev_user;`
查看用户列表	列出所有用户/角色	`\du` 或 `SELECT usename FROM pg_user;`

3 数据库与表操作

命令/操作	说明	示例
创建数据库	新建数据库	`CREATE DATABASE mydb;`
删除数据库	删除数据库	`DROP DATABASE mydb;`
查看所有表	列出当前数据库所有表	`\dt`
查看表结构	显示表字段和索引	`\d+ mytable`
创建表	定义新表结构	`CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));`
删除表	删除表及数据	`DROP TABLE mytable;`
清空表数据	快速清空表（不删结构）	`TRUNCATE TABLE mytable;`

4 数据查询与优化

命令/操作	说明	示例
基础查询	查询数据	`SELECT * FROM users WHERE age > 30;`
执行计划分析	查看查询执行计划	`EXPLAIN ANALYZE SELECT * FROM large_table;`
创建索引	提升查询性能	`CREATE INDEX idx_users_email ON users(email);`
重建索引	优化索引性能	`REINDEX INDEX idx_users_email;`
统计信息更新	刷新表的统计信息	`ANALYZE mytable;`

5 备份与恢复

命令/操作	说明	示例
逻辑备份（全库）	导出所有数据库	`pg_dumpall -U postgres > full_backup.sql`
逻辑备份（单库）	导出单个数据库	`pg_dump -U postgres -Fc -Z 9 mydb > mydb.dump`
逻辑恢复	从备份文件恢复	`pg_restore -U postgres -d mydb mydb.dump`
物理备份	文件系统级备份	`rsync -av /var/lib/postgresql/data /backup/`

6 复制与高可用

命令/操作	说明	示例
查看复制状态	主库检查从库状态	`SELECT * FROM pg_stat_replication;`
检查是否从库	验证节点角色	`SELECT pg_is_in_recovery();`
创建复制槽	管理逻辑复制槽	`SELECT * FROM pg_create_physical_replication_slot('slot1');`
延迟监控	计算主从延迟（字节）	`SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication;`

7 维护与监控

命令/操作	说明	示例
查看锁信息	检测当前锁冲突	`SELECT * FROM pg_locks;`
终止会话	强制结束问题连接	`SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%LOCK%';`
查看活跃查询	实时监控运行中的 SQL	`SELECT * FROM pg_stat_activity WHERE state = 'active';`
清理膨胀空间	回收表/索引空间	`VACUUM FULL ANALYZE mytable;`

8 配置文件与日志

操作	说明	路径示例
主配置文件	核心参数配置	`/etc/postgresql/14/main/postgresql.conf`
客户端认证配置	管理连接权限	`/etc/postgresql/14/main/pg_hba.conf`
日志文件	查询错误和慢 SQL	`/var/log/postgresql/postgresql-14-main.log`

9 高级技巧

事务控制

1
2
3
4
BEGIN;  -- 开启事务
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- 提交事务
-- 或 ROLLBACK; 回滚事务

JSON 数据处理

1
SELECT data->>'name' FROM json_table WHERE data @> '{"age": 30}';

定时任务（pg_cron）

1
SELECT cron.schedule('nightly-vacuum', '0 2 * * *', 'VACUUM ANALYZE;');

附：常用快捷键（psql 命令行）

\e：用编辑器打开最后执行的 SQL
\x：切换扩展显示模式（列转行）
\timing：显示查询执行时间
\i script.sql：执行外部 SQL 文件

可结合 man psql 和官方文档深入学习。

文章目录