错误信息服务器意外关闭了连接,psql：服务器意外关闭了连接 - 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:

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

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:

但问题是，有时我会对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数据，强烈建议您使用表示区域设置字符集的正确数据库编码，以便在需要时自动转换为不同的客户端编码。如果将非ASCII数据存储在SQL_ASCII数据库中，则可能会遇到由代码转换问题导致写入或读取数据库的奇怪字符。使用不同的客户端程序和驱动程序访问数据库时，这可能会让您头疼。对于大多数安装，Unicode(UTF8)编码将提供最灵活的功能。

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

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

4 个解决方案

#1

14

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

看看你的postgresql.conf，sudo vim /etc/postgresql/9.3/main/postgresql.conf

Add this line: listen_addresses = '*'

添加以下行：listen_addresses ='*'

Restart the service sudo /etc/init.d/postgresql restart

重启服务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

#2

9

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修复了这个问题。谢谢！

#3

5

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。

#4

2

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文件，因为它可能是获取此错误代码的一个很好的理由。

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