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
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
\dx Display extensions
\d Display tables etc.
\du Display User
\? help about these commands
\pset pager off
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();
Database can be deleted and renamed with:
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
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