When we draw a Newtonian free body diagram of a man standing still on land, we draw force g and the reaction force. In water when floating still, we still have force g but we also have the reaction force and buoyant force. We are not accelerating in either case. So what makes us feel weightless in water but not on land?
This is less of a physics question and more of a neurophysiology question. Physically, as you have noted, both the proper acceleration (measured by an accelerometer) and the coordinate acceleration are the same in the two situations. So this is about the neural pathways involved in producing this specific illusion.
There are four main senses involved in the sensation of acceleration. The first is your inner ear. This is your body’s accelerometer. Inside your ear are several tubes filled with fluid, and the location where the fluid pools tells you which direction you are (proper) accelerating. This sensation habituates very quickly, meaning that after a short time of constant sensation the brain ignores it. So it is practically a sensor of changes in proper acceleration.
The inner ear sensation is heavily modified by the second sense, your visual system. In humans the visual system is and the associated neural pathways are particularly strong. When your inner ear says that you are accelerating and your vision says that you are not then you feel dizzy. Usually your vision dominates and the overall sensation is more determined by the vision than the inner ear.
When floating or standing generally both the above sensations are rather similar. Not much visual input and not much inner ear activity. Any differences may be a gentle inner ear “sloshing” which is easily habituated and ignored in favor of the visual input.
The third related sense is pressure, sensed by your skin. This sensation is particularly different in standing or floating. In standing the pressure sensation is strong and concentrated on your feet. In floating it is weak and distributed across a large surface area. This weak and distributed sensation is easier to habituate and ignore than the strong concentrated sensation. If you specifically direct your attention then you will notice it, but because it is weak it is easy to neglect if you do not specifically attend to it. However, the brain also habituates rather quickly to the pressure on your feet. So although this sensation is different in the two situations, after a short time it is ignored.
Finally, the last sense involves in producing this illusion is called proprioception. Proprioception is the combination of all of the internal signals used to determine the body’s position relative to itself. This is the sense that allows you to accurately touch your nose instead of poke your eye, even with your eyes closed. In my opinion this is probably the most important sense in producing this illusion. Proprioception is one of the slowest to habituate senses. Proprioception is crucial in the ability to stand upright in gravity, with many very short reflex pathways that detect perturbations to your posture and trigger muscles to contract and maintain your posture. Muscles in your back, shoulders, neck, butt, and legs are all triggered and adjusted frequently to stand. In contrast, to maintain posture while floating takes very little effort from far fewer muscles. The perturbations to the proprioceptive system are weak, and as this sensation is less subject to habituation this seems likely to be the dominant one in producing the illusion of weightlessness. Since the body is not subject to the burden of continual muscular effort to maintain posture, it feels substantially different from our normal experience, and is interpreted as weightlessness.
Do you feel weightless when floating on water?
I don't. I feel that the water is carrying me. I can feel how gravity pulls me down while the buoyancy force holds be up. Just like my soft mattress is carrying me when lying in bed, holding me up with a normal force.
What is typically meant by weightlessness is either
None of these two definitions qualify for the scenario of floating on water. I wouldn't call that weightlessness.
You might call it weightlessness, when you are submerged into the water with nothing but water pressure and gravity acting equally from all around. This would be comparable to skydiving where you are "submerged" in the air with no other external effects other than the air and gravity acting. In both situations a buoyancy force is also present, though, and it is much stronger in the water due to water's density, which might disqualify being submerged in water as being weightless anyways.
Although in each situation- on land and in water- the weight of your body is countered by a normal force, there are several differences between the two cases that account for the difference in the physical sensation.
On land, the normal force is concentrated on whichever parts of your body are in contact with the supporting surface, whereas in water it is spread over a much larger area, so the pressure of contact feels much less.
On land, the normal force exists only when you are in contact with a fixed supporting surface. If you lie in the floor, for example, your weight holds you at the level of the floor- you cannot move further down, and to move up you have to take your weight on your hands or knees or feet and push yourself up. In water, by contrast, there is no hard fixed surface- you can move around in it vertically and it constantly repositions itself to support you.
Unlike the firm ground, water envelopes you- a price you pay for finding it relatively easy to move vertically within water, is that you will find it harder to move horizontally. Walking submerged is much harder than walking along the ground in air.
Dale's answer is an excellent breakdown, but there is still an important component missing.
Notably: why is the experience so different from lying on a highly contouring mattress in air? Proprioception and the ability to move freely in the vertical direction (as Marco pointed out) are part of it, but there's another factor.
We've established that the brain perceives pressure equally distributed over the whole body when fully immersed in water. But what is not mentioned is that the body experiences more inward pressure in water than in air. Therefore, the contrast the brain perceives between buoyant (upward) forces and inward pressure is greatly reduced compared to our normal experience in air. Considering the brain habituates more easily to less intense and more uniform forces, this is what we're experiencing.
Another way of saying this, and why it's different from Dale's answer, is that even if you have the perfect conforming mattress in air, the sensation that prevents your brain from habituating as easily as in water is that the inward pressure from above and to the sides due to air is much less intense than the support from below. In water, the buoyant force feels almost indistinguishable from the rest of the forces surrounding the body.
Even though the forces due to water pressure are additive to the normal and gravitational forces in the respective directions, the additional water pressure is less familiar and so uniform that the brain has more difficulty sensing the differential. This would be analogous to swaddling a baby tightly: the inward pressure makes the normal force a lesser part of the overall sensation.
