Skip to content

Replica Phase-0 Spec

For: Nisarg From: Dan Scope: Phase 0 of the Ops Nervous System initiative. One-time setup. No ongoing work after this. Date: 2026-04-18

The ask, one paragraph

Set up a PostgreSQL streaming read replica of the production Odoo database, hosted on EXP. Dan builds scoreboards, nudges, and the live adapter for the Personal Assistant against the replica. Nisarg owns the plumbing (this setup + any future replication monitoring). Dan owns the app layer — queries, apps, UX, iteration. Once the replica is running, Nisarg is out of the critical path for everything built on top.

Why a replica (vs. XML-RPC sync or direct read of prod)

  • Zero load on Odoo's Python process. Replica is a separate Postgres instance; queries never touch Odoo's app tier.
  • Zero risk to prod. Replica is physically read-only at the engine level. Nothing written by downstream code can touch prod data.
  • Real-time data. Sub-second replication lag, vs. 30-minute XML-RPC sync.
  • Unblocks Dan. After setup, every new report, nudge, or PWA is Dan-only work.

Deliverables

  1. Streaming replica running on EXP — primary = Odoo prod Postgres; replica = new Postgres cluster on EXP; secured transport between them.
  2. Two read-only roles on the replica:
    • dan_assistant_reader — SELECT on all tables. For the Personal Assistant.
    • scoreboard_reader — SELECT only on operational tables listed below. For sr-pulse.
  3. Connection verification — psql from EXP as both roles succeeds with a SELECT.
  4. Read-only enforcement verified — INSERT / UPDATE as either role is rejected by Postgres.
  5. Brief README at /opt/odoo-replica/README.md — how to check replication status, how to restart if it falls behind, how to rotate passwords on the two roles.

Topology

  [Prod server, wherever prod Odoo lives]
          │
          │  WAL streaming over secured transport
          │  (SSH tunnel preferred — simplest; SSL also fine)
          ▼
  [EXP: replica Postgres on dedicated port, e.g. 5433]
          │
          ├─ dan_assistant_reader (all tables)
          │     ↳ /opt/personal-assistant queries this
          │
          └─ scoreboard_reader (operational tables only)
                ↳ /opt/sr-pulse queries this

Table grants for scoreboard_reader

Scope to these tables only. These are the operational surfaces the employee-facing app needs; everything else (financials, CRM deep fields, admin tables) stays out of scope for this role.

res_partner
customer_pickups
box_details
note_note
sr_kpi_daily
sr_workstation_session
truck_schedule
hr_employee
hr_attendance
fleet_vehicle
contact_result
customer_region
maintenance_request
sr_ops_alert

Odoo replaces model-name dots with underscores (so sr.kpi.daily → sr_kpi_daily). If a table name doesn't exist in prod under this spelling, flag it so Dan can update the spec.

dan_assistant_reader gets SELECT on every table in the replica.

Steps (reference — use what works in your environment)

1. On prod Odoo's Postgres

  • Verify wal_level = replica (or higher), max_wal_senders >= 2, max_replication_slots >= 1.
  • Create a replication user: 
    CREATE ROLE odoo_replicator WITH REPLICATION LOGIN PASSWORD '<strong>';
  • Add a pg_hba.conf entry allowing odoo_replicator to connect for replication from EXP's IP.
  • Create a replication slot: 
    SELECT pg_create_physical_replication_slot('exp_replica');
  • Reload config.

2. Secure transport (SSH tunnel from EXP side)

  • Set up a persistent SSH tunnel from EXP to prod that forwards prod's Postgres port locally on EXP.
  • autossh with a systemd service is the standard pattern.

3. On EXP

  • Install a second Postgres cluster (same major version as prod).
  • Stop it, init replica with pg_basebackup through the SSH tunnel using odoo_replicator credentials and the exp_replica slot.
  • Configure as streaming standby (standby.signal file + primary_conninfo pointing at the local tunnel endpoint).
  • Start on a dedicated port (5433 if free; otherwise next free).

4. Create the two read-only roles

-- On the primary (replicates to replica automatically via WAL)
CREATE ROLE dan_assistant_reader WITH LOGIN PASSWORD '<strong>';
GRANT CONNECT ON DATABASE <odoo_prod_db> TO dan_assistant_reader;
GRANT USAGE ON SCHEMA public TO dan_assistant_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dan_assistant_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO dan_assistant_reader;
ALTER ROLE dan_assistant_reader SET statement_timeout = '30s';

CREATE ROLE scoreboard_reader WITH LOGIN PASSWORD '<strong>';
GRANT CONNECT ON DATABASE <odoo_prod_db> TO scoreboard_reader;
GRANT USAGE ON SCHEMA public TO scoreboard_reader;
GRANT SELECT ON res_partner, customer_pickups, box_details, note_note,
                sr_kpi_daily, sr_workstation_session, truck_schedule,
                hr_employee, hr_attendance, fleet_vehicle, contact_result,
                customer_region, maintenance_request, sr_ops_alert
                TO scoreboard_reader;
ALTER ROLE scoreboard_reader SET statement_timeout = '10s';

5. Lock down the replica's pg_hba.conf

Restrict both roles to localhost only — not reachable from anywhere but EXP processes.

6. Smoke test

  • Write a sentinel row on prod (e.g., insert a contact_result record with a known name).
  • Wait <5 seconds.
  • Query the replica as scoreboard_reader — confirm the row is there.
  • Attempt INSERT on any table as scoreboard_reader — must fail with permission error.
  • Delete the sentinel from prod, confirm it disappears from the replica.

Acceptance criteria

  • psql -h localhost -p <replica-port> -U dan_assistant_reader -d <odoo_prod_db> connects from EXP and SELECT from any table succeeds.
  • psql -h localhost -p <replica-port> -U scoreboard_reader -d <odoo_prod_db> connects from EXP and SELECT * FROM customer_pickups LIMIT 1 succeeds.
  • SELECT * FROM account_move LIMIT 1 as scoreboard_reader fails (table not in grant list).
  • INSERT or UPDATE as either role fails with permission denied or cannot execute ... in a read-only transaction.
  • A write on prod appears in the replica within 5 seconds (measured via the sentinel smoke test above).
  • Short README at /opt/odoo-replica/README.md covering replication status checks, restart, password rotation.

Scope boundary — NOT in this ask

  • No Odoo changes. No new modules, no field additions, no view changes, no workflow modifications.
  • No writes back to prod. If writes are ever needed from downstream apps, that's a separate conversation (likely via XML-RPC).
  • No ongoing maintenance of downstream apps. When the Personal Assistant or sr-pulse breaks, that's Dan — not Nisarg.
  • No webhook/automation triggers. Those may come later as a Phase 3 add-on; skip for now.

Questions needed before starting

  1. Prod Postgres host + port — where exactly is it?
  2. Prod Postgres version — EXP is on 16.13. Needs to match at major version for pg_basebackup. If it's 15 or 17, we'd use logical replication instead.
  3. Prod Odoo database name — the one actually serving users.
  4. Current DB size — ballpark, for disk planning. EXP has 76 GB free.
  5. Preferred secure transport — SSH tunnel, SSL/TLS, or something else?
  6. Any table in the scoreboard_reader grant list that doesn't exist in prod?
  7. Any disk space or CPU concerns on EXP for adding a second Postgres cluster?

Push back on anything above if a cleaner approach exists in your environment. The deliverables and acceptance criteria are what matter; the exact commands/tools are your call.

This unlocks a lot. Once it's done, you're done with this initiative.