Building an GIS-RPI using PostgreSQL-11
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.
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.