Spickzettel für meine Lieblings-Datenbank - und PostGIS.
Übrigens, meine Lieblings-Anwendung von PostGIS und GeoJSON: Schumm find
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
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;
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;
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
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
[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
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
As of 2025, this story was for Raspberry Pi OS (Debian 12 bookworm) and PostgreSQL 15.
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) ReleasesThe 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.
pg_upgradeclusterFirst, 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.
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.
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
on macOS:
tbd…
hard stuff:
yum (etc.) install @postgresql
sudo rm -rf /var/lib/pgsql/data
sudo postgresql-setup initdb
sudo systemctl restart postgresql
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:
Breite - lat - Nord-Süd (47°…)
Länge - long - West-Ost (8°…)
meistens lat/long, bei PostGIS verkehrt.
fix PostGIS Version:
select * from pg_extension;
alter extension postgis update to '2.5.5';
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