The phrase isn't related to the well-known antipathy between dogs and cats. Nor is the phrase in any sense literal, i.e. it doesn't record an incident where cats and dogs fell from the sky.
We do know that the phrase was in use in a modified form in 1653, when Richard Brome's comedy The City Wit or The Woman Wears the Breeches referred to stormy weather with the line:
"It shall raine... Dogs and Polecats".
The much more probable source of 'raining cats and dogs' is the prosaic fact that, in the filthy streets of 17th/18th century England, heavy rain would occasionally carry along dead animals and other debris. The animals didn't fall from the sky, but the sight of dead cats and dogs floating by in storms could well have caused the coining of this colourful phrase. Jonathan Swift described such an event in his satirical poem 'A Description of a City Shower', first published in the 1710 collection of the Tatler magazine.
The first appearance of the currently used version is in Jonathan Swift’s A Complete Collection of Polite and Ingenious Conversationin 1738:
"I know Sir John will go, though he was sure it would rain cats and dogs".
The fact that Swift had alluded to the streets flowing with dead cats and dogs some years earlier and now used 'rain cats and dogs' explicitly is good evidence that poor sanitation was the source of the phrase as we now use it.