以postgres用户身份连接到PostgreSQL时出现连接错误？ [英] Connection Error while connecting to PostgreSQL as postgres user?

问题描述

我无法使用python和psycopg2远程连接到PostgreSQL：

I am not able connect to PostgreSQL remotely using python and psycopg2:

这是我的代码。

>>> import psycopg2
>>> conn_string = "host='localhost' dbname='mydb' user='postgres'"
>>> print "Connecting to database\n     ->%s" % (conn_string)
    Connecting to database
      ->host='localhost' dbname='mydb' user='postgres'
>>> conn = psycopg2.connect(conn_string)

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/tools/lib/python2.7/site-packages/psycopg2/__init__.py", line 164, in connect
conn = _connect(dsn, connection_factory=connection_factory, async=async)
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?

无法连接到服务器：连接被拒绝
服务器是否在主机 localhost上运行（127.0.0.1）并在端口5432上接受
TCP / IP连接？

could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

未为postgres用户设置密码。

The password is not set for postgres user.

通常，我可以通过在主机上运行以下方法来连接数据库。

Generally, I can connect to database by running following method on host.

 1. SSH to box
 2. su - postgres
 3. psql
 4. \c mydb

服务器运行PostgreSQL 9.1。

The server runs PostgreSQL 9.1.

推荐答案

您正在尝试连接使用计算机上运行的脚本在 localhost 上运行PostgreSQL，但是那里没有运行PostgreSQL服务器。

You're trying to connect to PostgreSQL on localhost using a script running on your computer, but there's no PostgreSQL server running there.

为此要工作，您必须SSH到远程服务器，然后在其中运行Python脚本，其中PostgreSQL服务器相对于Python脚本是本地。

For this to work, you'd have to ssh to the remote server, then run your Python script there, where the PostgreSQL server is "local" relative to the Python script.

（这就是为什么运行 psql 的原因-因为您是在远程服务器上运行 ，相对于<$ c $，PostgreSQL是本地的 c> psql ）。

(That's why running psql works - because you're running it on the remote server, where PostgreSQL is "local" relative to psql).

或者，您可以：

• 使用SSH隧道从本地计算机转发PostgreSQL端口到远程计算机；或

在服务器上启用远程连接后，使用其主机名或IP地址通过TCP / IP直接连接到远程PostgreSQL服务器。

Connect directly over TCP/IP to the remote PostgreSQL server using its host name or IP address, after enabling remote connections on the server.

请注意，只需将服务器的IP地址或主机名放入连接字符串中，而不是 localhost 将不起作用，除非您还将服务器配置为接受远程连接。您必须设置 listen_addresses 来侦听非本地连接，添加任何必需的防火墙规则，设置 pg_hba.conf 以允许来自远程计算机的连接，最好设置SSL。所有这些都在PostgreSQL用户手册的客户端身份验证一章中进行了介绍。

Note that just putting the server's IP address or host name into the connection string instead of localhost will not work unless you also configure the server to accept remote connections. You must set listen_addresses to listen for non-local connections, add any required firewall rules, set pg_hba.conf to permit connections from remote machines, and preferably set up SSL. All this is covered in the Client Authentication chapter of the PostgreSQL user manual.

您可能会发现更简单，更易于理解的SSH隧道。

You'll probably find an SSH tunnel simpler and easier to understand.

这篇关于以postgres用户身份连接到PostgreSQL时出现连接错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！