GITGUD

neiam neiam /fieri-forever

the flavortown lunch engine

public · Issues · Pulls · Labels · Forks · Compare · Actions success · Badges · Packages

⭐

Log in to mark this repository.

7.3 KiB · text History 904aa0c 
#!/usr/bin/env bash
# Copy the dev database into prod, so prod starts with your areas,
# restaurants, ranks and history instead of re-entering them by hand.
#
#   scripts/db-transfer.sh export [file]   dev Postgres  -> dump file
#   scripts/db-transfer.sh import <file>   dump file     -> prod (REPLACES it)
#
# export works on a throwaway copy inside the dev container, so the dev
# database itself is never changed. In the copy it:
#   - empties sessions, device tokens and invites (they belong to dev:
#     cookies for localhost, tokens for this machine, links to dev URLs);
#   - deletes DROP_USERS (default: the alice/bob/carol/dave test accounts,
#     whose password is public) and DROP_AREAS (default: the "Boston office"
#     test area). Restaurants, lunches and outings they created stay, with
#     the creator left blank. Set either to "" to keep everything.
#
# import first saves a dump of whatever prod has now (prod-backup-*.dump,
# restorable with this same command), then stops the app, replaces the
# fieri_eternal database with the file, and starts the app again; its boot
# migrations run as usual. It asks before touching anything unless --yes.
#
# Environment:
#   DEV_CONTAINER  dev Postgres container   (fieri-eternal-dev-pg)
#   DEV_USER, DEV_DB                         (app_user, app)
#   ENGINE         podman or docker          (podman)
#   KUBE_CONTEXT   kubectl context for prod  (kubectl's current one)
#   NAMESPACE      prod namespace            (fieri-eternal)
set -euo pipefail

DEV_CONTAINER=${DEV_CONTAINER:-fieri-eternal-dev-pg}
DEV_USER=${DEV_USER:-app_user}
DEV_DB=${DEV_DB:-app}
ENGINE=${ENGINE:-podman}
NAMESPACE=${NAMESPACE:-fieri-eternal}
PROD_DB=fieri_eternal
DEPLOYMENT=fieri-eternal
CLUSTER=postgres
DROP_USERS=${DROP_USERS-alice bob carol dave}
DROP_AREAS=${DROP_AREAS-Boston office}

die() { echo "error: $*" >&2; exit 1; }
say() { echo "==> $*" >&2; }
dev() { "$ENGINE" exec -i "$DEV_CONTAINER" "$@"; }
kc() { kubectl ${KUBE_CONTEXT:+--context "$KUBE_CONTEXT"} -n "$NAMESPACE" "$@"; }

# Single-quoted SQL literal list: "a b" -> 'a','b'
sql_list() {
    local out="" w
    for w in "$@"; do out+="${out:+,}'${w//\'/\'\'}'"; done
    echo "$out"
}

cmd_export() {
    local file=${1:-fieri-$(date +%Y%m%d-%H%M%S).dump}
    local stage=fieri_export_stage
    [[ -e $file ]] && die "$file already exists"

    say "copying $DEV_DB into a staging database in $DEV_CONTAINER"
    dev dropdb -U "$DEV_USER" --if-exists "$stage"
    dev createdb -U "$DEV_USER" "$stage"
    # Expanded now: $stage is local, and gone by the time EXIT fires.
    trap "dev dropdb -U '$DEV_USER' --if-exists '$stage' >/dev/null 2>&1 || true" EXIT
    dev pg_dump -U "$DEV_USER" -Fc "$DEV_DB" | dev pg_restore -U "$DEV_USER" -d "$stage" --no-owner

    local users areas
    # shellcheck disable=SC2086  # word-split usernames on purpose
    users=$(sql_list $DROP_USERS)
    areas=$( [[ -n $DROP_AREAS ]] && sql_list "$DROP_AREAS" || true)
    say "pruning: dev-only rows${users:+; users $DROP_USERS}${areas:+; area \"$DROP_AREAS\"}"
    dev psql -U "$DEV_USER" -d "$stage" -v ON_ERROR_STOP=1 -q <<SQL
BEGIN;
TRUNCATE user_sessions, api_tokens, registration_token_areas, registration_tokens;
${areas:+DELETE FROM areas WHERE name IN ($areas);}
${users:+
CREATE TEMP TABLE gone AS SELECT id FROM users WHERE username IN ($users);
UPDATE areas            SET created_by = NULL WHERE created_by IN (SELECT id FROM gone);
UPDATE restaurants      SET added_by   = NULL WHERE added_by   IN (SELECT id FROM gone);
UPDATE lunches          SET logged_by  = NULL WHERE logged_by  IN (SELECT id FROM gone);
UPDATE outings          SET created_by = NULL WHERE created_by IN (SELECT id FROM gone);
UPDATE area_invitations SET invited_by = NULL WHERE invited_by IN (SELECT id FROM gone);
DELETE FROM users WHERE id IN (SELECT id FROM gone);
}
COMMIT;
SQL

    dev pg_dump -U "$DEV_USER" -Fc --no-owner --no-privileges "$stage" > "$file"
    say "wrote $file. It holds:"
    dev psql -U "$DEV_USER" -d "$stage" -At -F ': ' <<'SQL' >&2
SELECT 'users', string_agg(username, ', ' ORDER BY username) FROM users
UNION ALL SELECT 'areas', string_agg(name, ', ' ORDER BY name) FROM areas
UNION ALL SELECT 'restaurants', count(*)::text FROM restaurants
UNION ALL SELECT 'ranks', count(*)::text FROM rankings
UNION ALL SELECT 'lunches', count(*)::text FROM lunches;
SQL
}

# The StackGres primary: the pod labelled role=master (older StackGres) or
# role=primary, else the only pod there is.
primary_pod() {
    local sel="stackgres.io/cluster-name=$CLUSTER" role pod
    for role in master primary; do
        pod=$(kc get pods -l "$sel,role=$role" -o name 2>/dev/null | head -1)
        [[ -n $pod ]] && { echo "${pod#pod/}"; return; }
    done
    pod=$(kc get pods -l "$sel" -o name | head -1)
    [[ -n $pod ]] || die "no Postgres pod for SGCluster $CLUSTER in $NAMESPACE"
    echo "${pod#pod/}"
}

cmd_import() {
    local file=${1:-} yes=${2:-}
    [[ -n $file ]] || die "usage: $0 import <file> [--yes]"
    [[ -f $file ]] || die "$file not found"
    pg_restore --list "$file" >/dev/null 2>&1 \
        || { dev pg_restore --list < "$file" >/dev/null || die "$file isn't a pg_dump custom-format file"; }

    local pod; pod=$(primary_pod)
    local ctx; ctx=$(kubectl ${KUBE_CONTEXT:+--context "$KUBE_CONTEXT"} config current-context)
    pg() { kc exec -i "$pod" -c patroni -- "$@"; }

    echo "This REPLACES database $PROD_DB in $ctx/$NAMESPACE (pod $pod) with $file." >&2
    if [[ $yes != --yes ]]; then
        read -r -p "Type the namespace ($NAMESPACE) to go ahead: " answer
        [[ $answer == "$NAMESPACE" ]] || die "not confirmed"
    fi

    local exists backup=""
    exists=$(pg psql -U postgres -Atc "SELECT 1 FROM pg_database WHERE datname='$PROD_DB'")
    if [[ $exists == 1 ]]; then
        backup=prod-backup-$(date +%Y%m%d-%H%M%S).dump
        say "backing up prod to $backup"
        pg pg_dump -U postgres -Fc "$PROD_DB" > "$backup"
    fi

    local replicas
    replicas=$(kc get deployment "$DEPLOYMENT" -o jsonpath='{.spec.replicas}' 2>/dev/null || echo 1)
    [[ $replicas == 0 ]] && replicas=1
    say "stopping the app"
    kc scale deployment "$DEPLOYMENT" --replicas=0
    kc wait --for=delete pod -l name="$DEPLOYMENT" --timeout=60s 2>/dev/null || true
    # If anything below fails, still bring the app back up (on the backup's
    # data, which is still there unless the restore got partway).
    trap "say 'starting the app again'; kc scale deployment '$DEPLOYMENT' --replicas='$replicas' >/dev/null" EXIT

    say "replacing $PROD_DB"
    pg psql -U postgres -v ON_ERROR_STOP=1 -q -o /dev/null <<SQL
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$PROD_DB' AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS $PROD_DB;
CREATE DATABASE $PROD_DB;
SQL
    pg pg_restore -U postgres -d "$PROD_DB" --no-owner --no-privileges --exit-on-error < "$file"

    pg psql -U postgres -d "$PROD_DB" -At -F ': ' >&2 <<'SQL'
SELECT 'users', string_agg(username, ', ' ORDER BY username) FROM users
UNION ALL SELECT 'areas', string_agg(name, ', ' ORDER BY name) FROM areas
UNION ALL SELECT 'restaurants', count(*)::text FROM restaurants;
SQL
    say "done${backup:+; the old prod data is in $backup}"
}

case ${1:-} in
    export) shift; cmd_export "$@" ;;
    import) shift; cmd_import "$@" ;;
    *) sed -n '2,/^set -e/p' "$0" | sed '$d; s/^# \{0,1\}//' >&2; exit 2 ;;
esac