Bourke St Bakery on (wait for it) Bourke St in Surry Hills has some great pies. The same people also own Central Baking Depot in the CBD, so I imagine the pies are good there as well. Bourke St Bakery also has some incredible sausage rolls.
I had a good pie from Dinky Di pies on Elizabeth St in Surry Hills headed towards Waterloo last weekend. I got the chicken and leek, so haven't had the meat pie, but I was pretty impressed as chicken pies can often be a letdown. This had big chunks of chicken and a good sauce with nice leek flavor.
For novelty if nothing else, a tiger at Harry's Cafe de Wheels in Wooloomooloo (or Chinatown if you can't make it to Wooloomooloo) is definitely worth a try. It's a pie topped with mushy peas, mash potato and gravy. A big feed and the pie is pretty decent as a bonus. If you want to get the same thing from the source of the pies, go to Hannah's Pies in Ultimo right by the Powerhouse Museum (just on the other side of Darling Harbour). Not much for atmosphere, but a good pie.