Trouble connecting to PostgreSQL in python with psycopg2

Question

I am having problems connecting to my database through postgreSQL3 version 9.5. However, after running the code below:

import psycopg2 as p

con = p.connect("dbname ='dvdrental' user = 'myusername' host ='localhost' password ='somepassword'")
cur = con.cursor()
cur.execute("select * from title")
rows = cur.fetchall()

I get this error message:

psycopg2.OperationalError: could not connect to server: Connection refused 
(0x0000274D/10061)
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

For background information, I accidentally downloaded the newest version of PostgreSQL, and it connected to the part 5432. I need it to connect to PostgreSQL port 5433. I do not know how to do that. How can I solve this DB problem? Is this a PostgreSQL problem or a python problem?

If you installed a new version: the new version comes with a default postgres.conf file that only listens on the unix-domain-socket, not localhost or any other ip socket. — wildplasser
– wildplasser, Commented Jul 5, 2017 at 19:16
You can force connection over the unix-domain socket by specifying its directory instead of the hostname: host ='/tmp' (its default location, but often somewhere in /var/... ) — wildplasser
– wildplasser, Commented Jul 5, 2017 at 19:22

wildplasser · Accepted Answer · 2017-07-05 19:37:22Z

7

Check the listen adresses of postgres, using netstat (from the shell):

plasser@pisbak$ netstat -nl |grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     9002     /tmp/.s.PGSQL.5432
plasser@pisbak$

If nothing shows up, Postgres is not listening on port 5432.

answered Jul 5, 2017 at 19:37

wildplasser

44.4k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dubbbdan · Accepted Answer · 2017-07-05 18:20:55Z

2

what if you add port = '5433' to your p.connect line?

import psycopg2 as p

con = p.connect("dbname ='dvdrental' user = 'myusername' host ='localhost' password ='somepassword' port='5433'")
cur = con.cursor()
cur.execute("select * from title")
rows = cur.fetchall()

answered Jul 5, 2017 at 18:20

dubbbdan

2,7503 gold badges29 silver badges44 bronze badges

4 Comments

rollinstonegathersnomoss Over a year ago

I just tried that, and I get this following error: 'psycopg2.ProgrammingError: invalid dsn: invalid connection option "'port"'

dubbbdan Over a year ago

That is strange. port is one of the basic connection parameter.

dubbbdan Over a year ago

Maybe you could try passing a set of keyword arguments, rather than a connection string. i.e. con = p.connect(dbname="dvdrental", user = "myusername", host ="localhost", password ="somepassword", port="5433")

rollinstonegathersnomoss Over a year ago

Ah I got it to work finally. It was the username that threw me off. Thanks everyone!

Collectives™ on Stack Overflow

Trouble connecting to PostgreSQL in python with psycopg2

2 Answers 2

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related