I've found one that checks all the boxes, except they don't offer any managed databases. Is it a fools errand to host my own Postgres database on a VPS? I've actually considered running everything off of (multliple instances of) SQLite so that I don't have to manage Postgres.
Some of the suggestions here are adding complexity that's probably uncalled for unless the project is large, such as kubernetes, docker etc.
You can simply install it on a vps, even the one hosting the app server. It's relatively straighforward unless you're doing unusual things. Just consider how to do your backups so your data is safe, and change the default passwords.
Install postgres Set the postgres configuration - passwords, user accounts, port for external access Set the firewall configuration - such as iptables or whatever is used on your distribution assuming Linux.
That's it, then you should be able to access it from external mgmt UIs and external applications.
It's also possible to setup whitelisted IP access which is a good idea for a database, so that only the IPs of the client applications servers are allowed to connect.
If your data is particularly high value/likely to be targeted, then you should consider further security steps. That can get more complex, but the above basics are fine for simple standard/low security applications. This is pretty much equivalent to what you would get from a cloud database service in standard configuration.
In the context of your one-man-show side projects, for the love of god, don't use Kubernetes. Kubernetes is a tool for a) having separate teams take care of the hardware and OS-level administration on one side, and application payloads on the other side, and for b) realizing high-availablity setups spread across multiple machines and multiple datacenters. None of that is going to apply to your side project in all likelihood.
(You can still go with Kubernetes if you want to learn it and play around with it, but then that would be its own side project.)
Mainly, what do you do of backups? Is it possible to instantly restore any snapshot? What are the database sizes on average etc?
Ever had issues with volumes attaching/detaching? Would be nice if you could blog about your setup/experiences.
“Continuously stream SQLite changes to AWS S3, Azure Blob Storage, Google Cloud Storage, SFTP, or NFS. Quickly recover to the point of failure if your server goes down.”
It can also stream to a self-hosted Minio instance: https://litestream.io/getting-started/#setting-up-minio
https://bitnami.com/stack/postgresql/helm
https://github.com/bitnami/charts/tree/master/bitnami/postgr...
-
Otherwise you can definitely find a local provider offering managed PostGreSQL databases.
Just for the big ones in Europe:
https://www.scaleway.com/en/database/
Took me roughly 2 months to feel confident in entire setup. And because I used bare metal the journey was much harder than it would be if I used any cloud provider. There definitely is some learning curve, but if all you want to do is host postgres with some read replicas, then it should be straightforward.
It's not too hard if your needs are simple (i.e. you don't have any complicated DR or replication requirements).
First and most importantly: Setup a simple and reliable way to backup and restore.
Make sure you log long running queries.
Finally you can do some tuning: https://pgtune.leopard.in.ua/
When I host small side-projects, I have PostgreSQL instance running on the same server and using the ident auth strategy so I avoid managing and keeping passwords and access the DB via SSH.
If you are going to make a separate VM, then don't forget on SSL.
p.s. I wrote Deployment from Scratch (https://deploymentfromscratch.com) which has a full chapter on PostgreSQL and Redis. It also has a demo that sets up a standalone PostgreSQL cluster on a VM (including SELinux, SSL, attached storage).
If you do want to use Kubernetes, then check out:
- StackGres.io
- https://github.com/zalando/postgres-operator
- https://blog.flant.com/comparing-kubernetes-operators-for-po...
If you considered SQLite, then you might want to just run a local Postgres instance on your Application's VPS and connect with a unix socket. The DSN is as simple as postgresql://dbname while still providing all features like replication
Even more points if it's a centralized blockchain like the ones a16z are funding.