Graham McIntire
b3c035793a
scripts/sync_prod_to_local.sh pulls only rows whose updated_at is newer than the corresponding local row, versus restore_prod_to_local which does a full pg_dump + drop + restore. For day-to-day refreshes the incremental path is dramatically cheaper — the full dump is ~12 GB, while an hourly incremental is typically a few thousand rows. Approach: - Discover every public table that carries `updated_at` (skips the oban_* runtime tables and schema_migrations by default). - For each, intersect the column list across local and prod so schema drift (a new column on prod not yet migrated locally, or vice-versa) doesn't break the sync — we transfer the columns that exist on both sides and leave the rest alone. - Resolve the ON CONFLICT target: primary key first, else the shortest non-partial unique index (important for partitioned tables like hrrr_profiles which have no top-level PK). - Stream the changed rows via `\COPY ... TO STDOUT` into a per-table temp file, then load into a LIKE-cloned temp table and UPSERT across `SET session_replication_role = replica` so FK ordering doesn't block the dev-only run. - Partitioned parents (relkind='p', relispartition=false) are kept in the discovery list; partition children (relkind='r', relispartition=true) are excluded so INSERTs route through the parent. Flags: `--dry-run` counts without writing; a positional argument limits the run to a single table. `SKIP_TABLES` env var overrides the exclusion list. Also added a pointer from restore_prod_to_local.sh's header comment to the new script.
101 lines
3.3 KiB
Bash
Executable file
101 lines
3.3 KiB
Bash
Executable file
#!/usr/bin/env bash
|
|
#
|
|
# Dump the production Postgres database and restore it into the local
|
|
# `prop_dev`, replacing whatever is there. Pulls prod credentials from
|
|
# `.envrc` (direnv handles the export) so there is no duplicated config.
|
|
#
|
|
# Usage:
|
|
#
|
|
# scripts/restore_prod_to_local.sh
|
|
#
|
|
# Before running: stop `mix phx.server` so Ecto doesn't hold connections.
|
|
# Requires direnv (or `source .envrc`) so PROP_PROD_DB_URL is set.
|
|
#
|
|
# For an *incremental* top-up (pull only rows changed since the last
|
|
# sync) see `scripts/sync_prod_to_local.sh` — same credential source,
|
|
# much faster for day-to-day refreshes.
|
|
|
|
set -euo pipefail
|
|
|
|
: "${PROP_PROD_DB_URL:?not set — run via direnv or 'source .envrc' first}"
|
|
|
|
LOCAL_DB="${LOCAL_DB:-prop_dev}"
|
|
LOCAL_USER="${LOCAL_USER:-postgres}"
|
|
LOCAL_HOST="${LOCAL_HOST:-localhost}"
|
|
LOCAL_TMP="${LOCAL_TMP:-/tmp}"
|
|
|
|
# Prod runs Postgres 18; pg_dump refuses to talk to a newer server than
|
|
# its own version. Pin to the Homebrew @18 binaries so this works on a
|
|
# machine whose default PATH still points at an older series.
|
|
PG_BIN="${PG_BIN:-/opt/homebrew/opt/postgresql@18/bin}"
|
|
PG_DUMP="${PG_BIN}/pg_dump"
|
|
PG_RESTORE="${PG_BIN}/pg_restore"
|
|
PSQL="${PG_BIN}/psql"
|
|
|
|
timestamp=$(date -u +'%Y%m%d_%H%M%S')
|
|
dump_file="${LOCAL_TMP}/prop_prod_${timestamp}.dump"
|
|
|
|
log() {
|
|
printf '%s restore_prod_to_local: %s\n' "$(date -u +'%Y-%m-%dT%H:%M:%SZ')" "$*"
|
|
}
|
|
|
|
die() {
|
|
log "ERROR: $*"
|
|
exit 1
|
|
}
|
|
|
|
[[ -x "$PG_DUMP" ]] || die "$PG_DUMP missing — brew install postgresql@18"
|
|
[[ -x "$PG_RESTORE" ]] || die "$PG_RESTORE missing — brew install postgresql@18"
|
|
[[ -x "$PSQL" ]] || die "$PSQL missing — brew install postgresql@18"
|
|
|
|
# Always remove the downloaded dump, even on mid-run failure. Keeps
|
|
# multi-GB dumps from accumulating in /tmp.
|
|
cleanup() {
|
|
[[ -n "${dump_file:-}" && -f "$dump_file" ]] && rm -f "$dump_file"
|
|
}
|
|
trap cleanup EXIT
|
|
|
|
log "dumping prod -> ${dump_file}"
|
|
# --verbose streams per-object progress to stderr so the run isn't a
|
|
# black box against a multi-GB remote DB.
|
|
"$PG_DUMP" \
|
|
--dbname="$PROP_PROD_DB_URL" \
|
|
--format=custom \
|
|
--compress=6 \
|
|
--no-owner \
|
|
--no-acl \
|
|
--verbose \
|
|
--file="$dump_file"
|
|
|
|
size=$(stat -f '%z' "$dump_file" 2>/dev/null || stat -c '%s' "$dump_file")
|
|
log "dump complete ($(numfmt --to=iec "$size" 2>/dev/null || echo "$size bytes"))"
|
|
|
|
# Kick any existing connections off the DB before dropping it — otherwise
|
|
# `DROP DATABASE` fails with "is being accessed by other users".
|
|
log "terminating existing connections to ${LOCAL_DB}"
|
|
"$PSQL" -h "$LOCAL_HOST" -U "$LOCAL_USER" -d postgres -v ON_ERROR_STOP=1 <<SQL >/dev/null
|
|
SELECT pg_terminate_backend(pid)
|
|
FROM pg_stat_activity
|
|
WHERE datname = '${LOCAL_DB}' AND pid <> pg_backend_pid();
|
|
SQL
|
|
|
|
log "dropping and recreating ${LOCAL_DB}"
|
|
"$PSQL" -h "$LOCAL_HOST" -U "$LOCAL_USER" -d postgres -v ON_ERROR_STOP=1 <<SQL >/dev/null
|
|
DROP DATABASE IF EXISTS ${LOCAL_DB};
|
|
CREATE DATABASE ${LOCAL_DB} OWNER ${LOCAL_USER};
|
|
SQL
|
|
|
|
# -j 4 parallelises table data restore; --verbose prints each object as
|
|
# workers pick it up so progress is visible.
|
|
log "restoring into ${LOCAL_DB}"
|
|
"$PG_RESTORE" \
|
|
-h "$LOCAL_HOST" \
|
|
-U "$LOCAL_USER" \
|
|
-d "$LOCAL_DB" \
|
|
--no-owner \
|
|
--no-acl \
|
|
--verbose \
|
|
-j 4 \
|
|
"$dump_file"
|
|
|
|
log "done — ${LOCAL_DB} now mirrors prod as of ${timestamp}"
|