[lucw]

I went through this around a year ago. I wanted to postgres for django apps, and I didn't want to pay the insane prices required by cloud providers for a replicated setup. I wanted a replicated setup on hetzner VMs and I wanted full control over the backup process. I wanted the deployment to be done using ansible, and I wanted my database servers to be stateless. If you vaporize both my heztner postgres VMs simultaneously, I lose one minute of data. (If I just lose the primary I probably lose less than a second of data due to realtime replication).

I'll be honest it's not documented as well as it could, some concepts like the archive process and the replication setup took me a while to understand. I also had trouble understanding what roles the various tools played. Initially I thought I could roll my own backup but then later deployed pgBackrest. I deployed and destroyed VMs countless times (my ansible playbook does everything from VM creation on proxmox / hetzner API to installing postgres, setting up replication).

What is critical is testing your backup and recovery. Start writing some data. Blow up your database infra. See if you can recover. You need a high degree of automation in your deployment in order to gain confidence that you won't lose data.

My deployment looks like this: * two Postgres 16 instances, one primary, one replica (realtime replication) * both on Debian 12 (most stable platform for Postgres according to my research) * ansible playbooks for initial deployment as well as failover * archive file backups to rsync.net storage space (with zfs snapshots) every minute * full backups using pgBackrest every 24hrs, stored to rsync.net, wasabi, and hetzner storage box.

As you can guess, it was kind of a massive investment and forced me to become a sysadmin / DBA for a while (though I went the devops route with full ansible automation and automated testing). I gained quite a bit of knowledge which is great. But I'll probably have to re-design and seriously test at the next postgres major release. Sometimes I wonder whether I should have just accepted the cost of cloud postgres deployments.

[LVB]

I've got a less robust version of this (also as Ansible -> Hetzner) that I've toyed with. I'm often tempted to progress it, but I've realized it is a distraction. I say that about me, and not too negatively. I know that I want to get some apps done, but the sysadmin-y stuff is pretty fun and alluring but it can chew up a lot of time.

Currently I'm viewing the $19 plan from Neon as acceptable (I just look in my Costco cart for comparison) for me now. Plus, I'm getting something for my money beyond not having to build it myself: branching. This has proved way handier than I'd expected as a solo dev and I use it all the time. A DIY postgres wouldn't have that, at least not as cleanly.

If charges go much beyond the $19 and it is still just me faffing about, I'll probably look harder at the DIY PG. OTOH if there is good real world usage and/or $ coming in, then it's easier to view Neon as just a cost of business (within reason).