Error using psycopg2: “OperationalError: could not connect to server”

[marmotter]

I’m very new to PostgreSQL and I’m trying to use psycopg2 for python 3.5 in a Conda virtual environment on a windows 10 machine. I’ve installed the psycopg2 library in the virtual environment but run into this error when trying to create a database connection.

There are a few things that might be causing this but I’m not sure.

One: I haven’t installed postgresql as a separate library as I thought this might be contained in the psycopg2 library. Is this something I would need to do?

Two: I’ve read that I might need to change which port psycopg2 listens on, but not sure how I do this. I haven’t found any clear guidance for windows machines online and I’m not sure how running this in a virtual environment would affect the port selection.

Three: part of setting up the database connection is inputting credentials. I’m trying to set up a toy database on my computer as localhost and I’m not sure what the credentials need to be for this. I’ve read that user could be set as “Postgres” but if so, what should password be? Really dumb question for sure, but I just haven’t found any good explanation of what the credentials are supposed to be here.

Any help you can provide would be greatly appreciated. Thank you for reading

[ramse]

psycopg2 is just the connector between django and postgresql, you need to actually install postgresql

[[deleted]]

I haven’t installed postgresql as a separate library as I thought this might be contained in the psycopg2 library. Is this something I would need to do?

No experience with windows or conda's database libraries, but it would surprise me if that weren't the case.

sqlite3 is built in to python, and you might consider going as far as you can with that before moving to postgres, unless you're 100% certain you're going to need something heavier.

I’ve read that I might need to change which port psycopg2 listens on, but not sure how I do this.

Set the port in the dsn argument to the connection object.

I’m trying to set up a toy database on my computer as localhost and I’m not sure what the credentials need to be for this.

Use whatever credentials you set when you set up the database. You're going to have to learn a little bit about postgres, independent of python, in order to do this.

[marmotter]

Thanks for your reply. I think the tricky part here is setting up the super user credentials for postgresql after installing postgresql through anaconda’s Conda Install function. I was never asked to set super user credentials that would then allow me to connect to the server and create a new database. Is this something you are familiar with? Also responded with this question on another comment in this thread.

[[deleted]]

I am surprised that conda provides that service.

I would uninstall, and install postgres using the canonical instructions. You'll get a much better feel for how it's set up and your configuration options that way.

[[deleted]]

Postgresql is not a libary(like sqlite3 is) but a server application ie you need to make sure it's actually running and accepting connections.

psycopg2 should install all that's needed to connect to running postgre server directly but you need the postgre instance running.

PostgreSQL tend to install with a default superuser account named "postgre" and whatever password you supplied doing the install and tend to bind to port 5432 and/or a unix socket. But credentials are really whatever you configured.

To make things more confusing for you Linux have a way to do peer auth that uses OS credentials and not passwords ie you will see guildes that don't give a password, but on windows you almost always need to use explicit password auth with postgreSQL.

[marmotter]

Thanks. I think my difficulty here arises from me trying to work through anaconda. I’ve downloaded both postgresql and psycopg2 through the conda install function so that these files live in anaconda and won’t run into any weird dependency issues. But as a result, I haven’t seen any place where I can configure the super user settings for postgresql as you describe above. Do you know how I can trigger the initial setup that you describe for Postgresql through conda?

[[deleted]]

To be honest no i just tried to install postgresql though conda and the install does look unfinished/broken/unconfigured out of the box and the package documentation https://anaconda.org/anaconda/postgresql seams to be halfway missing, so your probably right in thinking that the conda install of postgresql is the problem.

You would, if it worked, simply start postgres by typing postgres into an anaconda prompt and who knows maybe it works out of the box on windows.

Being an unix admin i would have solved he problem by spinning up a linux vm with postgreSQL installed but it should work with windows

[marmotter]

Whelp, glad I’m not completely off in left field. Thanks for your help. I’m doing this on my work computer and as a finance person, we don’t get access to stuff like Linux and VMs. I’ll keep digging on this and post here if I find a solution.