Install PostGIS on Red Hat Enterprise Linux (RHEL) 8 (2024)

Motivation

I was ready to copy a table with a geometry column from R to PostgreSQL, and I saw this error that wasn’t there yesterday:

Error: Failed to fetch row : ERROR: type "geometry" does not exist at character 108

I ran sudo -i -u postgres; psql -d mydatabase -c "CREATE EXTENSION postgis;", which means to re-activate PostGIS for my particular database, but it said that the extension was not installed, as in the following error message:

ERROR: could not open extension control file "/usr/share/postgresql/10/extension/postgis.control": No such file or directory

I tried to re-install PostGIS as I wrote in my notes:

sudo dnf install postgis 25_12

But I got more errors:

Error: Unable to find a match: postgis25_12

A further search with sudo dnf list postgis returned No matching Packages to list. It happened that somebody updated the system and some packages were removed in the process in order to avoid conflicts.

Here is how I solved it.

Add PostgreSQL 12 repository

Why PostgresSQL 12? Because of the following dependency problems:

1. RHEL 8 offers PostgreSQL 10, which works with PostGIS 2.4, which in turn depends on Armadillo 9.x.
2. RHEL 8 offers Armadillo 12.x, which is incompatible with PostGIS 2.4.
3. The official PostgreSQL repositories for newer PostgreSQL versions (such as 15) lead to more unmet dependencies.
4. The oldest PostgreSQL available in the PostgreSQL repository is 12, which is compatible with PostGIS 3.4, that in turn is compatible with Armadillo 12.x.

I typed cat /etc/redhat-release to check the version of RHEL I was using, and it was 8.10. In my previous notes I had 8.4. Because of this I typed sudo nano /etc/yum.repos.d/pgdg.repo and added the following lines:

[pgdg12]name=PostgreSQL 12 for RHEL8baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-8.10-x86_64enabled=1gpgcheck=0[pgdgextras12]name=PostgreSQL Extras for RHEL8baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-8.10-x86_64/enabled=1gpgcheck=0

Then I updated the repositories with:

sudo dnf clean allsudo dnf makecache

Then I ran:

sudo dnf remove postgres*sudo dnf install postgis34_12

The last step installed the following packages without conflicts:

Installed: CGAL-4.14-1.rhel8.x86_64 SFCGAL-1.4.1-13.rhel8.x86_64 SFCGAL-libs-1.4.1-13.rhel8.x86_64 gdal38-libs-3.8.5-3PGDG.rhel8.x86_64 gmp-c++-1:6.1.2-11.el8.x86_64 gpsbabel-1.6.0-3.el8.x86_64 libarrow-8.0.1-2.el8.x86_64 libdeflate-1.9-3.el8.x86_64 libgeotiff17-1.7.1-6PGDG.rhel8.x86_64 libgeotiff17-devel-1.7.1-6PGDG.rhel8.x86_64 libqhull_r-2015.2-5.el8.x86_64 librttopo-1.1.0-2.rhel8.x86_64 libspatialite50-5.1.0-5PGDG.rhel8.x86_64 libspatialite50-devel-5.1.0-5PGDG.rhel8.x86_64 libusb-1:0.1.5-12.el8.x86_64 postgis34_12-3.4.2-3PGDG.rhel8.x86_64 postgresql12-12.19-3PGDG.rhel8.x86_64 postgresql12-contrib-12.19-3PGDG.rhel8.x86_64 postgresql12-libs-12.19-3PGDG.rhel8.x86_64 postgresql12-server-12.19-3PGDG.rhel8.x86_64 proj94-9.4.0-1PGDG.rhel8.x86_64 qt5-qtsvg-5.15.3-2.el8.x86_64 re2-20190801-1.el8.x86_64 shapelib-1.5.0-12.el8.x86_64 

I checked with psql --version that returned psql (PostgreSQL) 12.19.

Activating PostgresSQL

After running sudo -i -u postgres; psql -d mydatabase I got the following message:

psql: error: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I typed the following commands to start the server:

sudo postgresql-12-setup initdbsudo systemctl start postgresql-12

That made sudo -i -u postgres; psql work.

Creating a new database

I created a new database with the following commands:

CREATE DATABASE mydatabase;\q

Then after typing psql -d mydatabase I ran:

CREATE ROLE student;ALTER ROLE student WITH LOGIN;ALTER ROLE student WITH ENCRYPTED PASSWORD 'SomePassword';GRANT CONNECT ON DATABASE mydatabase TO student;GRANT USAGE ON SCHEMA public TO student;GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO student;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO student;GRANT CREATE ON SCHEMA public TO student;

Connecting from R

I ran:

con <- dbConnect( drv = Postgres(), dbname = Sys.getenv("POSTGRES_DB"), user = Sys.getenv("POSTGRES_USR"), password = Sys.getenv("POSTGRES_PWD"), host = Sys.getenv("POSTGRES_HOST"))

That gave me the following error:

Error: connection to server at "localhost" (::1), port 5432 failed: FATAL: Ident authentication failed for user "student"

One way to fix it is to type sudo nano /var/lib/pgsql/12/data/pg_hba.conf and change these lines:

# "local" is for Unix domain socket connections onlylocal all all peer# IPv4 local connections:host all all 127.0.0.1/32 md5 # IPv6 local connections:host all all ::1/128 md5

Where the change is to replace ident/peer with md5 in the IPv4/6 lines.

Then I ran sudo systemctl restart postgresql-12 and the connection worked.

Installing PostGIS

After running sudo -i -u postgres; psql -d mydatabase I ran:

CREATE EXTENSION postgis;

PostGIS also required sudo systemctl restart postgresql-12.service or then R pretends to write properly, but when you read the table back from SQL, the geometry column is of type pq_NA and of binary type instead of sfc_geometry.

Finally, I was able to write the table with the geometry column from R to PostgreSQL and read it back with the proper geometry column type.