Building an GIS-RPI using PostgreSQL-11

Tim Holthaus/ May 22, 2020/ Linux

Number crunching power on single board computers gets better and better. But is a Raspberry Pi 4 (4GB-model) a usefull board to provide a simple GIS-endpoint? In the following short articles I will use the above mentioned Raspberry Pi 4 (RPI4). As storage I use an Class 10 16GB SD-Card for the OS and an old 250GB EVO 840 SSD. Since RPI4 supports UBS3 using an external SSD should boost the TPS. This is the first article – Quick installation of PostgreSQL and some I/O boost by using a SSD as storage.

Getting PostgreSQL-11

PostgreSQL-11 is incuded in the default rasbian repositories while I am writing this article (May 2020). All we have to do is:

sudo apt update
sudo apt upgrade
sudo apt install postgresql postgresql-contrib

This will install PostgreSQL-11 to your default OS-storage. Now lets set a password for the default postgresql-user “postgres”. 

sudo su postgres 
psql posstgres

# \password postgres

Now PostgreSQL-11 is ready to use.

Enabling SSD performance and increase durability

Since we use a limited lifespan SD as default OS-storage and Databases can be very write insensitive (specially the wal) it is useful to move the corresponding directories to an durable storage.

The default path of PostgreSQL (data and wal location) is /var/lib/postgresql. Furthermore logs are written into /var/log/postgresql. We will use symbolic links to move these locations onto the external SSD. I assume that the external SSD is mounted at /mnt/ssd. The following article describes the installation of PostgreSQL-11 and how to link write intensive paths to external SSD.

First stop the postgresql service and confirm by checking the status.

sudo service postgresql stop
sudo service postgresql status

Organisation is important for maintenance. This why er create a new folder “var” and inside this new folder the folder log on the external SSD.

mkdir /mnt/ssd/var
mkdir /mnt/ssd/var/log

To copy the original folder /var/log/postgresql to the new created path /mnt/ssd/var/log we use rsync. rsync copies also the ownership and permissions of the original folder.

sudo rsync -av /var/log/postgresql /mnt/ssd/var/log

After this backup the original folder /var/log/postgresql by move it to /var/log/postgresql-backup. 

sudo mv /var/log/postgresql /var/ssd/log/postgresql-backup

Now the important part. Create the symbolic link. The first path represents the destination of the link. In this examlpe it is /mnt/ssd/var/log/postgresql. 

sudo ln -s /mnt/ssd/var/log/postgresql /var/log/postgresql

Last step is to chown the created link to the OS user postgres.

sudo chown -h postgres:postgres /var/log/postgresql

Now start postgresql and test if the instance is online. Last can be done by open the psql prompt.

sudo service postgresql start
sudo service postgresql status

sudo su postgres 
psql posstgres

If postgresql is running normaly the backup can be deleted.

sudo rm -r /var/ssd/log/postgresql-backup

Repeat this steps

Repeat the above mentioned steps for /var/lib/postgresql and maybe even for /etc/postgresql. Since /var/lib/postgresql is the home for the data and wal log, moving it saves lifetime for the SD-storage.

The next steps will be installing postgis and doing some benchmarks to see how the configuration of PostgreSQL affects the performance.

Share this Post