encoach_backend_new_v2/docs/RESTORE_BACKUP_ON_VPS.md

Restoring the local backup onto the production VPS

The symptom you hit — restored a zip through /web/database/manager and logged in to https://encoach.com/ afterwards but the content looked unchanged — almost always means one of three things:

1. The restore targeted a new database name, so the zip loaded into an orphan DB while the running app keeps serving the original one.
2. The Odoo worker(s) were not restarted after the restore, so the in-memory registry is still the old one.
3. dbfilter in odoo.conf pins the app to a specific DB name that doesn't match the one you restored into.

This runbook walks through a clean restore. Run it on the VPS (SSH in); if Odoo is dockerised, run inside the Odoo container (docker exec -it <odoo_container> bash) or wrap each psql/odoo-bin command with docker compose exec <service> ….

0. Transfer the backup

From your laptop:

scp backups/encoach_v2_20260420_183426.zip user@encoach.com:/tmp/

Zip contents (for reference):

File	Size	Purpose
dump.sql	44 MB	plain pg_dump, no owner/ACL
manifest.json	3 KB	Odoo 19.0, 94 modules, source DB encoach_v2
filestore/	0 B	empty (no attachments yet)

1. Identify the live database name

On the VPS:

# If Odoo is systemd:
sudo grep -E '^(db_name|dbfilter)' /etc/odoo/odoo.conf

# If Odoo is dockerised (compose):
docker compose exec backend sh -c "grep -E '^(db_name|dbfilter)' /etc/odoo/odoo.conf"

Note the value of db_name (e.g. encoach_prod). That is the name the restored DB MUST use. If dbfilter is set too, it forces the same name.

List what already exists in PostgreSQL:

sudo -u postgres psql -c '\l' | grep -i encoach

2. Back up whatever is currently live (safety net)

Before destroying anything:

sudo -u postgres pg_dump --no-owner --no-privileges \
    -d encoach_prod \
    -f /tmp/encoach_prod_safety_$(date +%Y%m%d_%H%M%S).sql

# Also snapshot the filestore (in case anyone uploaded attachments
# since the last clean snapshot)
sudo tar czf /tmp/filestore_prod_safety_$(date +%Y%m%d_%H%M%S).tgz \
    -C /var/lib/odoo/filestore .

3. Stop Odoo

Nothing should be connected to the database during restore.

# systemd:
sudo systemctl stop odoo

# docker-compose:
docker compose stop backend

4. Drop & recreate the target DB, then load the dump

Let's assume the live DB name is encoach_prod. Replace it with whatever step 1 told you.

cd /tmp
unzip -o encoach_v2_20260420_183426.zip -d encoach_restore

sudo -u postgres psql <<'SQL'
-- Kick any lingering connections off the DB
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='encoach_prod' AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS encoach_prod;
CREATE DATABASE encoach_prod OWNER odoo ENCODING 'UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE template0;
SQL

sudo -u postgres psql -d encoach_prod -f /tmp/encoach_restore/dump.sql

Expected output: a long stream of CREATE TABLE … COPY … ALTER TABLE … lines. If you see ERROR: entries, capture them — missing extensions are the usual culprit (see §6).

5. Restore the filestore & restart

Our current backup has an empty filestore, so this step is a no-op for this particular zip, but keep it in the runbook for future backups:

# If your zip has filestore entries:
sudo rm -rf /var/lib/odoo/filestore/encoach_prod
sudo mkdir -p /var/lib/odoo/filestore/encoach_prod
sudo cp -a /tmp/encoach_restore/filestore/. /var/lib/odoo/filestore/encoach_prod/
sudo chown -R odoo:odoo /var/lib/odoo/filestore/encoach_prod

# Start Odoo again
sudo systemctl start odoo            # systemd
docker compose start backend         # docker-compose

Then watch the log to confirm the registry loaded cleanly:

sudo journalctl -u odoo -f           # systemd
docker compose logs -f backend       # docker

Look for Registry loaded and no Traceback near the end.

6. Verify

Open https://encoach.com/ in a private / incognito window (to bypass any service-worker or session cache), log in as admin / admin, and check:

Where	Expect
Sidebar → Exams List	18 custom exams, including "QA Smoke Writing"
Sidebar → Rubrics	"QA Writing Rubric" visible
Sidebar → Exam Structures	"QA IELTS Academic" visible
Sidebar → Assignments	"QA Smoke Assignment v2" visible

If any of the "QA …" items are missing, the restore didn't actually hit this DB — repeat from step 1 with the correct db_name.

If psql -f dump.sql errors out

Common errors and fixes:

• extension "unaccent" is not available — install it on the VPS:

  sudo -u postgres psql -d encoach_prod -c "CREATE EXTENSION IF NOT EXISTS unaccent;"
  

  Then rerun step 4's dump load.

• role "odoo" does not exist — the dump is --no-owner / --no-acl so this shouldn't happen, but if it does, create the role:

  sudo -u postgres psql -c "CREATE USER odoo WITH PASSWORD 'change-me';"
  

• server_version mismatch warning — the dump was made on PG 18.3, safe to restore on PG 14/15/16/18. If you see incompatible dump format, the target PG is too old — upgrade or use pg_restore -F c from a custom-format dump instead.

Why NOT to use /web/database/manager on a production VPS

• It requires list_db = True in odoo.conf, which exposes a "Select Database" dropdown to anonymous visitors — a tenant-leak footgun.
• The UI silently restores into whatever DB name you type, even if it doesn't match what dbfilter is set to — which is exactly how you got into this state.
• For dockerised deployments, the filestore winds up inside the container but the zip's filestore isn't restored in the right volume.

Keep list_db = False in production and always restore via the steps above.