3 ms·

> 2. Self-hosted production and staging server that hosts my web/django app. It has a local postgres db and is encrypted and backed up on S3 every hour. Did yo

> 2. Self-hosted production and staging server that hosts my web/django app. It has a local postgres db and is encrypted and backed up on S3 every hour. 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.

scottydelta 5y ago
> Did you have any instance where you needed to restore the DB / Have you done restoration tests to ensure this is a feasible solution? 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.
- iKevinShah 5y ago
  scottydelta, Thank you for taking the time to give me more context / insight. 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.

scottydelta 5y ago

> Did you have any instance where you needed to restore the DB / Have you done restoration tests to ensure this is a feasible solution? 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.

iKevinShah 5y ago
scottydelta, Thank you for taking the time to give me more context / insight. 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.