Datenbanken verwalten

PostgreSQL ist ein RDBMS. Die Infrastruktur von Debian erleichtert es, auf einem Host verschiedene Versionen (im Beispiel 13 und 15) und mehrere Instanzen (Vorgabe ist main) parallel zu verwalten.

Glossar

Pakete installieren

Das Paket postgresql installiert die aktuelle Version und erzeugt eine Instanz mit dem Namen 15-main.

apt install --no-install-recommends postgresql

Alle vorhandenen Instanzen lassen sich entweder über Systemwerkzeuge oder die mitgelieferten Programme starten, stoppen und überwachen.

systemctl status postgresql
systemctl status postgresql@15-main
pg_ctlcluster 15 main status
pg_ctl: Server läuft (PID: 14022)
/usr/lib/postgresql/15/bin/postgres "-D" "/var/lib/postgresql/15/main" \
	"-c" "config_file=/etc/postgresql/15/main/postgresql.conf"
pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Instanz konfigurieren

Die Startkonfiguration legt fest, ob die jeweilige Instanz beim Systemstart automatisch gestartet wird, oder ob der Systemadministrator dies manuell durchführt.

/etc/postgresql/15/main/start.conf
auto

In der Konfigurationsdatei werden die Pfade für Zugriffsrechte, Protokolle, Nutzdaten, Zertifikate sowie Netzwerkeinstellungen, Speicherverwaltung Land und Sprache festgelegt.

/etc/postgresql/15/main/postgresql.conf
data_directory		= '/var/lib/postgresql/15/main
hba_file		= '/etc/postgresql/15/main/pg_hba.conf'
ident_file		= '/etc/postgresql/15/main/pg_ident.conf'
external_pid_file	= '/var/run/postgresql/15-main.pid'
listen_addresses	= '*'
port			= 5432
max_connections		= 100
unix_socket_directories	= '/var/run/postgresql'
password_encryption	= 'scram-sha-256'
ssl			= on
ssl_cert_file		= '/etc/ssl/durmstrang.crt'
ssl_key_file		= '/etc/ssl/durmstrang.key'
ssl_ca_file		= '/etc/ssl/ca.crt'
ssl_crl_file		= '/etc/ssl/ca.crl'
shared_buffers		= 128MB
dynamic_shared_memory_type = posix
max_wal_size		= 1GB
min_wal_size		= 80MB
log_line_prefix		= '%m [%p] %q%u@%d '
log_timezone		= 'Europe/Berlin'
cluster_name		= '15/main'
track_counts		= on
autovacuum		= on
datestyle		= 'iso, dmy'
lc_messages		= 'de_DE.UTF-8'
lc_monetary		= 'de_DE.UTF-8'
lc_numeric		= 'de_DE.UTF-8'
lc_time			= 'de_DE.UTF-8'
default_text_search_config = 'pg_catalog.german'

Zugriff gewähren

Jede Instanz hat einen Administrator mit uneingeschränkten Rechten, der normalerweise postgres heißt. Die Hostbasierte Authentifizierung (HBA) verknüpft Datenbanken, Benutzer und Netzwerke mit einer Authentifizierungsmethode.

/etc/postgresql/15/main/pg_hba.conf
#       Database        User       Address                 Method
local   all             postgres                           peer
local   all             all                                peer
local   replication     all                                peer

hostssl all             all        ::1/128                 scram-sha-256
hostssl all             all        127.0.0.1/32            scram-sha-256
hostssl all             all        2001:1b18:a1::70/128    scram-sha-256
hostssl replication     replicant  2001:1b18:a1::70/128    scram-sha-256

Instanz replizieren

Auf dem Primärserver legen wir einen speziell für die Replikation zuständigen Benutzer an.

su postgres -c 'createuser replicant --replication --pwprompt'

In der Konfiguration aktivieren wir die Archiverung des WAL.

/etc/postgresql/15/main/postgresql.conf
listen_addresses  = '*'
archive_mode      = on
archive_command   = 'cp %p /var/lib/postgresql/15/main/archive/%f'
wal_level         = replica
wal_keep_segments = 10
max_wal_senders   = 5
hot_standby       = off

Schließlich legen wir das Puffer-Verzeichnis für das WAL an und starten PostgreSQL mit der neuen Konfiguration.

mkdir -p -m 0700        /var/lib/postgresql/15/main/archive
chown postgres.postgres /var/lib/postgresql/15/main/archive
systemctl restart postgresql

Auf dem Bereitschafts-Server stoppen wir den Daemon und ziehen eine vollständige Kopie der Datenbanken vom Primärserver inklusive Datenbanksystemidentifikator.

systemctl stop postgresql
rm -rf /var/lib/postgresql/15/main
pg_basebackup \
	--user replicant \
	--secret \
	--host durmstrang.illusioni.de \
	--port 5432 \
	--checkpoint=fast \
	--wal-method=stream \
	--pgdata /var/lib/postgresql/15/main \
	--write-recovery-conf \
	--progress

Dieses Kommando erzeugt automatisch eine Bereitschafts-Konfiguration:

/var/lib/postgresql/15/main/postgresql.auto.conf
primary_conninfo = 'user=replicant
	password=Password
	channel_binding=prefer
	host=durmstrang.illusioni.de
	port=5432
	sslmode=prefer
	sslcompression=0
	sslsni=1
	ssl_min_protocol_version=TLSv1.2
	gssencmode=prefer
	krbsrvname=postgres
	target_session_attrs=any'

In der Konfigurationsdatei aktivieren wir den Bereitschaftsmodus, so dass keine Schreibenden Zugriffe erlaubt sind.

/etc/postgresql/15/main/postgresql.conf
hot_standby       = on

Schließlich starten wir den Daemon mit der neuen Konfiguration.

mkdir -p -m 0700           /var/lib/postgresql/15/main/archive
chown -R postgres.postgres /var/lib/postgresql/15/main
systemctl start postgresql

Datenbanken sichern

pg_basebackup

Datenverzeichnisse sichern

pg_dumpall

Alle Datenbanken als SQL-Skript sichern

pg_dump

Einzelne Datenbank, Schema oder Tabelle sichern

pg_restore

Datenbanken aus Sicherung wiederherstellen

Datenbanken verwalten

Benutzer anlegen

CREATE ROLE User PASSWORD 'Password';

Datenbank anlegen

CREATE DATABASE DbName OWNER User;

Speicherplatz freigeben

VACUUM ANALYZE;

Konfiguration auslesen

SELECT context, name, unit, setting, boot_val, reset_val FROM pg_settings ORDER BY context, name;

Aktuelle Verbindungen auflisten

SELECT * FROM pg_stat_activity;

Aktive Anfrage beenden

SELECT pg_cancel_backend(Pid);

Verbindung beenden

SELECT pg_terminate_backend(Pid);

Referenz

Diese Auflistung soll nur einen Überblick über die verfügbaren Kommandos und Optionen vermitteln und erhebt keinen Anspruch auf Vollständigkeit. Als authoritative Quelle gelten nur die Manpages und die offizielle Dokumentation.

Literatur

1. PostgreSQL Documentation