Installing PostgreSQL and PostGIS from source
This post walks through the process of building and installing PostgreSQL
and PostGIS from source on a Raspberry Pi. Building from source
might not be for everyone, but having this skill opens the doors to a number of
new opportunities. With a little experience with Linux and the command line this
task can quickly become second nature.
Yes, it is more involved than running
apt install and it will take longer.
What you gain is the ability to test new features, bug fixes, review
Commitfest patches, and ultimately have
more understanding about what is installed and how.
You'll probably also learn a thing (or 10) in the process... I know I did!
This post is part of the series PostgreSQL: From Idea to Database.
Nuances of the Pi
My main driver to install from source on the Pi is the lack of availablity of
version choices with software via
being a customized ARM-specific derivitive of Debian has its challenges,
and Rasbian Jesse easily can install the 9.6 line, typically
lagging 1-3 minor releases from current.
I've never had success getting the PGDG repos to work on the Pi either,
and while 9.6 isn't that old (< 3 years) there are a ton of
improvements and features in the major releases since.
With Raspbian Buster was released (June 2019) PostgreSQL 11.4 and PostGIS 2.5 were available, the latest released versions of both! As you can tell, I was a bit excited for the discovery:
While that is awesome today, in the next few months we should see both PostgreSQL 12 and PostGIS 3.0 released, and I don't expect another built-in upgrade in major versions until whatever replaces Buster comes out. There are a lot of really cool improvements in Pg12 that I intend to take full advantage of.
While this post is specifically targeted for the Raspberry Pi and its Raspbian OS, the basic instructions should work for the related Debian and Ubuntu OSs.
Getting the Pi ready
Download and install Raspbian if you haven't already. This post provides specific instructions for both the Jesse and Buster where the process deviates. While Raspbian Buster is released, it does not yet provide support for Mathmatica at the time of writing. Until that resolves I expect a large number of Raspbian Jesse users to remain.
This guide uses three Linux users, an unspecified user with
postgres users. If you are using the default
(not really recommended) you need to change the password from its well-known default.
Install required software
Install the prerequisite software while logged in as your user with
The first step of installing pre-req software has differences between the OS versions.
The only difference between the following two commands are the
though I have included each
apt install command in its entirety for copy/paste convenience.
Raspbian Jesse pre-reqs
user@pi:~$ sudo apt install git libxml2 libxml2-dev libreadline6 libreadline6-dev flex bison libbison-dev build-essential zlib1g-dev libxslt-dev libssl-dev postgresql-server-dev-all libxml2-utils xsltproc
Raspbian Buster pre-reqs
user@pi:~$ sudo apt install git libxml2 libxml2-dev libreadline7 libreadline-gplv2-dev flex bison libbison-dev build-essential zlib1g-dev libxslt-dev libssl-dev postgresql-server-dev-all libxml2-utils xsltproc
The rest of the PostgreSQL installation is the same regardless of which of the two OSs you are using.
In the terminal, create the postgres user if it doesn't exist.
user@pi:~$ sudo adduser postgres
This user may already exist causing you to get a warning
adduser: The user postgres' already exists.. This is OK.
Get the source code
Switch to the postgres user in its home directory.
user@pi:~$ sudo su - postgres postgres@pi:~$
Create a directory to clone into (
~/git) and clone the PostgreSQL repository.
If you clone the full repository it will take a few minutes on the Raspberry Pi,
it's a few hundred MB. Instead, the following command clones just the
specific branch for the version we wish to install (
-b REL_12_STABLE )
and uses a
--depth 1) to minimize the download. This method has two distinct advantages:
a) faster for you; and b) nicer on the git server.
postgres@pi:~$ mkdir git && cd ~/git postgres@pi:~/git/postgresql$ git clone -b REL_12_STABLE \ --depth 1 \ https://git.postgresql.org/git/postgresql.git
This should result in output similar to:
Cloning into 'postgresql'... remote: Counting objects: 6000, done. remote: Compressing objects: 100% (5541/5541), done. remote: Total 6000 (delta 718), reused 2238 (delta 263) Receiving objects: 100% (6000/6000), 23.03 MiB | 6.98 MiB/s, done. Resolving deltas: 100% (718/718), done. Checking out files: 100% (5592/5592), done.
Change into the cloned postgresql repo.
Within the repo we can verify which branch is checked out.
postgres@pi:~/git/postgresql$ git branch * REL_12_STABLE
We can also check the log and see there is only 1 commit of history. The single commit you see listed will almost certainly be different than the one I show here.
postgres@pi:~/git/postgresql$ git log --oneline ff597b6 docs: remove pg_roles mention of the oid column being displayed
STABLEbranch of a non-production version should be used with caution! PostgreSQL 12 is currently in Beta with first production release expected in Fall 2019. See PostgreSQL versioning for more details.
Apply patches (Optional)
If you are testing changes to the PostgreSQL source code,
now is the time to apply your patch. Make sure it's available at a path
postgres user has permissions to and run the command:
postgres@pi:~/git/postgresql$ git apply -p1 < /path/to/your.patch
See the Postgres short version for the basic outline of the steps to come.
./configure --help to see the full range of options. The instructions
show using configure without options, I always add at least two for
my needs. Because I use PostGIS with QGIS layer styles, I need XML support,
--with-libxml. My default
pg_hba.conf only provides
for non-local connections so I also need SSL support.
postgres@pi:~/git/postgresql$ ./configure \ --with-libxml --with-openssl checking build system type... armv7l-unknown-linux-gnueabihf checking host system type... armv7l-unknown-linux-gnueabihf checking which template to use... linux checking whether NLS is wanted... no checking for default port number... 5432 checking for block size... 8kB checking for segment size... 1GB checking for WAL block size... 8kB ...
During this step there will be a lot of lines stating the checks for various
components. If your system is missing something important it will fail and tell you
why. Its explanation may seem cryptic, but copy key words and search
install <keyword> on <OS> and you're likely to find pointers to what
This is the step that takes a while, on a Raspberry Pi 3B this takes about 35
minutes. Leaving off the
make will save you about 10 minutes.
make world because I need the contrib modules installed so
postgres@pi:~/git/postgresql$ make world
For quite a while before you see the "installation complete" message there will be a lot of messy text scrolling across the screen. At times it might not update for a little while, other times it will scroll so fast you can't read it all. It starts something like this...
--set-version=12 ../../../src/include/catalog/pg_proc.h ../../../src/include/catalog/pg_type.h ../../../src/include/catalog/pg_attribute.h ../../../src/include/catalog/pg_class.h ../../../src/include/catalog/pg_attrdef.h ../../../src/include/catalog/pg_constraint.h ../../../src/include/catalog/pg_inherits.h ../../../src/include/catalog/pg_index.h ../../../src/include/catalog/pg_operator.h ../../../src/include/catalog/pg_opfamily.h ../../../src/include/catalog/pg_opclass.h ../../../src/include/catalog/pg_am.h ../../../src/
When running long operations I typically have a second terminal open and use
htop to monitor what's running. This is especially helpful on the Raspberry Pi, which
often runs significantly slower than my internal clock expects. The following
screenshot shows the output from htop while installing PostgreSQL 11 from source
with Raspbian Jesse.
make step completes, exit from the
postgres user and switch to root.
postgres@pi:~/git/postgresql$ exit user@pi:~$ sudo su - root
As root, change to the
postgres directory where the source code was just made
and install. This shouldn't take more than 30 seconds.
root@pi:~# cd /var/lib/postgresql/git/postgresql root@pi:/var/lib/postgresql/git/postgresql# make install-world … … … PostgreSQL, contrib, and documentation installation complete.
Setup directory for database data and make the
postgres user the owner.
root@pi:~# mkdir /usr/local/pgsql/data root@pi:~# chown postgres /usr/local/pgsql/data
Switch back to the
postgres user and
initdb and start!
root@pi:~# su - postgres postgres@pi:~$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... US/Mountain creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
The init command helpfully outputs the exact command needed to start our newly created Postgres instance.
postgres@pi:~$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start waiting for server to start.... done server started
psql can connect and the correct version is installed.
postgres@pi:~$ /usr/local/pgsql/bin/psql -c "SELECT version();" ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ version │ ╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ PostgreSQL 12beta2 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6+rpi1) 8.3.0, 32-bit │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
You may have noticed the commands above use lengthy commands with the
full path, such as
/usr/local/pgsql/bin/psql. To make it possible to
use just the binary name, the PATH needs to be updated.
Exit to root.
/etc/profile file to make this change for all users.
root@pi:~# nano /etc/profile
PATH=/usr/local/pgsql/bin:$PATH export PATH
Now switch back to to the
postgres user to test:
root@pi:~# su - postgres postgres@pi:~$ which psql /usr/local/pgsql/bin/psql
That last line shows that our binaries can now be found.
psql command to ensure the correct version is installed. This should
match the output you received from the same command above.
postgres@pi:~$ psql -c "SELECT version();" ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ version │ ╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ PostgreSQL 12beta2 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6+rpi1) 8.3.0, 32-bit │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Congratulations! You have a fully functional PostgreSQL server installed from source on your Raspberry Pi. If you want to install PostGIS as well, read on! The most time consuming and important steps are already completed.
Start as your user with
sudo powers and install a few more pieces of software.
You can omit
osm2pgsql if you do not plan to
import OpenStreetMap data.
user@pi:~$ sudo apt install libgdal-dev libgeos-dev osm2pgsql
PostGIS, PostgreSQL and Raspbian versions
Major PostGIS releases (2.3, 2.4, 2.5, 3.0) generally align with major PostgreSQL releases (9.6, 10, 11, 12). For example, the default versions on Rasbpian Jesse are PostgreSQL 9.6 and PostGIS 2.3; Raspbian Buster installs PostgreSQL 11 and PostGIS 2.5. There isn't a strict requirement to install specific versions with each other, as you will see.
That said, when I upgrade to PostgreSQL 12 in production
this fall, I intend to upgrade to PostGIS 3.0 at the same time.
Naturally, I want to test those two releases together. On Raspbian
Jesse, this isn't easy because Jesse won't install a new enough GEOS
for PostGIS 3.0. Trying to run
./configure on Jesse
with PostGIS 3.0 results in:
configure: error: PostGIS requires GEOS >= 3.6.0
You can install newer versions of GEOS from source, this post does not cover those steps. I made the effort once, it took 3+ hours IIRC, and that was on the second attempt. The easiest way to get a newer GEOS version is to use Buster.
The following installation walk through the process with the differences between installing PostGIS 3.0 on Buster and PostGIS 2.5 on Jesse.
PostGIS from source on Raspbian
Switch to the
postgres user and download the desired PostGIS version
for your Postgres installation. Check the
PostGIS site for links
to the latest versions.
user@pi:~$ sudo su - postgres
postgres@pi:~$ wget https://download.osgeo.org/postgis/source/postgis-3.0.0alpha3.tar.gz postgres@pi:~$ tar xvzf postgis-3.0.0alpha3.tar.gz postgres@pi:~$ cd postgis-3.0.0alpha3
postgres@pi:~$ wget https://download.osgeo.org/postgis/source/postgis-2.5.2.tar.gz postgres@pi:~$ tar xvzf postgis-2.5.2.tar.gz postgres@pi:~$ cd postgis-2.5.2
Within the PostGIS source directory:
Jesse users might get a warning about outdated GEOS library. This is OK if you can handle the limitations of the older version.
configure: WARNING: --------- GEOS VERSION WARNING ------------ configure: WARNING: You are building against GEOS 3.5.1 configure: WARNING: To take advantage of all the features of configure: WARNING: this PostGIS version requires GEOS 3.7.0 or higher. configure: WARNING: To take advantage of most of the features of this PostGIS configure: WARNING: we recommend GEOS 3.6 or higher configure: WARNING: You can download the latest versions from configure: WARNING: http://trac.osgeo.org/geos configure: WARNING:
Make and install PostGIS
make step for PostGIS takes about 6 minutes on the Pi 3B.
Exit the postgres user, switch to root to install.
postgres@pi:~$ exit user@pi:~$ sudo su root@pi:~# cd /var/lib/postgresql/postgis-<version> root@pi:~# make install
Switch back to
postgres user to create a database and test PostGIS.
root@pi:~# su - postgres postgres@pi:~$ createdb gis_test postgres@pi:~$ psql -d gis_test
Create the PostGIS extension:
([local]) postgres@gis_test=# CREATE EXTENSION postgis;
Version check on Buster with PostGIS 3.0:
([local]) postgres@gis_test=# SELECT PostGIS_Full_Version(); ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ postgis_full_version │ ╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ POSTGIS="3.0.0alpha3 r17573" [EXTENSION] PGSQL="120" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September…│ │… 15th, 2018" LIBXML="2.9.4" LIBJSON="0.12.1" │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Version check on Jesse with PostGIS 2.5:
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ postgis_full_version │ ╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="120" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" …│ │…GDAL="GDAL 2.1.2, released 2016/10/24" LIBXML="2.9.4" LIBJSON="0.12.1" RASTER │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Get output similar to the above? Congratulations! You have installed PostgreSQL and PostGIS from source on your Raspberry Pi!
This post walked through the steps to install development versions of PostgreSQL and PostGIS from source on a Raspberry Pi. Once you work through the steps a couple of times with good instructions (this post is based on my own procedures) it really isn't that bad. Add in Ansible to automate the configuration and mostly the process is waiting. 45 minutes is a decent goal for the process start to finish on the Pi.
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published July 11, 2019
Last Updated July 11, 2019