[sql] psql: server closed the connection unexepectedly

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.

This question is related to sql postgresql cmd

The answer is


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

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 = '*'

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


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.


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


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


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.


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


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!


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to cmd

'ls' is not recognized as an internal or external command, operable program or batch file '' is not recognized as an internal or external command, operable program or batch file XCOPY: Overwrite all without prompt in BATCH VSCode Change Default Terminal How to install pandas from pip on windows cmd? 'ls' in CMD on Windows is not recognized Command to run a .bat file VMware Workstation and Device/Credential Guard are not compatible How do I kill the process currently using a port on localhost in Windows? how to run python files in windows command prompt?