It sounds like we've more or less come to a reasonable facsimile of a consensus of the D&D facts of the matter:
1) The multiverse "Great Wheel/Planescape" universal D&D cosmology predates the Forgotten Realms.
2) The Forgotten Realms was initially designed to use this cosmology.
3) Within this cosmology (including the Forgotten Realms) is an idea that the gods at least partially depend on and derive part of their power from their worshippers.
4) At some point significant changes, such as the Fugue Plane*, were introduced.
5) In 3e, Forgotten Realms was given its own somewhat different cosmology with nominal ties to the original.
6) At one point Kelemvor took down the Wall. This resulted in a lot of people not following the gods, and Kelemvor reinstated the Wall.
7) The 5e products restore all settings to the multiverse cosmology (with the caveat that the same cosmology can be viewed in different ways).
With that out of the way, I find it interesting to discuss how the whole deal with the Wall (and the way the Forgotten Realms deities have decided to run their world) might be looked at from an in-character perspective, and if there is anyway of dealing with it in a more satisfying manner, without actually changing what is written.
So, what are some differences between the deities in the Forgotten Realms and those in other settings? For one, there are a lot of them. And there are a ridiculous number of them that are Greater Powers. If the Forgotten Realms pantheon went to war with any other D&D pantheon on neutral ground, they would just wipe the floor with them in a fair fight.
It's also interesting that the Forgotten Realms pantheon has "interloper" deities who have been accepted in the Faerunian pantheon. Tyr is the first one that always comes to my mind. Why are these other gods showing up and hanging out with the Faerunians, when some of them are already parts of other pantheons?
Here is my theory for why things are the way they are in the Forgotten Realms, compared to the rest of the multiverse:
A) The way they have their afterlife set up channels extra worship (=power) to the gods of the Forgotten Realms.
B) This excess of power is what allows for so many deities and such powerful ones.
C) This is also what draws foreign deities. Follow the rules when you're over here and get a pay raise.
D) While the Forgotten Realms pantheon is fairly loose, it is also fairly strongly regulated by Ao. He makes sure that divine portfolios do not overlap.
It is probably the extra worship that accounts for A), but it might also depend on the Wall of the Faithless itself (not just the threat of it). By not allowing any souls to escape, they are keeping all the soul-power directly bound to their world (assuming the Fugue Plane is bound to their world).
So, in order to keep the Forgotten Realms deities living the high-life, they have to maintain this current setup. It's also possible that Ao demands it for his own neutrally unfathomable reasons, and would just throw them out if they got too upity.
Now, with that setup, we also have to realize that it was Myrkul, a really nasty god, who created the Wall of the Faithless in the first place. And he was the one who held the portfolio that let him make that call. So the origin of the Wall can be based squarely on Myrkul, and secondarily on Ao's permission. The good deities aren't actually involved in the origin of the Wall. How did the good gods see the Wall? If they were born n' raised in the Forgotten Realms pantheon, and never traveled far from home, they might not even realize there was any other way of doing it! Maybe no one even told them that it was all Uncle Myrkul's idea. For a deity like Tyr, who apparently gets around enough that he found the Forgotten Realms, that isn't going to hold true.
So it is unclear what the reactions of the majority of the good deities would have been, because we don't know how much they understood about the differences of doing things. I think they would probably, however, realize that the current setup is what much of their power is deriving from.
Apparently, when Kelemvor took the Wall down and started judging people by alignment rather than worship, it was publicized to the mortals of the world. Enough so that people started acting crazy. The point could be made that perhaps the reason inhabitants of the Forgotten Realms acted in such a manner, when inhabitants of other worlds don't, is because they had been so used to the strict requirements that they were reacting to the loosening up of the rules. (I think there is a better explanation, which I will explain in a bit).
It became apparent that this new situation wasn't working. The good deities now have a moral quandary, regardless of how they initially reacted to the presence of the Wall. Without the Wall, we can let souls move on to a fair afterlife without having to worship us (they now can worship us if they choose to because they like us, or because they like what we are offering in particular, etc), but our power is starting to drop. Presumably, deities would start diminishing in power. Many Greater Deities might downgrade, and some of the minor ones might lose their divine status entirely.
So, do we throw our opinions in with the choice that seems to be more benevolent, or do we throw our support to the choice that allows us to maintain our power? Why might they take the second angle, other than selfishness? I don't know, but perhaps they might be concerned that a diminishing of power would somehow favor evil over good. It's a possibility, depending on how the power is distributed. I could go through the listing of how much power the various deities currently had if I really wanted to (and I don't) but that is unnecessary, since we can assume there might be factors other than the published divine rankings that are known the Forgotten Realms deities that might validly influence their assessment.
So it is
possible that maintaining their power was actually the most good choice given the situation, if we assume there are elements of losing their power that we don't know about.
However, if I'm hearing it right, apparently the good deities (at least Mystra, and were there others?) actually supported Kelemvor's changes. (If they were split on it, I think it's fair to give the good deities the benefit of the doubt and assume there were other morally justifiable reasons to take the other side.)
But the thing is, it wasn't their call. They could try to convince Kelemvor, but it's his portfolio. So it's up to him and any relevant orders from Ao as to what happens. Again, I haven't read the books, but appears that some combinations of circumstances made the lawful neutral Kelemvor decide that putting the Wall back up was the more responsible course of action. Some of the good deities were very unhappy about this.
So, the moral question then becomes--what can the good deities who disagree with reinstating the Wall do about it? It doesn't seem like they have a lot of good options. They could challenge Ao on it, but I take him for a jerk, so that wouldn't do anything unless they decided to actually go to war against him. And I'm not sure if there is any chance of overthrowing him, or if it is purely impossible. Maybe they don't know either! Or maybe they do know, and its impossible. Or maybe it's theoretically possible, but they'd take losses in the attempt, and that would strengthen the evil deities. In any event, it's mostly like a very bad idea. They could always pack up shop and leave. I can't think of anything that would really prevent that. They'd probably have to go join some other pantheon and get worshippers elsewhere. A hassle to be sure, potentially filled with doubt and uncertain, but a possible life decision to be sure. Or the other choice, and apparently what they decided on, was to stick around and try to make the best of it. Just do their job, use their power for good, and maybe, just maybe, see if you can convince Kelemvor to give something else a try the next time something shakes the foundations of the heavens and he's in a listening mood.
Moving on from the good deities, how about mortals? Why did they go crazy, and why don't mortals on other worlds? Here are my thoughts.
It was (in addition to poor writing) the publicizing of the changes that was at fault. I don't imagine many people on any D&D world actually understand much about the afterlife. I doubt the average inhabitant of Oerth could list off the Outer Planes, or even has a clear conception of what happens after they die. They probably know there are heavenly places and hellish places, and likely know the name of their deity's home domain--where they are hoping to end up. They know that if they are good boy or girl, and do what their deity says they are supposed to, they will end up in Heaven Home (or whatever), and receive certain rewards. If they are bad, they'll go to someplace hellish. Those rare evil people who willingly follow evil gods figure they can get a break by worshipping the ones who are in charge where they expect they are going (unless they are crazy or misinformed--always a possibility).
As far as the idea that if you die without worshiping a deity your soul drifts through the Astral Plane (the what?) to reach an Outer Plane (yer usin' them words again! I dinna know what yer sayin!) that most closely corresponds with their alignment (heh? what's wagon wheels got ta do wit it?)...I doubt even a typical entry-level priest knows that sort of thing.
So Kelemvor's mistake, the reason people stopped worshipping the gods,
wasn't because he made a nicer afterlife, but because he told people details they weren't able to process well, and they made dumb decisions based on that.
Sounds fairly reasonable to me.
Now, how do I handle that in my campaign? What twist can I put on it, without changing what is written in the 5e materials, to make the Forgotten Realms afterlife a bit more forgiving? My thought is to say that Kelemvor learned from his mistake, put back the Wall, but is now walking a middle path--and isn't telling the mortals about it. "The Wall of the Faithless is back for unbelievers." What he doesn't say is "I'm only going to throw the real troublemakers into it. For those who were more 'meh' I'll probably give them a chance to convert once they are dead, or if they really aren't into it, just kick them out into the Astral and let their souls drift where they belong." Since a neutral person who worships a good deity can get a better afterlife (subject to their deity's judgment) than a neutral person who just drifts to an Outer Plane, it's probably the nicer thing to do to try to convince them to follow a deity and then leave them to the natural results of their actions if they don't.
That should allow Kelemvor to maintain the best of both worlds. Most people end up sticking with the gods, and thus little power is lost. Those who just really don't like having a patron deity can get quietly booted out to wherever their alignment takes them. And for those who are really, really annoying...well, the Wall is always an option.
* Does anyone know when the Fugue Plane even made its debut? Was it pre-3e?