🐘
ポスグレのダンプファイルをポスグレを介さずDuckDBに取り込んでみた
900拠点分くらいの、ポスグレのダンプファイルがある。(所謂拠点毎の日次フルバックアップ)
これをデータ分析で使いたい⋯⋯
(自動で全拠点分をDBを分けて同一ホストのポスグレに全部復元するツールは作ってあるが如何せん使いづらい⋯)
「ポスグレダンプをDDLとDMLに分割して、DuckDBにぶち込むだけでDuckDBへのマイグレーションが完了した!!」
みたいな記事を見つけた。
やってみる
背景
900箇所の拠点で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
--
ダンプファイルの考察
ざっくりとこんな感じ。
- SET句による、環境設定。 →DuckDBには不要
- 各種DROP
- 制約、テーブル、拡張、スキーマ
- スキーマ作成
- スキーマ
- 拡張機能インストール →DuckDBには不要
- テーブル作成
- データ投入
- COPY文と標準入力から渡すテキスト
- 制約作成 →DuckDBには不要
- 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に引き込むのとどちらが早いかは謎⋯
(実データでは、データに混入している不正な制御コードとかでエラー発生しまくったので、データの整合性を担保するなら一回ポスグレに復元するのが安全⋯)
Discussion