はじめに

今回は、論理バックアップやデータ移行で活躍するpg_dumpについて調べてみた。また、合わせて関連ツールのpg_dumpallやpg_restoreの改造についても調べてみた。

調査対象

今回調査したのは、commit 9e360f0e8327bcf4b7c5ce957024fdff8f95f7deのもの。4/6の朝のものなので、この記事を書いているときから少し前のものだが・・・。比較対象はPostgreSQL 11.2。

Commitfestからのピックアップ

pg_dumpは古くからあるバックアップツールだが、現在でも改造されている。
PostgreSQL 12では以下の改造が入る予定(Commitfest 4/7時点の情報より)

pu_dumpの差分

pg_dumpの差分については、上記のcommitfestにリストアップされた項目以外の改造も入っているようだ(他のcommitfest項目の一部になっているのだろう、たぶん)。
PostgreSQL 11とPostgreSQL 12のpg_dumpのhelpを比較すると以下のような差分があるようだ。

27d26
<   -o, --oids                   include OIDs in dump
41a41
>   --extra-float-digits=NUM     override default setting for extra_float_digits
51a52
>   --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
52a54
>   --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
74c76
< Report bugs to <pgsql-bugs@postgresql.org>.
---
> Report bugs to <pgsql-bugs@lists.postgresql.org>.

(どうでもいいけど)さりげなく、バグ報告のアドレス(というかドメイン)が変わってるｗ
パラメータの差分を見てみる。

削除：--oids
- 出力結果にoid列を含める、というオプションなんだろうが、このオプションはPostgreSQL 12では廃止されたようだ。
追加：--extra-float-digits=NUM
- これは、パラメータ調査のときに挙げた浮動小数点の出力形式に関連するものかな。
追加：--on-conflict-do-nothing
- これはINSERT文出力モードのときに、PostgreSQL 9.5からサポートされた、INSERT ON CONFLICT構文の形でダンプを出力するものらしい。
追加：--rows-per-insert=NROWS
- これが、pg_dump multi VALUES INSERTに対応する項目だろうか。これをしないと、行単位にINSERT文のダンプが生成されるが、これを指定することで、NROWS文のデータを1つのINSERTにまとめてくれるようだ。

以下、此処の項目についてみてみる。

--oids オプション

PostgreSQL 12からOIDは設定不可に！

PostgreSQL 11までは、ユーザ作成のテーブルにoid列をつけることができた。
例えば、PostgreSQL 11まではpsqlの\dメタコマンドでoid列がついているテーブルを参照すると、以下のようにHas OIDs: yesという付加情報が出力されていた。

testdb=# \d+ table2
                                   Table "public.table2"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              |
 data1  | real    |           |          |         | plain    |              |
 data2  | text    |           |          |         | extended |              |
Has OIDs: yes

PostgreSQL 11までは、pg_dumpのオプションに--oidsを付与することで、OID列を設定していたテーブルをダンプする前に、以下のようなSET文を生成していた。

ALTER TABLE public.table1 OWNER TO postgres;

SET default_with_oids = true;

--
-- Name: table2; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.table2 (
    id integer,
    data1 real,
    data2 text
);

しかし、PostgreSQL 12からはユーザテーブルにoid列をつけることができなくなった。

PostgreSQL 12では、CREATE TABLEのコマンドから、WITH OIDS指定がなくなっている。
また、SET文でもdefault_with_oidsパラメータは指定できなくなっている。

PostgreSQL 11 -> PostgreSQL 12へのリストア時の注意点

PostgreSQL 11で作成された、WITH OIDSオプションつきのダンプファイルを、PostgreSQL 12のデータベースにリストアしようとすると、SET default_with_oids = true;が以下のようなエラーになってしまう。アバーッ！

psql:testdb.dmp:35: ERROR:  tables declared WITH OIDS are not supported

このエラーが発生してもリストア自体は継続されるが、当然ながら対象のテーブルはOID列がない状態で定義＆ロードされることになる。

PostgreSQL 12のpg_dumpで旧バージョンのDBをダンプしたら？

PostgreSQL 12のpg_dumpを使って、PostgreSQL 11以前のデータベース、かつOID列を含むテーブルをダンプしようとすると、以下のような警告が出力されるようになった。

$ pg_dump --version
pg_dump (PostgreSQL) 12devel
$ pg_dump -U postgres testdb > testdb_from_11_to_12.dmp
pg_dump: warning: WITH OIDS is not supported anymore (table "table2")
$

もちろん、このダンプファイルの内容には、SET default_with_oids = true;は含まれなくなる。

--extra-float-digits=NUM オプション

これは浮動小数点を含むテーブルをダンプするときに、使用可能な最大精度の代わりに、extra_float_digitsで指定した丸めた値をダンプするものっぽい。

例えば、以下のような値を保持しているreal型の列を、このオプションなしでpg_dumpを実行したときに、こんなダンプ結果が出力されるとする。

COPY public.table1 (id, data1, data2) FROM stdin;
1       0.00618094
2       0.81896394
3       0.2446924
4       0.0024819942
（略）

例えば、このテーブルに対して、--extra-float-digits=0オプションをつけてpg_dumpすると、以下のようなダンプ結果が生成される。

COPY public.table1 (id, data1, data2) FROM stdin;
1       0.00618094
2       0.818964
3       0.244692
4       0.00248199

最大有効桁が8桁から6桁に丸められている！

NUMの値は、現状、-15～3の範囲で指定可能である。
上記のrealの値に対して、NUMの指定を3～15まで変えるとこんな感じになる。

--extra-float-digitsの指定値	id=1の値	id=2の値	id=4の値
指定なし	0.00618094	0.81896394	0.0024819942
3	0.00618094	0.81896394	0.0024819942
0	0.00618094	0.818964	0.00248199
-3	0.00618	0.819	0.00248
-5	0.006	0.8	0.002
-15	0.006	0.8	0.002

このオプションだが、バックアップ目的で使う場合は（浮動小数点型の値の精度が落ちるため）使うべきではない。
では、どういうときに使うのか。たとえば、これを使うケースとしては、別のデータベース（浮動小数点を使わない or 多少雑に扱ってもいい）にデータをリストアしたいときに、ダンプファイルのサイズを小さくするのに役に立つのかもしれない・・・（本当にそういう目的がどうかは良くわからない）。

--on-conflict-do-nothing オプション

これはデータをCOPYではなく、INSERTコマンドで挿入するダンプ出力モードで有効なオプション。

--insertオプションをつけない場合、以下のようなCOPYコマンドの結果をダンプとして出力する。

$ pg_dump -U postgres testdb -t table1
(中略)
COPY public.table1 (id, data1, data2) FROM stdin;
1       0.00618094
2       0.81896394
(中略)
10      0.8915277
\.

--insertオプションをつけると、こんなダンプ出力になる。

$ pg_dump -U postgres testdb -t table1 --insert
(中略)
INSERT INTO public.table1 VALUES (1, 0.00618094, '                ');
INSERT INTO public.table1 VALUES (2, 0.81896394, '                ');
(中略)
INSERT INTO public.table1 VALUES (10, 0.8915277, '                ');

--insertと

$ pg_dump -U postgres testdb -t table1 --insert --on-conflict-do-nothing
(中略)
INSERT INTO public.table1 VALUES (1, 0.00618094, '                ') ON CONFLICT DO NOTHING;
INSERT INTO public.table1 VALUES (2, 0.81896394, '                ') ON CONFLICT DO NOTHING;
(中略)
INSERT INTO public.table1 VALUES (10, 0.8915277, '                ') ON CONFLICT DO NOTHING;

この機能、ダンプ出力後に、ダンプファイルを編集して特定のINSERT文にCONFLICT DO ...を自分で追記して、UPSERT相当に書き換える時に役に立つんだろうか・・・？

--rows-per-insert=NROWS

これも、--insertオプションがついたときに有効になる機能。
上記にも挙げたように、--insertオプションをつけると、1行毎にINSERT文を実行するダンプ結果を出力するけど、--rows-per-insert=NROWSを指定すると、Multi Row Insertの形式のINSERT文を生成するようになる。

pg_dump -U postgres testdb -t table1 --insert --rows-per-insert=3
(中略)
INSERT INTO public.table1 VALUES
        (1, 0.00618094, '                '),
        (2, 0.81896394, '                '),
        (3, 0.2446924, '                ');
INSERT INTO public.table1 VALUES
        (4, 0.0024819942, '                '),
        (5, 0.25659811, '                '),
        (6, 0.8484567, '                ');
INSERT INTO public.table1 VALUES
        (7, 0.54172105, '                '),
        (8, 0.17237735, '                '),
        (9, 0.3064296, '                ');
INSERT INTO public.table1 VALUES
        (10, 0.8915277, '                ');

なお、NUMSの範囲は1～2147483647(INT MAX)。0以下の数を指定すると以下のようなエラーになる。

$ pg_dump -U postgres testdb -t table1 --insert --rows-per-insert=0
pg_dump: error: rows-per-insert must be in range 1..2147483647

pg_dumpallの差分

pg_dumpallのコマンドパラメータ差分を比較するとこんな感じになる。

28a29,30
>   --exclude-database=PATTERN   exclude databases whose name matches PATTERN
>   --extra-float-digits=NUM     override default setting for extra_float_digits
39a42
>   --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
58c61
< Report bugs to <pgsql-bugs@postgresql.org>.
---
> Report bugs to <pgsql-bugs@lists.postgresql.org>.

--extra-float-digits=NUMと--on-conflict-do-nothingについては、pg_dumpと同じなので説明は割愛。
あと、pg_dumpallではINSERT文形式の出力オプションは対応しているが、pg_dumpのように、--rows-per-insert=NUMSの追加には対応していないようだ。なんでだろ？

--exclude-database=PATTERN オプション

pg_dumpall固有のオプションとしては、--exclude-database=PATTERNオプションが追加された。これはデータベース名のPATTERNを指定してデータベースを除外するものっぽい。
PATTERNの規則は、「psqlの\dと同じ」とのこと。なので、*などを記述して複数マッチするような規則も書ける。規則の詳細は、Patternsの節を参照。

pg_restoreの差分

pg_restoreのパラメータ差分はこんな感じ。

8c8
<   -f, --file=FILENAME      output file name
---
>   -f, --file=FILENAME      output file name (- for stdout)
64c64
< Report bugs to <pgsql-bugs@postgresql.org>.
---
> Report bugs to <pgsql-bugs@lists.postgresql.org>.

オプション自体の変更はないが、--fileオプションに、"-"を指定可能となった。
もともと、--file=<filename>のオプションをpg_restoreに指定すると、ダンプ用の出力結果を、<filename>で指定したファイルに書き出すのだが、PostgreSQL 12では、<filename>に-を指定することで、標準出力への書き出しが行えるようになった。

<failename>に普通のファイル名(hoge.txt)を指定した例。

$ pg_restore --format=c --file=hoge.txt testdb.gz
pg_restore: creating TABLE "public.table1"
pg_restore: creating TABLE "public.table2"
pg_restore: processing data for table "public.table1"
pg_restore: processing data for table "public.table2"
$

上の出力例の中にあるpg_restore: ...は標準エラー出力であることに注意。

<failename>に標準出力(-)を指定した例。標準エラー出力のみを画面に出力。

$ pg_restore --format=c --file=- testdb.gz 1> /dev/null
pg_restore: creating TABLE "public.table1"
pg_restore: creating TABLE "public.table2"
pg_restore: processing data for table "public.table1"
pg_restore: processing data for table "public.table2"
$

<failename>に標準出力(-)を指定した例。標準出力のみを画面に出力。

$ pg_restore --format=c --file=- testdb.gz 2> /dev/null
--
-- PostgreSQL database dump
--

-- Dumped from database version 12devel
-- Dumped by pg_dump version 12devel

SET statement_timeout = 0;
(中略)
--
-- Name: table1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.table1 (
    id integer,
    data1 real,
    data2 text
);


ALTER TABLE public.table1 OWNER TO postgres;
(中略)
--
-- Data for Name: table2; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.table2 (id, data1, data2) FROM stdin;
1       0.78704184
2       0.28236648
(中略)
--
-- PostgreSQL database dump complete
--

$

おわりに

今回の記事は、論理バックアップ処理で使う、3種類のユーティリティに関するPostgreSQL 12の改造点をまとめた。
結構、枯れたツールではあるけど、まだまだ改善点が残っていたのだなあと。

PostgreSQL 12がやってくる(5) - pg_dump/pg_dumpall/pg_restoreの変更