48

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!

Lanston
  • 10,286
  • 8
  • 31
  • 37

7 Answers7

64

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"
)
Tometzky
  • 20,923
  • 4
  • 58
  • 71
  • 5
    This is usually a problem if you are mixing a Debian/Ubuntu-packaged PostgreSQL installation with a hand-compiled one. Avoid that, unless you are prepared to deal with problems such as these. – Peter Eisentraut Mar 31 '11 at 20:43
  • 1
    Is there a way to get the socket back to /var/run/postgresql/ ? (I just edited a django source file to get my project to work again for now, but that isn't exactly a nice solution) I didn't install from source by the way... – Ciske Nov 24 '13 at 17:29
  • 6
    I had this problem on OSX when I replaced the built-in Postgres with the Homebrew one. I had to uninstall and reinstall psycopg2 to get it to use the correct libpq.dylib, which -- in turn -- used the correct unix socket path. – Roger Lipscombe Apr 14 '15 at 15:50
  • I had this problem after switching to PostgreSQL packages from [PGDG repository](https://yum.postgresql.org/repopackages.php). Adding [`--no-binary`](http://initd.org/psycopg/docs/install.html#disabling-wheel-packages-for-psycopg-2-7) to `requirements.txt` and adding `pg_config`'s dir to `PATH` resolved it. – x-yuri Feb 03 '18 at 21:57
10

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:

x-yuri
  • 13,809
  • 12
  • 96
  • 141
9

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

Dhanalal Bhardwaj
  • 131
  • 1
  • 3
8

Try change port to 5433 instead of 5432

radeklos
  • 1,978
  • 20
  • 18
  • This especially works in cases where a new postgres version was installed while a previous version was still running. Since postgres could not use port 5432, it fails up to the next available port, frequently 5433. – ClimbsRocks Jan 25 '21 at 21:52
3

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

Community
  • 1
  • 1
rikb
  • 584
  • 4
  • 18
2

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

Mariana Meireles
  • 323
  • 1
  • 8
1

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.

EliadL
  • 5,191
  • 2
  • 21
  • 38