Bearbeiten von „PostGreSQL“

Zur Navigation springen Zur Suche springen

Warnung: Du bist nicht angemeldet. Deine IP-Adresse wird bei Bearbeitungen öffentlich sichtbar. Melde dich an oder erstelle ein Benutzerkonto, damit Bearbeitungen deinem Benutzernamen zugeordnet werden.

Die Bearbeitung kann rückgängig gemacht werden. Bitte prüfe den Vergleich unten, um sicherzustellen, dass du dies tun möchtest, und veröffentliche dann unten deine Änderungen, um die Bearbeitung rückgängig zu machen.

Aktuelle Version Dein Text
Zeile 1: Zeile 1:
[[Kategorie:DB]]
[[Kategorie:DB]]
[[Kategorie:ServerApplikation]]
[[Kategorie:ServerApplikation]]
= Links =
* https://gis-ops.com/postgrest-postgis-api-tutorial-geospatial-api-in-5-minutes/
* https://postgis.net/docs/reference.html


= SQL-Tipps =
= 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 ==
== DB anlegen ==
<pre>createdb dbtest
<pre>createdb dbtest
</pre>
</pre>
== Services ==
<pre>
postgresql@13-main
postgresql@15-main
</pre>
== Mit DB arbeiten ==
== Mit DB arbeiten ==
* Programm: /usr/bin/psql dbtest
* Programm: /usr/bin/psql dbtest
Zeile 53: Zeile 26:
\du ODER \du+
\du ODER \du+
</pre>
</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 ==
== Geometry-Variante ==
Zeile 144: Zeile 41:
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis;
CREATE TABLE public.geodb (
CREATE TABLE geodb (
   id SERIAL PRIMARY KEY,
   id SERIAL PRIMARY KEY,
   name varchar(64),
   name varchar(64),
  count INT,
  changed TIMESTAMP NULL,
   geom geography(POLYGON,3857)
   geom geography(POLYGON,3857)
);
);
CREATE INDEX geodb_geom_idx ON geodb USING GIST (geom);
CREATE INDEX geodb_geom_idx ON geodb USING GIST (geom);
ALTER TABLE public.geodb OWNER TO dbusr;


select distinct(st_isvalid(geom)) from geodb;
select distinct(st_isvalid(geom)) from geodb;
Zeile 162: Zeile 56:
</pre>
</pre>
* Hinweis: Polygon mit 1000 Ecken erfolgreich eingetragen.
* 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 ==
== Abfragen der Geometry ==
Zeile 177: Zeile 65:
select ST_AsText(geom) froom geodb;
select ST_AsText(geom) froom geodb;
-- Display with maximal float precision of 2 (digits behind the dot)
-- Display with maximal float precision of 2 (digits behind the dot)
select ST_AsText(polygon_gis, 1) from polygons where polygon_id=83;
select ST_AsText(geom, 2) from geodb;
# 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>
</pre>
= Werkzeuge =
<syntaxhighlight lang=bash>
# Alle Datenbanken anzeigen
echo '\list' | sudo -u postgres psql postgres | egrep -o "^ [^ ]+ "
</syntaxhighlight>


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


Zeile 258: Zeile 100:
</syntaxhighlight>
</syntaxhighlight>


== 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';
</pre>


== Ohne Admin-Passwort arbeiten (z.B. temporär) ==
== Ohne Admin-Passwort arbeiten (z.B. temporär) ==
Zeile 263: Zeile 118:
** host    all            all            127.0.0.1/32            trust
** host    all            all            127.0.0.1/32            trust
** host    all            all            ::1/128                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 ==
== Passwort-Authentifizierung erlauben ==
Zeile 281: Zeile 142:
* /etc/postgresql/13/main/postgresql.conf
* /etc/postgresql/13/main/postgresql.conf
<pre>listen_addresses = '*'
<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>
</pre>


Zeile 305: Zeile 157:
== DB erzeugen ==
== DB erzeugen ==
* als User hm:
* als User hm:
<pre>createdb -e -O $owner -h $host -p $port sale
<pre>createdb sale
* oder als User  postgres:
* oder als User  postgres:
psql
psql
create database owner hm;
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>
</pre>

Bitte kopiere keine Webseiten, die nicht deine eigenen sind, benutze keine urheberrechtlich geschützten Werke ohne Erlaubnis des Urhebers!
Du gibst uns hiermit deine Zusage, dass du den Text selbst verfasst hast, dass der Text Allgemeingut (public domain) ist, oder dass der Urheber seine Zustimmung gegeben hat. Falls dieser Text bereits woanders veröffentlicht wurde, weise bitte auf der Diskussionsseite darauf hin. Bitte beachte, dass alle Info-Theke-Beiträge automatisch unter der „Gemeinfreiheit“ stehen (siehe Info-Theke:Urheberrechte für Einzelheiten). Falls du nicht möchtest, dass deine Arbeit hier von anderen verändert und verbreitet wird, dann klicke nicht auf „Seite speichern“.

Abbrechen Bearbeitungshilfe (wird in einem neuen Fenster geöffnet)
Abgerufen von „https://wiki.hamatoma.de/index.php?title=PostGreSQL“