Bearbeiten von „PostGreSQL“

[[Kategorie:DB]]
[[Kategorie:ServerApplikation]]

= Links =
* https://gis-ops.com/postgrest-postgis-api-tutorial-geospatial-api-in-5-minutes/
* https://postgis.net/docs/reference.html

= SQL-Tipps =
== Tabellenänderungen ==
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

== Reguläre Ausdrücke ==
<syntaxhighlight lang="sql">
select * from polygons where group similar to '[0-9]+';
select * from polygons where group similar to '(Gruppe|Group)[0-9]{2,4}%';
</syntaxhighlight>
* '.' 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 ==
<pre>createdb dbtest
</pre>

== Services ==
<pre>
postgresql@13-main
postgresql@15-main
</pre>

== Mit DB arbeiten ==
* Programm: /usr/bin/psql dbtest
* Oder: sudo -u postgres psql postgres
<pre>
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+
</pre>

== 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.
<pre>
sudo -u postgres psql postgres
</pre>
<pre>
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 TO dbadm, forum, postgres;
# Zugriff in Datenbank erlauben
GRANT SELECT,INSERT,UPDATE,DELETE,RULE,REFERENCES,TRIGGER,CREATE,TEMPORARY,EXECUTE,USAGE ON mytable TO joe;
# Zugriff für DB erlauben:
GRANT CREATE,TEMPORARY,TEMP ON DATABASE dbname TO joe;
GRANT ALL PRIVILEGES ON DATABASE vanadium TO joe;
# Passwort ändern:
ALTER USER madonna WITH PASSWORD 'MySecret';
</pre>
<pre>
# Benutzer auflisten:
sudo -u postgres psql postgres <<EOS
\du+
EOS
# Superuser dba und Gruppe admins anlegen.
USER=dba
GROUP=admins
CODE=TopSecret
sudo -u postgres psql postgres <<EOS
CREATE ROLE $GROUP WITH SUPERUSER CREATEDB CREATEROLE;
CREATE ROLE $USER WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD '$CODE';
GRANT $GROUP TO $USER;
EOS
# Readonly-Admin dbbup mit Gruppe roadmins anlegen:
USER=dbbup
GROUP=roadmins
CODE=TopSecret
sudo -u postgres psql postgres <<EOS
CREATE ROLE $GROUP;
CREATE ROLE $USER WITH LOGIN PASSWORD '$CODE';
GRANT $GROUP TO $USER;
GRANT USAGE ON SCHEMA public TO $GROUP;
EOS
# DB pgdb anlegen mit Benutzer dbuser und Gruppe pgdb:
DB=pgdb
USER=dbuser
GROUP=g_$DB
CODE=TopSecret
sudo -u postgres psql postgres <<EOS
CREATE ROLE $GROUP;
CREATE ROLE $USER WITH LOGIN PASSWORD '$CODE';
GRANT $GROUP TO $USER, admins;
CREATE DATABASE $DB OWNER = $GROUP;
GRANT ALL ON $DB TO $GROUP;
GRANT SELECT ON $DB TO roadmins;
GRANT CONNECT ON DATABASE $DB TO roadmins;
EOS
</pre>
=== Eigentümer ändern ===
<pre>
ALTER database xy OWNER TO dbusr;
ALTER scheme public OWNER TO dbusr;
ALTER TABLE table1 OWNER TO dbusr;
</pre>

== 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 ==
<pre>
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION postgis;
CREATE TABLE public.geodb (
  id SERIAL PRIMARY KEY,
  name varchar(64),
  count INT,
  changed TIMESTAMP NULL,
  geom geography(POLYGON,3857)
);
CREATE INDEX geodb_geom_idx ON geodb USING GIST (geom);
ALTER TABLE public.geodb OWNER TO dbusr;

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))')
;
</pre>
* Hinweis: Polygon mit 1000 Ecken erfolgreich eingetragen.

=== SRID für Spalte ändern ===
<pre>
ALTER TABLE roads
 ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 3857) USING ST_Transform(ST_SetSRID(geom,4326),3857) ;
</pre>

== Abfragen der Geometry ==
<pre>
# 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(polygon_gis, 1) from polygons where polygon_id=83;
# POLYGON((727382.1 5432550.3,727389.8 5432536.6,727403.4...))
select ST_AsEWKT(polygon_gis) from polygons where polygon_id=83;
# SRID=3035;POLYGON((727382.11 5432550.29,727389.78 ....))
</pre>

== Spaltentyp GEOMETRYCOLLECTION ==
* https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry
<pre>
GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10))
POINT ZM (1 1 5 60)
POINT M (1 1 80)
POINT EMPTY
MULTIPOLYGON EMPTY
TRIANGLE((0 0 0,0 1 0,1 1 0,0 0 0))
TIN (((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)))
POLYHEDRALSURFACE Z ( PATCHES
    ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
    ((0 0 0, 0 1 0, 0 1 1, 0 0 1, 0 0 0)),
    ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
    ((1 1 1, 1 0 1, 0 0 1, 0 1 1, 1 1 1)),
    ((1 1 1, 1 0 1, 1 0 0, 1 1 0, 1 1 1)),
    ((1 1 1, 1 1 0, 0 1 0, 0 1 1, 1 1 1))
  )
</pre>

== Sequenz (Primary Key) ändern ==
<pre>
ALTER SEQUENCE sysflags_sysflag_id_seq RESTART WITH 1100;
</pre>

= Werkzeuge =
<syntaxhighlight lang=bash>
# Alle Datenbanken anzeigen
echo '\list' | sudo -u postgres psql postgres | egrep -o "^ [^ ]+ "
</syntaxhighlight>

= Installation =
== Interface ändern ==
* vi /etc/postgresql/13/main/postgresql.conf
<pre>listen_addresses = '10.10.100.100,localhost'
</pre>

== Zugriff von anderen Hosts erlauben ==
* vi /etc/postgresql/13/main/pg_hba.conf
<pre>
host    all             all             127.0.0.1/32            trust
host    all             all             10.10.100.151/32        trust
</pre>

== Debian ==

<syntaxhighlight lang="bash">
adduser -uid 251 postgres
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
</syntaxhighlight>
* Änderungen in /etc/postgresql/13/main/postgresql.conf
<pre>
data_directory=/data/postgresql
...
log_destination = 'syslog'
...
log_directory = '/var/log/local'
...
full_page_writes = off

</pre>
<syntaxhighlight lang="bash">
systemctl list-units | grep post
systemctl restart postgresql@13-main.service
</syntaxhighlight>


== 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

<pre>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
</pre>

== DBMS-Zugriff von außen ermöglichen ==
* /etc/postgresql/13/main/postgresql.conf
<pre>listen_addresses = '*'
oder
listen_addresses = '10.10.10.1'
</pre>
* in /etc/postgresql/13/main/pg_hba.conf
<pre>
# 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
</pre>

== Rolle einrichten ==
<pre>sudo -u postgres -i
createuser --interactive
# z.B. hm einrichten
# oder
createuser hm
# Passwort setzen als user postgres:
psql
\password hm
</pre>

== DB erzeugen ==
* als User hm:
<pre>createdb -e -O $owner -h $host -p $port sale
* oder als User  postgres:
psql
create database owner hm;
</pre>

= Datensicherung / Backup =
<pre># Sichern:
pg_dump dbname > dumpfile
pg_dump -h $HOST -p $PORT dbname | gzip > dbname.sql.gz
pg_dumpall > dumpfile
# Restaurieren:
psql dbname < dumpfile
psql --set ON_ERROR_STOP=on dbname < dumpfile
pg_restore 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

# DB auf anderen Server kopieren
pg_dump -h host1 dbname | psql -h host2 dbname

# Rollen exportieren:
pg_dumpall --roles-only >/tmp/roles.sql
# Restaurieren:
sudo -u postgres psql postgres < /tmp/roles.sql
</pre>
== Backup und Restore ==

Formate:
* c custom (ist komprimiert)
* d=Verzeichnis
* t tar
* p plain text

= Sequenzen (lastval) =
<pre>
\ds
oder
SELECT sequence_schema, sequence_name FROM information_schema.sequences ORDER BY sequence_name;

CREATE SEQUENCE IF NOT EXISTS configurations_configuration_id_seq MINVALUE 2000;
SELECT max(configuration_id) from configurations;
ALTER SEQUENCE configurations_configuration_id_seq MINVALUE 2000;
</pre>
<pre>
CREATE SEQUENCE IF NOT EXISTS configurations_configuration_id_seq;
SELECT setval('configurations_configuration_id_seq', (SELECT 10+max(configuration_id) FROM configurations));
ALTER TABLE objects ALTER COLUMN configuration_id SET DEFAULT nextval('configurations_configuration_id_seq');
</pre>

* SqlSeq.py
<pre>
#! /usr/bin/python3
import sys
def Create(table: str, id: str):
  seq = f'{table}_{id}_seq'
  print(f'''
CREATE SEQUENCE IF NOT EXISTS {seq};
SELECT setval('{seq}', (SELECT 10+max({id}) FROM {table}));
ALTER TABLE objects ALTER COLUMN {id} SET DEFAULT nextval('{seq}');
''')
def main(argv):
  if len(argv) < 1:
    print("+++ missing table")
  else:
    table = argv[0]
    id = argv[1] if len(argv) > 1 else table[0:-1] + "_id"
    Create(table, id)

main(sys.argv[1:])
</pre>