Automated testing of recovering PostgreSQL database backups

We need a procedure to recover a PostgreSQL backup. This could be something along the lines of the following:

Once a week a host is started automatically, or otherwise woken up somehow. Once it starts it will try to grab the most recent database backup and use this to start a local PostgreSQL database. Once this process has completed it will connect to the DB and check a few tables to see if there's data (e.g. users, projects, and issues). If these tables don't have any data, or the restoring process failed, errors should be sent to Slack.

This restore procedure should use the same underlying tools as a production engineer might use to manually restore a backup. This means we can automatically (and periodically) test these tools, removing the need for engineers to do this manually.