Anwenden von PostgreSQL-Erweiterungen
Die Woodgrove Bank hat veranlasst, dass den verwendeten Datenbanken zusätzliche Funktionen hinzugefügt werden. Azure Cosmos DB for PostgreSQL ermöglicht es Ihnen, die Funktionalität einer Datenbank mithilfe der vielen beliebten PostgreSQL-Erweiterungen zu erweitern.
Anzeigen unterstützter und vorinstallierter Erweiterungen
Die Woodgrove Bank hat Erweiterungen angefordert, mit denen Geodaten in der Datenbank gespeichert werden und geplante Rollupaufträge ausgeführt werden können. Bei der Überprüfung der verfügbaren PostgreSQL-Erweiterungen haben Sie PostGIS und die pg_cron-Erweiterungen als geeignete Kandidaten identifiziert, die diese Funktionen bereitstellen.
Hinweis
PostGIS ist ein Raumdatenbank-Extender für objektrelationale PostgreSQL-Datenbanken, mit dem die Unterstützung für geografische Objekte hinzugefügt wird, sodass Standortabfragen in SQL ausgeführt werden können.
pg_cron ist ein cronbasierter Auftragsplaner, der Ihnen das Planen von PostgreSQL-Befehlen direkt über die Datenbank ermöglicht.
Im nächsten Schritt müssen Sie bestimmen, ob die von Ihnen identifizierten Erweiterungen in Azure Cosmos DB for PostgreSQL unterstützt werden. Es gibt zwei Möglichkeiten, diesen Schritt auszuführen. Bei der ersten Option überprüfen Sie die Liste der von Azure Cosmos DB for PostgreSQL unterstützten Erweiterungen in der Microsoft-Dokumentation. Der zweite und empfohlene Ansatz besteht in der Ausführung der folgenden Abfrage für Ihre Datenbank:
SELECT * FROM pg_available_extensions;
Die pg_available_extensions-Ansicht bietet eine Liste der zur Installation verfügbaren unterstützten Erweiterungen. Die Überprüfung der Ausgabe zeigt, dass beide ausgewählten Erweiterungen unterstützt werden.
Viele beliebte PostgreSQL-Erweiterungen sind auf jeder Azure Cosmos DB for PostgreSQL-Instanz vorinstalliert. Bevor Sie neue unterstützte Erweiterungen installieren, sollten Sie sich die vollständige Liste der vorinstallierten Erweiterungen in Ihrer Datenbank ansehen, um Konflikte zu vermeiden. Sie können diese Liste anzeigen, indem Sie die folgende Abfrage ausführen:
SELECT * FROM pg_extension;
Hinweis
Alternativ können Sie \dx über die Befehlszeile ausführen. Dies ist ein Kurzbefehl, mit dem eine Liste der installierten Erweiterungen in Ihrer Datenbank angezeigt wird.
Für die Erweiterungen, die Sie für die Woodgrove Bank verwenden möchten, zeigt die Abfrageausgabe an, dass pg_cron vorinstalliert ist, die PostGIS-Erweiterung jedoch nicht. Um die erforderlichen Funktionen bereitzustellen, müssen Sie PostGIS in Ihre Datenbank laden.
Installieren von Erweiterungen in Azure Cosmos DB for PostgreSQL
Um PostgreSQL-Erweiterungen verwenden zu können, müssen Sie sie zuerst in Ihrer Datenbank installieren. Verwenden Sie das psql-Tool, um die gepackten Objekte in Ihre Datenbank zu laden.
Um die PostGIS-Erweiterung in der Datenbank der Woodgrove Bank zu installieren, sollten Sie zunächst den Befehl CREATE EXTENSION verwenden. Dieser Befehl installiert die angegebene Erweiterung in der aktuellen Datenbank.
Durch die Ausführung von CREATE EXTENSION wird im Hintergrund die Skriptdatei der Erweiterung ausgeführt. Das Skript erstellt normalerweise neue SQL-Objekte wie Funktionen, Datentypen, Operatoren und Indexunterstützungsmethoden. Zusätzlich zeichnet CREATE EXTENSION die Identitäten aller erstellten Objekte auf, sodass sie wieder gelöscht werden können, wenn DROP EXTENSION ausgegeben wird.
Führen Sie den Befehl CREATE EXTENSION aus, um die PostGIS-Erweiterung in Ihrer Datenbank zu installieren.
CREATE EXTENSION IF NOT EXISTS postgis;
Wenn Sie versuchen, eine Erweiterung zu installieren, die denselben Namen wie eine bereits in der Datenbank geladene Erweiterung aufweist, wird eine Fehlermeldung angezeigt, dass die Erweiterung bereits existiert. Wenn Sie bei der Ausführung des CREATE EXTENSION-Befehls die IF NOT EXISTS-Klausel angeben, können Sie diesen Fehler vermeiden.
Wenn Sie diesen Befehl für die Woodgrove Bank-Datenbankergebnisse ausführen, tritt ein Fehler auf:
ERROR: permission denied to create extension "postgis"
HINT: Must be superuser to create this extension.
Das Laden einer Erweiterung erfordert in der Regel dieselben Berechtigungen, die zum Erstellen der Komponentenobjekte erforderlich sind. Bei vielen Erweiterungen bedeutet diese Anforderung, dass Superuserberechtigungen erforderlich sind. Bei diesen Erweiterungen ist es möglich, dass CREATE EXTENSION mit einem Fehler wegen fehlender Berechtigung fehlschlägt. Da Azure Cosmos DB for PostgreSQL ein verwalteter PaaS-Dienst in Azure ist, kann sich nur Microsoft mit der postgres-Superuser-Rolle anmelden. Wenn die Erweiterung jedoch in ihrer Kontrolldatei als vertrauenswürdig gekennzeichnet ist, kann sie jede*r Benutzer*in mit der entsprechenden CREATE-Berechtigung für die aktuelle Datenbank installieren.
Wenn beim Installieren einer Erweiterung dieser Fehler angezeigt wird, verwenden Sie stattdessen die create_extension()-Funktion. Wenn Sie einen Azure Cosmos DB for PostgreSQL-Cluster erstellen, geben Sie ein Kennwort für die citus-Serveradministratorbenutzer*innen an. Dieses Konto gewährt eingeschränkten administrativen Zugriff auf die Datenbank und den Cluster. Die citus-Benutzer*innen sind Mitglieder von azure_pg_admin, wodurch PostgreSQL-Erweiterungen installiert werden können. Due Funktion create_extension() wird im Kontext der citus-Benutzer*innen ausgeführt.
Verwenden Sie die Funktion create_extension(), um herauszufinden, ob diese Funktion das Erstellen der PostGIS-Erweiterung zulässt.
SELECT create_extension('postgis');
Der Befehl wird erfolgreich ausgeführt, sodass Sie zur Überprüfung Folgendes ausführen können:
\dx
Die Befehlsausgabe zeigt, dass PostGIS erfolgreich installiert wurde. Außerdem wird die Version, das Schema und die Beschreibung der Erweiterung bereitgestellt.
Die Benutzer*innen, die CREATE EXTENSION ausführen, werden als Besitzer*innen der Erweiterung zugewiesen. In der Regel werden diese Benutzer*innen als Besitzer*innen aller Objekte festgelegt, die vom Erweiterungsskript erstellt wurden. Erweiterungsbesitzer*innen sind zum Entfernen und Ändern einer Erweiterung im Kontext dieser Benutzer*innen von entscheidender Bedeutung.
Hinzufügen von Geospalten zur Ereignistabelle der Woodgrove Bank
Wenn die PostGIS-Erweiterung neu geladen wurde, können Sie mit der Arbeit mit Geodaten in der Datenbank beginnen. Die Entwickler*innen der Woodgrove Bank haben die Anwendung für kontaktloses Bezahlen aktualisiert, um die Längen- und Breitengrade aller Transaktionen zu erfassen, die im System vorgenommen werden. Um diese Daten zu speichern, müssen Sie die payment_events-Tabelle so ändern, dass eine geometry-Spalte hinzugefügt wird, die den point-Datentyp akzeptiert. Diese neuen Datentypen sind in der PostGIS-Erweiterung enthalten.
Die payment_events-Tabelle weist die folgende Struktur auf:
/* Table structure provide here for reference.
CREATE TABLE payment_events
(
event_id bigint,
event_type text,
user_id bigint,
merchant_id bigint,
event_details jsonb,
created_at timestamp,
PRIMARY KEY (event_id, user_id)
);
*/
Um point-Daten einfügen zu können, müssen Sie der Tabelle eine neue geometry-Spalte hinzufügen, die point-Daten akzeptiert:
ALTER TABLE payment_events
ADD COLUMN event_location geometry(point, 4326);
Testen Sie anschließend Ihre Änderungen, indem Sie den ersten Ereignisdatensatz mit Geodaten aktualisieren, die vom Entwicklungsteam der Woodgrove Bank bereitgestellt werden.
UPDATE payment_events
SET event_location = ST_GeomFromText('POINT(-71.060316 48.432044)', 4326)
WHERE event_id = (SELECT event_id FROM payment_events ORDER BY event_id LIMIT 1);
Da der Datensatz jetzt aktualisiert ist, können Sie die von PostGIS geladenen Funktionen ST_X(point) und ST_Y(point) zum Anzeigen der eingefügten Geodaten verwenden:
SELECT event_id, event_type, ST_X(event_location) AS longitude, ST_Y(event_location) AS latitude FROM payment_events WHERE event_location IS NOT null;
Die Datenbank der Woodgrove Bank ist bereit, Geodaten von der App für kontaktloses Bezahlen zu akzeptieren. Das Entwicklungsteam hat den einzelnen Händlern Standortdaten zugewiesen und verwaltet die Massenaktualisierung vorhandener Transaktionen.
Einrichten geplanter Aufträge
Die Woodgrove Bank hat Sie gebeten, die geplanten Aufgaben in der Datenbank zu verwenden, um die stündlichen Rollups von Ereignissen pro Benutzer*in für ein Analysedashboard zu berechnen. Mithilfe von pg_cron können Sie einen Auftrag planen, der eine Funktion zur stündlichen Ausführung aufruft.
Hinweis
Der Datumsbereich der Beispieldaten ist begrenzt, weshalb die in die folgende Funktion eingegebenen Zeitangaben festgelegt sind, um darzustellen, wie der Auftrag aufgerufen und geplant werden kann. In einem realen Szenario würden die in die Funktion eingegebenen Start- und Endzeiten des Cronjobs dem aktuellen 60-minütigen Zeitfenster entsprechen.
Um zu beginnen, müssen Sie eine Rolluptabelle zum Speichern der Aggregatdaten erstellen:
-- Define the table
CREATE TABLE rollup_events (
user_id bigint,
event_type text,
hour timestamptz,
event_count bigint
);
-- Create a unique constraint on user_id, event_type, and hour
CREATE UNIQUE INDEX rollup_events_unique_idx ON rollup_events(user_id, event_type, hour);
-- Distribute the table, assigning the user_id as the distribution column
SELECT create_distributed_table('rollup_events', 'user_id');
Verwenden Sie für den Rollup einen INSERT ... SELECT-Befehl, der auf allen Knoten im Cluster parallel ausgeführt wird. Dieser Befehl lädt die aggregierten Daten in die Rollup-Tabelle. Um das Aufrufen der Abfrage zum Ausführen der Datenaggregation zu vereinfachen, können Sie eine Funktion zum Ausführen des Rollups erstellen:
CREATE OR REPLACE FUNCTION compute_event_rollups(start_time timestamptz, end_time timestamptz)
RETURNS void LANGUAGE PLPGSQL AS $function$
BEGIN
RAISE NOTICE 'Computing 60-minute rollups from % to % (excluded)', start_time, end_time;
RAISE NOTICE 'Aggregating event data into 60-minute rollup table';
INSERT INTO rollup_events
SELECT user_id,
event_type,
date_trunc('hour', created_at) as hour,
count(*) AS event_count
FROM payment_events
WHERE created_at >= start_time AND created_at <= end_time
GROUP BY user_id, event_type, hour
ON CONFLICT (user_id, event_type, hour)
DO UPDATE SET event_count = rollup_events.event_count + excluded.event_count;
END;
$function$;
Führen Sie nun die folgende Abfrage aus, um Ihre Funktion auszulösen und somit einen Rollup für die Daten der letzten Stunde in der Datenbank auszuführen:
SELECT compute_event_rollups(timestamp '2016-01-12 08:00:00',timestamp '2016-01-12 09:00:00');
Der letzte Schritt besteht darin, Ihre Rollups mithilfe der pg_cron-Erweiterung zu automatisieren. Führen Sie die folgende Abfrage mithilfe der cron.schedule()-Funktion aus, um die Ausführung des Auftrags auf den Beginn jeder Stunde an allen Tagen festzulegen (0 * * * *).
SELECT cron.schedule('0 * * * *', $$SELECT compute_event_rollups(timestamp '2016-01-12 05:00:00',timestamp '2016-01-12 06:00:00');$$);
Für das Dashboard der Woodgrove Bank wurde eine Abfrage angefordert, die eine Liste der Top-10-Benutzer*innen mit den meisten Ereignissen im Laufe der letzten Stunde bereitstellt. Sie haben die folgende Abfrage für die Rolluptabelle erstellt, um diese Informationen zu generieren:
SELECT user_id, sum(event_count) as total_events FROM rollup_events WHERE hour >=timestamp '2016-01-12 08:00:00' AND hour <=timestamp '2016-01-12 09:00:00' GROUP BY user_id ORDER BY total_events DESC LIMIT 10;
Wenn Sie den Auftrag abbrechen möchten, fragen Sie die cron.job-Tabelle ab, um die jobid Ihres Auftrags abzurufen:
SELECT * FROM cron.job;
Heben Sie dann den Zeitplan für den Auftrag mithilfe der folgenden Informationen auf, indem Sie das {job_id}-Token durch die jobid ersetzen, die Sie über die vorherige Abfrage abgerufen haben.
SELECT cron.unschedule({job_id});
Durch das Erweitern Ihrer Datenbank mit der pg_cron-Erweiterung können Sie PostgreSQL-Befehle direkt über die Datenbank planen. Sie können diese leistungsstarke und einfache Erweiterung für viele Aufgaben verwenden, z. B. für die Aggregation von Daten in nahezu Echtzeit, für Datenbankbereinigungen und administrative Aufgaben und vieles mehr.
Erweiterungen und pg_dump-Sicherungen
Die Woodgrove Bank verwendet das pg_dump-Hilfsprogramm für Sicherungen ihrer Datenbank. Bei der Verwendung mit Erweiterungen weiß pg_dump, dass die einzelnen Memberobjekte der Erweiterung nicht gesichert werden sollen. Stattdessen wird ein CREATE EXTENSION-Befehl in Sicherungen eingeschlossen. Diese Funktion vereinfacht die Migration zu einer neuen Erweiterungsversion, die im Vergleich zur älteren Version möglicherweise mehr oder andere Objekte enthält.