PostgreSQL + PostGIS + PgAdmin4 install and maintenance notes

bluesanta · March 29, 2026, 9:55pm

Hello,

This isn’t a request for support. I was able to troubleshoot with the help of Claude.

These are the notes on what we did to get PostgreSQL 17.x and PostGIS up and running on OM.

PostgreSQL and PgAdmin4

PostGIS

IMPORTANT

PostGIS, and any other extensions, must be installed in the PostgreSQL database BEFORE attempting to import any previous databases.

Any table that has postgis data will not be imported if postgis is not installed before the attempt.

OM has it in the repos. No need to install it the website way. (I was lost for some time trying to activate the epel-release repo).

Notes from Claude on Install Process

We had to do a super involved install, because OpenMandriva doesn’t keep the repos up to date and PostGIS and PostgreSQL have to version match.

OpenMandriva: PostgreSQL 17 + PostGIS Setup Guide

Problem Context

OpenMandriva’s rolling release upgraded PostgreSQL from 17 to 18 before PostGIS was updated to support version 18, breaking the native installation. This guide rebuilds PostgreSQL 17 from source and uses the OpenMandriva PostGIS package (which is built for PostgreSQL 17).

Complete Installation Steps

1. Remove Existing PostgreSQL Installation

# Stop all PostgreSQL services
sudo systemctl stop postgresql
sudo systemctl disable postgresql

# Remove all PostgreSQL packages
sudo dnf remove postgresql* -y

# Backup data directory (if needed)
sudo cp -r /var/lib/pgsql /var/lib/pgsql.backup.$(date +%F)

# Clean up data directories
sudo rm -rf /var/lib/pgsql/data
sudo rm -rf /var/lib/postgresql/*
sudo rm -rf /etc/postgresql/*

# Clean package cache
sudo dnf clean all
sudo dnf makecache

# Verify complete removal
rpm -qa | grep postgresql  # Should return nothing

2. Install Build Dependencies

# Install PostgreSQL build dependencies
sudo dnf install -y \
    gcc gcc-c++ make \
    lib64readline-devel lib64z-devel \
    lib64openssl-devel lib64xml2-devel lib64xslt-devel \
    lib64systemd-devel lib64icu-devel \
    perl-IPC-Run perl-Test-Simple perl-devel \
    lib64python-devel tcl-devel

3. Build PostgreSQL 17 from Source

# Download PostgreSQL 17.7 source
cd /tmp
wget https://ftp.postgresql.org/pub/source/v17.7/postgresql-17.7.tar.gz
tar xzf postgresql-17.7.tar.gz
cd postgresql-17.7

# Configure with standard options
./configure \
    --prefix=/usr/local/pgsql \
    --with-openssl \
    --with-libxml \
    --with-libxslt \
    --with-icu \
    --with-systemd

# Compile (uses all CPU cores)
make -j$(nproc)

# Install
sudo make install

# Build and install contrib modules (includes pg_trgm, etc.)
cd contrib
make -j$(nproc)
sudo make install

4. Create PostgreSQL User and Initialize Database

# Create postgres system user (skip if already exists)
sudo useradd -r -m -d /var/lib/pgsql -s /bin/bash postgres

# Create data directory with correct permissions
sudo mkdir -p /var/lib/pgsql/data
sudo chown -R postgres:postgres /var/lib/pgsql

# Add PostgreSQL binaries to system PATH
echo 'export PATH=/usr/local/pgsql/bin:$PATH' | sudo tee /etc/profile.d/postgresql.sh
source /etc/profile.d/postgresql.sh

# Initialize database cluster
sudo -u postgres /usr/local/pgsql/bin/initdb -D /var/lib/pgsql/data

5. Create systemd Service

# Create PostgreSQL systemd service file
sudo tee /etc/systemd/system/postgresql.service > /dev/null <<'EOF'
[Unit]
Description=PostgreSQL 17 Database Server
After=network.target

[Service]
Type=notify
User=postgres
Group=postgres
Environment=PGDATA=/var/lib/pgsql/data
ExecStart=/usr/local/pgsql/bin/postgres -D /var/lib/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target
EOF

# Enable and start PostgreSQL
sudo systemctl daemon-reload
sudo systemctl enable postgresql
sudo systemctl start postgresql

# Verify PostgreSQL is running
sudo systemctl status postgresql
/usr/local/pgsql/bin/psql --version
sudo -u postgres /usr/local/pgsql/bin/psql -c "SELECT version();"

6. Install PostGIS from OpenMandriva Repos

# Install PostGIS (built for PostgreSQL 17)
sudo dnf install -y postgis

# Verify PostGIS files locations
rpm -ql postgis | grep -E '\.so|\.control'

7. Link PostGIS to Custom PostgreSQL Installation

# Create symlinks for PostGIS libraries
sudo ln -s /usr/lib64/postgresql/postgis-3.so /usr/local/pgsql/lib/
sudo ln -s /usr/lib64/postgresql/postgis_raster-3.so /usr/local/pgsql/lib/
sudo ln -s /usr/lib64/postgresql/postgis_topology-3.so /usr/local/pgsql/lib/

# Create symlinks for PostGIS extension control files
sudo mkdir -p /usr/local/pgsql/share/extension
sudo ln -s /usr/share/postgresql/extension/postgis* /usr/local/pgsql/share/extension/

# Verify symlinks
ls -la /usr/local/pgsql/lib/ | grep postgis
ls -la /usr/local/pgsql/share/extension/ | grep postgis

8. Test PostGIS Installation

# Enable PostGIS extension in default database
sudo -u postgres /usr/local/pgsql/bin/psql -c "CREATE EXTENSION postgis;"

# Verify PostGIS version
sudo -u postgres /usr/local/pgsql/bin/psql -c "SELECT PostGIS_Version();"

Expected output:

            postgis_version
---------------------------------------
 3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

9. Restore Database from Backup (if applicable)

# Restore from pg_dumpall backup
sudo -u postgres /usr/local/pgsql/bin/psql -f /path/to/backup.sql 2>&1 | tee restore.log

# Check for errors (ignore "role already exists")
grep -i error restore.log

# Verify databases restored
sudo -u postgres /usr/local/pgsql/bin/psql -c "\l"

10. Install SBDC Drivers to Connect LibreOffice and PostgreSQL

sudo dnf install libreoffice-postgresql

11. postgres psql Path

sudo -su postgres
echo 'export PATH=/usr/local/pgsql/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

Key Points

Why This Works:

PostgreSQL 17.7 is built from source, so it’s decoupled from OpenMandriva’s package updates
PostGIS 3.5 from OpenMandriva repos is already compiled for PostgreSQL 17
Symlinks allow the custom PostgreSQL to use the system’s PostGIS libraries

Maintenance Notes:

When OpenMandriva eventually updates PostGIS for PostgreSQL 18, you can choose to migrate
Security updates for PostgreSQL require rebuilding from source
PostGIS updates from dnf will automatically update the shared libraries

File Locations:

PostgreSQL binaries: /usr/local/pgsql/bin/
PostgreSQL libraries: /usr/local/pgsql/lib/
PostgreSQL data: /var/lib/pgsql/data/
PostGIS libraries: /usr/lib64/postgresql/ (symlinked)
PostGIS extensions: /usr/share/postgresql/extension/ (symlinked)

Quick Verification Commands

# Check PostgreSQL version
/usr/local/pgsql/bin/psql --version

# Check PostgreSQL status
sudo systemctl status postgresql

# Check PostGIS availability
sudo -u postgres /usr/local/pgsql/bin/psql -c "\dx"

# List all databases
sudo -u postgres /usr/local/pgsql/bin/psql -c "\l"

# Connect to a database
sudo -u postgres /usr/local/pgsql/bin/psql -d database_name

Troubleshooting

PostgreSQL won’t start:

# Check logs
sudo journalctl -u postgresql -n 50

# Check data directory permissions
ls -la /var/lib/pgsql/

PostGIS extension not found:

# Verify symlinks exist
ls -la /usr/local/pgsql/lib/ | grep postgis
ls -la /usr/local/pgsql/share/extension/ | grep postgis

# Recreate symlinks if missing (see Step 7)

Missing extension (pg_trgm, etc.):

# Rebuild contrib modules
cd /tmp/postgresql-17.7/contrib
make -j$(nproc)
sudo make install

Future PostgreSQL Updates

Backup first — using the actual backup path:

sudo -u postgres /usr/local/pgsql/bin/pg_dumpall > /mnt/appa/backups/pg_dump_all_archive/auto-gen-dumps/pg-dump-$(date +%F)-pre-upgrade.sql

Download and build:

cd /tmp
wget https://ftp.postgresql.org/pub/source/v17.X/postgresql-17.X.tar.gz
tar xzf postgresql-17.X.tar.gz
cd postgresql-17.X
./configure --prefix=/usr/local/pgsql --with-openssl --with-libxml --with-libxslt --with-icu --with-systemd
make -j$(nproc)
sudo make install
cd contrib
make -j$(nproc)
sudo make install

Restart PostgreSQL:

sudo systemctl restart postgresql

Re-verify PostGIS symlinks (rebuild may have cleared them):

ls -la /usr/local/pgsql/lib/postgis*

# If any are missing, restore them:
sudo ln -s /usr/lib64/postgresql/postgis-3.so /usr/local/pgsql/lib/
sudo ln -s /usr/lib64/postgresql/postgis_raster-3.so /usr/local/pgsql/lib/
sudo ln -s /usr/lib64/postgresql/postgis_topology-3.so /usr/local/pgsql/lib/

Reinstalling PostGIS After an OM System Update

OM system updates can remove the postgis package, breaking the symlinks.
If pg_dumpall fails with "could not access file $libdir/postgis-3", do this:

    # Step 1: Create GDAL compatibility symlink (OM ships .38, PostGIS expects .37)
    sudo ln -s /usr/lib64/libgdal.so.38 /usr/lib64/libgdal.so.37
    sudo ldconfig

    # Step 2: Force-install PostGIS RPM bypassing dependency check
    cd ~/env/setup   # or wherever you keep setup files
    sudo dnf download postgis
    sudo rpm -ivh --nodeps postgis-*.omv*.x86_64.rpm

    # Step 3: Restore symlinks
    sudo ln -s /usr/lib64/postgresql/postgis-3.so /usr/local/pgsql/lib/postgis-3.so
    sudo ln -s /usr/lib64/postgresql/postgis_raster-3.so /usr/local/pgsql/lib/postgis_raster-3.so
    sudo ln -s /usr/lib64/postgresql/postgis_topology-3.so /usr/local/pgsql/lib/postgis_topology-3.so

    # Step 4: Test
    sudo -u postgres /usr/local/pgsql/bin/pg_dumpall > ~/test_backup.sql && echo "SUCCESS"

Note: The GDAL version numbers (.37, .38) may drift over time as OM updates. If the symlink trick stops working, check ls /usr/lib64/libgdal.so* and update accordingly.

Backups

There is a backup script in:

/mnt/appa/backups/pg_dump_all_archive/scripts/pg_backup.sh

To restore:

# 1. Stop PostgreSQL
sudo systemctl stop postgresql

# 2. Remove existing data directory
sudo rm -rf /var/lib/pgsql/data

# 3. Reinitialize empty database
sudo -u postgres /usr/local/pgsql/bin/initdb -D /var/lib/pgsql/data

# 4. Start PostgreSQL
sudo systemctl start postgresql

# 5. Restore from backup
sudo -u postgres /usr/local/pgsql/bin/psql -f /mnt/appa/backups/pg_dump_all_archive/auto-gen-dumps/2026-01-17-164010-pg-dump-all.sql 2>&1 | tee restore.log

# 6. Check for errors (ignore "role already exists")
grep -i error restore.log

# 7. Verify databases restored
sudo -u postgres /usr/local/pgsql/bin/psql -c "\l"

Install pgAdmin4

flatpak install flathub org.pgadmin.pgadmin4

Update pgAdmin4

flatpak update org.pgadmin.pgadmin4

zeroability · March 30, 2026, 11:21am

You should probably report that to Issues on GH:

I don’t think that is really needed. It looks like instructions from somewhere else. If you used other resources (especially any AI or LLM) you need to provide citation.

These should probably be package update requests filed at the same Issues page mentioned earlier. We can’t really add this to Resources until the applications are sourced from our repos. I will move this to Coffee break instead.

system · April 14, 2026, 11:21am

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.