Restoring form data

Developers
1

When restoring form data where no file attachments are submitted (purely text/numeric/boolean types), which databases need to be restored? Should I be doing all 3 (couchdb2, blob and postgresql) or is PostgreSQL sufficient?

Thanks!

2

Hi Ed

PostgreSQL and BlobDB need to be restored for form data since Postgres only stores the form metadata. The full XML is stored in the BlobDB.

You shouldn't need to touch CouchDB.

1 Like
3

Thanks Simon, apologies, my semantics were probably unclear. What I meant was submitted data, not the forms themselves.

4

OK, this is a bit confusing, but I tried restoring all 3 (blobdb, couchdb and postgresql) and when I go to forms, I see forms with a newer date than the backup under both archived and unarchived forms:

image
image1413×601 39.4 KB

I restored backups from 31 October 2022...
Is this caching or is there some other explanation for this?

5

So quite bizarrely I have restored the postgres DB by:

  1. renaming /opt/data/postgresql/9.6/main -> data/postgresql/9.6/main.old
  2. restoring the backup to /opt/data/postgresql/9.6/main

If I check the postgres process, it appears to be using the correct directory:
/usr/lib/postgresql/9.6/bin/postgres -D /opt/data/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf

...yet somehow it's not because the files in /opt/data/postgresql/9.6/main.old are current while the restored files in /opt/data/postgresql/9.6/main are dated 31 Oct (the date of the backup) - i.e. they appear untouched after restarting Postgres after the install...

I'm now going to blitz the /opt/data/postgresql/9.6/main.old directory and see what happens. *Not this is an out of date instance that is getting an overhaul in a month or so.

6

OK, so it seems the system is now indeed using /opt/data/postgresql/9.6/main for postgresql but I'm still seeing data submitted after 31 October when I'm only expecting data submitted up to 31 October... any ideas why?

7

Just to add to this, I restored all 3 databases from 30 October and when I go in via the UI, I see records from November and the records with issues appear to still have issues. Either the data is sitting elsewhere or perhaps this is data from handsets that are syncing after the restore?

8

Ah, have you reset Elasticsearch? Most of the reports are pulling data from ES so you'll need to delete those indexes and re-create them after the restore:

Warning: this will delete your ES indexes and re-create them from the primary data
To just do forms and cases:

python manage.py ptop_reindexer_v2 sql-case --cleanup
python manage.py ptop_reindexer_v2 sql-form --cleanup

Or if you want to do everything (including users, applications etc):

python manage.py ptop_es_manage --code_red
python manage.py ptop_preindex --reset
1 Like
9

I had a suspicion it was ES that was the issue. Thanks a ton for that @Simon_Kelly, I'm sure this will resolve the report output.
I'll revert if not.

Thanks!

10

Just to report back that this worked like a charm. Took 8 hours or so to recreate the indexes but it solved the issue. Good to know for future.
Thanks @Simon_Kelly !

1 Like