It's actually probably a more difficult problem at scale.

When you have a single smallish schema, you export, restore, and write automated tests that'll probably prove that backups in 10 minutes ( runtime, development time few days / weeks ). Either the transaction runs or errors, and either the test passes or not.

The problem when small is obviously knowledge, skills, and procedures.

Things like:

- What if the monitoring that alerts me that the backups are down, is also actually down. - What do you mean it's no longer "safe" to kubectl delete pvc --all? - What do you mean there's nobody around with the skills to unfuck this? - What do you mean I actually have to respond to these alerts in a timely manner?

The reality is, when the database is small, it typically doesn't cost a whole lot, so there's a lack of incentive to really tool and skill for this when you can get a reasonable managed service.

I typically have those skills, but still use a managed service for my own startup because it's not worth my time.

Once the bill is a larger than TCO of self-hosting you have another discussion.

> Doing backups and validating backups is very error-prone and time consuming

Right, but regardless of using a managed database service or self-hosted database, this is something you probably are doing anyways, at least for a production service with real users. Sure, the managed service probably helps with you with the details of how the backup is made, where it's stored, and how the restore process happens, but you still need to validate your backups and the rest, so replicate that process/experience with your self-hosted setup and you're nearly there.

> occasionally digging into kernel bugs

Haha, been there! We recently had outages on kube-proxy due to a missing `—set-xmark` option in iptables-restore on Ubuntu 24.04.

On any stateful server we always try to be several major versions behind due to issues like above - that really avoids most kernel bugs and related issues.

> occasionally digging into kernel bugs

No, it doesn't. I've been self-hosting a multi-node, highly available, and fault-tolerant PostgreSQL setup for years, and I've never had to go to that level. After reading your whole post, I'm not sure where you're getting your information from.

We use barman too, but we do hourly and daily restores into different database instances.

So for example our prod db is tootie_prod We setup another instance the restores from barman every hour and renames the db to tootie_hourly.

We do the same thing daily.

This means we have backup copies of prod that are great for customer service and dev troubleshooting problems. You can make all the changes you want to _daily or _hourly and it will all get erased and updated in a bit.

Since _hourly and _daily are used regularly, this ensures that our backups are working too, since they are now a part of our daily usage to ensure they never break for long.

Not OP, but:

Barman on the host with a cronjob for physical backups and as archive/restore command for wal archiving and point in time recovery.

Another cronjob for logical backups.

They all ship to some external location (S3/SFTP) for storage.

I like the above since it adds minimal complexity, uses mainly native postgres commands and gives pretty good reliability (in our setup, we’d lose the last few minutes of data in the absolute worst case).