ssh to DB server and su normal users very slow :

update : the ssh -v root@db_Server_IP from my Windows cmd pasted
below for more details

On Mon, Sep 9, 2024 at 4:50 PM KK CHN <kkchn.in@gmail.com> wrote:

List,

I have configured pgbackrest for the DB server and Repo Server(created the
stanza info check all fine. in these machines.

/var/spool/pgbackrest shows the .Ok for each WAL and the Repo
server receiving the archiving of WAL in the archive directory .

I didn't schedule a pgbackrest back as of now due to an issue I am
facing as follows.

PROBLEM Statement:

I am facing a delay in ssh root@dbserver_IP from my Desktop PC. More
than a minute to login to the root console from any remote terminal, but
this issue was not there all these days.

I have done two changes in the DB server :-

1.
pg_hba.conf entry

ie; changed the entry #local all all trust

To
#local all all md5

It already having replication entry as
local replication all peer

2.
Added a .pgpass entry in the DB user's ~/dir/ with the following

[root@db1 ~]# cat /var/lib/edb/.pgpass
*:*:*:enterprisedb:password

Is this causing login delays ? Local connection asking
password(pg_hba.conf entry ) and .pgpass contain the user and password
for connecting ?

3. Even if I able to login to the DB server from the Remote Repo server
after a minute or two, in side the DB server doing a # su
enterprisedb taking around 90 to 120 seconds to change the user as
enterprisedb user ??

Any hints much appreciated ..

Thanks in advance,
Krishane

*For more details I am pasting the top output ( vCPU 16 nos , RAM
16 GB)*

top - 10:11:43 up 5 days, 17:21, 5 users, load average: 0.97, 1.38, 1.26
Tasks: 710 total, 1 running, 708 sleeping, 1 stopped, 0 zombie
%Cpu(s): 1.3 us, 0.6 sy, 0.0 ni, 97.1 id, 0.6 wa, 0.1 hi, 0.3 si,
0.0 st
MiB Mem : 15733.6 total, 664.0 free, 6371.1 used, 13237.6 buff/cache
MiB Swap: 8060.0 total, 7985.1 free, 74.9 used. 9362.4 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND
3547252 enterpr+ 20 0 4656880 262304 252032 S 8.3 1.6 0:01.97
edb-postgres
2588 enterpr+ 20 0 4622104 12704 10888 S 2.0 0.1 106:10.00
edb-postgres
3554955 enterpr+ 20 0 4661692 632052 621364 S 2.0 3.9 0:00.99
edb-postgres
3555894 enterpr+ 20 0 4633432 628388 621056 S 1.3 3.9 0:00.26
edb-postgres
3525520 enterpr+ 20 0 4658888 555596 543872 S 1.0 3.4 0:10.82
edb-postgres
3546456 enterpr+ 20 0 4627288 40852 38016 S 1.0 0.3 0:00.30
edb-postgres
3554919 enterpr+ 20 0 4655376 564024 557020 S 1.0 3.5 0:00.30
edb-postgres
3555796 enterpr+ 20 0 4635024 565716 556840 S 1.0 3.5 0:00.22
edb-postgres
3556084 enterpr+ 20 0 4653424 59156 51968 S 1.0 0.4 0:00.06
edb-postgres
3525597 enterpr+ 20 0 4627444 44052 41088 S 0.7 0.3 0:00.47
edb-postgres
377 root 0 -20 0 0 0 I 0.3 0.0 2:43.11
kworker/5:1H-kblockd
2923344 enterpr+ 20 0 4625236 225176 223104 S 0.3 1.4 1:23.93
edb-postgres
3525722 enterpr+ 20 0 4627328 99220 96128 S 0.3 0.6 0:01.99
edb-postgres
3555151 root 20 0 226580 4864 3456 R 0.3 0.0 0:00.15 top
3555807 enterpr+ 20 0 4627444 350228 347136 S 0.3 2.2 0:00.03
edb-postgres
3556023 enterpr+ 20 0 4653636 60052 52608 S 0.3 0.4 0:00.15
edb-postgres
3556026 enterpr+ 20 0 4653424 59796 52608 S 0.3 0.4 0:00.22
edb-postgres
3556074 enterpr+ 20 0 4653448 59540 52224 S 0.3 0.4 0:00.11
edb-postgres
3556075 enterpr+ 20 0 4653372 59412 52224 S 0.3 0.4 0:00.18
edb-postgres

and # ps -ax shows
[root@db1 ~]# ps -ax |grep "idle"

3511515 ? I 0:00 [kworker/5:0-inet_frag_wq]
3512991 ? Ss 0:00 postgres: enterprisedb postgres
10.21.134.205(56754) idle
3513003 ? Ss 0:00 postgres: enterprisedb er_db
10.21.13.205(56770) idle
3513005 ? Ss 0:00 postgres: enterprisedb tp_db
10.21.13.205(56772) idle
3513267 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(39262) idle
3513476 ? Ss 0:00 postgres: enterprisedb er_db
10.21.13.205(56839) idle
3513704 ? Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(56608) idle
3513729 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(44926) idle
3514113 ? Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(53743) idle
3514374 ? Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(58623) idle
3514397 pts/1 T 0:00 top
3515012 ? Ss 0:00 postgres: enterprisedb mt_db
10.21.13.202(58686) idle
3515088 ? Ss 0:00 postgres: enterprisedb mgt_db
10.21.13.202(58586) idle
3515942 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(64844) idle
3515987 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(27190) idle
3516230 ? Ss 0:00 postgres: enterprisedb postgres
10.21.13.202(60354) idle
3516655 ? Ss 0:00 postgres: enterprisedb er_db
10.21.13.205(57348) idle
3519209 ? Ss 0:00 postgres: enterprisedb g_db
10.21.13.202(59611) idle
3519948 ? Ss 0:00 postgres: enterprisedb postgres
10.21.13.204(58224) idle
3520004 ? Ss 0:00 postgres: enterprisedb er_db
10.21.13.204(58239) idle
3521990 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(39000) idle
3525800 ? Ss 0:00 postgres: enterprisedb er_db
10.21.13.204(58606) idle
3525914 ? Ss 0:00 postgres: enterprisedb er_db
10.21.13.204(59004) idle
3528403 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(44250) idle
3532345 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(64230) idle
3533852 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(34514) idle
3533969 ? Ss 0:00 postgres: enterprisedb g_db
10.21.13.202(58464) idle

3354541 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(62640) idle
3356744 ? I 0:00 [kworker/3:2]
3365496 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(47376) idle
3367163 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(48814) idle
3368555 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(56520) idle
3370980 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(19930) idle
3371824 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(36006) idle
3372285 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(37632) idle
3373166 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(36862) idle
3379317 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(17632) idle
3385442 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(21096) idle
3385790 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(47792) idle
3389859 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(56094) idle
3397876 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(32436) idle
3404172 ? Ss 0:00 postgres: enterprisedb ad_db
10.23.0.206(37122) idle
3404779 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(62946) idle
3405628 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(37568) idle
3406414 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(50994) idle
3408051 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(14956) idle
3410935 ? I 0:00 [kworker/7:2-events]
3432820 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(31348) idle
3434126 ? Ss 0:01 postgres: enterprisedb ad_db
10.23.0.206(48372) idle
3436428 ? Ss 0:00 postgres: enterprisedb mt_db
10.23.0.207(36930) idle
3443063 ? Ss 0:00 postgres: enterprisedb er_db
10.23.0.203(57624) idle

########################################################################

Verbose out for ssh -v root@dbserver_ip from my Windows Desk

PS C:\Users\Krishane> ssh -v root@10.23.0.202
OpenSSH_for_Windows_8.6p1, LibreSSL 3.4.3
debug1: Authenticator provider $SSH_SK_PROVIDER did not resolve; disabling
debug1: Connecting to 10.23.0.202 [10.23.0.202] port 22.
debug1: Connection established.
debug1: identity file C:\\Users\\Krishane/.ssh/id_rsa type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_rsa-cert type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_dsa type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_dsa-cert type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ecdsa type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ecdsa-cert type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ecdsa_sk type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ecdsa_sk-cert type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ed25519 type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ed25519-cert type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ed25519_sk type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_ed25519_sk-cert type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_xmss type -1
debug1: identity file C:\\Users\\Krishane/.ssh/id_xmss-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_for_Windows_8.6
debug1: Remote protocol version 2.0, remote software version OpenSSH_8.7
debug1: compat_banner: match: OpenSSH_8.7 pat OpenSSH* compat 0x04000000
debug1: Authenticating to 10.13.0.20:22 as 'root'
debug1: load_hostkeys: fopen C:\\Users\\Krishane/.ssh/known_hosts2: No such
file or directory
debug1: load_hostkeys: fopen __PROGRAMDATA__\\ssh/ssh_known_hosts: No such
file or directory
debug1: load_hostkeys: fopen __PROGRAMDATA__\\ssh/ssh_known_hosts2: No such
file or directory
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: algorithm: curve25519-sha256
debug1: kex: host key algorithm: ssh-ed25519
debug1: kex: server->client cipher: chacha20-poly1305@openssh.com MAC:
<implicit> compression: none
debug1: kex: client->server cipher: chacha20-poly1305@openssh.com MAC:
<implicit> compression: none
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: SSH2_MSG_KEX_ECDH_REPLY received
debug1: Server host key: ssh-ed25519
SHA256:WZmh3cHN7W3GZasu+d45CMNu8jANDQi4DXA52/xS940
debug1: load_hostkeys: fopen C:\\Users\\Krishane/.ssh/known_hosts2: No such
file or directory
debug1: load_hostkeys: fopen __PROGRAMDATA__\\ssh/ssh_known_hosts: No such
file or directory
debug1: load_hostkeys: fopen __PROGRAMDATA__\\ssh/ssh_known_hosts2: No such
file or directory
debug1: Host '10.23.0.202' is known and matches the ED25519 host key.
debug1: Found key in C:\\Users\\Krishane/.ssh/known_hosts:12
debug1: rekey out after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: rekey in after 134217728 blocks
debug1: pubkey_prepare: ssh_get_authentication_socket: No such file or
directory
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_rsa
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_dsa
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_ecdsa
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_ecdsa_sk
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_ed25519
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_ed25519_sk
debug1: Will attempt key: C:\\Users\\Krishane/.ssh/id_xmss
debug1: SSH2_MSG_EXT_INFO received
debug1: kex_input_ext_info: server-sig-algs=<ssh-ed25519,
sk-ssh-ed25519@openssh.com
,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,
sk-ecdsa-sha2-nistp256@openssh.com,
webauthn-sk-ecdsa-sha2-nistp256@openssh.com>
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue:
publickey,gssapi-keyex,gssapi-with-mic,password
debug1: Next authentication method: publickey
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_rsa
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_dsa
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_ecdsa
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_ecdsa_sk
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_ed25519
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_ed25519_sk
debug1: Trying private key: C:\\Users\\Krishane/.ssh/id_xmss
debug1: Next authentication method: password
root@10.23.0.202's password:
debug1: Authentication succeeded (password).
Authenticated to 10.23.0.202 ([10.23.0.202]:22).
debug1: channel 0: new [client-session]
debug1: Requesting no-more-sessions@openssh.com
debug1: Entering interactive session.
debug1: pledge: filesystem full
debug1: ENABLE_VIRTUAL_TERMINAL_INPUT is supported. Reading the VTSequence
from console
debug1: ENABLE_VIRTUAL_TERMINAL_PROCESSING is supported. Console supports
the ansi parsing // After this line it waits for 1 or 2 minutes and
then get logged in ..
debug1: client_input_global_request: rtype hostkeys-00@openssh.com
want_reply 0
debug1: client_input_hostkeys: searching
C:\\Users\\Krishane/.ssh/known_hosts for 10.23.0.202 / (none)
debug1: client_input_hostkeys: searching
C:\\Users\\Krishane/.ssh/known_hosts2 for 10.23.0.202 / (none)
debug1: client_input_hostkeys: hostkeys file
C:\\Users\\Krishane/.ssh/known_hosts2 does not exist
debug1: client_input_hostkeys: no new or deprecated keys from server
Activate the web console with: systemctl enable --now cockpit.socket

Register this system with Red Hat Insights: insights-client --register
Create an account or view all your systems at
https://red.ht/insights-dashboard
Last login: Mon Sep 9 17:33:06 2024 from 10.23.0.46
[root@db1 ~]#

On 9/9/24 16:18, KK CHN wrote:

update :  the      ssh -v root@db_Server_IP from my Windows cmd 
 pasted below for more details

Why is your windows cmd important here, show ssh debugging from your DB
host to the pgbackrest host.

Hi

What does the server side do in the wait time?

Cheers Thiemo