note2self

PostgreSQLで論理バックアップを行う手順を整理します。

■論理バックアップ（SQLダンプ）とは

データベースのバックアップを「pg_dump」と呼ばれるツールを用いて取得することができます。取得する方法が「スクリプトファイル形式」か「アーカイブファイル形式」かでリストア方法が異なるので注意が必要です。pg_dumpを用いて取得するダンプはデータベースが使用中でも一貫性を保った状態で取得ができます。この方法で取得するダンプには統計情報は含まれないようなのでリストア後は統計情報の収集を行うのが望ましいです。

▼スクリプトファイル形式とアーカイブファイル形式とは

簡単に書くと、以下の２種類の形式に分類されます。いずれの形式も取得したダンプをリストアする際は取得した環境と同じバージョンでなくてもよく（リストア先が新しいバージョンでもOK)、ビット数の異なるアーキティクチャ(32bit→64bit)でもOKのようです。

■ダンプ取得の手順

「pg_dump」コマンドのオプションを先に記載します。

▼ダンプ取得（スクリプト形式）

データベースへアクセス許可があれば、リモートからの実行も可能です。リモートの場合は、「–host=ホスト名」、「–port=ポート番号」、「–username=ユーザ名」、「–password=パスワード」、「–dbname=データベース名」のように追加のパラメータが必要になります。

デフォルトはスクリプト形式となりますので「-F」オプションを省略すればスクリプト形式での取得となります。

pg_dump -h xxx.xxx.xxx.xxx -p xxxx -U username -W databasename > database_name.dmp

▼ダンプ取得（アーカイブファイル形式）

「–format」パラメータで「custom」もしくは「directory」を選択するとアーカイブファイル形式となります。「directory」形式を選択するとダンプされるテーブルごとにファイル、さらに目次の役割を果たすファイルのように分割された状態でディレクトリに出力されます。

pg_dump --host=xxx.xxx.xxx.xxx --port=xxxx --username=testuser --password=testpassword --format=custom --encoding=UTF-8 --dbname=testdb --file ~/Documents/temp/testdb.backup

以下は、上記と同じコマンド内容をオプションの指定を異なる記述で行ったパターンです。パラメータの大文字・小文字は注意が必要です。

pg_dump -h xxx.xxx.xxx.xxx -p xxxx -U testuser -W testpassword -F c -E UTF-8 -d testdb -f ~/Documents/temp/testdb.backup

（参考）

https://www.postgresql.jp/document/14/html/app-pgdump.html

■ダンプリストアの手順

psql、pg_restore共通のオプションは以下の表にまとめました。

▼ダンプのリストア（スクリプト形式）

「psql」コマンドでリストアします。最もシンプルなパターンは下記のとおりです。

psql -h xxx.xxx.xxx.xxx -U username -W password -d database_name < dump_file_to_restore.dmp

（参考）

https://www.postgresql.jp/document/14/html/app-psql.html

▼ダンプのリストア（アーカイブファイル形式）

「pg_restore」コマンドでリストアします。pg_restoreでは柔軟な対応ができるため、その分オプションも複雑になります。詳細は下にある参考欄より本家の説明ページを確認ください。

pg_restore --host=xxx.xxx.xxx.xxx --port=xxxx --username=restoreuser --no-owner --no-privileges --single-transaction --clean --if-exists --format=custom --dbname=testdb ~/Documents/temp/testdb.backup

（参考）

https://www.postgresql.jp/document/14/html/app-pgrestore.html