Comment by scottydelta | Hacker News Reader

Comment by scottydelta | Hacker News Reader

I have been working on https://quantale.io since a year and half. It's a web based alternative to Bloomberg Terminal.

I personally manage the CI/CD infra as well as the data infrastructure for my startup.

Here is my setup:

1. Self-hosted Drone.io instance connected to github(the instance is behind a firewall and can only be accessed via a self-hosted VPN. Github IPs are whitelisted so that auto build/deployment can be initiated via github hooks)

I have drone.io config that helps me manange the versioning, deployment to stage/production, run tests, do rollbacks etc.

for eg: merging to master on github auto builds the master branch on drone and deploys to staging along with pulling the latest db backup from S3 and adding to stage for testing

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.

3. Self-hosted Elasticsearch cluster using Ansible playbooks with daily snapshot backups to S3.

beyond this, I self-host 3 VPNs to protect my entire infrastructure. read my old HN comment about it: https://news.ycombinator.com/item?id=28671753

All this is one time infrastructure setup and it all has been running smoothly for more than a year without any hiccups.

I would be happy to help you setup a similar setup if you want. hit me up at vikash@quantale.io

> 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.

> 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.

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.

Reply on news.ycombinator.com