SQL 用SQL完全复制一个postgres表

Completely copying a postgres table with SQL

提问by Erik

DISCLAIMER:This question is similar to the stack overflow question here, but none of those answers work for my problem, as I will explain later.

免责声明：这个问题类似于这里的堆栈溢出问题，但这些答案都不适用于我的问题，我将在后面解释。

I'm trying to copy a large table (~40M rows, 100+ columns) in postgres where a lot of the columns are indexed. Currently I use this bit of SQL:

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

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

This method has two issues:

这种方法有两个问题：

1. It adds the indices before data ingest, so it will take much longer than creating the table without indices and then indexing after copying all of the data.
2. This doesn't copy `SERIAL' style columns properly. Instead of setting up a new 'counter' on the the new table, it sets the default value of the column in the new table to the counter of the past table, meaning it won't increment as rows are added.

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

The table size makes indexing a real time issue. It also makes it infeasible to dump to a file to then re-ingest. I also don't have the advantage of a command line. I need to do this in SQL.

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

What I'd like to do is either straight make an exact copy with some miracle command, or if that's not possible, to copy the table with all contraints but without indices, and make sure they're the constraints 'in spirit' (aka a new counter for a SERIAL column). Then copy all of the data with a SELECT *and then copy over all of the indices.

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

Sources

来源

1. Stack Overflow question about database copying: This isn't what I'm asking for for three reasons

   • It uses the command line option pg_dump -t x2 | sed 's/x2/x3/g' | psqland in this setting I don't have access to the command line
   • It creates the indices pre data ingest, which is slow
   • It doesn't update the serial columns correctly as evidence by default nextval('x1_id_seq'::regclass)

2. Method to reset the sequence value for a postgres table: This is great, but unfortunately it is very manual.

1. 关于数据库复制的堆栈溢出问题：这不是我要求的三个原因

   • 它使用命令行选项pg_dump -t x2 | sed 's/x2/x3/g' | psql，在此设置中我无权访问命令行
   • 它在数据摄取前创建索引，这很慢
   • 它没有正确更新序列列作为证据 default nextval('x1_id_seq'::regclass)

2. 重置 postgres 表的序列值的方法：这很好，但不幸的是它非常手动。

采纳答案by Scott Marlowe

Well, you're gonna have to do some of this stuff by hand, unfortunately. But it can all be done from something like psql. The first command is simple enough:

好吧，不幸的是，您将不得不手动完成其中的一些工作。但这一切都可以通过 psql 之类的东西来完成。第一个命令很简单：

select * into newtable from oldtable

This will create newtable with oldtable's data but not indexes. Then you've got to create the indexes and sequences etc on your own. You can get a list of all the indexes on a table with the command:

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

select indexdef from pg_indexes where tablename='oldtable';

Then run psql -E to access your db and use \d to look at the old table. You can then mangle these two queries to get the info on the sequences:

然后运行 ​​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;

Replace that 74359 above with the oid you get from the previous query.

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

回答by Phill

The create table asfeature in PostgreSQL may now be the answer the OP was looking for.

create table asPostgreSQL 中的功能现在可能是 OP 正在寻找的答案。

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

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

create table my_table_copy as
  select * from my_table

This will create an identical table with the data.

这将创建一个包含数据的相同表。

Adding with no datawill copy the schema without the data.

添加with no data将复制没有数据的模式。

create table my_table_copy as
  select * from my_table
with no data

This will create the table with all the data, but without indexes and triggers etc.

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

---

---

create table my_table_copy (like my_table including all)

create table my_table_copy (like my_table including all)

The create table like syntax will include all triggers, indexes, constraints, etc. But not include data.

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

回答by Peter Eisentraut

The closest "miracle command" is something like

最接近的“奇迹命令”是这样的

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

In particular, this takes care of creating the indexes after loading the table data.

特别是，它负责在加载表数据后创建索引。

But that doesn't reset the sequences; you will have to script that yourself.

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

回答by K M Rakibul Islam

To copy a table completely, including both table structure and data, you use the following statement:

要完全复制表，包括表结构和数据，请使用以下语句：

CREATE TABLE new_table AS 
TABLE existing_table;

To copy a table structure without data, you add the WITH NO DATA clause to the CREATE TABLE statement as follows:

要复制没有数据的表结构，您可以将 WITH NO DATA 子句添加到 CREATE TABLE 语句中，如下所示：

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

To copy a table with partial data from an existing table, you use the following statement:

要从现有表复制包含部分数据的表，请使用以下语句：

CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

回答by Tomek

WARNING:

警告：

All the answers which use pg_dump and any sort of regular expression to replace the name of the source table are really dangerous. What if your data contains the substring that you are trying to replace? You will end up changing your data!

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

I propose a two-pass solution:

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

1. eliminate data lines from the dump using some data-specific regexp
2. perform search-and-replace on the remaining lines

1. 使用一些特定于数据的正则表达式从转储中消除数据行
2. 在剩余的行上执行搜索和替换

Here's an example written in Ruby:

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

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

In the above I am trying to copy "members" table into "members_copy_20130320". My data-specific regexp is /^\d+\t.*(?:t|f)$/

在上面我试图将“成员”表复制到“members_copy_20130320”中。我的数据特定正则表达式是 /^\d+\t.*(?:t|f)$/

The above type of solution works for me. Caveat emptor...

上述类型的解决方案对我有用。买者自负...

edit:

编辑：

OK, here's another way in pseudo-shell syntax for the regexp-averse people:

好的，对于不喜欢正则表达式的人来说，这是另一种伪 shell 语法：

1. pg_dump -s -t mytable mydb > mytable_schema.sql
2. search-and-replace table name in mytable_schema.sql > mytable_copy_schema.sql

3. psql -f mytable_copy_schema.sql mydb

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

5. replace "mytable" in the few SQL statement preceding the data section
6. psql -f mytable_data.sql mydb

1. pg_dump -s -t mytable mydb > mytable_schema.sql
2. 在 mytable_schema.sql > mytable_copy_schema.sql 中搜索和替换表名

3. psql -f mytable_copy_schema.sql mydb

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

5. 在数据部分之前的几个 SQL 语句中替换“mytable”
6. psql -f mytable_data.sql mydb

回答by peufeu

Apparently you want to "rebuild" a table. If you only want to rebuild a table, not copy it, then you should use CLUSTER instead.

显然你想“重建”一张桌子。如果你只想重建一个表，而不是复制它，那么你应该使用 CLUSTER 代替。

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

You get to choose the index, try to pick one that suits your queries. You can always use the primary key if no other index is suitable.

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

If your table is too large to be cached, CLUSTER can be slow though.

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

回答by user2940756

create table newTableName (like oldTableName including indexes); insert into newTableName select * from oldTableName

创建表 newTableName（如 oldTableName 包括索引）；insert into newTableName select * from oldTableName

This worked for me 9.3

这对我有用 9.3