Hatchbox - Sync CMS Database

This page explains how to sync the database from one hatchbox server to another. In this example, we're syncing the prod database with the staging database.

Note: This will not sync the S3 bucket storing all images. To do that please follow this tutorial.

Scroll down to line 47 to start the tutorial.

#!/usr/bin/env bash

# Function to log messages
log_message() {
    echo "$(date +"%Y-%m-%d %H:%M:%S") - $1"
}

execute_command() {
    LC_ALL=C LANG=C "$@"
    status=$?
    if [ $status -ne 0 ]; then
        log_message "Error with $1, exiting."
        exit $status
    fi
    return $status
}

remote_user="deploy"
remote_path="/home/deploy"

# server_1="16.171.240.56"
# db_1="db_a129eef0009d"
# user_1="user_d61b47f5f7ee"

# server_2="13.51.249.208"
# db_2="db_0fef4ca56aee"
# user_2="user_781c7b3a7737"

server_1="13.51.249.208"
db_1="db_0fef4ca56aee"
user_1="user_781c7b3a7737"

server_2="16.171.240.56"
db_2="db_a129eef0009d"
user_2="user_d61b47f5f7ee"

local_path="."

current_time=$(date +"%Y%m%d%H%M%S")
backup_file="${db_1}-backup-${current_time}.bak"

echo "Syncing database from ${server_1} -> ${server_2}"

read -s -p "Enter the database password for (from) user ${user_1}: " db_password
echo  # Newline for better formatting

# Step 1: Dump the database
execute_command ssh -t ${remote_user}@${server_1} "PGPASSWORD=${db_password} pg_dump -h localhost -U ${user_1} -d ${db_1} -F c -f \"${remote_path}/${backup_file}\""

log_message "Database dumped to ${backup_file}"

# Step 2: Copy file to local machine
execute_command scp "${remote_user}@${server_1}:${remote_path}/${backup_file}" "${local_path}/${backup_file}"

log_message "Database dump copied to local machine"

# Step 3: Copy the dump file to another server
execute_command scp "${local_path}/${backup_file}" "${remote_user}@${server_2}:${remote_path}"

log_message "Database dump copied to ${server_2}"

read -s -p "Enter the database password for (to) ${user_2}: " db_password_staging
echo  # Newline for better formatting

# Step 4: Import the database on the remote server
execute_command ssh -t ${remote_user}@${server_2} "PGPASSWORD=${db_password_staging} pg_restore -U ${user_2} -h 127.0.0.1  -d ${db_2} --no-acl --no-owner \"${remote_path}/${backup_file}\""

log_message "Database imported on remote server"