SQL psql: 服务器意外关闭了连接

psql: server closed the connection unexepectedly

提问by user974047

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:

我一直在尝试运行这个通过 Postgre DB 服务器的批处理文件并运行两个不同的 sql 文件，如下所示：

set PGPASSWORD=blah
cls
@echo on
"C:\Progra~1\pgAdmin III.16\psql" -d [db name] -h [server name] -p 5432 -U postgres -f C:\query1.sql
"C:\Progra~1\pgAdmin III.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:

但是问题来了，有时我会为 query1 或 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."

更新：在实际 Postgre 应用程序中尝试打开远程服务器时，有时我也会遇到相同的错误：“发生错误：”服务器意外关闭了连接这可能意味着服务器在处理请求之前或处理请求时异常终止。

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

单击错误弹出窗口后，我也立即收到了 Guru Hint 提示：

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.

数据库编码 创建数据库 VA-trac 以使用 SQL_ASCII 编码存储数据。此编码仅针对 7 位字符定义；未定义第 8 位设置的字符（非 ASCII 字符 127-255）的含义。因此，服务器不可能将数据转换为其他编码。如果您在数据库中存储非 ASCII 数据，强烈建议您使用代表您的区域设置字符集的正确数据库编码，以便在需要时从自动转换为不同的客户端编码中受益。如果您在 SQL_ASCII 数据库中存储非 ASCII 数据，您可能会遇到由代码转换问题引起的写入数据库或从数据库读取的奇怪字符。在使用不同的客户端程序和驱动程序访问数据库时，这可能会让您很头疼。

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

无论如何，服务器随后仍会打开，从那时起我就可以访问数据库了。

回答by Gab

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.

如果 PostgreSQL 服务器在另一台机器上并且没有侦听外部接口，也可能导致此错误。

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

• 看看你的 postgresql.conf， sudo vim /etc/postgresql/9.3/main/postgresql.conf
• 添加这一行： listen_addresses = '*'
• 重启服务 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)

（注意，上面的命令是针对 ubuntu 的。其他 linux 发行版或操作系统可能有不同的这些文件的路径）

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.

注意：'*'用于侦听地址将侦听所有接口。如果你这样做，'0.0.0.0'它会监听所有 ipv4，如果你这样做，'::'它会监听所有 ipv6。

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

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

回答by user974047

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!

原来这是因为我的本地和服务器之间的 postgre SQL 版本不匹配，在我的计算机上安装相同版本的 PostgreSQL 解决了这个问题。谢谢！

回答by n_y

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

就我而言，这是因为我在 pg_hba.conf 中错误地设置了 IP 配置，该文件位于 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.

我错误地输入了（复制粘贴的 :-) ）192.168.0.0 而不是 192.168.1.0。

回答by georgeos

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

就我而言，我使用的是 Postgresql 9.2.24，解决方案是这样的 (pg_hba.conf)：

host    all             all             0.0.0.0/0            trust

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

对于远程连接，请使用trust。结合（如上所述）

listen_addresses = '*'

回答by WKT

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.

只是惊讶没有人谈论 pg_hba 文件，因为它可能是获取此错误代码的一个很好的理由。

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

检查这里忘记配置的人：http: //www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

回答by Dmitry Chernikov

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

在我的情况下，我通过 pgAdmin 使用 ssh 隧道建立连接并设置为主机字段 ip 地址，但有必要设置 localhost

回答by Ali Sepehri.Kh

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

如果您的 Postgres 正在工作并且突然遇到此错误，则只需重新启动 Postgres 服务或容器即可解决我的问题。