Datenbanken verwalten

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

Pakete installieren

Das Paket postgresql installiert die aktuelle Version und erzeugt eine Instanz mit dem Namen 11-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@11-main
pg_ctlcluster 11 main status
pg_ctl: Server läuft (PID: 14022)
/usr/lib/postgresql/11/bin/postgres "-D" "/var/lib/postgresql/11/main" \
	"-c" "config_file=/etc/postgresql/11/main/postgresql.conf"
pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-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/11/main/start.conf
auto

In der Hauptkonfiguration werden die Pfade für Konfigurationsdateien, Protokolle sowie Nutzdaten und konfiguriert Netzwerkeinstellungen, Speicherverwaltung sowie Land und Sprache festgelegt.

/etc/postgresql/11/main/postgresql.conf
data_directory		= '/var/lib/postgresql/11/main
hba_file		= '/etc/postgresql/11/main/pg_hba.conf'
ident_file		= '/etc/postgresql/11/main/pg_ident.conf'
port			= 5432
max_connections		= 100
unix_socket_directories	= '/var/run/postgresql'
ssl			= on
ssl_cert_file		= '/etc/ssl/durmstrang.crt'
ssl_key_file		= '/etc/ssl/durmstrang.key'
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		= '11/main'
stats_temp_directory	= '/var/run/postgresql/11-main.pg_stat_tmp'
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'
include_dir		= 'conf.d'
external_pid_file	= '/var/run/postgresql/11-main.pid'

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/11/main/pg_hba.conf
#       Database        User       Address            Method
local   all             postgres                      peer map=admin
local   all             all                           peer
local   replication     all                           peer

hostssl all             all        ::1/128            md5
hostssl all             all        127.0.0.1/32       md5
hostssl replication     replicant  81.92.164.64/26    md5

Die Identitätstabelle bildet Systembenutzer auf PostgreSQL-Rollen ab. Wenn man als root angemeldet ist, kann man mit folgender Konfiguration direkt als Superuser agieren.

/etc/postgresql/11/main/pg_ident.conf
# Mapname   System      Postgres
admin       root        postgres
admin       postgres    postgres

Instanz replizieren

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

su postgres -c "psql --user postgres postgres"

Und dann

CREATE ROLE replicant REPLICATION LOGIN PASSWORD 'Password';

In der Hauptkonfiguration aktivieren wir die Archiverung des WAL.

/etc/postgresql/11/main/conf.d/master.conf
listen            = '*'
archive_mode      = on
archive_command   = 'cp %p /var/lib/postgresql/11/main/archive/%f'
wal_level         = replica
wal_keep_segments = 10
max_wal_senders   = 5

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/11/main/archive
chown postgres.postgres /var/lib/postgresql/11/main/archive
systemctl restart postgresql

Auf dem Slave stoppen wir den Daemon und ziehen eine vollständige Kopie des Masters inklusive dem Datenbanksystemidentifikator.

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

In der automatisch erzeugten Standby-Konfiguration ergänzen wir ein Kommando zum nachfahren gepufferter WALs.

/var/lib/postgresql/11/main/recovery.conf
standby_mode      = on
primary_conninfo  = 'user=replicant password=Password
                    host=durmstrang port=5432
                    sslmode=prefer sslcompression=0
                    krbsrvname=postgres target_session_attrs=any
                    application_name=replica1'
trigger_file      = 'failover.now'
restore_command   = 'cp %p /var/lib/postgresql/11/main/archive/%f'

In der Hauptkonfiguration aktivieren wir den Bereitschaftsmodus, der dazu führt, dass PostgreSQL die Standby-Konfiguration berücksichtigt.

/etc/postgresql/11/main/conf.d/slave.conf
listen            = '*'*
hot_standby       = on

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

mkdir -p -m 0700           /var/lib/postgresql/11/main/archive
chown -R postgres.postgres /var/lib/postgresql/11/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

apt install --no-install-recommends pgadmin3

Kommandos in psql [--host Host] --user User [Password]

Benutzer anlegen

CREATE ROLE User PASSWORD '123' ENCRYPTED NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE DbName OWNER User;

Konfiguration auslesen

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

Liste aktueller Verbindungen

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.

Glossar

Literatur

1. PostgreSQL 11.7 Documentation