Skip to content

Instantly share code, notes, and snippets.

@josser

josser/db_sync.sh

Created February 5, 2018 16:40
Show Gist options
  • Save josser/f5ba8f8808f68d44ff0287ead75d165c to your computer and use it in GitHub Desktop.
Save josser/f5ba8f8808f68d44ff0287ead75d165c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
db_sync.sh
#!/bin/bash
DB_TARGET_NAME='techcheck-dev';
DB_TARGET_USER='postgres';
DB_TARGET_PW='postgres';
DB_TARGET_HOST='';
DB_TARGET_PORT='5432';
DB_SOURCE_NAME='';
DB_SOURCE_USER='';
DB_SOURCE_PW='';
DB_SOURCE_HOST='';
DB_SOURCE_PORT='5432'
PG_DUMP='/usr/local/bin/pg_dump';
PG_RESTORE='/usr/local/bin/pg_restore';
PSQL='/usr/local/bin/psql';
export PGPASSWORD=$DB_TARGET_PW;
SQL_CMD="$PSQL -U $DB_TARGET_USER -h $DB_TARGET_HOST --port $DB_TARGET_PORT -d postgres -c";
echo $SQL_CMD;
echo 'Closing all opened connections to target DB:';
$SQL_CMD "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_TARGET_NAME';"
echo 'Recreating target database';
$SQL_CMD "DROP DATABASE \"$DB_TARGET_NAME\";"
$SQL_CMD "CREATE DATABASE \"$DB_TARGET_NAME\";"
echo "Starting migration";
PGPASSWORD=$DB_SOURCE_PW && $PG_DUMP --host $DB_SOURCE_HOST --port $DB_SOURCE_PORT --username $DB_SOURCE_USER -d $DB_SOURCE_NAME --format custom --blobs --no-privileges --verbose -Z=9 | PGPASSWORD=$DB_TARGET_PW $PG_RESTORE --host $DB_TARGET_HOST --port $DB_TARGET_PORT --username $DB_TARGET_USER --dbname $DB_TARGET_NAME --no-owner --no-privileges --verbose;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment