Einrichten

Siehe /usr/share/doc/postgresql-common/README.Debian.gz Den Port musste ich angeben, weil ich Postgres-10 verwende und standardmäßig nach Postgres-9 auf Port 5432 gesucht wird.

# sudo -u postgres createuser -p 5433 joerg
# sudo -u postgres createdb -p 5433 -O joerg mydb
% psql -p 5433 mydb

Wechsel von einer Version zu einer neuen

Bei Postgres ändert sich immer das Datenformat und man muss die Datenbanken von einer auf die neue Version migrieren:

# old_ver=11 new_ver=13
# systemctl stop postgresql
# cd /tmp
# sudo -u postgres /usr/lib/postgresql/$new_ver/bin/pg_upgrade --link --jobs $(nproc) \
  -b /usr/lib/postgresql/$old_ver/bin -d /etc/postgresql/$old_ver/main \
  -B /usr/lib/postgresql/$new_ver/bin -D /etc/postgresql/$new_ver/main
# systemctl mask postgresql@$old_ver-main
# mv /var/lib/postgresql/$old_ver/main{,+}
# systemctl start postgresql
# sudo -u postgres ./analyze_new_cluster.sh

Meldungen auf Englisch stellen

Bei der Installation wird in die Datei /etc/postgresql/10/main/postgresql.conf für die Sprache lc_messages und andere Werte die aktuelle Sprache eingetragen, die unter Umständen nicht Englisch ist. Grundlegend ist es aber besser, wenn die Fehlermeldungen des Servers in Englisch erfolgen, da man besser im Internet nach diesen suchen kann. Daher sollte man den Wert auf C.UTF-8 setzen.

Aktuelle Abfragen auflisten

Ähnlich wie man mit einem ps in der Kommandozeile sich anzeigen lassen kann, wer gerade im DBMS aktiv ist, kann man dies auch bei Postgresql:

SELECT application_name, datname, usename, state, age(now(), query_start), wait_event_type, wait_event, query FROM pg_stat_activity ORDER BY age;

Abkürzungen für Befehle – Aliase

Eine sehr hilfreiche Funktion der Kommandozeile sind Abkürzungen (Aliase) für Befehle. Damit spart man sich einiges an Tipparbeit und muss sich auch keine langen Befehle mit Optionen merken. Etwas ähnliches kann man für psql mit Variablen erreichen. In der Datei ~/.psqlrc kann man zum Beispiel den folgenden Eintrag erstellen und daraufhin in einer Sitzung mit der Eingabe :ps die Liste der aktuellen Abfragen sehen – eine Art Prozessstatistik wie in der Kommandozeile mit ps.

\set ps 'SELECT datname, usename, state, age(now(), query_start), wait_event_type, wait_event, query FROM pg_stat_activity;'

Slow queries

Ähnlich wie bei MySQL kann man sich auch vom Server die Anfragen protokollieren lassen, die länger als eine bestimmte Zeit gebraucht haben: https://stackoverflow.com/questions/12670745/can-i-log-query-execution-time-in-postgressql-8-4#answer-12670828

Speicherverbrauch von Tabellen auswerten

Von Stackoverflow

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

Backups

Backups erstellen:

#!/bin/sh

psql() {
    command psql --no-align --tuples-only --no-psqlrc "$@"
}

for db in $(psql -c 'SELECT datname FROM pg_database WHERE datistemplate = false')
do
    # --exclude-table-data=public.state_groups_state
    pg_dump --format=directory --dbname=$db --file=db-$db --jobs=$(nproc --ignore=1) --compress=0
done

Backups einspielen

% sudo -u postgres createdb -O joerg new
% sudo -u postgres pg_restore --format=directory --dbname=new --jobs=$(nproc --ignore=1) db-matrix

Weiteres