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 =
Zeile 25: Zeile 21:
== DB anlegen ==
== DB anlegen ==
<pre>createdb dbtest
<pre>createdb dbtest
</pre>
== Services ==
<pre>
postgresql@13-main
postgresql@15-main
</pre>
</pre>


Zeile 69: Zeile 59:
# Der Gruppe dbusr zuordnen:
# Der Gruppe dbusr zuordnen:
GRANT dbusr TO dbadm, forum, postgres;
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>
</pre>
=== Eigentümer ändern ===
=== Eigentümer ändern ===
Zeile 144: Zeile 88:
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 103:
</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 112:
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>
</pre>
== Sequenz (Primary Key) ändern ==
== Sequenz (Primary Key) ändern ==
<pre>
<pre>
ALTER SEQUENCE sysflags_sysflag_id_seq RESTART WITH 1100;
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 =
Zeile 311: Zeile 216:
</pre>
</pre>


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


# Duplizieren einer DB:
# Duplizieren einer DB:
Zeile 326: Zeile 230:
ls -ld $FN
ls -ld $FN
sudo -u postgres pg_restore -h dragon --dbname=pggrlabtest $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“