Would like to know the database strategy we use with your DHIS2
Do you also use commands like
sudo -u dhis PGPASSWORD="XXXX" pg_dump -h XXX.XXX.XXX.XXX -U dhis dhis2 or do you suggest more better best practices
I think this is a very interesting question and I hope the big implementations can come up with their solution so this post can be used for further reference.
My main remark would be that I think it really depends on your system and your recovery plan. I think you should define your backup policy plan according to your needs. It is not the same having a huge database in a system that can be down for a week than a system with a small database that can not suffer from any downtime at all. Also if you could afford data loss or not at all.
In my previous work experiences we use to have a system where some downtime was allowed, not much, let’s say we could stop the system for max 12 hours. The database was big but not huge. And we could even afford some data loss (at DB level as there were records on paper). This implies a very easy backup plan. We had the resources to make a full backup every 24 hours. Then we kept the last 7 days in rotation, one for the last 4 weeks, one for each month and one for each year. This meant that we could go back in time if we needed or for archiving purposes. The huge advantage of having a full backup is that the restoration was very easy and straight forward. In case of deletion we could always restore the full DB in a testing machine and get the missing information (although DHIS2 already offers the historical data and the audit).
All this was handled by a set of scripts in the cron table to create and rotate the backups.
Please note that from the security perspective you might want to encrypt your backups, you probably need to protect where they are stored and you should avoid leaking any information (i.e. by using a crontab with the command you posted in an unsecure account).