9 ms·

Ask HN: Tips on hosting your own Postgres instance

33 points by brtkdotse 5y ago

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.

samokhvalov 5y ago
If not Kubernetes, check out this: https://github.com/vitabaks/postgresql_cluster 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://github.com/zalando/postgres-operator - https://blog.flant.com/comparing-kubernetes-operators-for-postgresql/ https://blog.flant.com/comparing-kubernetes-operators-for-po...
sidcool 5y ago
Dockerize it and host it on Kubernetes with volume mounts.
- strzibny 5y ago
  But why, when you can install a regular RPM package?
  - qorrect 5y ago
    I didn't hear one buzz word in that sentence @strzibny. We're going to have to let you go.
    - noiwillnot 5y ago
      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 5y ago
        One month later, "Why we stopped using <buzz word> (and why you should too)"
      - MaknMoreGtnLess 5y ago
        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 5y ago
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://bitnami.com/stack/postgresql/helm https://github.com/bitnami/charts/tree/master/bitnami/postgresql/ https://github.com/bitnami/charts/tree/master/bitnami/postgr... https://k3s.io 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.scaleway.com/en/database/ https://www.ovhcloud.com/en-ie/public-cloud/databases/ https://www.ovhcloud.com/en-ie/public-cloud/databases/ https://www.hetzner.com/webhosting https://www.hetzner.com/webhosting
- ponyous 5y ago
  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 5y ago
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/ https://pgtune.leopard.in.ua/
blablabla123 5y ago
> 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 5y ago
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 5y ago
  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 5y ago
    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 5y ago
      Thanks for sharing! This is great.
strzibny 5y ago
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 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 5y ago
  Wow, that book looks fantastic! Will pick it up later this week!
majewsky 5y ago
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 5y ago
  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.
cpach 5y ago
You might want to have a look at Litestream then: https://litestream.io/ 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 https://litestream.io/getting-started/#setting-up-minio
tepitoperrito 5y ago
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/#performance https://docs.fedoraproject.org/en-US/quick-docs/postgresql/#... Besides figuring out a backup strategy it "just works"
visox 5y ago
Using docker on my rented server which has postgres, no issues. Pretty cheap obviusly.
newusertoday 5y ago
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 https://github.com/fergusstrange/embedded-postgres . There is similar solution for java as well.

33 points by brtkdotse 5y ago

samokhvalov 5y ago

If not Kubernetes, check out this: https://github.com/vitabaks/postgresql_cluster 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://github.com/zalando/postgres-operator - https://blog.flant.com/comparing-kubernetes-operators-for-postgresql/ https://blog.flant.com/comparing-kubernetes-operators-for-po...

sidcool 5y ago

Dockerize it and host it on Kubernetes with volume mounts.

strzibny 5y ago
But why, when you can install a regular RPM package?
- qorrect 5y ago
  I didn't hear one buzz word in that sentence @strzibny. We're going to have to let you go.
  - noiwillnot 5y ago
    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 5y ago
      One month later, "Why we stopped using <buzz word> (and why you should too)"
    - MaknMoreGtnLess 5y ago
      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 5y ago

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://bitnami.com/stack/postgresql/helm https://github.com/bitnami/charts/tree/master/bitnami/postgresql/ https://github.com/bitnami/charts/tree/master/bitnami/postgr... https://k3s.io 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.scaleway.com/en/database/ https://www.ovhcloud.com/en-ie/public-cloud/databases/ https://www.ovhcloud.com/en-ie/public-cloud/databases/ https://www.hetzner.com/webhosting https://www.hetzner.com/webhosting

ponyous 5y ago
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 5y ago

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/ https://pgtune.leopard.in.ua/

blablabla123 5y ago

> 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 5y ago

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 5y ago
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 5y ago
  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 5y ago
    Thanks for sharing! This is great.

strzibny 5y ago

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 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 5y ago
Wow, that book looks fantastic! Will pick it up later this week!

majewsky 5y ago

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 5y ago
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.

cpach 5y ago

You might want to have a look at Litestream then: https://litestream.io/ 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 https://litestream.io/getting-started/#setting-up-minio

tepitoperrito 5y ago

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/#performance https://docs.fedoraproject.org/en-US/quick-docs/postgresql/#... Besides figuring out a backup strategy it "just works"

visox 5y ago

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

newusertoday 5y ago

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 https://github.com/fergusstrange/embedded-postgres . There is similar solution for java as well.