Building and Installing PostgreSQL Extension Modules

PostgreSQL imposes few formal constraints on extension module build procedures; these instructions assume a module written for the PGXS build system. Use of PGXS is pervasive among publicly-available extension modules.

Unix-like Platforms

PGXS originated on Unix-like systems, and it is easy to use there. Unpack the extension module archive and run these commands in the resulting directory:

make PG_CONFIG=path_to_postgresql_installation/bin/pg_config
make PG_CONFIG=path_to_postgresql_installation/bin/pg_config install

You may omit the PG_CONFIG overrides if running type pg_config in your shell locates the correct PostgreSQL installation. Subject to the ownership of the existing PostgreSQL installation directories, the second command will often require root privileges. These instructions also apply when building for Windows using the MinGW or Cygwin compilers. However, those Windows configurations have special needs and receive lighter testing than most; expect a greater incidence of build problems.

A common mistake is to specify the PG_CONFIG=... on the command line before the 'make', which does not work as the value is then overridden by the inner workings of makefiles.

Windows with Microsoft Visual Studio

The PostgreSQL project distributes Windows binaries built using the Microsoft Visual Studio toolset, and you can build similar binaries yourself. Such builds are not compatible with and do not include the PGXS infrastructure.

The PostgreSQL build system for Visual Studio does contain its own support for building the extension modules found in the contrib subdirectory of the core PostgreSQL distribution. One method of building a third-party module on Windows entails harnessing that build process. The other approach requires you to create your own Visual Studio project for your extension.

Building with a Visual Studio project file

It's not overly difficult to build an extension with Visual Studio by creating a project file. You must:

• Create the project
• Add the extension sources as .c files
• For each function you have a PG_FUNCTION_INFO_V1 for, i.e. it's to be exported as a C extension function, prepend PGDLLEXPORT before the Datum return value.
• Edit the project properties to:
  • Compile a DLL
  • Compile with plain C
  • Disable C++ exceptions
  • Do a Release build
  • Set the include path, adding:
    • include\server\port\win32_msvc
    • include\server\port\win32
    • include\server
    • include
  • Set the library path, adding the PostgreSQL lib dir
  • Add postgres.lib to the library link list
  • In the C preprocessor definitions add WIN32 (may not be required in later versions)

More detailed articles describing this process:

• Compiling PostgreSQL Extensions with Visual Studio on Windows
• Build PostgreSQL C Functions on Windows

Troubleshooting

Failure to define WIN32 causes:

9.3\include\server\pg_config_os.h(207): error C2011: 'timezone' : 'struct' type redefinition
1>          c:\program files\postgresql\9.3\include\server\pg_config_os.h(207) : see declaration of 'timezone'
1>c:\program files\postgresql\9.3\include\server\pg_config_os.h(216): error C2011: 'itimerval' : 'struct' type redefinition
1>          c:\program files\postgresql\9.3\include\server\pg_config_os.h(216) : see declaration of 'itimerval'

Failure to add PGDLLEXPORT will cause PostgreSQL to fail to CREATE FUNCTION, complaining that it could not find the function in the library even though it's clearly there.

Failure to add postgres.lib will cause link errors, but probably to functions you don't think you're using. Many "functions" in the PostgreSQL API, like elog are macros that do a bunch of work then call an underlying function of a different name.

Building in contrib in a build tree

Begin by [http://www.postgresql.org/docs/current/static/install-windows-full.html establishing a working PostgreSQL build].

If the module will ultimately run on elsewhere-compiled PostgreSQL binaries, such as the official binaries, your build should follow the official one as closely as possible. Ideally it would match exactly in PostgreSQL version, config.pl selections, and dependency library builds. In practice, arranging an exact match is extraordinarily laborious, and simple modules will work without such efforts. A closer match becomes important when the module itself references a particular dependency. For example, to build a module calling into libxml2, the version of libxml2 used at build time should closely resemble the version used at runtime. For a module not using libxml2, you could typically get away with disabling xml support in the build-time config.pl regardless of the runtime binary configuration.

Upon establishing a working ordinary build, introduce the desired extension module by moving its unpacked source directory into the contrib subdirectory of the PostgreSQL source tree. When you next run the build command, this PostgreSQL build system will notice the new directory and attempt to build it. The install command will likewise pick up any added module. A typical module creates files in in the lib, share/extension and sometimes symbols subdirectories of the installation root, with all such files having names prefixed by the name of the module. After running install, you can extract those module-applicable files from the local installation directory tree and merge them into other installations requiring them.

PostgreSQL's build system for Visual Studio handles extension modules by heuristically parsing the Makefile intended for PGXS. For simple modules, this will just work. For others, you must modify src/tools/msvc/Mkvcbuild.pm to account for the module's needs beyond what the build system can discover. Affected modules include any module that references DLLs other than the core DLLs used for all PostgreSQL code. Given an affected module, look for a similar contrib module and adapt its special handling in Mkvcbuild.pm to the third-party module at hand.

PostgreSQL would benefit from the development of a much-smoother process in this area, similar to the PGXS process.

Windows with MinGW

If you build and use a postgres distro for MinGW and you have some type of msys/mingw installed on your windows box then you can build by using the traditional unix style make, ex:

 make PG_CONFIG=C:\installs\postgres95_installed\usr\local\pgsql\bin\pg_config.exe
 make install PG_CONFIG=C:\installs\postgres95_installed\usr\local\pgsql\bin\pg_config.exe