Hello all,
Would like to know the database strategy we use with your DHIS2
Do you take daily full back up or incremental back up
Do you use any tools/scripts (like Bob’s scripts for installation) for doing this?
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
Hi Jins,
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).
Need more on this.
I’m looking for specific commands, like the
sudo -u dhis PGPASSWORD=“XXXX” pg_dump -h XXX.XXX.XXX.XXX -U dhis dhis2 one.
I’m not really a postgres wiz, so now I’ll run through google to hack through something I think might work and test it out on my AWS.
My current backup strategy is just making an amazon snapshot at the tune of 8gb, but would like something more elegant. I’m also in a situation where I’ll have to run an update on my client’s main server, so now I’m going to learn myself the better strategy of backing up a postgres database.
Adding this silly comment just because I want to make sure to come back and report what I did.
Alright, update here - Found this post which is wonderful and got me what I needed from a backup perspective (Still need to practice the restore function, but I’ll cross that bridge later)
Also see here for my commands on how I’m running my software update.
cd /home/dhis/
sudo -u dhis tomcat-dhis/bin/shutdown.sh
cd ~
sudo -u postgres pg_dump -T analytics_* -T aggregated* -T completeness* -O -x dhis2 | gzip > 2023-05-27_dhis2_database_backup.sql.gz
cd /home/dhis/
sudo wget https://releases.dhis2.org/2.38/dhis2-stable-2.38.3.1.war
sudo mv dhis2-stable-2.38.3.1.war /home/dhis/tomcat-dhis/webapps/ROOT.war
sudo -u dhis /home/dhis/tomcat-dhis/bin/startup.sh
@Hobbes
The restore process is quite easy. below are the scripts I use to restore the database in question,
$ gunzip -c folderpath/filename.gz | psql dbname
$ gunzip -c 2023-05-23.sql.gz | psql dhis2
You can probably get rid of that gzip compression as pg_dump uses compression by default (if I am not mistaken) and you can even specifiy the amount of compression you want making it better that your gzip lines up there. Please check this: PostgreSQL: Documentation: 15: pg_dump