#!/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
コメント