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:
- RHEL 8 offers PostgreSQL 10, which works with PostGIS 2.4, which in turn depends on Armadillo 9.x.
- RHEL 8 offers Armadillo 12.x, which is incompatible with PostGIS 2.4.
- The official PostgreSQL repositories for newer PostgreSQL versions (such as 15) lead to more unmet dependencies.
- 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.