f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
143 lines
4.5 KiB
Bash
Executable file
143 lines
4.5 KiB
Bash
Executable file
#!/bin/bash
|
|
# sync-db.sh — Sync PostgreSQL database between prod (.205) and dev (.72)
|
|
#
|
|
# Usage:
|
|
# ./scripts/sync-db.sh dump # Create dump on current server
|
|
# ./scripts/sync-db.sh restore <file> # Restore dump on current server
|
|
# ./scripts/sync-db.sh push-to-dev # Dump + SCP to dev + restore on dev (run from prod)
|
|
# ./scripts/sync-db.sh pull-from-prod # SSH dump on prod + SCP + restore locally (run from dev)
|
|
|
|
set -euo pipefail
|
|
|
|
PROD_HOST="<LEGACY_IP>"
|
|
DEV_HOST="<DEV_IP>"
|
|
PROD_DB="godcrm_prod"
|
|
PROD_USER="godcrm"
|
|
DUMP_DIR="/tmp"
|
|
DUMP_FILE="godcrm_sync_$(date +%Y%m%d_%H%M%S).dump"
|
|
DUMP_PATH="${DUMP_DIR}/${DUMP_FILE}"
|
|
REMOTE_PROJECT="/root/production/business-crm"
|
|
|
|
# Colors
|
|
RED='\033[0;31m'
|
|
GREEN='\033[0;32m'
|
|
YELLOW='\033[1;33m'
|
|
NC='\033[0m'
|
|
|
|
log() { echo -e "${GREEN}[SYNC]${NC} $1"; }
|
|
warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
|
|
err() { echo -e "${RED}[ERROR]${NC} $1" >&2; exit 1; }
|
|
|
|
# Load .env from project root if available
|
|
load_env() {
|
|
local env_file="${REMOTE_PROJECT}/.env"
|
|
if [ -f "$env_file" ]; then
|
|
export $(grep -E '^POSTGRES_' "$env_file" | xargs)
|
|
export PGPASSWORD="${POSTGRES_PASSWORD:-}"
|
|
fi
|
|
}
|
|
|
|
cmd_dump() {
|
|
load_env
|
|
local db="${POSTGRES_DB:-$PROD_DB}"
|
|
local user="${POSTGRES_USER:-$PROD_USER}"
|
|
local host="${POSTGRES_HOST:-localhost}"
|
|
local port="${POSTGRES_PORT:-5432}"
|
|
|
|
log "Dumping database '$db' on ${host}:${port}..."
|
|
pg_dump -h "$host" -p "$port" -U "$user" -d "$db" \
|
|
-Fc --no-owner --no-privileges \
|
|
-f "$DUMP_PATH"
|
|
|
|
local size=$(du -h "$DUMP_PATH" | cut -f1)
|
|
log "Dump created: $DUMP_PATH ($size)"
|
|
echo "$DUMP_PATH"
|
|
}
|
|
|
|
cmd_restore() {
|
|
local dump_file="${1:-}"
|
|
[ -z "$dump_file" ] && err "Usage: $0 restore <dump_file>"
|
|
[ ! -f "$dump_file" ] && err "File not found: $dump_file"
|
|
|
|
load_env
|
|
local db="${POSTGRES_DB:-$PROD_DB}"
|
|
local user="${POSTGRES_USER:-$PROD_USER}"
|
|
local host="${POSTGRES_HOST:-localhost}"
|
|
local port="${POSTGRES_PORT:-5432}"
|
|
|
|
local size=$(du -h "$dump_file" | cut -f1)
|
|
log "Restoring $dump_file ($size) into '$db' on ${host}:${port}..."
|
|
|
|
warn "This will REPLACE all data in '$db'. Continuing in 3 seconds... (Ctrl+C to abort)"
|
|
sleep 3
|
|
|
|
pg_restore -h "$host" -p "$port" -U "$user" -d "$db" \
|
|
--clean --if-exists --no-owner --no-privileges \
|
|
-Fc "$dump_file" 2>&1 | grep -v "ERROR: .* does not exist" || true
|
|
|
|
log "Restore complete!"
|
|
|
|
# Restart backend if running
|
|
if command -v pm2 &>/dev/null; then
|
|
log "Restarting backend via pm2..."
|
|
pm2 restart business-crm 2>/dev/null || warn "pm2 restart skipped (process not found)"
|
|
fi
|
|
}
|
|
|
|
cmd_push_to_dev() {
|
|
log "=== PUSH TO DEV: dump locally, SCP to $DEV_HOST, restore ==="
|
|
|
|
# Step 1: Dump
|
|
local dump=$(cmd_dump)
|
|
|
|
# Step 2: SCP to dev
|
|
log "Copying dump to dev ($DEV_HOST)..."
|
|
scp "$dump" "root@${DEV_HOST}:${DUMP_DIR}/"
|
|
|
|
# Step 3: Restore on dev
|
|
log "Restoring on dev..."
|
|
ssh "root@${DEV_HOST}" "cd ${REMOTE_PROJECT} && bash scripts/sync-db.sh restore ${DUMP_DIR}/$(basename $dump)"
|
|
|
|
# Cleanup
|
|
rm -f "$dump"
|
|
ssh "root@${DEV_HOST}" "rm -f ${DUMP_DIR}/$(basename $dump)"
|
|
log "=== SYNC COMPLETE: prod → dev ==="
|
|
}
|
|
|
|
cmd_pull_from_prod() {
|
|
log "=== PULL FROM PROD: dump on $PROD_HOST, SCP here, restore ==="
|
|
|
|
# Step 1: Dump on prod
|
|
log "Creating dump on prod ($PROD_HOST)..."
|
|
local remote_dump=$(ssh "root@${PROD_HOST}" "cd ${REMOTE_PROJECT} && bash scripts/sync-db.sh dump" | tail -1)
|
|
|
|
# Step 2: SCP to local
|
|
log "Copying dump from prod..."
|
|
scp "root@${PROD_HOST}:${remote_dump}" "${DUMP_DIR}/"
|
|
local local_dump="${DUMP_DIR}/$(basename $remote_dump)"
|
|
|
|
# Step 3: Restore locally
|
|
cmd_restore "$local_dump"
|
|
|
|
# Cleanup
|
|
ssh "root@${PROD_HOST}" "rm -f ${remote_dump}"
|
|
rm -f "$local_dump"
|
|
log "=== SYNC COMPLETE: prod → dev ==="
|
|
}
|
|
|
|
# --- Main ---
|
|
case "${1:-help}" in
|
|
dump) cmd_dump ;;
|
|
restore) cmd_restore "${2:-}" ;;
|
|
push-to-dev) cmd_push_to_dev ;;
|
|
pull-from-prod) cmd_pull_from_prod ;;
|
|
*)
|
|
echo "Usage: $0 {dump|restore <file>|push-to-dev|pull-from-prod}"
|
|
echo ""
|
|
echo " dump Create pg_dump on current server"
|
|
echo " restore <file> Restore dump file on current server"
|
|
echo " push-to-dev Run from PROD: dump → SCP → restore on dev"
|
|
echo " pull-from-prod Run from DEV: SSH dump on prod → SCP → restore"
|
|
exit 1
|
|
;;
|
|
esac
|