How to Compile PostgreSQL Modules

When developing PostgreSQL with ServBay, you may need to compile and install additional PostgreSQL modules to extend the database’s functionality. This article will cover how to compile PostgreSQL modules in a ServBay environment, specifically including the compilation of the postgis and pg_jieba modules.

Prerequisites

Before you start compiling PostgreSQL modules, ensure you've completed the initialization of the build environment. For detailed steps on how to initialize the build environment, please refer to the documentation Secondary Compilation with ServBay.

Importance of Specifying PostgreSQL Version

ServBay comes with multiple PostgreSQL versions, and it's crucial to specify the PostgreSQL version and related configurations when using the build tool. This is because different versions of PostgreSQL may have different configurations and dependencies. Selecting the correct version can help avoid compilation errors and runtime issues.

This example uses PostgreSQL 15.

Compiling the postgis Module

The postgis module is an extension of PostgreSQL to handle Geographic Information System (GIS) data. The following are the steps to compile the postgis-3.4.2 module:

Step 1: Download the Source Code

First, download the source package for postgis-3.4.2 from the official PostGIS website.

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

Step 2: Extract the Source Package

Extract the downloaded source package.

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

Step 3: Configure Build Options

Configure the build options, specifying the path to the PostgreSQL configuration and other parameters.

./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"

Step 4: Compile and Install

Compile and install the postgis-3.4.2 module.

make -j ${CPU_NUMBER}
make install

Step 5: Verify Module Loading

Verify whether the postgis module is successfully loaded via the PostgreSQL command-line tool:

${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "CREATE EXTENSION postgis;"
${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "SELECT PostGIS_Version();"

If the module is successfully loaded, you should see the version information of PostGIS.

Compiling the pg_jieba Module

The pg_jieba module is a PostgreSQL extension for Chinese word segmentation. The following are the steps to compile the pg_jieba module:

Step 1: Get the Source Code

First, clone the pg_jieba module's source repository from GitHub.

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

Step 2: Configure Build Options

Configure the build options, specifying the path to the PostgreSQL configuration and other parameters.

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}

Step 3: Compile and Install

Compile and install the pg_jieba module.

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

Step 4: Verify Module Loading

Verify whether the pg_jieba module is successfully loaded via the PostgreSQL command-line tool:

${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "CREATE EXTENSION pg_jieba;"
${SERVBAY_PACKAGE_FULL_PATH}/postgresql/15/15.7/bin/psql -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_jieba';"

If the module is successfully loaded, you should see the relevant information for pg_jieba.

Conclusion

By following the above steps, you can compile and install the necessary PostgreSQL modules in the ServBay environment. Make sure to specify the correct PostgreSQL version and related configurations during the compilation process to ensure compatibility with your database environment. Hopefully, this article will help you successfully complete the compilation of PostgreSQL modules.