postgres [netzwissen.de Wiki]

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== postgres Basics ======

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

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

<code>
thommie@db2b:~$ sudo su postgres
</code>

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

===== Alle aktiven Verbindungen trennen =====

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

===== psql =====

Basisbefehle

\l Áuflisten aller DBs



Danach zu einer DB verbinden

<code>
postgres=# \c openproject postgres
You are now connected to database "openproject" as user "postgres".
</code>

\dt listet alle Tabellen in einer Datenbank auf

Schemata: https://www.postgresqltutorial.com/postgresql-administration/postgresql-schema/
Das Standard Schema ist public 

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

===== User Passwort ändern =====

\du listet alle User

<code>
ALTER ROLE [username] WITH PASSWORD 'xxxx';
</code>

Zugriffsrechte auf Datenbank erteilen

<code>
GRANT ALL ON DATABASE [dbname] TO [username];

postgres=# GRANT ALL ON DATABASE keycloak TO keycloak;
GRANT
</code>

Historie der Kommandos in psql 

\s command history

beenden

\q DB shell beenden

====== Duplizieren einer Datenbank ======

Zuerst müssen alles laufenden Connections getrennt werden

<code>
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'Discourse' AND pid <> pg_backend_pid();
</code>

Danach  wird kopiert

<code>
CREATE DATABASE Discoursedev WITH TEMPLATE Discourse OWNER postgres;
</code>

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

  - Login auf Postgress 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>

====== Alte Daten löschen ======

Datenbank

<code>
drop DATABASE onlyoffice;
</code>

User/Rolle

Auflisten der User

<code>
\du
</code>

Löschen eines Users

<code>
drop user IF EXISTS dendrite;
</code>

====== Postgres und UTF8 ======

Standardmässig werden neue Datenbanken mit der Zeichenkondierung SQL_ASCII angelegt. 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/

===== Für template1 das encoding auf UTF8 ändern =====

<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