Did you have any instance where you needed to restore the DB / Have you done restoration tests to ensure this is a feasible solution? Also, since you are using local postgres DB, do you not run into issues like transaction id wraparound ?
Also, how many days of backup you keep on S3? Considering you create 24 backups a day.
Whenever a build is deployed to stage server, it automatically pulls the latest version backup in last 1 hour and restores it on the stage for testing with the latest data. I have config to do the same restore to the prod if needed.
I have been using the same setup for over 2 years to support an internal web app of a company.
> Also, since you are using local postgres DB, do you not run into issues like transaction id wraparound ?
This problem only arises when there is heavy load on the database, specifically high WRITE/UPDATE/DELETE operations. Quantale uses postgres to store only user info and all the other data is stored in Elasticsearch(twitter, reddit, news, SEC, Financial data on over 15000+ stocks). This is intentional because with the scalable server resources, my postgres setup can handle a large number of users before I see that problem. I can always scale up later when needed rather than over-engineering it right now.
>Also, how many days of backup you keep on S3? Considering you create 24 backups a day.
I have 7 days backups on S3 with versioning switched on for 60 days. So at anytime I can access the version backed up in last 60 days.
Safe to say you have a verified way of backup and restore (with worst case data loss of an 1 hour).
I am not exactly an expert on what stage does Transaction ID Wraparound comes in but still know that as one of the key criticisms of PostgreSQL so wanted to check.
Also, 7 days backup with versioning switched is a smart tactic! Thanks once again for this.