PostgreSQL is working only for couple hour and connection faild. Where is the problem?

Question

I have installed PostgreSQL on my server. Everything works fine for a few hours, but after some time, I can't connect to the PostgreSQL server until I restart the PostgreSQL service. Then it works again for a few hours, and then it stops working again. Please help me

I notice some intresting:

Before the breakdown:

root@db:/etc/postgresql/15/main# netstat -nlp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      127977/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      127977/postgres
unix  2      [ ACC ]     STREAM     LISTENING     394005186 127977/postgres      /var/run/postgresql/.s.PGSQL.5432

root@db:/var/run/postgresql# ps -ef | grep postgres
postgres   13470       1  0 Aug20 ?        00:01:26 /tmp/kinsing
postgres   13665       1 68 Aug20 ?        2-15:24:24 /tmp/kdevtmpfsi
postgres  127977       1  0 20:07 ?        00:00:00 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf
postgres  127978  127977  0 20:07 ?        00:00:00 postgres: 15/main: checkpointer
postgres  127979  127977  0 20:07 ?        00:00:00 postgres: 15/main: background writer
postgres  127981  127977  0 20:07 ?        00:00:00 postgres: 15/main: walwriter
postgres  127982  127977  0 20:07 ?        00:00:00 postgres: 15/main: autovacuum launcher
postgres  127983  127977  0 20:07 ?        00:00:00 postgres: 15/main: logical replication launcher
postgres  128021  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36100) idle
postgres  128024  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36124) idle
postgres  128025  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36130) idle
postgres  128026  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36140) idle
postgres  128029  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36150) idle
postgres  128030  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36156) idle
postgres  128031  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36158) idle
postgres  128032  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36160) idle
postgres  128033  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36172) idle
postgres  128034  127977  0 20:08 ?        00:00:00 postgres: 15/main: postgres solomon 178.255.41.43(36174) idle
root      128096  127492  0 20:09 pts/2    00:00:00 grep --color=auto postgres

-solomon is my little java app writen in spring

After the breakdown:

root@db:/etc/postgresql/15/main# service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2023-08-20 20:32:14 CEST; 3 days ago
    Process: 8201 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 8201 (code=exited, status=0/SUCCESS)

Aug 20 20:32:14 db.pl systemd[1]: Starting PostgreSQL RDBMS...
Aug 20 20:32:14 db.pl systemd[1]: Finished PostgreSQL RDBMS.
root@db:/etc/postgresql/15/main# man postgres
root@db:/etc/postgresql/15/main#
root@db:/etc/postgresql/15/main# sudo find /tmp/ -name .s.PGSQL.5432
root@db:/etc/postgresql/15/main# /tmp/.s.PGSQL.5432
-bash: /tmp/.s.PGSQL.5432: No such file or directory
root@db:/etc/postgresql/15/main# service postgresql restart
root@db:/etc/postgresql/15/main# /tmp/.s.PGSQL.5432
-bash: /tmp/.s.PGSQL.5432: No such file or directory
root@db:/etc/postgresql/15/main# /tmp/.s.PGSQL.5432
-bash: /tmp/.s.PGSQL.5432: No such file or directory

root@db:/etc/postgresql/15/main# netstat -nlp | grep 5432
root@db:/etc/postgresql/15/main# netstat  | grep 5432

root@db:/etc/postgresql/15/main# ps -ef | grep postgres
postgres   13470       1  0 Aug20 ?        00:01:26 /tmp/kinsing
postgres   13665       1 68 Aug20 ?        2-15:21:01 /tmp/kdevtmpfsi

My configuration:

root@db:/etc/postgresql/15/main# cat pg_hba.conf
# PostgreSQL Client Authentication Configuration File


# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               md5

edit: I check logs cat /var/log/postgresql/postgresql-15-main.log


2023-08-24 22:24:15.460 CEST [128219] postgres@solomon LOG:  could not receive data from client: Connection timed out
2023-08-24 22:24:15.468 CEST [128217] postgres@postgres LOG:  could not receive data from client: Connection timed out
2023-08-24 23:04:07.509 CEST [129897] postgres@solomon LOG:  could not receive data from client: Connection timed out
2023-08-25 00:15:07.360 CEST [133297] postgres@postgres LOG:  could not receive data from client: Connection timed out
2023-08-25 00:15:07.366 CEST [133293] postgres@solomon LOG:  could not receive data from client: Connection timed out
2023-08-25 01:51:55.992 CEST [137133] postgres@bbbbbbb FATAL:  expected SASL response, got message type 0
2023-08-25 02:15:07.907 CEST [127977] LOG:  received smart shutdown request
2023-08-25 02:15:07.920 CEST [137144] postgres@solomon FATAL:  terminating connection due to administrator command
2023-08-25 02:15:07.923 CEST [137145] postgres@solomon FATAL:  terminating connection due to administrator command
2023-08-25 02:15:07.960 CEST [127977] LOG:  background worker "logical replication launcher" (PID 127983) exited with exit code 1
2023-08-25 02:15:07.961 CEST [127978] LOG:  shutting down
2023-08-25 02:15:07.979 CEST [127978] LOG:  checkpoint starting: shutdown immediate
2023-08-25 02:15:08.019 CEST [127978] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.059 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB
2023-08-25 02:15:08.028 CEST [127977] LOG:  database system is shut down
2023-08-25 19:42:11.761 CEST [156325] LOG:  starting PostgreSQL 15.4 (Ubuntu 15.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
2023-08-25 19:42:11.772 CEST [156325] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-08-25 19:42:11.772 CEST [156325] LOG:  listening on IPv6 address "::", port 5432
2023-08-25 19:42:11.787 CEST [156325] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-08-25 19:42:11.876 CEST [156328] LOG:  database system was shut down at 2023-08-25 02:15:08 CEST
2023-08-25 19:42:11.965 CEST [156325] LOG:  database system is ready to accept connections
2023-08-25 19:47:11.960 CEST [156326] LOG:  checkpoint starting: time
2023-08-25 19:47:12.118 CEST [156326] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.055 s, sync=0.033 s, total=0.159 s; sync files=2, longest=0.022 s, average=0.017 s; distance=0 kB, estimate=0 kB
root@db:~#

edit2: syslog at 2 at night


Aug 25 02:15:04 db crontab[137940]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137942]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137943]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137945]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137946]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137948]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137949]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137951]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137952]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137954]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137955]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137957]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137958]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137960]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137961]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137963]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137964]: (postgres) LIST (postgres)
Aug 25 02:15:04 db crontab[137966]: (postgres) REPLACE (postgres)
Aug 25 02:15:04 db crontab[137967]: (postgres) LIST (postgres)
Aug 25 02:15:05 db crontab[137969]: (postgres) REPLACE (postgres)
Aug 25 02:15:05 db crontab[137970]: (postgres) LIST (postgres)
Aug 25 02:15:05 db crontab[137972]: (postgres) REPLACE (postgres)
Aug 25 02:15:05 db crontab[137973]: (postgres) LIST (postgres)
Aug 25 02:15:05 db crontab[137975]: (postgres) REPLACE (postgres)
Aug 25 02:15:12 db postgresql@15-main[138441]: Cluster is not running.
Aug 25 02:15:12 db systemd[1]: postgresql@15-main.service: Control process exited, code=exited, status=2/INVALIDARGUMENT
Aug 25 02:15:12 db systemd[1]: postgresql@15-main.service: Failed with result 'exit-code'.
Aug 25 02:15:24 db crontab[139385]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139387]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139388]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139390]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139391]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139394]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139396]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139397]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139399]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139400]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139402]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139403]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139405]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139406]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139408]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139409]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139411]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139412]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139414]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139415]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139417]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139418]: (postgres) LIST (postgres)
Aug 25 02:15:24 db crontab[139420]: (postgres) REPLACE (postgres)
Aug 25 02:15:24 db crontab[139421]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139423]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139424]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139426]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139427]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139429]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139430]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139432]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139433]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139435]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139436]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139438]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139439]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139441]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139442]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139444]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139445]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139447]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139448]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139450]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139451]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139453]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139454]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139456]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139457]: (postgres) LIST (postgres)
Aug 25 02:15:25 db crontab[139459]: (postgres) REPLACE (postgres)
Aug 25 02:15:25 db crontab[139460]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139462]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139463]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139465]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139466]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139468]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139469]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139471]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139472]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139474]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139475]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139477]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139478]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139480]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139481]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139483]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139484]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139486]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139487]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139489]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139490]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139492]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139493]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139495]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139496]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139498]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139499]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139501]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139502]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139504]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139505]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139507]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139508]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139510]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139511]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139513]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139514]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139516]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139517]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139519]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139520]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139522]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139523]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139525]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139526]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139528]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139529]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139531]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139532]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139534]: (postgres) REPLACE (postgres)
Aug 25 02:15:26 db crontab[139535]: (postgres) LIST (postgres)
Aug 25 02:15:26 db crontab[139537]: (postgres) REPLACE (postgres)
Aug 25 02:16:01 db cron[128]: (postgres) RELOAD (crontabs/postgres)

I tried to rreinstal ubuntu and use this tutorial to set up petgree: https://www.cherryservers.com/blog/how-to-install-and-setup-postgresql-server-on-ubuntu-20-04 I try to change sha to md5 I try to change pool in java app to max 2

After it fails please check the last few lines of postgresql server log (probably `/var/log/postgresql/postgresql-15-main.log`) and system log (`/var/log/syslog`). It is possible that there's some out of memory error. You might need to [tune memory usage](https://www.enterprisedb.com/postgres-tutorials/how-tune-postgresql-memory) for your computer or virtual machine limits. — Tometzky, Aug 25 '23 at 18:31
Thank You @Tometzky for your suggestion. I add log to my question. — Matt Hol, Aug 25 '23 at 19:30
So the db server process received an external shutdown request at `2023-08-25 02:15:07.907 CEST` and it complied. So please check `/var/log/syslog` for about this time. There might be some hint about what sent the request. — Tometzky, Aug 25 '23 at 20:19
Cryptomining hackers. They almost certainly got in by guessing a crappy password you have on the superuser account. Rebuild the system, and use a good password this time. It also wouldn't hurt to not allow any random people from the internet to attempt to connect as the superuser. — jjanes, Aug 25 '23 at 21:02
I'm add syslog abaout 02.15 :/ at this time there was process from crontab that do something — Matt Hol, Aug 26 '23 at 08:04
@Matt Hol: Haven't you read the linked duplicate answer? `kdevtmpfsi` is a crypto miner malware. You have been hacked and need to setup your system from scratch. — Codo, Aug 26 '23 at 08:10
Of course, I've read that response suggesting it might be hackers. Honestly, I hope that's true. There's nothing of value in the database that can be stolen. It's a test database created for fun. However, verifying whether it's hackers will take me some time because I need to completely reset the system. I don't want to leave any questions unanswered. I'm investigating every lead that talented programmers are providing me. Thank you to everyone who wants to help me. — Matt Hol, Aug 26 '23 at 09:09

PostgreSQL is working only for couple hour and connection faild. Where is the problem?

0 Answers0