postgres

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
postgres [04/11/2023 - 13:36] – thommie4postgres [11/05/2025 - 16:46] (aktuell) – thommie_netzwissen.de
Zeile 1: Zeile 1:
-====== postgres ======+====== postgres Basics ======
  
-https://computingforgeeks.com/how-to-install-postgresql-13-on-ubuntu/+[[https://computingforgeeks.com/how-to-install-postgresql-13-on-ubuntu/|https://computingforgeeks.com/how-to-install-postgresql-13-on-ubuntu/]]
  
-====== Shell Zugang ======+===== Shell Zugang =====
  
 +psql Cheat Sheet: [[https://tomcam.github.io/postgres/|https://tomcam.github.io/postgres/]]
 <code> <code>
 +
 thommie@db2b:~$ sudo su postgres thommie@db2b:~$ sudo su postgres
 +
 </code> </code>
 +
 +<file>
 +\l
 +
 +</file>
 +
 +listet alle Datenbanken
 +
 +  * \c verbindet zu einer Datenbank
 +  * \dt zeigt die Tabellen nach einer Verbindung
 +  * \d und \d+ zeigt die Spalten einer Tabelle
 +  * \du Benutzerrollen
 +
 +===== Datenbank und User anlegen =====
  
 <code> <code>
-postgres@db2b:/home/thommie$ psql +create database sqlmail; 
-psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1)) + 
-Type "help" for help. +create user sqlmail with encrypted password 'xxxxxxxxxxxx'; 
-postgres=# + 
 +grant all privileges on database sqlmail to sqlmail; 
 </code> </code>
  
 +===== Datenbank duplizieren =====
  
-\l listet alle DBs+WER greift gerade auf die DB zu?
  
-Zu einer DB verbinden+<code> 
 +SELECT usename, datname, state FROM pg_stat_activity WHERE usename='discourse2'; 
 + 
 +</code> 
 + 
 +Alle aktiven Verbindungen zu dieser DB trennen
  
 <code> <code>
-postgres=# \c openproject postgres +SELECT pg_terminate_backend(pid) 
-You are now connected to database "openproject" as user "postgres".+FROM pg_stat_activity 
 +WHERE datname = 'discourse2' AND leader_pid IS NULL; 
 </code> </code>
  
-\dt listet alle Tabellen+Danach kann man die Datenbank duplizieren, indem man die Quelldatenbank als Template benutzt
  
-Schemata: https://www.postgresqltutorial.com/postgresql-administration/postgresql-schema/ +<code> 
-Das Standard Schema ist public +CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
  
-Alle Tabellen löschen+</code>
  
 +===== Schemata =====
 +
 +[[https://www.postgresqltutorial.com/postgresql-administration/postgresql-schema/|https://www.postgresqltutorial.com/postgresql-administration/postgresql-schema/]] Das Standard Schema ist public
 +
 +===== Arbeiten an der Datenbank =====
 +
 +dt listet alle Tabellen in einer Datenbank auf
 +
 +Nach "\c discourse2": alle Tabellen in einer DB löschen
 <code> <code>
 +
 DO $$ DECLARE DO $$ DECLARE
   r RECORD;   r RECORD;
Zeile 41: Zeile 78:
   END LOOP;   END LOOP;
 END $$; END $$;
 +
 +</code>
 +
 +q beendet die Verbindung
 +
 +===== Benutzeraktionen =====
 +
 +\du listet alle User
 +
 +neuen User anlegen ("ROLE")
 +<code>
 +
 +postgres=# CREATE ROLE discourse2;
 +CREATE ROLE
 +postgres=#
 +
 +</code>
 +
 +Passwort setzen
 +
 +<code>
 +ALTER ROLE [username] WITH PASSWORD 'xxxx';
 +
 +</code>
 +
 +==== GRANT Statement ====
 +
 +nur login
 +
 +<code>
 +ALTER ROLE discourse2 LOGIN;
 +
 +</code>
 +
 +Weitere Privilegien:
 +
 +<code>
 +GRANT privilege_list | ALL
 +ON  table_name
 +TO  role_name;
 +
 +</code>
 +
 +privilege_list kann ''[[https://neon.tech/postgresql/postgresql-tutorial/postgresql-select|SELECT'']]'' , ''[[https://neon.tech/postgresql/postgresql-tutorial/postgresql-insert|INSERT'']]'',''[[https://neon.tech/postgresql/postgresql-tutorial/postgresql-update|UPDATE'']]'', ''[[https://neon.tech/postgresql/postgresql-tutorial/postgresql-delete|DELETE'']]'',''[[https://neon.tech/postgresql/postgresql-tutorial/postgresql-truncate-table|TRUNCATE'']]''etc. sein. Mit ALL geht alles (lesen, schreiben, löschen usw.
 +<code>
 +
 +GRANT ALL ON DATABASE [dbname] TO [username];
 +
 +postgres=# GRANT ALL ON DATABASE keycloak TO keycloak;
 +GRANT
 +
 +</code>
 +
 +Löschen eines Users
 +
 +<code>
 +drop user IF EXISTS dendrite;
 +
 +</code>
 +
 +===== Tabellen-Aktionen =====
 +
 +Alle Tabellen löschen
 +
 +<code>
 +DO $$
 +DECLARE
 +    r RECORD;
 +BEGIN
 +    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
 +   EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
 +    END LOOP;
 +END $$;
 +
 +</code>
 +
 +check
 +<code>
 +
 +SELECT tablename FROM pg_tables WHERE schemaname = 'public';
 +
 +</code>
 +
 +===== Daten-Aktionen =====
 +
 +<code>
 +do $$ declare
 +   r record;
 +begin
 +   for r in (select tablename from pg_tables where schemaname = 'my-schema-name') loop
 +   execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
 +   end loop;
 +end $$;
 +
 +</code>
 +
 +Komplette Datenbank löschen
 +<code>
 +
 +drop DATABASE onlyoffice;
 +
 </code> </code>
  
 +===== Historie der Kommandos in psql =====
  
 \s command history \s command history
 +
 +beenden
  
 \q DB shell beenden \q DB shell beenden
  
-====== Dump der Datenbanken ======+====== Dump einer Datenbank als sql file ======
  
-Login auf Postgress Server, sudo nach postgres, Wechsel ins Home Dir von postgres (wg. Schreibrechten für sqwl File+  - Login auf postgres Server 
 +  - sudo nach postgres 
 +  - Wechsel ins Home Dir von postgres (wg. Schreibrechten für sql File)
  
 <code> <code>
Zeile 57: Zeile 200:
 postgres@db2b:~$ pwd postgres@db2b:~$ pwd
 /var/lib/postgresql /var/lib/postgresql
 +
 </code> </code>
  
Zeile 66: Zeile 210:
 Type "help" for help. Type "help" for help.
 postgres=# \l postgres=# \l
 +
 </code> </code>
  
-Danach Datenbanken dumpen:+Danach die Datenbanken dumpen:
  
 <code> <code>
-pg_dump pixelfed >> pixelfed_tokoeka.sql+pg_dump pixelfed>> pixelfed_tokoeka.sql 
 </code> </code>
  
-====== Postgres und UTF8 ======+Datenbank löschen 
 + 
 +<code> 
 +$ dropdb your_database​ 
 + 
 +</code>
  
-Standardmässig werden neue Datenbanken mit Zeichenkondierung SQL_ASCII angelegt. Das passt meistens, aber nicht immer. Mit dieser methode werden DBs mit UTF8 angelegt:  +====== Verbindungstest zum postgresql Server ======
-https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/+
  
-===== Datenbank sichern =====+Test einer postgres Verbindung: pg_isready ist Teil von postgresql-client
  
 <code> <code>
-$ pg_dump your_database > dump.sql​+pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user> 
 </code> </code>
  
-Datenbank löschen+Praktisch:
  
 <code> <code>
-$ dropdb your_database​+root@docker2:/var/discourse# pg_isready -d Discoursedev -h 10.10.10.18 -p 5432 -U Discourse 
 +10.10.10.18:5432 - Verbindungen werden angenommen 
 +root@docker2:/var/discourse# 
 </code> </code>
  
-===== Für template1 das encoding auf UTF8 ändern =====+====== Import der Datenbanken ======
  
 <code> <code>
 +psql -U postgres -W -d keycloak -f keycloak_tokoeka.sql
 +
 +</code>
 +
 +===== Templates und UTF8 encoding =====
 +
 +Standardmässig werden neue Datenbanken mit der Zeichenkondierung SQL_ASCIIangelegt. Das passt meistens, aber nicht immer. Mit dieser Methode werden DBs mit UTF8 Kodierung angelegt:
 +
 +[[https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/|https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/]]
 +<code>
 +
 postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1'; postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
 postgres=# DROP DATABASE template1; postgres=# DROP DATABASE template1;
Zeile 101: Zeile 266:
 You are now connected to database "template1" as user "postgres". You are now connected to database "template1" as user "postgres".
 template1=# VACUUM FREEZE; template1=# VACUUM FREEZE;
-</code> 
  
 +</code>
  
 Neu angelegte DBs auf Basis von template1 haben danach utf8 encoding Neu angelegte DBs auf Basis von template1 haben danach utf8 encoding
  
  
  • postgres.1699105012.txt.gz
  • Zuletzt geändert: 17/08/2024 - 07:06
  • (Externe Bearbeitung)