Zenn
🐕

[PostgreSQL] パーティションテーブルのバックアップ・リストア

2023/03/26に公開・約6,100字
PostgreSQL
tech

概要

  • DB全体ではなくパーティションテーブル単体をバックアップ・リストアする方法
  • パーティションテーブルをpg_dumpするにはコマンドオプションの指定に要注意

パーティションテーブルとは

公式より

パーティショニングとは、論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分割することを指します。 パーティショニングによって得られる利点は以下のようにいくつかあります。

  • 特定の条件下で問い合わせのパフォーマンスが劇的に向上することがあります。 特にテーブル内のアクセスが集中する行の殆どが単一または少数のパーティションに存在している場合がそうです。 パーティショニングは実質的にインデックスの上位木レベルの代わりになり、インデックスの頻繁に使われる部分がメモリに収まりやすくなるようにします。
  • 問い合わせや更新が一つのパーティションの大部分にアクセスする場合、インデックス使用してテーブル全体にまたがるランダムアクセス読み取りをする代わりに、そのパーティションへの順次アクセスをすることでパフォーマンスを向上させることができます。
  • 一括挿入や削除について、その使い方のパターンをパーティションの設計に組み込んでいれば、それをパーティションの追加や削除で実現することが可能です。 個々のパーティションをDROP TABLEで削除する、あるいはALTER TABLE DETACH PARTITIONを実行することにより、一括の操作をするよりも遥かに高速です。 これらのコマンドはまた、一括のDELETEで引き起こされるVACUUMのオーバーヘッドを完全に回避できます。
  • 滅多に使用されないデータを安価で低速なストレージメディアに移行することができます。

https://www.postgresql.jp/document/14/html/ddl-partitioning.html

事前準備

デモリポジトリ

以降はこちらのリポジトリの実装を元に解説します。

https://github.com/i-zacky/psql-partition-table-demo

前提条件

リポジトリの実装を動かすにあたって必要なツール等は以下の通り。

  • Docker
  • direnv
  • Node.js v16.x

ツールのインストール手順やDockerコンテナの起動方法等については割愛する。

バックアップ元DBへのテーブル作成

今回バックアップ対象のテーブルとして以下のような入金テーブルを作成する。
ポイントとしては入金日（ deposit_date ）を範囲パーティションを使って分割している点。

CREATE TABLE IF NOT EXISTS deposit (
    id varchar(26) NOT NULL,
    deposit_date date NOT NULL,
    currency varchar(3),
    fx_rate decimal(8, 4),
    amount decimal(17, 4),
    PRIMARY KEY (id, deposit_date)
) PARTITION BY RANGE (deposit_date);

-- partition of 2020-01-01 ~ 2020-12-31
CREATE TABLE deposit_y2020m01 PARTITION OF deposit FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');
CREATE TABLE deposit_y2020m02 PARTITION OF deposit FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');
CREATE TABLE deposit_y2020m03 PARTITION OF deposit FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE deposit_y2020m04 PARTITION OF deposit FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE deposit_y2020m05 PARTITION OF deposit FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE deposit_y2020m06 PARTITION OF deposit FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE deposit_y2020m07 PARTITION OF deposit FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE deposit_y2020m08 PARTITION OF deposit FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE deposit_y2020m09 PARTITION OF deposit FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE deposit_y2020m10 PARTITION OF deposit FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE deposit_y2020m11 PARTITION OF deposit FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE deposit_y2020m12 PARTITION OF deposit FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');

-- partition of 2021-01-01 ~ 2021-12-31
CREATE TABLE deposit_y2021m01 PARTITION OF deposit FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE deposit_y2021m02 PARTITION OF deposit FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE deposit_y2021m03 PARTITION OF deposit FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE deposit_y2021m04 PARTITION OF deposit FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE deposit_y2021m05 PARTITION OF deposit FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE deposit_y2021m06 PARTITION OF deposit FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE deposit_y2021m07 PARTITION OF deposit FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE deposit_y2021m08 PARTITION OF deposit FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE deposit_y2021m09 PARTITION OF deposit FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE deposit_y2021m10 PARTITION OF deposit FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE deposit_y2021m11 PARTITION OF deposit FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE deposit_y2021m12 PARTITION OF deposit FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

バックアップ元DBへの投入データの生成

簡単なNodeスクリプトを実装してあるので、それを使ってCSV形式のデータを生成する。
第1引数にランダム生成する入金日の年を指定し、第2引数に生成するCSVのレコード数を指定する。

以下の例では2021年1月1日から2021年12月31日の期間で入金日をランダムに生成し、CSVを100万行生成する。

$ yarn --cwd generator generate 2021 1000000

バックアップ元DBへのCSVデータの投入

COPYコマンドを使用してCSVデータをバックアップ元（source-db）の deposit テーブルへ投入する。
/usr/share/app にサンプルリポジトリのルートディレクトリをボリュームマウントしてあるので、前項で生成したCSVファイルを参照してCOPYコマンドを実行する。

$ docker compose exec -it source-db /bin/bash
root@95e5c9a045fb:/# psql -U ${POSTGRES_USER} -d ${POSTGRES_DB}
psql (14.5 (Debian 14.5-1.pgdg110+1))
Type "help" for help.

sandbox_db=# \COPY deposit FROM /usr/share/app/generator/deposit-y2020-5000000row.csv DELIMITER ',' CSV;
COPY 5000000
sandbox_db=# \COPY deposit FROM /usr/share/app/generator/deposit-y2021-5000000row.csv DELIMITER ',' CSV;
COPY 5000000

本題

pg_dumpによるバックアップ

pg_dumpコマンドを使い、バックアップ元DB（source-db）から deposit テーブルのバックアップを取得する。
ポイントとしては2つ。

  • --table オプションを対象のテーブルにパーティションテーブルが含まれるように正規表現で指定する
    • データの実体はパーティションテーブル側に存在しているため、親の deposit テーブルのみをpg_dumpしてもデータがエクスポートされない
  • --load-via-partition-root オプションはパーティションテーブルをpg_dumpする際には付与しておく
$ docker compose exec -it source-db /bin/bash
root@95e5c9a045fb:/# pg_dump -v -U ${POSTGRES_USER} \
  --dbname=${POSTGRES_DB} \
  --schema=public \
  --table="deposit|deposit_y*m*" \
  --load-via-partition-root \
  --format=custom \
  --file=/usr/share/app/dump_source-db.tar.gz

pg_restoreによるリストア

pg_restoreコマンドを使い、前項で取得したバックアップファイルからリストア先DB（dest-db）へのリストアを行う。

$ docker compose exec -it dest-db /bin/bash
root@63ec446c52a7:/# pg_restore -v --exit-on-error -U ${POSTGRES_USER} \
  --dbname=${POSTGRES_DB} \
  --format=custom \
  /usr/share/app/dump_source-db.tar.gz

補足

  • 今回のデモにおいてはテーブルそのものからバックアップ・リストアを行ったが、データのみの移行をしたい場合はpg_dumpの -a / --data-only オプションを使用すればよい
  • リストアに関してはpg_restoreではなくシンプルにpsqlコマンドに対する標準入力で行うやり方も存在するが、パーティションを切るようなテーブルの場合はサイズが大きいケースがほとんどだと思われるため、pg_dumpでの出力をtar.gzで圧縮、pg_restoreによるリストアとしている
    • pg_dumpを行う際にプレーンテキストの出力であれば、リストアはpsqlコマンドで出来る
    • 詳細はpg_dumpの -c / --format オプションを参照されたい

おわりに

パーティションテーブルでのバックアップ・リストアをCOPYモードとINSERT INTOモードでベンチマークを取ろうと思ったけど面倒くさくてやめた。
今回のデモのようにCOPYモードであれば圧倒的に速いことは違いない。

Discussion

ログインするとコメントできます