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]]
= Debian=
[[Kategorie:ServerApplikation]]
== Installation ==
 
<pre>apt-get install postgresql-9.5 pgadmin3
= Links =
oder
* https://gis-ops.com/postgrest-postgis-api-tutorial-geospatial-api-in-5-minutes/
yum install postgresql-server postgresql-contrib
* 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>
</pre>


== Services ==
== Ohne Admin-Passwort arbeiten (z.B. temporär) ==
<pre>
* /etc/postgresql/9.5/main/pg_hba.conf
postgresql@13-main
** host    all            all            127.0.0.1/32            trust
postgresql@15-main
** host    all            all            ::1/128                trust
</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 ==
== Benutzer anlegen ==
Zeile 129: Zeile 17:
** CREATE ROLE jonny LOGIN  ENCRYPTED PASSWORD 'md582dfb88f80303e0d0e2ffdbdc9e82b64'  SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
** CREATE ROLE jonny LOGIN  ENCRYPTED PASSWORD 'md582dfb88f80303e0d0e2ffdbdc9e82b64'  SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;


 
= Centos7 =
== Geometry-Variante ==
== Installation ==
* EPSG Projection 4326 - WGS 84
<pre>yum install postgresql-server postgresql-contrib
** 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>
</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
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
</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 ==
== Passwort-Authentifizierung erlauben ==
Linux-User werden zu PostgreSql-Rollen
Linux-User werden zu PostgreSql-Rollen
Zeile 279: Zeile 36:


== DBMS-Zugriff von außen ermöglichen ==
== DBMS-Zugriff von außen ermöglichen ==
* /etc/postgresql/13/main/postgresql.conf
* /var/lib/pgsql/data/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 53:
== 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“