Can't connect the postgreSQL with psycopg2

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!

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"
)

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

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:

Try change port to 5433 instead of 5432

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/')

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

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.

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

and host :'/cloudsql/::

It should work for private Ip postgresql on gcp

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