← back to Designer Wallcoverings
catalog-push: diff-based redesign w/ two-bucket safety (sync only mac3-fuller/missing; flag Kamatera-fuller for review). Prevents the data-loss the blanket --clean would cause — 32/42 divergent tables are FULLER on Kamatera (spoonflower 125850 vs 573, osborne 14107 vs 787). +mkdir lock, --inserts, superuser source, re-grant, exclude vendor_catalog
65004b34ca72a73a040476a992c5899e15d47845 · 2026-07-09 23:08:54 -0700 · steve@designerwallcoverings.com
Files touched
M shopify/scripts/catalog-push-to-kamatera.sh
Diff
commit 65004b34ca72a73a040476a992c5899e15d47845
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Thu Jul 9 23:08:54 2026 -0700
catalog-push: diff-based redesign w/ two-bucket safety (sync only mac3-fuller/missing; flag Kamatera-fuller for review). Prevents the data-loss the blanket --clean would cause — 32/42 divergent tables are FULLER on Kamatera (spoonflower 125850 vs 573, osborne 14107 vs 787). +mkdir lock, --inserts, superuser source, re-grant, exclude vendor_catalog
---
shopify/scripts/catalog-push-to-kamatera.sh | 140 ++++++++++++++++++----------
1 file changed, 92 insertions(+), 48 deletions(-)
diff --git a/shopify/scripts/catalog-push-to-kamatera.sh b/shopify/scripts/catalog-push-to-kamatera.sh
index c6065306..b7406c50 100755
--- a/shopify/scripts/catalog-push-to-kamatera.sh
+++ b/shopify/scripts/catalog-push-to-kamatera.sh
@@ -1,58 +1,102 @@
#!/usr/bin/env bash
-# Standing mac3 → Kamatera catalog push (Steve 2026-07-08).
+# Standing mac3 → Kamatera catalog sync — DIFF-BASED (rewritten 2026-07-09 after the
+# full---clean version caused an incident: it dropped+reloaded all 142 tables every run,
+# stripped repl_user grants, hit pg_dump \restrict/COPY breakage, and stacked DB connections).
#
-# WHY: mac3 is the canonical dw_unified where vendor *_catalog staging tables are
-# built. The all.designerwallcoverings.com grid runs on Kamatera and reads KAMATERA's
-# dw_unified, which is NOT replicated from mac3 (mac3 has 0 outgoing replication; the
-# shopify_products sync bridges products via Shopify-as-intermediary, but the *_catalog
-# staging tables have no bridge). Result: new vendor catalog data (Pierre Frey, etc.)
-# never reaches Kamatera, so grid enrichment (manufacturer-SKU search, stock, specs)
-# goes stale. This job keeps Kamatera's *_catalog tables current. ONE-WAY mac3→Kamatera.
+# This version only refreshes tables whose row-count differs between mac3 and Kamatera, so a
+# normal run touches 0–few tables instead of 142. Lessons baked in:
+# 1. DIFF-BASED — compare per-table count(*); sync only the changed/missing ones.
+# 2. --inserts — pure-SQL INSERTs dodge the \restrict/COPY \. breakage (newer pg_dump vs psql 14).
+# 3. superuser source (peer postgresql:///dw_unified) — reads foreign-owned tables (e.g. sangetsu).
+# 4. re-GRANT SELECT to repl_user on every synced table — --clean's DROP strips grants
+# (the ALTER DEFAULT PRIVILEGES set by regrant.sh also auto-grants; this is belt-and-suspenders).
+# 5. flock — no overlapping runs stacking connections.
+# 6. EXCLUDE vendor_catalog (291k rows, not needed by the grid) + reachability guards + bash-3.2-safe.
+# ONE-WAY mac3→Kamatera. The grid picks up changes on its next ≤10-min snapshot refresh (no reload needed).
#
-# STRATEGY: FULL PER-TABLE REFRESH via --clean --if-exists. Each table is dropped +
-# recreated to match mac3's CURRENT schema, then reloaded. This is the only strategy
-# that self-heals against SCHEMA DRIFT — Kamatera stubs were columns behind mac3 (e.g.
-# pierre_frey_catalog: mac3 51 cols vs Kamatera 49), and additive INSERTs can't fill a
-# narrower target ("INSERT has more expressions than target columns"). dw_admin OWNS the
-# Kamatera catalog tables, so the DROP/CREATE succeed. psql runs WITHOUT ON_ERROR_STOP so
-# one bad table doesn't abort the rest. Brief per-table drop window is tolerable (off-peak;
-# the grid keeps its last in-RAM snapshot through a transient read miss). Propagates schema
-# changes, new rows, updates, AND deletes (true mirror of mac3's catalog tables).
+# Usage: catalog-push-to-kamatera.sh → APPLY (sync changed tables) [cron default]
+# catalog-push-to-kamatera.sh --dry-run → report what WOULD sync, touch nothing
set -uo pipefail
export PATH="/usr/local/bin:/opt/homebrew/bin:/opt/homebrew/opt/postgresql@14/bin:$PATH"
+DRY=0; [ "${1:-}" = "--dry-run" ] && DRY=1
LOG="$HOME/.claude/logs/catalog-push.log"; mkdir -p "$(dirname "$LOG")"
-exec >> "$LOG" 2>&1
-echo "==== $(date '+%Y-%m-%d %H:%M:%S') catalog-push START ===="
-
-# SOURCE = local peer auth as macstudio3, a mac3 SUPERUSER. pg_dump locks ACCESS SHARE
-# on every -t table up front and aborts the WHOLE dump if denied on even one; dw_admin
-# lacks read on foreign-owned tables (e.g. sangetsu_catalog), so we dump as the superuser
-# (bypasses per-table grants). TARGET stays dw_admin (only needs write on its own tables).
-MAC3="postgresql:///dw_unified"
-KAM='postgresql://dw_admin@127.0.0.1:5432/dw_unified'
-
-# reachability guards — never half-run
-psql "$MAC3" -tAc 'select 1' >/dev/null 2>&1 || { echo "mac3 dw_unified unreachable — abort"; exit 1; }
-ssh -o ConnectTimeout=15 my-server "psql '$KAM' -tAc 'select 1'" >/dev/null 2>&1 || { echo "Kamatera unreachable — abort"; exit 1; }
-
-# every *_catalog table on mac3 (bash 3.2-safe array read — macOS /bin/bash lacks mapfile)
-TABLES=()
-while IFS= read -r line; do [ -n "$line" ] && TABLES+=("$line"); done < <(psql "$MAC3" -tAc "select tablename from pg_tables where schemaname='public' and tablename ~ '^[a-z0-9_]+_catalog\$' order by 1")
-[ "${#TABLES[@]}" -eq 0 ] && { echo "no *_catalog tables on mac3 — abort"; exit 1; }
-ARGS=(); for t in "${TABLES[@]}"; do ARGS+=(-t "$t"); done
-echo "syncing ${#TABLES[@]} catalog tables"
-
-# single pass — full refresh: DROP + CREATE (to mac3's current schema) + reload, per table.
-# --inserts (NOT default COPY): mac3's newer pg_dump wraps output in \restrict, which puts
-# psql in restricted mode where backslash commands are rejected — COPY's \. terminator then
-# fails and tables get dropped-but-not-refilled. Pure-SQL INSERTs have no backslash, so they
-# load cleanly. --clean recreates each table at mac3's current schema first, so the INSERT
-# value-count matches (fixes the 51-vs-49 drift). No ON_ERROR_STOP → one bad table can't
-# abort the other 141.
+log(){ echo "$@" | tee -a "$LOG"; }
+
+MAC3="postgresql:///dw_unified" # peer superuser (reads ALL tables)
+KAM='postgresql://dw_admin@127.0.0.1:5432/dw_unified' # target (dw_admin owns catalog tables)
+EXCLUDE_RE="^vendor_catalog$" # too big, grid doesn't use it
+
+# single-run lock (mkdir is atomic + portable; macOS has no flock). Reclaim a >30-min stale lock.
+LOCKDIR=/tmp/catalog-push.lock.d
+if ! mkdir "$LOCKDIR" 2>/dev/null; then
+ if [ -n "$(find "$LOCKDIR" -maxdepth 0 -mmin +30 2>/dev/null)" ]; then
+ rmdir "$LOCKDIR" 2>/dev/null; mkdir "$LOCKDIR" 2>/dev/null || { echo "$(date '+%F %T') lock contended — skipping" >>"$LOG"; exit 0; }
+ else
+ echo "$(date '+%F %T') another catalog-push active — skipping" >>"$LOG"; exit 0
+ fi
+fi
+trap 'rmdir "$LOCKDIR" 2>/dev/null' EXIT
+
+log "==== $(date '+%F %T') catalog-push DIFF (dry-run=$DRY) ===="
+
+# reachability — never half-run
+psql "$MAC3" -tAc 'select 1' >/dev/null 2>&1 || { log "mac3 unreachable — abort"; exit 1; }
+ssh -o ConnectTimeout=15 my-server "psql '$KAM' -tAc 'select 1'" >/dev/null 2>&1 || { log "Kamatera unreachable — abort"; exit 1; }
+
+# counts SQL — a DO block that RAISE NOTICEs "tbl|count" for every *_catalog table that EXISTS
+# on that host (loops pg_class, so no missing-table errors), excluding vendor_catalog.
+cat > /tmp/catalog-counts.sql <<'SQL'
+DO $$
+DECLARE r record; n bigint;
+BEGIN
+ FOR r IN SELECT c.relname FROM pg_class c JOIN pg_namespace ns ON ns.oid=c.relnamespace
+ WHERE ns.nspname='public' AND c.relkind='r'
+ AND c.relname ~ '^[a-z0-9_]+_catalog$' AND c.relname <> 'vendor_catalog'
+ LOOP
+ EXECUTE format('SELECT count(*) FROM public.%I', r.relname) INTO n;
+ RAISE NOTICE '%|%', r.relname, n;
+ END LOOP;
+END $$;
+SQL
+
+# collect counts from both hosts (NOTICE → stderr → grep the tbl|n lines)
+psql "$MAC3" -f /tmp/catalog-counts.sql 2>&1 | grep -E 'NOTICE:.*\|' | sed -E 's/^.*NOTICE: //' | sort > /tmp/mac3_counts.txt
+ssh my-server "psql '$KAM' -f -" < /tmp/catalog-counts.sql 2>&1 | grep -E 'NOTICE:.*\|' | sed -E 's/^.*NOTICE: //' | sort > /tmp/kam_counts.txt
+log "mac3 catalog tables: $(grep -c '|' /tmp/mac3_counts.txt), Kamatera: $(grep -c '|' /tmp/kam_counts.txt)"
+
+# Classify divergent tables into two buckets (SAFETY: mac3 is NOT cleanly authoritative for
+# every table — e.g. Kamatera can hold a fuller kravet_catalog than mac3):
+# SYNC — missing on Kamatera, OR mac3 has MORE rows (mac3 fuller → propagate, gains rows)
+# REVIEW — Kamatera has MORE rows than mac3 (syncing would DELETE) → flag for Steve, NEVER auto-overwrite
+rm -f /tmp/catalog-sync.txt /tmp/catalog-review.txt
+awk -F'|' -v S=/tmp/catalog-sync.txt -v R=/tmp/catalog-review.txt '
+ NR==FNR{k[$1]=$2; seen[$1]=1; next}
+ { if(!($1 in seen)) { print $1 > S } # missing on Kamatera
+ else if($2+0 > k[$1]+0) { print $1 > S } # mac3 fuller
+ else if(k[$1]+0 > $2+0) { print $1" (mac3="$2" < kam="k[$1]")" > R } } # Kamatera fuller — review
+' /tmp/kam_counts.txt /tmp/mac3_counts.txt
+touch /tmp/catalog-sync.txt /tmp/catalog-review.txt
+NCOUNT=$(grep -c . /tmp/catalog-sync.txt || true)
+RCOUNT=$(grep -c . /tmp/catalog-review.txt || true)
+log "tables to sync (mac3-fuller/missing): $NCOUNT | flagged for review (Kamatera-fuller): $RCOUNT"
+[ "$NCOUNT" -gt 0 ] && sed 's/^/ → sync /' /tmp/catalog-sync.txt | tee -a "$LOG"
+[ "$RCOUNT" -gt 0 ] && { log " ⚠ REVIEW (NOT synced — Kamatera has more rows; Steve decides authority):"; sed 's/^/ ⚠ /' /tmp/catalog-review.txt | tee -a "$LOG"; }
+cp /tmp/catalog-sync.txt /tmp/catalog-need.txt
+
+if [ "$NCOUNT" -eq 0 ]; then log "nothing safe to sync"; log "==== $(date '+%F %T') DONE ===="; exit 0; fi
+if [ "$DRY" -eq 1 ]; then log "DRY RUN — re-run without --dry-run to apply the $NCOUNT safe syncs"; exit 0; fi
+
+# build -t args (bash-3.2-safe read)
+ARGS=(); while IFS= read -r t; do [ -n "$t" ] && ARGS+=(-t "$t"); done < /tmp/catalog-need.txt
+
+# refresh only the changed tables: DROP+CREATE (schema-current) + INSERT data
pg_dump "$MAC3" "${ARGS[@]}" --clean --if-exists --inserts --rows-per-insert=500 \
--no-owner --no-privileges 2>>"$LOG" \
| ssh my-server "psql '$KAM' -q" 2>>"$LOG" || true
-# summary — spot-check a couple of key tables
-ssh my-server "psql '$KAM' -tAc \"select 'pierre_frey_catalog='||count(*) from pierre_frey_catalog union all select 'pierre_frey_fabric_catalog='||count(*) from pierre_frey_fabric_catalog\"" 2>>"$LOG" || true
-echo "==== $(date '+%Y-%m-%d %H:%M:%S') catalog-push DONE ===="
+# re-GRANT SELECT to repl_user on the synced tables (--clean's DROP strips grants)
+{ while IFS= read -r t; do [ -n "$t" ] && echo "GRANT SELECT ON public.\"$t\" TO repl_user;"; done < /tmp/catalog-need.txt; } \
+ | ssh my-server "psql '$KAM' -q" 2>>"$LOG" || true
+
+log "synced $NCOUNT tables + re-granted repl_user; grid refreshes within ≤10 min"
+log "==== $(date '+%F %T') DONE ===="
← 8de32f95 WallQuest onboard: swatch DL + local qwen2.5vl enrich + room
·
back to Designer Wallcoverings
·
auto-save: 2026-07-09T23:12:38 (10 files) — pending-approval 9aa0dfb9 →