PostgreSQL データベース接続ユーザー(ロール)のバックアップとリストア

PostgreSQL データベース接続ユーザー(ロール)のバックアップとリストア

PostgreSQLでデータベース接続ユーザー(ロール)のバックアップを作成する方法と、リストアを行う方法について、以下に示します。

※ここで説明に使用しているPostgreSQLは、当サイトのCentOS7 64bitのyumリポジトリにPostgreSQLを追加し、最新バージョンをインストールのページを元にインストールした、バージョン9.4になります。

※全てのデータベースのバックアップとリストアについては、当サイトのPostgreSQL 全てのデータベースのバックアップとリストアのページをご覧ください。

※特定のデータベースのバックアップとリストアについては、当サイトのPostgreSQL 特定のデータベースのバックアップとリストアのページをご覧ください。

※データ定義(データベース定義、テーブル定義)のみのバックアップ、データのみのバックアップについては、当サイトのPostgreSQL データ定義(データベース定義、テーブル定義)のみのバックアップ、データのみのバックアップのページをご覧ください。

動作確認用のデータベース接続ユーザーの作成

ここでは、データベース接続ユーザーのバックアップとリストアの動作確認用に、以下のようなデータベース接続ユーザーを使用します。

データベース接続ユーザー「user01」を作成

データベース接続ユーザー「user01」を以下のように作成します。

postgres=# CREATE ROLE user01 LOGIN PASSWORD 'pass01',

データベース接続ユーザー「user02」を作成

データベース接続ユーザー「user02」を以下のように作成します。

postgres=# CREATE ROLE user02 LOGIN PASSWORD 'pass02',

データベース接続ユーザー(ロール)のバックアップを作成

データベース接続ユーザー(ロール)のバックアップを作成するには、以下のようなコマンドを実行します。

peer認証を使用する場合

# su - postgres
$ pg_dumpall --roles-only --clean --if-exists >, /xxx/postgresql/dump/user.sql

パスワード認証を使用する場合

# pg_dumpall -h 127.0.0.1 -U postgres --roles-only --clean --if-exists >, /xxx/postgresql/dump/user.sql
  • データベース接続ユーザー(ロール)のバックアップの作成には、「pg_dumpall」コマンドを使用します。

  • バックアップを作成時の認証方式は、peer認証、パスワード認証のどちらでもバックアップの内容に変わりはありません。

    peer認証を使用する場合、「su」コマンドでユーザーを「postgres」に切り替えてから、「pg_dumpall」コマンドを実行します。

    パスワード認証が設定ファイル「pg_hba.conf」で「host」接続時に行われるように設定されている場合、「-h」オプションで接続先のホストを指定し、「-U」オプションで接続ユーザーを指定します。

  • 「–roles-only」オプションを指定すると、データベース接続ユーザー(ロール)のバックアップのみが出力されるようになります。

  • 「–clean」オプションを指定すると、データベース接続ユーザー(ロール)のCREATE文の前に、DROP文が出力されるようになります。

    「–if-exists」オプションも指定することで、DROP文には「IF EXISTS」が付くようになります。

  • リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

    ここでは、ディレクトリ「/xxx/postgresql/dump/」に、ファイル名「user.sql」で出力するようにしています。

    peer認証を使用する場合、出力先のディレクトリには、ユーザー「postgres」による書き込み権限が必要となります。

作成されたバックアップの内容は、以下のようになります。

-- -- PostgreSQL database cluster dump -- SET default_transaction_read_only = off, SET client_encoding = 'UTF8', SET standard_conforming_strings = on, -- -- Drop roles -- DROP ROLE IF EXISTS postgres, DROP ROLE IF EXISTS user01, DROP ROLE IF EXISTS user02, -- -- Roles -- CREATE ROLE postgres, ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md532e12f215ba27cb750c9e093ce4b5127', CREATE ROLE user01, ALTER ROLE user01 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md53190bd72fda98853c755257d817aefdb', CREATE ROLE user02, ALTER ROLE user02 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5a734509272668d8c5e087c48af5ea20c', -- -- PostgreSQL database cluster dump complete -- 
  • バックアップの内容は、データベース接続ユーザー(ロール)に関するもののみになっています。

データベース接続ユーザー(ロール)のバックアップからのリストア

上記で取得したデータベース接続ユーザー(ロール)のバックアップ「user.sql」からリストアを行うには、以下のようなコマンドを実行します。

peer認証を使用する場合

# su - postgres
$ psql -f /xxx/postgresql/dump/user.sql postgres

パスワード認証を使用する場合

# psql -h 127.0.0.1 -U postgres -f /xxx/postgresql/dump/user.sql postgres

実行結果は、以下のようになります。

SET SET SET psql:/xxx/postgresql/dump/user.sql:14: ERROR: 現在のユーザを削除できません psql:/xxx/postgresql/dump/user.sql:15: NOTICE: ロール'user01'は存在しません。省略します DROP ROLE psql:/xxx/postgresql/dump/user.sql:16: NOTICE: ロール'user02'は存在しません。省略します DROP ROLE psql:/xxx/postgresql/dump/user.sql:23: ERROR: ロール'postgres'はすでに存在します ALTER ROLE CREATE ROLE ALTER ROLE CREATE ROLE ALTER ROLE
  • 平文のSQLで出力されたバックアップからのリストアには、「psql」コマンドを使用します。

  • 全てのデータベースのバックアップからのリストアでは、リストア時にパスワード認証を使用すると、postgresのパスワードがリストア前とバックアップの内容で異なる場合に、バックアップ対象の各データベースへの接続でエラーになってしまいますが、データベース接続ユーザー(ロール)のリストアではデータベースへの接続は「psql」コマンドの実行時のみとなるため、認証方式は、peer認証、パスワード認証のどちらでも変わりはありません。

    peer認証を使用する場合、「su」コマンドでユーザーを「postgres」に切り替えてから、「psql」コマンドを実行します。

    パスワード認証が設定ファイル「pg_hba.conf」で「host」接続時に行われるように設定されている場合、「-h」オプションで接続先のホストを指定し、「-U」オプションで接続ユーザーを指定します。

  • 「-f」オプションで、リストアを行うバックアップファイルを指定します。

    ここでは、ディレクトリ「/xxx/postgresql/dump/」にある、ファイル「user.sql」を指定しています。

  • 最後に「postgres」と指定し、リストア時に接続するデータベースを「postgres」にしています。

    「postgres」は、PostgreSQLでデフォルトで用意されているデータベースになります。

    尚、リストア時に接続するデータベースの名前は、コマンドの引数ではなく、「-d」オプションで指定することもできます。

  • 「psql:/xxx/postgresql/dump/user.sql:14: ERROR: 現在のユーザを削除できません」と表示されているエラーは、「psql」コマンドでデータベースへの接続に使用しているユーザー「postgres」が削除できないことによるものになります。

    「psql:/xxx/postgresql/dump/user.sql:23: ERROR: ロール’postgres’はすでに存在します」と表示されているエラーは、ユーザー「postgres」が削除できなかった状態で、作成しようとしたことによるものになります。

    「psql」コマンドでのデータベースへの接続を「postgres」以外のスーパーユーザーで行ったとしても、ユーザー「postgres」の削除は、「データベースシステムが必要としているためロール postgresを削除できません」のようにエラーとなり行えません。

    ユーザー「postgres」の削除と作成でエラーにはなりますが、バックアップに含まれるALTER ROLE文は実行されますので、結果としては問題のないようになります。

PostgreSQL データベース接続ユーザー(ロール)のバックアップとリストアについては、以上です。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。