GR: Pseudo Riemannian or Riemannian?

In relativity (both special and general) one of the key quantities is the proper length given by:

$$ ds^2 = g_{\alpha\beta}dx^\alpha dx^\beta \tag{1} $$

where $g_{\alpha\beta}$ is the metric tensor. The physical significance of this is that if we have a small displacement in spacetime $(dx^0, dx^1, dx^2, dx^3)$ then $ds$ is the total distance moved. You can think of it as a spacetime equivalent of Pythagoras' theorem. The quantity $ds$ is an invariant i.e. all observers in any frame of reference will agree on the value of $ds$.

A metric is positive definite if $ds^2$ is always positive, and Riemannian manifolds have a metric that is positive definite.

However in relativity $ds^2$ can be positive, zero or negative, which correspond to timelike, lightlike and spacelike intervals respectively. It is because $ds^2$ can have different signs that manifolds in GR are not Riemannian but only pseudo-Riemannian.

Lorentzian manifolds are a special case of pseudo-Riemannian manifolds where the signature of the metric is $(3,1)$ (or $(1,3)$ depending on your sign convention).

If we take the metric tensor that corresponds to special relativity (i.e. flat spacetime) equation (1) becomes:

$$ ds^2 = -dt^2 + dx^2 + dy^2 + dz^2 $$

That minus sign on the $dt^2$ term means $ds^2$ can be negative as well as positive, so the manifold is pseudo-Riemannian, and the one negative and three positive signs on the right hand side make the signature $(3,1)$ so the manifold is Lorentzian.

A pseudo Riemannian manifold is a manifold equiped with a metric of signature $(p,q)$, $p$ indicating the number of positive eigenvalues and $q$ the negative eigenvalues. For a Riemannian manifold, $q = 0$.

A spacetime of dimension $n$ is defined by a pseudo-Riemannian manifold of signature $(1, n-1)$, or alternatively $(n-1,1)$, also called a Lorentzian manifold.

John has already given a nice intuitive answer, I'd like to write it in a bit more mathematical way. First of all metric means a bilinear defined over a product (vector) space: $g(v,w): V \times V \rightarrow \mathbb{R}$. Where the vectors $v,w$ lie in the vector space $V$. This bilinear should be smooth, symmetric $g(v,w) = g(w,v)$, and positive definite , which means for any $v,w$ it should be positive. This allows one to measure length of the vectors in $V$. And now if we weaken the condition of positive definiteness to 'non-degenrate' then we call it a pseudo-Riemannian metric. Non-degenerate means $g(v,w)=0, \forall w \in V \Rightarrow v = 0$.

The reason why in physics we usually don't go into such language is the following fact: metric is a bilinear so you can always construct the $g(v,w)$ for any pair of vectors if you know what $g(e^\mu,e^\nu)$ are, where $\{ e^\mu \}$ is a basis of the space $V$. This is nothing but the usual $g^{\mu \nu}$. For instance, take $\mathbb{R}^2$ as our $V$, you can define a metric $g^{\mu\nu} = \text{diag}(1,1)$, which is a valid (Riemannian) metric and means nothing but $g(\hat{x}, \hat{x}) = 1 = g(\hat{y}, \hat{y})$. You can check with such a metric (as John pointed out) you will always get $ds^2 > 0$.

If we try to think all these at the level of matrix $g^{\mu \nu}$ it is important to note (Gram-Schmidt theorem) at least locally one can always find a suitable orthonormal basis such that $$g^{\mu \nu} = \eta^{\mu \nu} = \text{diag} \big(-1, \cdots (s \,\, \text{times}), +1, \cdots (r \,\, \text{times}) \big) $$ Hence Riemannian metric would mean $s=0$ (because we want positive definite) and pseduo-Riemannian metric will have $s \neq 0$. In fact the 'signature' $(r,s)$ is always sufficient to tell apart between different metrics, no matter what basis you choose (Sylvester's law of inertia). For example metric which have a signature $(\text{dim}(V)-1,1)$ are called Lorentzian metrics. (Some people might exchange $r$ and $s$).

As of yet I've not talked about manifolds, be it Riemannian or pseudo-Riemannian. We can endow a 'metric structure' to a manifold also. The vector space over which we define the metric is $T_pM$, the tangent space of manifold $M$ at point $p$ on it. In fact we usually define it over the co-tangent space $T^{^*}_pM$ (which has the basis $\{dx^\mu\}$) and that's why we write it as $ds^2 = g_{\mu \nu} dx^\mu dx^\nu = g(dx^\mu, dx^\nu)$. So if you can write a (pseudo-) Riemannian metric on $T_pM$ then $M$ is said to be a (pseudo-) Riemannian manifold.

Hope it might be clear that in special relativity we restrict ourselves only to Lorentzian metrics, but in general relativity we consider all possible pseudo-Riemannian metrics (such as an anti-de Sitter metric has $s=2$).