PostgreSQL PostGIS

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

Schnell alles neu:

sudo su - postgres

create user holzdemo password 'blablabla';
create database holz with owner holzdemo; 
alter user holzdemo password 'blablabla'; 



Metadaten und -befehle psql:

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


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

[luna:~] rschumm% more .pgpass 

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 trust


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;

Admin 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

Admin 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


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


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

as brew:

PostgreSQL and PostGIS on macOS with brew:

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


brew info postgresql
and logs: 

Admin RHEL / Fedora

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.

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( 
                        '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