Pressure gauges commonly use a Bourdon tube. Bourdon tubes are a coiled metal tube and as the pressure increases, it forces the coil straight which then moves the needle. (think blowing a party horn, which causes it to unroll) The type of fluid (water/air) makes no difference to the tube, only the pressure exerted against the tube does. And since most of the tubes are made from bronze, copper or stainless water will not corrode it any faster than moist air from the compressor.
In all said, the air compressor gauge will most likely work without any issues.
But if you really want to get a water gauge, go to a pool supply store. They have pressure gauges for filters that are designed for water and use a 1/4 npt. They also have a nifty little ring on the outside where you can mark your normal pressure so it is easy to see if your pressure is low or high.
The hole is to provide atmospheric pressure inside as a reference for the gauge. That is, the pressure indicated is relative to ambient pressure. Or, in other words, the gauge indicates how much more pressure there is inside the pipe than outside the gauge.
If the gauge is leaking through the air vent, it is time to replace the gauge. The internal mechanism has failed, probably by rusting through or otherwise breached the pressure-sensing diaphragm.
Freezing conditions could well stress the gauge possibly due to a little ice build up.
Yes, this is something most anyone can do. Turn off the pump and relieve the pressure in the line (open a faucet). Using a wrench, vicegrip, or similar tool, unscrew the gauge assembly (gauge plus its short input pipe). Using the tool, grasp the gauge's pipe which comes out below it. There should be a four- or six-sided grip (turn it counter-clockwise as seen from above.
If it has been installed for a long time (years), it will be reluctant to turn at first. A longer handled tool will provide more torque, but take care to provide counter-torque to the pipe it is screwed into to prevent damaging other parts of the plumbing. (Provide counter-torque with another wrench or tool attached to the plumbing near where the gauge is screwed into. If the wrenches are about 90° relative to each other, it is convenient and efficient to twist them opposite to each other so that the water pipe experiences greatly lessened torque.)
Once you have the old gauge assembly out, take it to the parts store for a suitable replacement. Consider using a fine wire brush to clean the seat (the threaded hole the gauge is screwed into); Teflon tape makes it easier to insert the new gauge.
I wouldn't wait long; do it in the next few days. It will leak more and more the longer you wait.
Best Answer
Imagine, if you will, that your whole plumbing system was threaded pipe. Which was common in many places for a certain time period...
With pipe fittings, you don't get a choice of orientation on threads. You just work with them as they come, and there is some practical leeway between "so loose it leaks" and "too tight" which is how you get things to align - proper use of decent pipe dope helps with this.
So, solder it in any which way. Tighten until the gauge faces the way you want, without leaks.
If you are super-fussy and like overly complex and expensive solutions, you can insert a union between the gauge and the rest of the system.
There are other options, such as using a compression fitting where the threads and the seal are separate functions, and the fitting does not turn as the nut is tightened. You could think of those as "fittings that act like a union."