refresh-db.sh

#!/bin/bash

PSQL="/usr/bin/psql"
PGUSER="dpaikkos"
DBNAME="for_import"
DUMP="www-latest.pg"
REMOTE="proxima"

echo "Fetch $DUMP from $REMOTE"
rm -f $DUMP
/usr/bin/rsync -azL --progress $REMOTE:/mnt/sdb1/splupload/backups/spl-live-06/latest.pg $DUMP

echo "Dropping and creating temporary database"
$PSQL -d postgres -U $PGUSER<<EOF
    DROP DATABASE IF EXISTS "${PGUSER}_old";
    DROP DATABASE IF EXISTS $DBNAME;
    CREATE DATABASE $DBNAME WITH OWNER $PGUSER;
EOF

echo "Importing data"
$PSQL -d $DBNAME -U $PGUSER -f $DUMP
echo "Create and clone database $PGUSER"
$PSQL -d postgres -U $PGUSER<<EOF
    BEGIN;
    ALTER DATABASE $PGUSER RENAME TO "${PGUSER}_old";
    ALTER DATABASE $DBNAME RENAME TO "$PGUSER";
    COMMIT;
EOF