It reads like DracoFelis answered most of the pragmatic questions. I think this solution requires an optimum real estate situation, i.e. a hill that you own to locate the upper storage tank (or hole in the ground that you dig), good precipitation, and such. Ten to one this has already been attempted by an 0ff-grid homeowner. Seems like a homepower.com or homeenergy.org article to me.
We thought about doing this 20+ years ago when we had a place with a quarry on a hill top, but it wasn't the kind of quarry with pits full of water, so tanks or ponds would have had to be constructed. As it turned out, our wind generator located near the house at the bottom of the hill produced 90% of the power we needed, the rest supplied by rarely running an old engine driven generator, and later with PV panels, so we never did much except a rough estimate of piping cost, etc. , which was enough to scare me off.
Good old lead acid batteries work fine for me in this kind of situation, but using the power directly (such as solar hot water panels) can be cheaper.
Reminds me of when I lived in the mountains. Our house got water from a nearby stream, so we got a small water-powered generator. We had a water tank up the hill so there was plenty of pressure and constant supply. The generator was basically a 4" water wheel in a sealed housing with a fitting to screw in the garden hose. An automobile alternator was attached to the wheel. We got an AC/DC converter (rectifier?) to tricklecharge several batteries all day long. It was enough to power most of the lights, radio and TV at night, but we had to use a propane generator for the stove and fans. When the batteries couldn't keep the TV on anymore, it was bedtime!
The water coming out of the generator went in into the back garden which grew very well with a constant supply.
So let mother nature do the pumping. If you're house is efficient you won't need much power or storage capacity.