How to automatically drop and restore PostgreSQL database

Question

I am using dhis2 with PostgreSQL as database but I want to verify my backups from the cloud by restoring and checking whether the data are ok but I need it to be automatic.

• J-16 SDiZ: which distribution ? are you using chef ? puppet? docker?

Answer 1

This is going to depend on how you are doing your backups and if you are doing restores on the same database cluster that you are using for your live data.

The simplest case ( for small databases ) is that you are dumping your databases with pg_dump and storing them in the cloud as SQL files, possibly compressed.

Once you have backups you can load them into a database cluster using psql; you have various options in how you do this whether or not you specify that pg_dump drops entities before creating them and whether to include database creation statements.

If you are wanting to ensure the integrity of your backups, without overwriting a database you are using ( doing the restore on the same cluster you dumped from ) you want to dump the database without the create database statements and you want to have the restore script create a database with a different name.

There are a lot of variables in play in terms of what you can include in the dump and a fair bit of subtlety about ownership and permissions that are going to be dependent on what sort of restore you are doing and what roles are going to need access.