• Entwicklung/Architektur
  • Methoden
  • Programmiersprachen
• Betrieb
  • Datenbanken
  • Server
  • IT-Security
  • Speicher
  • Netzwerke
  • Virtualisierung
  • Verfügbarkeit
  • Künstliche Intelligenz
• Management und Recht
  • Projektmanagement
  • Digitalisierung
  • IT-Recht
  • IT-Selbständige
• Aktuelles
• Termine
• IT-Jobs

☰

• Entwicklung
• Betrieb
• Management und Recht
• News
• IT-Jobs
• Newsletter

Dirk Krautschick 11. Oktober 2021

Beyond PostgreSQL – Erweiterungen und Tools für Administration und Allgemeines

Weitere Funktionen und Konfigurationsmöglichkeiten sind auf der Github-Webseite dokumentiert.

Es existiert noch ein weiteres Open-Source-Tool (pgOperate), mit dem zusätzlich die Verwaltung der PostgreSQL-Cluster bzgl. Hochverfügbarkeit und Backup & Recovery vereinfacht werden kann. Dieses Tool wird im vierten Teil dieser Serie vorgestellt.

Extension ForeignDataWrapper

Eine wichtige Gruppe von Extensions sind die Foreign Data Wrappers. PostgreSQL selbst hat keine Konstrukte, wie z. B. die Datenbank-Links von Oracle. Externe Quellen werden mit solchen Foreign Data Wrappern erreichbar gemacht. Im einfachen Fall existiert zum Beispiel mit dem postgres_fdw ein Wrapper für die Verbindung zu anderen PostgreSQL-Datenbanken. Es existieren auch Data Wrapper für die Anbindung von dateibasierten Quellen.

Grundsätzlich wird davor gewarnt, dass viele Foreign Data Wrapper nicht offiziell unterstützt werden oder teilweise gar noch unausgereift sind. Daher sollten diese intensiv getestet werden, bevor man produktive Szenarien auf solche Extensions aufbauen möchte.

Dem Trend von der Migration von Oracle-Datenbanken nach PostgreSQL geschuldet soll der oracle_fdw als repräsentatives Beispiel dienen [2]. Es ermöglicht den lesenden und schreibenden Zugriff auf Oracle-Datenbanken aus einer PostgreSQL-Datenbank heraus. Die Extension muss zusätzlich installiert werden und mindestens ein installierter Oracle instant_client wird vorausgesetzt. Aus diesem Oracle Client werden entsprechende Libraries für den oracle_fdw verlinkt (s. Listing 2).

Listing 2: Installation von oracle_fdw am Beispiel CentOS Linux

root# yum install centos-release-scl
root# yum install clang  llvm5.0 llvm-toolset-7-clang-tools-extra llvm-toolset-7-clang-analyzer 
root# ln -s $ORACLE_HOME/lib/libclntsh.so.18.1 /usr/pgsql-13/lib/libclntsh.so.18.1
root# scl enable llvm-toolset-7 bash

root# unzip oracle_fdw.zip
root# cd oracle_fdw_master
root# make
root# make install

Wurde die Extension in der Datenbank aktiviert, kann man eine Oracle-Datenbank als Server unter Verwendung eines Connection Strings deklarieren. Anschließend kann man jeweils den Rollen die Zugriffe freigeben und über einen User Mapping und die Erstellung einer Foreign Table die Gegenseite bei der Oracle-Datenbank lokal abbilden. Wie im Listing beschrieben kann man dann von der PostgreSQL-Datenbank aus ganz regulär über gewohnte SQL-Statements auf die Daten in der Oracle-Datenbank zugreifen und diese auch verändern.

Listing 3: Beispiel-Einrichtung von oracle_fdw

postgres=# create extension oracle_fdw;
…
postgres=# CREATE SERVER oraclexe FOREIGN DATA WRAPPER oracle_fdw OPTIONS(dbserver '//localhost:1521/XEPDB1');
CREATE SERVER
postgres=# GRANT USAGE ON FOREIGN SERVER oraclexe TO postgres;
GRANT
postgres=# CREATE USER MAPPING FOR postgres SERVER oraclexe OPTIONS (user 'hr', password 'hr');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE dept (
postgres(#               deptno        integer         NOT NULL,
postgres(#               name          character varying(30),
postgres(#               manager       integer,
postgres(#               location_id   integer
postgres(#            ) SERVER oraclexe OPTIONS (schema 'hr', table 'departments');
CREATE FOREIGN TABLE
postgres=# select count(*) from dept;
 count
-------
   27
(1 row)

Um das mühsame Nachbauen der Tabellen zu vereinfachen, gibt es bei der oracle_fdw-Extension auch die Option, die komplette Schema-Definition des Oracle Users zu übertragen. So erspart man sich die Angabe der Foreign Table, was je nach Umfang eines Schemas den Aufwand deutlich reduziert.

Extension pgrowlocks

Als Teil des contrib-Pakets wird die Extension pgrowlocks mitgeliefert [3]. Wie in Teil 1 der Serie beschrieben, befinden sich in dem contrib-Paket einige Extensions, die direkt und offiziell von der PostgreSQL-Community gepflegt werden. Die Installation dieses Pakets lohnt sich daher in der Regel, weil damit einige Extensions sofort verfügbar sind.

Pgrowlocks ist dabei eine kleine aber durchaus nützliche Extension, die eine Anzeige von Locking-Situationen auf Tabellen aufzeigt und Information darüber liefert.

Dazu wird nur eine Funktion pgrowlocks(<tabellenname>) verwendet. Als Resultat bekommt man eine Auflistung der Locking-Situation (s. Listing 4).

Listing 4: Beispiel-Ausgabe von pgrowlocks

=# SELECT * FROM pgrowlocks('t1');
 locked_row | locker | multi | xids  |     modes      |  pids
------------+--------+-------+-------+----------------+--------
 (0,1)      |    609 | f     | {609} | {"For Share"}  | {3161}
 (0,2)      |    609 | f     | {609} | {"For Share"}  | {3161}
 (0,3)      |    607 | f     | {607} | {"For Update"} | {3107}
 (0,4)      |    607 | f     | {607} | {"For Update"} | {3107}
(4 rows)

Extension pg_track_settings

Häufig sind es auch subtile Dinge, die durch eine Extension problemlösend behandelt werden. Das Verwalten der Cluster-Konfiguration sowie die jeweils überladenen Settings pro Rolle kann auf Dauer herausfordernd sein, wenn die Kontrolle nicht ausschließlich an einer Stelle stattfindet.

Die Extension pg_track_settings kann über eine Funktion pg_track_settings_snapshot() den aktuellen Stand sämtlicher globaler und rollenbasierter Einstellungen erfassen und diese in einer Tabelle speichern [4]. Diese Funktion kann sowohl manuell jederzeit erneut als auch besser über beispielsweise einen Cron-Job regelmäßig ausgeführt werden. So erhält man eine Historie der Veränderungen in den Einstellungen und kann diese entsprechend dokumentativ nachhalten und abfragen.

Für diese Abfrage gibt es diverse Funktionen, die bestimmte Szenarien abdecken, wie z. B. die Historie eines bestimmten Parameters oder die relevanten Unterschiede zwischen zwei erfassten Snapshots. Im Folgenden Listing befinden sich einige Beispiele solcher Abfragen.

Listing 5: Beispiele zu pg_track_settings

postgres=# SELECT * FROM pg_track_settings_diff(now() - interval '2 minutes', now());

        name         | from_setting | from_exists | to_setting | to_exists
---------------------+--------------|-------------|------------|----------
 checkpoint_segments | 30           | t           | 35         | t

…
postgres=# SELECT * FROM pg_track_settings_log('checkpoint_segments');

              ts               |     name            | setting_exists | setting
-------------------------------+---------------------+----------------+---------
 2015-01-25 01:01:42.581682+01 | checkpoint_segments | t              | 35
 2015-01-25 01:00:37.449846+01 | checkpoint_segments | t              | 30

…

postgres=# SELECT * FROM pg_track_settings('2015-01-25 01:01:00');

            name              | setting
------------------------------+---------
[...]
 checkpoint_completion_target | 0.9
 checkpoint_segments          | 30
 checkpoint_timeout           | 300
[...]

In der Praxis hat sich dies insbesondere bei Umgebungen mit vielen Administratoren oder DevOps-Spezialisten als sehr nützlich erwiesen, da dort auch ohne Beteiligung eines Datenbank-Administrators Einfluss auf die Einstellungen genommen werden kann. Dann hat jeder die Chance, die Änderungen im Nachgang nachzuvollziehen und ggf. Maßnahmen zu ergreifen, wie z. B. Rückänderungen oder weitere Anpassungen in ähnlicher Richtung.

Es besteht sinnvollerweise die Möglichkeit, die gesammelten Daten zu löschen. Dazu gibt es eine Funktion pg_track_settings_reset(), die alle bisherigen gesammelten Konfigurationsänderungen entfernt.

Extension pg_repack

Orientiert an der Extension aus früheren Tagen pg_reorg, die aber seit 2011 nicht mehr weiterentwickelt wird, entstand über einen Fork vom Quellcode die Extension pg_repack [5]. Die grundsätzliche Idee dieser Extension ist die physikalische Reorganisation von Tabellen mit möglichst minimalen Sperren. Wenn bei PostgreSQL-Tabellen auf dem üblichen Weg zum Beispiel mit VACUUM FULL reorganisiert werden, ist die jeweilige Tabelle für weitere Zugriffe gesperrt, was je nach Zeitdauer dieser Aktion zu Problemen im Betrieb führen kann. Dennoch ist eine Reorganisation bei PostgreSQL ein relevantes Thema.

Die Anwendung baut auf einem Kommando pg_repack, welches auf der Kommandozeile und nicht innerhalb des Datenbank-Clusters ausgeführt wird. Im einfachsten Fall löst man mit pg_repack -a bzw. pg_repack –all eine komplette Reorganisation aller Datenbanken aus. Dies kann aber mit weiteren Optionen angepasst, eingeschränkt oder erweitert werden.

Fazit

An den genannten Beispielen ist erkennbar, dass sowohl subtile als auch komplexere Funktionalitäten in Extensions stecken können. In beiden Fällen ist der Mehrwert im administrativen Alltag aber unbestreitbar. Es lohnt sich immer, für bestimmte Problemstellungen nach passenden Extensions zu suchen, da man meistens nicht allein damit ist und die Chance gar nicht so schlecht steht, dass sich jemand diesem Problem mit der Entwicklung einer Extension angenommen hat.

Im folgenden Artikel der Serie werden Extensions und Tools zum Thema Monitoring und Performance-Analyse behandelt.

Demnächst

Teil 3 - Erweiterungen und Tools für Monitoring und Performance-Analyse
Teil 4 - Erweiterungen und Tools für Backup, Recovery und Hochverfügbarkeit
Teil 5 - Erweiterungen und Tools für Security und Auditing

Autor

Das könnte Sie auch interessieren