当前位置： 文江博客 话题详情

无法将 postgreSQL 与 psycopg2 连接

发布于 2024-10-27 20:36:13 字数 1387 浏览 6 评论 0 原文

第一次遇到一些技术问题找不到答案 这是我的问题：

>> conn=psycopg2.connect(database="mydb", user="postgres", password="123",port=5432)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.OperationalError: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
  1. 我的 postgreSQL 正在运行
  2. 我的监听端口肯定是 5432
  3. root@lanston-laptop:~# psql -l 密码：
                                       List of databases
         Name      |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges 
    ---------------+----------+----------+------------+------------+-----------------------
     checkdatabase | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     mydb          | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     postgres      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     template0     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                   |          |          |            |            | postgres=CTc/postgres
     template1     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                   |          |          |            |            | postgres=CTc/postgres

非常感谢！

原文

It's the first time that I can't find the answer about some tech problems
Here's my problems:

>> conn=psycopg2.connect(database="mydb", user="postgres", password="123",port=5432)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.OperationalError: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
  1. My postgreSQL is running
  2. My listeningport is 5432 for sure
  3. root@lanston-laptop:~# psql -l
    Password: 
                                       List of databases
         Name      |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges 
    ---------------+----------+----------+------------+------------+-----------------------
     checkdatabase | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     mydb          | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     postgres      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     template0     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                   |          |          |            |            | postgres=CTc/postgres
     template1     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                   |          |          |            |            | postgres=CTc/postgres

Thanks a lot!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（10）

删除→记忆 2024-11-03 20:36:13

psycopg2 使用的 libpq 期望 Postgres 套接字位于 /var/run/postgresql/ 中，但是当您从源代码安装 Postgres 时，默认情况下它位于 /tmp/ 中>。

检查是否存在文件 /tmp/.s.PGSQL.5432 而不是 /var/run/postgresql/.s.PGSQL.5432。尝试：

conn=psycopg2.connect(
  database="mydb",
  user="postgres",
  host="/tmp/",
  password="123"
)

Your libpq, which is used by psycopg2 expects Postgres socket to be in /var/run/postgresql/ but when you install Postgres from source it is by default it in /tmp/.

Check if there is a file /tmp/.s.PGSQL.5432 instead of /var/run/postgresql/.s.PGSQL.5432. Try:

conn=psycopg2.connect(
  database="mydb",
  user="postgres",
  host="/tmp/",
  password="123"
)
回复 原文
痴者 2024-11-03 20:36:13

尝试将端口更改为 5433 而不是 5432

Try change port to 5433 instead of 5432

回复 原文
溺深海 2024-11-03 20:36:13

只有这样解决了我的问题
建立一个指向 /tmp/.s.PGSQL.5432 的符号链接：

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

感谢 Sukhjit Singh Sehra - s-postgresql-server-is-running

Only this solved my problem,
make a symbolic link to the /tmp/.s.PGSQL.5432:

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

Thanks to, Sukhjit Singh Sehra - s-postgresql-server-is-running

回复 原文
过气美图社 2024-11-03 20:36:13

我原本打算将其作为对 Tometzky 的答案的评论，但是好吧，我在这里有很多话要说。关于不直接调用psycopg2.connect，而是使用第三方软件的情况。

tl;dr

将postgresql.conf中的unix_socket_directories设置为/var/run/postgresql, /tmp，然后重新启动PostgreSQL。

我尝试了发行

版存储库中的 PostgreSQL 9.2 (CentOS 7) 和 9.5 (Ubuntu Xenial)，CentOS 7 上的 PostgreSQL 9.3、9.4、9.5、9.6、10，来自 PostgreSQL 存储库、PostgreSQL 9.6、10来自 PostgreSQL 存储库。其中只有 9.3 只监听 /tmp：

$ systemctl stop postgresql-9.4 && systemctl start postgresql-9.3
$ lsof -aUp $(ps --ppid 1 -o pid= -o comm= | awk '$2 == "postgres" || $2 == "postmaster" {print $1}')
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
postgres 25455 postgres    4u  unix 0xffff9acb23bc5000      0t0 6813995 /tmp/.s.PGSQL.5432

$ systemctl stop postgresql-9.3 && systemctl start postgresql-9.4
$ lsof -aUp $(ps --ppid 1 -o pid= -o comm= | awk '$2 == "postgres" || $2 == "postmaster" {print $1}')
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
postgres 26663 postgres    4u  unix 0xffff9ac8c5474c00      0t0 7086508 /var/run/postgresql/.s.PGSQL.5432
postgres 26663 postgres    5u  unix 0xffff9ac8c5477c00      0t0 7086510 /tmp/.s.PGSQL.5432

python-psycopg2

这对 psql 来说没什么大不了的，只是运行匹配的二进制文件的问题。但是，例如，如果您从 CentOS 的 base 或 update 存储库安装了 python-psycopg2。它动态链接到操作系统提供的libpq。安装了 9.3 和 9.4 的操作系统提供了 9.4 的版本：

$ alternatives --display pgsql-ld-conf
pgsql-ld-conf - status is auto.
 link currently points to /usr/pgsql-10/share/postgresql-9.4-libs.conf
/usr/pgsql-9.3/share/postgresql-9.3-libs.conf - priority 930
/usr/pgsql-9.4/share/postgresql-9.4-libs.conf - priority 940
Current `best' version is /usr/pgsql-9.4/share/postgresql-9.4-libs.conf.

$ ls -l /etc/ld.so.conf.d
lrwxrwxrwx 1 root root 31 Feb  7 02:25 postgresql-pgdg-libs.conf -> /etc/alternatives/pgsql-ld-conf

$ ls -l /etc/alternatives/pgsql-ld-conf
lrwxrwxrwx 1 root root 43 Feb  7 02:25 /etc/alternatives/pgsql-ld-conf -> /usr/pgsql-9.4/share/postgresql-9.4-libs.conf

$ cat /usr/pgsql-9.4/share/postgresql-9.4-libs.conf
/usr/pgsql-9.4/lib/

但是 PostgreSQL 9.4 附带的 libpq 会在 /var/run/postgresql 中查找套接字，而不是 9.3：

$ strings /usr/pgsql-9.3/lib/libpq.so.5 | egrep '/(tmp|var)'
/tmp

$ strings /usr/pgsql-9.4/lib/libpq.so.5 | egrep '/(tmp|var)'
/var/run/postgresql

解决方案来自相应软件包的安装后脚本：

$ yum reinstall --downloadonly postgresql94-libs
$ rpm -qp /var/cache/yum/x86_64/7/pgdg94/packages/postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64.rpm --scripts

postinstall scriptlet (using /bin/sh):
/usr/sbin/update-alternatives --install /etc/ld.so.conf.d/postgresql-pgdg-libs.conf   pgsql-ld-conf        /usr/pgsql-9.4/share/postgresql-9.4-libs.conf 940
/sbin/ldconfig                                                                                 

# Drop alternatives entries for common binaries and man files                                  
postuninstall scriptlet (using /bin/sh):                                                       
if [ "$1" -eq 0 ]
  then
    /usr/sbin/update-alternatives --remove pgsql-ld-conf /usr/pgsql-9.4/share/postgresql-9.4-libs.conf
    /sbin/ldconfig                                                                             
fi

暂时删除 9.4 的替代方案：

$ alternatives --remove pgsql-ld-conf /usr/pgsql-9.4/share/postgresql-9.4-libs.conf
$ ldconfig

完成后，重新安装 postgresql94-libs，或添加替代方案：

$ alternatives --install /etc/ld.so.conf.d/postgresql-pgdg-libs.conf pgsql-ld-conf /usr/pgsql-9.4/share/postgresql-9.4-libs.conf 940
$ ldconfig

pip

另一方面，如果您使用 pip 安装 psycopg2，它默认会安装预编译包，该包带有自己的 libpq，在 /var/run/postgresql 中查找套接字：

$ python3.5 -m venv 1
$ . ./1/bin/activate
(1) $ pip install psycopg2

(1) $ python
>>> import psycopg2
>>>Ctrl-Z
[1]+  Stopped                 python

(1) $ pgrep python
26311

(1) $ grep libpq /proc/26311/maps | head -n 1
7f100b8cb000-7f100b90e000 r-xp 00000000 08:04 112980                     /home/yuri/1/lib/python3.5/site-packages/psycopg2/.libs/libpq-909a53d8.so.5.10

(1) $ strings /home/yuri/1/lib/python3.5/site-packages/psycopg2/.libs/libpq-909a53d8.so.5.10 | egrep '/(tmp|var)'
/var/run
/var/run/postgresql

解决方案是要求 pip 不要安装预编译包，并进行 pg_config > 的正确版本PostgreSQL 可用：

$ PATH=/usr/pgsql-9.3/lib:$PATH pip install --no-binary psycopg2 psycopg2

您甚至可以将 --no-binary 开关添加到 requirements.txt:

psycopg2==2.7.3.2 --no-binary psycopg2

unix_socket_directories

不过更简单的选择是使用 unix_socket_directories 选项：

I originally intended to make it a comment to Tometzky's answer, but well, I have a lot to say here... Regarding the case where you don't call psycopg2.connect directly, but use third-party software.

tl;dr

Set unix_socket_directories in postgresql.conf to /var/run/postgresql, /tmp, and restart PostgreSQL.

intro

I tried PostgreSQL 9.2 (CentOS 7) and 9.5 (Ubuntu Xenial) from distro repos, PostgreSQL 9.3, 9.4, 9.5, 9.6, 10 on CentOS 7 from PostgreSQL repo, PostgreSQL 9.6, 10 on Ubuntu Xenial from PostgreSQL repo. Among them only 9.3 listens to only /tmp:

$ systemctl stop postgresql-9.4 && systemctl start postgresql-9.3
$ lsof -aUp $(ps --ppid 1 -o pid= -o comm= | awk '$2 == "postgres" || $2 == "postmaster" {print $1}')
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
postgres 25455 postgres    4u  unix 0xffff9acb23bc5000      0t0 6813995 /tmp/.s.PGSQL.5432

$ systemctl stop postgresql-9.3 && systemctl start postgresql-9.4
$ lsof -aUp $(ps --ppid 1 -o pid= -o comm= | awk '$2 == "postgres" || $2 == "postmaster" {print $1}')
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
postgres 26663 postgres    4u  unix 0xffff9ac8c5474c00      0t0 7086508 /var/run/postgresql/.s.PGSQL.5432
postgres 26663 postgres    5u  unix 0xffff9ac8c5477c00      0t0 7086510 /tmp/.s.PGSQL.5432

python-psycopg2

That's not a big deal with psql, just a matter of running the matching binary. But if you, for instance, have python-psycopg2 installed from CentOS's base or update repo. It links dynamically to libpq that OS provides. With 9.3 and 9.4 installed OS provides 9.4's version:

$ alternatives --display pgsql-ld-conf
pgsql-ld-conf - status is auto.
 link currently points to /usr/pgsql-10/share/postgresql-9.4-libs.conf
/usr/pgsql-9.3/share/postgresql-9.3-libs.conf - priority 930
/usr/pgsql-9.4/share/postgresql-9.4-libs.conf - priority 940
Current `best' version is /usr/pgsql-9.4/share/postgresql-9.4-libs.conf.

$ ls -l /etc/ld.so.conf.d
lrwxrwxrwx 1 root root 31 Feb  7 02:25 postgresql-pgdg-libs.conf -> /etc/alternatives/pgsql-ld-conf

$ ls -l /etc/alternatives/pgsql-ld-conf
lrwxrwxrwx 1 root root 43 Feb  7 02:25 /etc/alternatives/pgsql-ld-conf -> /usr/pgsql-9.4/share/postgresql-9.4-libs.conf

$ cat /usr/pgsql-9.4/share/postgresql-9.4-libs.conf
/usr/pgsql-9.4/lib/

But libpq that comes with PostgreSQL 9.4 looks for socket in /var/run/postgresql as opposed to 9.3:

$ strings /usr/pgsql-9.3/lib/libpq.so.5 | egrep '/(tmp|var)'
/tmp

$ strings /usr/pgsql-9.4/lib/libpq.so.5 | egrep '/(tmp|var)'
/var/run/postgresql

The solution comes from postinstall scripts of corresponding packages:

$ yum reinstall --downloadonly postgresql94-libs
$ rpm -qp /var/cache/yum/x86_64/7/pgdg94/packages/postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64.rpm --scripts

postinstall scriptlet (using /bin/sh):
/usr/sbin/update-alternatives --install /etc/ld.so.conf.d/postgresql-pgdg-libs.conf   pgsql-ld-conf        /usr/pgsql-9.4/share/postgresql-9.4-libs.conf 940
/sbin/ldconfig                                                                                 

# Drop alternatives entries for common binaries and man files                                  
postuninstall scriptlet (using /bin/sh):                                                       
if [ "$1" -eq 0 ]
  then
    /usr/sbin/update-alternatives --remove pgsql-ld-conf /usr/pgsql-9.4/share/postgresql-9.4-libs.conf
    /sbin/ldconfig                                                                             
fi

Temporarily remove 9.4's alternative:

$ alternatives --remove pgsql-ld-conf /usr/pgsql-9.4/share/postgresql-9.4-libs.conf
$ ldconfig

When finished either reinstall postgresql94-libs, or add the alternative back:

$ alternatives --install /etc/ld.so.conf.d/postgresql-pgdg-libs.conf pgsql-ld-conf /usr/pgsql-9.4/share/postgresql-9.4-libs.conf 940
$ ldconfig

pip

If you install psycopg2 with pip on the other hand, it by default installs precompiled package which comes with its own libpq, which looks for socket in /var/run/postgresql:

$ python3.5 -m venv 1
$ . ./1/bin/activate
(1) $ pip install psycopg2

(1) $ python
>>> import psycopg2
>>>Ctrl-Z
[1]+  Stopped                 python

(1) $ pgrep python
26311

(1) $ grep libpq /proc/26311/maps | head -n 1
7f100b8cb000-7f100b90e000 r-xp 00000000 08:04 112980                     /home/yuri/1/lib/python3.5/site-packages/psycopg2/.libs/libpq-909a53d8.so.5.10

(1) $ strings /home/yuri/1/lib/python3.5/site-packages/psycopg2/.libs/libpq-909a53d8.so.5.10 | egrep '/(tmp|var)'
/var/run
/var/run/postgresql

The solution is to ask pip to not install precompiled package, and make pg_config of the proper version of PostgreSQL available:

$ PATH=/usr/pgsql-9.3/lib:$PATH pip install --no-binary psycopg2 psycopg2

You can even add --no-binary switch to requirements.txt:

psycopg2==2.7.3.2 --no-binary psycopg2

unix_socket_directories

The easier option though is to make use of unix_socket_directories option:

回复 原文
孤寂小茶 2024-11-03 20:36:13

在我的情况下，使用 conda 安装必须： sudo ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

In my case with a conda installation had to: sudo ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

回复 原文
凉宸 2024-11-03 20:36:13

几年后，在 OSX 10.8 上使用 EnterpriseDB“图形”安装，并使用 pip install psycopg2 （在链接 /Library/...dylib 的 ​​如此处所述之后）我有这个同样的问题。

对我来说，正确的连接命令是 conn = psycopg2.connect('dbname=DBNAME user=postgres password=PWHERE host=/tmp/')

a few years later, using the EnterpriseDB 'graphical' install on OSX 10.8, and pip install of psycopg2 (after linking the /Library/...dylib's as described here) i had this same issue.

for me the correct connect command was conn = psycopg2.connect('dbname=DBNAME user=postgres password=PWHERE host=/tmp/')

回复 原文
最美不过初阳 2024-11-03 20:36:13

在 brew 升级 后，我遇到了这种情况，我在 google 上搜索了 brew .s.PGSQL.5432。

根据这个答案中的建议，我运行了以下命令：

postgres -D /usr/local/var/postgres

并得到：

2019-10-29 17:43:30.860 IST [78091] FATAL:  database files are incompatible with server
2019-10-29 17:43:30.860 IST [78091] DETAIL:  The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.5.

我用谷歌搜索了那个致命错误，并根据这个答案我跑了：

brew postgresql-upgrade-database

这为我解决了问题。

Having this happen to me after a brew upgrade, I googled for brew .s.PGSQL.5432.

Per the suggestion in this answer I ran the following:

postgres -D /usr/local/var/postgres

And got:

2019-10-29 17:43:30.860 IST [78091] FATAL:  database files are incompatible with server
2019-10-29 17:43:30.860 IST [78091] DETAIL:  The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.5.

I googled that FATAL error and per the suggestion in this answer I ran:

brew postgresql-upgrade-database

That solved it for me.

回复 原文
ゞ记忆︶ㄣ 2024-11-03 20:36:13

把vpc_access_connector：
名称：project//locations/us-central1/connectors/

和主机：'/cloudsql/::

它应该适用于 gcp 上的私有 Ip postgresql

put vpc_access_connector:
name: project//locations/us-central1/connectors/

and host :'/cloudsql/::

It should work for private Ip postgresql on gcp

回复 原文
战皆罪 2024-11-03 20:36:13

尝试一次

cd /etc/postgresql/13/main
vi pg_hba.conf

更改此

数据库管理登录后的行 通过 Unix 域套接字

本地所有 postgres peer

到

本地所有 postgres md5

然后执行以下命令

sudo systemctl stop postgresql

sudo systemctl start postgresql

然后运行python程序即可正常运行

Try this once

cd /etc/postgresql/13/main
vi pg_hba.conf

Change the line after this

Database administrative login by Unix domain socket

local all postgres peer

To

local all postgres md5

Then execute following commands

sudo systemctl stop postgresql

sudo systemctl start postgresql

Then run the python program then it will work properly

回复 原文
故人爱我别走 2024-11-03 20:36:13

出现此问题的原因是有时 postgres 连接
localhost-5433 而不是 localhost-5432 作为其他应用程序
可能正在使用该 5432 端口。

试试这个：

con = pg2.connect(database='db', user='postgres', password='',port=5433)

This issue arises because sometimes postgres connects on
localhost-5433 instead of localhost-5432 as some other application
might be using that 5432 port.

Try this:

con = pg2.connect(database='db', user='postgres', password='',port=5433)
回复 原文
~没有更多了~

关于作者

无敌元气妹

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

胡图图

文章 0 评论 0

zt006

文章 0 评论 0

z祗昰~

文章 0 评论 0

冰葑

文章 0 评论 0

野の

文章 0 评论 0

天空

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文