Although either “Bordel de merde!” or “Putain de merde!” alone would probably suffice in most situations (see Reverso), in some rare cases of total disbelief, a combination of the two would be in order to take it from “just” “Holy Shit” all the way to “Holy Fucking Shit”: putain de bordel de merde!

Historically

Traditional swearing involving holy will be :

• Nom de Dieu

  (Populaire) (Trivial) Juron familier pour exprimer la colère, la surprise, le dépit, etc.

• Sacrebleu

  Etymologie :
  Altération de [Par le] sacre de Dieu, où bleu est utilisé par substitut pour éviter le blasphème.
  Interjection :
  Juron familier marquant l’admiration, la colère ou l’étonnement.

  Sacrebleu, si je m’attendais à te voir ici !

• All its derivatives : Palsambleu or Sangbleu

• Bon sang and Bon sang de bonsoir (with more about palsambleu too)

Today

More insulting and closer to what you are looking for and being more used nowadays will be :

• Bordel de Dieu or even, more raging one, Putain de bordel de Dieu

For your curiosity

And there is the curse words list of the most known swearing guy in French history ! (capitaine Haddock, friend of Tintin)

I’ll take the Urban Dictionary definitions and try to show you how the words putain, bordel and merde can fit in any of those situations. To me it’s more of a matter of how you say the words than a matter of words themselves. Hence my answer is more for spoken French than written French:

Surprise

One might add Oh in front of any of those words and get the “Holy fucking shit they found water on the sun” effect: Oh putain, il y a de l’eau sur le soleil! as well as the “Holy fucking shit the car in front of us is braking” effect. Repeating quickly that same word several times will add emphasis to the feeling of surprise and urgency: Merdemerdemerdemerdemerde

NB: good parents might say Oh lala in front of their kids in the car situation

Exasperation

The most natural thing I say when something exasperates me is some sort of Rhoo putaaain…!. Another way to handle those beautiful words would be to wrap them with mais and quoi, for example Mais merde quoi!. The wonderful Putain de bordel de merde works quite fine here. This is where the word bordel belongs the most

Indignation

Using those words to convey indignation is very similar to exasperation. It will be said in a more energetic way though: Mais putain quoi! C’est pas possible!

Wonder

To express wonder, once again, those words can be used. If you’ve been hiking for hours and finally reach this beautiful landscape you’ve been craving for, you might drop a ooh meeeerde! with a big smile on your face.

In short, if you say any of those words with a tone that expresses your feelings, it might just work 😉 Thank you for that question, I had never thought of how various usage we have of those words. And there are even more!

“Bordel de putain de merde à la con” is probably the most complete swear phrase.

For me, the best equivalent is Putain de bordel de merde.

“Bigre !”

Attribué à Maurice Couve de Murville face à une situation très embarrassante.
Ministre des affaires étrangères sous de Gaulle, issu de la HSP, ce personnage d’apparence austère, n’était pas dépourvu d’un humour pouvant être féroce.

« Putain de bordel de merde! » grogna-t-il en se secouant les cheveux avec degoût. « Cet endroit me répugne! »

p.233, Train d’enfer pour Ange rouge, THILLIEZ, F.

For the sake of completeness, for expressing strong surprise:

Ouah, la vache!

(or variants like “Oh, la vache!”, “Ah, la vache!”). It might perhaps work especially well in movie dubbing.

It also works in advertising, because it is socially acceptable (see a very humorous hijacking of the phrase, which shortly became a meme in the 1980s).