Search Postgresql Archives

Re: ssh to DB server and su normal users very slow :

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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.

On Mon, Sep 9, 2024 at 4:50 PM KK CHN <kkchn.in@xxxxxxxxx> 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@xxxxxxxxxxx MAC: <implicit> compression: none
debug1: kex: client->server cipher: chacha20-poly1305@xxxxxxxxxxx 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@xxxxxxxxxxx,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,sk-ecdsa-sha2-nistp256@xxxxxxxxxxx,webauthn-sk-ecdsa-sha2-nistp256@xxxxxxxxxxx>
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@xxxxxxxxxxx
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@xxxxxxxxxxx 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 ~]#
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux