Accept That Character Death, and Character Failure, Are Part Of The Game
First and foremost, no matter where you are on the scale of confident to terrified of killing PCs, you need to accept that character death is a part of the game. If your game has combat in it, and that combat is resolved with some form of random number generator - as most games use - then you inherently run the risk of player death every time combat comes into your game. This is important for your players to know as well. My personal view on it is that if you're not willing to lose your character in a fight, your character should not get into fights. Sometimes, however, it is good to remind your players of that. As the old maxim goes, those who live by the sword often die by it.
Choice Is The Difference Between A Good Death and A Bad One
The key differentiator between whether a PC's death is a good one or bad one (or should I say fair or unfair) comes down to choice. Now by choice I don't mean that a PC can't be killed unless the PC says "hey, it's ok to kill me here" but rather who chose for the - now - fatal conflict to happen? Did the PC pick the fight? Did they choose a path that led them to the fight? Did they have the option to disengage, runaway, surrender, go down another path? Or was there no way to escape the fight?
When I say no way to escape the fight, I mean before the fight actually began. Once blades are crossed you have mechanics dictating things, but before the fight?
A great example of this - even if he survives - is Luke Skywalker in Empire Strikes Back. Luke sees the plight his friends are in and then he chooses to go to Cloud City. He then chooses to follow the storm troopers until he ends up in the room with Vader, where he chooses to engage Vader in a lightsaber duel. Hell, part way through the fight Luke actually creates distance between him and Vader but chooses to pursue Vader instead of getting out while the getting is good.
If Luke were to die in the fight with Vader, the GM would/should have a clear conscience. Why? Because it doesn't matter if Vader is 10 levels higher than Luke, that was a known quantity before hand. The player/Luke made the choice to engage, and so that is where the blame - if it exists - should go.to the player.
Death Should Be Memorable
Ideally you want a player's death to be memorable. By memorable I don't mean poignant. Sometimes death can't be super epic or anything like that. A TPK doesn't always leave room for a heartfelt goodbye. However, you should do what you can to make the death memorable. Player Choice is a big part of this as well. After all, if the players chose the fight they had a reason, and that helps make it memorable. Beyond that though, doing what you can to make things memorable can help ease the sting.
Did a random mook just kill the rogue? Maybe it's not such a random mook anymore. Maybe it gets a name, and it escapes only to harass the players again and again until they kill him and get revenge. Did a big bad kill a PC in a fight the PC ought not to have taken? Use it. Play it up. Taunt the PCs with their dead friend.
Death can be a wonderful motivator for the survivors, and it can help make a villain feel more real.
That really is it. If a death, or TPK, comes about from the player(s)'s choice, and you make it memorable, then it can and should be a good time. it may suck to lose beloved characters. It can leave a "what now" feeling for things, but ultimately if you have reasonable players and they chose that path they'll know what happeend, and they may have a blast doing it. It can be good times.
Like the picture below says. Do it right, and they'll never forget it. (I found the picture here.)