More than 3 years have passed since last update.

PostgreSQLのデータをpg_dumpallで移行する

Last updated at 2021-02-11Posted at 2021-02-10

#目的

pg_dumpall¹コマンドを使って__データベースクラスタ__²ごとデータ移行する方法を説明します！
今回はTeraTermでWebサーバーに接続してpg_dumpallで出力したスクリプトファイルを使い、
Docker上のPostgreSQLコンテナにデータを移行します。
異なる環境の方でもpg_dumpallを利用したデータ移行の参考になれば幸いです！

#環境

Windows 10 Pro 64bit メモリ 32GB
Docker for Windows v20.10.2
Docker Compose v1.27.4
TeraTerm v4.105

#1. pg_dumpallでデータベースクラスタをスクリプト化する

TeraTermでWebサーバーに接続後、任意のディレクトリでpg_dumpallを実行します。

[web] $ pg_dumpall -U [user_name] > [file_name].out

user_name: データベースのユーザー名を入れてください。
file_name: スクリプトファイルに任意のファイル名をつけてください。

lsコマンドで指定したファイル名が表示されていれば成功です。

[web] $ ls
file_name.out

#2. スクリプトファイルをWebサーバーからローカルに転送する

TeraTermのSSH SCP機能を使ってWebサーバーからローカルにスクリプトファイルを転送します。
ファイル > SSH SCP... の順に操作し、以下のウィンドウを開きます。

From: スクリプトファイルのパスを入力します。
To: スクリプトファイルの転送先を入力します。ここではデスクトップを指定しています。

FromとToを入力したら「Recieve」をクリックして転送を開始します。

#3. Dockerコンテナに入る

Dockerコンテナは以下の通りです。ここではwebというコンテナがWebサーバーになります。

[win] $ docker-compose ps 
pgadmin4   /entrypoint.sh                   Up      443/tcp, 0.0.0.0:8000->80/tcp
php-cli    docker-php-entrypoint apac ...   Up      80/tcp
postgres   docker-entrypoint.sh postgres    Up      0.0.0.0:5432->5432/tcp
web        docker-php-entrypoint apac ...   Up      0.0.0.0:80->80/tcp

以下のコマンドでwebに入ります。

[win] $ docker-compose exec web bash

#4. スクリプトファイルからデータベースクラスタを作成する

以下のコマンドでスクリプトファイルを実行します。

[web] $ psql -h postgres -U default -d postgres -f file_name.out

-h: ホスト名を指定しています。
-U: ユーザー名を指定しています。
-d: データベース名を指定しています。
-f: 実行するファイル名を指定しています。

エラーが出ることなく処理が終われば、これで移行完了です。

試しに、スクリプトファイルのCREATE ROLEで作成されたロールで、新たに作成されたデータベースに接続してみます。

[web] $ psql -h postgres -U [role_name] -d [db_name]

以下のコマンドでテーブルが作成されていることを確認します。


db_name=# ¥dt

以上がPostgreSQLのデータをpg_dumpallで移行する流れになります。

#スクリプトファイル実行時にALTER ROLEの権限エラーが発生した場合

pg_dumpallで出力したスクリプトファイルをそのまま実行すると、以下のようなエラーが出る場合があります。
僕もAWSのRDSにデータ移行をする際にこのエラーが発生したました。その時、対処した方法を説明します。

ERROR:  must be superuser to alter superusers
ERROR:  must be superuser to alter replication users

上記のエラーが出る場合、以下の権限についてはスクリプトファイルでALTER ROLEすることができません。

SUPERUSER
NOSUPERUSER
REPLICATION
NOREPLICATION

この場合は、スクリプトファイルのCREATE ROLEとALTER ROLEをコメントアウトし、
データベースに接続してCREATE ROLEをしてからGRANTで代替ロールを付与することで解消できます。

###CREATE ROLEとALTER ROLEをコメントアウトする

-- CREATE ROLE test_user;
-- ALTER ROLE test_user WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'xxxxxx';

###webからpsqlコマンドでデータベースに接続する

[web] $ psql -h postgres -U default -d postgres

###ロールの一覧を表示する


postgres=# ¥du
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 default    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

これからスクリプトファイルでコメントアウトしたCREATE ROLEを実行し、
defaultが持つ権限をCREATE ROLEで作成されたロールに付与します。

###CREATE ROLEでロールを作成する


db_name=# CREATE ROLE test_user
CREATE ROLE

CREATE ROLE: PostgreSQLデータベースクラスタに新しいロールを追加します。

これで、test_userというロールが新たに作成されました。


db_name=# ¥du
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 default    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user  | Cannot login                                               | {}

test_userでログイン可能にする場合は、以下のコマンドを実行してください。


db_name=# ALTER ROLE test_user WITH LOGIN PASSWORD 'xxxxxx';
ALTER ROLE

ALTER ROLE: ロールの属性を変更します。WITH以降に付与する属性を指定します。
LOGIN: ロールでログイン可能にします。
PASSWORD: パスワードを設定します。

これでtest_userのCannot loginが消え、test_userがユーザーとしてみなされるようになり、
設定したパスワードでログインできるようになります。


db_name=# ¥du
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 default    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user  |                                                            | {}

###defaultが持っている権限をtest_userに付与する


postgres=# GRANT "default" TO postgres;
GRANT ROLE

GRANT: ロールに付与された権限を各メンバに伝えます。
ここではdefaultが持つ権限をtest_userに付与しています。

下記のようにtest_userのMember ofに{default}と記載されていれば成功です。


postgres=# ¥du
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 default   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {default}

これでスクリプトファイルを実行する準備ができました。

###psqlを終了する


postgres=# ¥q

###スクリプトファイルからデータベースクラスタを作成する

[web] $ psql -h postgres -U default -d postgres -f file_name.out

エラーが出ることなく処理が終われば、これで移行完了です。

#おわりに

間違っている点やもっと良い方法などありましたら、ご指摘いただけるとありがたいです！
最後までお読みいただきありがとうございました！

#参考にした記事

データベースクラスタの全てのPostgreSQLデータベースをスクリプトファイルに出力します。 ↩
1つのサーバインスタンスによって管理されるデータベースの集合体を意味します。 ↩

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up