There are many 12V pumps. So you have a lot of pumps to choose from.
There are 5V pumps ment for battery operated devices. But they might not be very reliable.
A relay is more expensive than a transistor or a logic level mosfet.
If you use a transistor or logic level mosfet, a diode is needed.
Or you could use some solonoid or motor driver ic.
How high the water can be pumped up, depends on the pump.
Check Ebay for "water pump 12v dispenser", or "water pump 12v car".
The tubing diameter could be small (even a few mm, or 2/10" would be okay) and the tube can be long (many meters). I suppose you don't need a lot of water at once. The most important thing is, how high the water must be pumped. If you need to pump the water to the second floor, you need a stronger pump, stronger valves, and even stronger tubes and connectors.