Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » datenbank » postgresql
Zuletzt angesehen: • postgresql
datenbank:postgresql

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
datenbank:postgresql [2025-08-09 17:32:16] – [Anleitungen bzw. Beispiele] manfreddatenbank:postgresql [2026-05-15 10:46:00] (aktuell) – manfred
Zeile 1: Zeile 1:
 +====== PostgreSQL ======
 +
 +  * [[http://www.postgresql.org/]]
 +  * **[[https://www.codeflow.site/de/article/introduction-to-queries-postgresql|Eine Einführung in Abfragen in PostgreSQL]]**
 +  * [[http://www.heise.de/ix/news/foren/S-PostgreSQL-9-0-Beta-mit-eingebauter-Replikation/forum-178753/list/]]
 +  * [[https://wiki.postgresql.org/wiki/FAQ/de]]
 +  * [[http://burger-ag.de/postgresql.whtml]]
 +
 +Seit ''PostgreSQL 9.0 (2010)'' gibt es eine eingebaute Replikation (Streaming Replication: [[::datenbank:PostgreSQL WAL-Shipping]]).
 +
 +  * [[http://wiki.postgresql.org/]]
 +  * [[http://www.pg-forum.de/|deutsches PostgreSQL Forum]]
 +
 +  * **[[http://www.postgresql.de/postgresql_outline.html]]** -> Überblick über die Eigenschaften
 +    * **[[http://www.postgresql.de/features.html]]** -> Überblick über die Funktionen
 +  * [[http://www.postgresql.sk/docs/books/pghandbuch.html.de|PostgreSQL: Das offizielle Handbuch]] -> [[http://www.amazon.de/PostgreSQL-offizielle-Handbuch-Peter-Eisentraut/dp/3826613376|ISBN 3-8266-1337-6]]
 +    * {{::pg-handbuch.pdf|Das offizielle Handbuch als PDF}}
 +  * [[http://www.postgresql.de/info.whtml#doc]]
 +    * **[[http://www.postgresql.org/docs/]]**
 +
 +
 +
 +===== Anleitungen bzw. Beispiele =====
 +
 +  * **[[::Datenbank:PostgreSQL Dump]]**
 +  * **[[::Datenbank:PostgreSQL Upgrade]]**
 +  * **[[::Datenbank:HA-PostgreSQL-Cluster]]**
 +  * [[::Datenbank:PostgreSQL 12]]
 +  * [[::Datenbank:PostgreSQL 9.5.2]]
 +  * [[::Datenbank:PostgreSQL 8.1]]
 +  * [[::Datenbank:PostgreSQL 8.0]]
 +  * [[::Datenbank:PostgreSQL 7.1.3]]
 +
 +passwortloser login mit ''[[https://wiki.postgresql.org/wiki/Pgpass|~/.pgpass]]'':
 +  > touch ~/.pgpass
 +  > chmod 0600 ~/.pgpass
 +  > vi ~/.pgpass
 +  hostname:port:database:username:password
 +  
 +  > vi ~/.bashrc
 +  PGPASSFILE="$HOME/.pgpass"
 +  
 +  > echo "\dt;" | psql database
 +
 +individuelle Konfiguration mit ''~/.psqlrc'':
 +  > echo "\dt;" | psql postgresql://username:password@address:5432/database
 +
 +  > echo '\timing on' >> ~/.psqlrc
 +  > echo '\c postgresql://username@address:5432/database' >> ~/.psqlrc
 +
 +PG-User ändert sein eigenes Passwort:
 +  > psql -U dbuser -d dbname
 +  dbname=> \password
 +  Enter new password for user "dbuser":
 +  Enter it again:
 +  dbname=> quit
 +
 +''SHOW USERS'':
 +  > echo "\du" | psql -U postgres -d postgres
 +
 +''SHOW DATABASES'':
 +  > psql -U postgres -l
 +  > echo -e "\l" | psql -U username -d postgres
 +
 +''SHOW TABLES'':
 +  > echo "\dt" | psql -U username -d database
 +  > echo "SELECT * FROM pg_catalog.pg_tables;" | psql -U username -d database
 +
 +''SELECT FROM TABLE'':
 +  > echo "echo "SELECT COUNT(*) FROM tabellenname;" | psql -U username -d database
 +
 +''CREATE DATABASE'':
 +  > echo "CREATE DATABASE database OWNER = username;" | psql -U postgres
 +
 +''CREATE TABLE'':
 +  > echo "CREATE TABLE tabellenname (...);" | psql -U username -d database
 +
 +''CREATE TABLE'' mit "Auto Increment" //("Auto Increment" heißt in PostgreSQL ''SERIAL'' oder ''BIGSERIAL'')//:
 +  > echo "CREATE TABLE film (ID BIGSERIAL PRIMARY KEY, verzeichnis varchar(1000), datei varchar(100), name varchar(100), type varchar(100), jahr integer, laufzeit integer, altersfreigabe integer, genre varchar(100), produzent varchar(100), regie varchar(100), schauspieler varchar(10000), handlung varchar(100000), UNIQUE (pfad, dateiname));" | psql -U username -d database
 +
 +Tabellenstruktur anzeigen:
 +  > echo "\d+ tabellenname" | psql -U username -d database
 +
 +soll die ''~/.psqlrc'' nicht berücksichtigt werden, dann braucht man ''-X''
 +  > echo "SELECT COUNT(*) FROM tabellenname;" | psql -X -U username -d database
 +
 +''SHOW CREATE TABLE'':
 +  > pg_dump --schema-only -U username -d database -t tabellenname
 +
 +''ADD PRIMARY KEY'':
 +  > ALTER TABLE tabellenname ADD PRIMARY KEY(spalte1, spalte2);
 +
 +''DROP TABLE'':
 +  > echo "DROP TABLE tabellenname;" | psql -U username -d database
 +
 +''SHOW FULL PROCESSLIST'':
 +  > echo "SELECT * FROM pg_stat_activity;" | psql -U postgres
 +  > echo "SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_activity WHERE state <> 'idle' AND query NOT LIKE '% FROM pg_stat_activity %' ORDER BY age;" | psql -U username -h 10.1.1.10
 +
 +
 +==== Netzwerkzugriffe erlauben ====
 +
 +<code bash Zugriffe aus allen Netzen erlauben>
 +> echo "listen_addresses = '*'" >> /etc/postgresql/16/main/postgresql.conf
 +> echo "host    all             all             0.0.0.0/0               scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
 +> echo "host    all             all             ::/0                    scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
 +</code>
 +
 +<code bash neue CFG laden>
 +> service postgresql reload
 +</code>
 +
 +<code bash offene DB-Ports zeigen>
 +> ss -antp | grep -F LISTEN | grep -F postgres
 +LISTEN    0      200          0.0.0.0:5432          0.0.0.0:*     users:(("postgres",pid=40606,fd=6))                   
 +LISTEN    0      200             [::]:5432             [::]:*     users:(("postgres",pid=40606,fd=7))
 +</code>
 +
 +
 +==== Benutzer anlegen ====
 +
 +<code bash DB-Benutzer anzeigen>
 +> echo '\du' | sudo -u postgres psql
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 +</code>
 +
 +<code bash Datenbanken anzeigen>
 +> echo '\l' | sudo -u postgres psql
 +                                                   List of databases
 +   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
 +-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 + postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 + template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
 +           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 + template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
 +           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 +(3 rows)
 +</code>
 +
 +
 +=== Benutzer mit allen Rechten, jedoch ohne Benutzer anlegen oder verändern zu können ===
 +
 +<code sql>
 +# Benutzer anlegen
 +CREATE ROLE dbadmin
 +  LOGIN
 +  CREATEDB
 +  PASSWORD 'Passwort01';
 +
 +# nicht! zuviele Rechte
 +#ALTER ROLE dbadmin CREATEROLE;
 +#ALTER ROLE dbadmin SUPERUSER;
 +
 +# Rechte für eine Datenbank
 +GRANT CONNECT ON DATABASE deine_datenbank TO dbadmin;
 +GRANT USAGE, CREATE ON SCHEMA public TO dbadmin;
 +
 +# Rechte auf eine bestimmte Tabelle erteilen
 +GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE deine_tabelle TO dbadmin;
 +
 +# Rechte auf alle vorhandenen Tabellen in "public" erteilen
 +GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO dbadmin;
 +
 +# Rechte auf alle neuen Tabellen in "public" erteilen
 +ALTER DEFAULT PRIVILEGES IN SCHEMA public
 +GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
 +ON TABLES
 +TO dbadmin;
 +
 +# (Optional) Rechte auf Sequences (IDs!)
 +GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dbadmin;
 +ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO dbadmin;
 +</code>
 +
 +
 +=== Benutzer mit reduzierten Rechten, kann nicht einmal Datenbanken anlegen ===
 +
 +<code sql>
 +# Benutzer anlegen
 +CREATE ROLE dbuser
 +  LOGIN
 +  PASSWORD 'Passwort01';
 +
 +# Rechte für eine Datenbank
 +GRANT CONNECT ON DATABASE deine_datenbank TO dbuser;
 +GRANT USAGE, CREATE ON SCHEMA public TO dbuser;
 +
 +# Rechte auf eine bestimmte Tabelle erteilen
 +GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE deine_tabelle TO dbuser;
 +
 +# Rechte auf alle vorhandenen Tabellen in "public" erteilen
 +GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO dbuser;
 +
 +# Rechte auf alle neuen Tabellen in "public" erteilen
 +ALTER DEFAULT PRIVILEGES IN SCHEMA public
 +GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
 +ON TABLES
 +TO dbuser;
 +</code>
 +
 +
 +=== Test-DB + Test-Tabelle anlegen ===
 +
 +<code bash>
 +> echo "CREATE DATABASE testdb;" | psql -h localhost -d postgres -U dbadmin -W
 +Password: 
 +CREATE DATABASE
 +
 +> echo "GRANT CONNECT ON DATABASE testdb TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
 +> echo "GRANT USAGE, CREATE ON SCHEMA public TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
 +> echo "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
 +> echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
 +> echo "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
 +> echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
 +
 +> echo -e '\du\n\l\n' | sudo -u postgres psql
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + dbadmin   | Create DB
 + dbuser    | 
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 +
 +                                                   List of databases
 +   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
 +-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 + postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 + template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
 +           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 + template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
 +           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 + testdb    | dbadmin  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/dbadmin          +
 +           |          |          |                 |         |         |            |           | dbadmin=CTc/dbadmin  +
 +           |          |          |                 |         |         |            |           | dbuser=c/dbadmin
 +(4 rows)
 +
 +
 +> echo 'CREATE TABLE testtab (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10));' | psql -h localhost -d testdb -U dbuser -W
 +Password: 
 +CREATE TABLE
 +
 +> echo '\dt' | psql -h localhost -d testdb -U dbuser -W
 +Password: 
 +         List of relations
 + Schema |  Name   | Type  | Owner  
 +--------+---------+-------+--------
 + public | testtab | table | dbuser
 +(1 row)
 +
 +> echo "INSERT INTO testtab (code) VALUES ('ABC');" | psql -h localhost -d testdb -U dbuser -W
 +Password: 
 +INSERT 0 1
 +
 +> echo "SELECT * FROM testtab;" | psql -h localhost -d testdb -U dbuser -W
 +Password: 
 + id | code 
 +----+------
 +  1 | ABC
 +(1 row)
 +</code>
 +
 +
 +==== TEMP TABLE ====
 +
 +  DROP TABLE IF EXISTS TmpTabNamen;
 +  BEGIN TRANSACTION;
 +    CREATE TEMP TABLE TmpTabNamen AS SELECT ... FROM TabNamen WHERE ...;
 +  COMMIT;
 +  
 +  SELECT * FROM TmpTabNamen WHERE ...;
 +  DROP TABLE IF EXISTS TmpTabNamen;
 +
 +
 +===== PostgreSQL 12 auf Debian 11 installieren =====
 +
 +Repository einrichten
 +  > apt update && apt -y full-upgrade
 +  > wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc > /etc/apt/keyrings/pgdg.asc
 +  > apt -y install gnupg2
 +  > echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list
 +  > apt update
 +
 +den Postgresql-Client installieren
 +  > apt install postgresql-client-12
 +
 +den Postgresql-Server (incl. Client) installieren
 +  > apt install postgresql-12
 +
 +
 +===== PostgreSQL-Wartung =====
 +
 +Werkzeug für die PostgreSQL-Leistungsüberwachung, ähnlich top:
 +  [root@freebsd12 ~]# cd /usr/ports/www/p5-LWP-UserAgent-WithCache && make clean && make && make install ; make clean
 +  [root@freebsd12 ~]# cd /usr/ports/databases/pgtop && make clean && make && make install ; make clean
 +  
 +  [root@freebsd12 ~]# touch ~/.pgtop
 +  [root@freebsd12 ~]# chmod 0600 ~/.pgtop
 +  [root@freebsd12 ~]# vi ~/.pgtop
 +  user=postgres
 +  pass=
 +  host=localhost
 +  port=5432
 +  db=test
 +  
 +  [root@erde ~]# su - postgres -c "echo 'CREATE DATABASE test OWNER = postgres;' | psql postgres"
 +  CREATE DATABASE
 +  
 +  [root@erde ~]# su - postgres -c "echo '\l' | psql postgres"
 +                                    List of databases
 +        Name      |  Owner   | Encoding | Collate |    Ctype    |   Access privileges   
 +  ----------------+----------+----------+---------+-------------+-----------------------
 +   ...
 +   test           | postgres | UTF8     | C       | de_DE.UTF-8 | 
 +   ...
 +  
 +  [root@freebsd12 ~]# pgtop
 +
 +Manueller Aufruf für die DB-Wartung, gelöschte Bereiche wieder frei zugeben.
 +
 +__eine einzelne DB:__
 +  [root@freebsd12 ~]# su - postgres -c "echo 'vacuum verbose' | psql DatenbankName"
 +  ...
 +  ...
 +  ...
 +  VACUUM
 +
 +__alle DBs:__
 +  [root@freebsd12 ~]# su - postgres -c "vacuumdb -aefz"
 +  SELECT pg_catalog.set_config('search_path', '', false)
 +  SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;
 +  SELECT pg_catalog.set_config('search_path', '', false)
 +  vacuumdb: vacuuming database "postgres"
 +  VACUUM (FULL, ANALYZE);
 +  SELECT pg_catalog.set_config('search_path', '', false)
 +  vacuumdb: vacuuming database "template1"
 +  VACUUM (FULL, ANALYZE);
 +
 +
 +==== PostgreSQL-Datenbank bläht sich auf ====
 +
 +[[https://medium.com/compass-true-north/dealing-with-significant-postgres-database-bloat-what-are-your-options-a6c1814a03a5|Dealing with significant Postgres database bloat — what are your options?]]
 +
 +Das Aktualisieren eines vorhandenen Datensatzes führt zu einem toten Tupel, der vorherigen Version des Datensatzes sowie einem neuen Datensatz. Wenn die Rate der erstellten toten Tupel die Fähigkeit der Datenbank überschreitet, tote Tupel automatisch zu bereinigen, tritt ein Aufblähen auf.
 +Stellen wir uns ein Szenario vor, in dem eine Anwendung Datensätze mit durchschnittlich 1.000 Datensätzen pro Sekunde aktualisiert oder löscht und der __Autovacuum-Daemon__ tote Tupel mit einer durchschnittlichen Rate von 800 pro Sekunde entfernt. Jede Sekunde treten 200 Tupel Blähungen auf.
 +//Beachten Sie, dass dies ein hypothetisches Szenario ist. Es ist nicht möglich, den Autovakuum-Daemon so einzustellen, dass tote Tupel mit 800/s entfernt werden.//
 +
 +__Autovacuum-Daemon__ aktivieren:
 +  > vi /etc/postgresql/10/main/postgresql.conf
 +  
 +  > cp /usr/local/share/postgresql/postgresql.conf.sample /usr/local/pgsql/data/postgresql.conf
 +  > vi /usr/local/pgsql/data/postgresql.conf
 +  ...
 +  track_counts = on
 +  ...
 +  autovacuum = on
 +  ...
 +
 +
 +===== erste Schritte in PostgreSQL =====
 +
 +Übrigens! [[https://chat.openai.com|ChatGPT]] kann einem ruck-zuck die richtigen Befehle nennen und das auch noch wenn man die Frage in umgangssprachlichem Deutsch gestellt hat.
 +
 +ein Backup einer Datenbank anlegen:
 +  pg_dump -d filme -f postgres_backup_-_filme.sql
 +
 +einen Benutzer "indy" anlegen:
 +  createuser -U postgres --no-superuser --no-createdb --no-createrole indy
 +  echo "\du" | psql
 +
 +einem DB-Benutzer Rechte über eine ganze DB erteilen:
 +  echo "GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO indy;" | psql -d filme
 +
 +einem DB-Benutzer Rechte über eine Tabelle erteilen:
 +  echo "GRANT INSERT, UPDATE, DELETE ON TABLE tabellenname TO indy;" | psql -d filme
 +
 +einen Benutzer löschen:
 +  echo "DROP USER indy;" | psql
 +
 +ein Datenbank-Backup in ein leeres DBMS einspielen:
 +  echo "CREATE DATABASE filme;" | psql
 +  psql -d filme -f postgres_backup_-_filme.sql
 +
 +alle Datenbanken anzeigen:
 +  echo "\l" | psql
 +
 +alle Tabellen aus einer Datenbank anzeigen:
 +  echo "\dt" | psql database_name
 +
 +Anzeigen der Struktur einer bestimmten Tabelle:
 +  echo "\d table_name" | psql database_name
 +
 +eine Tabelle löschen:
 +  echo "DROP TABLE table_name;" | psql database_name
 +
 +eine DB löschen:
 +  echo "DROP DATABASE database_name;" | psql
 +
 +    Indexe anzeigen: \di
 +    Fremdschlüssel anzeigen: \dfk
 +    VIEWs anzeigen: \dv
 +    Funktionen anzeigen: \df
 +
 +  psql postgres -l
 +  echo "\l" | psql
 +
 +  echo "\dt" | psql -U indy -d filme
 +
 +
 +  * [[http://www.freebsddiary.org/postgresql.php]]
 +  * [[https://www.postgresqltutorial.com/postgresql-update/]]
 +
 +//MySQL:// ''SHOW FULL PROCESSLIST''
 +  SELECT * FROM pg_stat_activity;
 +
 +
 +==== 1. Benutzer in PostgreSQL anlegen ====
 +
 +  * [[http://www.postgresql.org/docs/9.5/static/sql-grant.html|GRANT]]
 +  * [[http://www.postgresql.org/docs/9.5/static/sql-revoke.html|REVOKE]]
 +  * [[http://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html|ALTER DEFAULT PRIVILEGES]]
 +
 +zum PostgreSQL-root-User werden:
 +  > su - postgres
 +
 +User anlegen:
 +  $ createuser fritz
 +
 +wieder ausloggen und wieder "ich" werden:
 +  $ exit
 +
 +User anzeigen:
 +  > echo "\dg" | psql postgres
 +                                        Liste der Rollen
 +   Rollenname |                            Attribute                            | Mitglied von 
 +  ------------+-----------------------------------------------------------------+--------------
 +   fritz      |                                                                 | {}
 +   postgres   | Superuser, Rolle erzeugen, DB erzeugen, Replikation, Bypass RLS | {}
 +
 +
 +==== 2. Benutzer in PostgreSQL anlegen ====
 +
 +zum PostgreSQL-root-User werden:
 +  > su - postgres
 +
 +Syntax anzeigen:
 +  $ echo "\h CREATE USER;" | psql postgres
 +  Anweisung:    CREATE USER
 +  Beschreibung: definiert eine neue Datenbankrolle
 +  Syntax:
 +  CREATE USER Name [ [ WITH ] Option [ ... ] ]
 +  
 +  wobei Option Folgendes sein kann:
 +  
 +        SUPERUSER | NOSUPERUSER
 +      | CREATEDB | NOCREATEDB
 +      | CREATEROLE | NOCREATEROLE
 +      | CREATEUSER | NOCREATEUSER
 +      | INHERIT | NOINHERIT
 +      | LOGIN | NOLOGIN
 +      | REPLICATION | NOREPLICATION
 +      | BYPASSRLS | NOBYPASSRLS
 +      | CONNECTION LIMIT Verbindungslimit
 +      | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'Passwort'
 +      | VALID UNTIL 'Zeit'
 +      | IN ROLE Rollenname [, ...]
 +      | IN GROUP Rollenname [, ...]
 +      | ROLE Rollenname [, ...]
 +      | ADMIN Rollenname [, ...]
 +      | USER Rollenname [, ...]
 +      | SYSID Uid
 +
 +User anlegen:
 +  $ echo "CREATE USER fritz;" | psql postgres
 +  $ echo "CREATE USER fritz WITH CREATEDB PASSWORD 'geheim';" | psql postgres
 +
 +Passwort ändern:
 +  $ echo "ALTER USER fritz WITH PASSWORD 'geheim';" | psql postgres
 +
 +fritz alle Privilegien nehmen:
 +  $ echo "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM fritz;" | psql postgres
 +
 +allen Usern alle Privilegien nehmen:
 +  $ echo "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;" | psql postgres
 +
 +User löschen
 +  $ echo "DROP USER fritz;" | psql postgres
 +
 +User anlegen:
 +  $ echo "CREATE USER fritz WITH CREATEDB;" | psql postgres
 +
 +wieder ausloggen und wieder "ich" werden:
 +  $ exit
 +
 +User anzeigen:
 +  > echo "\dg" | psql postgres
 +                                        Liste der Rollen
 +   Rollenname |                            Attribute                            | Mitglied von 
 +  ------------+-----------------------------------------------------------------+--------------
 +   fritz      | DB erzeugen                                                     | {}
 +   postgres   | Superuser, Rolle erzeugen, DB erzeugen, Replikation, Bypass RLS | {}
 +  
 +  > echo "\du" | psql postgres
 +                                        Liste der Rollen
 +   Rollenname |                            Attribute                            | Mitglied von 
 +  ------------+-----------------------------------------------------------------+--------------
 +   fritz      | DB erzeugen                                                     | {}
 +   postgres   | Superuser, Rolle erzeugen, DB erzeugen, Replikation, Bypass RLS | {}
 +
 +
 +  > echo "ALTER USER fritz RESET tabellen_name;" | psql postgres
 +  > echo "GRANT UPDATE ON tabellen_name TO fritz;" | psql postgres
 +  > echo "REVOKE ALL PRIVILEGES ON tabellen_name FROM fritz;" | psql postgres
 +  > echo "REVOKE ALL PRIVILEGES ON tabellen_name FROM PUBLIC;" | psql postgres
 +  > echo "REVOKE ALL PRIVILEGES ON datenbank FROM fritz;" | psql postgres
 +
 +
 +==== PostgreSQL-Hilfe ====
 +
 +Datenbanken auflisten:
 +  > echo "\l" | psql postgres
 +                                    Liste der Datenbanken
 +     Name    | Eigentümer | Kodierung | Sortierfolge | Zeichentyp  |  Zugriffsprivilegien
 +  -----------+------------+-----------+--------------+-------------+-----------------------
 +   postgres  | postgres   | UTF8      | C            | de_DE.UTF-8 |
 +   template0 | postgres   | UTF8      | C            | de_DE.UTF-8 | =c/postgres          +
 +             |            |           |              |             | postgres=CTc/postgres
 +   template1 | postgres   | UTF8      | C            | de_DE.UTF-8 | =c/postgres          +
 +             |            |           |              |             | postgres=CTc/postgres
 +  (3 Zeilen)
 +
 +Tabellen auflisten:
 +  > echo "\dt" | psql postgres
 +
 +Zugriffsprivilegien für Tabellen, Sichten und Sequenzen auflisten
 +  > echo "\dp" | psql postgres
 +
 +alle Konfigurationsparameter anzeigen:
 +  > echo "SHOW ALL;" | psql postgres
 +
 +Hilfe:
 +  > psql --help
 +  > echo "\h" | psql postgres
 +  > echo "\?" | psql postgres
 +  ...
 +    Hilfe
 +    \? [commands]          Hilfe über Backslash-Befehle anzeigen
 +    \? options             Hilfe über psql-Kommandozeilenoptionen anzeigen
 +    \? variables           Hilfe über besondere Variablen anzeigen
 +    \h [NAME]              Syntaxhilfe über SQL-Anweisung, * für alle Anweisungen
 +  ...
 +
 +Verbindungen anzeigen:
 +  > echo "\conninfo" | psql postgres
 +  Sie sind verbunden mit der Datenbank "postgres" als Benutzer "fritz" via Socket in "/tmp" auf Port "5432".
 +
 +
 +==== Datenbank in PostgreSQL anlegen ====
 +
 +Datenbank anlegen:
 +  > createdb dbname
 +
 +oder so
 +  > echo "CREATE DATABASE dbname OWNER = fritz;" | psql postgres
 +  CREATE DATABASE
 +
 +neue DB anzeigen:
 +  > echo "\l" | psql postgres
 +                                   Liste der Datenbanken
 +     Name    | Eigentümer | Kodierung | Sortierfolge | Zeichentyp  | Zugriffsprivilegien 
 +  -----------+------------+-----------+--------------+-------------+---------------------
 +   dbname    | fritz      | UTF8      | C            | de_DE.UTF-8 | 
 +   postgres  | pgsql      | UTF8      | C            | de_DE.UTF-8 | 
 +   template0 | pgsql      | UTF8      | C            | de_DE.UTF-8 | =c/pgsql           +
 +             |            |           |              |             | pgsql=CTc/pgsql
 +   template1 | pgsql      | UTF8      | C            | de_DE.UTF-8 | =c/pgsql           +
 +             |            |           |              |             | pgsql=CTc/pgsql
 +  (4 Zeilen)
 +
 +Backup erstellen:
 +  > pg_dump dbname > dbname.pgdump
 +
 +Backup zurück spielen:
 +  > cat dbname.pgdump | psql dbname
 +
 +
 +==== Tabelle in PostgreSQL anlegen ====
 +
 +Tabelle anlegen:
 +  > echo "CREATE TABLE film (ID serial, verzeichnis varchar(1000), datei varchar(100), name varchar(100), type varchar(100), jahr integer, laufzeit integer, altersfreigabe integer, genre varchar(100), produzent varchar(100), regie varchar(100), schauspieler varchar(10000), handlung varchar(100000), PRIMARY KEY (ID));" | psql dbname
 +
 +Index (name_idx) in der Tabelle (film) auf eine Spalte (Name) angelegt:
 +  > echo "CREATE INDEX name_idx ON film (name);" | psql dbname
 +  CREATE INDEX
 +
 +Tabelle löschen:
 +  > echo "DROP TABLE film;" | psql dbname
 +  DROP TABLE
 +
 +mal reinschauen:
 +  > echo "SELECT * FROM film;" | psql filme
 +   id | verzeichnis | datei | name | type | jahr | laufzeit | altersfreigabe | genre | produzent | regie | schauspieler | handlung 
 +  ----+-------------+-------+------+------+------+----------+----------------+-------+-----------+-------+--------------+----------
 +  (0 Zeilen)
 +
 +
 +==== Datensätze in der Tabelle ====
 +
 +zwei Datensätze eintragen:
 +  INSERT INTO film (verzeichnis, datei, name, type, jahr, laufzeit, altersfreigabe, genre, produzent, regie, schauspieler, handlung) VALUES ('/Filme/','Otto_der_Film.mp4','Otto der Film','Film',1985,5100,0,'Komödie','Horst Wendlandt','Xaver Schwarzenberger, Otto Waalkes','Otto Waalkes',''), ('/Filme/','Grosse_Pointe_Blank.mp4','Ein Mann - ein Mord','Film',1997,6420,16,'Komödie','Susan Arnold, Roger Birnbaum','George Armitage','John Cusack','');
 +
 +beide Datensätze nacheinander ändern:
 +  UPDATE [Tabellenname] SET [Spaltenname1]='Inhalt1', [Spaltenname2] = 'Inhalt2' WHERE [Spaltenname3] = 'Inhalt3';
 +  
 +  UPDATE film SET genre='Filmkomödie', handlung = 'Otto wächst im beschaulichen Ostfriesland auf.' WHERE datei = 'Otto_der_Film.mp4';
 +  UPDATE film SET genre='Filmkomödie', handlung = 'Martin Blank ist Profikiller. Nicht immer läuft alles nach Plan, weshalb seine Auftraggeber unzufrieden sind.' WHERE datei = 'Grosse_Pointe_Blank.mp4';
 +
 +
 +==== eine Tabelle umbenennen ====
 +
 +  * [[https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-rename-table/|]]
 +
 +  ALTER TABLE [alter Tabellenname] RENAME TO [neuer Tabellenname];
 +
 +
 +==== eine Spalte zur Tabelle hinzufügen ====
 +
 +[[https://oracleplsql.info/alter-table-postgresql.html]]
 +
 +  ALTER TABLE [Tabellenname] ADD [Spaltenname] [Spaltentyp];
 +  ALTER TABLE order_details ADD sort_datum date;
 +
 +//Es gibt keine Möglichkeit, hier eine Anweisung mit zugeben, um die Spalte an einer bestimmten Stelle einzufügen!//
 +
 +
 +==== eine neue Tabelle anlegen, die genau die gleichen Spalten hat wie eine bereits vorhandene ====
 +
 +  * [[https://www.postgresql.org/docs/9.1/sql-createtable.html|CREATE TABLE]]
 +
 +  CREATE TABLE [neuer Tabellenname] (LIKE [alter Tabellenname]);
 +
 +
 +==== Volltextsuche (case-insensitive) ====
 +
 +  > echo "SELECT * FROM Tabelle WHERE Spalte ILIKE ('%suchzeichen%');" | psql -U Benutzer -d Datenbank
 +
 +
 +==== weitere PostgreSQL-Kommandos ====
 +
 +<file>
 +Verbindungen: echo "\conninfo" | psql postgres
 +User anzeigen: echo "\dg" | psql postgres
 +Zugriffsprivilegien: echo "\dp" | psql dbname
 +Standard-Zugriffsprivilegien: echo "\ddp" | psql dbname
 +Datentypen: echo "\dT" | psql dbname
 +
 +alle Datenbanken anzeigen: echo "\l" | psql postgres
 +alle Relationen (Tabellen+Indizes+...): echo "\d" | psql dbname
 +
 +Tabellen: echo "\dt" | psql dbname
 +Tablespaces: echo "\db" | psql dbname
 +
 +Indizes: echo "\ds" | psql dbname
 +Schemas: echo "\dn" | psql dbname
 +
 +Trigger: echo "\dy" | psql dbname
 +Domänen: echo "\dD" | psql dbname
 +Funktionen: echo "\df" | psql dbname
 +Objektbeschreibungen: echo "\dd" | psql dbname
 +Large Objects: echo "\dl" | psql dbname
 +Operatoren: echo "\do" | psql dbname
 +installierten Erweiterungen: echo "\dx" | psql dbname
 +Aggregatfunktionen: echo "\da" | psql dbname
 +Konversionen: echo "\dc" | psql dbname
 +Textsuchekonfigurationen: echo "\dF" | psql dbname
 +Textsuchewörterbücher: echo "\dFd" | psql dbname
 +Textsucheparser: echo "\dFp" | psql dbname
 +Textsuchevorlagen: echo "\dFt" | psql dbname
 +Liste der prozeduralen Sprachen: echo "\dL" | psql dbname
 +Liste der Operatoren: echo "\do" | psql dbname
 +Liste der Sortierfolgen: echo "\dO" | psql dbname
 +
 +Stringausgabe: echo "\echo" | psql dbname
 +Zeichensatzkodierung: echo "\encoding" | psql dbname
 +Zeldtrennzeichen: echo "\f ' '" | psql dbname
 +Ausgabe direkt in eine Datei: echo "\o '/tmp/datenbankausgabe.txt'" | psql dbname
 +Ausgabe in eine Datei: echo "\w '/tmp/datenbankausgabe.txt'" | psql dbname
 +Ausgabe einem Kommando übergeben: echo "\w '| /bin/systembefehl'" | psql dbname
 +</file>
 +
 +
 +===== SQL =====
 +
 +  * [[https://www.postgresql.org/docs/8.3/queries-union.html]]
 +
 +''UNION'' und ''UNION DISTINCT'' entfernen alle doppelten Zeilen, ''UNION ALL'' gibt dagegen auch die doppelten Zeilen aus, um schneller zu sein.
 +  (SELECT * FROM tabelle1) UNION (SELECT * FROM tabelle2)
 +
 +''INTERSECT'' zeigt alle Zeilen, die in beiden Ausgaben vorhanden sind, doppelten Zeilen werden entfernt, ''INTERSECT ALL'' gibt dagegen auch die doppelten Zeilen aus, um schneller zu sein.
 +  (SELECT * FROM tabelle1) INTERSECT (SELECT * FROM tabelle2)
 +
 +''EXCEPT'' gibt alle Zeilen zurück, die im Ergebnis von ''SELECT * FROM tabelle**1**'', aber nicht im Ergebnis von ''SELECT * FROM tabelle**2**'' enthalten sind. (Dies wird manchmal als Unterschied zwischen zwei Abfragen bezeichnet.) Auch hier werden Duplikate entfernt, sofern nicht ''EXCEPT ALL'' verwendet wird.
 +  (SELECT * FROM tabelle1) EXCEPT (SELECT * FROM tabelle2)
 +
 +Damit die Gegenüberstellung der beiden Ausgaben funktioniert, müssen beide Ausgaben die gleiche Anzahl an Zeilen haben und vom gleichen Datentyp sein.
 +
 +
 +==== Mehrfacheinträge in einer Tabelle finden ====
 +
 +<file SQL mehrfache.sql>
 +DROP TABLE mehrfach_vorhanden;
 +CREATE TABLE mehrfach_vorhanden ( anzahl INT, zeile VARCHAR(10000) );
 +
 +DO $$
 +DECLARE
 +        eine_zeile VARCHAR(10000);
 +BEGIN
 +        FOR eine_zeile IN
 +                SELECT name
 +                FROM liste
 +                GROUP BY 1
 +        LOOP
 +                INSERT INTO mehrfach_vorhanden SELECT COUNT(name), eine_zeile FROM kino WHERE name = eine_zeile;
 +        END LOOP;
 +END;
 +$$ LANGUAGE PLPGSQL;
 +
 +DELETE FROM mehrfach_vorhanden WHERE ANZAHL = 1;
 +SELECT anzahl, zeile FROM mehrfach_vorhanden ORDER BY 1 ASC;
 +</file>
 +
 +Am Ende werden in der zweiten Spalte alle mehrfach vorhandenen Einträge (Namen) und in der ersten Spalte die Anzahl dieser Einträge ausgegeben:
 +  cat mehrfache.sql | psql -h 192.168.0.100 -p 5432 -U dbnutzer datenbank
 +
 +
 +==== Summierung von Spesenabrechnungen ====
 +
 +So können beispielsweise die Gesamtausgaben (Spesen) jeder (mehrfach) eingetragenen Person leicht berechnet werden:
 +  echo "SELECT SUM(ausgaben), name FROM spesen GROUP BY 2 ORDER BY 1 ASC;" | psql -h 192.168.0.100 -p 5432 -U buchhaltung db2019
 +
 +
 +===== PostgreSQL-Replikation (Cluster) =====
 +
 +[[https://www.linux-magazin.de/ausgaben/2017/04/postgresql/|PostgreSQL-Replikation: Ein Statusbericht]] - 04/2017
 +
 +  * [[https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling|Replication, Clustering, and Connection Pooling]]
 +    * **PgPool:** [[https://wiki.postgresql.org/wiki/Pgpool-II]]
 +    * **Citus:** [[https://www.citusdata.com/]]
 +      * [[https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/]]
 +  * [[https://hevodata.com/learn/postgresql-cluster/|PostgreSQL Cluster Guide 101: Easy Set-Up and How to Deploy for High Availability]] - //Sharon Rithika • February 25th, 2022//
 +
 +
 +==== PgPool ====
 +
 +  * [[https://www.pgpool.net/mediawiki/index.php/Main_Page]]
 +    * [[https://www.pgpool.net/docs/pgpool-II-3.1/pgpool-de.html]]
 +    * [[https://www.pgpool.net/docs/pgpool-II-4.2.12/en/html/]]
 +  * [[https://severalnines.com/blog/guide-pgpool-postgresql-part-one]]
 +
 +  FreeBSD:
 +  > pkg install databases/pgpool-II-42
 +
 +  Debian:
 +  > apt install pgpool2
 +