Problem connecting to PostgreSQL db - Win 10, Sofa 1.4.5, PostGreSQL 9.4

606 views
Skip to first unread message

Andreas Mahn

unread,
Sep 21, 2015, 8:21:29 PM9/21/15
to sofastatistics
Hi there,

TI've been tying to connect to a local PostgreSQL data base, with no luck so far.

When I try to connect, I get the following error message:
---------------------------
Unable to use the selected project file. Please check name of file and its contents using default.proj as example.
Caused by error: Unable to set proj dic.
Caused by error: Unable to get dbe resources.
Caused by error: Unable to get dbe_resources. Orig error: Unable to connect to PostgreSQL db.
Caused by error: could not translate host name "5433" to address: Unknown host

---------------------------

I've checked the .proj file and it looks alright to me.
Tried variation of hostname: "5433", "localhost", "remote:5433", and "localhost:5433"
Tried different user names (one of them the default, postgres).
PostgreSQL bin path is added to path system variable.
Tried running Sofa "As Administrator".

No luck...

Any hints are much appreciated.
Thanks, Andreas

Grant Paton-Simpson

unread,
Sep 22, 2015, 1:46:48 AM9/22/15
to sofasta...@googlegroups.com
Hi Andreas,


On 22/09/15 12:21, Andreas Mahn wrote:
Hi there,

TI've been tying to connect to a local PostgreSQL data base, with no luck so far.
We should be able to figure this out. Are you comfortable playing around with simple Python scripts? If so we could try a couple of things.

I am able to connect to a Postgresql 9.4 database using SOFA on Ubuntu with host being localhost and user being postgres. So it shouldn't be impossible.

All the best,
Grant

--

---
You received this message because you are subscribed to the Google Groups "sofastatistics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sofastatistic...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Andreas Mahn

unread,
Sep 22, 2015, 4:46:44 AM9/22/15
to sofastatistics
Hi Grant
I'm a complete newbie to Python...still I'm happy to give this a crack. What do you suggest..?
I suspect the culprit may be Win 10.

Thanks, Andreas

Grant Paton-Simpson

unread,
Sep 22, 2015, 5:48:58 AM9/22/15
to sofasta...@googlegroups.com
Before we go any further, are you sure the Postgresql server is actually running at the time you are trying to connect? Do you have any other interface into postgresql so you can check?

Assuming it is, it would be good if you could install Python 3.5 and psycopg2 and idle3. I'm not sure how you do that on Windows but IIRC it isn't too hard. SOFA runs on Python 2.6/7 but I prefer using Python 3 for everything else these days and it will be fine for the test I have in mind. Plus you can play with it afterwards ;-)

Good luck!

Andreas Mahn

unread,
Sep 22, 2015, 6:04:29 AM9/22/15
to sofasta...@googlegroups.com

Yes the server is definitely running. I'm using QGis to tap into my database (and I'm planning to use SOFA to do some data analysis).
I'll give Python 3.5 a try. ..we'll see what happens:)
I'll be away for a few days but will get onto it when I'm back.

Thanks, Andreas


You received this message because you are subscribed to a topic in the Google Groups "sofastatistics" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sofastatistics/gkxfsxT6HKM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sofastatistic...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.
--

.- -. -.. .-. . .- ...   -- .- .... -.
Andreas Mahn
Wellington, NZ
menta...@gmail.com

04 3899423
021 375 165

Grant Paton-Simpson

unread,
Sep 22, 2015, 6:10:46 AM9/22/15
to sofasta...@googlegroups.com


On 22/09/15 22:04, Andreas Mahn wrote:

Yes the server is definitely running. I'm using QGis to tap into my database (and I'm planning to use SOFA to do some data analysis).
I'll give Python 3.5 a try. ..we'll see what happens:)

Ah - QGis has Python bindings so you might be best sticking with python2.7 and idle2. That way you can play with QGis and Python afterwards if interested.

I'll be away for a few days but will get onto it when I'm back.

Cool

Andreas Mahn

unread,
Oct 11, 2015, 11:22:47 PM10/11/15
to sofastatistics
Hi Grant,

finally managed to sink some time into this again. Got Python 2.7 and psycopg2 installed (had to use the 32bit version I guess in order to match python 2.7).

But where to next..?

Cheers, Andreas

Grant Paton-Simpson

unread,
Oct 16, 2015, 7:25:53 PM10/16/15
to sofasta...@googlegroups.com
Hi Andreas,

Sorry for the delay - I just went through a whole lot of grief getting postgresql working (again) on my machine. For some unknown reason it shifted from port 5432 to 5434. I've documented what I've learned for myself so it wasn't all a disaster. Anyway, the attached script should be saved somewhere and the password changed to your own database password. Does it raise any errors if you run it?

Note - don't share your database password with this mail group by accident ;-)

All the best,
Grant
connect_to_pg.py

Andreas Mahn

unread,
Oct 18, 2015, 9:04:01 PM10/18/15
to sofastatistics
Hi Grant,

thanks for the script...it seems to be running alright, without errors. Still, no progress on connecting with SOFA though. I should note that my data base server listens on port 5433 (also for reasons unknown to me).

Thanks, Andreas

Grant Paton-Simpson

unread,
Oct 18, 2015, 11:26:12 PM10/18/15
to sofasta...@googlegroups.com
Hi Andreas,

Hmm - no errors. Did you specify the port in the script or did you run it in the original form I sent? From the psycopg2 docs:

The basic connection parameters are:

  • dbname – the database name (only in the dsn string)
  • database – the database name (only as keyword argument)
  • user – user name used to authenticate
  • password – password used to authenticate
  • host – database host address (defaults to UNIX socket if not provided)
  • port – connection port number (defaults to 5432 if not provided)
so I'm intrigued to hear the connection was successful without and explicit setting of the port to 5433.

In any case I have checked my code and unfortunately there is nothing in 1.4.5 which allows specification of a different port than the default. My mistake sorry but it is fixed in the 1.4.6 development branch (as of a few minutes ago ;-)). But in the meanwhile you can manually edit "dbe_plugins/dbe_postgresql.py" so that right near the end of the file:
        con_dets_pgsql = {"host": pgsql_host, "user": pgsql_user, 
                          "password": pgsql_pwd}

is replaced with:

con_dets_pgsql = {"host": pgsql_host, "user": pgsql_user, "password": pgsql_pwd, "port": 5433}
Success?

All the best,
Grant

Andreas Mahn

unread,
Oct 19, 2015, 10:06:12 PM10/19/15
to sofastatistics
Hi Grant,

me again...sigh...
Still no luck.

The script works if I add the port. I've worked out that I need to run that script in the Python shell...sorry newbie error on my behalf. So when I hit F5 in IDLE without the port number, (ie as you sent the script) I get the same error message as I get in SOFA when I try try to connect to my data base. When I add the port number, the shell just prints =============RESTART ===========. Presumably that's good.

Then I've altered dbe_postgresql.py as you suggested. Then, when I then enter the Projects Settings dialog it won't let me confirm that dialog by clicking Update. (The button is not inactive, simply nothing happens when I press it). I guess con_dets_pgsql doesn't expect a fourth parameter? Just a guess...

I've also tried to make my database listen to port 5432. Also without luck - all the evidence points to a configuration file (postgresql.conf), and a batch file (pg_env.bat) which are supposed to set the port being listened to. Except in my case it seems that port 5433 is hard wired somewhere else.
Maybe I'll just wait until you release 1.4.6;)

Cheers, Andreas

Grant Paton-Simpson

unread,
Oct 20, 2015, 2:42:53 AM10/20/15
to sofasta...@googlegroups.com


On 20/10/15 15:06, Andreas Mahn wrote:
Hi Grant,
Hi Andreas,


me again...sigh...
Still no luck.
If I had a dollar for every time programming made me feel like that ... ;-)


The script works if I add the port. I've worked out that I need to run that script in the Python shell...sorry newbie error on my behalf. So when I hit F5 in IDLE without the port number, (ie as you sent the script) I get the same error message as I get in SOFA when I try try to connect to my data base. When I add the port number, the shell just prints =============RESTART ===========. Presumably that's good.
That's very good - it means my mental model about what is happening is correct which means I can reason about solving the problem.


Then I've altered dbe_postgresql.py as you suggested. Then, when I then enter the Projects Settings dialog it won't let me confirm that dialog by clicking Update. (The button is not inactive, simply nothing happens when I press it). I guess con_dets_pgsql doesn't expect a fourth parameter? Just a guess...
Just email me a copy of your dbe_postgresql.py file and there's probably a simple syntax error.


I've also tried to make my database listen to port 5432. Also without luck - all the evidence points to a configuration file (postgresql.conf), and a batch file (pg_env.bat) which are supposed to set the port being listened to. Except in my case it seems that port 5433 is hard wired somewhere else.
Yeah - don't worry about that - we can easily change what port we look for in SOFA.

Maybe I'll just wait until you release 1.4.6;)
I would be surprised if we can't fix this based on the file you send me (maybe to gr...@sofastatistics.com would make sense).

All the best,
Grant


Andreas Mahn

unread,
Oct 20, 2015, 4:23:16 PM10/20/15
to sofastatistics
Hi Grant,

just sent you the file, with change I made.

I've just had another play...If I add the pot number as a string (ie "port": u"5433") the dialog closes, but SOFA still won't connect.

Hope that helps as a pointer...

Thanks, Andreas

Grant Paton-Simpson

unread,
Oct 24, 2015, 4:50:21 AM10/24/15
to sofasta...@googlegroups.com
Hi Andreas,

I couldn't see anything wrong with your code so I'm a bit puzzled. The key thing to remember is that SOFA is basically doing the same things as the separate script you used to connect to PostgreSQL. So something must be different if one lets you connect and the other one doesn't.

Please send me at gr...@sofastatsistics.com your _internal/output.txt file - it might have a useful error message. Probably under /Users/username/sofastats/_internal

All the best,
Grant

Andreas Mahn

unread,
Oct 26, 2015, 5:05:03 PM10/26/15
to sofastatistics
Hi Grant,

just sent you the file.
Yes very puzzling indeed. Thanks so much for looking into this.

Cheers, Andreas

Grant Paton-Simpson

unread,
Oct 27, 2015, 5:10:38 AM10/27/15
to sofasta...@googlegroups.com
I think I fixed all of that in the dev version. How about I send you an early release and see if things start working?
Reply all
Reply to author
Forward
0 new messages