Fiber products of manifolds

Here's an argument for the first question. Take $X=Y=S=\mathbb R$ and take $x\mapsto x^2$ for the maps $X,Y\to S$. As you note, the underlying set of $X\times_S Y$ is what you'd expect, $\{(x,y)\mid x=\pm y\}$ (at least up to bijection, and so without loss of generality).

Lemma: Let $Z$ be a subset of $\{(x,y)\mid x=\pm y\}$ such that $Z$ defines a smooth manifold $M$ in the usual topology coming from $X\times Y$. Then the subspace topology induced on $Z$ from $X\times_S Y$ is the usual topology.

Proof: By the universal property of $X\times_S Y$ (and using morphisms from the point), the inclusion $M\to X\times Y$ factorises in $\mathsf{Man}$ into two inclusions: $$M\to X\times_S Y\to X\times Y$$ Restricting to $Z$ we get continuous inclusions which we can denote $M\to M'\to M$. But this implies $M=M'$. $\square$

In particular, applying the lemma three times, with $Z$ equal to $\{(x,y)\mid x=\pm y\}\setminus\{(0,0)\}$ and $\{(x,x)\mid x\in\mathbb R\}$ and $\{(x,-x)\mid x\in\mathbb R\}$, we find that $X\times_S Y$ induces the usual topology on these sets. So $X\times_S Y$ is connected but $X\times_S Y\setminus\{(0,0)\}$ has four connected components. This cannot happen with smooth manifolds: removing a point from a manifold increases the number of connected components by at most $1$.

Regarding failure of existence of fibre products and the comparison with the case of schemes, the basic point is that the intersection of smooth objects need not be smooth. (The fibre product of $f:X \to S$ and $g: Y \to S$ is the intersection of $\Gamma_f \times Y$ and $X \times \Gamma_g$ in $X \times S \times Y = X \times Y \times S$; here $\Gamma$ denotes graph.)

In algebraic geometry we "remedy" this by allowing non-smooth varieties/schemes, etc. But the possible singularities that can arise by intersecting algebraic varieties are much tamer than those that can arise by intersecting submanifolds of some ambient manifold. So it's not surprising that it's not routine to extend the theory of manifolds to include singular objects. There are such extensions, such as the theory of stratified spaces, which among other things are related to issues of transversality and so on that arise when considering singularities of intersections. But the theory of such objects is certainly not a routine extension of the theory of manifolds.

One reason that people work with O-minimal structures is that these provide a setting which is somewhat close to the topology of manifolds, while being tame enough that one can form pullbacks and fibre products and stay in a world of reasonable objects.