咨询pg_restore的ALTER TABLE操作内容及剩余执行时长估算方法

API 探路者

2026-7-31

关于pg_restore长时间运行的疑问

我正在执行一个耗时极长的pg_restore操作，恢复一个包含70张表、大小为800GB的数据库，目前已耗时5天。我正在通过一些方式监控进度以估算剩余时长，但仍有疑问，因此前来咨询。

我曾使用参数-F d -j 10执行pg_dump，备份耗时约12小时，观察到10个线程各自负责一张表的完整处理，完成一张表后同一进程（pid）会接手另一张未被处理的表。

本次pg_restore耗时远超备份（已5天仍在运行），主要原因是恢复至通过nfs挂载的NAS外置硬盘，该硬盘比本地硬盘慢很多，但这并非问题，待格式化原硬盘并安装新系统后，我会将数据从NAS迁回原硬盘。

目前我通过两种方式监控进度：

在单独终端执行du -sh /var/lib/pgsql，评估新实例的磁盘占用量，目标是达到原数据库的大致占用空间；
在单独终端执行ps -fu postgres，看到多个pg_restore进程运行，每个进程关联一个形如postgres: postgres {dbname} [local] {command}的进程，其中{command}会变化：初始时是COPY命令（用于恢复表数据），之后出现CREATE INDEX命令（用于重建表索引），现在所有进程均在执行ALTER TABLE命令，我不清楚具体用途。

当前磁盘占用已接近原数据库大小，但进程仍未结束，已耗时5天。因此咨询：

解答

一、pg_restore中ALTER TABLE的具体操作

在pg_restore的后期阶段，ALTER TABLE通常负责以下几类核心操作：

添加外键约束：为了加速恢复，备份时会先导入数据、创建索引，最后才建立表间外键关联——如果提前创建外键，导入数据时需要逐行校验约束，会大幅拖慢速度。这一步需要遍历关联表验证数据完整性，在低速NAS上耗时极长。
恢复表权限设置：通过ALTER TABLE ... GRANT ...语句，还原备份时记录的用户对表的SELECT/INSERT等权限配置。
重置表存储参数：比如恢复表的fillfactor、指定的tablespace，或是启用行级安全、调整表的其他属性。
添加触发器/触发器函数：部分场景下，触发器会在数据导入完成后通过ALTER TABLE ... ADD TRIGGER ...添加，避免导入数据时触发业务逻辑影响恢复速度。

这些操作不会显著增加磁盘占用，但涉及元数据修改和跨表校验，是低速存储上恢复流程的最后“瓶颈”阶段。

除了你已使用的方式，还有这些实用的监控手段：

查看PostgreSQL日志
临时开启数据库日志（调整postgresql.conf中logging_collector = on后重启），日志会记录每个ALTER TABLE的开始和结束时间。统计已完成操作的平均耗时，再结合备份目录中剩余未执行的元数据SQL文件数量（比如约束、权限相关文件），可以估算剩余时间。
查询系统视图追踪当前操作
连接目标数据库，执行以下SQL查看正在运行的ALTER TABLE细节：
```
SELECT pid, query_start, query, state 
FROM pg_stat_activity 
WHERE query LIKE '%ALTER TABLE%';
```
通过对比已完成的同类型操作耗时，推算剩余操作的执行时间。
跟踪备份目录的文件处理进度
-F d生成的备份目录中，每个表和元数据对应独立文件。可以用lsof查看pg_restore主进程当前读取的文件：
```
lsof -p <pg_restore主进程PID> | grep <备份目录路径>
```
统计已处理的文件数量，对比总文件数（70张表加元数据文件约几十到上百个），估算整体进度。
监控磁盘IO负载
使用iostat -x 10或dstat工具监控NAS挂载目录的IO使用率、读写速度。如果ALTER TABLE阶段IO负载稳定，可结合剩余需要完成的操作量（比如外键数量）和当前IO速度，大致推算剩余时长。

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