# Golden template refresh — quick procedure (≈ 45 min hands-on) Condensed from the 2026-10-01/02 rebuild of VM 115 `logship-template-3` (192.168.12.67). Full background + every gotcha: RECLONE-RUNBOOK.md. Production VM 105 is never touched — the source is its PBS backup. ## 0. Before you start (5 min, on your Mac) 1. Pick a **new** VMID + IP (never rebuild in place — the old template stays the fallback until sign-off). Check: `ssh root@prox2.younex.de 'qm list; grep -hoE "ip=192\.168\.12\.[0-9]+" /etc/pve/qemu-server/*.conf | sort -V'` and `ping` the IP. 2. Diff what changed on prod since the last template, so the scripts cover it: ```bash ssh root@192.168.12.72 'grep -oE "^[A-Z_0-9]+=" /opt/erp-nuxt-frontend/.env | tr -d = | sort' > /tmp/env-keys.txt grep -oE "blank [A-Z_ 0-9]+" 03-neutralize-env.sh | tr ' ' '\n' | grep -v blank | sort > /tmp/env-blanked.txt comm -23 /tmp/env-keys.txt /tmp/env-blanked.txt # new keys: credential? -> add to blank list ssh root@192.168.12.72 'ls -A /opt/erp-nuxt-frontend/data' # new runtime dirs -> 00-defuse step 4 wipes data/ generically ssh root@192.168.12.72 'sudo -u postgres psql -d idempiere -Atc "select tablename from adempiere.ad_table where created > '"'"''"'"'"' ``` New transactional tables: add to pass 1 of `01-idempiere-truncate.sql` (the dynamic catch-all gets them anyway — but only if populated, and it reports them as `DYNAMIC target`). New **config** tables: add to `keep_tables` or they are wiped. 3. Stage the scripts: `scp reference/template-cleanup/* root@prox2.younex.de:/tmp/tc/` (a copy of the 2026-10-02 set also lives on prox2 in `/root/template-cleanup-src/`) ## 1. Restore + offline defuse (≈ 12 min, on prox2, one command) ```bash ssh root@prox2.younex.de nohup /tmp/tc/refresh-template.sh logship-template-N > /root/refresh-.log 2>&1 & tail -f /root/refresh-.log # restore ~8 min @ 350 MB/s, agent ~2 min, defuse ~1 min ``` It restores the newest PBS snapshot of VM 105, boots with the NIC `link_down`, copies all scripts in via the guest agent, runs `00-defuse.sh` (crontab, postfix, runtime data, /root/storage PDFs, deploy copies, ES bind, Check_MK registration) and `03-neutralize-env.sh `, then brings the NIC up. The log ends with the next commands. ## 2. Truncate (≈ 10–15 min, over SSH) ```bash ssh-keygen -R ssh root@ 'cd /root/idempiere-elastic && docker compose up -d' ssh root@ 'nohup /root/template-cleanup/run-cleanup.sh > /root/template-cleanup/cleanup.log 2>&1 &' ssh root@ 'tail -f /root/template-cleanup/cleanup.log | grep -E "=== |truncated|deleted|survivors|DYNAMIC|cycle-break|CLEANUP|FEHLER"' ``` Skip the `--dry-run`: it runs with iDempiere up (its housekeeping jobs queue behind the locks) and the real run has the same report plus pg_dump safety copies and rollback-on-error. Review in the log: - `DYNAMIC target …` — tables nobody listed; config? → `keep_tables`, rerun. - `cycle-break: unlinked …` — mutual FK links that were NULLed (expected: RMA↔shipment, RMA line↔return line, order↔payment). - `FK-protected survivors` — expected only 17 billing products, 4 locations, 1 partner (Standard), 1 user (logyouAdmin); `remaining orgs` = 1000000 only; `c_bpartner` zero-check = 3, `m_product` = 18. Anything else: `sudo -u postgres psql -d idempiere < diagnose-survivors.sql` (add the table there), fix the SQL, `run-cleanup.sh --no-dump` (idempotent). - `zero checks` all 0, `remaining orgs` = 1000000 only. ## 3. Validate (≈ 5 min) ```bash ssh root@ reboot; sleep 90 ssh root@ bash /root/template-cleanup/validate-template.sh ``` Everything must read as in the script's comments (services active, postfix inactive, crontab 0, cmk 0 connections, data/ = .gitkeep, storage 0 files, all zero checks 0, 4 LogShip attribute sets, Jasper processes, env blanked). Browser: `http://:3001` — login with a staff account (prod password at snapshot time), check empty grids + org list. ## 4. Finalize (≈ 5 min) — only after sign-off ```bash ssh root@ 'rm -rf /root/template-cleanup/backup/ /root/env.production.bak; history -c; rm -f /root/.bash_history; fstrim -av; poweroff' ssh root@prox2.younex.de 'qm set --memory 20480' # retire the old template + repoint provisioning: ssh root@prox2.younex.de 'qm destroy --purge; sed -i "s/^TEMPLATE_ID=.*/TEMPLATE_ID=/" /root/clone-logship-instance.sh' ``` New instance afterwards: `clone-logship-instance.sh … --domain ` → DNS A → 5.9.170.82 → `reference/edge-proxy/add-instance.sh --letsencrypt info@logyou.de` on edge-proxy-1. Then in the repo: `TEMPLATE_ID` in `clone-logship-instance.sh`, TEMPLATE-README.md header (VM/IP/date/GITHASH), README.md, and the memory note. Re-stage: `scp reference/template-cleanup/* root@prox2:/tmp/tc/`. ## What is deleted vs. kept (traffic data goes, configuration stays) | Goes (client 1000000) | Stays | |-----------------------|-------| | orders, invoices, shipments, payments, bank statements, RMAs, movements, inventories, productions, requests/tickets, contact activities, payment selections, accounting facts, stock, costs, ASI/serial/charge instances | doc types, price lists, taxes, charges, bank 1000001, warehouses/locators of org 1000000, roles, staff users (prod passwords!) | | all `cust_*` transactional/partner-linked tables (fee lines, commissions, storage usage, AP/Amazon inboxes, agreements, overrides, pricing rules) | `cust_commissiontable`, `cust_printer`, `cust_labeltemplate`, `cust_nonworkingdays`, `cust_shippingcost_default` (= `keep_tables`) | | partners + products except FK-protected config ones, partner contacts, locations | 3 partners, ~17 billing products, `m_shipper` rows, `c_ordersource` of org 0/1000000 (secrets scrubbed) | | orgs 1000001+ from every table, their roles/users | org 0 + 1000000, GardenWorld demo client | | logs, sessions, tokens, process instances, workflow runtime, `i_*`/`t_*`, ES queue, attachments **of transactional records only** | `ad_process` incl. Jasper (files under `/opt/idempiere-server/data/reports`), `ad_printformat*` + their logo attachments, 2Pack attachments, `ad_reference`/`ad_ref_list`, `ad_image`, `m_attributeset`, `ad_sysconfig` | Credentials in surviving config rows are NULLed by the scrub block at the end of the SQL (m_shipper DHL key/secret/user/pass + billing numbers, c_ordersource marketplace/ shopware/ebay/DHL/DPD secrets). `.env` secrets are blanked by `03-neutralize-env.sh`. ## What makes it fast now (keep these when editing the SQL) | Mechanism | Why | |-----------|-----| | Set-based RI: triggers off → bulk DELETE → one anti-join check per incoming FK (violation = subtransaction rollback) | per-row RI triggers were the dominant cost (6M trigger queries for one 294k-row table); engine now ~35 s total | | Pass 5 repeats pass 2/4 targets | partners/orgs unblocked only by the org purge in pass 3 | | Temporary indexes on every unindexed FK column pointing at a target table (created before, dropped after the engine) | RI checks per deleted row otherwise seq-scan referencing tables incl. dead pages — 30+ min per big table | | TRUNCATE instead of DELETE when all rows match + no incoming FK | instant, and gives a fresh empty relfilenode — otherwise later FK checks seq-scan the dead pages (ad_session × ad_changelog = hours) | | Generic reversal unlink (`reversal_id`, `reversalline_id`) + generic cycle-break for pass-1 targets | mutual references can never be deleted row-wise; nulling one side is harmless since both rows go | | Stage 2: collect every referenced id into `ref_ids` (one scan per populated referencing table), then ONE hashed anti-join DELETE | row-wise fallback over 56k rows took 25 min; a DELETE with 20–100 chained NOT EXISTS (c_order, c_bpartner) made the planner pick nested loops and ran > 30 min | | Dynamic catch-all (cust_*, document-FK columns, i_/t_/*log) | new tables are picked up without editing the list, each announced with its row count | Timings 2026-10-02 (2.3 GB idempiere DB, 8 cores): pre-engine prep (unlink, cycle-break, 3.5k temp indexes) ≈ 20 s, engine passes 1–5 ≈ 35 s, VACUUM FULL + Strapi + ES + restarts ≈ 3–4 min, pg_dump safety copies ≈ 2 min. Expected survivors: 17 billing products, 4 locations, 1 partner (Standard, via ad_clientinfo), 1 user (logyouAdmin, via c_acctprocessor.supervisor_id); remaining: 3 partners, 18 products, org 1000000 only. Eight earlier attempts without these mechanisms ran 1 h+ each and were killed.