postgresql SQLによるバックアップ・リストア方法

SQLによるバックアップとは

• バックアップファイルとして、SQLコマンドを生成する
• 他のDBサーバへそのファイルを実行することで、同じ状態を再構築出来る
• バージョン違いや、マシンアーキテクチャの違うDBサーバ間でも実行出来る

環境

postgresql 12.4

テストデータの作成

• sampleデータベース作成
• usersテーブル作成し、テストデータを3件追加
• restore_sampleリストア先のデータベース作成

-- db作成
create database sample;

-- usersテーブル作成
create table users(
    id serial not null,
    name character varying
);

-- データ追加
insert into users(name) values ('A'),('B'),('C');

-- リストア先データベース作成
create database restore_sample;

データベース単体に対して実行

• データベース毎に個別でバックアップを行う

バックアップ

$ pg_dump sample > dump_sample

リストア

• リストア実行途中にエラーが発生した場合(ユーザが作成されていない等)でも、継続してリストアを続ける
  • 中断したい場合は--set ON_ERROR_STOP=onを設定する。
  • 中断した場合、それまで実行した不完全なデータが残る
• --single-transactionオプションを使用し1つのトランザクションと実行することで、不完全なデータが残らないようにすることも可能

$ pg_dump restore_sample < dump_sample

# リストア途中でエラーが発生した場合、中断する
$ pg_dump --set ON_ERROR_STOP=on restore_sample < dump_sample

# 1つのトランザクションとして実行し、中断した場合でも不完全なデータを残さない
$ psql --single-transaction restore_sample < dump_sample

データベースクラスタ全体に対して実行

• pg_dumpと違い、データベースクラスタ全体をバックアップする
• クラスタレベルのみや、テーブル、ロールなど、分けてバックアップ可能
  • -a, --data-only: データのみ
  • -g, --globals-only: データベース以外の、クラスタレベルの情報のみ
  • -O, --no-owner: リレーションシップの省く
  • -r, --roles-only: ロールのみ
  • -s, --schema-only: スキーマのみ
  • -t, --tablespaces-only: テーブルスペースのみ スキーマ+データ

バックアップ

$ pg_dumpall > dump_all

# クラスタレベルの情報のみバックアップ
$ pg_dumpall --globals-only > dump_all

リストア

# 他サーバのデータベースに対して、リストアを実行
psql -h [ホスト名:IPアドレス] -p 5432 -f dump_all postgres

大規模データベースの場合

• バックアップ時に出力されるファイルサイズが大きくなるため、圧縮を行う

バックアップ

• カスタムバックアップ書式-Fを使用することで、テーブルの復元を部分的に行えるらしい(わからん)

$ pg_dump -Fc sample > dump_sample

リストア

• カスタムバックアップ書式を使用した場合、pg_restoreコマンドを使用する必要がある

$ pg_restore -d restore_sample dump_sample

並列実行

• 大きなデータベースの場合、バックアップ・リストアを高速で実行できる
• 並列ジョブ数を-jで設定出来る
• バックアップは出力フォーマット-F dとし、ディレクトリフォーマットに設定する必要がある

# 並列ジョブ3でバックアップ
pg_dump -j 3 -F d -c -f dump_sample_dir sample
# 並列ジョブ3でリストア
pg_restore -j 3 -d restore_sample dump_sample_dir