I was in exactly this situation about a year ago - DS unhappy with his primary school, passed exam for local good private school. We spent an agonising 10 days trying to make our minds up (honestly the most stressful 10 days I can think of since 20 years back when I was made redundant!) Lots of pros and cons, and I did feel he had to be involved in the decision, not have it imposed on him, even at age 9.
In the end what swung it was the issue of "fitting in". He said "I liked the day I spent there, but there was this feeling of 'I could fit in, but I'd have to turn myself into someone I'm not to do it.'"
That's exactly the feeling I had in my first job out of university (really excellent job I worked incredibly hard to get and was lucky to get, with superb career prospects). I just didn't fit. I had to pretend to be someone I wasn't. In the end I went back to academia. So I took DS's feelings about this very serious.
At the same time there was this feeling of "shit, this could enable him 8 or 9 years down the line to get the exam results he'll need to get into a really top university to do whatever he wants... I could be making a huge mistake here."
A year on, I'm more sanguine about it. He has a much better teacher in his state primary school, our top choice secondary (fingers crossed) offers a lot of the same outdoor ed opportunities the private school would have done (together with an ethos of inclusion where they do massive amounts of fundraising so kids from poorer backgrounds get to do stuff like Duke of Edinburgh awards completely funded by the school - like I said, fingers crossed...)
I also recall my experiences as a postgrad tutoring at (insert name of really prestigious university here). The kids from state school who'd got there against the odds did spectacularly well. The kids from private school really separated into the sheep and the goats - there were of course the massively talented ones who'd have done well whatever, but there were the ones who got where they were by being drilled and force fed by their school - and left to self-motivate at university, they sank without trace.
So ultimately what matters (assuming the state school isn't a hopelessly failing school) is fostering an attitude within your child where the drive and desire comes from inside them, not from being drilled and regimented.
Good luck OP, it's a massively, massively hard and stressful decision to make - I know, I've been there.