Migrates PostgreSQL database from Genologics LIMS, from production to staging.

genologics postgresql migrate

#!/bin/bash -e
# Dumps a PostgresSQL over the network, from production to staging
DB="clarityDB"
DB_USER="clarity"

PROD=""
STAGING=""

echo "Preventing new connections from happening on $STAGING..."
ssh $STAGING "psql -U $DB_USER $DB -c 'REVOKE CONNECT ON DATABASE \"$DB\" FROM PUBLIC;'"

# http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it
echo "Killing all active PostgresSQL sessions on $STAGING..."
while true;
do
        # Annoyingly, some connections don't die at once
        ssh $STAGING "psql -U $DB_USER $DB -c 'SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE procpid <> pg_backend_pid();'"
        if (( $? == 0 )); then
                break;
        fi
done

#ssh $STAGING "psql -U $DB_USER $DB -c 'SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE procpid <> pg_backend_pid();'"
#ssh $STAGING "psql -U $DB_USER $DB -c 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname=$DB;'"

echo "Dropping and recreating $STAGING database..."

ssh $STAGING "dropdb -U $DB_USER $DB"
ssh $STAGING "createdb -U $DB_USER $DB"

echo "Dumping $PROD database $DB into $STAGING..."
pg_dump -U $DB_USER $DB | sed -e "s/$PROD/$STAGING/g" | ssh $STAGING "psql -U $DB_USER $DB"

echo "Re-enabling connections on $STAGING..."
ssh $STAGING "psql -U clarity clarityDB -c 'GRANT CONNECT ON DATABASE \"$DB\" TO PUBLIC'"