PostGreSQL: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
Zeile 34: | Zeile 34: | ||
* EPSG:3857 | * EPSG:3857 | ||
** https://spatialreference.org/ref/sr-org/6864/ | ** https://spatialreference.org/ref/sr-org/6864/ | ||
** Verwendung: | ** Verwendung: Openstreetmap | ||
** Bereich: | ** Bereich: 0, 10E7 | ||
== Tabelle mit GIS-Daten anlegen == | == Tabelle mit GIS-Daten anlegen == | ||
<pre> | <pre> |
Version vom 13. Februar 2022, 16:42 Uhr
SQL-Tipps
DB anlegen
createdb dbtest
Mit DB arbeiten
- Programm: /usr/bin/psql dbtest
- Oder: sudo -u postgres psql postgres
CREATE DATABASE geodb OWNER = forum; # Datenbanken auflisten: \list ODER \l # Relationen auflisten: \dt # Relationen mit mehr Infos: \dt+ # Definition der Table xxx: \d xxx # Mit DB yyy verbinden: \c yyy # Alternative: Tabellen auflisten: SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'; # Benutzer auflisten: \du ODER \du+
Geometry-Variante
- EPSG Projection 4326 - WGS 84
- https://spatialreference.org/ref/epsg/4326/
- Verwendung: Horizontal component of 3D system. Used by the GPS satellite navigation system and for NATO military geodetic surveying.
- Bereich: -180.0000, -90.0000, 180.0000, 90.0000
- EPSG:3857
- https://spatialreference.org/ref/sr-org/6864/
- Verwendung: Openstreetmap
- Bereich: 0, 10E7
Tabelle mit GIS-Daten anlegen
CREATE EXTENSION IF NOT EXISTS plpgsql; CREATE EXTENSION postgis; CREATE TABLE geodb ( id SERIAL PRIMARY KEY, name varchar(64), geom geography(POLYGON,3857) ); CREATE INDEX geodb_geom_idx ON geodb USING GIST (geom); select distinct(st_isvalid(geom)) from geodb; INSERT into geodb (name, geom) VALUES ('3-Eck', ST_GeomFromEWKT('SRID=4326;POLYGON((10.4267776 47.8347264, 10.4277776 47.8357264,10.4287776 47.8367264,10.4287776 47.8357264,10.4267776 47.8347264))'), ('4-Eck', ST_GeomFromEWKT('SRID=4326;POLYGON((10.4267776 47.8347264, 10.4277776 47.8357264,10.4287776 47.8367264,10.4287776 47.8367264,10.4297776 47.8357264,10.4267776 47.8347264))') ;
- Hinweis: Polygon mit 1000 Ecken erfolgreich eingetragen.
Installation
Debian
apt install postgresql-13 postgresql-13-postgis-3
passwd postgres
# Cluster festlegen:
DB_BASE=/data/postgresql
POST_BIN=/usr/lib/postgresql/13/bin
LOGFILE=/var/log/local/postgres.log
test -d $DB_BASE || mkdir $DB_BASE
chown postgres $DB_BASE
sudo -u postgres $POST_BIN/initdb -D $DB_BASE
- Änderungen in /etc/postgresql/13/main/postgresql.conf
data_directory=/data/postgresql ... log_destination = 'syslog' ... log_directory = '/var/log/local' ... full_page_writes = off
systemctl list-units | grep post
systemctl restart postgresql@13-main.service
Rollen
- Eine Rolle kann als "Gruppe" oder als "Benutzer" verwendet werden.
- Ein Benutzer ist eine Rolle mit Attribut LOGIN.
- "CREATE USER" ist ein Alias von "CREATE ROLE" mit gesetztem "LOGIN"-Attribut.
sudo -u postgres psql postgres
SELECT rolname FROM pg_roles; CREATE ROLE root WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'NoOneKnows'; CREATE ROLE dbadm WITH CREATEDB CREATEROLE LOGIN PASSWORD 'UnknownPw'; CREATE ROLE forum WITH CREATEDB LOGIN PASSWORD 'ThinkAbout';
Ohne Admin-Passwort arbeiten (z.B. temporär)
- /etc/postgresql/9.5/main/pg_hba.conf
- host all all 127.0.0.1/32 trust
- host all all ::1/128 trust
Benutzer anlegen
- pgadmin3 aufrufen
- Login-Rollen: neue Rolle eintragen
- SQL:
- CREATE ROLE jonny LOGIN ENCRYPTED PASSWORD 'md582dfb88f80303e0d0e2ffdbdc9e82b64' SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
Passwort-Authentifizierung erlauben
Linux-User werden zu PostgreSql-Rollen
vi /var/lib/pgsql/data/pg_hba.conf # change host all all 127.0.0.1/32 ident host all all ::1/128 ident #to host all all 127.0.0.1/32 md5 host all all ::1/128 md5 systemctl restart postgresql
DBMS-Zugriff von außen ermöglichen
- /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
Rolle einrichten
sudo -u postgres -i createuser --interactive # z.B. hm einrichten # oder createuser hm # Passwort setzen als user postgres: psql \password hm
DB erzeugen
- als User hm:
createdb sale * oder als User postgres: psql create database owner hm;