PostgreSQLデータベース初期化とSQL実行を自動化するシェルスクリプト

PostgreSQLを使用したシステム開発では、データベースの初期化やロール設定、テーブル・データの投入作業を効率化することが重要です。
本記事では、それらの作業を自動化するシェルスクリプトを紹介します。

シェルスクリプトの全体

このスクリプトは、以下の機能を備えています。

以下は、今回紹介する完全なシェルスクリプトです。

#!/bin/bash

# パラメータ
IPADDRESS=localhost
PORT=5432
USER=postgres
DATABASE=postgres
ROLEUSERS=("postgres")
ROLEPASSWORD=("postgres")

# パスワード設定
export PGPASSWORD='postgres'

# 権限付与の場合
TARGET_USER="postgres"

# SSLモード OFF
# export PGSSLMODE='disable'

# 文字コード設定：UTF8
export PGCLIENTENCODING=UTF8

# データベースの追加
DB_EXISTS=$(psql -h $IPADDRESS -p $PORT -U $USER -lqt | cut -d \| -f 1 | grep -wq $DATABASE && echo "true" || echo "false")

if [ "$DB_EXISTS" == "false" ]; then
    psql -h $IPADDRESS -p $PORT -U $USER -c "CREATE DATABASE $DATABASE;"
fi

# ロールの追加
for ((i=0; i<${#ROLEUSERS[@]}; i++)); do
    if ! psql -h "$IPADDRESS" -p "$PORT" -U "$USER" -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='${ROLEUSERS[$i]}'" | grep -q 1; then
        psql -h "$IPADDRESS" -p "$PORT" -U "$USER" -c "CREATE ROLE \"${ROLEUSERS[$i]}\" LOGIN PASSWORD '${ROLEPASSWORD[$i]}';"
    fi
done

# テーブル削除
psql -h $IPADDRESS -p $PORT -U $USER -d $DATABASE << EOF
DO \$\$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || tbl.tablename || ' CASCADE';
    END LOOP;
END \$\$;
EOF

# シーケンス削除
psql -h $IPADDRESS -p $PORT -U $USER -d $DATABASE << EOF
DO \$\$
DECLARE
    seq RECORD;
BEGIN
    FOR seq IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema='public' LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS public.' || seq.sequence_name || ' CASCADE';
    END LOOP;
END \$\$;
EOF

# 全テーブル追加
for sql_file in $(ls -v ./ddl/*.sql); do
    psql -h $IPADDRESS -p $PORT -U $USER -d $DATABASE -f $sql_file
done

# TARGET_USERが空でない場合にのみ処理を実行
if [ -n "$TARGET_USER" ]; then
psql -h $IPADDRESS -p $PORT -U $USER -d $DATABASE << EOF
DO \$\$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'GRANT ALL ON TABLE public.' || quote_ident(tbl.tablename) || ' TO \"$TARGET_USER\";';
    END LOOP;
END \$\$;
EOF

psql -h $IPADDRESS -p $PORT -U $USER -d $DATABASE << EOF
DO \$\$
DECLARE
    seq RECORD;
BEGIN
    FOR seq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public') LOOP
        EXECUTE 'GRANT SELECT, USAGE ON SEQUENCE public.' || quote_ident(seq.sequence_name) || ' TO \"$TARGET_USER\";';
    END LOOP;
END \$\$;
EOF
fi

# 全データ追加
for sql_file in $(ls -v ./data/*.sql); do
    psql -h $IPADDRESS -p $PORT -U $USER -d $DATABASE -f $sql_file
done

スクリプトの解説

1. データベースの存在確認と作成

DB_EXISTS=$(psql -h $IPADDRESS -p $PORT -U $USER -lqt | cut -d \| -f 1 | grep -wq $DATABASE && echo "true" || echo "false")
if [ "$DB_EXISTS" == "false" ]; then
    psql -h $IPADDRESS -p $PORT -U $USER -c "CREATE DATABASE $DATABASE;"
fi

ここでは、データベースの存在を確認し、存在しない場合に新規作成します。

2. ロールの追加

for ((i=0; i<${#ROLEUSERS[@]}; i++)); do
    if ! psql -h "$IPADDRESS" -p "$PORT" -U "$USER" -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='${ROLEUSERS[$i]}'" | grep -q 1; then
        psql -h "$IPADDRESS" -p "$PORT" -U "$USER" -c "CREATE ROLE \"${ROLEUSERS[$i]}\" LOGIN PASSWORD '${ROLEPASSWORD[$i]}';"
    fi
done

指定したロールが存在しない場合、新しいロールを作成します。

3. テーブル・シーケンス削除

テーブルやシーケンスの削除処理には、PostgreSQLのDOブロックを使用しています。

実行方法

1. スクリプトを任意のディレクトリに保存します。
2. 必要に応じてパラメータを設定します。
3. 実行権限を付与します：chmod 755 reset_table.sh
4. スクリプトを実行します：./reset_table.sh

注意点

データベースやロール名、パスワードは慎重に設定してください。
本番環境で使用する場合はバックアップを取得しておきましょう。
これで、PostgreSQLの初期化を効率的に行えるようになります。
ぜひ活用してください！

• Original:https://minory.org/postgresql-auto-sql-shell.html
• Source:minory
• Author:管理者

Amazonベストセラー