I'm interested in achieving a similar painting style to the one in the attached image. I assume this involves various types of brushes. Is this effect easy to achieve in photoshop? Or is Painter required? If I can use PS what sort of brushes should I be looking for? Or how should I be making them?
It is less about brushes and more about just painting. You could do almost all of that with just a basic round brush. But there is a brush sharing thread in the PS forums (I think) if you want to find some things to play around with.
Brushes are fun, but they can't replace the ability to paint and understanding about light, atmosphere, perspective, or color
The answer to your question is something you largely have to discover yourself with your own experience and experimentation.
I've been using photoshop as my primary medium for about 11 years now and only in the last 2-3 did I start to really get that "painted" look. If you want a quick fix, buy Artrage (like 25 bucks) or Painter (more, obviously) and use them in conjunction with Photoshop...that's the best advice I can give.
Getting that 'painterly' texture is mostly about not making everything perfectly smooth/blended, from what I've gathered in tutorials and experimentation on my own. Most people tend to want to render everything totally smooth (especially areas of skins and fabrics), but seeing those brush strokes is what gives you that texture you're looking for. You can have things look smooth, just try not to make it look like a seamless blend. It's not about what brush you're using, really, it's about how you're using it.
Most of the really good guys do use some texture brushes for quick areas of detail, but they generally use either the basic PS brushes, or they've got slightly modified versions of the PS brushes that they use as their main brushes.
What you're looking at is not an "effect" so much, at least I'd be particularly weary about using that term with digital mediums. What you're looking at is simply a result, not some sort of special effect or simple trade secret. It's the result of work, marks on a surface on top of previous marks. It's just the result of the workflow, of time spent. That look isn't an afterthought, it's in the making from the very beginning. Some of that texture you're seeing and wondering how it got there? Some of it's starting brushstrokes done early on, then some others that wound up there an hour later, etc.
Brushes can perhaps help, but as has been said, you could achieve similar results with a basic round brush. It's just work on top of work, building up, and what you get is that end result..."effect". It doesn't happen in minutes, but that's part of the beauty of it.
Different artists have different "painterly" styles of looks to their work because different artists have different workflows and methods. Keep working hard, and discover your own.
There's nothing in that picture that you couldn't get with the hard and soft round brushes in Photoshop. Looking at it, it looks like the artist worked primarily with a soft brush, and then layed some hard edges down with a hard brush at full opacity, giving it that "painterly" quality. You wouldn't need any special software or custom brushes to duplicate the effect.