PostgreSQL: INSERT INTO SELECT を使ってテーブルを別のデータベースにコピーする方法

2024-04-02

PostgreSQLでテーブルを別のデータベースにコピーする方法

pg_dump と psql を使用する方法

これは、最も基本的な方法です。以下の手順で実行できます。

コピー元のデータベースをダンプする。

pg_dump -Fc -t テーブル名 データベース名 > dump.sql

-Fc: カスタムフォーマットでダンプ
-t: コピーしたいテーブル名
データベース名: コピー元のデータベース名
dump.sql: ダンプファイル名

psql データベース名

ダンプファイルをインポートする。

\i dump.sql

この方法は、テーブルの構造とデータだけでなく、制約やインデックスなどもコピーできます。

INSERT INTO SELECT を使用する方法

CREATE TABLE コピー先データベース名.テーブル名 (
  カラム名1 型1,
  カラム名2 型2,
  ...
);

カラム名1: カラム名
型1: カラムの型
...: 他のカラムも同じように記述

INSERT INTO SELECT を使用して、データをコピーする。

INSERT INTO コピー先データベース名.テーブル名 (
  カラム名1,
  カラム名2,
  ...
)
SELECT
  カラム名1,
  カラム名2,
  ...
FROM コピー元データベース名.テーブル名;

pglogical を使用する方法

これは、論理レプリケーションを使用してテーブルをコピーする方法です。以下の手順で実行できます。

pglogicalをインストールする。

yum install pglogical

コピー元のデータベースとコピー先のデータベースをpglogicalで登録する。

pglogical register-database --dbname データベース名

論理レプリケーションを有効にする。

pglogical start

サブスクリプションを作成する。

pglogical create subscription --subscription-name サブスクリプション名 --provider-database データベース名 --table-name テーブル名

サブスクリプション名: サブスクリプション名

シンプルな方法でコピーしたい場合は、INSERT INTO SELECT を使用するのがおすすめです。
制約やインデックスも含めてコピーしたい場合は、pg_dump と psql を使用するのがおすすめです。
リアルタイムに更新を反映したい場合は、pglogical を使用するのがおすすめです。

# コピー元のデータベースをダンプする
pg_dump -Fc -t テーブル名 データベース名 > dump.sql

# コピー先のデータベースに接続する
psql データベース名

# ダンプファイルをインポートする
\i dump.sql

# コピー先のデータベースに空のテーブルを作成する
CREATE TABLE コピー先データベース名.テーブル名 (
  カラム名1 型1,
  カラム名2 型2,
  ...
);

# INSERT INTO SELECT を使用して、データをコピーする
INSERT INTO コピー先データベース名.テーブル名 (
  カラム名1,
  カラム名2,
  ...
)
SELECT
  カラム名1,
  カラム名2,
  ...
FROM コピー元データベース名.テーブル名;

pglogical を使用する方法

# pglogicalをインストールする
yum install pglogical

# コピー元のデータベースとコピー先のデータベースをpglogicalで登録する
pglogical register-database --dbname データベース名

# 論理レプリケーションを有効にする
pglogical start

# サブスクリプションを作成する
pglogical create subscription --subscription-name サブスクリプション名 --provider-database データベース名 --table-name テーブル名

各方法の詳細については、上記の解説を参照してください。

PostgreSQLでテーブルを別のデータベースにコピーするその他の方法

pg_copy を使用する方法

pg_copy は、PostgreSQL に付属のユーティリティツールです。このツールを使用して、テーブルデータを別のデータベースにコピーできます。

pg_copy -c "COPY テーブル名 FROM 'データベース名' TO 'データベース名'"

-c: コマンドを実行

エクスポート・インポートツールを使用する方法

いくつかのエクスポート・インポートツールを使用して、PostgreSQL テーブルデータを別のデータベースにコピーできます。

pgAdmin
DBeaver
SQuirreL SQL Client

これらのツールは、GUI を使用してテーブルデータを簡単にコピーできます。

外部キー制約を使用して、別のデータベースのテーブルにデータをコピーできます。

CREATE TABLE コピー先データベース名.テーブル名 (
  カラム名1 型1,
  カラム名2 型2,
  ...
  FOREIGN KEY (カラム名2) REFERENCES コピー元データベース名.テーブル名 (カラム名2)
);

INSERT INTO コピー先データベース名.テーブル名 (
  カラム名1,
  カラム名2,
  ...
)
SELECT
  カラム名1,
  カラム名2,
  ...
FROM コピー元データベース名.テーブル名;

カラム名2: コピー元テーブルの主キー

この方法は、テーブル間の関連性を維持したい場合に便利です。

CREATE TRIGGER コピートリガー
AFTER INSERT ON コピー元データベース名.テーブル名
FOR EACH ROW
EXECUTE PROCEDURE コピー先データベース名.コピープロシージャ();

CREATE PROCEDURE コピー先データベース名.コピープロシージャ()
AS
BEGIN
  INSERT INTO コピー先データベース名.テーブル名 (
    カラム名1,
    カラム名2,
    ...
  )
  VALUES (
    NEW.カラム名1,
    NEW.カラム名2,
    ...
  );
END;