Issue
i checked so many other questions but none have this specific problem.
i have a NodeJs Express Postgresql app deployed on Digital Ocean, Ubuntu 22.04 x64, Droplet. the app is running on port 8080.
I used nginx and pm2 and postgresql version 14.
at first everything works normally but sometimes it randomly gives me the ECONNREFUSED error (shows up on pm2 logs) and i can't connect to the database anymore (probably worth mentioning that it happens especially after a another person accesses the site).
i followed this tutorial to set up nginx : https://dev.to/logrocket/how-to-run-a-node-js-server-with-nginx-588
and this tutorial for seeting up postgresql on the server : https://www.youtube.com/watch?v=zujeb8VWncI&t=80s
this is nginx config file
server {
index index.html index.htm index.nginx-debian.html;
listen 0.0.0.0:80;
server_name 165.257.202.2;
location / {
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header Host $http_host;
proxy_set_header X-NginX-Proxy true;
proxy_pass http://165.257.202.2:8080/;
proxy_redirect off;
}
location /api/registration {
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header Host $http_host;
proxy_set_header X-NginX-Proxy true;
proxy_pass http://165.257.202.2:8080/api/registration;
proxy_redirect off;
}
}
i only changed listen addresses in postgres.conf
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
and i added this in pg_hba.conf (at first it was just the first one but when i couldn't solve it i added the rest)
host all all all trust
host all all 0.0.0.0/0 md5
host all all ::/0 md5
this is the line it writes on pm2 logs
| connect ECONNREFUSED 165.257.202.2:5432
command service postgresql status says psotgresql is active
command ufw status verbose says 'inactive'
command netstat -anp | grep postgres outputs this :
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 14262/postgres
tcp6 0 0 :::5432 :::* LISTEN 14262/postgres
udp6 0 0 ::1:56997 ::1:56997 ESTABLISHED 14262/postgres
unix 2 [ ACC ] STREAM LISTENING 179264 14262/postgres /var/run/postgresql/.s.PGSQL.5432
it's Important to say the issue gets solved when i restart postgresql and pm2, but after a while the issue happens again (probably after a user accesses the website which uses this backend)
Would really appreciate any help i'm getting a bit desprate with this issue
EDIT :
this is the error that i found in the log file which is repeated many times
2022-09-25 16:30:17.966 UTC [28724] LOG: database system is ready to accept connections
2022-09-25 17:34:04.468 UTC [29907] [unknown]@[unknown] FATAL: unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-25 17:34:04.618 UTC [29908] [unknown]@[unknown] FATAL: unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-25 17:34:04.782 UTC [29909] [unknown]@[unknown] FATAL: no PostgreSQL user name specified in startup packet
2022-09-25 17:34:05.038 UTC [29915] cms@cms FATAL: role "cms" does not exist
2022-09-25 17:34:05.042 UTC [29913] etl@etl FATAL: role "etl" does not exist
2022-09-25 17:34:05.045 UTC [29916] admin@admin FATAL: role "admin" does not exist
2022-09-25 17:34:05.095 UTC [29922] backup@backup FATAL: role "backup" does not exist
2022-09-25 17:34:05.118 UTC [29928] admin@admin FATAL: role "admin" does not exist
2022-09-25 17:34:05.125 UTC [29930] crm@crm FATAL: role "crm" does not exist
2022-09-25 17:34:05.129 UTC [29931] django@django FATAL: role "django" does not exist
2022-09-25 17:34:05.149 UTC [29938] app@app FATAL: role "app" does not exist
2022-09-25 17:34:05.174 UTC [29919] postgres@postgres LOG: could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.176 UTC [29918] postgres@postgres LOG: could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.178 UTC [29914] postgres@postgres LOG: could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.185 UTC [29917] postgres@postgres LOG: could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.187 UTC [29912] postgres@postgres LOG: could not receive data from client: Connection reset by peer
then it continues with the last line like that until i restart postgresql! i did not create any user on postgresql or on the server. i did everything with root.
Another server (droplet) postgresql log file with the same problem shows this mostly :
2022-09-26 06:58:28.310 UTC [740] LOG: database system is ready to accept connections
2022-09-26 09:58:24.542 UTC [3402] [unknown]@[unknown] FATAL: unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 09:58:24.699 UTC [3403] [unknown]@[unknown] FATAL: unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 09:58:24.876 UTC [3404] [unknown]@[unknown] FATAL: no PostgreSQL user name specified in startup packet
2022-09-26 10:30:33.024 UTC [3784] [unknown]@[unknown] FATAL: unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 10:30:33.164 UTC [3785] [unknown]@[unknown] FATAL: unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 10:30:33.308 UTC [3786] [unknown]@[unknown] FATAL: no PostgreSQL user name specified in startup packet
2022-09-26 13:51:49.510 UTC [6934] [unknown]@[unknown] FATAL: unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 13:51:49.711 UTC [6935] [unknown]@[unknown] FATAL: unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 13:51:49.926 UTC [6936] [unknown]@[unknown] FATAL: no PostgreSQL user name specified in startup packet
2022-09-26 14:42:38.833 UTC [7997] [unknown]@[unknown] FATAL: unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 14:42:38.993 UTC [7998] [unknown]@[unknown] FATAL: unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 14:42:39.158 UTC [7999] [unknown]@[unknown] FATAL: no PostgreSQL user name specified in startup packet
2022-09-26 17:13:03.947 UTC [10100] [unknown]@[unknown] LOG: could not accept SSL connection: unsupported protocol
2022-09-26 17:13:03.947 UTC [10100] [unknown]@[unknown] HINT: This may indicate that the client does not support any SSL protocol version between TLSv1.2 and TLSv1.3.
2022-09-26 18:58:23.879 UTC [11540] [unknown]@[unknown] FATAL: unsupported frontend protocol 0.0: server supports 3.0 to 3.0
EDIT 2
found another very interesting thing : this happens to the memory of the server even though the database isn't that big !! enter image description here
Solution
thanks to @jjanes comments and this github post :
there was a process that was using the CPU at 100% capacity called "kdevtmpfsi", which turns out to be a mining malware.
i also found a cronjob to re-run it after i restart postgresql which explains how db "randomly" stops connecting.
i was able to solve the issue by changing postgres default password .
Answered By - junior_ali Answer Checked By - Mary Flores (WPSolving Volunteer)