I am looking for an example where $f:Y\to X$ and $f':Y'\to X$, are both smooth maps of smooth manifolds, but the pullback does not exist.
Remarks:
1) A pullback in a certain category is defined as a space satisfying a universal property, not as a fiber product. (Which is just the usual form of many pullbacks…). See a clarification at the bottom.
2) I know there are examples where the pullback in the smooth category exists, but is different from the fiber product $Y \times_X Y' = \lbrace (y,y') \in Y \times Y'\mid f(y)=f'(y') \rbrace$ (which is always the pullback in the topological category).
This is not what I am looking for, since in this example the smooth pullback (as the space satisfying the required universal property) exists.
3) In any case where the fiber product is a (smooth) submanifold, it is the pullback. Therefore, any possible example must be one whose fiber product is not a (smooth) submanifold. (In particular $f,f'$ can't be transverse to each other)
4) In this answer there is a possible way of poving some limits does not exist. Maybe it's possible to use this method here also, but so far I didn't find an example
Clarification of the definition of pullback:
A space $Z$ (more precisely a diagram $Y \overset{g}{\leftarrow}Z\overset{g'}{\rightarrow}Y'$ which complete $Y\overset{f}{\rightarrow}X\overset{f'}{\leftarrow}Y'$ into a commutative square) is said to be a pullback if for any diagram $Y \overset{h}{\leftarrow}W\overset{h'}{\rightarrow}Y'$ (which commutes with $Y\overset{f}{\rightarrow}X\overset{f'}{\leftarrow}X$), there is a unique smooth/continuous map $u:W \to Z$ such that $h=g \circ u$ and $h'=g' \circ u$. (see Wikipedia).
Best Answer
If a pullback exists in the category of smooth manifold then, its underlying set of points has to be what you described simply by looking at morphism from the point. Moreover a map into the pullback is smooth if and only if the map to the product is smooth (because it is smooth if and only if each component is smooth by the universal properties of the pullback).
So the only things surprising things that can happen if the categorical pullback exists, is that the fiber product has a smooth structure which is not induced by the differentiable structure on the product, but still have the same smooth map into it, like the example you gave a link to... But this situation is rather weird, and it is a lot more common to simply have no pullback:
So take for example the pullback of $\{0\}$ along the map $(x,y) \rightarrow xy$. If this pullback existed it would be a smooth structure on the union of the horizontal and the vertical line in $\mathbb{R^2}$ such that a map into it is smooth if and only if it is smooth when seen as a map into $\mathbb{R^2}$.