Taking this as my original question: Do we know if there exists a RHS and an initial (unlucky) guess that will require $\Theta(\sqrt{\kappa})$
steps?
The answer to the question is "no". The idea of this answer comes from the comment from Guido Kanschat.
Claim: For any given condition number $k$, there exists a matrix $A$, with that condition number for which the CG algorithm will terminate in at most two steps (for any given RHS and initial guess).
Consider $A\in \mathbb{R}^{n\times n} $ where $A=\mathrm{diag}(1,\kappa,\kappa,\ldots, \kappa)$. Then the condition number of $A$ is $\kappa$. Let $b\in \mathbb{R}^n$ be the RHS, and denote the eigenvalues of $A$ as $\lambda_i$ where
$$\lambda_i = \left\{\begin{array}{ll}1 & i=1\\
\kappa & i\not= 1
\end{array} \right. .
$$
We first consider the case where $x^{(0)} \in \mathbb{R}^n$, the initial guess, is zero. Denote $x^{(2)}\in \mathbb{R}^n$ as the second estimate of $A^{-1}b$ from the CG algorithm. We show that $x^{(2)} =A^{-1}b$ by showing $\langle x^{(2)}-A^{-1}b, A(x^{(2)}-A^{-1}b)\rangle =0$. Indeed, we have
\begin{align*}
\langle x^{(2)}-A^{-1}b, A(x^{(2)}-A^{-1}b)\rangle &=
\left\| x^{(2)}-A^{-1}b \right\|_A^2 \\
&=\min_{p\in \mathrm{poly}_{1} } \left\| (p(A)-A^{-1}) b \right\|_A^2\\
&=\min_{p\in \mathrm{poly}_{1} } \sum_{i=1}^n (p(\lambda_i) - \lambda_i^{-1})^2 \lambda_i b_i^2 \\
&\le \sum_{i=1}^n (\widehat{p}(\lambda_i) - \lambda_i^{-1})^2 \lambda_i b_i^2
= 0
\end{align*}
Where we use the first order polynomial $\widehat{p}$ defined as $\widehat{p}(x)= (1+\kappa-x)/\kappa$. So we proven the case for $x^{(0)}= 0$.
If $x^{(0)} \not = 0$, then $x^{(2)}= \overline{x^{(2)}}+ x^{(0)}$ where $\overline{x^{(2)} }$ is the second estimate of the CG algorithm with $b$ replaced with $\overline{b} = b-A x^{(0)}$. So we have reduced this case to the previous one.