A generation spaceship (a spaceship designed for multiple generations of humans to live in) could sail through the galaxy for hundreds of years before finding another planet, let alone a planet with water. What method would such a spaceship use to maintain a source of water fit for humans to drink (for an indefinite period of time)?
Here I will evaluate the most obvious option: can a ship actually simply store the amount of water needed? Using Fermi estimation, we can suggest that each persons drinks maybe 2 litres of water a day to stay healthy, which is about 730L or so. If the population of the ship were about 105 people, we'd need about 108 L of water each year. If every 106 years or so the ship managed to find fresh water (on a new planet) to refill, the ship would need to store a total of 1014 litres of water.
Is it viable to store 1014 litres of water on a spaceship? A litre of water occupies 1000cm3, so 1014 litres of water would occupy 1011 cubic metres of space. Wolfram Alpha helpfully informs us that 1011 cubic metres of space is about two and a half the volume of the Three Gorges Dam in China:
According to Wikipedia, the volume of the Three Gorges Dam is about 40km3, so if we can have 120km3 space (in addition to space for living, etc.) we could actually simply store the amount of water required. The Bernal sphere, an existing proposed generational ship, is a sphere with diameter 16km. The volume of that sphere is 2144km3, so it easily has enough space to store water to keep humans alive for hundreds of thousands of years. A surprising result, but one that is actually entirely viable. In addition to this, resuse of water and recycling could even decrease the amount of water needed from my estimation, making this method even more possible.
However, as soon as we get to higher orders of magnitude of how long we wait in space for without any additional water "refills", this storage method becomes impractical. At 1015 litres of water it is already impossible to store all of the water - we'd need 250 Three Gorges Dams to store all the water -- clearly an impossible task. We'd have to create the water instead.
Water has the chemical formula H2O, which means it is formed out of two hydrogen parts and one oxygen part. If we had the parts, and enough energy to do it, we could synthesise water. We won't worry about where the energy comes from - we'll just assume we have a magic energy crystal or something. If we also had an infinite supply of oxygen and hydrogen, we would have an infinite supply of water - this way, no matter how long we stay in space for, there would always be water. But where could we get an infinite supply of oxygen and hydrogen from?
Even the deep vacuum of outer space is not completely devoid of hydrogen - there is an average of 0.1 hydrogen atoms per cubic metre in outer space. Assuming that this value probably is higher nearer to planets, and considering the large surface area of the generational ship, this may be a valid option to create water. There is about 3.34 * 1025 molecules of water in a litre, with each molecule needing two hydrogen atoms. The surface area of the Bernal sphere is about 804 km2. Assuming it moves at least its length per minute, and the surface area of the ship is capable of 'absorbing' hydrogen atoms, it would be getting 8.04 * 107 hydrogen atoms per minute, which is a litre of water every five minutes or so. This isn't sustainable for 105 people, but remember that the 0.1 hydrogen atoms per cubmic metre figure is only for outer space -- nearer planets and in astroid belts, etc. will have much higher values. For Oxygen, obviously the ship is producing oxygen somehow for the people inside to breathe - so water production could just use the same source as whatever source the ship is using. Altogether, this seems like a viable approach to water storage as well for very long distance travel.
In addition, it should also be noted that most of the water would likely be recycled -- this would further reduce the water requirements. I'm not sure how much of the water could be recycled with each go and how much would be lost to space, but if the ratio is even only one half, that already means halfing the number of Three Gorge Dams the ship needs to store, or halfing the required rate of synthesis of new water. My calculations -- especially with the water synthesis -- is very conservative, so it is more than likely for 106 years or less of travel, storage of water is a good solution, whereas for greater than that, you'd have to resort to making your own water.
Generation ships would need to have reserves to cover loss into space but the vast majority of the water would be recycled. This could either be done artificially (using filters, processing plants etc) or naturally by simulating the water cycle on earth using rivers, lakes, etc.
There is a certain "yuck" factor to the idea of drinking water that someone else eliminated but you have to remember that that's exactly what you do every time you take a drink here on earth. Most of the water on the planet has passed through a multitude of creatures at some point in its existence!
Since you're talking about a space ship designed to operate for hundreds of years I'm assuming a fairly decent level of technological advancement for the ship to have been built, as such
Method 1: Recycle
How are the people on board cleaning dishes, bathing or going to the toilet? If they use water, what's happening to the dirty water? Rather that blowing out into space, store it and treat it to become drinkable again.
Method 2: Molecular Construction
Obviously the people who built the ship know that water is made from 2 parts Hydrogen and 1 part Oxygen. Since Oxygen is in the air needed for humans to breath, how is the ship generating enough air for everyone? Maybe boost it to supply Oxygen and find some indefinite source of Hydrogen (eg. regular detours to asteroids every...say 50 years?) then with these build the molecules to make water.
Method 3: Still unknown technology
Maybe there's a piece of technology (or artifact) that is producing water (a machine, a crystal which seeps water), its mystery could be the focus of a plot/scenario to discover its origins. Maybe it itself was the reason why the ship was built since water is seen as the source of life the ship was build as a means to sustain generations from a single source of water.
Method 4: Evolution
Maybe with scarce water humanity on the ship has evolved so no longer require it...though this method kinda defeats the purpose of your question.
We already have the technology to purify sewage to drinkability, what you are after is trivial compared to what else is needed for a generation ship.
I forget the name of the process but under enough heat and pressure water becomes incredibly reactive and can tear apart most anything organic in it. Add enough oxygen and the output products are water and atmospheric gasses.
Considering the size of a generation ship, there's a few options available
Using water mixed with organic waste products for food growing. There's good nutrients there, and the right plants plants fairly efficiently convert waste water and other nutrient rich, post-consumer substances into oxygen, biomass and water vapor, which could be condensed into fairly clean drinking water, and processed into other products. Some algae may even produce fossil fuel analogs or plastic.
the swimming pool/beach in space is a common thrope in science fiction - You want to store a fair quantity of water anyway - its great, low cost radiation shielding and at a pinch could be used for reaction mass.
Assuming you have energy, water recycling is not a hard problem - not just taking into account the biological approach, assuming you have energy or funding, its a fairly mature technology. There's even consumer ultra/nano filtration systems and/or devices that wick moisture from the athmosphere. And least in singapore, part of the water we drink is post consumer, and recycled from our waste system. No one really complains. We even have bottles of the stuff given away at official functions.
The name of the process you are looking for is reverse osmosis. It's currently cheap, quick, effective and easy. You can turn waste water into drinking water in seconds. Add a ion exchange resin and you get almost pure water. Now you have a problem that is treating the solid waste. This can be done with composting. Water will lose its salt content so you need to extract back the salts from the solid components that you composted.
The resulting water will have very low salt content, and will be toxic if ingested as such - considering a very powerful reverse osmosis membrane - now you have to return salts to it.
Use the composted solids to increase soil yield. Build your agriculture ponds in such a way that they have solid compost on the top layer, followed by fine sand, gross sand, small pebbles, large pebbles and a base plate full of holes that drain into a storage tank. This water will be clean while at the same time will have the correct salts to avoid demineralized water toxicity.
It's useful to link your water recycling system to your agriculture, because this mimics how hydrological cycle works on earth.
Combining oxygen and hydrogen with a spark is one way. Storing liquid O2 and liquid H2 would probably take up less space. (water is wide)
Humans EXHALE water every day. 2 liters is a figure that pops to mind. I cant remember if it was every 24 hrs, I was reading about moisture accumulation on walls of a tent so I think it was in 8 hrs, so 3x as much = 6 liters.
Food waste contains water.
HUMAN WASTE contains water that can be recycled.
Comets could be "harvested" by smaller scout ships along the way.
Some moisture MUST be in the air we breathe to keep lungs from drying out. and too little humidity is actually bad for computers(hard to imagine), causing static to build and discharge, damaging electronics. (source... some Intel documentation a few years ago...)
Waste humans (not sewage... dead people!) can be dehydrated and purified.
Extra hydrogen can be gathered from the interstellar medium, would need oxygen to construct water though. Roughly one atom per cubic meter? Due to solar winds, space is not actually COMPLETELY empty! A massive enough star could expel trace amounts of oxygen as well.
Agriculture in interstellar space? Guess again. There is no sunshine out there. “Grow lights” would need an energy source. Nuclear energy would be good for about six years and then you would need some fresh fuel rods. Not even out of the solar system yet, let alone getting to Proxima Centauri which will take a lot more than six years.