Usage: `sh rebuild.sh somedatabase august /tmp/somedatabase-2023-09-07.dump`

rebuild-postgres-db.sh

#!/bin/bash

echo "Database: $1";
echo "User: $2";
echo "Dump path: $3";

read -r -p "Is this correct? [y/N] " response
case "$response" in
    [yY][eE][sS]|[yY]) echo 'starting...' ;;
    *) exit 1;;
esac


# drop schema
psql $1 -c "DROP SCHEMA public CASCADE";
psql $1 -c "CREATE SCHEMA public";

# import backup
echo "Import backup"
psql $1 < $3;

# reset privilages
echo "reset privilages"
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" $1` ; do  psql -c "alter table \"$tbl\" owner to $2" $1 ; done
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $1` ; do  psql -c "alter sequence \"$tbl\" owner to $2" $1 ; done
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" $1` ; do  psql -c "alter view \"$tbl\" owner to $2" $1 ; done
psql $1 -c "grant usage on schema public to public";
psql $1 -c "grant create on schema public to public";
echo "Done!"