But then you have to check that these mechanisms work regularly and manually

One thing I learned working in the industry, you have to check them when you're using AWS too.

Really? You're saying RDS backups can't be trusted?

Trusted in what sense, that they'll always work perfectly 100% of the time? No, therefore one must still check them from time to time, and it's really no different when self hosting, again, if you do it correctly.

What are some common ways that RDS backups fail to be restored?

Why are you asking me this? Are you trying to test whether I've actually used RDS before? I'm sure a quick search will find you the answer to your question.

No backup strategy can be blindly trusted. You must verify it, and also test that restores actually work.