ポスグレのダンプファイルをポスグレを介さずDuckDBに取り込んでみた

９００拠点分くらいの、ポスグレのダンプファイルがある。（所謂拠点毎の日次フルバックアップ）
これをデータ分析で使いたい⋯⋯
（自動で全拠点分をDBを分けて同一ホストのポスグレに全部復元するツールは作ってあるが如何せん使いづらい⋯）

「ポスグレダンプをDDLとDMLに分割して、DuckDBにぶち込むだけでDuckDBへのマイグレーションが完了した！！」
みたいな記事を見つけた。

やってみる

背景

９００箇所の拠点でpg_dumpによりダンプファイルが日次で作成されている。
拠点で実行されるpg_dumpのオプションを変更したり、バックアップの方式を変更する事は難しい。

サンプルダンプファイル

ダンプファイルは以下のような形式。

dummyDB.dump

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.13
-- Dumped by pg_dump version 10.13

-- Started on 2025-01-19 00:45:30

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

ALTER TABLE ONLY public.dummy_table DROP CONSTRAINT pk_dummy_table;
DROP TABLE public.dummy_table;
DROP EXTENSION plpgsql;
DROP SCHEMA public;
--
-- TOC entry 2875 (class 0 OID 0)
-- Dependencies: 2874
-- Name: DATABASE dummy_db; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON DATABASE dummy_db IS 'sample database';


--
-- TOC entry 7 (class 2615 OID 16400)
-- Name: public; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA public;


--
-- TOC entry 2876 (class 0 OID 0)
-- Dependencies: 7
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA public IS 'standard public schema';


--
-- TOC entry 1 (class 3079 OID 16401)
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- TOC entry 2878 (class 0 OID 0)
-- Dependencies: 1
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 197 (class 1259 OID 16422)
-- Name: dummy_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.dummy_table (
    dummy_id smallint DEFAULT 0 NOT NULL,
    dummy_name character varying(20),
    dummy_description character varying(20)
);



--
-- TOC entry 2865 (class 0 OID 16422)
-- Dependencies: 197
-- Data for Name: dummy_table; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.dummy_table (dummy_id, dummy_name, dummy_description) FROM stdin;
0	ダミー1	DUMMY1
1	ダミー2	DUMMY2
2	ダミー3	DUMMY3
3	ダミー4	DUMMY4
4	ダミー5	DUMMY5
5	ダミー6	DUMMY6
7	ダミー7	DUMMY7
8	ダミー8	DUMMY8
6	ダミー9	DUMMY9
9	ダミー10	DUMMY10
31	ダミー11	DUMMY11
91	ダミー12	DUMMY12
14	ダミー13	DUMMY13
\.


--
-- TOC entry 2736 (class 2606 OID 19910)
-- Name: dummy_table pk_dummy_table; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.dummy_table
    ADD CONSTRAINT pk_dummy_table PRIMARY KEY (dummy_id);


--
-- TOC entry 2877 (class 0 OID 0)
-- Dependencies: 7
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: -
--

GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- TOC entry 2880 (class 0 OID 0)
-- Dependencies: 197
-- Name: TABLE dummy_table; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.dummy_table TO PUBLIC;


-- Completed on 2025-01-19 00:45:39

--
-- PostgreSQL database dump complete
--

ダンプファイルの考察

ざっくりとこんな感じ。

1. SET句による、環境設定。　→DuckDBには不要
2. 各種DROP
   1. 制約、テーブル、拡張、スキーマ
3. スキーマ作成
   1. スキーマ
   2. 拡張機能インストール　→DuckDBには不要
   3. テーブル作成
4. データ投入
   1. COPY文と標準入力から渡すテキスト
5. 制約作成　→DuckDBには不要
6. GRANT文による各種権限　→DuckDBには不要

DuckDBにぶち込むのに必要なものは2,3,4。

やってみた

事前準備

DDL部分を抜き出してファイル化

init.sql

DROP TABLE public.dummy_table;
DROP SCHEMA public;

CREATE SCHEMA public;

CREATE TABLE public.dummy_table (
    dummy_id smallint DEFAULT 0 NOT NULL,
    dummy_name character varying(20),
    dummy_description character varying(20)
);

DML部分を抜き出してSQLとTSVに変換

DML部分を抜き出す。

DML部分

COPY public.dummy_table (dummy_id, dummy_name, dummy_description) FROM stdin;
0	ダミー1	DUMMY1
1	ダミー2	DUMMY2
2	ダミー3	DUMMY3
3	ダミー4	DUMMY4
4	ダミー5	DUMMY5
5	ダミー6	DUMMY6
7	ダミー7	DUMMY7
8	ダミー8	DUMMY8
6	ダミー9	DUMMY9
9	ダミー10	DUMMY10
31	ダミー11	DUMMY11
91	ダミー12	DUMMY12
14	ダミー13	DUMMY13
\.

COPY文部分をinit.sqlに追記
stdinはこれから作るtsvファイルの名前に置き換える。

init.sql

COPY public.dummy_table (dummy_id, dummy_name, dummy_description) FROM 'dummy_table.tsv';

データ部分をTSVファイルとして保存。末尾の終端記号は取り除く。

dummy_table.tsv

0	ダミー1	DUMMY1
1	ダミー2	DUMMY2
2	ダミー3	DUMMY3
3	ダミー4	DUMMY4
4	ダミー5	DUMMY5
5	ダミー6	DUMMY6
7	ダミー7	DUMMY7
8	ダミー8	DUMMY8
6	ダミー9	DUMMY9
9	ダミー10	DUMMY10
31	ダミー11	DUMMY11
91	ダミー12	DUMMY12
14	ダミー13	DUMMY13

init.sql最終形

init.sql

DROP TABLE public.dummy_table;
DROP SCHEMA public;

CREATE SCHEMA public;

CREATE TABLE public.dummy_table (
    dummy_id smallint DEFAULT 0 NOT NULL,
    dummy_name character varying(20),
    dummy_description character varying(20)
);

COPY public.dummy_table (dummy_id, dummy_name, dummy_description) FROM 'dummy_table.tsv';

duckDB起動

以下のコマンドでinit.sqlで初期化しつつ起動。

duckdb -init init.sql

スキーマ込でデータ移行完了

まとめ

• ポスグレのダンプファイルをポスグレを介さずDuckDBに移行する事は確かに可能だっ⋯
• しかもpg_dumpの出力をほぼ変換無し流用が可能（pg_dumpする時に、スキーマダンプとデータダンプで分けて、データ部分はINSERT文で出力するようにしていれば、めっちゃ簡単では）

ダンプファイルをポスグレに復元してから、DuckDBに引き込むのとどちらが早いかは謎⋯
（実データでは、データに混入している不正な制御コードとかでエラー発生しまくったので、データの整合性を担保するなら一回ポスグレに復元するのが安全⋯）