I've OD'd a few times- fortunately consistantly around hardcore junkies who have naloxone for 'emergencies'

. How painful/unpleasant it is seems to depend on how much of a tolerance you have and whether or not you're physically addicted.
Basically:
* if you have no tolerance it's like a sledgehammer to the face- you might have a couple of seconds to say 'fuck', but that's about it then black.
* If you have a tolerance, but are not physically addicted, you probably get the worst of it- you won't lose consciousness as quickly, though you will be unable to move/nodding hard you'll still be with it enough to know that your breathing is getting shallower and shallower, you may well feel strong histamine-type effects (feeling extremely hot, pins and needles etc). Naloxone (the thing that saves you) makes you feel like shit- addiction or no, I think that a lot of the unpleasantness is a lesson in irony from naloxone.
* If you're an addict you have the above experience, minus the bad parts- it's definately not 'painful', though you may be 'with it' enough to know something is going wrong. On the other hand, Naloxone throws you into instant precipitated W/D so you have a nice little present waiting for you if you do survive. The worst thing, as bad as it sounds, is having to wait a few hours before you can use again- you're in full-blown W/D, but you have to wait for the naloxone to 'wash out' of your opiate receptors before using will actually do anything.
Also- the shittiness depends on what you're overdosing on- heroin is the 'best/least worst' to OD on (in my experience) because it's fairly fast-acting and has very little in the way of a histamine-effect compared to, say, morphine. Overdosing on methadone is fucking shithouse- never had it happen to me (would have had to go to hospital for that), but friends have OD'd on methadone- they have to repeatedly give you naloxone (yay!) over the course of 4-8 hours before they can release you without having to worry that you'll simply OD in the parking lot (It's still in your body, if not your brain, but it can get back in there if you don't get given naloxone repeatedly). That's got to be the closest thing to medical torture I can think of- repeatedly giving a highdose-methadone addict naloxone for a methadone overdose.