[pgsql]テーブル定義をgitで管理する為にCREATE TABLE文をpg_dumpで自動生成する

PostgreSQLにて、テーブルのテーブル定義（create table文）をgitで管理したい。それらのテーブル定義はDBから自動的に生成されるようにしたい。
pg_dumpを用いて次のようなコマンドで指定したテーブルのcreate table文を抽出できる。

pg_dump -U postgres --schema-only -Fp --table=public.table01 mydb01 > create_table_public.table01.sql

しかし、出力される情報は以下のように、かなり冗長である。

pg_dump出力結果

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1
-- Dumped by pg_dump version 14.1

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;

SET default_tablespace = '';

SET default_table_access_method = heap;

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

CREATE TABLE public.table01 (
    id character varying(4) NOT NULL,
    num1 numeric(10,0),
    text1 character varying(100)
);


ALTER TABLE public.table01 OWNER TO postgres;

--
-- Name: TABLE table01; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.table01 IS 'テーブルID';


--
-- Name: COLUMN table01.num1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.table01.num1 IS '数値1';


--
-- Name: COLUMN table01.text1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.table01.text1 IS 'テキスト1';


--
-- Name: table01 table01_key0; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.table01
    ADD CONSTRAINT table01_key0 PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

上記のうち、必要なのは以下の情報のみである。

• CREATE TABLE
• COMMENT ON TABLE
• COMMENT ON COLUMN
• 一意制約の追加

それ以外の行は不要であるし、不要な行の中に環境によって変わるPostgreSQLのバージョン番号や、ユーザーID等が入っているのも、git管理するに当たって不都合と思われる。

findstrコマンドで不要な行を除外する

私の環境はwindowsなので、findstrコマンドを使って不要な行を除外してみた。

除外したい行と対応する正規表現は次の通り。

• コメント行："^--"
• SET で始まる行："^SET "
• SELECT で始まる行："^SELECT "
• 空行："^&"
• テーブルの所有者設定行："^ALTER TABLE .* OWNER TO "

上記をそれぞれ /C:オプションに指定し、正規表現オプション（/R）、一致行を除外するオプション（/V）を付けたfindstrをパイプに通した。

pg_dump -U postgres --schema-only -Fp --table=public.table01 mydb01 | findstr /R /V /C:"^--" /C:"^SET " /C:"^SELECT " /C:"^$" /C:"^ALTER TABLE .* OWNER TO " > create_table_public.table01.sql

すると、いい感じで不要な行が除外された。空行が一切ないのでちょっと詰まっているが運用上問題ないだろう。

フィルタ付きpg_dump出力結果

CREATE TABLE public.table01 (
    id character varying(4) NOT NULL,
    num1 numeric(10,0),
    text1 character varying(100)
);
COMMENT ON TABLE public.table01 IS 'テーブルID';
COMMENT ON COLUMN public.table01.num1 IS '数値1';
COMMENT ON COLUMN public.table01.text1 IS 'テキスト1';
ALTER TABLE ONLY public.table01
    ADD CONSTRAINT table01_key0 PRIMARY KEY (id);

指定するテーブル全てをバッチファイルでループ処理する

上記を踏まえ、別テーブルに指定したテーブル一覧を読み込んでテーブル定義を出力するバッチファイルを作成した。

output_schemas.bat

@echo off

set tablenames=output_schemas.config

setlocal EnableDelayedExpansion

for /F %%T in (%tablenames%) do (
    set tablename=%%T
    if not "!tablename:~0,1!"=="#" (
        echo create_table_%%T.sql
        pg_dump --schema-only -Fp --table=%%T %* | findstr /R /V /C:"^--" /C:"^SET " /C:"^SELECT " /C:"^$" /C:"^ALTER TABLE .* OWNER TO " > create_table_%%T.sql
    )
)

pause

output_schemas.config

public.table01
public.table02
public.table03

このように使う。mydb01は対象となるDB名。

output_schemas mydb01

hostやport、userなどを指定したい場合は次のように指定する。

output_schemas -h yourhost -p 5432 -U postgres mydb01

尚、そのまま実行するとコマンドの回数分パスワードを聞かれるので、パスワードファイルでパスワードを指定しておくのが良い。

パスワードファイルは%APPDATA%\postgresql\pgpass.confに保存する。%APPDATA%の場所が分からない場合は、エクスプローラのパスのところに直接%APPDATA%と入力してENTERを押せばその場所へジャンプする。

ファイルの各行の書式は次の通り。

hostname:port:database:username:password

複数行書ける。該当のホスト＋ポート＋データベース＋ユーザーでpg_dumpを実行しようとすると、ここに指定したパスワードが自動で使用される。

PostgreSQL用のPATH環境変数設定

ところで、PostgreSQLのコマンドがPATHに設定されていないと、上記のコマンドは実行できない。
事前にPATHを設定する必要がある。

Windows 10用のPostgreSQL14の場合、コマンド用exeのパスは次の場所となる。

%ProgramFiles%\PostgreSQL\14\bin

上記の場所を環境変数PATHに追加する。
詳しい方法は以下の記事を参照。

参考