How to Compile PostgreSQL Modules in ServBay

When working on PostgreSQL development within the ServBay local web development environment, you may need to compile and install additional PostgreSQL modules (also referred to as extensions) to enhance or extend your database’s capabilities. These modules can provide new data types, functions, operators, index methods, and more.

This article provides a detailed walkthrough for compiling and installing PostgreSQL modules in the ServBay environment, using popular extensions like postgis (for GIS support) and pg_jieba (for Chinese text segmentation) as examples.

Overview

ServBay offers a flexible and feature-rich environment, allowing developers to build or compile additional components based on its integrated software packages. Compiling PostgreSQL modules generally involves downloading the module source code, configuring the build options, using specific build tools (like make or cmake) to compile and install, and finally enabling the module in the PostgreSQL database.

The key to success lies in correctly configuring the build environment so it can locate the right headers, libraries, and dependencies for the specific PostgreSQL version provided by ServBay.

Prerequisites

Before compiling any PostgreSQL module, you must initialize the build environment as required by ServBay. This is essential because it sets up the necessary toolchain (compilers, linkers, etc.), environment variables (such as $PATH, $CFLAGS, $LDFLAGS, etc.), and ServBay-specific path configurations for building.

Please refer to the Secondary Compilation with ServBay section of the official ServBay documentation for detailed steps on initializing the build environment. Make sure to complete the setup described there, which usually involves running a specific environment initialization script in your ServBay terminal session.

The Importance of Specifying the PostgreSQL Version

ServBay allows multiple versions of the PostgreSQL package to be installed and run simultaneously. Since compiling and running PostgreSQL modules is tightly tied to the version of PostgreSQL (e.g., specific headers, libraries, and internal APIs), it is crucial to explicitly specify which PostgreSQL version and configuration paths you want to target during the build process.

Selecting the correct configuration path for your PostgreSQL version ensures the build system locates the right dependencies and generates module files compatible with your target PostgreSQL instance. This avoids build errors, load failures, or unpredictable runtime issues.

This guide uses PostgreSQL 15 (specifically version 15.7) as installed in ServBay as an example. Please adapt paths according to the actual version you are using.

ServBay assists you with environment variables and the pg_config tool to find the correct paths. ${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/pg_config is a particularly useful tool shipped with PostgreSQL; it reports the installation path, build configuration, library and include paths for that PostgreSQL version. When compiling modules, pg_config is crucial for specifying dependencies.

Compiling the PostGIS Module

The postgis module is one of PostgreSQL’s most popular and powerful extensions, adding geospatial (GIS) objects and functions to PostgreSQL and enabling storage, querying, and analysis of spatial data. Here are detailed steps for compiling the postgis-3.4.2 module:

Step 1: Download the Source Code

First, download the postgis-3.4.2 source package from the official PostGIS site. Make sure you get the source version (usually .tar.gz or .zip).

wget https://download.osgeo.org/postgis/source/postgis-3.4.2.tar.gz

wget is a common command-line tool for downloading files from a specified URL.

Step 2: Extract the Source Package

Extract the downloaded postgis-3.4.2.tar.gz to your local directory and switch into the source directory.

tar zxvf postgis-3.4.2.tar.gz
cd postgis-3.4.2

The tar zxvf command unpacks .tar.gz archives. The cd command changes the current working directory to the root of the extracted postgis-3.4.2 source.

Step 3: Configure Build Options

PostGIS uses GNU Autotools as its build system. The configure step checks system dependencies, determines features, and generates a Makefile. Here, you’ll need to specify the installation prefix for the specific PostgreSQL version in ServBay along with other required library paths.

./configure \
  --prefix=${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7 \
  --bindir=${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin \
  --datarootdir=${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/share \
  --with-protobufdir=${SERVBAY_BIN_PATH} \
  --disable-nls \
  --without-raster \
  --without-topology \
  --with-pgconfig=${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/pg_config \
  CFLAGS="${CFLAGS} -I${SERVBAY_COMMON_INCLUDE_PATH}/libxml2 -I${SERVBAY_COMMON_INCLUDE_PATH}" \
  CXXFLAGS="${CXXFLAGS} -std=c++17 -I${SERVBAY_COMMON_INCLUDE_PATH}/libxml2 -I${SERVBAY_COMMON_INCLUDE_PATH}" \
  LDFLAGS="${LDFLAGS} -L${SERVBAY_COMMON_LIB_PATH} -lxml2 -lz -lpthread -liconv -licui18n -licuuc -licudata -lm"

• ./configure: Run the configure script.
• --prefix, --bindir, --datarootdir: Specify installation into the directory structure corresponding to the target PostgreSQL version in ServBay. This ensures PostgreSQL can locate installed module files via its standard search paths. ${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7 is the root install path of PostgreSQL 15.7 in ServBay, usually set by the ServBay build environment initialization script.
• --with-protobufdir: Specifies the Protobuf library path, which PostGIS may require. ${SERVBAY_BIN_PATH} is the ServBay binary packages directory.
• --disable-nls: Disables native language support, often unnecessary for development environments.
• --without-raster, --without-topology: Disables PostGIS Raster and Topology features to simplify the build. You can enable them if needed, but this may require extra dependencies.
• --with-pgconfig: The critical option, points to the pg_config for your target PostgreSQL. pg_config supplies compilation flags, library and header paths for the configuration script, ensuring compatibility with the correct PostgreSQL version.
• CFLAGS, CXXFLAGS, LDFLAGS: These are standard environment variables for passing extra flags to the C/C++ compiler and linker. ${CFLAGS}, ${CXXFLAGS}, ${LDFLAGS} preserve ServBay’s environment settings.
  • -I${SERVBAY_COMMON_INCLUDE_PATH}/libxml2 -I${SERVBAY_COMMON_INCLUDE_PATH}: Adds ServBay’s common include directories so the compiler can find headers for libxml2 and others. ${SERVBAY_COMMON_INCLUDE_PATH} is the universal include path provided by ServBay.
  • -L${SERVBAY_COMMON_LIB_PATH} -lxml2 -lz ...: Adds ServBay’s common library directory and links necessary libraries (like libxml2, zlib, pthread, iconv, icu, etc.). ${SERVBAY_COMMON_LIB_PATH} is the universal library path provided by ServBay. These dependencies are commonly included in the ServBay build environment.
  • -std=c++17: Specifies the C++17 standard for the C++ compiler, required by many modern C++ projects.

Step 4: Compile and Install

Once configured, run make to compile, followed by make install to install the module files into the ServBay PostgreSQL directory as previously specified.

make -j ${CPU_NUMBER}
make install

• make -j ${CPU_NUMBER}: Initiates the build. The -j ${CPU_NUMBER} flag enables parallel compilation using all available CPU cores (${CPU_NUMBER} is set by ServBay), speeding up the process.
• make install: Copies the compiled files (including shared libraries, SQL scripts, etc.) into the correct locations under ServBay’s PostgreSQL 15.7 installation directory (typically lib/postgresql and share/postgresql/extension subdirectories).

Step 5: Verify Module Loading

After compiling and installing, you’ll need to enable the postgis extension in your PostgreSQL database. Use the command line (psql) provided by ServBay or your preferred PostgreSQL client to connect and run the following SQL commands.

To verify using ServBay’s psql tool, ensure you’re in a ServBay terminal session so that all environment variables are correctly loaded:

# Enable PostGIS by running the CREATE EXTENSION command
${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "CREATE EXTENSION postgis;"

If successful, the PostGIS extension has been created. You may further check the version:

# Query the PostGIS version
${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "SELECT PostGIS_Version();"

If all goes well, you should see the PostGIS version output, indicating that the module was successfully compiled, installed, and loaded into your database.

Compiling the pg_jieba Module

pg_jieba is a PostgreSQL extension based on the C++ library cppjieba, providing Chinese text segmentation, often used in full-text search scenarios. It uses CMake as its build system. Here’s how to compile pg_jieba:

Step 1: Obtain the Source Code

First, clone the pg_jieba repository from GitHub. Note that pg_jieba depends on cppjieba as a Git submodule, so you’ll need to initialize and update submodules as well.

git clone https://github.com/jaiminpan/pg_jieba.git
cd pg_jieba
git submodule update --init --recursive

• git clone ...: Clones the pg_jieba repository locally.
• git submodule update --init --recursive: Initializes and updates all Git submodules found in the repository. Since pg_jieba includes cppjieba, this ensures its source code is downloaded into the proper subdirectory.

Step 2: Configure Build Options (CMake)

pg_jieba uses CMake. Just like Autotools’s ./configure, CMake’s cmake command generates platform-specific build files. You’ll need to specify the PostgreSQL installation path and ServBay environment-related build targets.

cmake -S . -B builddir \
  -DCMAKE_PREFIX_PATH=${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7 \
  -DCMAKE_OSX_DEPLOYMENT_TARGET=${BUILD_OS_MIN_VERSION} \
  -DCMAKE_OSX_ARCHITECTURES=${BUILD_CPU_ARCH_CMAKE}

• cmake -S . -B builddir: Runs CMake configuration. -S . sets the current directory as the source, -B builddir tells CMake to create a builddir subfolder to store build files. Keeping the build in a separate directory is CMake’s best practice for keeping your source tree clean.
• -DCMAKE_PREFIX_PATH: Key option, tells CMake where to search for libraries and headers, especially PostgreSQL. Here it points to ServBay’s PostgreSQL 15.7 root install. CMake looks within this path for lib/cmake, share/cmake, etc., and uses pg_config info to locate dependencies.
• -DCMAKE_OSX_DEPLOYMENT_TARGET: Sets the minimum supported macOS version, ensuring compatibility. ${BUILD_OS_MIN_VERSION} is configured by ServBay.
• -DCMAKE_OSX_ARCHITECTURES: Sets the target CPU architecture (x86_64, arm64, etc.). ${BUILD_CPU_ARCH_CMAKE} is set by ServBay. These macOS-specific flags ensure that the module will run properly on your macOS system.

Step 3: Compile and Install (CMake)

Once configuration is done, use cmake --build to compile, and cmake --install to install.

cmake --build builddir -j ${CPU_NUMBER}
cmake --install builddir

• cmake --build builddir: Starts the compilation process in the designated build directory.
• -j ${CPU_NUMBER}: Again enables parallel compilation to make use of your CPU.
• cmake --install builddir: Installs compiled files into the appropriate directories under ServBay’s PostgreSQL installation, typically determined by -DCMAKE_PREFIX_PATH and pg_config.

Step 4: Verify Module Loading

After compiling and installing, you’ll need to enable the pg_jieba extension in your PostgreSQL database. Connect and run the SQL command.

Verify with ServBay’s psql command-line tool—make sure you are within a ServBay terminal:

# Enable pg_jieba extension
${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "CREATE EXTENSION pg_jieba;"

If this command succeeds, the pg_jieba extension has been created. To confirm, query the pg_available_extensions view:

# List installed extensions
${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_jieba';"

If the module appears in the results, it means the build, install, and enable steps all succeeded.

FAQ (Frequently Asked Questions)

• Compilation fails with missing headers or libraries?
  • Check to ensure you have initialized the ServBay build environment correctly. Run the environment initialization script in the ServBay terminal, and make sure all relevant environment variables ($CFLAGS, $LDFLAGS, ${SERVBAY_COMMON_INCLUDE_PATH}, ${SERVBAY_COMMON_LIB_PATH}, etc.) are set and point to the correct ServBay paths.
  • Verify that the path you set for --with-pgconfig (Autotools) or -DCMAKE_PREFIX_PATH (CMake) matches your intended PostgreSQL version and that the pg_config file exists and is executable at that location.
  • Some modules may require extra system dependencies beyond ServBay's common libraries (e.g., PostGIS with Raster/Topology needs GEOS, GDAL, etc.). While many common dependencies are included, you may have to manually install missing ones or adjust build options to disable extra features.
• CREATE EXTENSION fails, reporting missing module file?
  • Make sure you ran make install (Autotools) or cmake --install (CMake) without errors. Check the logs to confirm the files were copied to the correct locations.
  • Double-check that the installation path specified with --prefix (Autotools) or -DCMAKE_PREFIX_PATH (CMake) points to the desired ServBay PostgreSQL version directory. Module files are generally installed under the lib/postgresql subdirectory of this path.
  • Try restarting the PostgreSQL service. Sometimes, a database restart is required before new modules are recognized. You can easily restart PostgreSQL via the ServBay application interface.
  • Check PostgreSQL’s shared_preload_libraries setting. Most extensions loaded with CREATE EXTENSION do not require changes here, but some special modules (ones using low-level hooks) may need to be added and preloaded at startup. Refer to the module’s official documentation for clarification.
• How do I compile modules for different PostgreSQL versions?
  • Repeat these build steps, but be sure to specify the correct --with-pgconfig (Autotools) or -DCMAKE_PREFIX_PATH (CMake) parameter for your target version. For instance, to compile for PostgreSQL 14 in ServBay, update the path to ${SERVBAY_PACKAGE_FULL_PATH}/postgresql/14/14.x/bin/pg_config or ${SERVBAY_PACKAGE_FULL_PATH}/postgresql/14/14.x (where 14.x is the full version string in ServBay).

Summary

By following these steps and making good use of ServBay’s build environment and tools, you can successfully compile and install the PostgreSQL modules you need to extend your database capabilities and meet specific development requirements. Remember, correctly initializing the build environment, specifying your target PostgreSQL version, and carefully checking build configurations and install paths are key to success.

We hope this guide helps you customize your PostgreSQL environment in ServBay with ease. If you encounter further issues, please refer to other ServBay documentation or reach out to the community for assistance.