Managing database backups myself is something that gives me nightmares. I would refuse to use bare-metal dbs unless I have a dedicated team just to manage the database (or data that is okay to lose, like caching layers).
Managing database backups is fairly straightforward. Postgres + a base backup + long term wal archiving in a blob store is very easy to set up and monitor. It could be easier, and if you don't want to manage that using RDS is certainly a valid choice, but it's a tradeoff - I often have customers that help addressing performance issues with RDS they simply wouldn't have if they sized a bare metal setup with enough RAM and NVMe and configured it even halfway decently instead, and the end result is often that they end up paying more for devops help to figure out performance bottlenecks than they'd spend putting the same devops consultant on retainer ensuring they have a solid backup setup.
I dunno, it does sound like significant work and way outside my (and most devs) area of expertise. I can definitely supervise a managed RDBMS (like RDS) by myself without help on the side even though I am no dba.
A mismanaged VPS is downtime and churn, a mismanaged DB will insta-kill your business if you have unrecoverable data loss. I would definitely use a managed solution until I can get a dedicated person to babysit the DB, but I would consider managing a VPS myself.
There's no need for a dedicated person. A single operator can easily manage dozens of DB instances unless your needs are extremely complex. Managing these kinds of things are serviced trivially available on retainer.
