Move database from server to server

gistfile1.txt

#!/bin/bash

#set -ex # Set debug mode

SOURCE_DB="source_db"
SOURCE_USER="source_user"
SOURCE_PASSWORD="source_password"
SOURCE_HOST="source_host"

TARGET_DB="target_db"
TARGET_USER="target_user"
TARGET_PASSWORD="target_password"
TARGET_HOST="target_host"

BACKUP_FILE="/tmp/${SOURCE_DB}_backup.sql"

echo "Export source db"
export PGPASSWORD="$SOURCE_PASSWORD"
pg_dump -U "$SOURCE_USER" -h "$SOURCE_HOST" -Fc "$SOURCE_DB" --no-owner --no-privileges -f "$BACKUP_FILE"
unset PGPASSWORD
echo "... Done"

echo "delete target db if exists"
export PGPASSWORD="$TARGET_PASSWORD"
psql -U "$TARGET_USER" -h "$TARGET_HOST" -d postgres -c "DROP DATABASE IF EXISTS \"$TARGET_DB\";"

echo "create target db"
psql -U "$TARGET_USER" -h "$TARGET_HOST" -d postgres -c "CREATE DATABASE \"$TARGET_DB\";"
echo "... Done"
echo "Restore dump"
pg_restore -U "$TARGET_USER" -h "$TARGET_HOST" --no-owner --no-privileges -d "$TARGET_DB" "$BACKUP_FILE"
echo "... Done"

unset PGPASSWORD

echo "Done"