PostgreSQLで異なるスキーマにデータをリストアするには？

異なるデータベースにリストアするのは簡単ですが、異なるスキーマにリストアするのに結構はまったので、以下覚書。psqlなどのコマンドをローカルにインストールするのが嫌だったのでdockerを使ってます。

シナリオは、

データベース名:my_db
スキーマ名:public

をダンプし、

データベース名:my_db2
スキーマ名:my_schema2

にリストアするというものです。pg_restoreコマンドでは異なるスキーマにリストアすることはできなかったです。うまいやり方ご存じのかた教えてほしいです(スキーマ名をリネームするのは無しで）。

方針

pg_restoreコマンドはあきらめて、pg_dumpでプレーンテキストを出力し、適宜プレーンテキストを文字列置換して、psqlコマンドでスキーマ定義、データを反映します。

• pg_dumpコマンドで、スキーマ定義をプレーンテキストでダンプ。
• pg_dumpコマンドで、データをプレーンテキストでダンプする。
• ダンプしたスキーマ定義をスキーマ名とユーザ名をエディタでリストア先のスキーマ名、ユーザ名に置き換える。
• psqlコマンドで、修正したスキーマ定義を投入。
• psqlコマンドで、データを投入の際に、スキーマ名をリストア先のスキーマ名に変更してからpsqlコマンドを実行。

かなりメンドクサイ。

事前準備

次のdocker-compose.ymlでデータベースを起動している前提です。

docker-compose.yml

version: '3.7'
services:
  my-posgre-db:
    image: postgres:latest
    volumes:
      - ./data:/var/lib/postgresql/data
    environment:
      - TZ=Asia/Tokyo
      - POSTGRES_PASSWORD=postgres
    networks:
        - my-posgre-network
    ports:
      - 5432:5432

networks:
  my-posgre-network:
    driver: bridge

リストア先の異なるデータベース、ユーザ、スキーマを作ります。
※だいぶ雑な表現で恐縮ですが。。。

CREATE USER "my_user2" WITH PASSWORD 'my_user2';
CREATE DATABASE "my_db2";
\c "my_db2"
CREATE SCHEMA "my_schema2";
ALTER SCHEMA "my_schema2" OWNER TO "my_user2";
GRANT ALL PRIVILEGES ON SCHEMA "my_schema2" TO "my_user2";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "my_schema2" TO "my_user2";

pg_dumpコマンドで、スキーマ定義をプレーンテキストでダンプ

docker run -it --rm -v ./work:/tmp --network postgresql_my-posgre-network postgres:latest pg_dump -h my-posgre-db -d my_db  -U my_user  -n public -s -F p -f /tmp/public_schema.sql

pg_dumpコマンドで、データをプレーンテキストでダンプする

docker run -it --rm -v ./work:/tmp --network postgresql_my-posgre-network postgres:latest pg_dump -h my-posgre-db -d my_db  -U my_user  -n public -F p -a | gzip > ./work/public_schema.dump.gz

わかりにくいですが、このコマンドを打つと、裏で「Password:」のプロンプトが出ています。gzipにリダイレクトしているので、プロンプトが標準出力されないです。
なので、パスワードを入力するとダンプが始まります。
残念なことに、「Password:」の文字列がgzipにリダイレクトされているので、ダンプファイルの先頭に書き込まれてしまいます。
この後でリストアする際には除外するようにしなければならないです。

ダンプしたスキーマ定義をスキーマ名とユーザ名をエディタでリストア先のスキーマ名、ユーザ名に置き換える

sedやviなどのエディタでスキーマ名とOWNERユーザ名を書き換える。sedコマンドで置換した結果を標準入力を食わせても良いです。以下でデータを投入するところででは標準入力で食わせてます。
あと、先頭の方にCREATE SCHEMA文があるので、削除すること。

psqlコマンドで、修正したスキーマ定義を投入

docker run -it --rm -v ./work:/tmp --network postgresql_my-posgre-network postgres:latest psql -h my-posgre-db -d my_db2 -U my_user2 -f /tmp/public_schema.sql

psqlコマンドで、データを投入の際に、スキーマ名をリストア先のスキーマ名に変更してからpsqlコマンドを実行

コマンドの解説。
コマンドの先頭で{ echo -e ～をしているのは、docker runに食わせる標準入力を組み立てています。
まず、echo -e "my_user2\n"の部分はパスワードをdocker runに食わせる標準入力の先頭行に入れています。
次に、gzip -d -c ./work/public_schema.dump.gzでダンプファイルを解凍。
sed 's/COPY public/COPY my_schema2/g'でスキーマ名を変更。ここが肝。
sed 's/^Password://g'でダンプした際の「Password:」を消しています。

あと、docker run -iの-iの部分。いつもは-itなのに、今回は-iとしているのは、{ echo -e ～の標準入力を受け付けるため。tをつけてしまうと対話モードになってしまい、リダイレクトで入力を受け付けられなくなるため。

{ echo -e "my_user2\n"; gzip -d -c ./work/public_schema.dump.gz | sed 's/COPY public/COPY my_schema2/g' | sed 's/^Password://g';} | docker run -i --rm -v ./work:/tmp --network postgresql_my-posgre-network postgres:latest psql -h my-posgre-db -d my_db2 -U my_user2