Staging and production database

[pantierra]

Currently the staging and production databases differ significantly. In order to be able to properly test upgrades or debug on staging, the databases should be kept in sync regularly.

Two approaches I could think of:

• Auto sync: maybe daily, or weekly.
• Manual sync: one button/github action or something. Or at least documenting on how to clone the db trying to make it as easy as possible.

@thenav56, @szabozoltan69, any suggestions?

[pantierra]

I would probably sync only montandon-eoapi and via a shared storage account.

production DB → [export job] → shared storage → [import job] → staging DB

• A scheduled job in prod dumps its DB and uploads it to a storage container.
• A scheduled job in staging picks up the latest dump and restores it.
• The scheduling/jobs would be added as ArgoCD-managed CronJobs
• Terraform would add the storage account + access permissions.

[thenav56]

This might get tricky for eoapi.
The database size is massive. It uses many jsonb objects. A normal pg_dump pg_restore would likely have problems. It would take a long time to export and import.

The better way would be to use physical backups for the export/import job. But not sure if we can do this with azure managed postgres.

On the other side, montandon-etl staging will also have problem. Data is pushed from there to the staging. cc: @ranjan-stha