Reminder script used to automate postgresql related stuff

postgresql-drop-create.sh

#!/bin/bash

#-------------------------------------------------------------------------------
# RECREATE DATABASE FROM DDL AND DML SCRIPTS
#-------------------------------------------------------------------------------

BASEDIR="$( cd -P "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
SQLDIR="${BASEDIR}/sql"
PSQL_BIN="/usr/bin/psql"
PG_USER="<enter_pg_user_to_connect_with>"
PG_DB="<enter_pg_database_to_connect_to>"
PG_HOST="<enter_pg_host>"
PG_PORT="<enter_pg_port>"

# drop all tables and sequences
echo "Droping all tables and sequences..."

${PSQL_BIN} -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} ${PG_DB} -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'" | ${PSQL_BIN} -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} ${PG_DB}
${PSQL_BIN} -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} ${PG_DB} -t -c "select 'drop sequence \"' || relname || '\" cascade;' from pg_class where relkind='S'" | ${PSQL_BIN} -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} ${PG_DB}

# run ddl creation and dml insertions
echo "Updating database..."
for i in $(find ${SQLDIR} -name *.sql -o -name *.sql.zip); do
  echo "  Executing : ${i}"
  cmd="cat"
  if [[ ${i##*.} = "zip" ]]; then
    cmd="unzip -p"
  fi
  ${cmd} ${i} | ${PSQL_BIN} -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} ${PG_DB}
done

echo "Done"