Menu ▾ ▴

Compiling Additional Extensions Using "postgresql-mingw-w64"

documentation (3) postgresql (3) extension (3) mingw-w64 (3) windows 7 (3) compiling (3) cross-compiling (3) win64 (3) win32 (3) 64-bit (3) 32-bit (3) sqlite_fdw (3) www_fdw (3) mysql_fdw (3) ldap_fdw (3) installer (3) gcc-4.8 (3) Foreign Data Wrappers (3) couchdb_fdw (3) oracle_fdw (3)
Gauthier Boaglio

Preliminary Note: In the following will be demonstrated, with two new extensions / Foreign Data Wrappers (mysql_fdw and ldap_fdw), how to build an extension using the ready-made MinGW-w64 compiler for the targeted architecture.

The resulting sources and compiled files can be found in the file repository of this project.

Two tutorials, one per architecture

(If you are experiencing problems during the process described in this document, feel free to send a ticket, and I'll try to help...)

Targeting 64-bit host tutorial

(Demo using "mysql_fdw" extension)

  • Unpack the postgresql-mingw-w64 ready-made build from this archive in /SOME/DIR
  • Change directory to /SOME/MING/DIR/mingw64/

  • Run the MSYS Shell: /SOME/MING/DIR/mingw64/msys/msys.bat

    !!! WARNING: Never run msys in RUNAS (Administrator) mode !!!

  • Some handy environment settings:
    (More accurate description about how to setup the environment here. Especially configuring correctly the MSYS /etc/fstab file).

    export ARCH="x86_64-w64-mingw32";
export MGWDIR="/SOME/MING/DIR/mingw64/"

  • Setup the PATH environment variable:

    export PATH=$PATH:$MGWDIR"bin":$MGWDIR"lib"
export PATH=$PATH:/usr/local/pgsql/bin:/usr/local/pgsql/lib

  • Download the mysql_fdw extension's sources

  • Unpack in /SOME/EXT/DIR

  • Handle extension's dependencies (need a release of MySQL):

    • If you don't have yet installed a MySQL server, download win64 binaries from here, or use the installer
      (Otherwise, use your current installation path for the next step...)
    • Unpack in /SOME/MYSQL/DIR/

    • Edit the extension's /SOME/EXT/DIR/mysql_fdw-master/Makefile to fit your MySQL install path:

      #MYSQL_CONFIG = mysql_config
#SHLIB_LINK := $(shell $(MYSQL_CONFIG) --libs)
#PG_CPPFLAGS := $(shell $(MYSQL_CONFIG) --include)

SHLIB_LINK = -L/SOME/MYSQL/DIR/lib/ -lmysql
PG_CPPFLAGS = -I/SOME/MYSQL/DIR/include/

  • Change directory to the extension's source directory and install

    export USE_PGXS=1
cd /SOME/EXT/DIR/mysql_fdw-master
make
make install

  • The installation should have resulted in copying:

    • mysql_fdw.dll -> /usr/local/pgsql/lib/
    • mysql_fdw.control -> /usr/local/pgsql/share/extension/
    • sql/mysql_fdw--1.0.sql -> /usr/local/pgsql/share/extension/

  • Then you have two options:

    • Using directly the enhanced provided gcc-compiled server:

      cp /SOME/MYSQL/DIR/lib/libmysql.dll /usr/local/pgsql/lib/     # Store ext deps
cd /usr/local/pgsql/
bin/initdb -Ddata                                # Do it only once
bin/postgres -Ddata                              # This starts the server
bin/createuser -s -r -W username                 # Do it only once

    • Inject the installed extension and its dependencies (DLLs) in an other already operational server:

      cp /usr/local/pgsql/lib/mysql_fdw.dll /YOUR/SERVER/ROOT/DIR/lib/
cp /SOME/MYSQL/DIR/lib/libmysql.dll /YOUR/SERVER/ROOT/DIR/bin/
cp /usr/local/pgsql/share/extension/mysql_fdw* /YOUR/SERVER/ROOT/DIR/share/extension/

/YOUR/SERVER/ROOT/DIR/bin/postgres -Ddata         # Start the server
Testing the extension

{ Example grabbed from here }

Start your MySQL server, and we're good to go:

Personally, I like EsayPHP - Development for testing purpose under Windows - Download.

-- Install the extension
CREATE EXTENSION mysql_fdw;

-- Create the foreign server, a pointer to the MySQL server.
CREATE SERVER mysql_svr 
    FOREIGN DATA WRAPPER mysql_fdw 
    OPTIONS (address '127.0.0.1', port '3306');

-- Grant other user's permissions to create and/or have access to foreign tables.
GRANT ALL ON FOREIGN DATA WRAPPER mysql_fdw TO some_user; -- do everything
-- GRANT USAGE ON FOREIGN DATA WRAPPER mysql_fdw TO some_user; -- just query
GRANT ALL ON FOREIGN SERVER mysql_svr TO some_user;
-- GRANT ALL ON FOREIGN SERVER mysql_svr TO some_user; -- just query

-- Create one or more foreign tables on the MySQL server. The first of 
-- these maps to a remote table, whilst the second uses an SQL query.
-- (Assuming you have a database named 'hr' on MySQL server side)...
CREATE FOREIGN TABLE employees (
    id integer,
    name text,
    address text)
    SERVER mysql_svr
    OPTIONS (table 'hr.employees');

CREATE FOREIGN TABLE overtime_2010 (
    id integer,
    employee_id integer,
    hours integer)
    SERVER mysql_svr
    OPTIONS (query 'SELECT id, employee_id, hours FROM hr.overtime WHERE year = 2010;');

-- Create a user mapping to tell the FDW the username/password to 
-- use to connect to MySQL, for PUBLIC. This could be done on a per-role basis.
-- (Assuming user 'some_user:some_passwd' exists on MySQL server side)...
CREATE USER MAPPING FOR PUBLIC 
    SERVER mysql_svr 
    OPTIONS (username 'some_user', password 'some_passwd');

-- Run a request.
SELECT * FROM employees;

Targeting 32-bit host tutorial

(Demo using "ldap_fdw" extension)

  • Unpack the postgresql-mingw-w64 ready-made build from this archive in /SOME/DIR
  • Change directory to /SOME/MING/DIR/mingw32/

  • Run the MSYS Shell: /SOME/MING/DIR/mingw32/msys/msys.bat

    !!! WARNING: Never run msys in RUNAS (Administrator) mode !!!

  • Some handy environment settings:
    (More accurate description about how to setup the environment here. Especially configuring correctly the MSYS /etc/fstab file).

    export ARCH="i686-w64-mingw32";
export MGWDIR="/SOME/MING/DIR/mingw32/"

  • Setup the PATH environment variable:

    export PATH=$PATH:/SOME/MING/DIR/bin:/SOME/MING/DIR/lib
export PATH=$PATH:/usr/local/pgsql/bin:/usr/local/pgsql/lib

  • Download the ldap_fdw extension's sources

  • Unpack in /SOME/EXT/DIR

  • Handle extension's dependencies:

    • Need a release of the regex library compatible with MinGW { download }
      ( OpenLDAP depends on it )

      cd ..../mingw-libgnurx-2.5.1
./configure --build=$ARCH --host=$ARCH
make
make install

cp /usr/local/bin/libgnurx-0.dll /usr/local/bin/libregex.dll

    • Need the libldap.dll, which can be obtained by compiling OpenLDAP

      • Get OpenLDAP from sources here

      • Some information the configure script will need:

        cd ..../openldap-2.4.38
export CC=$ARCH"-gcc"
export LDFLAGS="-L/usr/local/bin/ -lregex"

{!!!    
    Only 64-bit => patch the "configure" script:
        LINE 6736: # func_win32_libid shell function, so use a weaker test based on 'objdump'.
        LINE 6737: lt_cv_deplibs_check_method='file_magic file format pei*-i386(.*architecture: i386)?'         // Bad (32-bit specific) way of checking deps validity !!
        LINE 6737: lt_cv_deplibs_check_method='file_magic file format pei*-x86-64(.*architecture: i386)?'       // Turn check_method to 64-bit specific check !!
}

      • Configure and make the dependencies:

        ./configure --build=$ARCH --host=$ARCH --disable-slapd --disable-static --disable-backends
make depend

      • patch ./include/portable.h:

        LINE 1115: /* Define like ber_socklen_t if <sys/socket.h> does not define. */
LINE 1116: #define socklen_t int        
LINE 1116: //#define socklen_t int      // Comment the offending line (this is safe!)

      • patch ./libraries/liblutil/Makefile { !!! NOT NECESSARY if targeting Win64 !!! }:

        LINE 309: slapdmsg.res: slapdmsg.rc slapdmsg.bin
LINE 310: windres $< -O coff -o $@
LINE 310: $(ARCH)"-windres" $< -O coff -o $@        // Add arch specific prefix to binary

      • Terminate the build and install:

        make
make install

  • Change directory to the extension's source directory and install

    export USE_PGXS=1
cd /SOME/EXT/DIR/ldap_fdw-master
# ('ldap_fdw' depends on "libldap.dll" and "liblber.dll")
make LDFLAGS="-L/usr/local/bin -lldap -llber"
make install

  • The installation should have resulted in copying:

    • ldap_fdw.dll -> /usr/local/pgsql/lib/
    • ldap_fdw.control -> /usr/local/pgsql/share/extension/
    • sql/ldap_fdw--0.1.1.sql -> /usr/local/pgsql/share/extension/

  • Then you have two options:

    • Using directly the enhanced provided gcc-compiled server:

      cp /SOME/MYSQL/DIR/lib/libmysql.dll /usr/local/pgsql/lib/     # Store ext deps
cd /usr/local/pgsql/
bin/initdb -Ddata                                # Do it only once
bin/postgres -Ddata                              # This starts the server
bin/createuser -s -r -W username                 # Do it only once

    • Inject the installed extension and its dependencies (DLLs) in an other already operational server:

      cp /usr/local/pgsql/lib/mysql_fdw.dll /YOUR/SERVER/ROOT/DIR/lib/
cp /SOME/MYSQL/DIR/lib/libmysql.dll /YOUR/SERVER/ROOT/DIR/bin/
cp /usr/local/pgsql/share/extension/mysql_fdw* /YOUR/SERVER/ROOT/DIR/share/extension/

/YOUR/SERVER/ROOT/DIR/bin/postgres -Ddata         # Start the server
Testing the extension

{ Example grabbed from here }
If you don't have already chosen a LDAP server, I would recommend OpenLDAP for Windows - Download.

Start your LDAP server, and we're good to go:

CREATE EXTENSION ldap_fdw;

-- Then create a foreign server to connect to your LDAP server:

CREATE SERVER ldap_myldap_server
FOREIGN DATA WRAPPER ldap_fdw
OPTIONS ( address '127.0.0.1', port '389');

-- Create user mapping:

CREATE USER MAPPING FOR current_user
SERVER ldap_myldap_server
OPTIONS (user_dn 'cn=Manager,dc=maxcrc,dc=com', password 'manager_passwd');

-- Finally create a foreign table with a base DN pointing to some OU:

CREATE FOREIGN TABLE ldap_people (
   dn text,
   object_body text
)
SERVER ldap_myldap_server
OPTIONS (base_dn 'ou=People,dc=maxcrc,dc=com');

-- And list people...

SELECT * FROM ldap_people;

01_Piwik_img

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.