Can't connect to Postgresql on port 5432

Question

I have PostgreSQL 9.3 installed on a server running Ubuntu Server 14.04.

If I ssh into the server via terminal, I'm able to connect with psql. But when I try to configure pgAdmin III to do the remote connection, I get:

Server doesn't listen The server doesn't accept connections: the connection library reports could not connect to server: Connection refused Is the server running on host "172.24.3.147" and accepting TCP/IP connections on port 5432?

When I run on the server service postgresql status it gives me:

9.3/main (port 5432): online

So of course I'm missing something important here.

EDIT

When running netstat -na on the server, I get (relevant portion, I guess):

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp        0      0 172.24.3.147:22         172.24.3.240:61950      ESTABLISHED
tcp        0      0 172.24.3.147:22         172.24.3.240:60214      ESTABLISHED

you didn't bind to that ip. pg's listening on something else, like maybe just 127.0.0.1:5432 — Marc B, Jul 19 '16 at 18:42
What do you mean 'bind'? I'm using the port of the server (used in ssh), when I try to connect via pgAdmin. — Rodrigo, Jul 19 '16 at 18:46
you have to tell pg what interface/ip to listen to for connections. if it bound to only 127.0.0.1:5432, then any attempts on 172.24.3.147:5432 won't work, because nothing is listening for that ip:port combo — Marc B, Jul 19 '16 at 18:47
Per your comment, it should be reading on port 22 (why, if the default is 5432?), right? (That's what's in the State=ESTABLISHED line above). But when I try to connect to that port on pgAdmin, I get a different error: "An error has occurred: 14:57:13: Error: Error connecting to the server: SSL error: unknown protocol expected authentication request from server, but received S" — Rodrigo, Jul 19 '16 at 18:57

score 134 · Accepted Answer · edited May 04 '23 at 22:53

134

You have to edit postgresql.conf file and change line with 'listen_addresses'.

This file you can find in the /etc/postgresql/9.3/main directory.

Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other computers, you have change this config line in this way:

listen_addresses = '*'

Then you have edit pg_hba.conf file, too. In this file you have set, from which computers you can connect to this server and what method of authentication you can use. Usually you will need similar line:

host    all         all         192.168.1.0/24        md5

Please, read comments in this file...

EDIT:

After the editing postgresql.conf and pg_hba.conf you have to restart postgresql server: sudo service postgresql restart

EDIT2: Highlited configuration files.

edited May 04 '23 at 22:53

Tms91

3,456
6
40
74

answered Jul 19 '16 at 19:02

Jan Marek

10,390
3
21
19

Is "192.168.1.0" the remote server connecting to the Postgres DB? Why port 24? – Scott Skiles Oct 25 '18 at 09:17
3

@ScottSkiles 192.168.1.0 is address of network, /24 is not a number of port, but length of network mask in bites (255.255.255.0). In the other words: Allowed are computers from network 192.168.1.0/255.255.255.0, having addresses from 192.168.1.1 to 192.168.1.254 – Jan Marek Oct 25 '18 at 13:54
So a single IP would be (for 192.168.1.1): 192.168.1.1/1? – Scott Skiles Oct 25 '18 at 14:31
7

@ScottSkiles single ip would be 192.168.1.1/32 – Foreever Nov 01 '18 at 00:53
1

Which file are you talking about? There are at least 5 conf files. – Soerendip Jan 29 '19 at 17:32
@Sören In my answer you can find at least two files, which you have to change. – Jan Marek Feb 11 '19 at 11:17
You explained it all correct, but still I was making some common mistakes which I posted here.. https://stackoverflow.com/a/61182904/1005741 – hriziya Apr 13 '20 at 07:01
Pls I'm using google cloud shell editor and Postgres V13, however when I make changes to the postgresql.conf file, I can't save those changes, The save action never takes effect. When I try to open the file from terminal, I get prompted that edits are only allowed for files in the home directory. How do I change and save the postgresql.conf file in cloud shell? – Urchboy May 25 '21 at 11:53
@Urchboy Changes in file postgresql.conf can make only root or user postgres. – Jan Marek May 26 '21 at 11:03
How would this go for the IPV6 line beneath the IPV4 line in pg_hba.conf for a specific individual machine? – thefonso Oct 27 '21 at 03:01
@thefonso You can use, for example, `fe80::7a31:c1ff:abcd:abcd/128` instead of IP address and netmask. But there is a problem with IPv6 address privacy (autogenerated address). If you have static IPv6 address, it will works. – Jan Marek Nov 19 '21 at 09:56

score 42 · Answer 2 · edited Apr 20 '21 at 21:23

Uncomment the listen_addresses = '*' in the postgresql.conf

This has bitten me a second time so I thought might be worth mentioning. The line listen_addresses = '*' in the postgresql.conf is by default commented. Be sure to uncomment (remove the pound sign, # at the beginning) it after updating otherwise, remote connections will continue to be blocked.

PS: psql -U postgres -c 'SHOW config_file' - to locate the postgresql.conf file path

score 9 · Answer 3 · edited Jun 30 '17 at 14:52

9

Remember to check firewall settings as well. after checking and double-checking my pg_hba.conf and postgres.conf files I finally found out that my firewall was overriding everything and therefore blocking connections

edited Jun 30 '17 at 14:52

Eric Aya

69,473
35
181
253

answered Jun 30 '17 at 13:40

Alex

516
5
12

Your solution works for me. I ran '`sudo ufw status` and saw that my firewall was blocking the connection to port 5432 because it was not part of the allowed list of ports. To fix the issue run `sudo ufw allow 5432/tcp` – Seunope Dec 10 '22 at 11:33

score 9 · Answer 4 · answered Dec 01 '21 at 08:41

9

just summary from the accepted answer

if maybe someone like me overlook the filename that must be edit

in my case the conf file located at

/etc/postgresql/14/main'

if you type cd /etc/postgresql/14/main

so just sudo nano add these 2 filenames

postgresql.conf

listen_addresses = '*'

uncomment this line and change localhost to *

pg_hba.conf

host all all 0.0.0.0/0

change the 127.0.0.1/32 change to 0.0.0.0/0

last don't forget to restart using sudo service postgresql restart

I hope this one clears the notification error

answered Dec 01 '21 at 08:41

rtfmpliz

159
1
6

doesnt help with my connection refused... – Pfinnn Jul 01 '22 at 17:25
Did you use md5 after specifiying the IP? – Jose Luis Landivar S Feb 17 '23 at 17:49
This is allowing connections from anywhere, I wouldn't recommend allowing all in pg_hba.conf – Darryl Morley Apr 18 '23 at 21:01
upvoting for reminding me to restart the service....! – WobbleMeister May 01 '23 at 12:46

score 8 · Answer 5 · answered Oct 06 '16 at 19:16

Had same problem with psql via command line connecting and pgAdmin not connecting on RDS with AWS. I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following: sudo find . -name *.conf then sudo nano ./data/pg_hba.conf then added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5 and pgAdmin automatically logged me in.

This also worked in pg_hba.conf file host all all md5 without any IP address and this also worked with my IP address host all all <myip>/32 md5

As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.

score 7 · Answer 6 · answered Jan 29 '22 at 13:34

You have to edit pg_hba.conf to accept all requests within the network

#TYPE  DATABASE        USER            ADDRESS                 METHOD 
host    all             all             0.0.0.0/0                md5
host    all             all             ::1/128                  md5

From your application, you can connect using this IP address like below example: -

postgresql://postgres:******@192.168.1.101:5432/app

score 3 · Answer 7 · answered Jul 19 '16 at 18:47

3

You probably need to either open up the port to access it in your LAN (or outside of it) or bind the network address to the port (make PostgreSQL listen on your LAN instead of just on localhost)

answered Jul 19 '16 at 18:47

Daniel Kravetz Malabud

797
3
12

Sorry, as a lifetime PC developer, I'm not entirely familiar with this port thing. Right now I only need to access it inside our LAN (a proxy-based institutional network). The remaining of your message is unintelligible to me. See the other comments, please. – Rodrigo Jul 19 '16 at 19:02
1

The answer by Jan Marek should be what you are looking for. `tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN ` this line shows that Postgre's only listening for connection coming from the same computer where postgre is running, and not from the local network. – Daniel Kravetz Malabud Jul 19 '16 at 19:10
All right. But how do you guys know that the selected line is about Postgres? Because 5432 is their default port number? – Rodrigo Jul 19 '16 at 19:22
1

Correct. It only shows two ports, 22 which is the default one for SSH, and you already mentioned that you are using SSH, and 5432 which is the default one for Postgres, which you mentioned that you are using and having issues with. – Daniel Kravetz Malabud Jul 19 '16 at 19:24

score 2 · Answer 8 · answered Apr 03 '19 at 21:10

I had the same problem after a MacOS system upgrade. Solved it by upgrading the postgres with brew. Details: it looks like the system was trying to access Postgres 11 using older Postgres 10 settings. I'm sure it was my mistake somewhere in the past, but luckily it all got sorted out with the upgrade above.

score 2 · Answer 9 · answered Feb 12 '21 at 22:17

I had this same issue. I originally installed version 10 because that was the default install with Ubuntu 18.04. I later upgraded to 13.2 because I wanted the latest version. I made all the config modifications, but it was still just binging to 1207.0.0.1 and then I thought - maybe it is looking at the config files for version 10. I modified those and restarted the postgres service. Bingo! It was binding to 0.0.0.0

I will need to completely remove 10 and ensure that I am forcing the service to run under version 13.2, so if you upgraded from another version, try updating the other config files in that older directory.

Had the same issue as well. I had upgraded my version of PostgreSQL and turns out editing the old version's config was the way to go. — Steve S, Aug 17 '21 at 00:28

score 2 · Answer 10 · answered Nov 30 '21 at 02:19

If error occurred first thing first you have to read the log.

This only applicable for postgresql installation through brew on your mac *

First, you have to check the postgresql status by running

brew services on your terminal

If its stopped, try to started it and then run the brew services again

And if the status show error, you can easily find the log location through the *.plist file.

Example, on my configuration

cat /Users/drosanda/Library/LaunchAgents/homebrew.mxcl.postgresql@12.plist
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>KeepAlive</key>
    <true/>
    <key>Label</key>
    <string>homebrew.mxcl.postgresql@12</string>
    <key>ProgramArguments</key>
    <array>
        <string>/usr/local/opt/postgresql@12/bin/postgres</string>
        <string>-D</string>
        <string>/usr/local/var/postgresql@12</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>StandardErrorPath</key>
    <string>/usr/local/var/log/postgresql@12.log</string>
    <key>StandardOutPath</key>
    <string>/usr/local/var/log/postgresql@12.log</string>
    <key>WorkingDirectory</key>
    <string>/usr/local</string>
</dict>
</plist>

Here is the log file content:

2021-11-30 09:07:20.154 WIB [3891] FATAL: lock file "postmaster.pid" already exists

2021-11-30 09:07:20.154 WIB [3891] HINT: Is another postmaster (PID 422) running in data directory "/usr/local/var/postgresql@12"?

For my case, the postgresql won't start caused by the PID file is still exists.

It's easy to fix, by removed the postmaster.pid file.

Just add an answer that might useful for someone later.

score 2 · Answer 11 · answered Mar 16 '22 at 05:11

2

adding an updated answer for another possibility. running postgres 10 on AWS EC2 I had to add a custom inbound rule through Windows Defender Firewall to allow port 5432.

answered Mar 16 '22 at 05:11

Johnny D

31
3

score 2 · Answer 12 · answered Mar 17 '22 at 08:06

For anyone else coming from docker and other applications issues like node js or Django or anything

i faced this problem for 2 days or more watched videos tutorials nothing seem to work although ill say i have missed some but here is the solution that worked for me

instead of local host for PGHOST

you must specify your service name

e.g

    image: postgres
    container_name: postgresdb
    restart: always
    env_file:
      - ./env/.env
    volumes:

then your host must be PGHOST:nodeapp-db

score 1 · Answer 13 · answered Jan 14 '22 at 10:12

1

windows === press WIN + R //open services type - services.msc
find postgres - double click on it.

properties box open then click on start enjoy.

answered Jan 14 '22 at 10:12

joy

21
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 14 '22 at 11:48

score 1 · Answer 14 · answered Feb 06 '23 at 11:43

1

Just give one sudo service postgresql start

answered Feb 06 '23 at 11:43

Bruno Lima

11
2

score 0 · Answer 15 · answered Aug 22 '21 at 15:02

I use home-brew to start my server.

1)stop the server (but it is not working. it Is the problem)

brew services stop postgresql

2)make update if it is needed

brew postgresql-upgrade-database

3)below comment solve my problem it start the server. Thats it

brew services start postgresql

score 0 · Answer 16 · edited Feb 06 '23 at 14:47

0

Had this same issue and searches resulted in many complex overly solutions. Ultimately this issue was that a password had not been set after Postgres was installed on Ubuntu.

Solution:

From the Postgres terminal - reached by entering "sudo -u postgres psql" which should result in "postgres=#"...
Set the password using "\password postgres"
Update PG Admin with the new password and the username "postgres"

Reference https://www.cherryservers.com/blog/how-to-install-and-setup-postgresql-server-on-ubuntu-20-04.

edited Feb 06 '23 at 14:47

Pranav Singh

17,079
30
77
104

answered Jul 11 '22 at 15:05

Kmb40

2,341
3
18
14

For windows, "psql -U postgres" is all you need. My god it finally works. The password I made during installation finally gets me in. Wasted an hour on this stupid issue. – Bob the Builder Nov 05 '22 at 02:58

Matt · Answer 17 · 2022-09-15T07:34:04.507

In my case, the server wasn't listenting because it didn't start up automatically (Windows version of PostgreSQL), because the setup program didn't create a user and didn't initialize the server. The following helped me:

After the setup I had to run (in a non-admin cmd.exe shell):

set PGDATA=C:\Program Files\PostgreSQL\14\data
cd C:\Program Files\PostgreSQL\14\bin
createuser.exe -s postgres
pg_ctl init
pg_ctl start

After that, I used the pgAdmin tool and selected "Add new server" in the dashboard. There, I typed in the desired server name in the "General" tab, and in the "Connection" tab I entered 127.0.0.1 because I wanted to run it locally. After confirming it with Save it got created properly.

It seems that in Windows, Postgres isn't running as a Windows Service, so it doesn't start automatically and you have to run it manually. However you could create a StartPg.cmd file and put in the command C:\Program Files\PostgreSQL\14\bin\pg_ctl start, then you can open a Windows Explorer via Win+E, enter shell:Startup in its path textbox (then press Enter) and copy the batch file (or a shortcut of it) into it.

Then it will start up automatically.

Namwanza Ronald · Answer 18 · 2022-11-17T12:34:23.637

If you have tried to edit pg_hba.conf and postgres.conf files as suggested above and nothing worked, then here you go !

A try for the late birds: | LINUX USERS

After trying all other options suggested above, none worked for me.

So I released that this was happening due to failed PostgreSQL Cluster 14-main when booting/starting.

I followed below steps,

Applied Solution :

sudo chmod 700 -R /var/lib/postgresql/14/main
sudo -i -u postgres
/usr/lib/postgresql/14/bin/pg_ctl restart -D /var/lib/postgresql/14/main

Note: You can replace 14 with your postgresql version number, you can check /usr/lib/postgresql/{mark the number you see here}

After Solution status :

pg_ctl: PID file "/var/lib/postgresql/14/main/postmaster.pid" does not exist Is server running? trying to start server anyway waiting for server to start....2022-11-17 13:06:36.614 CET [5542] LOG: starting PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit 2022-11-17 13:06:36.614 CET [5542] LOG: listening on IPv4 address "0.0.0.0", port 5432 2022-11-17 13:06:36.614 CET [5542] LOG: listening on IPv6 address "::", port 5432 2022-11-17 13:06:36.616 CET [5542] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2022-11-17 13:06:36.623 CET [5543] LOG: database system was shut down at 2022-11-17 09:20:13 CET 2022-11-17 13:06:36.631 CET [5542] LOG: database system is ready to accept connections done server started

pg_lsclusters

10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

Finally, I was able to connect to PORT = 5432 with no problem and everything worked perfectly fine.

score 0 · Answer 19 · answered Feb 06 '23 at 01:10

I’m using MacPorts and after migrating to a new machine using Apple’s Migration Assistant the db server was unavailable:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?

I tried

sudo port load postgresql14-server

but that didn’t work. Looking at the /Library/LaunchDaemons/org.macports.postgresql14-server.plist file, though revealed two things:

<key>Disabled</key><true/>

and the actual start command

<string>--start-cmd</string>
<string>/opt/local/etc/LaunchDaemons/org.macports.postgresql14-server/postgresql14-server.wrapper</string>

So, running the start command manually finally showed the issue:

> sudo /opt/local/etc/LaunchDaemons/org.macports.postgresql14-server/postgresql14-server.wrapper start
pg_ctl: could not open PID file "/opt/local/var/db/postgresql14/defaultdb/postmaster.pid": Permission denied

and indeed:

> sudo ls -la /opt/local/var/db/postgresql14/defaultdb/
total 60
drwx------ 26 postgres postgres   832 Jan 31 05:13 .
drwxr-xr-x  4 postgres postgres   128 Feb  5 12:31 ..
-rw-------  1      500 postgres     3 Nov  7  2021 PG_VERSION
drwx------ 22      500 postgres   704 Feb  4 10:37 base
drwx------ 60      500 postgres  1920 Feb  4 10:37 global
drwx------  2      500 postgres    64 Nov  7  2021 pg_commit_ts
...
-rw-------  1      500 postgres   105 Jan 31 05:13 postmaster.pid

The user id was messed up. Fix:

sudo chown -hR postgres /opt/local/var/db/postgresql14/defaultdb/

to set the owner of all files to postgres.

With that change, the server works:

> sudo /opt/local/etc/LaunchDaemons/org.macports.postgresql14-server/postgresql14-server.wrapper start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started
> psql -U postgres -W -p 5432 -h localhost
Password: 
psql (14.6)
Type "help" for help.

postgres=# 
\q

Can't connect to Postgresql on port 5432

19 Answers19

EDIT:

A try for the late birds: | LINUX USERS

Applied Solution :

After Solution status :

Linked

Related