I'm biased - I'm a researcher at a big research institution and finished my PhD 15+ years ago. Seeing how the sausage is made shouldn't cause one to become less "pro-science". It should give people perspective that the scientific process is a process performed by humans, with all their flaws (ego, ambition, competition, etc). Furthermore, science is all about making hypotheses and seeing how they work out - with the understanding that many are wrong for all kinds of reasons (bad idea, bad motivations to push known bad ideas, etc.).
When I hear people disillusioned with how science works, more often than not I see someone who is forced to see that the mental model they had of how it works doesn't line up with how it actually works. It's messy, and has always been messy. Just read some history books, and you'll see "scientific" ideas in the 19th and early 20th century that persisted for a long time simply because some people disliked specific other people with opposing ideas. You'll see horrible egos, non-scientific arguments to reject ideas, people scheming and trying to undermine each other, and so on. Basically, humans being humans to each other. There never was a time when science was performed in the idealized model that we sometimes wish it was done: it always involves people being people, flaws and all.
Plus, I don't know any working scientist who doesn't assume most scientific discoveries are complete bullshit (or, more likely, they aren't bullshit as much as they ultimately contribute no new knowledge or understanding). We all know how the game works and why people put ideas out there (especially with the increased volume of publications people are expected to have now). What keeps people like me going is that I know most ideas are dumb or flat out garbage, but good ones emerge from that churn too and push things along. I like to think that some of my dumb ideas, when I throw them into that churn, will also occasionally push things along.
Don't get me started in the "believe in the science" line. The fact that we've turned that phrase into a political cudgel is not helping the situation.