postgres Basics
https://computingforgeeks.com/install-postgresql-on-debian-linux/
Upgrade
Shell Zugang
psql Cheat Sheet: PostgreSQL Cheat Sheet & Quick Reference
Allgemeiner Zugang
thommie@db2b:~$ sudo su postgres
psql
\l listet alle Datenbanken
\xx Kürzel für einzelne Aktionen
\du zeigt Benutzerrollen
\c verbindet zu einer Datenbank
\dt zeigt die Tabellen nach einer Verbindung
\d und \d+ zeigt die Spalten einer Tabelle
\dx zeigt die Extensions einer Datenbank (nach dem Connect mit \c)
Datenbank und User anlegen
Dabei wird die Standard Kollation benutzt (UTF-8 seit postgresql 15)
CREATE DATABASE sqlmail;
User anlegen
CREATE USER sqlmail WITH encrypted PASSWORD 'xxxxxxxxxxxx';
User Passwort ändern
ALTER USER gitea WITH PASSWORD 'xxxx';
Datenbank mit spezieller kollation anlegen
z.B: für matrix synapse, siehe Warning about incorrect database collation/ctype should have a link on how to fix it · Issue #6831 · matrix-org/synapse · GitHub
CREATE DATABASE synapse WITH LC_CTYPE = 'C' LC_COLLATE='C' TEMPLATE='template0';
Name | Eigentümer | Kodierung | Locale-Provider | Sortierfolge | Zeichentyp | Locale | ICU-Regeln | Zugriffsprivilegien
-----------+------------+-----------+-----------------+--------------+-------------+--------+------------+-----------------------
postgres | postgres | UTF8 | libc | de_DE.UTF-8 | de_DE.UTF-8 | | |
synapse | postgres | UTF8 | libc | C | C | | |
Grant: Privilegien Vergabe
grant all privileges on database sqlmail to sqlmail;
nur login erlaubt
ALTER ROLE discourse2 LOGIN;
Weitere Privilegien:
GRANT privilege_list | ALL
ON table_name
TO role_name;
privilege_list kann SELECT’ oder INSERT oder UPDATE oder DELETE oder TRUNCATE‘ etc. sein. Mit ALL geht alles (lesen, schreiben, löschen usw.).
GRANT ALL ON DATABASE [dbname] TO [username];
postgres=# GRANT ALL ON DATABASE keycloak TO keycloak;
GRANT
Schema
Siehe Schemata PostgreSQL: Documentation: 18: 5.10. Schemas . Der Zugriff auf ein Schema wird separat vergeben. Das Standard Schema ist public.
synapse braucht Zugriff auf das public Schema.
GRANT USAGE ON SCHEMA public TO synapse;
GRANT ALL ON SCHEMA public TO synapse;
Seit postgresql 15 kann e nötig sein, dass man die ownership separat setzt
ALTER DATABASE synapse OWNER TO synapse;
Priviliegien zeigen
SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'YOUR_USER';
Datenbank und User löschen
DROP DATABASE synapse;
mit automatischer Trennung aller User
DROP DATABASE synapse WITH (FORCE);
User löschen
postgres=# DROP USER synapse;
Datenbank duplizieren
WER greift gerade auf die DB zu?
SELECT usename, datname, state FROM pg_stat_activity WHERE usename='discourse2';
Alle aktiven Verbindungen zu dieser DB trennen
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'discourse2' AND leader_pid IS NULL;
Danach kann man die Datenbank duplizieren, indem man die Quelldatenbank als Template benutzt
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
psql: Arbeiten an der Datenbank
dt listet alle Tabellen in einer Datenbank auf
Nach „\c discourse2“: alle Tabellen in einer DB löschen
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 $$;
q beendet die Verbindung
Benutzeraktionen
\du listet alle User
neuen User anlegen („ROLE“)
postgres=# CREATE ROLE discourse2;
CREATE ROLE
postgres=#
Passwort setzen
ALTER ROLE [username] WITH PASSWORD 'xxxx';
Löschen eines Users
drop user IF EXISTS dendrite;
Tabellen-Aktionen
Alle Tabellen löschen
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 $$;
check
SELECT tablename FROM pg_tables WHERE schemaname = ‚public‘;
Daten-Aktionen
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 $$;
Komplette Datenbank löschen
drop DATABASE onlyoffice;
Historie der Kommandos in psql
\s command history
beenden
\q DB shell beenden
Export einer Datenbank als sql file
-
Login auf postgres Server
-
sudo nach postgres
-
Wechsel ins Home Dir von postgres (wg. Schreibrechten für sql File)
root@db2b:/home/thommie# sudo su postgres
postgres@db2b:/home/thommie$ cd ~
postgres@db2b:~$ pwd
/var/lib/postgresql
mit psql \l Namen aller DBs auflisten
postgres@db2b:~$ psql
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1))
Type „help“ for help.
postgres=# \l
Danach die Datenbanken dumpen:
pg_dump pixelfed>> pixelfed_tokoeka.sql
Datenbank löschen
$ dropdb your_database
Export aus Docker Container
Manchmal will man nicht, dass eine Datenbank im Container läuft, sondern lieber auf einem zentralen Datenbank (Cluster). Dann muss man die Datenbank aus dem Container rausholen. Bei postgres geht das so:
docker exec -t [docker-id] pg_dumpall -c -U gitea > dump\_\`date +%Y-%m-%d"\_"%H\_%M\_%S\`.sql
Import einer Datenbank
Hintergrund: 3 Methods to Dump and Restore PostgreSQL Database | Airbyte
bash psql -U -W -d testdb -f my_dump.sql
oder
psql -U postgres -W -d keycloak -f keycloak_tokoeka.sql
Verbindungstest zum postgresql Server
Test einer postgres Verbindung: pg_isready ist Teil von postgresql-client
pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user>
Praktisch:
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#
Achtung: Defaultmässig erlaubt postgres nur Zugriffe von localhost. Zugriffe “von aussen müssen an zwei Stellen erlaubt werden:
- listen_adresses in postgresql.conf
- Erlaubte connections von aussen in pg_hba.conf (IP Adresse oder range)
Templates und Encoding (bis postgresql 14)
Standardmässig werden neue Datenbanken mit der Zeichenkodierung SQL_ASCII angelegt. Das gilt nur bis postgresql 14, mit neueren Version ist UTF-8 das standard encondig. SQL-ASCII passt meistens, aber nicht immer. Mit dieser Methode werden neue DBs auch in älteren postgres Versionen mit der UTF8 Kodierung angelegt:
postgres=# SHOW SERVER_ENCOLDING;
FEHLER: unbekannter Konfigurationsparameter »server_encolding«
postgres=# SHOW SERVER_ENCODING;
server_encoding
-----------------
UTF8
(1 Zeile)
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;
Neu angelegte DBs auf Basis von template1 haben danach utf8 encoding
LLMs mit Postgres
postgres kann mit der pgvector Erweiterung auch als Speicher für Vektoren genutzt werden.