Aplicar extensões PostgreSQL
O Woodgrove Bank solicitou que recursos extras fossem adicionados ao seu banco de dados. O Azure Cosmos DB para PostgreSQL permite estender a funcionalidade de um banco de dados usando muitas extensões populares do PostgreSQL.
Ver extensões suportadas e pré-instaladas
O Woodgrove Bank pediu extensões que permitissem que os dados geoespaciais fossem armazenados no banco de dados e a capacidade de executar trabalhos de rollup agendados. Depois de pesquisar as extensões PostgreSQL disponíveis, você identificou o PostGIS e pg_cron as extensões como os melhores candidatos para fornecer esses recursos.
Nota
PostGIS é um extensor de banco de dados espacial para bancos de dados objeto-relacionais PostgreSQL, adicionando suporte para objetos geográficos que permitem que consultas de localização sejam executadas em SQL.
pg_cron é um agendador de tarefas baseado em cron que permite agendar comandos PostgreSQL diretamente do banco de dados.
A próxima etapa que você precisa executar é determinar se as extensões identificadas têm suporte no Azure Cosmos DB para PostgreSQL. Há duas maneiras de realizar essa etapa. A primeira é rever a lista de Extensões suportadas pelo Azure Cosmos DB para PostgreSQL nos documentos da Microsoft. A segunda abordagem recomendada é executar a seguinte consulta em seu banco de dados:
SELECT * FROM pg_available_extensions;
A pg_available_extensions vista fornece uma lista de extensões suportadas disponíveis para instalação. A revisão da saída revela que ambas as extensões selecionadas são suportadas.
Muitas extensões populares do PostgreSQL são pré-instaladas em cada instância do Azure Cosmos DB para PostgreSQL. Antes de instalar quaisquer novas extensões suportadas, é uma boa ideia ver a lista completa de extensões pré-instaladas na sua base de dados para evitar conflitos. Você pode exibir essa lista executando a seguinte consulta:
SELECT * FROM pg_extension;
Nota
Como alternativa, você pode executar \dx a partir da linha de comando, um comando de atalho que fornece uma lista de extensões instaladas em seu banco de dados.
Para as extensões que você deseja usar para o Woodgrove Bank, a saída da consulta mostra que pg_cron vem pré-instalada, mas a PostGIS extensão não. Para fornecer os recursos solicitados, você deve carregar PostGIS em seu banco de dados.
Instalar extensões no Azure Cosmos DB para PostgreSQL
Para usar extensões PostgreSQL, você deve primeiro instalá-las em seu banco de dados. Use a ferramenta psql para carregar os objetos empacotados em seu banco de dados.
Para instalar a PostGIS extensão no banco de dados do Woodgrove Bank, você deve primeiro tentar usar o comando CREATE EXTENSION . Este comando instala a extensão especificada no banco de dados atual.
Nos bastidores, a execução CREATE EXTENSION executa o arquivo de script da extensão. O script normalmente cria novos objetos SQL, como funções, tipos de dados, operadores e métodos de suporte de índice. Além disso, CREATE EXTENSION registra as identidades de todos os objetos criados para que eles possam ser descartados novamente se DROP EXTENSION for emitido.
Execute o CREATE EXTENSION comando para instalar a PostGIS extensão em seu banco de dados.
CREATE EXTENSION IF NOT EXISTS postgis;
Se você tentar instalar uma extensão com o mesmo nome de uma já carregada no banco de dados, receberá um erro informando que a extensão já existe. Especificar a IF NOT EXISTS cláusula ao executar o CREATE EXTENSION comando permite evitar esse erro.
A execução deste comando no banco de dados do Woodgrove Bank resulta em erro:
ERROR: permission denied to create extension "postgis"
HINT: Must be superuser to create this extension.
Carregar uma extensão normalmente requer os mesmos privilégios necessários para criar seus objetos componentes. Para muitas extensões, esse requisito significa que privilégios de superusuário são necessários. Para essas extensões, é possível que CREATE EXTENSION falhe com um erro de permissão negada. Como o Azure Cosmos DB para PostgreSQL é um serviço PaaS gerenciado no Azure, somente a Microsoft pode entrar com a postgres função de superusuário. No entanto, se a extensão estiver marcada como confiável em seu arquivo de controle, qualquer usuário com o CREATE privilégio no banco de dados atual poderá instalá-lo.
Se você receber esse erro ao instalar uma extensão, tente a create_extension() função. Ao criar um cluster do Azure Cosmos DB para PostgreSQL, você fornece uma senha para o usuário administrador do citus servidor. Essa conta concede acesso administrativo limitado ao banco de dados e ao cluster. O citus usuário é um membro do azure_pg_admin, que pode instalar extensões PostgreSQL. A create_extension() função é executada no contexto do citus usuário.
Vamos tentar usar a função e ver se esta função permite que a create_extension()PostGIS extensão seja criada.
SELECT create_extension('postgis');
O comando é executado com êxito, então vamos executar o seguinte para verificar:
\dx
A saída do comando revela que PostGIS foi instalado com êxito. Ele também fornece a versão, esquema e descrição da extensão.
O usuário que executa CREATE EXTENSION é atribuído como o proprietário da extensão. Normalmente, esse usuário é designado como o proprietário de todos os objetos criados pelo script da extensão. Conhecer o proprietário da extensão torna-se vital se você precisar remover ou alterar uma extensão, pois ambos devem ser feitos no contexto desse usuário.
Adicionar colunas geoespaciais à tabela de eventos do Woodgrove Bank
Com a PostGIS extensão agora carregada, você está pronto para começar a trabalhar com dados geoespaciais no banco de dados. Os desenvolvedores do Woodgrove Bank atualizaram o aplicativo de pagamento sem contato para capturar a latitude e longitude de todas as transações que entram no sistema. Para armazenar esses dados, você precisa alterar a payment_events tabela para adicionar uma geometry coluna que aceite o point tipo de dados. Esses novos tipos de dados estão incluídos na PostGIS extensão.
Como lembrete, a tabela tem a payment_events seguinte estrutura:
/* 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)
);
*/
Para acomodar point dados, você deve adicionar uma nova geometry coluna à tabela que aceita point dados:
ALTER TABLE payment_events
ADD COLUMN event_location geometry(point, 4326);
Em seguida, teste suas alterações atualizando o primeiro registro de evento com dados geoespaciais fornecidos pela equipe de desenvolvimento do Woodgrove Bank.
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);
Com um registro agora atualizado, você pode usar as ST_X(point) funções e ST_Y(point) carregadas por PostGIS para visualizar os dados geoespaciais que foram inseridos:
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;
A base de dados do Woodgrove Bank está pronta para aceitar dados geoespaciais da aplicação de pagamentos sem contacto. A equipe de desenvolvimento atribuiu dados de localização a cada um de seus comerciantes e lidará com a atualização em massa das transações existentes.
Configurar trabalhos agendados
O Woodgrove Bank pediu que você usasse tarefas agendadas no banco de dados para calcular rollups horários de eventos por usuário para um painel analítico. Usando pg_crono , você pode agendar um trabalho que chama uma função para ser executada a cada hora.
Nota
O intervalo de datas dos dados de exemplo é limitado, portanto, os tempos passados para a função abaixo são fixos para representar como o trabalho pode ser chamado e agendado. Em um cenário do mundo real, os horários de início e término que estão sendo passados para a função a partir do trabalho cron seriam para a janela de 60 minutos mais recente.
Para começar, você precisa criar uma tabela cumulativa para armazenar os dados agregados:
-- 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');
Para o rollup, você usará um INSERT ... SELECT comando, que será executado em paralelo em todos os nós do cluster. Este comando carrega os dados agregados na tabela de rollup. Para simplificar a chamada da consulta para executar a agregação de dados, você pode criar uma função para executar o rollup:
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$;
Agora, execute a seguinte consulta para acionar sua função para acumular a última hora de dados no banco de dados:
SELECT compute_event_rollups(timestamp '2016-01-12 08:00:00',timestamp '2016-01-12 09:00:00');
A etapa final é automatizar seus rollups usando a pg_cron extensão. Execute a seguinte consulta usando a cron.schedule() função para agendar o trabalho para ser executado na parte superior de cada hora de cada dia (0 * * * *).
SELECT cron.schedule('0 * * * *', $$SELECT compute_event_rollups(timestamp '2016-01-12 05:00:00',timestamp '2016-01-12 06:00:00');$$);
Para o painel do Woodgrove Bank, eles solicitaram uma consulta que fornece uma lista dos 10 principais usuários com mais eventos na última hora. Você criou a seguinte consulta na tabela cumulativa para gerar essas informações:
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;
Se você precisar parar o trabalho, consulte a cron.job tabela para recuperar o jobid do seu trabalho:
SELECT * FROM cron.job;
Em seguida, desagende o trabalho usando o seguinte, substituindo o {job_id} token quando você recuperou jobid da consulta anterior.
SELECT cron.unschedule({job_id});
Estender seu banco de dados com a pg_cron extensão permite que você agende comandos PostgreSQL diretamente do banco de dados. Você pode usar essa extensão poderosa e simples para muitas tarefas, incluindo a agregação de dados em tempo quase real, limpeza de banco de dados e tarefas administrativas, e muito mais.
Extensões e backups pg_dump
O Woodgrove Bank usa o utilitário para fazer backup de pg_dump seu banco de dados. Felizmente, quando usado com extensões, pg_dump sabe que não deve despejar os objetos membros individuais da extensão. Em vez disso, ele incluirá um CREATE EXTENSION comando em dumps. Esse recurso simplifica muito a migração para uma nova versão de extensão que pode conter mais objetos ou objetos diferentes da versão antiga.