HN Explorer

Ask HN: Tips on hosting your own Postgres instance

33 points by brtkdotse ↗ HN
After all brouhaha around GDPR over the past month or so, I've decided to port my sideproject from Azure, Netlify and DigitalOcean to a local provider.

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.

23 comments

[ 3.3 ms ] story [ 67.5 ms ] thread
sidcool
Dockerize it and host it on Kubernetes with volume mounts.
strzibny
But why, when you can install a regular RPM package?
qorrect
I didn't hear one buzz word in that sentence @strzibny. We're going to have to let you go.
noiwillnot
Please check my Medium post about how I use a Postgres WASM instance for my TODO web3 app in Cloudflare Workers KV for pennies a month.
anaxag0ras
One month later, "Why we stopped using <buzz word> (and why you should too)"
MaknMoreGtnLess
No go if the pennies are fiat. Has to be crypto, blockchain, blugh blag blah.

Even more points if it's a centralized blockchain like the ones a16z are funding.

speedgoose
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

ponyous
I use this approach on bare metal and backup the cluster using Velero to S3 compatible backend.

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.

dimitar
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/

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

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

xu3u32
How easy is it to make a postgres instance installed on a VPS open to the world so that various applications installed on other machines can interact with it?
hackerfromthefu
Very easy, assuming the machine has external access which a VPS does

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.

xu3u32
Thanks for sharing! This is great.
strzibny
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).

brtkdotse
Wow, that book looks fantastic! Will pick it up later this week!
majewsky
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.)

wg0
Hi, This is really interesting. I'd like to know more about your experience/setup/toolchain/charts/strategies around it if not confidencial.

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.

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