[解決済み] PostgresのテーブルをSQLで完全にコピーする

質問

免責事項 この質問はstack overflowの質問と似ている ここで に似ていますが、後で説明するように、これらの回答はどれも私の問題には役立ちません。

私は、多くの列がインデックスされている postgres の大きなテーブル (~40M rows, 100+ columns) をコピーしようとしています。現在、私はこの SQL のビットを使用しています。

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

この方法には2つの問題があります。

1. データ取り込みの前にインデックスを追加するため、インデックスなしでテーブルを作成し、すべてのデータをコピーした後にインデックスを作成するよりもはるかに時間がかかるでしょう。
2. これは `SERIAL' スタイルのカラムを正しくコピーしません。新しいテーブルに新しい 'counter' を設定する代わりに、新しいテーブルの列のデフォルト値を過去のテーブルのカウンターに設定します。

テーブルのサイズにより、インデックスの作成がリアルタイムで問題となります。また、再インデックス化のためにファイルにダンプすることも不可能になります。また、コマンド ラインの利点もありません。SQL でこれを行う必要があります。

私がやりたいことは、奇跡のコマンドで正確なコピーを作成するか、それが不可能な場合、すべての制約でインデックスなしでテーブルをコピーし、それらが制約の「精神」であることを確認します (別名 SERIAL 列の新しいカウンタ)。それから、すべてのデータを SELECT * ですべてのデータをコピーし、すべてのインデックスの上にコピーします。

ソース

1. データベースのコピーに関するStack Overflowの質問 : これは3つの理由で私が求めているものではありません。

   • これは、コマンドラインオプション pg_dump -t x2 | sed 's/x2/x3/g' | psql で、この設定では、私はコマンドラインにアクセスできないので
   • データ取り込み前にインデックスを作成するので、時間がかかる
   • によって証明されるように、シリアルカラムを正しく更新しません。 default nextval('x1_id_seq'::regclass)

2. postgresテーブルのシーケンス値をリセットするメソッドです。 : これは素晴らしいのですが、残念ながら非常に手動です。

どのように解決するのですか？

まあ、残念ながら、このうちのいくつかは手作業でやらなければならないでしょう。 しかし、これはすべてpsqlのようなものから行うことができます。 最初のコマンドはとても簡単です。

select * into newtable from oldtable

これはoldtableのデータでnewtableを作成しますが、インデックスを作成しません。 その後、インデックスやシーケンスなどを自分で作成する必要があります。 コマンドでテーブル上のすべてのインデックスのリストを得ることができます。

select indexdef from pg_indexes where tablename='oldtable';

それから psql -E を実行してデータベースにアクセスし、古いテーブルを見るために \d を使用します。 そして、この2つのクエリを組み合わせて、配列の情報を取得することができます。

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に置き換えてください。