PostGreSQL

SQL-Tipps

Tabellenänderungen

alter table mytable rename table_length to tablesize;
alter table mytable alter table_group type varchar(128);
alter table mytable alter table_reference type int using asset_no::integer;

Reguläre Ausdrücke

select * from polygons where group similar to '[0-9]+';
select * from polygons where group similar to '(Gruppe|Group)[0-9]{2,4}%';

• '.' ist kein Metazeichen, benutze '%' und '_'
• '%' ist Ersatz für .*, '_' für '.'
• Keine Metazeichen für Anfang und Ende nötig, da Muster immer am Anfang gesucht wird und immer komplett verglichen wird.

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+

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';
CREATE ROLE dbusr WITH CREATEDB;
# Der Gruppe dbusr zuordnen:
GRANT dbusr dbadm, forum, postgres;

Benutzer anlegen

• pgadmin3 aufrufen
  • Login-Rollen: neue Rolle eintragen
• SQL:
  • CREATE ROLE jonny LOGIN ENCRYPTED PASSWORD 'md582dfb88f80303e0d0e2ffdbdc9e82b64' SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;

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.

Abfragen der Geometry

# Objekte mit maximalem Abstand von 1000000:
SELECT name FROM geodb WHERE ST_DWithin(geom, 'SRID=4326;POINT(-110 29)'::geography, 1000000);
-- Distance calculation using GEOGRAPHY
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)'::geography);
select ST_AsText(geom) froom geodb;
-- Display with maximal float precision of 2 (digits behind the dot)
select ST_AsText(geom, 2) from geodb;

Sequenz (Primary Key) ändern

ALTER SEQUENCE sysflags_sysflag_id_seq RESTART WITH 1100;

Installation

Interface ändern

• vi /etc/postgresql/13/main/postgresql.conf

listen_addresses = '10.10.100.100,localhost'

Zugriff von anderen Hosts erlauben

• vi /etc/postgresql/13/main/pg_hba.conf

host    all             all             127.0.0.1/32            trust
host    all             all             10.10.100.151/32        trust

Debian

adduser -uid 251 postgres
PG_VERSION=13
apt install postgresql-$PG_VERSION postgresql-$PG_VERSION-postgis-3
passwd postgres
# Cluster festlegen:
DB_BASE=/data/postgresql
POST_BIN=/usr/lib/postgresql/$PG_VERSION/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

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

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

• /etc/postgresql/13/main/postgresql.conf

listen_addresses = '*'
oder
listen_addresses = '10.10.10.1'

• in /etc/postgresql/13/main/pg_hba.conf

# Passwort vom User notwendig:
host    all             all             10.10.10.202/32           md5
# Kein Passwort notwendig
host    all             all             10.10.10.204/32           trust

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 -e -O $owner -h $host -p $port sale
* oder als User  postgres:
psql
create database owner hm;

Datensicherung

# Sichern:
pg_dump dbname > dumpfile
pg_dump -h $HOST -p $PORT dbname | gzip > dbname.sql.gz

# Restaurieren:
psql dbname < dumpfile
psql --set ON_ERROR_STOP=on dbname < dumpfile

# Duplizieren einer DB:
FN=/tmp/current.db.dump
sudo -u postgres pg_dump -h dragon -F c -f $FN pggreenlab
ls -ld $FN
sudo -u postgres pg_restore -h dragon --dbname=pggrlabtest $FN