The Saint John River system is generally a great spot for pumpkinseed. Also a lot of the lakes that are close to the river are also good for them. Indian Lake, Harts Lake, etc as is above the dam at Mactaquac.
From what I have seen (and read) the Oromocto River system is one of the best places for red breast sunfish. To be fair, the only place on it that I have caught redbreast is in Oromocto Lake (the big one). There are also some isolated spots on the Saint John River that I have caught them. That being said, they are still not terribly common.
As ipop said, if you are looking for a specific area that might help people give you more useful help.
Anywhere hooked to sj river has lots of pumpkinseeds, French and Indian lake in particular have a lot and they tend to be quite big...ive caught some big red breasts bass fishing on the sj river , they seem to be more rock and open water oriented than pumkinseeds. I find pukmins like the weeds teh most where red breasts like rocks, pilings, and other structure.. and also will take bigger baits but that is just my personal experience. spots ive caught them on teh sj are off Maugerville warf, the boat launch in Lincoln , and the stretch between Caseys restaurant in Sheffield down to McGowans Corner although they are pretty hit or miss there, most incidental fishing for bass..the best spot ive found for red breasts to target them tho is at the boat launch on the Oromocto river at French lake in Geary, as well as French lake itself...my gf and I go there all the time and have caught some monster ones there, and the only spot they far out number the pumkins, really fun fishing them with a light weight fly rod or even with an ultralight rod and worms, ive even caught some really big ones fishing bass and pickerel in the river and lake, my biggest last summer caught on a spinnerbait of all things, thought it was a small bass until i got it in the boat