Restoring DB backup of PostgreSQL >9.5

When you messed up your database of testing or production servers you usually want to restore a clean state as fast as possible with the backup and not mixing up date from the current state and the backup. An easy way to achieve this is to drop the database and create it again afterwards restore the dump.

But for PostgreSQL >9.5 there is the problem that you cannot drop a database if others are connected to the database which is very likely when you have services running.

An easy work around for this is if you have no other databases in use on the server therefore to not allow further connections then drop the database, create it again, restore the dump and then allow connections again.

The following bash script does exactly this for you and you can call it from the command line with the dbname and backup path as argument.



if [[ "$DBNAME" == "" ]] || [[ ! -d $BACKUP_PATH ]]; then
echo "Missing DBNAME as argument or BACKUP_PATH is not a directory"
exit 1

psql -d postgres -c "UPDATE pg_database SET datallowconn = ‘false’ WHERE datname = ‘$DBNAME’;"
service postgresql stop
service postgresql start
psql -d postgres -c "DROP DATABASE $DBNAME;"
psql -d postgres -c "CREATE DATABASE $DBNAME;"
pg_restore -d $DBNAME $BACKUP_PATH
psql -d postgres -c "UPDATE pg_database SET datallowconn = ‘true’ WHERE datname = ‘$DBNAME’;"

Example usage:

bash mydb /backup/mydb

Leave a Reply

Your email address will not be published. Required fields are marked *