Postgresql & psycopg2: database does not exist

Question

I am trying to establish a connection to a database like this:

psycopg2.connect(database="movies", user="lfcj", host="127.0.0.1");

my pg_hba.conf file has a line:

TYPE __ DATABASE___USER__ADDRESS___METHOD
local all lfcj peer

I am trying to use peer identification, and my SO user name is also lfcj.

When I log in postgresql like this, grant all privileges to lfcj, and run \list :

psql lfcj -h 127.0.0.1 -d movies 
Enter password:  'myPassword'

psql (9.4.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

movies=# GRANT ALL PRIVILEGES ON DATABASE  movies TO lfcj WITH GRANT OPTION;
movies=#\list

I get this information:

                            List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------+----------+----------+-------------+-------------+-----------------------
movies  | lfcj     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/lfcj         +
        |          |          |             |             | lfcj=C*T*c*/lfcj+

So: the database movies exists, lfcj is the owner and has all priviliges. But when I run:

psycopg2.connect(movies, lfcj);

it throws an error:

File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
connection_factory=connection_factory, async=async)
psycopg2.OperationalError: FATAL:  database "movies" does not exist

Any ideas?

Answer 1

Your connection string should look like this:

psycopg2.connect("dbname=test user=postgres password=secret")

The connect method will give you a connection to the database so you will probably need to store it in a variable. Typically like this:

conn = psycopg2.connect("dbname=test user=postgres password=secret")

You can also use a set of keywords like this:

conn = psycopg2.connect(database="test", user="postgres", password="secret")

Answer 2

Could you try connecting to the "postgres" database:

psql -d postgres

and then execute the following command:

show data_directory;

On 9.3.1.0-alpha1 that should return

/Users/USERNAME/Library/Application Support/Postgres93/var

Then check which databases exist on the server using the shorthand \l

If all that works, it seems that Postgresapp called initdb successfully, but createdb failed. You can do that manually, just execute createdb USERNAME from the terminal (not inside psql)

Answer 3

I had the same problem here.

I had two versions of postgres installed. One in port 5432 and the other in port 5433, as you can see here

When I tried to connect with:

conn = pg2.connect(dbname='my_db_name', user='my_user', host='localhost', port=5433, password='my_pass')

It worked and made the connection with all the databases within postgres version 9.5. When I switched to port 5432 I made the connection to the databases inside postgres version 11.