In the real world of material existence, there are no Gods. But God is "real" in the sense of Durkheim's social fact. If you believe in something, that thing is "real" between your ears. All the effects of prayer are valid so long as the thing prayed to exists between your ears. You are right that praying to something you know doesn't exist is, well, a questionable pastime, like rooting for the Mets.
Prayer is self-hypnosis, and communal prayer can be an extremely powerful narcotic since it is reinforced wherever the praying person looks. The god that Puritans felt in their little churches in the wilderness was far more real to them than medicine or physics is to the average person today. To be truly filled with the grace, power, and potentiality of that God must have been fulfillment itself. To have that feeling reinforced in everyday life and throughout your society must have multiplied a man many times over in confidence, satisfaction, and the feeling of righteousness. That may have been the closest men have come to the sleep of the just.
It was "fake" in the sense that the literal statements of doctrine -- Adam & Eve, the prophesies, miracles, the virgin birth, the resurrection, the body and blood of Christ, the Reckoning, salvation, the Trinity -- are all nonsense. But it was purely real in its internal and social experience -- men lived a story they constantly recapitulated to one another, and purged anyone who wouldn't play along in order to keep the illusion going. What else is civilization than telling ourselves a story and then holding ourselves and reach other to it?