I consider myself very fortunate as I can count the number of bad managers I’ve had in my long career on one finger. This isn’t to say my other managers were stellar but they had a good mix of people skills, technical knowledge, and business acumen to build teams that the job done. That said, I do see many managers, especially in technical fields, exemplify the Peter Principle so I don’t think you’re experience is uncommon.
Earlier in my career, my manager decided to leave the firm to pursue a different vocation (farming). He strongly encouraged me to apply for the vacated database manager position since I was the lead DBA. Even though the pay bump was significant, I declined because I enjoyed my hands-on technical role and would rather be an excellent DBA than a mediocre manager.