3 ms·
I do host a medium-sized PostgreSQL database, but i would love not to. I need the control you won't have with a cloud offering (you can't install extensions) bu
by tpetry 5y ago
I do host a medium-sized PostgreSQL database, but i would love not to. I need the control you won't have with a cloud offering (you can't install extensions) but i don't want to do all the other steps, and you always have the fear that backups do not work correctly.
And i am not the only one hosting it by my own, many people are using self-hosting PaaS like dokku, flynn or caprover. And all these solutions have a common problem, they all need to reinvent the database as a service layer. What is currently really missing is a good open-source PostgreSQL as a service appliance, something "simple" like Heroku. There have been attempts like Elephant Shed, but they all try to do too much and therefore fail in adoption as they never reach stability. Or people are forced to use complex solutions like patroni which is doing many things, but if something fails you have no clue what to do.
So what is really missing?
1. A simple old-school PostgreSQL vm image
2. Built on a copy-on-write file system to clone production environments like https://postgres.ai/ https://postgres.ai/ and Heroku for development reasons which will not really need any storage space.
3. A built-in backup solution like pgBackRest storing the files encrypted in some cloud storage (and restore options!)
4. It does not need a complex ui or inspection/monitoring software, there are many solutions you can run on a different machine.
5. Replication, Auto-Failover etc. are hard just make it a single server which you scale vertically, if you need horizontal scaling you have very specific needs, there can't be a one size fits all solution, so don't even try it. And a real bare-metal server with nvme disks has a lot of power it's insane how fast it is compared to cloud hosted databases.
- remus 5y ago> I need the control you won't have with a cloud offering (you can't install extensions) Depends on who you go with. Google's managed postgres thing lets you install supported extensions. https://cloud.google.com/sql/docs/postgres/extensions https://cloud.google.com/sql/docs/postgres/extensions
- mushufasa 5y ago> supported extensions in my experience, vendors support few extensions (for good reason), but lots of time you need a specific use-case in that long tail of unsupported extensions. for example, timescaledb isn't supported anywhere, so the options are managing it yourself or using their own hosted Postgres version.
- Lunrtick 5y agoDigitalOcean does offer Timescaledb in the their managed postgres offering AFAIK, along with quite a few other extensions (PostGIS etc)
- tpetry 5y agoBut does it offer the new TimescaleDB 2.0 with all the new features? They changed their license and made all enterprise features free but only for their Community License (for self-hosting). Cloud providers are only allowed to use the fully open source Apache licensed extensions which is missing some parts.
- gsa 5y agoThe version of Timescaledb offered by DigitalOcean is Apache licensed, which is somewhat limited compared to the community licensed version.
- tpetry 5y agoThey don't let you "install" an extension, the `CREATE EXTENSION` command is just enabling an installed extension for a specific database. There are a ton more extensions than Google Cloud SQL or AWS RDS support. Both of them for example don't have the often used hypopg extensions which allows you to hypothetical create an index to check whether it's used for your queries and see how your execution plan changes. Building indexes for large databases can take a lot of time.
- mattmanser 5y agoIf you genuinely have a fear about backups, why don't you just automate a weekly/daily restore of your backup and have it check the data makes sense (e.g. there's an order in the order table within the last 24 hours, that the orders have increased, etc.).