PostgreSQL on the Microsoft Windows operating system is provided by Enterprise DB. They provide an installer for the base PostgreSQL server and a “Stack Builder” tool, to help you easily install additional libraries, utilities, and tools.

Skip to “Installing PostgreSQL Server” if you do not want to use Foreign Data Wrappers.

Installing OSGeo4W (Optional)

Download the network installer for the OSGeo4W project. Right click on the executable and Run as Administrator.

Choose Express Install and when provided with a series of checkboxes for different open-source projects, choose GDAL. If you would also like to install QGIS, select that or the QGIS LTR (long-term release) option and then continue.

Copy ca-bundle.crt (Optional)

Navigate to the cURL Project page for certificates and download cacert.pem. Rename the file to ca-bundle.crt and place the file in C:\Windows\System32.

If you are (understandably) concerned about copying a file into System32, the explanation is available here: https://curl.se/docs/sslcerts.html

I recommend copying into System32 as that should be available to the underlying cURL, PostgreSQL and PostGIS libraries.

Installing PostgreSQL Server

Download the appropriate version from Enterprise DB for your Windows operating system.

This installation is in three parts – installing the PostgreSQL server, using the Stack Builder, and then the PostGIS extension installer.

While installing PostgreSQL, you can accept the default values for most of the prompts. You will want to take note of the two following options presented to you.

If you have more than one non-removable storage device, you might want to consider putting the PostgreSQL application and the data folder on separate hard disks.

You should not place the data directory on a removable or network drive. The PostgreSQL application will always expect to have access to the underlying data directory. You will likely lose data if you place the data directory on a removable or networked drive.

It is always good practice to have a strong password for the postgres user. This account has full control over all of your databases. The caveat here is that if you use the out-of-the-box settings – which we are using for these workshops – you will not need to provide a password to the database when connecting from the same machine!

The default host-based authentication for PostgreSQL prevents connections from over a network connection, but allows all connections to the database if made from the same computer.

Despite this, some applications will not allow you to connect without providing a password, so you should still set a password at this time and commit it to memory.

While this will make it easy for us to get started, you should never use the default security settings for the system if it is to be used in a production environment.

Once you have completed the installation of the PostgreSQL server, you will be prompted to start the Stack Builder.

Downloading New Packages with Stack Builder

There are many add-ons and utilities available for PostgreSQL. In order to support spatial data types, you will need the PostGIS extension. While you can download PostGIS from the source, the Stack Builder will help you identify the appropriate version for what you have installed.

Once you select PostGIS and proceed through Stack Builder, it will download and prompt you to install PostGIS.

Installing PostGIS

PostGIS installation through Stack Builder should be straight-forward. You should feel comfortable accepting the defaults, unless you have any of the following:

• Installed GDAL, Proj.4, GEOS, or other spatial libraries from source,
• Have unusual or modified your PATH environment variable, or
• Otherwise customized your Windows shell environment.

In those cases, you may want to consider revising the installer’s defaults to better suit your configuration.