无法将 postgreSQL 与 psycopg2 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5500332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't connect the postgreSQL with psycopg2
提问by Lanston
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"?
- My postgreSQL is running
- My listeningport is 5432 for sure
- root@lanston-laptop:~# psql -l Password:
- 我的 postgreSQL 正在运行
- 我的监听端口肯定是 5432
- 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
Thanks a lot!
非常感谢！
回答by Tometzky
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/.
psycopg2 使用的 libpq 期望 Postgres 套接字位于其中，/var/run/postgresql/但是当您从源代码安装 Postgres 时，默认情况下它位于/tmp/.
Check if there is a file /tmp/.s.PGSQL.5432instead of /var/run/postgresql/.s.PGSQL.5432. Try:
检查是否有文件/tmp/.s.PGSQL.5432而不是/var/run/postgresql/.s.PGSQL.5432. 尝试：
conn=psycopg2.connect(
database="mydb",
user="postgres",
host="/tmp/",
password="123"
)
回答by Dhanalal Bhardwaj
Only this solved my problem, make a symbolic link to the /tmp/.s.PGSQL.5432:
只有这样解决了我的问题，建立一个指向 /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
回答by radeklos
Try change port to 5433 instead of 5432
尝试将端口更改为 5433 而不是 5432
回答by x-yuri
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.connectdirectly, but use third-party software.
本来是想对Tometzky的回答做个评论的，不过好吧，我这里有很多话要说……关于你不psycopg2.connect直接打电话，而是使用第三方软件的情况。
tl;dr
tl;博士
Set unix_socket_directoriesin postgresql.confto /var/run/postgresql, /tmp, and restart PostgreSQL.
设置unix_socket_directories在postgresql.conf给/var/run/postgresql, /tmp，并重新启动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:
我尝试了发行版存储库中的 PostgreSQL 9.2 (CentOS 7) 和 9.5 (Ubuntu Xenial)，PostgreSQL 存储库中 CentOS 7 上的PostgreSQL9.3、9.4、9.5、9.6、10，PostgreSQL 存储库中Ubuntu Xenial 上的PostgreSQL9.6、10 。其中只有9.3只听/tmp：
$ systemctl stop postgresql-9.4 && systemctl start postgresql-9.3
$ lsof -aUp $(ps --ppid 1 -o pid= -o comm= | awk ' == "postgres" || == "postmaster" {print }')
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 ' == "postgres" || == "postmaster" {print }')
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
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-psycopg2installed from CentOS's baseor updaterepo. It links dynamically to libpqthat OS provides. With 9.3 and 9.4 installed OS provides 9.4's version:
这对 来说没什么大不了的psql，只是运行匹配的二进制文件的问题。但是，例如，如果您是python-psycopg2从 CentOSbase或updaterepo安装的。它动态链接到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/
But libpqthat comes with PostgreSQL 9.4 looks for socket in /var/run/postgresqlas opposed to 9.3:
但这libpq与 PostgreSQL 9.4 一起查找套接字/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
The solution comes from postinstall scripts of corresponding packages:
解决方案来自相应包的postinstall脚本：
$ 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 [ "" -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:
暂时删除 9.4 的替代方案：
$ 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:
完成后要么重新安装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
If you install psycopg2with pipon the other hand, it by default installs precompiled package which comes with its own libpq, which looks for socket in /var/run/postgresql:
另一方面，如果您psycopg2使用 with安装pip，它默认安装预编译包，该包带有自己的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
The solution is to ask pipto not install precompiled package, and make pg_configof the proper version of PostgreSQL available:
解决方案是要求pip不要安装预编译包，并pg_config提供正确版本的 PostgreSQL：
$ PATH=/usr/pgsql-9.3/lib:$PATH pip install --no-binary psycopg2 psycopg2
You can even add --no-binaryswitch to requirements.txt:
您甚至可以将--no-binaryswitch添加到requirements.txt：
psycopg2==2.7.3.2 --no-binary psycopg2
unix_socket_directories
unix_socket_directories
The easier option though is to make use of unix_socket_directoriesoption:
更简单的选择是使用unix_socket_directoriesoption：
回答by rikb
a few years later, using the EnterpriseDB 'graphical' installon OSX 10.8, and pip install of psycopg2 (after linking the /Library/...dylib's as described here) i had this same issue.
几年后，使用OSX 10.8 上的EnterpriseDB“图形”安装和 psycopg2 的 pip 安装（在链接 /Library/...dylib 之后，如此处所述）我遇到了同样的问题。
for me the correct connect command was conn = psycopg2.connect('dbname=DBNAME user=postgres password=PWHERE host=/tmp/')
对我来说正确的连接命令是 conn = psycopg2.connect('dbname=DBNAME user=postgres password=PWHERE host=/tmp/')
回答by EliadL
Having this happen to me after a brew upgrade, I googled for brew .s.PGSQL.5432.
发生这种情况后brew upgrade，我用谷歌搜索了brew .s.PGSQL.5432.
Per the suggestion in this answerI 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 answerI ran:
我在谷歌上搜索了那个致命错误，并根据这个答案中的建议我跑了：
brew postgresql-upgrade-database
That solved it for me.
那为我解决了。
