Redshift中如何撤销COPY命令执行的错误数据插入操作

未提交事务场景 #

• 如果执行COPY命令的会话仍未断开，且操作后未执行COMMIT语句，直接运行ROLLBACK;即可撤销本次COPY的所有写入，无额外成本。

已提交事务场景 #

如果已经执行了提交操作，可根据你的集群配置和数据规模选择以下方案： #

方案1：通过隐藏query字段删除错误数据

Redshift所有用户表默认内置隐藏系统字段query，记录了插入该行数据对应操作的query ID，操作步骤如下：

1. 从STL_QUERY系统表查询本次误操作COPY对应的query ID：

SELECT query, querytxt, starttime
FROM stl_query
WHERE querytxt ILIKE '%COPY <你的目标表名>%'
  AND starttime >= '<误操作的大致起始时间>'
ORDER BY starttime DESC
LIMIT 1;

2. 用拿到的query ID删除对应写入的错误数据：

DELETE FROM <你的目标表名> WHERE query = <上一步查到的query ID>;

注意：删除操作执行后，被标记删除的数据不会立即释放存储空间，需要手动执行VACUUM <你的目标表名>;回收空间 #

方案2：时间点恢复（需集群开启PITR功能）

如果你的集群是Redshift Serverless版本，或者预置集群开启了时间点恢复（PITR）功能，可以直接用时间旅行特性读取误操作前的表数据替换现有表：

-- 生成误操作前的表备份
CREATE TABLE <目标表名>_restored AS
SELECT * FROM <目标表名> AT TIMESTAMP '<误操作发生前1-2分钟的时间点>';

-- 校验备份数据无误后替换原表
DROP TABLE <目标表名>;
ALTER TABLE <目标表名>_restored RENAME TO <目标表名>;

方案3：快照恢复

如果集群有开启自动快照，或者误操作前手动生成过集群快照，可以先恢复快照到临时集群，再导出目标表的正确数据覆盖现有表即可，该方案适合表数据量极大、DELETE操作耗时过长的场景。

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