psql: server closed the connection unexepectedly

Question

I've been trying to run this batch file that goes through the Postgre DB Server and run two different sql files, as shown below:

set PGPASSWORD=blah
cls
@echo on
"C:\Progra~1\pgAdmin III\1.16\psql" -d [db name] -h [server name] -p 5432 -U postgres -f C:\query1.sql
"C:\Progra~1\pgAdmin III\1.16\psql" -d [db name] -h [server name] -p 5432 -U postgres -f C:\query2.sql

But the issue comes that sometimes I will get the following error for either the command for query1 or query2:

psql: server closed the connection unexpectedly 
This probably means the server terminated abnormally
before or while processing the request.

This only happens sometimes, so I'm not entirely sure why it is happening. Can someone explain why this is the case and if there's a solution to this problem. Thanks!

Update: I also get the same error SOMETIMES when trying to open the remote server in the actual Postgre application: "An error has occured: "server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request."

I also get this Guru Hint thing right after I click out of the error popup:

Database encoding The database VA-trac is created to store data using the SQL_ASCII encoding. This encoding is defined for 7 bit characters only; the meaning of characters with the 8th bit set (non-ASCII characters 127-255) is not defined. Consequently, it is not possible for the server to convert the data to other encodings. If you're storing non-ASCII data in the database, you're strongly encouraged to use a proper database encoding representing your locale character set to take benefit from the automatic conversion to different client encodings when needed. If you store non-ASCII data in an SQL_ASCII database, you may encounter weird characters written to or read from the database, caused by code conversion problems. This may cause you a lot of headache when accessing the database using different client programs and drivers. For most installations, Unicode (UTF8) encoding will provide the most flexible capabilities.

Regardless, the server still opens up afterward and I'm able to access the database from that point on.

Do you get the same effect when you log in interactively (i.e. without the SQL script)? What does PostgreSQL's log file say about the terminated connections? — Ansgar Wiechers, Apr 10 '13 at 21:41
PostgreSQL version? What happens if you use the `psql` from PostgreSQL's `bin` directory rather than one bundled in PgAdmin-III? Is there a firewall involved anywhere? — Craig Ringer, Apr 10 '13 at 23:32
If the connection aborts randomly I'd suspect network issues. Again, what does the log on the PostgreSQL server say about these terminated connections? And Craig was asking for the *PostgreSQL* version, not the *PgAdmin* version. — Ansgar Wiechers, Apr 11 '13 at 17:42
It turns out it is because there was a mismatch between the postgre SQL version between my local and the server, installing the same version of PostgreSQL in my computer fixed the issue. Thanks! — user974047, Apr 12 '13 at 17:45
I had this same problem but when I tried to connect to the DB using ADO in an Excel macro to run a query and populate the record set in Excel. I ran VACUUM/ANALYZE, reran the macro, and the record set populated in under 5 seconds. — jones-chris, Mar 30 '16 at 02:19

Gab · Answer 1 · 2016-07-11T20:37:53.617

43

Leaving this here for info,

This error can also be caused if PostgreSQL server is on another machine and is not listening on external interfaces.

To debug this specific problem, you can follow theses steps:

Look at your postgresql.conf, sudo vim /etc/postgresql/9.3/main/postgresql.conf
Add this line: listen_addresses = '*'
Restart the service sudo /etc/init.d/postgresql restart

(Note, the commands above are for ubuntu. Other linux distro or OS may have different path to theses files)

Note: using '*' for listening addresses will listen on all interfaces. If you do '0.0.0.0' then it'll listen for all ipv4 and if you do '::' then it'll listen for all ipv6.

http://www.postgresql.org/docs/9.3/static/runtime-config-connection.html

edited Jul 11 '16 at 20:37

answered Sep 29 '15 at 12:57

Gab

5,604
6
36
52

3

This line `listen_addresses = '*'` exists by default on official Dockerized images on https://hub.docker.com/_/postgres – Devy Oct 16 '19 at 18:38
10

This doesn't make sense to me. How could the connected be terminated if it was never established? – Michael Mior Feb 19 '20 at 12:21
For 64bit Windows, postgresql.conf is under `C:\Program Files\PostgreSQL\\data` – Ivan Chau Jan 26 '21 at 09:34

score 28 · Answer 2 · answered Apr 12 '13 at 17:46

28

It turns out it is because there was a mismatch between the postgre SQL version between my local and the server, installing the same version of PostgreSQL in my computer fixed the issue. Thanks!

answered Apr 12 '13 at 17:46

user974047

2,115
7
33
45

4

August 2020. This is still relevant. – t_sologub Aug 25 '20 at 17:07
1

how can I match them together? – k_afr Oct 05 '20 at 12:31
Just one example: pgAdmin 4.28 won't connect to any postgres server 12.4. If you downgrade to pgAdmin 4.27 you will be able to connect to 12.4. Unbelievable... – FireEmerald Nov 30 '20 at 07:57
2

How do you find out which pgAdmin version can connect to which postgres version? – Huiting May 14 '21 at 08:00
i did installed the same version on my machine but the problem still there. – gary Jun 02 '21 at 17:35
Still relevant in October 2022. For me, having matching major versions fixed the issue. – ryuzakyl Oct 14 '22 at 10:06

score 8 · Answer 3 · answered Jun 25 '16 at 07:23

8

In my case, it was because I set up the IP configuration wrongly in pg_hba.conf, that sits inside data folder in Windows.

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24            md5

I mistakenly entered (copied-pasted :-) ) 192.168.0.0 instead of 192.168.1.0.

answered Jun 25 '16 at 07:23

gdenuf

840
1
12
16

1

For 64-bit Windows, pg_hba.conf is under `C:\Program Files\PostgreSQL\VERSION\data\` – Ivan Chau Jan 26 '21 at 09:52
Possible Error Message: State: 08001 Native error: 101 Message: FATAL: no pg_hba.conf entry for host "a.b.c.d", user "postgres", database "template1", SSL off – Ivan Chau Jan 26 '21 at 09:54

score 7 · Answer 4 · answered Feb 28 '20 at 11:56

7

In my case I was making an connection through pgAdmin with ssh tunneling and set to host field ip address but it was necessary to set localhost

answered Feb 28 '20 at 11:56

Dmitry Chernikov

93
1
4

score 5 · Answer 5 · answered Apr 07 '16 at 12:32

5

this is an old post but...

just surprised that nobody talk about pg_hba file as it can be a good reason to get this error code.

Check here for those who forgot to configure it: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

answered Apr 07 '16 at 12:32

WKT

263
3
9

score 5 · Answer 6 · answered Mar 15 '19 at 17:17

5

In my case, i'm using Postgresql 9.2.24 and solution was this (pg_hba.conf):

host    all             all             0.0.0.0/0            trust

For remote connections use trust. Combined with (as mentioned above)

listen_addresses = '*'

answered Mar 15 '19 at 17:17

georgeos

2,351
2
24
28

3

Please also note that `listen_addresses = '*'` doesn't belongs to `pg_hba.conf`, instead it belongs to `postgresql.conf`, by default, list_addresses is set to all (*). https://www.postgresql.org/message-id/A76B25F2823E954C9E45E32FA49D70EC9199AF46@mail.corp.perceptron.com – Devy Oct 16 '19 at 18:37
1

This was definitely the required solution for me -- adding this line to `pg_hba.conf` in addition to the `listen_addresses = '*'` line in `postgresql.conf` – bunkerdive Jan 16 '20 at 20:59
2

This isn't secure, though, so I don't suggest you use this in production. It might indicate that indeed there is a networking allowance issue in pg_hba.conf to be worked out. – Robert Casey Jan 04 '21 at 15:26

score 1 · Answer 7 · answered Sep 02 '20 at 18:53

1

If you are using Docker make sure you are not using the same port in another service, in my case i was mistakenly using the same port for both PostgreSQL and Redis.

answered Sep 02 '20 at 18:53

Yoann Kergall

2,993
5
22
23

score 1 · Answer 8 · answered Jul 13 '21 at 20:32

1

In my case I was trying use ssh tunnel connection through pgAdmin4 and before that set AllowTcpForwarding no in server's /etc/ssh/sshd_config. But it should be AllowTcpForwarding yes.

answered Jul 13 '21 at 20:32

Dmitry Chernikov

93
1
4

score 1 · Answer 9 · answered Feb 17 '23 at 17:51

1

If you are connection through a SSH tunnel, this could mean you did a wrong port redirect on the target machine, check the log of the sshd on your jumpbox to see if this is the error.

answered Feb 17 '23 at 17:51

jocelyn

788
6
12

score 0 · Answer 10 · answered Aug 26 '19 at 12:15

0

If your Postgres was working and suddenly you encountered with this error, my problem was resolved just by restarting Postgres service or container.

answered Aug 26 '19 at 12:15

Ali Sepehri.Kh

2,468
2
18
27

bwl1289 · Answer 11 · 2020-11-15T03:55:50.187

0

Solved by setting a password for the user first.

In terminal

sudo -u <username> psql

ALTER USER <username> PASSWORD 'SetPassword';
# ALTER ROLE

\q

In pgAdmin

**Connection**

Host name/address: 127.0.0.1
Port: 5432
Maintenance database: postgres
username: postgres
password: XXXXXX

edited Nov 15 '20 at 03:55

answered Nov 15 '20 at 03:43

bwl1289

1,655
1
12
10

score 0 · Answer 12 · answered Dec 07 '22 at 20:26

0

In my case my postgres rds instance was stopped...

answered Dec 07 '22 at 20:26

Bguess

1,700
1
11
24

score 0 · Answer 13 · answered Jun 16 '23 at 19:16

0

In my case it was a discrepancy between postgresql.conf and pg_hba.conf

pg_hba.conf had a hostssl rule

hostssl   all           all             0.0.0.0/0               md5

and

postgresql.conf had

ssl = off

answered Jun 16 '23 at 19:16

hanspr

9
1

score 0 · Answer 14 · answered Jul 18 '23 at 05:38

0

I have the same problem in my Postgres.

As it's only happenning to one of my Django models save() method (a huge one), I blame the size of the record being inserted.

answered Jul 18 '23 at 05:38

mm49307

2,490
2
19
15

psql: server closed the connection unexepectedly

14 Answers14

Linked