The green stuff, 609, is the medium strength "retaining" compound. It can fill small voids, as opposed to thread locker. It works good for worn dovetails for those who do not have the courage to properly fix them with drift and hammer. Many use it on the barrel extension of an AR to fix a sloppy upper receiver, but there are better ways to fix that too. To each their own.
I don't have an 1860 colt at hand. How is the sight installed? My guess would be a shallow dovetail and soldered/brazed on? The green stuff would be considered a temporary expedient fix.
EDIT: your sight should be a press fit. (brass?) you can lightly peen the bottom to increase tension. The green stuff should would work as a temporary fix. Will easily remove with just a little heat (like touching a soldering iron to the sight) when you are ready to do it right.