I'm not sure how much pumps pressurize water, if that even factors into the calculations at all, but having the source of your water at a higher z-level than the output causes it to be pressurized. Thus, a simplistic if likely not practical method is to build a reservoir at the highest z-level you can manage. I think there's a limit built in that's somewhere around 10 levels above the highest terrain feature on your map. If you do that, then put the output of your cannon on the lowest practical z-level, that should theoretically cause a lot of pressure.
Thing is, I'm not sure exactly how the game handles this. I'm not sure if there's a limit on how high the pressure can go, or if the game even treats this in a manner like you'd expect. It may well just spew water out on the z-level it comes out on and spread out too quickly to work as a weapon (that is, it may not actually produce much of a stream of water).
The minecart method would probably work better in general. It would definitely be more immediately lethal. I think being hit with a minecart's worth of water does pretty devastating damage now, whereas being hit by sprayed water can't hurt you directly so far as I know.
Pumps would be best. In terms of pressure, for each pump that's fully supplied with water to its intake square every frame, thats one 7/7 cube of water placed every frame. The concept behind magma/water cannons is that they place a LOT of 7/7 cubes every frame, and if done over empty space, will place water/magma out in space faster than it will fall to the earth. The game's concept of pressure is just how high (z-levels) that water will be allowed to path to (if it behaved like actual pressure you wouldn't need 100 pumps for a magma pump-stack). In that sense, pressure is limited only by how high the water source or pump is. On the same z-level though, several pumps behind a 1-wide corridor will fill that corridor in seconds, but of course once things are submerged in 7/7 water, there is no longer any pushing that occurs, so instead of blasting invaders out, they'll most likely just drown while the water magically moves about them without pushing them. There's a detailed discussion (with links to more detailed discussions) in my sig.
In practical terms, you can use moving water to kill things. The simplest is a 1-wide ledge with water held on one side and empty space on the other. You pull the lever, and water knocks them off. You can do wider ledges, but the empty space (provided there is adequate drainage) will make sure the water flows off the edge, taking creatures with it. More often than not though this results in explosions of water that overwhelms the drainage system, resulting in drowning instead of falling. Its just as effective to let the water trickle out in 3/7's and 4/7's, although in-game this is still a sizable wave.
There's one more way to weaponize high velocity water that I recently saw, and it involved minecarts filled with water hitting their max speed and somehow stopping. The result is a dense 'glob' of water flying at max-speed, crushing anything in its path as a projectile before being converted back into liquid water.