Unable to map ms access mdb file, postgresql crashes

[clustermass]

Hello, I am trying to map ms access mdb file to use with postgresql database. I am running clean fresh installs of Ububntu server 22.04 I tried postgres 13 and 14 version. For 14 version I downloaded latest stable build and compiled it, for postgres 13 I downloaded whatever version ubuntu repo has. In both cases, my postgres db crashes when I try to execute initial query that should link file. I tried different mdb files, even created simple one myself with only one table and one row, and the result is always the same:

2022-08-07 23:26:50.434 UTC [6081] DETAIL: Failed process was running: CREATE SERVER myserver FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource '/tmp/db/test2.mdb', format 'ODBC'); 2022-08-07 23:26:50.434 UTC [6081] LOG: terminating any other active server processes 2022-08-07 23:26:50.435 UTC [9560] WARNING: terminating connection because of crash of another server process 2022-08-07 23:26:50.435 UTC [9560] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-08-07 23:26:50.435 UTC [9560] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-08-07 23:26:50.441 UTC [6081] LOG: all server processes terminated; reinitializing 2022-08-07 23:26:50.458 UTC [9588] LOG: database system was interrupted; last known up at 2022-08-07 23:23:28 UTC 2022-08-07 23:26:50.486 UTC [9588] LOG: database system was not properly shut down; automatic recovery in progress 2022-08-07 23:26:50.487 UTC [9588] LOG: redo starts at 0/1F89598 2022-08-07 23:26:50.489 UTC [9588] LOG: invalid record length at 0/1FA6638: wanted 24, got 0 2022-08-07 23:26:50.489 UTC [9588] LOG: redo done at 0/1FA6600 2022-08-07 23:26:50.500 UTC [6081] LOG: database system is ready to accept connections 2022-08-07 23:30:48.958 UTC [6081] LOG: server process (PID 9675) was terminated by signal 11: Segmentation fault 2022-08-07 23:30:48.958 UTC [6081] DETAIL: Failed process was running: CREATE SERVER myserver FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource '/tmp/db/Northwind.MDB', format 'ODBC'); 2022-08-07 23:30:48.958 UTC [6081] LOG: terminating any other active server processes 2022-08-07 23:30:48.960 UTC [9592] WARNING: terminating connection because of crash of another server process 2022-08-07 23:30:48.960 UTC [9592] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-08-07 23:30:48.960 UTC [9592] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-08-07 23:30:48.965 UTC [6081] LOG: all server processes terminated; reinitializing 2022-08-07 23:30:48.983 UTC [9678] LOG: database system was interrupted; last known up at 2022-08-07 23:26:50 UTC 2022-08-07 23:30:49.013 UTC [9678] LOG: database system was not properly shut down; automatic recovery in progress 2022-08-07 23:30:49.014 UTC [9678] LOG: redo starts at 0/1FA66B0 2022-08-07 23:30:49.015 UTC [9678] LOG: invalid record length at 0/1FA6740: wanted 24, got 0 2022-08-07 23:30:49.015 UTC [9678] LOG: redo done at 0/1FA6708 2022-08-07 23:30:49.023 UTC [6081] LOG: database system is ready to accept connections

I am using ODBC driver that I installed as part of odbc-mdbtools package. How can I debug it further? Or any suggetions are appreciated.

[pramsey]

There shouldn't be anything in there that would crash the backend, so my suspicion is a bad mix'n'match install. A version of the extension that doesn't match the pgsql version you are putting it in. Or built against a msvc runtime that doesn't match the one you have. Or some other thing of that sort. Nothing I can really help with I'm afraid.

[clustermass]

Ok I figured it out. I did fresh reinstall again with Ubuntu server 22.04 and postgres 14 I think the portion that I missed last time was the config file specifically needed to work with MS Access mdb files. The unixodbc driver requires config file in /etc/odbcinst.ini, and the tricky part there is to specify the correct spelling in brackets as well as point libmdbodbc.so file with full path. (this helped alot: https://ubuntuforums.org/showthread.php?t=2066628)

So mine looks like this:

[Microsoft Access Driver (*.mdb)] Description = MDB Tools ODBC drivers Driver = /usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so Setup = libmdbodbc.so FileUsage = 1 Usage Count = 1 Description = MDB Tools ODBC drivers

And now it works. I will try to find some time to record youtube video on this topic, as I spent 5 days and numerous virtual machines to make it work, as I am not DB expert and mainly do web development with JS. Thanks a lot for your hard work

[robe2]

@pramsey I think you can close this one out. User solved his own problem.