Copy Postgres prod to staging when connected via Wireguard

copy_prod_to_staging.sh

#!/bin/bash

# Script to copy production database to staging environment
set -e  # Exit on any error

if [ -z "$PROD_DATABASE_URL" ] || [ -z "$STAGING_DATABASE_URL" ]; then
    echo "Error: Database URLs not found in environment"
    echo "Make sure you have set up .envrc with PROD_DATABASE_URL and STAGING_DATABASE_URL"
    exit 1
fi

# Extract database name from STAGING_DATABASE_URL
STAGING_DB_NAME=$(echo "$STAGING_DATABASE_URL" | sed -n 's/.*\/\([^?]*\).*/\1/p')

echo "Terminating existing connections to staging database..."
psql "$STAGING_DATABASE_URL" -c "
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = current_database() 
  AND pid <> pg_backend_pid();"

# Connect to postgres database to drop and recreate the staging database
POSTGRES_URL=$(echo "$STAGING_DATABASE_URL" | sed 's/\/[^/]*$//g')/postgres

echo "Dropping and recreating staging database..."
psql "$POSTGRES_URL" -c "DROP DATABASE IF EXISTS $STAGING_DB_NAME;"
psql "$POSTGRES_URL" -c "CREATE DATABASE $STAGING_DB_NAME;"

echo "Copying production database to staging..."
pg_dump --verbose --clean --no-owner --no-privileges "$PROD_DATABASE_URL" | \
    psql "$STAGING_DATABASE_URL"

echo "Database copy completed successfully!" 

echo "Copying production database to staging..."
pg_dump --verbose --clean --if-exists --no-owner --no-privileges "$PROD_DATABASE_URL" | \
    psql "$STAGING_DATABASE_URL"

echo "Database copy completed successfully!"