PostgreSQL PostGIS

Spickzettel für meine Lieblings-Datenbank - und PostGIS.

Übrigens, meine Lieblings-Anwendung von PostGIS und GeoJSON: Schumm find

Admin

Zugriff über psql locally:

rschumm@kassandra:~$ psql --username=find --host=localhost --dbname=holz

wenn nötig, kann das Passwort als Umgebungsvariable vorangestellt werden:

PGPASSWORD=[the password from the secret]

Schnell alles neu:

sudo su - postgres
psql
create user holzdemo password 'blablabla';
create database holz with owner holzdemo; 
alter user holzdemo password 'blablabla'; 

jdbc

jdbc:postgresql://localhost:5432/holz?sslmode=disable
org.postgresql.Driver

Metadaten und -befehle psql:

\c explic   connect (to DB explic)
\l          List of Databases
\l+         ...with size etc. 
\dx         Display extensions
\d          Display tables etc. 
\du         Display User 
\?          help about these commands 
\pset pager off

Open Cennnections:

include application_name and query in the output:

SELECT pid, usename, datname, client_addr, client_port, backend_start, state, application_name, query
FROM pg_stat_activity;

Find Configs:

SHOW data_directory;
rschumm@kyburg ~ % pg_config

Abkürzung:

psql -d explic -U stunden -c "select experiment from video";

[luna:~] rschumm% more .pgpass 
localhost:5432:*:postgres:clandestino
[host:port:database:user:password]

config files:

IP listen_address Zugriff von überall * zulassen, wenn nötig:

vi /etc/postgresql/10/main/postgresql.conf  
vi /etc/postgresql/10/main/pg_hba.conf 
vi /var/lib/pgsql/data/.... 
systemctl status postgres (to find out where the files are)


host all all 0.0.0.0/0 trust

Grant-Things:

ALTER USER find SUPERUSER; 
create extension postgis; 

grant all privileges on table video to "userJaJa";

grant all privileges on sequence video_id_seq to "userJaJa";

-- reset the database: 
drop owned by stunden; 

Stats - Connections

Open Connections:

SELECT * FROM pg_stat_activity;

Kill Open Connections for a Database:

SELECT  *
FROM pg_stat_activity
WHERE datname = 'databaseName';

to kill:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'databaseName'
AND pid <> pg_backend_pid();

This ends all other active connections to that database, except for my current connection, so I can drop the database or rename it. Database can then be deleted or renamed with:

DROP DATABASE databaseName;
ALTER DATABASE databaseName RENAME TO newDatabaseName;

Duplicate a Database

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Ubuntu Linux

to find out where the files are:

systemctl status postgres 

restart als sysdamin:

sudo systemctl restart postgresql.service

upgrade a cluster:

rschumm@kassandra:~$sudo su - postgres   
postgres@kassandra:~$ pg_dumpall -U postgres -w > ~/Desktop/dumpAllMyDataEtc.sql
postgres@kassandra:~$ pg_lsclusters 
postgres@kassandra:~$ pg_upgradecluster 1x main
rschumm@kassandra:~$ sudo systemctl daemon-reload
rschumm@kassandra:~$ sudo apt-get install pgadmin4-desktop 

this will migrate the data. 
if reload needed: 
rschumm@kyburg Desktop % psql -f ella.sql -d postgres

macOS

as Entreprise Install:

Quick dump:

[luna:~] rschumm% cd /Library/PostgreSQL/xx/bin
[luna:PostgreSQL/xx/bin] rschumm% ./pg_dumpall -U postgres -w > ~/Desktop/viadukt-db-9.5.sql

or (one database): 
/Library/PostgreSQL/12/bin/pg_dump -U stunden -w  stunden > ~/Desktop/stunden.sql

to reload: 
psql -f ella.sql -d postgres

Access-Rights etc. see blog

Logs:

[luna-1:~] rschumm% sudo su -  postgres
luna-1:~ postgres$ cd /Library/PostgreSQL/12/data/log

Service:

rschumm% sudo launchctl list | grep postgresql
rschumm% sudo launchctl start com.edb.launchd.postgresql-12

as brew:

PostgreSQL and PostGIS on macOS with brew:
install:

brew install postresql 
brew install postgis
brew services start postgresql
brew install --cask pgadmin4
createuser -s postgres 

this will install PostgreSQL with PostGIS running as a macOS Service - with superuser postgres open for localhost access. pgAdmin will be installed as a macOS Application:

initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres

Data-Dir:

brew info postgresql
usally: 
/opt/homebrew/var/postgres
and logs: 
/opt/homebrew/var/log

access:

rschumm@kyburg ~ % psql -U postgres

upgrade (still unclear)

brew services stop postgresql@14

brew install postgresql@16

createuser -s postgres
psql -U postgres
   
postgres=# select * from pg_extension; 

pg_upgrade -b /opt/homebrew/opt/postgresql@14/bin -B /opt/homebrew/opt/postgresql@16/bin  -d  /opt/homebrew/var/postgres -D /opt/homebrew/var/postgresql@16

pg_upgrade  -B /opt/homebrew/opt/postgresql@16/bin  -d  /opt/homebrew/var/postgres -D /opt/homebrew/var/postgresql@16

brew services start postgresql@16

Dump an Restore:

rschumm@kyburg ~ % pg_dumpall -U postgres -w > all.sql
to reload: 
psql -f all.sql -d postgres

Raspberry Pi OS (Debian)

As of 2025, this story was for Raspberry Pi OS (Debian 12 bookworm) and PostgreSQL 15.

a Migration Story

In the beginning, I had a messy setup with several not-used, outdated versions:

Note: all the following commands are run as root.

root@raspberrypi:~# pg_lsclusters
Ver Cluster Port Status                Owner    Data directory               Log file
9.4 main    5432 down,binaries_missing postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
11  main    5432 online                postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
13  main    5433 online                postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log
15  main    5434 online                postgres /var/lib/postgresql/15/main  /var/log/postgresql/postgresql-15-main.log
root@raspberrypi:~# 

Version 11 was running, and as of this writing (March 1, 2025), this was much too old. Other versions were here, but not used.

To begin, I dumped all the data using the «Backup Server» from pgAdmin4 to my Mac for safety.

apt PostgreSQL Repositories and Debian (RaspberryPi OS) Releases

The newest Versions are not available in the standard Debian repositories, so I had to add the PostgreSQL repository:

root@raspberrypi:~# apt install -y postgresql-common
root@raspberrypi:~# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

root@raspberrypi:~# grep -i postgresql /etc/apt/sources.list /etc/apt/sources.list.d/*

root@raspberrypi:~# apt-cache search postgresql | grep -E "^postgresql-[0-9]+"

This showed me the available versions - the newest was 15 for my Raspberry Pi.

Version 17 - the newest - is not there and would have been built from source. The reason for this is that Debian releases «freeze» the software versions by the time of realease, and the newest versions are not available in the standard repositories. The newest versions will be available in the next Debian release, which is Debian 13 (Trixie) scheduled for 2025 later.

Version 15 was already installed, so I had to upgrade the cluster from 11 to 15.

Upgrade the Cluster and Clean Up: pg_upgradecluster

First, clean up:
The command pg_upgradecluster 11 main automatically migrates the cluster with version 11 to the newest, in this case, 15.
Caution: because I already had a cluster with version 15, I had to remove it first with pg_dropcluster 15 main. Be careful to save the data in it if you have. Im my case it was not used.

root@raspberrypi:~# pg_lsclusters
root@raspberrypi:~# systemctl stop postgresql
root@raspberrypi:~# 
root@raspberrypi:~# pg_dropcluster 15 main
root@raspberrypi:~# pg_upgradecluster 11 main

...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 11 main


root@raspberrypi:~# sudo systemctl start postgresql
root@raspberrypi:~# pg_lsclusters

The command should do everything and also re-configure the configs, so that the default port 5432 is used for the new cluster.

This worked: the last command showd the new working cluster in version 15. I checked my applications and the pgAdmin4, and everything was working.

So I removed old version:

root@raspberrypi:~# sudo systemctl stop postgresql
root@raspberrypi:~# pg_lsclusters
Ver Cluster Port Status                Owner    Data directory               Log file
9.4 main    5432 down,binaries_missing postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
11  main    5434 down                  postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
13  main    5433 down                  postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log
15  main    5432 down                  postgres /var/lib/postgresql/15/main  /var/log/postgresql/postgresql-15-main.log
root@raspberrypi:~# pg_dropcluster 11 main
root@raspberrypi:~# pg_dropcluster 13 main
root@raspberrypi:~# pg_dropcluster 9.4 main
root@raspberrypi:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 down   postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
root@raspberrypi:~# sudo systemctl start postgresql

root@raspberrypi:~# apt list --installed | grep postgresql
root@raspberrypi:~# sudo apt remove --purge postgresql-11 postgresql-13 postgresql-client-11 postgresql-client-13
root@raspberrypi:~# sudo apt autoremove

This cleaned up the system and removed the old versions.

Cleaned-up Final State

The final installed stuff was this, including the PostGIS extension, which I already had installed:

root@raspberrypi:~# apt list --installed | grep postgresql

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

postgresql-15-postgis-3-scripts/stable,now 3.3.2+dfsg-1 all [installed,automatic]
postgresql-15-postgis-3/stable,now 3.3.2+dfsg-1+b1 armhf [installed,automatic]
postgresql-15/stable,now 15.9-0+deb12u1 armhf [installed,automatic]
postgresql-client-15/stable,now 15.9-0+deb12u1 armhf [installed,automatic]
postgresql-client-common/stable,now 248 all [installed,automatic]
postgresql-common/stable,now 248 all [installed]
postgresql-postgis-scripts/stable,now 3.3.2+dfsg-1 all [installed,automatic]
postgresql-postgis/stable,now 3.3.2+dfsg-1+b1 armhf [installed,automatic]
postgresql/stable,now 15+248 all [installed]

root@raspberrypi:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
root@raspberrypi:~# 

To access the cluster on commandline and list extensions to verify if PostGIS is installed:

pi@raspberrypi:~ $ sudo su - postgres
postgres@raspberrypi:~$ psql
psql (15.9 ( 15.9-0+deb12u1))
Type "help" for help.

postgres=#
...
select * from pg_extension; 
SELECT * FROM pg_available_extensions;

A final remarkt to security: make sure your cluster is not accessible from the public Internet by coincidence, or even worse without a password. Check the configs. In my case the Server is hidden behind a firewall and only accessible from a secure network zone.

Kubernetes

Artefacts for Kubernetes

a quick and dirty collection of k8s artefacts for a PostgreSQL deployment on our Institute Cluster based on k3s and Rancher:


apiVersion: v1
kind: ConfigMap
metadata:
  name: database-configmap
data:
  quarkus.datasource.username: pm4
  quarkus.datasource.jdbc.url: jdbc:postgresql://postgresql-service:5432/pm4?ApplicationName=PM4 Hallo
---
apiVersion: v1
kind: Secret
metadata:
  name: database-secret
type: Opaque
data:
  quarkus.datasource.password: adfadfasdfasdf # base64 encoded password
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgresql-pvc
spec:
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
  storageClassName: cinder-csi  # Stellen Sie sicher, dass dies zu Ihrer StorageClass passt
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgresql-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgresql
  template:
    metadata:
      labels:
        app: postgresql
    spec:
      containers:
      - name: postgres
        image: postgres:17.4
        env:
        - name: POSTGRES_USER
          valueFrom:
            configMapKeyRef:
              name: database-configmap
              key: quarkus.datasource.username
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: database-secret
              key: quarkus.datasource.password
        - name: POSTGRES_DB
          value: pm4
        ports:
        - containerPort: 5432
        volumeMounts:
        - mountPath: /var/lib/postgresql/data
          name: postgresql-storage
      volumes:
      - name: postgresql-storage
        persistentVolumeClaim:
          claimName: postgresql-pvc

---
apiVersion: v1
kind: Service
metadata:
  name: postgresql-service
spec:
  type: ClusterIP
  selector:
    app: postgresql
  ports:
  - port: 5432
    targetPort: 5432

Nix - NixOS

on macOS:

tbd…

RHEL / Fedora

hard stuff:

yum (etc.) install @postgresql 
sudo rm -rf /var/lib/pgsql/data
sudo postgresql-setup initdb

sudo systemctl restart postgresql

Other Tricks

WAL-PANIC

korruptes WAL:
(usually happen on macOS after the reboot kernel-panic - no idea why)

2020-08-29 10:40:31.092 CEST [35132] LOG:  database system was shut down at 2020-07-02 14:10:10 CEST
2020-08-29 10:40:31.093 CEST [35132] LOG:  invalid record length at 0/B71E678: wanted 24, got 0
2020-08-29 10:40:31.093 CEST [35132] LOG:  invalid primary checkpoint record
2020-08-29 10:40:31.093 CEST [35132] PANIC:  could not locate a valid checkpoint record
[luna-1:~] rschumm% sudo su -  postgres
luna-1:~ postgres$ cd /Library/PostgreSQL/12/
luna-1:12 postgres$ bin/pg_resetwal data/
Write-ahead log reset

cf: gelöst durch Reset von WAL:

Geo

Breite - lat - Nord-Süd (47°…)
Länge - long - West-Ost (8°…)
meistens lat/long, bei PostGIS verkehrt.

update häcks:

fix PostGIS Version:

select * from pg_extension; 
alter extension postgis update to '2.5.5'; 

Generate GeoJSON with PostGIS

note: starting with PostGIS 3, st_AsGeoJSON(row)should be able to generate a whole feature. This example is for PostGIS 2.. but works also for 3. of course.

create or replace view geojson as 
select json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg( 
                    json_build_object(
                        'type', 'Feature', 
                        'geometry', st_AsGeoJSON(shape)::json, 
                        'properties', json_build_object (
                            'uuid', id, 
                            'plan', plan, 
                            'description', description, 
                            'area', st_area(shape)
                        ) 
                    )
                )
    ), plan
from geopart
group by plan

zurück zum Seitenanfang