pg_hba.conf for Postgresql from Windows Anaconda

Question

I've just installed Postgressql (9.5.4 vc14_0) and Psycopg2 (2.7.5 py36h74b6da3_0) and I'm trying to use them within my Anaconda environment on Windows 10.

Whenever I run psycopg2.connect("host=localhost user=postgres") in a python interpreter or just psql on the command line I get this error:

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?

I've looked at other Stackoverflow questions regarding this, some answers say to look into a pg_hba.conf file although I haven't be able to find any. Where can I find a pg_hba.conf file for my Postgresql in my Anaconda Environment for Windows? If I have to make one, what should go in it? I haven't seen another SO question that uses the same Postgresql-Anaconda-Windows setup that I'm using.

Also I've looked into services.msc but haven't seen a service regarding postgresql.

The obvious question: is the Postgres Windows service running? The `pb_hba.conf` is always located in the data directory. The graphical installer asks for the location during installation. — a_horse_with_no_name, Dec 10 '18 at 07:09
I assume since it's not showing up in `services.msc` that it's not running. My installation is in `C:\Users\username\AppData\Local\Continuum\miniconda3\pkgs` but in that directory there's only an `info` and `Library` directory and no `data` subdirectory within those. — user2361174, Dec 10 '18 at 07:56
Is it possible to install postgres through conda? I only ran `conda install -c anaconda postgresql`and I'm not sure if I'm missing anything else. — user2361174, Dec 10 '18 at 08:00
I just took whatever version Conda gave me, is there a specific version on Conda that works best? — user2361174, Dec 10 '18 at 17:32

kerwei · Answer 1 · 2019-04-15T04:32:28.140

I just ran into this error as well. Similar to what you did, I followed the installation page, ran conda install -c anaconda postgresql and received the same error in your post.

I have used postgresql through direct installation before, in osx and as far as I can recall, you could just psql into the database once the installation is complete.

Maybe this isn't the case for anaconda installations. I overcame this by initializing a new database system in a new, empty folder. In my case, I created a new folder in "\AppData\Local\conda\"

-- Initialize the database system
pg_ctl init -D <path_to_your_database_system>

-- Start the database
C:/Users/kerwei/AppData/Local/Continuum/anaconda3/envs/py36/Library/bin/pg_ctl -D <path_to_your_database_system> -l logfile start

NOTE: After playing around with for awhile, I realized that once you exit the conda environment, the database instance gets terminated too - without properly shutting down. As I don't use it for production stuffs, it doesn't really bother me. However, further steps can be taken to include the database booting and shutting down during conda activate or conda deactivate to make it less cumbersome.

pg_hba.conf for Postgresql from Windows Anaconda

1 Answers1