Basically a switch that can be controlled by GPIO is a relay.
relays that include manual controls do exist but these are typically going to be pulse (latching) relays because otherwise there would be no benefit from having the external control. theses relays are typically much more expensive than ordinary relays.
with the pulse relay there are only two switch settings on and off and both the user and the gpio get to fight over which setting is chosen.
If you find these relays unsuited you are basically left with some circuit that combines both a manual switch and a relay, it could be a circuit like the automotive dome light circuit where there are three switch settings on automatic and off.
For your tank application I would considwer using float switches as limit switches in combination with the relay, perhaps give the user a button that runs the pump, and a breaker to throw to turn everything off.