In one-particle quantum mechanics
The equation
$$[i\partial_t-H]G(x,t; x',t')=\delta(t-t')\delta(x-x'),$$
is how a Green's function is defined mathematically. If the equation is solved with boundary condition $G(x,t;x',t')=0\text{ if } t-t'<0$, we obtain a retarded Green's function, which propagates a solution of the corresponding Schrödinger equation:
$$\psi(x,t) = \int dt' dx' G(x,t;x',t')\psi(x',t').\tag1$$
If we wish to work in a representation other than position representation, or in a general Hilbert space, we can indeed re-express the wave functions as bras and kets:
$$
\psi(x,t)=\langle x|\psi(t)\rangle\Rightarrow
\hat{G}(t,t')=\int dx\int dx'|x\rangle G(x,t;x',t')\langle x'|$$
$$\Rightarrow
|\psi(t)\rangle=\hat{G}(t,t')|\psi(t'\rangle.\tag2$$
This is identical to
$$
|\psi(t)\rangle=\hat{U}(t,t')|\psi(t')\rangle=e^{-iH(t-t')/\hbar}|\psi(t')\rangle,$$
in other words - one-particle retarded Green's function is just an evolution operator:
$$
G(x,t;x',t')=\langle x|e^{-iH(t-t')/\hbar}|x'\rangle\theta(t-t').
$$
In many-body theory
In many-body theory one would usually work in second quantization representation, where we have
$$
|\psi(x,t)\rangle=\hat{\psi}^\dagger(x,t)|\emptyset\rangle.
$$
This makes Eq. (2) look like Eq. (1) in the question, and the Green's function becomes identical with the Green's function of the one-particle Schrödinger equation, but one is actually working in Fock space here. But the interpretation is that of Eq. (2) - we are describing time evolution of a state in Hilbert space.
The alternative physical definition of a time-ordered Green's function in second quantization is
$$
G(x,t;x',t')=-\frac{i}{\hbar}\langle\emptyset |T[\psi(x,t)\psi^\dagger(x',t')]|\emptyset\rangle,$$
where $T$ stands for time ordering. In abesence of interactions this Green's function also satisfies the one-particle Schrödinger equation, but it satisfies different boundary condition at $t=t'$ - because it describes propagation not only of particles, but also holes/antiparticles "backward in time", i.e., also propagation of state $\psi(x,t)|\emptyset\rangle$, where particle is first removed from vacuum and then addad back at an earlier instant.
Thus, this Green's function is not literally equivalent to propagator, but is more convenient, since it possesses a closed perturbative expansion in terms of the Green's functions of the same type.
One often interprets this Green's function as adding a particle or whole at point x',t' and observing whether it propagated to point x,t. IMHO it is better interpreted as a response function or susceptibility, rather than literally as a propagator. Indeed, in the end Green's function is not literally intended to be an observable quantity, but facilitate calculation of observable quantities (like wave function or density matrix inn single-particle QM.)
In non-equilibrium theory (e.g., in Keldysh formalism) one often defines a related retarded function which is actually identical with that of the one-particle Schrödinger equation:
$$
G(x,t;x',t')=-\frac{i}{\hbar}\langle\emptyset |[\psi(x,t),\psi^\dagger(x',t')]_{\pm}|\emptyset\rangle\theta(t-t'),$$
where time ordering is replaced by a commutator and a theta function
(see, e.g., in Quantum field-theoretical methods in transport theory of metals by Rammer and Smith).
Doniach and Sondheimer's Green's functions for solid state physics uses retarded one-particle Green's functions to obtain many basic results in solid state physics - I strongly recommend this accessible book for understanding the link between propagators of Quantum mechanics and QFT.
