The functor Over is covariant, and you do have that Over(X1∐X2)≅Over(X1)×Over(X2). However, I wouldn't say Over "turns coproducts into products", since that phrase usually
means that additionally the canonical maps Xi→X1∐X2 are sent to the canonical projections Over(X1)×Over(X2)→Over(Xi), and for that, of course, the functor would need to be contravariant.
In this example what is really going on is that Over(X) is equivalent to Fun(X,Set), the category of functors from X to Set (where the set X is regarded as a discrete category, i.e., the category whose objects are the elements of X and that only has identity morphisms). [The equivalence is given by sending f:A→X to the functor that sends an element x in X to its inverse image under f.] Of course, the functor Set→Cat given by Fun(_,Set) is contravariant, and it does turn coproducts into products in the sense I mentioned above. In summary, the covariant functor Over and the contravariant functor Fun(_, Set) agree on objects even though they have opposite variance, and the second turns coproducts into products.
Edit to answer the edit to the question: the pullback does indeed make Over(X) into a contravariant functor which is naturally isomorphic to Fun(_,Set). This is easy to check: if you have an object of Over(Y), say f:A→Y, and a function g:X→Y, then the inverse image of an element x in X under the pullback object of Over(X) is just the preimage of g(x) under f (the definition of pull-back is basically chosen to make this true).
As k.stm says in the comments, usually a more general thing is true: these categories $C$ are equipped with forgetful / underlying set functors $U : C \to \text{Set}$ which tend to have a left adjoint, the "free" functor $F : \text{Set} \to C$. Whenever this is true, it follows that $U$ preserves all limits, not just products.
Sometimes, but more rarely, $U$ will also have a right adjoint, which will give a "cofree" functor. Whenever this is true, it follows that $U$ preserves all colimits, not just coproducts. This happens, for example, when $C = \text{Top}$: here the left adjoint equips a set with the discrete topology and the right adjoint equips a set with the indiscrete topology. But it doesn't happen for, say, groups or rings.
So one way to reformulate your question is:
Why do the forgetful functors $U : C \to \text{Set}$ we write down tend to have left adjoints, but not right adjoints? Equivalently, why are there usually free structures, but not usually cofree structures?
A rough answer is that we can expect "free" structures whenever the structure is described by operations satisfying equational axioms, since then we can build free objects by applying all possible operations modulo all axioms. On the other hand, operations also make it difficult for the forgetful functor to preserve coproducts, since in a coproduct of two structures you can apply operations to elements of both structures, so you'll usually get something bigger than the disjoint union.
(Dually, you should expect "cofree" structures whenever the structure is described by "co-operations," and this does in fact happen: for example, the forgetful functor from coalgebras to vector spaces has a right adjoint but not a left adjoint, called the cofree coalgebra.)
A more precise answer would invoke, say, the machinery of Lawvere theories, which among other things has the benefit of also telling you exactly what colimits you can expect these forgetful functors $U$ to preserve. This is a long story so I don't want to get into it unless you feel like it really answers your question, but the gist is that Lawvere theories present familiar structures like groups, rings, and modules in a way that fundamentally uses finite products, but nothing else. You can deduce from this that the forgetful functor $U$ preserves (and in fact creates) any limits or colimits that commute with finite products in $\text{Set}$. Every limit commutes with finite products, and the colimits that commute with finite products in $\text{Set}$ are precisely the sifted colimits. These include, for example, increasing unions, which is an abstract way to see why the set-theoretic increasing union of a sequence of groups is still a group, and the same with groups replaced by rings, modules, etc.
Best Answer
For all the algebraic examples $\mathscr{C}$, you could observe that they are all equivalent to some category $\mathrm{Fun}^\times(\mathscr{T},\mathsf{Set})$ of finite-product preserving functors, where $\mathscr{T}$ is the Lawvere theory of that algebraic structure. For each of the algebraic examples you list, there is an object $x\in\mathscr{T}$ for which the evaluation functor $\mathrm{ev}_x\colon\mathrm{Fun}^\times(\mathscr{T},\mathsf{Set})\to\mathrm{Set}$ corresponds to the forgetful functor $\mathscr{C}\to\mathsf{Set}$ under our chosen equivalence between $\mathscr{C}$ and $\mathrm{Fun}^\times(\mathscr{T},\mathsf{Set})$. But then it is not difficult to show that this forgetful functor preserves limits, but generally not all colimits.
If you accept that the forgetful functor $\mathsf{Top}\to\mathsf{Set}$ preserves limits, then it is general nonsense that the composite $\mathsf{Top}_{X/}\to\mathsf{Top}\to\mathsf{Set}$ does too, for any $X\in\mathsf{Top}$. This is because the forgetful maps $\mathscr{C}_{X/}\to\mathscr{C}$ and $\mathscr{C}_{/X}\to\mathscr{C}$ preserve limits respectively colimits, while they do not preserve general colimits respectively limits.
Since $\mathsf{Top}\to\mathsf{Set}$ also preserves all colimits, it isn't an example of the asymmetry, and the asymmetry in $\mathsf{Top}_*$ arises from taking the slice category $\mathsf{Top}_{*/}$, whereas taking an arbitrary overcategory would have made colimits be preserved while limits would be not preserved. (And overcategories are also generally interesting categories).
This brings us to examples of interesting categories where the underlying set of the product is not the product of the underlying sets. One example is $\mathrm{Top}_{/X}$ for $X\ncong *$, and generally categories consisting of ''bundles over a base object'' will be examples. I'd argue that these are genuinely interesting categories.
Another example in a similar spirit is the category of schemes, where the underlying set is the underlying set of the locally ringed space associated to the scheme. You might argue that schemes are closely related to $\mathsf{CRing}^\mathrm{op}$ and therefore it may not count, but when restricted to affine schemes the forgetful functor to sets is genuinely different than the forgetful functor you'd get if you'd define affine schemes to be objects in the category $\mathsf{CRing}^\mathrm{op}$, so this is a nontrivial example.