What makes a superconductor topological?
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.
A prototypical example of an intrinsic topological superconductor is the so-called $p$-wave superconductor [more details there: What is a $p_x + i p_y$ superconductor? Relation to topological superconductors, also, Meng-Cheng wrote the spinless $p$-wave model in 2D somewhere else on this page, and comment it carefully]. You can also induce topological non-trivial situation in $d$-wave superconductors, since the essential ingredient is the change of sign of the gap. All Cooper-pair based condensate would exhibit a change of sign in the momentum representation of the gap, except the $s$-wave case. The main problem is to transfer this momentum gap-closure into a spatial one.
Unfortunately, there is no known example of $p$-wave superconductors in nature. $p$-wave superfluids exist, and recent experiments aim at demonstrating the Majorana physics there.
Nevertheless, Gor'kov and Rashba in Phys. Rev. Lett. 87, 37004 (2001) shown that a conventional superconductor ($s$-wave) with spin-orbit interaction would lead to a mixture of both $s$- and $p$-wave correlations (*). Carefully selecting the spinless $p$-wave structure by means of a Zeeman effect may make the Majorana physics emerging, hence the proposals by a few peoples, see e.g.
R. M. Lutchyn, J. D. Sau, and S. Das Sarma, Phys. Rev. Lett. 105, 077001 (2010) or arXiv:1002.4033
Y. Oreg, G. Refael, and F. von Oppen, Phys. Rev. Lett. 105, 177002 (2010) or arXiv:1003.1145
associating $s$-wave superconductors in the proximity with spin-orbit systems under strong exchange interaction. Such a proposal is actually under experimental exploration in several groups around the world.
- (*): Note that there are a lot of papers by Edelstein studying similar effects all along the 80's and 90's but these papers are not as clear as the one by Gor'kov and Rashba to my taste
In order to maintain the gap -- an essential ingredient in the topological business as you already know from topological insulator -- it seems preferable to be in proximity, since bulk systems are not perfectly understood yet (role of impurities, exact gap-symmetry, multiple phase-transitions between different gap-symmetries, ... are still under debate, and pretty difficult to answer experimentally) and might well be less robust. About proximity-induced stable topological system in nano-wires, see e.g.
- A. C. Potter and P. A. Lee, Phys. Rev. B 83, 184520 (2011) or arXiv:1103.2129 and references therein,
but clearly the subject of proximity and/or bulk is still vivid. In addition, there are a lot of different proposals to realise Majorana physics now, as e.g. spatially organised ferromagnetic macro-molecules on top of a superconductor, quantum-dots arrays, ... I do not enter into much details. My understanding about all these proposals is that they try to reproduce the same toy-model Hamiltonian as discussed in the above cited papers (and kindly wrote by @MengCheng in her answer somewhere else on this page). For an pedagogical review about toy-model of Majorana wires, please see J. Alicea, Y. Oreg, G. Refael, F. von Oppen, and M. P. A. Fisher, Nat. Phys. 7, 412 (2011) or arXiv:1006.4395
Do not hesitate to ask further questions in this or separate post.
I'd like to point out a different sense in which superconductors are topological, which was hinted at by Meng Cheng in his comment and also discussed in the reference he provided. The above discussions all primarily focused on superconductors where the fluctuations of the electromagnetic gauge field can be ignored. In this case, it is appropriate to use the BdG framework and the topological superconductors are examples of topological phases $\it{without}$ topological order i.e., there are no fractionalized excitations (or anyons) in the system.
However, it was pointed out quite some time ago in
https://arxiv.org/abs/cond-mat/0404327
that if the dynamics of the gauge field are taken into account, then even an $s$-wave superconductor is topologically ordered and has anyonic excitations that braid non-trivially with each other. Indeed, they showed that in 2+1d, an $s$-wave superconductor has $\mathbb{Z}_2$ topological order, same as the Toric Code. A related paper extended this to other ($d$-wave etc) superconductors and also discussed symmetries: https://arxiv.org/abs/1606.03462
I'm not certain how realistic these proposals are since they seem to require confining electromagnetism to 2 spatial dimensions, but in principle it's been known since Hansson et al. that superconductors are intrinsically topological.