首页 文章

使用SQL完全复制postgres表

提问于
浏览 次
50

DISCLAIMER: 这个问题类似于堆栈溢出问题here，但这些答案都不适合我的问题，我稍后会解释 .

我正在尝试在postgres中复制一个大表（~40M行，100列），其中很多列都被索引 . 目前我使用这个SQL：

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

这种方法有两个问题：

  • 它在数据摄取之前添加索引，因此比创建没有索引的表所需的时间长，然后在复制所有数据后进行索引 .

  • 这样做没有't copy `SERIAL'样式列 . 它不是在新表上设置新的'counter'，而是将新表中列的默认值设置为过去表的计数器，这意味着它不会随着行的添加而增加 .

表大小使索引成为实时问题 . 它还使得转储到文件然后重新摄取是不可行的 . 我也没有命令行的优势 . 我需要在SQL中执行此操作 .

我不可能复制具有所有约束但没有索引的表格，并确保它们是精神上的''re the constraints ''（又称SERIAL列的新计数器） . 然后用 SELECT * 复制所有数据，然后复制所有索引 .

Sources

sql postgresql data-migration

6 回答

  • 0

    好吧，不幸的是，你将不得不亲手做一些这样的事情 . 但它可以从像psql这样的东西完成 . 第一个命令很简单：

    select * into newtable from oldtable

    这将使用oldtable的数据而不是索引创建newtable . 然后你必须自己创建索引和序列等 . 您可以使用以下命令获取表上所有索引的列表：

    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 .

    回复于
  • 2

    PostgreSQL中的 create table as 功能现在可能是OP正在寻找的答案 .

    https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

    create table my_table_copy as
  select * from my_table

    这将创建一个与数据相同的表 .

    添加 with no data 将复制没有数据的架构 .

    create table my_table_copy as
  select * from my_table
with no data

    这将创建包含所有数据的表，但没有索引和触发器等 .

    create table my_table_copy (like my_table including all)

    create table like语法将包括所有触发器，索引，约束等 . 但不包括数据 .

    回复于
  • -1

    最接近的“奇迹命令”就像是

    pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

    特别是，这需要在加载表数据后创建索引 .

    但这不会重置序列;你必须自己编写脚本 .

    回复于
  • 40

    WARNING:

    所有使用pg_dump和任何类型的正则表达式替换源表名称的答案都非常危险 . 如果您的数据包含您要替换的子字符串，该怎么办？您最终会更改数据！

    我提出了一个两遍解决方案：

    • 使用一些特定于数据的正则表达式从转储中消除数据行

    • 对剩余的行执行搜索和替换

    这是一个用Ruby编写的例子：

    ruby -pe 'gsub(/(members?)/, "\\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

    在上面我试图将“成员”表复制到“members_copy_20130320” . 我的数据特定的正则表达式是/ ^ \ d \ t . *（？：t | f）$ /

    以上类型的解决方案适合我 . 买者自负...

    edit:

    好的，这是regexp-averse用户的伪shell语法的另一种方式：

    • pg_dump -s -t mytable mydb> mytable_schema.sql

    • mytable_schema.sql> mytable_copy_schema.sql中的搜索和替换表名

    • psql -f mytable_copy_schema.sql mydb

    • pg_dump -a -t mytable mydb> mytable_data.sql

    • 在数据部分之前的少数SQL语句中替换"mytable"

    • psql -f mytable_data.sql mydb

    回复于
  • 48

    显然你想“重建”一张 table . 如果您只想重建一个表而不是复制它，那么您应该使用CLUSTER .

    SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

    你可以选择索引，尝试选择一个适合你的查询 . 如果没有其他索引适合，您始终可以使用主键 .

    如果您的表太大而无法缓存，则CLUSTER可能会很慢 .

    回复于
  • 14

    create table newTableName（如oldTableName包括索引）;插入newTableName select * from oldTableName

    这对我有用9.3

    回复于

相关问题