Tips on hosting your own Postgres instance

You mention this is a side project, and of unknown scale.

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.

At work, I've been running Postgres instances in Kubernetes for years now. The setup makes a lot of sense for me within my work context.

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

You might want to have a look at Litestream then: https://litestream.io/

“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

If you can manage it yourself, I advise using the Bitnami PostGreSQL on top of Kubernetes. Using this, you will have a more solid installation than what is offered by many local cloud providers. Kubernetes can be scary, but it's not too hard to maintain if you use a simple Kubernetes distribution like K3S.

https://bitnami.com/stack/postgresql/helm

https://github.com/bitnami/charts/tree/master/bitnami/postgr...

https://k3s.io

-

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/

https://www.ovhcloud.com/en-ie/public-cloud/databases/

https://www.hetzner.com/webhosting

Not a fool's errand at all. Get a decent VPS and spend 15 minutes tweaking the config using the suggestions here: https://docs.fedoraproject.org/en-US/quick-docs/postgresql/#... Besides figuring out a backup strategy it "just works"

The official manual + distro docs are the best source afaik if you need to run in a VM or VPS.

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/

I think SQLite can be a good idea, actually.

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 not Kubernetes, check out this: https://github.com/vitabaks/postgresql_cluster – it's Postgres + patroni + pgBouncer + WAL-G or pgBackRest + Netdata + more, managed by Ansible.

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

> I've actually considered running everything off of (multliple instances of) SQLite so that I don't have to manage Postgres.

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

depending on the language you have chosen for your side project you might also be able to run postgresql in embedded mode here is the one for golang https://github.com/fergusstrange/embedded-postgres . There is similar solution for java as well.

Using docker on my rented server which has postgres, no issues. Pretty cheap obviusly.

Dockerize it and host it on Kubernetes with volume mounts.