golang-migrate在VPS迁移时冻结耗时异常的排查求助

1. 定位问题核心：SQL执行还是migrate工具本身 #

• 直接在VPS上用PostgreSQL客户端执行迁移文件的SQL语句（比如7/d add_user_settings对应的降级SQL），验证是否卡顿：

  psql -d 你的数据库名 -U 数据库用户 -f ~/migrations/7_downgrade_add_user_settings.sql
  

  • 如果直接执行SQL也慢，问题出在PostgreSQL或系统资源：
    1. 查看Postgres日志（默认路径/var/log/postgresql/postgresql-14-main.log），排查锁等待、超时或错误信息
    2. 用EXPLAIN ANALYZE分析迁移SQL的执行计划，确认是否存在全表扫描、索引缺失等性能瓶颈
    3. 用top、iostat工具检查VPS的CPU、内存、磁盘IO使用率，确认是否有资源耗尽情况

2. 验证数据库连接与SSH执行环境 #

• 改用Unix套接字连接数据库，避免TCP层开销：将DSN从postgres://user:pass@localhost:5432/db改为postgres://user:pass@/db?host=/var/run/postgresql
• 跳过SSH的终端交互参数测试：直接在VPS上执行migrate命令，或者用以下SSH命令（去掉-t）：

  ssh myusername@111.111.11.11 "migrate -path ~/migrations -database '$DSN' -verbose down 1"
  

• 检查PostgreSQL的pg_hba.conf，确认本地连接的认证方式是否高效（比如用peer或trust替代md5，仅限测试环境）

3. 处理脏状态与数据库锁 #

• 查看migrate元数据表状态：

  SELECT version, dirty FROM schema_migrations;
  

• 确认无正在运行的迁移进程后，手动重置脏标记：

  UPDATE schema_migrations SET dirty = false WHERE version = 7;
  

• 排查数据库锁等待：

  SELECT * FROM pg_locks WHERE NOT granted;
  

  若存在阻塞进程，终止对应PID：

  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%add_user_settings%';
  

4. 测试migrate基础功能 #

• 在VPS上创建全新测试库，执行最简迁移（比如仅创建空表），验证是否仍有缓慢问题
• 开启migrate调试模式：设置环境变量后执行命令，获取更详细日志：

  ssh myusername@111.111.11.11 "export MIGRATE_DEBUG=1 && migrate -path ~/migrations -database '$DSN' -verbose down 1"
  

5. 系统层面排查 #

• 检查磁盘IO性能：用dd if=/dev/zero of=testfile bs=1G count=1 oflag=direct测试写入速度，对比本地环境
• 查看系统文件描述符限制：执行ulimit -n，若数值过低，修改/etc/security/limits.conf提高限制
• 确认VPS防火墙规则未限制本地数据库连接或文件读写

内容的提问来源于stack exchange，提问作者Sam