Categories
Uncategorized

Server deployment pgAdmin 4.5 on Windows Server 2019 and Apache with mod_wsgi.

pgAdmin 4 is a great web based database management tool for postgres. Besides the possibility to use it as client-side tool, it is also possible to install it serverside and have clients login to pgAdmin so they don’t need local installations.

This does involve some steps though, and the docs won’t guide you from A to Z. You can find the pgAdmin server deployment documents here.

https://www.pgadmin.org/docs/pgadmin4/dev/server_deployment.html gives a good start. But there is nothing about compiling mod_wsgi

We start on a fresh Windows server 2019. We will also install postgres itself on the same server so we can test the setup.

A fresh Windows server installation will be pretty locked down and also IE is configured in super-safe mode. We could modify this IE behaviour, but I choose to download and use chrome instead for the rest of this walktru.

Although pgAdmin has a stand-alone webserver built-in, this is not suitable for production. We will use and configure Apache as our webserver. pgAdmin is a python flask webapplication and we will use the Apache mod_wsgi plugin to dispatch requests from Apache to the python pgAdmin web application.

The mod_wsgi plugin isn’t available in most Apache binaries. You will need to build them yourself. This is easy though, since we will use python’s pip to install.

Besides windows, I will use 32 bit installations of the software all the way. The reason for this is is that the pgAdmin dependencies are 32 bit and we need those. Therefore our mod_wsgi should also be 32 bit and Apache should also be 32 bit. I think it would be possible to create a 64 bit version if we would take care of the dependencies ourselves, but I keep it simple and just use 32 bit versions.

So, inside pgAdmin there is a python 32-bit virtual environment with all the packages and dependencies needed to run the web application. Including Python itself. But because we use apache to run the webapplication code, we also need a regular python 32-bit installation on the machine. Besides, that this installation is also needed to build our mod_wsgi later on.

Let’s give it a try.

Python 3.7 (32-bit)

Download and install from:
https://www.python.org/downloads/windows/

Make sure to choose a 32 bit binary. For example:
Windows x86 executable installer

After installation, we need to add two folders to PATH so that you can use python and pip from the commandline. From windows search enter ‘Env’ and the option ‘Edit the system environment variables’.

The settings will pop-up. Choose ‘Environment Variables’ and look for the line that says ‘Path’ under ‘System variables’ (the 2nd list, not the top one).

In my case, the paths needed for Python are below, but of course i won’t use ‘administrator’ in a real-world scenario. Add these to your environment variables with the correct <username> folder.

C:\User\<username>\AppData\Local\Programs\Python\Python37-32
C:\User\<username>\AppData\Local\Programs\Python\Python37-32\Scripts

Python installation is done.

pgAdmin

Download pgAdmin 4 for windows from
https://www.pgadmin.org/download/pgadmin-4-windows/

At the time of this writing, the version is 4.5.

Install using default options. This installation will create a default setup, dat doesn’t matter, but we will override it so we have more control over folders etc.

When default installation is done, create the following folders:

mkdir C:\pgAdminData\sessions
mkdir C:\pgAdminData\storage

Next, we need to prepare the new configuration. First go to the pgAdmin directory.

cd C:\Program Files (x86)\pgAdmin 4\v4\web

Now in this directory, create a file config_local.py and enter the following:

SERVER_MODE = True
DEFAULT_SERVER = '0.0.0.0'
DEFAULT_SERVER_PORT = 5050
LOG_FILE = 'C:\pgAdminData\pgadmin4.log'
SQLITE_PATH = 'C:\pgAdminData\pgadmin4.db'
SESSION_DB_PATH = 'C:\pgAdminData\sessions'
STORAGE_DIR = 'C:\pgAdminData\storage'

The IP adress ‘0.0.0.0’ makes sure that we will be able to reach the server from the outside world instead of Localhost only.

Now it is time to do run setup.py. Our fresh python installation doesn’t have the dependencies needed to run this file, but the bundled python virtual environment does have the correct dependencies. A virtual environment sounds fancy, but it is just a folder with python and installed dependencies.

For each virtual environment there is an activation script that just temporarily changes the PATH’s so that we can use it. This is nice, because it is isolated, and won’t break when you install something for your main python environment.

But… the included activation script for windows doesn’t work out of the box, probably caused by the build tools used by the developers. Luckily for us the script just needs a minor fix for our purpose. First go to the venv folder inside pgAdmin.

cd C:\Program Files (x86)\pgAdmin 4\v4\venv\Scripts

Now in notepad, change the first path (line 2) to the correct directory. So from:

set "VIRTUAL_ENV=C:\jenkins\workspace\pgadmin4-windows-build\win-build\venv" 

to:

set "VIRTUAL_ENV=C:\Program Files (x86)\pgAdmin 4\v4\venv

And save. Now we shoud be able to go to the top-level directory and activate the virtual environment. Then we can run our setup.py with the correct dependencies.

You will be prompted for initial username/password. Save this, you need it when you run pgAdmin. Finally we can deactivate the virtual environment.

> cd C:\Program Files (x86)\pgAdmin 4\v4\
>venv\Scripts\activate.bat
(venv)> python web\setup.py
(venv)> deactivate
>

Now, we won’t be using the venv itself when running the webapplications. We will use the Python that we installed on the computer. But we do want want to take advantage of the python dependencies that are bundled inside the pgAdmin application.

Therefore we need to add some code to the very top of “C:\Program Files (x86)\pgAdmin 4\v4\pgAdmin 4\web\pgAdmin4.wsgi”.

activate_this = 'C:/Program Files (x86)/pgAdmin 4/v4/venv/Scripts/activate_this.py'
 with open(activate_this) as file_:
     exec(file_.read(), dict(file=activate_this))

Be careful. It must be entered at the beginning of pgAdmin4.wsgi. And also there are some similar examples of ‘activate_this’ code that try to do the same, but somehow won’t work.

What this code does, is executing the activate_this script. This script makes sure that the venv dependencies are available to your main python application when mod_wsgi executes ‘pgAdmin4.wsgi’.

Apache Webserver

pgAdmin has it’s own webserver (provided by python Flask) but that won’t be sufficient for real-world scenario’s. Therefore we will install the world’s famous webserver Apache. It will handle the requests from the client browsers and send them to pgAdmin later on.

Apache doesn’t provide windows installers since quite a while, and we don’t want to build Apache ourselves. Luckily Apache Lounge provides windows installers. Go to https://www.apachelounge.com/download/ and download the latest win32 version. It downloads as zipfile. At this moment of this writing, the following version is the latest.

httpd-2.4.39-win32-VC15.zip

Extract it to C:\Apache24. Make sure this is the application directory directly, so no extra subfolders. We could start Apache by hand, but let’s install it as a service.

cd C:\Apache24\bin
httpd.exe -k install -n "Apache HTTP Server"

We will need do extra configuation, but first we need to build and install mod_wsgi. I couldn’t find a pre-built version of mod_wsgi, but we can use Python’s pip to built it for us. I think it is the easiest way.

mod_wsgi

By itself, Apache doesn’t know how to execute python code. For this we need a plugin called mod_wsgi. It WSGI is capable of doing more than that, but let’s focus on getting pgAdmin running.

Because we installed the correct version of Python and we have the paths setup correctly, installing mod_wsgi is super easy. But, we need one other tool first: Microsoft Build Tools for C++. We require a version that is equal or greater than the compiler version indicated by Apache’s download link. As we recall that was httpd-2.4.39-win32-VC15.zip. VC15 matches the build tools for Visual Studio 2017. But if you install the latest version, for example 2019, you are safe.

These steps should probably be prepared on a machine other than the production machine because we need this only once.

Download the installer here:

https://visualstudio.microsoft.com/downloads/

This page shows many downloads. Look for the one that says
‘Build Tools for Visual Studio 2019’. Download and install.

During installation, you’ll see many options to install. Just click c++ build tools end leave the options on the right as shown below. Hit install.

Now we are ready to build mod_wsgi. Open up a new cmd prompt and enter the following command:

pip install mod_wsgi

This takes a few seconds. mod_wsgi is installed as python module. Now we are ready to further configure Apache so that Apache will be able to communicate with python and execute the pgAdmin code.

Apache Configuration

Let’s fix the Apache configuration. Most of the configuration is in ‘httpd.conf’. But we will also setup a virtual host that listens on port 5050 and that requires a few changes in httpd-vhosts.conf.

Navigate to “C:\Apache24\conf” and open “httpd.conf” in notepad or any other text editor. Look for the line that says “#Include conf/extra/httpd-vhosts.conf”. Remove the “#”.

Keeping “httpd.conf” open, also open a new command prompt. The mod_wsgi installation has a function that creates the correct apache configuration for you. Enter the following command:

mod_wsgi-express module-config

This will show three lines of configuration (might be wrapped to more visual lines). Copy these three lines and enter them at the end of ‘httpd.conf’. Mine looked like this (again, it might appear more than 3 lines in your browser ;-))

LoadFile "c:/users/administrator/appdata/local/programs/python/python37-32/python37.dll"
LoadModule wsgi_module "c:/users/administrator/appdata/local/programs/python/python37-32/lib/site-packages/mod_wsgi/server/mod_wsgi.cp37-win32.pyd"
WSGIPythonHome "c:/users/administrator/appdata/local/programs/python/python37-32"

This configuration also shows where “mod_wsgi.cp37-win32.pyd” resides. You might need it if you move to production and don’t want to recompile on the production machine itself.

Save the file and open “C:\Apache24\conf\extra\httpd-vhosts.conf”. Remove everything and enter the following:

Listen 5050
<VirtualHost *:5050>
    #ServerName pgadmin.example.com
    WSGIScriptAlias / "C:\Program Files (x86)\pgAdmin 4\v4\web\pgAdmin4.wsgi"
    <Directory "C:\Program Files (x86)\pgAdmin 4\v4\web">
            AllowOverride all
            Require all granted
    </Directory>
</VirtualHost>

Save the file, Apache configuration is done.

PostgreSQL

What would be the use of pgAdmin when there is no postgreSQL database. Installing postgreSQL is easy. Here we can use the 64 bit version, that doesn’t matter.

We will install data into C:\pgData\ so create that folder first.

mkdir C:\pgData

Download version 11 from
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads After download is finished , start installation. Choose all options. At one point you’re asked for the Data directory. Change that to our new folder “C:\pgData”.

Keep de default port and locale. And remember your password, it is used for connecting to your DB.

At the end, launch Stack Builder. In Stack Builder choose the database.

On the next screen, only select the psqlODBC drivers and install those (optional).

That is it. Installation done!

Start Apache and visit pgAdmin

Start apache using the C:\Apache24\bin\ApacheMonitor.exe.
Navigate to http://localhost:5050. First launch might take a while. Then you need to login using the credentials you entered during the execution of ‘setup.py’ when doing the pgAdmin installation.

If you want to use it from another client, make sure to open port 5050 on the server’s firewall.

A bumpy ride

Not that hard, in the end, but it took me a while to connect all dots.

I initially mixed 32 bit and 64 bit components which just didn’t work. Also I spent a while getting the virtual environment to do what I want to be able to run setup.py.

Finally the code I had for running ‘activate_this.py’ inside pgAdmin4.wsgi was not working. Also it was not clear where to put the code in the file. In the end I copied the code from the python Flask documentation (which is the framework used by pgAdmin) and there was also the hint to put it at the very top of the wsgi file.

Leave a Reply

Your email address will not be published. Required fields are marked *