Upgrading Fhir Database

[Shivam Bhaskar]

we are trying to upgrade fhir RDS postgres database from 11.17 to 16.1 but it gets stuck at * pg_dump: reading large objects and fails.
how can i upgrade the database,
One way I am thinking of is to move all the large objects (i do not know what they are) from the database to s3 bucket and database and refer to those objects in s3 instead of storing them inside the database. But we are not sure how to achieve that.

Our end goal is to upgrade fhir RDS postgres database from 11.17 to 16.1 with the least downtime possible.

[James Agnew]

You haven't mentioned which version of HAPI FHIR you are using (please see Getting Help for tips on how to ask effective questions here) but if you aren't already on the latest version of HAPI FHIR I'd recommend upgrading, and then running a resource reindex job. We no longer use the pg_largeobject table is almost all cases because of the many issues it causes, so a reindex on a current HAPI FHIR release will migrate all data out of that table. That may well help in unsticking whatever is causing that failure.

Cheers,

James

[Shivam Bhaskar]

Thank you for your prompt response James, I was discussing the same within my team.

we are running HAPI Fhir version 6.2.1

we are using AWS RDS aurora postgres database for it which we wish to upgrade from version 11.17 to 16.1. However, the upgrade keeps failing at "pg_dump: reading large objects". we suspect it is due to the pg_largeobject being too huge.

as per your previous response am I correct to assume that instead of trying to move all large objects outside of the database (to s3) and creating a reference link in the db using some fhir utility (which we are not sure how to achieve).

If we just upgrade to 7th major version and run the "resource reindex job" it will help us reduce the large object size significantly so that we can upgrade the database with the least downtime?
Is there any reference links you can share that can help me and my team understand and proceed with this approach?

Regards,
Shivam Bhaskar 

[James Agnew]

Yup, that's correct. The reindex operation is described here (note this is Smile CDR documentation but this part applies to the open source product too) - You'll want to set optimizeStorage to ALL_VERSIONS in order to migrate all data away from the LO table in your database.

Cheers,

James

[Shivam Bhaskar]

we tried HAPI Fhir upgrade to 6.10.1 and 7.2.0 and then ran reindex on the database through fhir/postman with optimizeStorage set to ALL_VERSIONS
but the size of the largeobject and largeobject_metadata remained the same. 
is there anything we are missing?

[James Agnew]

First, check whether the HFJ_RES_VER.RES_TEXT column has any non-null values (these should all be null if the reindex worked).

Second, you may need to run a vacuumlo to actually delete the orphaned LO objects.

Definitely make a backup before doing this, just in case. vacuumlo is a destructive command.

Cheers,

James

--
You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/6a7064c4-17c5-4d73-bf07-6f8e6483e18dn%40googlegroups.com.

[Shivam Bhaskar]

Thank you for your helpful response. we have successfully upgraded to FHIR 7.2.0 which reduced the number of large objects from 150 mil to 2 millions.
We are now going to try the FHIR 7 upgrade in our production environment.

In case we have to rollback will fhir 6 be compatible with the fhir 7 migrated postgres database (reindexed and vacuumlo).