# Re-clone runbook — production → clean template VM

Complete, tested procedure (built 2026-07-26 for VM 113 `logship-template-2`,
192.168.12.66). Repeat it whenever the template should be refreshed from
current production. 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 web-push/chat DBs, drops the prod-IP ES bind and the
data.logship.de hosts entry. `03-neutralize-env.sh` blanks external creds.
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

```bash
ssh root@<CLONE>
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

## 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
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
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
```

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).