On the wikipedia page on the Balkema-de Haan-Pickands theorem, en.wikipedia.org/wiki/Pickands-Balkema-de_Haan_theorem, it is said the "for a large class of underlying distribution functions", their far-right values can be approximated by a generalized Pareto.
What is and what is not that "large class"?
Speaking informally, if centered, normalized maximum of $n \to \infty$ i.i.d. random variables sampled from your distribution is in the domain of attraction of Gumbel Type I EVD, corresponding residual life time $p(\frac{ \xi - u }{ a(u) } > x)$ for $u \to \infty$ converges to Generalized Pareto $(1 + \gamma x)^{-1/\gamma} \xrightarrow{\gamma \to 0} (1 - e^{-x})$.
E.g. if you have a sample of wagons aged $u=20$ (with their survival function being exponential), the chance that they serve $x=3$ more years is still exponential. Here $a(u)$ is a normalising function, called auxiliary function. All auxiliary functions are asymptotically equivalent as $u \to \infty$ by Khinchin's theorem. For instance, one can use $a(u) = \frac{1}{r(u)}$, the inverse of hazard rate $r(u) = \frac{F'(u)}{1 - F(u)}$ as auxiliary function.
If your distribution is in the domain of attraction of Inverse Weibull Type III EVD or Frechet Type II EVD, corresponding residual life time converges to "specialized" Generalized Pareto $(1 + x)^{-1/\gamma}$, also known as Lomax or Type II Pareto distribution.
E.g. if you have a sample of horses, aged 20, the chance that they survive $x=3$ more years is specialized Generalized Pareto-distributed. "Specialized", because it is not $(1 + \gamma x)^{-\frac{1}{\gamma}}$, it is just $(1 + x)^{-1/\gamma}$.
The difference is that survival function of horses has long fat tails (decaying as a polynomial hyperbola $1/x^\alpha$) and has a finite upper end point (all horses are dead by 40, chance of surviving past some point is exactly 0), maximum of horses survival function is in the domain of attraction of Inverse Weibull distribution (e.g. empirical estimators for humans always produce negative $\gamma$). In case of Frechet distribution the end point is infinite, while the tails are still fat (positive $\gamma$). At the same time the survival function of wagons has a light sub-exponential tail; survival functions in the domain of attraction of Gumbel distribution might have or have not an upper end point.
Centered, normalized maximum of most distributions converges to one of 3 types of EVD, those are the only max-stable distributions, i.e. such distributions that maximum of $n$ i.i.d. random variables, sampled from them, converges to themselves, this is Fisher-Tippett-Gnedenko theorem. The specific set of distributions is determined by application of necessary and sufficient conditions of convergence to EVD.
Some notable exceptions are geometric and Poisson distribution, they do not converge to any max-stable distribution due to discontinuity of their pdf at integer points (there is a criterion that requires that $\frac{S(x)}{S(x-)} \xrightarrow{x \to \infty} 1$ for a distribution to be in a domain of attraction of max-stable distribution), which is clearly not satisfied for these two, as survival function at integer point $S(x)$ is different from survival function at its left proximity $S(x-)$.
The standard requirement is that $F$ is in the domain of attraction of the GEV (generalized extreme value distribution). If F is in said domain of attraction, then the distribution of excesses over a high threshold (conditional on clearing that threshold) is well approximated by a distribution in the GP family.
"In the domain of attraction of the GEV" means that block maxima $M_n = \max_iX_i$, for some sequence of constants $a_n$, $b_n$, where $n$ refers to block size, the distribution of the standardized sample maximum doesn't degenerate.
$$ \lim\limits_{n\to\infty} \text{P}\left(\frac{M_n - b_n}{a_n} \leq x\right) = G(x) $$
If F is in the domain of attraction of a GEV, then $$ \lim\limits_{u\to\infty}\text{P}\left(X \geq u + y \mid x \geq u\right) = \lim\limits_{u\to\infty}\frac{1 - F(u + y)}{1 - F(u)} = H(y) $$
Where $H$ is generalized Pareto.
There are some helpful comments on this in a draft textbook by Smith and Weissman, available at https://rls.sites.oasis.unc.edu/s834-2020/s834.html.
Section 2 discusses domains of attraction and says:
In practice nearly all continuous distributions are in the domain of attraction of some extreme value limit.
Page 12 presents some counterexamples:
Continuous distributions not in any domain of attraction These are hard to construct but they do exist! Here are two examples: (a) Any distribution function with slowly varying tail, for instance $F(x) = 1 − 1/ \log x$ for $x > e$. (b) Consider $F_\delta(x) = 1-x^{-1}\{1 + \delta \sin(\log x)\}$ valid for $x \geq$ some $x_0$, with $|\delta|$ small enough to make it a valid distribution function.
The second example is credited to Resnick (1971) "Tail equivalence and its applications".
The question title mentions the log-normal distribution. Page 12 of the book has another example showing the log-normal is in the GPD domain of attraction.
Edit: the comments below refer to the contents of the wikipedia page when this answer was original written but it's since been updated to fix this issue
It might be worth pointing out that this involves a more general version of the Pickands theorem than is given on the wikipedia page currently, which says $\Pr(X-u \leq y | X>u)$ converges to a GPD (with constant scale $\sigma$) as $u \to \infty$. A fuller vesion is that $\Pr(\frac{X-u }{h(u)} \leq y | X>u)$ converges to a GPD for some scaling function $h(u)$. (The first pages of Tawn and Papastathopolous https://arxiv.org/abs/1111.6899 have a nice summary of this.) The example in the book requires $h(u)$ to be non-constant. So I suspect the log-normal only lies in the GPD domain of attraction for the general theorem, and not for the simplified wikipedia form.
