Ask HN: Tips on hosting your own Postgres instance
12 comments
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.
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.
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?
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.
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.
Thanks for sharing! This is great.
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.)
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.)
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.
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.
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
“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
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
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.
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.
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/
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).
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).
Wow, that book looks fantastic! Will pick it up later this week!
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...
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
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.
But why, when you can install a regular RPM package?
I didn't hear one buzz word in that sentence @strzibny. We're going to have to let you go.
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.
One month later, "Why we stopped using <buzz word> (and why you should too)"
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.
Even more points if it's a centralized blockchain like the ones a16z are funding.
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.