One of the benefits of having the source code is that often the comments in the code can provide good insight into the inner workings that the normal documentation does not. It can also show code that will output more logging when certain options are set.
The biggest benefit comes from being able to attach a debugger to the PostgreSQL processes and step through the code. This blog details how to build PostgreSQL 12 from the source code on Windows 10 64bit.
Let’s get the environment ready.
Downloading the Source Code
The source code is held in a GIT repository hosted on a PostgreSQL server. Getting the source code is much easier using a GIT windows or command line client. I am using TortoiseGIT.
The repository is hosted at https://git.postgresql.org/gitweb/?p=pgagent.git;a=summary and specifically I want the latest stable v12 release which at the time of writing was tagged at REL-4_0_0.
We will do a GIT clone using the URL git://git.postgresql.org/git/pgagent.git and specifying the tag REL-4_0_0. Create a folder called C:\PGAgent_4_Source. Right click and select Git Clone. Fill out the UI as below and click OK.
We can verify the actual version by checking the contents of the file C:\PGAgent_4_Source\pgAgent.rc.
This confirms the version of PGAgent we are using.
Install C++ Feature in Visual Studio 2019
I am using Enterprise edition of Visual Studio. If you find anything missing from yours, it could be your edition does not support it.
From Visual Studio click on Tools -> Get Tools and Features. From the Workloads tab locate the Desktop development with C++. If there is not a blue tick in the top right hand corner, click it.
On the right hand side locate the list of options. You can see what I have selected which is fine for compiling this project.
Downloading the C++ Boost Library
PGAgent uses the Boost C++ libraries to take advantage of the enhanced functionality they bring. When I first tested the build, I downloaded the latest version and I could not get CMake to complete successfully. Look at the steps below and use the information to determine the correct version based on your environment. The steps below works for my environment.
First let’s look at the .NET and Visual Studio version numbers that indicate our environment. I collated the information based on Wikipedia articles and the CMake documentation. I’ll only show from Visual Studio 2010.
I have installed Visual Studio 2017 15.9.14 and 2019 16.3.5. I am planning on using VS 2019. Let’s see what compiler version I have. Launch the Developer Command Prompt for VS 2019. Make sure you select the Native x64 version [x64 Native Tools Command Prompt for VS 2019]. Now run cl /?.
Here we can see I am using 19.23.28106 for x64. I want to compile to x64.
So looking at the chart above, my environment falls in the v142 toolset. At the time of writing v1.71.0 of the Boost libraries were available. If we navigate to https://sourceforge.net/projects/boost/files/boost-binaries/1.71.0/ we can see the x86 and x64 binaries available along with the toolset version.
When I first tested the build I selected the version above for my v142 toolset, the cmake failed to locate the correct libraries. When I delved deeper in the cmake files I found the following code in C:\PGAgent_4_Source\cmake\FindBoost.cmake.
The Boost cmake file says, ‘any version above 19.10, use either v141 or v140 toolset’. This means the current version of PGAgent has not been tested against the v142 Boost libraries. That’s fine, I don’t mind using the v141; I just want the build to work. So I downloaded the v14.1 version into folder C:\Apps\Boost_1_71_0.
Generate Visual Studio Project and Solution Files
This step invokes running CMAKE to generate the project and solution build files. The README file that comes with the PGAgent source is not very clear however; with some persistence I managed to get it to work using the steps below.
Launch the Developer Command Prompt for VS 2019. Make sure you select the Native x64 version [x64 Native Tools Command Prompt for VS 2019].
Run the following commands:
SET PGDIR=C:\PostgreSQL12 mkdir C:\PGAgent_4_Build_12 cd C:\PGAgent_4_Build_12 cmake -G "Visual Studio 16 2019" -A x64 -D BOOST_ROOT:Path=C:/Apps/boost_1_71_0 -D BOOST_INCLUDEDIR:Path=C:/Apps/boost_1_71_0/boost -D BOOST_LIBRARYDIR:Path=C:/Apps/boost_1_71_0/lib64-msvc-14.1 -D Boost_FIND_REQUIRED:Bool=ON -D Boost_FIND_QUIETLY:Bool=OFF -D Boost_DEBUG:Bool=ON -D Boost_USE_MULTITHREADED:Bool=ON -D Boost_USE_STATIC_LIBS:Bool=OFF "C:/PGAgent_4_Source"
It should finish with the following. Ignore any warnings.
Open the Solution in Visual Studio
Once the build has completed the solution and project files will be in C:\PGAgent_4_Build_12.
Open the solution C:\PGAgent_4_Build_12\
pgagent.sln. If all has gone well you should see the following.
I said earlier I was using Visual Studio 2019 which uses tools v142. We ran cmake using v141 of the Boost libraries which is Visual Studio 2017. In order for us to build we need to change the Platform Toolset for each project to v141. For each project in Solution Explorer, right click and select Properties. Change Platform Toolset to Visual Studio 2017 (v141) as shown below.
Once you have done all 7 projects, Solution Explorer will point out that the projects are using Visual Studio 2017 tooling.
Make sure we have the correct platform selected.
Go ahead and run Rebuild Solution and monitor the build in the Output window. This will create the folder C:\PGAgent_4\Debug.
As you can see the build has succeeded.
Having this solution available enables us to attach to a running PostgreSQL process and step through the code. Sweet.
Generating PGAgent Program Folder
Now we have our build folder created and code compiled, next we can build out our program folder where we can execute the PGAgent.
Launch the Developer Command Prompt for VS 2019. Make sure you select the Native x64 version [x64 Native Tools Command Prompt for VS 2019].
Run the following command. The –config parameter is selecting the DEBUG build. The –prefix parameter is the target folder.
cmake --install C:\PGAgent_4_Build_12 --config DEBUG --prefix C:\PGAgent_4
The build has also added the pgagent extension files into the PostgreSQL extension folder which is required for the step below.
Running PGAgent as a Windows Service
The following steps are required to get the PGAgent up and running. First we need to create the pgagent extension. This MUST be done in the postgres database.
CREATE EXTENSION pgagent;
Create a PostgreSQL user (role) which the service will connect to PostgreSQL as.
CREATE ROLE pgagent WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxx';
Create local windows user which will be the user the windows service runs as. I’ll use postgres as the user name. Password is the same as the name.
net user postgres postgres /ADD
Give the user Allow log on locally and Log on as a service rights by running secpol.msc from the command line as I have done below.
Since I don’t like adding everything to my path, I logged in using the local postgres account which generated a profile. Then using the psgetsid64 tool from https://docs.microsoft.com/en-gb/sysinternals/, I displayed the SID.
I then located the PATH environment setting for that user in the registry and added C:\Apps\boost_1_71_0\lib64-msvc-14.1;C:\PostgreSQL12\lib.
Open a command prompt and run the following to create the windows service.
SET PATH=%PATH%;C:\Apps\boost_1_71_0\lib64-msvc-14.1 "C:\PGAgent_4\pgAgent" INSTALL pgAgent -u postgres -p postgres dbname=postgres user=pgagent
Now start the service from service manager.
Any messages will appear in the event log. If you want to change the debugging level, you can amend the start-up parameters in the registry. –l 0 is the default. (0 = ERROR, 1 = WARNING and 2 = DEBUG).
If all goes well when you connect using pgAdmin, you should see a new option as shown below.
Running PGAgent from Visual Studio in Debug Mode
If you want to start PGAgent from Visual Studio rather than as a Windows Service, add the following pgagent project properties. You can now set a breakpoint in the main method in win32.cpp and hit F5 to run.
Enjoy.
Denham Coder 