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 RHEL8
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-8.10-x86_64
enabled=1
gpgcheck=0

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

Then I updated the repositories with:

sudo dnf clean all
sudo 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 initdb
sudo 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 only
local   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.