====== postgres Basics ======

[[https://computingforgeeks.com/how-to-install-postgresql-13-on-ubuntu/|https://computingforgeeks.com/how-to-install-postgresql-13-on-ubuntu/]]

===== Shell Zugang =====

psql Cheat Sheet: [[https://tomcam.github.io/postgres/|https://tomcam.github.io/postgres/]]
<code>

thommie@db2b:~$ sudo su postgres

</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>
create database sqlmail;

create user sqlmail with encrypted password 'xxxxxxxxxxxx';

grant all privileges on database sqlmail to sqlmail;

</code>

===== Datenbank duplizieren =====

WER greift gerade auf die DB zu?

<code>
SELECT usename, datname, state FROM pg_stat_activity WHERE usename='discourse2';

</code>

Alle aktiven Verbindungen zu dieser DB trennen

<code>
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'discourse2' AND leader_pid IS NULL;

</code>

Danach kann man die Datenbank duplizieren, indem man die Quelldatenbank als Template benutzt

<code>
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

</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>

DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
    EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
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>

===== Historie der Kommandos in psql =====

\s command history

beenden

\q DB shell beenden

====== Dump einer Datenbank als sql file ======

  - Login auf postgres Server
  - sudo nach postgres
  - Wechsel ins Home Dir von postgres (wg. Schreibrechten für sql File)

<code>
root@db2b:/home/thommie# sudo su postgres
postgres@db2b:/home/thommie$ cd ~
postgres@db2b:~$ pwd
/var/lib/postgresql

</code>

mit psql \l Namen aller DBs auflisten

<code>
postgres@db2b:~$ psql
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1))
Type "help" for help.
postgres=# \l

</code>

Danach die Datenbanken dumpen:

<code>
pg_dump pixelfed>> pixelfed_tokoeka.sql

</code>

Datenbank löschen

<code>
$ dropdb your_database​

</code>

====== Verbindungstest zum postgresql Server ======

Test einer postgres Verbindung: pg_isready ist Teil von postgresql-client

<code>
pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user>

</code>

Praktisch:

<code>
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>

====== Import der Datenbanken ======

<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=# DROP DATABASE template1;
postgres=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
postgres=# \c template1;
You are now connected to database "template1" as user "postgres".
template1=# VACUUM FREEZE;

</code>

Neu angelegte DBs auf Basis von template1 haben danach utf8 encoding