Error connecting to server : received invalid response to SSL negotiation

Question

I have installed PostgreSQL 9.3 on my pc. When I try to start the database by pgAdminIII, I am getting the error below.

Error connecting to server : received invalid response to SSL negotiation

I have set the port number to 5432 by editing the port number in the config file. But, when I was installing postgresql , and I entered 5432 as port number, it gave me an error message, " Connection cannot be made to this port. The port is not available" and I had to proceed with port number 5433. Later on I changed the port number to 5432 in config file and rebooted my pc. But I am still getting the same error. Please help.

Pulsifer

Did you check the listen ports and their IP addresses? You may be able to take a list by the command "lsof -i -n -P | grep postgres". – Emerson

My OS is Windows XP. lsof is not recognised as a command in Command Prompt. Could you please tell me other ways to check the same. – Pulsifer

Use 'netstat -a' command on 'Command Prompt' for listing up the ports. – Emerson

I checked the listen port and got below. – Pulsifer

Answer 1

I ran into this problem and it took me a while to realise that I was actually trying to use the psql client to connect to a MySQL server..

Knighton

Well I feel like a fool - was using a docker container which I thought defaulted to using the mysql client, turns out it was psql. Explicitly setting the option resolved this. – Hippogriff

MySQL used port 3306 and psql 5432, changing this resolved the problem – Alecto

How could you miss this? Well, if you are using a bunch of testcontainers and happen to suffer from github.com/moby/moby/issues/42442 ... damn Mondays. – Darksome

Answer 2

psql: error: could not connect to server: received invalid response to SSL negotiation: H

we also get above error if we are trying to access postgres container service exposed through some proxy container service like haproxy/nginx using http mode/protocol.

we need to use tcp mode/protocol.

Try it yourself with below details.

nginx.conf

events {}

stream {
  upstream postgres {
    server pdb:5432;
  }
  server {
    listen 5432;
    proxy_pass postgres;
  }
}

haproxy.cfg

global
  log 127.0.0.1   local1
  maxconn 4096

defaults
  mode http
  maxconn 2048

frontend postgresDB
  bind *:5000
  mode tcp
  timeout connect 5s
  timeout client 5s
  timeout server 5s
  default_backend postgresDB

backend postgresDB
  mode tcp
  server pdb pdb:5432  check inter 5s rise 2 fall 3

Containers deployment

sudo docker run -d --name testdb 'POSTGRES_PASSWORD=123456' postgres

sudo docker run -d --name pdb  -e 'POSTGRES_USER=admin' -e 'POSTGRES_DB=testdb' -e 'POSTGRES_PASSWORD=admin123456' postgres


sudo docker run -d --name nginx-TCPreverseProxy  -p 5432:5432  --link pdb:pdb -v /home/admin/nginx.conf:/etc/nginx/nginx.conf nginx

sudo docker run -d -p 5000:5000 --name haproxy --link pdb:pdb -v /home/admin/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg haproxy

#Testing of connection
sudo docker exec -it -u 0 testdb bash

#Inside test postgres container
#checking connection through nginx-proxy
root@34211600c3f7:/#  psql -h 192.168.0.2 -p 5432 -d testdb -U admin -W

#checking connection through haproxy-proxy
root@34211600c3f7:/#  psql -h 192.168.0.2 -p 5000 -d testdb -U admin -W

Grasping

Very important to don't forget this! Postgres only works with HAProxy if the latter is set to tcp mode. – Excitant

Answer 3

I got the problem resolved. I killed the pid for port number 5432 and then reinstalled the postgresql with port number entry as 5432. I was still getting error with port number 5432. Finally go to Control Panel-> administrative tools->Services ->right click postgresql service-> properies ->Log On tab-> check the radio button Local System Account and click ok. Now start the server. It will start working.

Pulsifer

Answer 4

In my case (Ubuntu 18.04 in WSL) I got the same error which turned out to be a mismatch between the port number that postgresql was running on and the port field specified in my Ruby on Rails application config/database.yml file.

You can find the postgresql port in Linux by running this command:

cat /etc/postgresql/12/main/postgresql.conf | grep port

Remark: 12 is the postgresql installed version, can be different in your situation.

Carder

Answer 5

I used navicat14 to connect. I copied a pgsql connection and changed it to the port and ip of mysql, resulting in an error message: received invalid response to SSL negotiation: J. enter image description here

It is estimated that the driver of each connection is inconsistent. I re-created a mysql connection and it worked.

Spiegleman