# Re-clone runbook — production → clean template VM

Complete, tested procedure (built 2026-07-26 for VM 113 `logship-template-2`,
rerun 2026-10-01 for VM 115 `logship-template-3`, 192.168.12.67). Repeat it
whenever the template should be refreshed from current production — build the
new template under a NEW VMID/IP, keep the old one until sign-off, then destroy
it and switch `TEMPLATE_ID` in `clone-logship-instance.sh` (repo + prox2 copy).
Every command was actually run; gotchas are inline.

Hosts: `prox2` = prox2.younex.de (Proxmox), `CLONE` = the new VM's IP.
Production VM 105 is NEVER touched — the clone source is its PBS backup.

## Phase 1 — Restore from PBS backup (zero prod impact)

```bash
ssh root@prox2
pvesm list pbs-logyou --vmid 105 | tail -1          # pick latest snapshot
qmrestore pbs-logyou:backup/vm/105/<SNAPSHOT> <NEWID> --storage Disk1-NVMe --unique 1
# ~6 min at 350 MB/s. --unique regenerates MAC/UUID/vmgenid.

qm set <NEWID> --name <NAME> --onboot 0 --protection 0 \
  --ipconfig0 "ip=<CLONE>/24,gw=192.168.12.1"
MAC=$(qm config <NEWID> | grep "^net0:" | sed -E "s/.*virtio=([^,]+).*/\1/")
qm set <NEWID> --net0 "virtio=${MAC},bridge=vmbr1,link_down=1"   # keep the MAC!
qm start <NEWID>
```

- The restored config carries production's `onboot 1` + `protection 1` — clear both.
- Changing `ipconfig0` makes cloud-init re-apply network + hostname (= VM name)
  on boot, even with the new MAC. `/etc/hosts` gets the new hostname too.
- Check the new IP is free first (`ping`, VM list, old known_hosts entries).

## Phase 2 — Quarantined first boot (NIC down, guest agent only)

Wait for the agent (`qm agent <NEWID> ping`, ~70 s), then defuse BEFORE any
network. Copy the scripts in via the agent — **base64-encode them**: pvesh
file-write dies on UTF-8 ("Wide character in subroutine entry"):

```bash
qm guest exec <NEWID> -- mkdir -p /root/template-cleanup
for f in 00-defuse.sh 01-idempiere-truncate.sql 02-strapi-truncate.sql \
         03-neutralize-env.sh 04-set-instance-identity.sh run-cleanup.sh diagnose-survivors.sql README.md; do
  pvesh create /nodes/prox2/qemu/<NEWID>/agent/file-write \
    --file "/root/template-cleanup/$f.b64" --content "$(base64 -w0 /tmp/tc/$f)"
  qm guest exec <NEWID> -- bash -c \
    "base64 -d /root/template-cleanup/$f.b64 > /root/template-cleanup/$f && rm /root/template-cleanup/$f.b64"
done
qm guest exec <NEWID> --timeout 120 -- bash -c "chmod +x /root/template-cleanup/*.sh; /root/template-cleanup/00-defuse.sh"
qm guest exec <NEWID> --timeout 120 -- bash -c "/root/template-cleanup/03-neutralize-env.sh <CLONE>"
```

`00-defuse.sh` wipes the root crontab (DHL crawler! wetzel report!), disables
postfix, clears ALL frontend runtime data (`data/*.db` + `data/<dirs>` incl.
signed contracts, APK releases; `logship_*.db` per-user caches), wipes the
`/root/storage` PDFs (GBs of labels/invoices) and stale deploy copies
(`-temp`, `-rollback`, old `-releases` — they contain the prod `.env`), drops
the prod-IP ES bind + the data.logship.de hosts entry, and removes the
Check_MK registration (`cmk-agent-ctl delete-all`; agent stays installed).
`03-neutralize-env.sh` blanks external creds — compare its key list with
`grep -oE '^[A-Z_0-9]+=' .env` on prod before each generation; new
integrations add new keys. Optionally verify iDempiere cannot mail:
`ad_client.smtphost` should be empty.

Then bring the NIC up (SAME MAC, no link_down):

```bash
qm set <NEWID> --net0 "virtio=${MAC},bridge=vmbr1"
ssh-keygen -R <CLONE>       # on your machine — clone has prod's host keys
ssh root@<CLONE> 'cd /root/idempiere-elastic && docker compose up -d; pm2 restart all'
```

## Phase 3 — Data truncation

Dry run first (iDempiere SQL with ROLLBACK, services keep running, a few
minutes): it prints every `DYNAMIC target … : N rows` the catch-all picked
up, the FK-protected survivors and the zero checks. Review the dynamic list —
anything that is config, not data, goes into `keep_tables` in the SQL.

```bash
ssh root@<CLONE>
/root/template-cleanup/run-cleanup.sh --dry-run 2>&1 | tee /root/template-cleanup/dryrun.log
grep -E 'DYNAMIC|survivors|zero checks' -A0 /root/template-cleanup/dryrun.log
nohup /root/template-cleanup/run-cleanup.sh > /root/template-cleanup/cleanup.log 2>&1 &
echo $! > /root/template-cleanup/cleanup.pid
# wait: until ! ps -p $(cat …/cleanup.pid); do sleep 15; done
# (do NOT `pgrep -f run-cleanup.sh` from a watcher — it matches itself)
```

Runs ~10 min: pg_dump safety copies → stop services → iDempiere truncation
(single transaction; any error = full rollback, safe to fix + re-run with
`--no-dump`) → VACUUM FULL → Strapi truncation + uploads wipe → delete ES
indices → restart everything (sync recreates indices empty) → log hygiene.

Review the report at the end of cleanup.log: `FK-protected survivors`,
`remaining orgs`, `zero checks`. **Expected survivors** (config-anchored):
~17 billing products (referenced from ad_client / c_acctschema_element),
3 partners (Standard, logyouAdmin, LogYou GmbH), staff users, few c_location.
If a NEW table blocks with hundreds of survivors, run
`sudo -u postgres psql -d idempiere < /root/template-cleanup/diagnose-survivors.sql`
— it prints which table blocks each survivor — then extend
`01-idempiere-truncate.sql` (new child-table target, or a new unlink pair for
mutual references) and re-run `run-cleanup.sh --no-dump`.

Hard-won specifics already handled inside `01-idempiere-truncate.sql`:
- `m_product_bom` is a VIEW in iDempiere 10+ (engine skips non-tables)
- ES-sync triggers (`log_*_changes`) must be disabled during the run —
  `log_product_changes()` reads NEW on DELETE and aborts the transaction
- iDempiere FKs are partly DEFERRABLE → `SET CONSTRAINTS ALL IMMEDIATE`
- Reversal pairs (m_inout/c_invoice/c_payment…) and invoice↔payment
  reference each other → link columns are NULLed first
- `ad_org.c_bpartner_id` (custom column) ↔ partner forms an org↔partner
  cycle → unlinked for deleted orgs only
- Role access rows / rest_authtoken / tree favorites / wlistbox / BOM-trl
  are child tables that block roles, sessions, users, BOMs
- `m_inoutlinema` & co. (attribute-set-instance children, unit tracking) have
  NO FK to their line parent — they never block, they silently survive;
  listed explicitly and must be deleted before `m_attributesetinstance`
- Dynamic catch-all (pass 1 tail): populated `cust_*` tables outside
  `keep_tables`, tables with document-FK columns, `i_*`/`t_*`/`*log` tables.
  Config custom tables (printers, label templates, commission tables, …)
  must be in `keep_tables` or they are wiped for client 1000000.
- Generic cycle-break (before the engine): every nullable single-column FK
  between two pass-1 targets that is a self-reference or has an FK pointing
  back is NULLed for client 1000000. 2026-10 it found what no list had:
  `m_rmaline.m_inoutline_id` ↔ `m_inoutline.m_rmaline_id` (return receipt
  lines point at the RMA line and vice versa), `m_inout.m_rma_id` ↔
  `m_rma.inout_id`, `c_order.c_payment_id` ↔ `c_payment.c_invoice_id`.
  Without it 648 lines / 601 shipments / 613 RMAs survived.
- Engine stage 2 first collects every id that some FK row still points at
  into the temp table `ref_ids` (one `SELECT DISTINCT fk` per populated
  referencing table) and bulk-deletes the rest with ONE hashed anti-join,
  then goes row-wise only over the remainder. Before that, a 56k-row table
  took 25 min row-wise, and a DELETE with 20–100 chained NOT EXISTS
  (`c_order`, `c_bpartner` ≈ 100 referencing tables) made the planner pick
  nested loops — > 30 min per table.
- Set-based referential integrity: for every table without multi-column
  incoming FKs the engine disables the table's triggers, deletes in bulk,
  re-enables them (re-disabling the ES-sync ones) and verifies each incoming
  FK with ONE anti-join; a violation raises foreign_key_violation so the
  subtransaction rolls back and the normal fallback runs. Per-row RI triggers
  were the real cost: 294k `c_bpartner_location` × ~20 FKs = 6M trigger
  queries, 25+ min even with indexes. With this the whole engine (4 passes,
  2.3 GB DB) runs in ~35 s; the full `run-cleanup.sh` incl. VACUUM FULL,
  Strapi and ES in a few minutes.
- Pass 5 repeats the pass-2/4 targets: a partner whose contacts were only
  removed by the org purge (pass 3), or an org bound by
  `c_bpartner.accounting_ad_org_id`, becomes deletable only late. Lead
  contacts (ad_user without password and role) are CRM data and go in pass 2.
- Temporary FK indexes (≈3.5k, 17 s) are created before the engine on every
  unindexed FK column that points at a target table and dropped afterwards.
  Without them each deleted row's RI check seq-scans the referencing table
  incl. the dead pages of our own earlier DELETEs — `m_inoutline` ×
  `m_costdetail`/`m_inoutlinema`/itself ran 30+ min per bulk statement.
- Stage 1 uses TRUNCATE instead of DELETE when every row matches the
  condition and no other table has an FK into the table: instant, and the
  fresh relfilenode matters — after a DELETE, FK checks on later deletes
  still seq-scan the dead pages (45k `ad_session` × 1.36M dead
  `ad_changelog` rows = hours; iDempiere has no index on most FK columns).
- `--dry-run` runs with iDempiere still up: its housekeeping jobs queue
  behind the run's locks (harmless on the clone) — the real run stops
  iDempiere first. On a 2.3 GB DB expect ~15 min for the real run.
- `pkill -f run-cleanup` from an ssh command kills the ssh shell itself
  (the pattern matches its own command line) — build the pattern from two
  strings or kill by pid list.

## Phase 4 — Validate

```bash
ssh root@<CLONE> reboot
# after boot, all must hold:
systemctl is-active idempiere postgresql@17-main nginx postgrest docker pm2-root   # active
systemctl is-active postfix        # inactive!
crontab -l | grep -cE '^[^#]'      # 0
postqueue -p                       # empty
cmk-agent-ctl status | grep -c Connection   # 0
ls -A /opt/erp-nuxt-frontend/data  # .gitkeep only; find /root/storage -type f | wc -l → 0
curl -s -o /dev/null -w '%{http_code}' http://127.0.0.1:3001/signin   # 200
psql: SELECT ad_org_id,name FROM adempiere.ad_org WHERE ad_client_id=1000000;  # only 1000000 LogYou
curl -s 'http://127.0.0.1:9200/_cat/indices?h=index,docs.count' | grep -v '^\.' # all 0
psql: SELECT count(*) FROM adempiere.m_attributeset WHERE name LIKE 'LogShip%';  # 4 (config kept)
qm config 105   # on prox2: production unchanged
```

Browser test: `http://<CLONE>:3001` — logins/passwords identical to
production at clone time (hashes cloned). Password login only (no HTTPS).

## Phase 5 — Finalize (ONLY after review sign-off)

```bash
ssh root@<CLONE>
rm -rf /root/template-cleanup/backup/          # pg_dumps contain PROD DATA
rm -f /root/env.production.bak
history -c; rm -f /root/.bash_history
fstrim -av
poweroff
# on prox2:
qm set <NEWID> --memory 20480                  # empty DB needs no 36G
# leave onboot 0; optionally: qm set <NEWID> --protection 1
# retire the previous template and point the provisioning script at the new one:
qm destroy <OLDID> --purge
sed -i 's/^TEMPLATE_ID=.*/TEMPLATE_ID=<NEWID>/' /root/clone-logship-instance.sh
# + copy the current scripts: scp reference/template-cleanup/* root@prox2:/tmp/tc/
```

REST secret rotation on instances (TEMPLATE-README step 4) targets
`adempiere.ad_sysconfig` `REST_TOKEN_SECRET`, not `idempiere.properties`.

The VM is intentionally in NO backup job. It stays a normal stopped VM
(bootable golden image) — boot it to update software, or `qm clone` it for
new instances (see TEMPLATE-README.md for the clone-to-instance checklist).