Amazon Redshift备份与恢复最佳实践及IDENTITY列备份问题咨询

我在Redshift环境里处理过不少带IDENTITY列的表备份恢复需求，尤其是测试环境频繁备份重置的场景，正好能给你几个实用的方案和最佳实践——先从你遇到的问题根源说起：

为什么传统CTAS备份会丢IDENTITY属性？ #

你用的CREATE TABLE XYZ_BKP AS SELECT * FROM XYZ（也就是CTAS语句），本质是通过查询结果生成新表，它只会复制列的数据类型和实际数据，完全不会保留原表的元数据属性——比如IDENTITY的序列定义、主键约束、默认值、甚至表的DISTKEY/SORTKEY配置。这就是你备份后丢失属性的核心原因，我之前也踩过这个坑。

靠谱的解决方案 #

1. 用CREATE TABLE ... LIKE+INSERT：最省心的单表备份 #

这是保留所有表属性的最优解，能完整复刻原表的所有配置，包括IDENTITY：

-- 第一步：创建和原表结构、属性完全一致的空备份表
CREATE TABLE XYZ_BKP (LIKE XYZ INCLUDING ALL);

-- 第二步：把原表数据插入备份表
INSERT INTO XYZ_BKP SELECT * FROM XYZ;

• INCLUDING ALL是关键，它会把原表的IDENTITY规则、约束、默认值、存储参数（DIST/SORTKEY）全部复制过来；如果只需要部分属性，也可以用INCLUDING COLUMNS/INCLUDING CONSTRAINTS这类更细的选项，但测试环境直接用ALL最省事。
• 如果表数据量不小，担心INSERT速度，可以确认下Redshift的并行插入是否开启（默认是开的），或者加个PARALLEL ON参数。

2. UNLOAD+COPY：大数据量表的高效备份 #

如果你的表数据量特别大，INSERT ... SELECT效率跟不上，就用Redshift原生的UNLOAD+COPY组合，既能高效备份，也能保留IDENTITY属性：

-- 先创建和原表一致的空备份表
CREATE TABLE XYZ_BKP (LIKE XYZ INCLUDING ALL);

-- 把原表数据导出到S3，用Parquet格式（比CSV压缩比高、速度快）
UNLOAD ('SELECT * FROM XYZ')
TO 's3://your-backup-bucket/xyz-data/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftUnloadRole'
FORMAT PARQUET;

-- 从S3把数据导回备份表，注意要加EXPLICIT_IDS才能保留原IDENTITY值
COPY XYZ_BKP
FROM 's3://your-backup-bucket/xyz-data/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT PARQUET
EXPLICIT_IDS;

• 要是你测试时不需要保留原数据的IDENTITY值，想让备份表的序列从初始值重新生成，去掉EXPLICIT_IDS就行，Redshift会自动给新插入的数据生成新的序列值。

3. Redshift集群快照：整个测试环境的快速重置 #

如果你的测试场景是频繁重置整个环境（不是单表），直接用Redshift的快照功能更方便：

• 手动创建快照：在AWS控制台或者用CREATE SNAPSHOT命令给集群拍个快照
• 恢复快照：直接恢复到新集群或者替换现有集群，这样所有表的结构、属性、甚至IDENTITY序列的当前状态都会完整保留
• 优点：操作简单，一键重置整个环境；缺点：粒度是集群级，没法单独恢复某一张表

测试环境最佳实践 #

• 彻底放弃CTAS做备份：除非你明确不需要保留元数据，否则CTAS就是给自己挖坑，丢属性是必然的
• 统一备份脚本：把单表备份的逻辑写成SQL脚本，比如循环遍历需要备份的表，自动执行CREATE TABLE ... LIKE和INSERT，避免手动操作出错
• 备份后一定要验证：备份完别直接用，跑个SQL检查IDENTITY属性是否保留：

-- 查看备份表的IDENTITY列信息
SELECT attname, attidentity FROM pg_attribute WHERE attrelid = 'XYZ_BKP'::regclass AND attidentity != '';

• 按需重置IDENTITY序列：如果测试时需要把序列重置回初始值（比如恢复后要重新生成从1开始的ID），用这条命令：

ALTER TABLE XYZ ALTER COLUMN your_identity_col RESTART WITH 1;

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