使用SQL完全复制Postgres表

小编典典

使用SQL完全复制Postgres表

sql

我正在尝试在postgres中复制一个大表（〜4000万行，超过100列），其中很多列都已建立索引。目前，我使用以下SQL：

CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;

此方法有两个问题：

它在数据摄取之前添加索引，因此比创建不带索引的表要花费更多的时间，然后在复制所有数据后再建立索引将花费更多的时间。
这不会正确复制“ SERIAL”样式列。它没有在新表上设置新的“计数器”，而是将新表中列的默认值设置为过去表的计数器，这意味着它不会随着行的添加而增加。

表的大小使索引成为实时问题。这也使得转储到文件然后重新注册变得不可行。我也没有命令行的优势。我需要在SQL中执行此操作。

我想做的就是要么用一些奇迹命令直接复制一个精确的副本，要么（如果不可能的话）复制带有所有约束但没有索引的表，并确保它们是“精神上”的约束（aka
SERIAL列的新计数器）。然后使用a复制所有数据，SELECT *然后复制所有索引。

资料来源

有关数据库复制的堆栈溢出问题：这不是我要问的三个原因
- 它使用命令行选项pg_dump -t x2 | sed 's/x2/x3/g' | psql，在此设置下，我无权访问命令行
- 它在数据摄取之前创建索引，这很慢
- 它不能正确更新序列列作为证据 default nextval('x1_id_seq'::regclass)
- 重置postgres表的序列值的方法：很好，但是很不幸，它非常手工。

阅读 241

2021-04-15

共1个答案

小编典典

好吧，不幸的是，您将不得不手工做一些这样的事情。但这一切都可以通过psql之类的方法完成。第一个命令很简单：

select * into newtable from oldtable

这将使用旧表的数据创建新表，但不使用索引。然后，您必须自己创建索引和序列等。您可以使用以下命令获取表上所有索引的列表：

select indexdef from pg_indexes where tablename='oldtable';

然后运行psql -E访问您的数据库，并使用\ d查看旧表。然后，您可以修改这两个查询以获取有关序列的信息：

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

将上面的74359替换为从上一个查询中获得的oid。

2021-04-15