1. 为什么需要复制一张表？从场景说起

如果你用过PostgreSQL，肯定遇到过这样的时刻：想对一张核心业务表做点“危险”操作，比如修改表结构、测试一个复杂的更新脚本，或者想基于现有数据快速创建一个测试环境。直接在生产表上动手？那无异于走钢丝，一个不小心就可能引发数据丢失或服务中断。这时候，最稳妥、最高效的办法，就是先“复制”一张表出来。

复制表，听起来简单，不就是CREATE TABLE new_table AS SELECT * FROM old_table吗？确实，这是最直观的一种方式，但PostgreSQL的魅力就在于，它提供了不止一种“复制”的路径。每种路径背后，都对应着不同的使用场景、性能开销和功能特性。用错了方法，你可能会发现复制过程慢如蜗牛，或者复制出来的表缺胳膊少腿（比如索引、约束没了），更严重的，可能在复制过程中锁表，影响线上业务。

我经历过一次惨痛的教训。早期处理一张有上千万记录的用户表，需要增加一个索引并填充一些计算字段。我图省事，直接用CREATE TABLE ... AS SELECT ...开干，结果这个操作不仅耗时极长，期间还因为产生了大量的WAL日志把磁盘空间撑爆了，更关键的是，它全程持有ACCESS EXCLUSIVE锁，导致原表有将近半小时完全不可读写，直接触发了线上告警。自那以后，我就开始深入研究PostgreSQL各种复制表方式的细微差别。

所以，今天我们就来彻底拆解PostgreSQL中复制一张表的5种核心方式。这不仅仅是5条SQL命令的罗列，我会带你深入每种方法的实现原理、适用场景、隐藏的坑以及我实战中总结的选型心得。无论你是要快速备份、迁移表结构，还是需要零停机地重构大表，这里总有一种方法适合你。

2. 方式一：CREATE TABLE AS SELECT – 快速的数据快照

这是大多数人学会的第一种复制表方法，语法直白，功能明确。

2.1 基本语法与效果

CREATE TABLE new_table AS SELECT * FROM old_table;

执行这条语句后，数据库会做两件事：

1. 创建一个名为new_table的新表。
2. 将old_table中的所有数据，通过执行SELECT *查询的结果集，插入到这个新表中。

关键特性：

• 仅复制数据与基本结构：新表只会拥有和查询结果集相同的列及其数据类型。原表的索引（INDEX）、主键（PRIMARY KEY）、外键（FOREIGN KEY）、约束（CHECK, NOT NULL）、触发器（TRIGGER）、所有者（OWNER）以及权限（GRANTS）等所有附加属性，统统不会复制。你得到的是一个纯粹的、裸的数据容器。
• 默认包含数据：如果你只想复制表结构而不复制数据，需要加上WITH NO DATA子句。

  CREATE TABLE new_table AS SELECT * FROM old_table WITH NO DATA;

2.2 内部机制与锁分析

理解它的锁行为至关重要。当执行CREATE TABLE AS SELECT（CTAS）时：

• 对源表（old_table）的锁：它通常只需要在读取数据时获取一个ACCESS SHARE锁。这是最轻量级的锁，与普通的SELECT查询相同，意味着它可以和绝大多数其他操作（包括其他的SELECT、UPDATE、DELETE）并发执行，一般不会阻塞线上业务。这是我后来才明白的，与我最初踩坑时的认知不同。
• 那么我当初的“锁表”灾难是怎么回事？问题出在目标表和系统目录上。创建新表（CREATE TABLE）本身需要在系统目录上获取较强的锁。如果操作非常庞大（像我那次千万级数据），或者系统繁忙，这个操作可能会因为等待其他事务而阻塞，或者它自身阻塞后续依赖系统目录的查询。虽然不直接锁原表，但仍可能对系统整体性能造成影响。
• WAL日志与性能：CTAS操作是“一次性”的，它生成的数据写入会完整地记录到WAL（Write-Ahead Logging）中，以确保崩溃恢复。对于海量数据，这会产生巨大的WAL日志量，这就是我那次撑爆磁盘的原因。同时，由于它不是并行优化的最佳选择，对于超大表，速度可能不是最快的。

2.3 适用场景与实战技巧

• 场景：当你只需要数据的快速快照，用于只读分析、临时计算，或者你打算手动重新定义所有约束和索引时。
• 技巧1：选择性复制。你可以利用SELECT语句的强大功能，进行数据清洗、过滤或转换。

  -- 只复制2023年的活跃用户数据，并增加一个计算列 CREATE TABLE user_2023_active AS SELECT id, username, email, created_at, (CASE WHEN status = 'active' THEN TRUE ELSE FALSE END) AS is_active FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

• 技巧2：与WITH子句（CTE）结合。可以在复制前进行复杂的数据准备。

  CREATE TABLE top_products AS WITH product_sales AS ( SELECT product_id, SUM(quantity) as total_sold FROM order_items GROUP BY product_id ORDER BY total_sold DESC LIMIT 100 ) SELECT p.*, ps.total_sold FROM products p INNER JOIN product_sales ps ON p.id = ps.product_id;

• 避坑提示：如果你复制后需要原表的自增序列（SERIAL）继续生效，CTAS不会自动创建新的序列并将其关联到新表。你需要手动处理nextval。

3. 方式二：CREATE TABLE LIKE – 结构的克隆专家

当你更关心表结构的“形似”，而数据可以稍后处理或不需要时，CREATE TABLE LIKE是你的首选。

3.1 基本语法与核心能力

CREATE TABLE new_table (LIKE old_table INCLUDING ALL);

这里的LIKE子句是精髓。它指示PostgreSQL参照old_table的结构来创建new_table。

INCLUDING子句详解（这是关键！）：

• INCLUDING ALL：这是最常用的选项，表示尽可能多地复制所有属性。包括：列定义、NOT NULL约束、默认值（DEFAULTS）、存储参数（如fillfactor）、压缩设置等。但是，请注意，即使使用INCLUDING ALL，它也不会复制索引、约束（主键、外键、唯一约束、检查约束）、触发器、所有者权限和表空间。这是与CREATE TABLE AS最大的不同之一——它专注于表本身的物理和逻辑存储定义。
• INCLUDING DEFAULTS：仅复制列的默认值。
• INCLUDING CONSTRAINTS：复制检查约束（CHECK）和非空约束（NOT NULL）。注意：不包括主键、外键等唯一性约束。
• INCLUDING INDEXES：复制索引。
• INCLUDING STORAGE：复制列的存储设置（如STORAGE PLAIN）。
• 你可以组合使用，例如：INCLUDING DEFAULTS INCLUDING CONSTRAINTS。

3.2 与CTAS的本质区别

很多人混淆CREATE TABLE ... LIKE和CREATE TABLE ... AS。让我们从本质上区分：

• CREATE TABLE ... AS：它的核心是一个查询。新表的结构由该查询结果集的列决定。它是一个“数据驱动”的创建过程。
• CREATE TABLE ... LIKE：它的核心是一个模板。新表是照着旧表的“样子”（结构定义）临摹出来的。它是一个“结构驱动”的创建过程。它不执行任何查询，因此速度极快，且不涉及原表数据，对原表没有任何锁影响（仅需读取系统目录）。

3.3 典型应用场景与操作流

• 场景1：创建测试表或临时表结构。你需要一个和原表结构一模一样的空表来跑测试用例。

  CREATE TABLE test_orders (LIKE orders INCLUDING ALL); -- 现在test_orders有了和orders一样的列、非空约束、默认值，但没有数据、索引和主键。

• 场景2：表结构迁移或重构的前置步骤。比如你想修改表的分区策略，可以先LIKE创建新结构，然后通过数据迁移工具（如INSERT INTO ... SELECT）慢慢挪动数据。
• 操作流示例：完整复制表（结构+数据+索引）。LIKE通常不单独完成全复制，而是作为工作流的一环。

  -- 第一步：复制结构（包括约束） CREATE TABLE orders_backup (LIKE orders INCLUDING CONSTRAINTS INCLUDING DEFAULTS); -- 第二步：复制索引（需要单独处理，因为LIKE不包含索引） -- 这是一个痛点，需要从系统表查询并动态生成索引创建语句，或使用外部工具。 -- 第三步：复制数据 INSERT INTO orders_backup SELECT * FROM orders; -- 第四步：添加主键等（如果原表有） ALTER TABLE orders_backup ADD PRIMARY KEY (id);

  可以看到，要完美克隆，步骤稍显繁琐。这也引出了下一种更强大的工具。

4. 方式三：pg_dump 与 pg_restore – 生态链中的瑞士军刀

这不是一条SQL命令，而是PostgreSQL官方客户端工具集里的王牌组合。当你的复制需求跨越数据库实例，或者需要最精细、最完整的控制时，它们是不二之选。

4.1 工具定位与核心优势

pg_dump用于将数据库、模式或单个表的结构和数据导出为一个脚本或归档文件。pg_restore则用于将这个文件导入（恢复）到另一个数据库（甚至是同一个数据库的不同模式）。

其不可替代的优势在于：

• 完整性：它能导出一切——表结构、数据、索引、约束、触发器、序列、所有者、权限、注释等等。这是真正的“克隆”。
• 灵活性：可以只导出结构（-s），只导出数据（-a），或两者都导出。可以指定格式（纯文本SQL、自定义归档、目录格式）。
• 跨版本/跨实例：备份和恢复可以在不同PostgreSQL版本或完全不同的服务器之间进行，是数据迁移的标准做法。
• 最小化锁影响：默认情况下，pg_dump使用一致性快照，它在开始时会获取一个快照，之后即使原表数据被修改，导出的也是快照时刻的数据。它对原表的锁影响非常小（主要是共享锁），适合在线业务数据库的备份。

4.2 单表复制实战命令

假设我们要将数据库mydb中的表public.orders复制到另一个数据库mydb_new中（可以是同一服务器，也可以是远程服务器）。

步骤1：使用目录格式转储单表目录格式功能最强大，允许pg_restore时精细选择对象。

pg_dump -F d -f /path/to/order_backup -t public.orders mydb

• -F d：指定目录格式。
• -f /path/to/order_backup：指定输出目录。
• -t public.orders：指定只转储public模式下的orders表。
• mydb：源数据库名。

步骤2：恢复到目标数据库

pg_restore -d mydb_new --clean --if-exists /path/to/order_backup

• -d mydb_new：指定目标数据库。
• --clean：在恢复前，清除目标数据库中同名对象（如表、索引）。使用此选项务必谨慎！
• --if-exists：与--clean配合使用，避免因对象不存在而报错。

步骤3：仅恢复表结构（不要数据）

pg_restore -d mydb_new -s /path/to/order_backup

• -s：只恢复结构（schema）。

步骤4：仅恢复数据（假设表结构已存在）

pg_restore -d mydb_new -a /path/to/order_backup

• -a：只恢复数据（data）。

4.3 复杂场景应用与注意事项

• 场景：复制表到不同模式或重命名。pg_restore本身不直接支持重命名，但你可以：
  1. 先只恢复结构到临时模式。
  2. 在数据库中修改表名或模式名。
  3. 或者，更简单的方法：在源数据库中使用CREATE TABLE new_schema.new_table (LIKE old_schema.old_table INCLUDING ALL);然后插入数据，再用pg_dump导出这个新表。
• 注意事项：
  • 序列：如果表中有SERIAL列，pg_dump会正确导出序列及其当前值。但如果你只用CREATE TABLE ... AS或LIKE，序列需要单独处理。
  • 大对象：如果表中存储了大对象（OID），需要额外使用-b/-B参数。
  • 性能：对于超大型表，pg_dump的纯SQL格式在恢复时可能较慢，因为需要重新解析和执行SQL。目录格式或自定义格式在恢复大数据时通常更快。
• 个人心得：对于生产环境的关键表克隆或备份，我几乎总是首选pg_dump。虽然步骤多了点，但它的可靠性和完整性是其他方法难以比拟的。特别是在需要保留所有依赖对象（如视图、函数中对该表的引用）的上下文时，导出整个相关模式往往是更安全的选择。

5. 方式四：INSERT INTO ... SELECT – 向已有结构注入数据

这种方式的前提是目标表已经存在。它不负责创建表结构，只负责数据的迁移。因此，它常与CREATE TABLE ... LIKE或手动建表语句结合使用，形成“先建壳，后灌数据”的标准流程。

5.1 基础语法与数据映射

INSERT INTO target_table (col1, col2, col3, ...) SELECT colA, colB, colC, ... FROM source_table WHERE ...;

• target_table：必须已经存在。
• 列列表(col1, col2, ...)是可选的。如果省略，则要求SELECT子句返回的列数、顺序和数据类型必须与target_table的列定义完全匹配。
• SELECT语句可以非常复杂，包含联接、过滤、聚合、子查询等，这提供了极大的灵活性。

5.2 锁行为与性能优化

这是需要特别关注的地方，尤其是在生产环境操作大表时。

• 锁分析：
  • 对源表（source_table）：通常只需要ACCESS SHARE锁（与普通SELECT一样），允许并发读写。
  • 对目标表（target_table）：需要ROW EXCLUSIVE锁。这个锁会阻塞其他试图对同一行进行UPDATE/DELETE/INSERT的事务，但不会阻塞SELECT。对于大批量插入，这个锁的影响相对可控，但长时间运行仍可能造成一定阻塞。
• 性能优化技巧：
  • 批量提交：对于海量数据，单条大INSERT事务会产生巨大的WAL日志和长锁持有时间。可以分批插入。

    DO $$ DECLARE batch_size INT := 10000; offset_val INT := 0; BEGIN LOOP INSERT INTO target_table SELECT * FROM source_table ORDER BY id -- 确保顺序稳定 LIMIT batch_size OFFSET offset_val; EXIT WHEN NOT FOUND; offset_val := offset_val + batch_size; COMMIT; -- 显式提交当前批次，减少事务大小 -- 可选：RAISE NOTICE '已插入 % 行', offset_val; END LOOP; END $$;

  • 禁用索引和触发器：在插入前，如果目标表有大量索引或触发器，可以先禁用它们，插入完成后再重建/启用，可以大幅提升速度。

    -- 禁用索引（非主键/唯一约束索引） UPDATE pg_index SET indisready = false WHERE indrelid = 'target_table'::regclass; -- 或者使用 ALTER INDEX ... DISABLE; (需要超级用户权限) -- 执行大批量 INSERT ... -- 重建索引 REINDEX TABLE target_table;

    注意：禁用索引是高级操作，需在维护窗口进行，并充分测试。对于主键/唯一索引，禁用可能导致数据不一致，通常不建议。

  • 使用UNLOGGED表作为临时目标：如果数据是中间过程，可以先将数据插入到UNLOGGED表（不写WAL，极快），最后再INSERT ... SELECT回普通表。

5.3 结合LIKE的完整克隆流程

这是手动完整克隆一张表的标准且灵活的方法：

-- 1. 创建结构（包含约束和默认值） CREATE TABLE orders_clone (LIKE orders INCLUDING CONSTRAINTS INCLUDING DEFAULTS); -- 2. （可选）如果有自增序列，需要创建并关联 CREATE SEQUENCE orders_clone_id_seq OWNED BY orders_clone.id; ALTER TABLE orders_clone ALTER COLUMN id SET DEFAULT nextval('orders_clone_id_seq'); -- 3. 插入数据 INSERT INTO orders_clone SELECT * FROM orders; -- 4. 创建索引（从原表定义生成或查询pg_index） -- 假设原表只有一个在`user_id`上的索引 CREATE INDEX ON orders_clone (user_id); -- 5. 设置主键 ALTER TABLE orders_clone ADD PRIMARY KEY (id); -- 6. 复制权限（如果需要） -- GRANT SELECT, INSERT ON orders_clone TO some_role;

这个流程给了你每一步的完全控制权，你可以在任何一步进行调整、过滤或转换。

6. 方式五：CREATE TABLE ... INHERITS – 面向对象的表继承

这是PostgreSQL独有的高级特性，它不仅仅是复制，而是建立了父子表之间的继承关系。理解它，能帮你解决一些特定场景下的优雅设计问题。

6.1 继承模型的概念

CREATE TABLE parent_table ( id SERIAL PRIMARY KEY, created_at TIMESTAMP NOT NULL DEFAULT now() ); CREATE TABLE child_table ( specific_field VARCHAR(50) ) INHERITS (parent_table);

• 子表（child_table）自动拥有父表（parent_table）的所有列。
• 向子表插入数据时，父表的列会自动存在。
• 查询父表时，默认会返回父表及其所有子表的数据（除非使用ONLY关键字）。这是继承最强大的特性之一。

6.2 用于“复制”的巧妙之处

虽然设计初衷不是为了复制，但我们可以利用它来快速创建一个与原表结构相同但没有继承关系的“兄弟”表，然后再解除继承。

-- 原表 CREATE TABLE original (id SERIAL PRIMARY KEY, name TEXT, value INT); -- 步骤1：创建一个继承自原表的空表 CREATE TABLE copy_like_original () INHERITS (original); -- 步骤2：此时copy_like_original拥有了和original完全相同的列定义。 -- 我们可以通过系统表检查确认。 -- 步骤3：解除继承关系，使其成为一个独立的表 ALTER TABLE copy_like_original NO INHERIT original; -- 现在，copy_like_original就是一张拥有original结构（包括NOT NULL约束，但**不包括**主键、索引、默认值）的独立空表。 -- 步骤4：插入数据 INSERT INTO copy_like_original SELECT * FROM original; -- 步骤5：手动添加原表拥有的其他属性（主键、索引等）。 ALTER TABLE copy_like_original ADD PRIMARY KEY (id); CREATE INDEX ON copy_like_original (name);

6.3 适用场景与重大限制

• 场景：这种方法在“复制表结构”这个特定点上，与CREATE TABLE ... LIKE有些类似，但更绕远。它真正的用武之地在于表分区（逻辑分区）和面向对象的数据建模。例如，你可以有一个vehicles父表，然后cars、trucks、motorcycles子表继承它，共享通用的license_plate,manufacturer字段，又各有特有字段。
• 重大限制与坑点：
  1. 不复制索引和约束：和LIKE一样，继承只复制列定义。主键、唯一约束、外键、索引不会被继承。这是最大的使用障碍。
  2. 默认值：列默认值会被继承。这是它与LIKE的一个区别。
  3. 触发器：不会被继承。
  4. 权限：不会被继承。
  5. 性能考虑：基于继承的查询（尤其是查询父表时扫描所有子表）在子表非常多时，规划器可能面临挑战。
• 个人建议：除非你明确需要表继承特性，否则不要专门为了复制一张表而使用INHERITS。CREATE TABLE ... LIKE在复制结构方面更直观、更少副作用。继承是一个强大的数据建模工具，但将其用作复制技巧显得笨重且容易遗漏属性。

7. 终极对决：5种方式如何选择？

面对一个具体的复制需求，我们该如何决策？下面这个表格从多个维度进行了对比，并附上我的选型推荐。

决策流程图：

1. 是否需要跨数据库或完整保留所有属性（索引、约束、触发器等）？
   • 是-> 毫不犹豫，选择pg_dump / pg_restore。
2. 是否只需要一个空的、结构完全一致的表？
   • 是-> 选择CREATE TABLE ... LIKE ... INCLUDING ALL。
3. 是否需要快速得到一个包含数据的副本，且可以接受手动重建索引和约束？
   • 是-> 选择CREATE TABLE ... AS SELECT ...。对于大数据量，注意WAL和系统目录锁的潜在影响。
4. 是否已经有一个目标表（结构可能不同），只需要灌入数据？
   • 是-> 选择INSERT INTO ... SELECT ...。这是最灵活的数据迁移方式，常与LIKE联用。
5. 是否在进行逻辑分区或特定的OO风格设计？
   • 是-> 考虑CREATE TABLE ... INHERITS。
   • 否-> 忽略此选项。

最后，分享一个我处理“在线大表重构”的常用组合拳：当需要修改一个数亿记录的生产表的表结构（如增加非空列、修改类型）并希望尽可能减少停机时，我会：

1. 使用CREATE TABLE new_table (LIKE old_table INCLUDING ALL)创建新结构空表。
2. 根据修改需求，对新表执行ALTER TABLE语句（例如ADD COLUMN,ALTER TYPE）。
3. 在新表上创建所有必要的索引和约束（此时是空表，创建速度极快）。
4. 编写一个可控的、分批的INSERT INTO new_table SELECT ... FROM old_table脚本，在业务低峰期执行，逐步将数据迁移到新表。过程中旧表始终可读写。
5. 数据迁移完成后，在一个短暂的维护窗口内，通过事务重命名表进行切换：BEGIN; ALTER TABLE old_table RENAME TO old_table_backup; ALTER TABLE new_table RENAME TO old_table; COMMIT;。 这个流程的核心就是灵活运用了LIKE和INSERT INTO ... SELECT，实现了平滑过渡。