`scripts/sync-deals-to-kamatera.sh`

25 lines
#!/bin/bash
# Refresh the deal-assets viewer's DB snapshot on Kamatera usre from Mac2's live data.
# recent_commercial_deals is a VIEW over 3.5GB of base tables that DON'T exist on Kamatera,
# so we materialize it (3.2k rows) here and atomically swap it in on prod. The two curated
# asset tables (15 rows each) are reloaded too. Called by dtt-cron.sh (4x/day). $0 (local pg).
set -euo pipefail
export PATH="/opt/homebrew/bin:/opt/homebrew/opt/postgresql@14/bin:/usr/bin:/bin:/usr/sbin:/sbin"
K=root@45.61.58.125
# 1) materialize a fresh snapshot on Mac2 + dump it (+ the curated asset rows)
psql usre -q -c "DROP TABLE IF EXISTS rcd_snap; CREATE TABLE rcd_snap AS TABLE recent_commercial_deals;"
pg_dump usre --no-owner --no-privileges -t rcd_snap > /tmp/reflyers-rcd.sql
pg_dump usre --no-owner --no-privileges --data-only -t external_marketing_asset -t asset_subject_link > /tmp/reflyers-assets.sql
psql usre -q -c "DROP TABLE IF EXISTS rcd_snap;"   # clean up the local temp table
# 2) ship + apply on Kamatera with an atomic swap (near-zero downtime)
scp -q /tmp/reflyers-rcd.sql /tmp/reflyers-assets.sql "$K:/tmp/"
ssh "$K" '
  set -e
  psql usre -q -c "DROP TABLE IF EXISTS rcd_snap;"
  psql usre -q -f /tmp/reflyers-rcd.sql
  psql usre -q -c "BEGIN; DROP TABLE IF EXISTS recent_commercial_deals; ALTER TABLE rcd_snap RENAME TO recent_commercial_deals; COMMIT;"
  psql usre -q -c "TRUNCATE external_marketing_asset, asset_subject_link RESTART IDENTITY;"
  psql usre -q -f /tmp/reflyers-assets.sql
'
echo "refreshed deals snapshot on Kamatera $(date '+%Y-%m-%d %H:%M:%S')"