Such manifolds are precisely the Riemannian ones and their “opposites”.

Suppose $M$ is a pseudo-Riemannian manifold whose signature is not trivial in one direction or the other. We work in a neighbourhood of a point and will define a vector field with arbitrarily small support, so we may as well consider $M=\mathbb R^d$. Suppose also, after a linear change of coordinates, that the metric at zero is $dx_1^2+\cdots+dx_k^2-dx_{k+1}^2-\cdots-dx_d^2$. By hypothesis $0<k<d$.

For all x sufficiently close to 0, define $X(x)$ as the only vector inbetween $\epsilon_1:=(1,0,\ldots,0)$ and $2\epsilon_d:=(0,\ldots,0,2)$ of norm 0. If you accept for a moment that this is a well-defined smooth vector field, then a suitable multiple of $X$ will have very small support, hence be defined everywhere upon extending by zero, but will be precisely $(2/3,\ldots,2/3)$ at zero.

The fact that it is smooth comes from the inverse function theorem. We are looking for $(1-t,0,\ldots,0,2t)$ where $t$ is solution to
$$ (1-t)^2g_x(\epsilon_1,\epsilon_1) + 4t(1-t)g_x(\epsilon_1,\epsilon_d) + 4t^2g_x(\epsilon_d,\epsilon_d) = 0. $$
Single roots of a polynomial depend smoothly on the coefficients, so if we show that $t$ is a single root, then it will depend smoothly on the metric. But this is an open condition, and at $x=0$ we have
$$ (1-t)^2g_0(\epsilon_1,\epsilon_1) + 4t(1-t)g_0(\epsilon_1,\epsilon_d) + 4t^2g_0(\epsilon_d,\epsilon_d) = (1-t)^2 - 4t^2 = -(3t-1)(t+1) $$
so the root $1/3$ is simple.