Hacker News Reader

Comment by hankchinaski | Hacker News Reader

The only thing that holds me back for self hosting is Postgres. Has anyone managed to get a rock solid Postgres setup self managed? Backups + tuning?

View on HN

Put it on a zfs dataset and back up data on the filesystem level (using sanoid/syncoid to manage snapshots, or any of their alternatives). It will be much more efficient compared to all other backup strategies with similar maintenance complexity.

Filesystem backups may not be consistent and may lose transactions that haven't made it to the WAL. You should always try to use database backup tools like pgdump.

Transactions that haven‘t been written to the WAL yet are also lost when the server crashes or you run pgdump. Stuff not in WAL is not safe in any means, its still a transaction in progress.

If a filesystem backup isn't consistent, the app isn't using sync correctly and needs a bug report. No amount of magic can work around an app that wants to corrupt data.

For most apps, the answer is usually "use a database" that correctly saves data.

Entire companies have been built around synchronizing the WAL with ZFS actions like snapshot and clone (i.e. Delphix and probably others). Would be cool to have `zpgdump` (single-purpose, ZFS aware equivalent).

I self-host Postgres at home and am probably screwing it up! I do at least have daily backups, but tuning is something I have given very little thought to. At home, traffic doesn't cause much load.

I'm curious as to what issues you might be alluding to!

Nix (and I recently adopted deploy-rs to ensure I keep SSH access across upgrades for rolling back or other troubleshooting) makes experimenting really just a breeze! Rolling back to a working environment becomes trivial, which frees you up to just try stuff. Plus things are reproducible so you can try something with a different set of machines before going to "prod" if you want.

I was using straight filesystem backups for a while, but I knew they could be inconsistent. Since then, I've setup https://github.com/prodrigestivill/docker-postgres-backup-lo..., which regularly dumps a snapshot to the filesystem, which regular filesystem backups can consume. The README has restore examples, too

I haven't needed to tune selfhosted databases. They do fine for low load on cheap hardware from 10 years ago.

Inconsistent how? Postgres can recover from a crash or loss of power which is more-or-less the same as a filesystem snapshot

Getting my backup infrastructure to behave they way I'd want with filesystem snapshot (e.g. zfs or btrfs snapshot) was not trivial. (I think the hurdle was my particularity about the path prefix that was getting backed up.) write once pg_dumps could still have race conditions, but considerably fewer.

So, if you're using filesystem snapshots as source of backups for database, then I agree, you _should_ be good. the regular pgdumps is a workaround for other cases for me.

Why would tuning be necessary for a regular setup, does it come with such bad defaults? Why not upstream those tunes so it can work out of the box?

I remember spending time on this as a teenager but I haven't touched my MariaDB config in a decade now probably. Ah no, one time a few years ago I turned off fsyncing temporarily to do a huge batch of insertions (helped a lot with qps, especially on the HDD I used at the time), but that's not something to leave permanently enabled so not really tuning it for production use

PostgreSQL defaults (last I looked, it's been a few years) are/were set up for spinning storage and very little memory. They absolutely work for tiny things like what self-hosting usually implies, but for production workloads tuning the db parameters to match your hardware is essential.

Correct, they're designed for maximum compatibility. Postgres doesn't even do basic adjustments out of the box and defaults are designed to work on tiny machines.

Iirc default shared_mem is 128MB and it's usually recommended to set to 50-75% system RAM.

I run a few PostgreSQL instances in containers (Kubernetes via Bitnami Helm chart). I know running stateful databases is generally not best practice but for development/homelab and tinkering works great.

Bitnami PostgreSQL Helm chart - https://github.com/bitnami/charts/tree/main/bitnami/postgres...

https://pgtune.leopard.in.ua/ is a pretty good start. There's a couple other web apps I've seen that do something similar.

Not sure on "easy" backups besides just running pg_dump on a cron but it's not very space efficient (each backup is a full backup, there's no incremental)

I've got an openbsd server, postgres installed from the package manager, and a couple of apps running with that as the database. My backup process just stops all the services, backs up the filesystem, then starts them again. Downtime is acceptable when you don't have many users!

What is your RTO/RPO?

1s/0s

RTO - best effort RPO - wait? You guys have backups and test then??

Reply on news.ycombinator.com