Tillämpa PostgreSQL-tillägg
Woodgrove Bank begärde att extra funktioner skulle läggas till i databasen. Med Azure Cosmos DB for PostgreSQL kan du utöka en databass funktioner med hjälp av många populära PostgreSQL-tillägg.
Visa tillägg som stöds och är förinstallerade
Woodgrove Bank bad om tillägg som gör det möjligt att lagra geospatiala data i databasen och möjligheten att köra schemalagda sammanslagningsjobb. När du har undersökt tillgängliga PostgreSQL-tillägg har du identifierat PostGIS och pg_cron tillägg som de bästa kandidaterna för att tillhandahålla dessa funktioner.
Kommentar
PostGIS är en rumslig databasutökning för PostgreSQL-objektrelationsdatabaser, vilket lägger till stöd för geografiska objekt som gör att platsfrågor kan köras i SQL.
pg_cron är en cron-baserad jobbschemaläggare som gör att du kan schemalägga PostgreSQL-kommandon direkt från databasen.
Nästa steg du behöver ta är att avgöra om de tillägg som du har identifierat stöds i Azure Cosmos DB för PostgreSQL. Det finns två sätt att utföra det här steget. Den första är att granska listan över tillägg som stöds av Azure Cosmos DB for PostgreSQL i Microsoft-dokumenten. Den andra och rekommenderade metoden är att köra följande fråga mot databasen:
SELECT * FROM pg_available_extensions;
Vyn pg_available_extensions innehåller en lista över tillägg som stöds som kan installeras. När du granskar utdata visas att båda tilläggen som du har valt stöds.
Många populära PostgreSQL-tillägg är förinstallerade på varje Azure Cosmos DB för PostgreSQL-instans. Innan du installerar nya tillägg som stöds är det en bra idé att visa den fullständiga listan över förinstallerade tillägg i databasen för att undvika konflikter. Du kan visa den här listan genom att köra följande fråga:
SELECT * FROM pg_extension;
Kommentar
Du kan också köra \dx från kommandoraden, ett genvägskommando som innehåller en lista över installerade tillägg i databasen.
För de tillägg som du vill använda för Woodgrove Bank visas frågeutdata som pg_cron är förinstallerade, men PostGIS tillägget gör det inte. Om du vill tillhandahålla de begärda funktionerna måste du läsa PostGIS in i databasen.
Installera tillägg i Azure Cosmos DB för PostgreSQL
Om du vill använda PostgreSQL-tillägg måste du först installera dem i databasen. Använd psql-verktyget för att läsa in de paketerade objekten i databasen.
Om du vill installera PostGIS tillägget i Woodgrove Banks databas bör du först försöka använda kommandot CREATE EXTENSION . Det här kommandot installerar det angivna tillägget i den aktuella databasen.
I bakgrunden körs tilläggets skriptfil genom att CREATE EXTENSION köras. Skriptet skapar vanligtvis nya SQL-objekt som funktioner, datatyper, operatorer och indexstödsmetoder. CREATE EXTENSION Dessutom registrerar identiteterna för alla skapade objekt så att de kan tas bort igen om DROP EXTENSION de utfärdas.
CREATE EXTENSION Kör kommandot för att installera PostGIS tillägget i databasen.
CREATE EXTENSION IF NOT EXISTS postgis;
Om du försöker installera ett tillägg med samma namn som det som redan har lästs in i databasen får du ett felmeddelande om att tillägget redan finns. Om du anger IF NOT EXISTS -satsen när du kör CREATE EXTENSION kommandot kan du undvika det här felet.
Om du kör det här kommandot mot Woodgrove Bank-databasen resulterar det i ett fel:
ERROR: permission denied to create extension "postgis"
HINT: Must be superuser to create this extension.
Inläsning av ett tillägg kräver vanligtvis samma behörigheter som krävs för att skapa dess komponentobjekt. För många tillägg innebär det här kravet att superanvändarbehörighet krävs. För dessa tillägg är det möjligt att CREATE EXTENSION det misslyckas med ett fel som nekas behörighet. Eftersom Azure Cosmos DB for PostgreSQL är en hanterad PaaS-tjänst i Azure kan endast Microsoft logga in med superanvändarrollen postgres . Men om tillägget har markerats som betrott i dess kontrollfil kan alla användare med behörighet på CREATE den aktuella databasen installera det.
Om du får det här felet när du installerar ett tillägg kan du prova create_extension() funktionen i stället. När du skapar ett Azure Cosmos DB for PostgreSQL-kluster anger du ett lösenord för serveradministratörsanvändaren citus . Det här kontot ger begränsad administrativ åtkomst till databasen och klustret. Användaren citus är medlem i azure_pg_admin, som kan installera PostgreSQL-tillägg. Funktionen create_extension() körs i användarens citus kontext.
Nu ska vi prova att använda create_extension() funktionen och se om den här funktionen tillåter PostGIS att tillägget skapas.
SELECT create_extension('postgis');
Kommandot körs, så vi kör följande för att verifiera:
\dx
Kommandots utdata visar att PostGIS det har installerats. Den innehåller även version, schema och beskrivning av tillägget.
Användaren som kör CREATE EXTENSION tilldelas som ägare till tillägget. Vanligtvis utses den användaren till ägare till alla objekt som skapats av tilläggets skript. Att känna till tilläggets ägare blir viktigt om du behöver ta bort eller ändra ett tillägg, eftersom båda måste göras i kontexten för den användaren.
Lägga till geospatiala kolumner i Woodgrove Banks händelsetabell
PostGIS När tillägget nu har lästs in är du redo att börja arbeta med geospatiala data i databasen. Woodgrove Banks utvecklare har uppdaterat det kontaktlösa betalningsprogrammet för att samla in latitud och longitud för alla transaktioner som kommer in i systemet. Om du vill lagra dessa data måste du ändra payment_events tabellen för att lägga till en geometry kolumn som accepterar point datatypen. Dessa nya datatyper ingår i PostGIS tillägget.
Som en påminnelse har tabellen payment_events följande struktur:
/* 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)
);
*/
För att hantera point data måste du lägga till en ny geometry kolumn i tabellen som accepterar point data:
ALTER TABLE payment_events
ADD COLUMN event_location geometry(point, 4326);
Testa sedan ändringarna genom att uppdatera den första händelseposten med geospatiala data som tillhandahålls av Woodgrove Banks utvecklingsteam.
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);
När en post nu har uppdaterats kan du använda funktionerna ST_X(point) och ST_Y(point) som läses in av PostGIS för att visa geospatiala data som infogades:
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;
Woodgrove Bank-databasen är redo att ta emot geospatiala data från appen contactless-payments. Utvecklingsteamet har tilldelat platsdata till var och en av sina handlare och hanterar massuppdateringen av befintliga transaktioner.
Konfigurera schemalagda jobb
Woodgrove Bank har bett dig att använda schemalagda uppgifter i databasen för att beräkna varje timme sammanslagningar av händelser per användare för en analytisk instrumentpanel. Med kan pg_crondu schemalägga ett jobb som anropar en funktion för att köras varje timme.
Kommentar
Datumintervallet för exempeldata är begränsat, så de tider som skickas till funktionen nedan är fasta för att representera hur jobbet kan anropas och schemaläggas. I ett verkligt scenario skulle start- och sluttiderna som skickas till funktionen från cron-jobbet vara för det senaste 60-minutersfönstret.
För att komma igång måste du skapa en sammanslagningstabell för att lagra aggregerade data:
-- 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');
För sammanslagningen använder du ett INSERT ... SELECT kommando som körs parallellt över alla noder i klustret. Det här kommandot läser in aggregerade data i sammanslagningstabellen. För att förenkla anropet av frågan för att utföra datasammansättningen kan du skapa en funktion för att utföra sammanslagningen:
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$;
Kör nu följande fråga för att utlösa funktionen för att samla in den senaste timmen med data i databasen:
SELECT compute_event_rollups(timestamp '2016-01-12 08:00:00',timestamp '2016-01-12 09:00:00');
Det sista steget är att automatisera dina sammanslagningar med hjälp av pg_cron tillägget. Kör följande fråga med hjälp av cron.schedule() funktionen för att schemalägga jobbet att köras högst upp i varje timme varje dag (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 Woodgrove Banks instrumentpanel begärde de en fråga som innehåller en lista över de 10 främsta användarna med flest händelser under den senaste timmen. Du skapade följande fråga mot sammanslagningstabellen för att generera den här informationen:
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;
Om du behöver stoppa jobbet frågar cron.job du tabellen för att hämta jobid jobbet:
SELECT * FROM cron.job;
Avmarkera sedan jobbet med hjälp av följande och ersätt {job_id} token när jobid du hämtade från föregående fråga.
SELECT cron.unschedule({job_id});
Om du utökar databasen med pg_cron tillägget kan du schemalägga PostgreSQL-kommandon direkt från databasen. Du kan använda det här kraftfulla och enkla tillägget för många uppgifter, inklusive aggregering av data i nästan realtid, rensning av databaser och administrativa uppgifter och mycket mer.
Tillägg och pg_dump säkerhetskopior
Woodgrove Bank använder pg_dump verktyget för att säkerhetskopiera sin databas. Lyckligtvis vet när det används med tillägg pg_dump att det inte ska dumpa de enskilda medlemsobjekten i tillägget. I stället kommer det att innehålla ett CREATE EXTENSION kommando i dumpar. Den här funktionen förenklar migreringen till en ny tilläggsversion som kan innehålla fler eller andra objekt än den gamla versionen.