カテゴリー
Linux

PostgreSQL のデータベースを別サーバへコピーする方法メモ♪

データベースを同じサーバの PostgreSQL へコピーするコマンド

-- データベースをコピー。db1（user1、password1） から db2（user2、password2）に
-- pg_dump -U user1 -W db1 | psql -U user2 -W db2 とすると
-- 「ERROR:  ロール"user1"のメンバでなければなりません」や
-- 「WARNING:  "public"の権限を取り上げられませんでした」の
-- メッセージが出るため、スーパーユーザーで実行することで回避
pg_dump -U postgres -W db1 | psql -U postgres -W db2

データベースを別のサーバの PostgreSQL へコピーするコマンド

データベースのコピーのコマンドの前に、次の内容のパスワードファイルを作成しておく必要があります。

  • コピー元サーバで、pg_dump を実行するユーザのホームディレクトリに .pgpass ファイルを作成する
  • コピー先サーバで、psql を実行するユーザのホームディレクトリに .pgpass ファイルを作成する
# hostname:port:database:username:password
localhost:5432:*:postgres:postgrespassword

実際の別サーバへのコピーは次のコマンドで行います。

-- 別サーバに PostgreSQL データベースをコピー。db1（user1、password1） から hostname サーバ（ユーザ名 hostuser）の db2（user2、password2）に
-- ただし、事前にパスワードファイルの設定が必要
pg_dump -U postgres db1 | ssh hostuser@hostname psql -U postgres db2

コピー元、コピー先など環境を整理！

ひとつのサーバ（192.168.56.111）の PostgreSQL

  • ユーザ：postuser、パスワード：postpass、データベース：postdb
  • ユーザ：postuser2、パスワード：postpass2、データベース：postdb2

別のサーバ（192.168.56.113）の PostgreSQL

  • ユーザ：gresuser、パスワード：grespass、データベース：gresdb

今回もまずはひとつの PostgreSQL 内でデータベースをコピーします。

これは、以前調べました。

ポイントは、スーパーユーザーの postgres で操作を行うこと、でした。なお、3行目の文字列は、パイプの右側、つまりインポート時の postgres ユーザのパスワードです。最初のパイプの左側、つまりエクスポート時の postgres パスワードは2行目時点で入力しており、この時は入力しても表示されません。

[root@localhost ~]# pg_dump -U postgres postdb | psql -U postgres postdb2
ユーザ postgres のパスワード: パスワード: 
postgrespassword

SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
[root@localhost ~]#

psql プロンプトで接続し、確認いたしました。バッチリです♪

いよいよ本番！別のサーバの PostgreSQL へデータベースのコピーを試みます！

前提として、コピーされる別サーバに、データベース gresdb は作成済みといたします。 以前の MySQL で別サーバのデータベースへコピーしたときと同様にコマンドを打ってみました。

[root@localhost ~]# pg_dump -U postuser postdb | ssh root@192.168.56.113 psql -U gresuser gresdb
パスワード: root@192.168.56.113's password: 
serverpassword

ユーザ gresuser のパスワード: 
psql: FATAL:  ユーザ"gresuser"のパスワード認証に失敗しました
[root@localhost ~]#

一筋縄ではいかないと思っておりましたが、やはりダメでした＞＜。行ったこととしては、

  1. まず2行目のところで postuser のパスワードを、
  2. 次に3行目で別サーバ（192.168.56.113）のパスワードを（表示されてしまいますが）、
  3. そして4行目で別サーバの PostgreSQL の gressuser のパスワードを入力

です。

念のために順番を入れ替えていろいろ試してみましたけれども、ダメでした。最初に postuser 以外のパスワードを入力しますと次のようなメッセージが表示されますので、最初の入力は postuser のパスワードで大丈夫かと思います。

[root@localhost ~]# pg_dump -U postuser postdb | ssh root@192.168.56.113 psql -U gresuser gresdb
パスワード: root@192.168.56.113's password: 
pg_dump: [アーカイバ(db)] データベース"postdb"への接続が失敗しました: FATAL:  ユーザ"postuser"のパスワード認証に失敗しました

では、同じサーバで PostgreSQL のデータベースをコピーした時と同様に、PostgreSQL のスーパーユーザーを設定してみましたらどうでしょうか？

[root@localhost ~]# pg_dump -U postgres postdb | ssh root@192.168.56.113 psql -U postgres gresdb
パスワード: root@192.168.56.113's password: 
serverpassword

ユーザ postgres のパスワード: 
psql: FATAL:  ユーザ"postgres"のパスワード認証に失敗しました
[root@localhost ~]#

ダメでした＞＜。

コピー元とコピー先のパスワード入力を省略するようにすれば！別サーバの PostgreSQL へデータベースのコピーができました！

MySQL データベースを別サーバへコピーした時は、コマンドにパスワードを記入することで認証の多くを省略できました。

PostgreSQL で同様のことはできないのでしょうか？コマンドへパスワードを記入する方法は、調べてみたところ見当たりませんでした。けれども、別の方法がございました。

それがこちらのパスワードファイルを設置し、それを自動で読み込む方法です。コピー元とコピー先のサーバの両方に設定する必要がございます。

コピー元サーバを例にやり方をメモします。コピー先も、同様に設定しました。

[root@localhost ~]# vim .pgpass

書く内容は次のとおりです。1行目はどのようなスタイルで記述するのか、フォーマット用のコメント行です。スーパーユーザー postgres の設定となりますので、次のように設定しました。

  • ローカルホストからの接続に限定し、PostgreSQL のデフォルトのポート 5432 の時にパスワード認証が不要となるようにしました。
  • 繋げられるデータベースは全て（*）としています。
  • 最後の2箇所で PostgreSQL のユーザ名、パスワードを指定します。
#hostname:port:database:username:password
localhost:5432:*:postgres:postgrespqssword

最後に、パーミッションを設定します。これを行いませんと、有効になりません。

[root@localhost ~]# chmod 600 .pgpass

なお、PostgreSQL の再起動は不要です。

では改めまして、別サーバへの PostgreSQL データベースのコピーを行ってみましょう。

[root@localhost ~]# pg_dump -U postgres postdb | ssh root@192.168.56.113 psql -U postgres gresdb
root@192.168.56.113's password: 
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ERROR:  ロール"postuser"は存在しません
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
[root@localhost ~]#

できました♪ただしひとつ気になります！ERROR が出ております＞＜。それもそのはず、別のサーバの PostgreSQL にはコピー元と同じロール、つまりはユーザのようなもの、はございません。ですので、「いらっしゃいませんことですの！」と ERROR が表示されたのだと考えられます。

これ、コピー先のデータは実際の運用に耐えうる状態なのでしょうか？わたくし、気になります！

ですけれども、その後の権限設定などを見ます限り、正常にできているようですし、コピー先のデータを確認しましたら、きちんと挿入されておりましたし、実際にやってみてしばらく様子を見てみたいと思います。

pg_dump -U postgres postdb | ssh root@192.168.56.113 psql -U postgres gresdb

下準備。ユーザ、そのパスワード、データベース、を作成

今回もテスト用にデータベースを作ったり削除したりしましたので、今後もパッと確かめられるように SQL コマンドをメモしておきます♪ なお、psql で PostgreSQL プロンプトにログインした状態でのコマンドとなります。

-- ユーザの作成（スーパーユーザ無効、DB作成無効、ユーザ作成無効、パスワード設定）。データベースの作成
CREATE USER testuser WITH NOSUPERUSER NOCREATEDB NOCREATEROLE PASSWORD 'testpassword';
CREATE DATABASE testdb WITH OWNER testuser ENCODING 'UTF8';

-- ユーザとそのデータベースの削除
DROP DATABASE testdb;
DROP USER testuser;

また、コピー元のデータベースに次のデータを予め入れておきます。

CREATE TABLE testtable (
  id integer,
  username varchar(255) NOT NULL,
  title text NOT NULL,
  post text DEFAULT NULL,
  status boolean NOT NULL,
  created timestamp DEFAULT NULL,
  modified timestamp DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO testtable (id, username, title, post, status, created, modified) VALUES (1, 'ユーザ1', 'タイトル1', 'ポスト1', true, 'now', 'now');

おわりに

考え方としては、MySQL のやり方と同じでした。ただし、パスワードの認証のやり方が異なりました。

パスワードファイルをホームディレクトリに置いておきますのでセキュリティ上大丈夫でしょうか？と思います。

ファイル作成ユーザ以外には全く権限を与えないように設定する必要がございますので、その点を考えますと問題なしとも思えます。

もっとも、root ユーザが乗っ取られてしまいましたら、意味は無いでしょうけれども。

パーミション設定と申しますと、先日パーミッション設定の不備で大きなニュースがございました。

気をつけてまいりたいと思います。

以上です。

コメントを残す