Subscribe to this thread
Home - General / All posts - trouble connecting to postgresql directly from MF
csb
133 post(s)
#16-Nov-17 23:44

Hello. I am having trouble connecting to a PostgreSQL server from MF and Radian.

Problem details.

On my workstation (WS1) I created a .MAP with two datasources, one is MS SQL the other is PostgreSQL. This works great. On a new workstation I installed MF 9.0.163.8 x64 and then copied the .MAP file from WS1 to WS2. The Map file connects to MSSQL on WS2, but not PostgreSQL. Strange, because the same .MAP file works on WS1.

To troubleshoot:

I successfully made a telnet connection from WS2 to the PostgreSQL server.

I installed PGAdmin on WS2 and was successfully able to connect to the Postgresql server from WS2.

I installed Radian 9.0.163, and even Manifold 8.0.30.0, thinking maybe MF only installation was the problem.

I copied libpq.dll to the MF folder and the c:\program files\manifold\v9.0\Bin64 folder.

I tried and failed to open the connection to PostGreSQL with a fresh new project in Radian and MF

I read the docs, and it says "direct connection" no other software needed.

I installed psqlodbc driver and created an ODBC DSN, this failed with "no pg_hbba.conf entry for host" until I selected SSL = ALLOW. I then created an ODBC Datasource in MF to the PostgreSQL server. Yay, MF connects via ODBC. boo...it is via ODBC :(

Questions:

Is there an option to "allow SSL" in the MF connection to PostgreSQL that I missed on WS2?

Why would MF behave different on WS1 than W2?

Anyone know what is going on ?

Dimitri


7,413 post(s)
#17-Nov-17 09:11

Why would MF behave different on WS1 than W2?

Future behaves exactly the same, always. You have to look at what are the differences between WS1 and WS2. You haven't mentioned anything at all about those two different machines. Example: are they both using Windows 10 installed from exactly the same distribution at the same time with absolutely the same programs and administrative history on each?

The Map file connects to MSSQL on WS2, but not PostgreSQL. Strange, because the same .MAP file works on WS1.

Why would you expect the same references to work on two different machines if the two different machines are not identical in all respects?

Let's simplify for an easily-understood example:

On WS1 I have a folder called C:\data and in that folder I have files called books.mdb and chinook.gpkg I launch Future and create a project with two data sources, one that links to books.mdb and a second data soruce that links to chinook.gpkg. I save the .map file.

I move the .map file to WS2. There I have a folder called C:\data that contains a file called books.mdb. I also have a C:\data\gpkg folder that contains a file called chinook.gpkg. I open the .map file and it opens the .mdb data source just fine, but fails to open the GPKG data source. Why? The problem is not some limitation within Future that prevents GPKG data sources from working on WS2, it is the simple error of not having the chinook.gpkg in the C:\data folder where it was on WS1.

Your problem is that something is different between WS1 and WS2. Probably many things are different. Your task is to identify what the differences are that matter. These probably fall into one or more of the following three categories:

1. Errors involving paths and other system organizational issues, such as not having a file where an absolute path or relative path expects it to be.

2. Failure to configure systems properly, such as having all required dlls on one machine but not on the other machine.

3. Windows administration issues such as permissions and system policies.

csb
133 post(s)
#17-Nov-17 15:54

well yes. The machines are completely different.

WS1=W10, I7, 16gb ram, 250gb ssd, 10tb disk storage

WS2= W2kr2 server, Xeon, 4gb ram, 500gb disk.

Why would you expect the same references to work on two different machines if the two different machines are not identical in all respects?

I expected the references would work, because in my experience, connection strings to a database server are identical no matter from what machine they are run. However, I admit that obviously my experience is not complete knowledge, so i ask the question.

I re-read, installation topics in the manual. I re-verified that the required c++, and .net packages are installed.

What other required DLL's ? Is there something required not mentioned in the manual?

I am logged in as the Administrator.

The paths to MF are identical C:\manifoldInstalls\manifold-future-9.0.163.8-x64\manifold-future-9.0.163.8-x64\bin64

After MF runs, what other paths matter? I am trying to make a connection to a remote database, not reference a local file...

I can connect to the database from WS2 using tools other than MF, so i can be reasonably sure that it is not a firewall or network issue. I can even connect to the database from MF using ODBC.

So to simplify my question:

What issues might interfere with MF connecting directly to a remote PostgreSQL database, while allowing MF to connect to the same database server via ODBC?

Dimitri


7,413 post(s)
#18-Nov-17 10:33

What other required DLL's ? Is there something required not mentioned in the manual?

Of course. At a minimum you'll need the PostgreSQL DLLs (see below).

Future is a beta and as helpful as it would be for Future documentation to describe PostgreSQL installation, admin and interoperability, the first task for the Future manual is to cover Future. Hard enough to keep up with the many rapid, changes these days. :-)

I expect that as Future documentation gets more mature and the very intense pace of change slows down a bit, then Future documentation will acquire example sections (as older Manifold versions often have) that show step by step how to install PostgreSQL and other DBMS packages (usually, the free versions of Oracle, SQL Server and DB2) as "hello world" examples of installing those DBMS packages and then connecting to them from Manifold.

But until then, for installing and administering PostgreSQL, I recommend the usual FOSS documentation, which is very helpful. Don't take basic installation for granted, by the way, because depending on how you have installed PostgreSQL you could have issues with things like permissions. This, for instance:

I expected the references would work, because in my experience, connection strings to a database server are identical no matter from what machine they are run.

...is taking way too much for granted. Yes, sure, connection strings might be similar but your login is different and you could be hitting that server from a different role.

For Radian/Future/Viewer integration / interoperability with PostgreSQL I recommend a careful search of this forum. Many people here use PostgreSQL, as it is a fine product and works very well with Radian technology so no doubt many can help you with the details.

I only use PostgreSQL installations that somebody else, more knowledgeable than me, has already set up, so I cannot guide you in that as well as others.

I would recommend visiting theProduct Downloads page and reading the section on "DLLS for Popular Open Source Packages" and downloading and installing the DLLs as recommended.

After MF runs, what other paths matter? I am trying to make a connection to a remote database, not reference a local file...

I gave the example of referencing a local file as a really simple case to emphasize the need to clear your mind of worrying if Future is functioning correctly and instead to focus on the details of the rest of your installation. My best guess is a) missing required *PostgreSQL* dlls, b) incorrect server configuration, c) permissions or network access or roles issues, or something similar.

What issues might interfere with MF connecting directly to a remote PostgreSQL database, while allowing MF to connect to the same database server via ODBC?

Same as what I wrote in my prior post, pretty much rephrased as a) through c) above (repeated below). Without looking over your shoulder and knowing every detail it's hard to zero in closer. My advice, however, would be to abstract the question slightly. Ask yourself... Given two completely different machines that are connected by a network, what issues might interfere with an application on one machine connecting to a database on a different machine with a native DBMS connection while connecting just fine through ODBC? Also, look closely at the two different machines. Are there two different versions of PostgreSQL ... either server or client dlls?

You could try some variations, like installing and running Future or Future Viewer on the remote machine to see if it can connect to the PostgreSQL database without going through the network. If that works, you can look into your network configuration.

1. Errors involving paths and other system organizational issues, such as not having a file where an absolute path or relative path expects it to be.

2. Failure to configure systems properly, such as having all required dlls on one machine but not on the other machine.

3. Windows administration issues such as permissions and system policies.

csb
133 post(s)
#18-Nov-17 16:52

yes you are right.

Reading the product download page your suggested helped. I thought (wrongly) that i had already done those things. Thanks!

adamw


10,447 post(s)
#21-Nov-17 15:19

It looks from your post that the problem is now fixed, but I will just leave one tip for whoever will stumble upon this thread searching for solutions for similar issues later:

I guess the issue was related to LIBPQ.DLL not loading.

LIBPQ.DLL that you copied into the Bin64 folder of Future uses other DLLs, so they should also be either copied or put into the system path, otherwise LIBPQ.DLL won't be able to locate them and won't load.

Normally, you never figure out which DLLs depend on which, you either locate where LIBPQ.DLL is and put that location into the PATH environment variable, or you download some package (like the ones here: Product Downloads, scroll down to DLLs for Popular Open Source DBMS Packages) which contains all the required DLLs and unpack + copy the contents of that package into Bin and / or Bin64.

In any case, the very first step of diagnosing the issue is to check the log window. If the issue is the one above, attempting to connect to a PostgreSQL data source will report that LIBPQ.DLL failed to load.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.