In short, what makes a superconductor topological is the nontrivial band structure of the Bogoliubov quasiparticles. Generally one can classify non-interacting gapped fermion systems based on single-particle band structure (as well as symmetry), and the result is the so-called ten-fold way/periodic table. The topological superconductivity mentioned in the question is related to the class D, namely superconductors without any symmetries other than the particle-hole symmetry. The simplest example in 2D is a spinless $p_x+ip_y$ superconductor:
$H=\sum_k c_k^\dagger(\frac{k^2}{2m}-\mu)c_k+ \Delta c_k^\dagger(k_x+ik_y)c_{-k}^\dagger+\text{h.c.}=\sum_k (c_k^\dagger, c_{-k})\left[(k^2/2m-\mu)\tau_z+\Delta k_x\tau_x+\Delta k_y\tau_y\right]\begin{pmatrix}c_k\\ c_{-k}^\dagger\end{pmatrix}$
This Hamiltonian defines a map from the $k$ space (topologically a sphere $S^2$) to a $SU(2)$ matrix $m_k\cdot \sigma$ where $m_k\propto (\Delta k_x, \Delta k_y, \frac{k^2}{2m}-\mu)$ (then normalized), which also lives on a sphere. Therefore such maps are classified by $\pi_2(S^2)=\mathbb{Z}$. If two Hamiltonians belong to the same equivalence class in the homotopy group, it means that one can continuously deform the Hamiltonian from one to another without closing the gap, thus topologically indistinguishable.
The integer, called the Chern number $C$, that classifies the class D topological superconductors can be calculated from the Hamiltonian, and in this case it is $C=1$. This idea can be generalized to other symmetry classes and dimensions, basically one needs to understand the map from the momentum space to the appropriate single-particle "Hamiltonian" space (the general case is much more complicated than the $2\times 2$ Hamiltonian).
This toy model (and its one-dimensional descendants) is behind all recent proposals of realizing topological superconductors in solid state systems. The basic idea is to combine various mundane elements (semiconductors, s-wave superconductor, ferromagnet, etc): since electrons have spin-$1/2$, one needs to have Zeeman field to break the spin degeneracy and get a non-degenerate Fermi surface (thus effectively "spinless" fermions, really spin-polarized). However, in s-wave superconductors electrons with opposite spins are paired. This is why spin-orbit coupling is necessary since it makes the electron spin "winds" around on the Fermi surface, so that at $k$ and $-k$ electrons can pair up. Putting all these together one can realize a topological superconductor.
There are various physical consequences. The general feature is that something peculiar happens on the boundary between superconductors belonging to different topological classes. For example, if the $p_x+ip_y$ superconductor has an edge to the vacuum, there are gapless chiral Majorana fermions localized on the edge. Also if one puts a $hc/2e$ vortex into the superconductor, it traps a zero-energy Majorana bound state.
The question also mentioned cuprates. There are some speculations about the possibility of $d+id$ pairing in cuprates, probably motivated by measurement of Kerr rotations which is a signal of time-reversal symmetry breaking. However this is highly debatable and not very well accepted. Notice that $d+id$ superconductor is the $C=2$ case of the class D family.
To learn more about the subject I recommend the excellent review by Jason Alicea: http://arxiv.org/abs/1202.1293.