Extensões PostgreSQL
Este documento explica o que são extensões do PostgreSQL, como habilitá-las em um banco de dados e lista as extensões disponíveis no DBaaS.
O que é uma extensão do PostgreSQL
Uma extensão empacota objetos SQL e/ou binários (funções, tipos, operadores, índices, procedimentos, views, arquivos de controle e, às vezes, background workers) que estendem a funcionalidade do PostgreSQL sem alterar o código-fonte do servidor. Em vez de compilar alterações no servidor, uma extensão entrega conjuntos reutilizáveis de objetos e scripts de instalação/atualização que podem ser gerenciados com comandos SQL padrão.
Principais características:
- Conteúdo misto: pode conter apenas scripts SQL (criação de funções, operadores, tipos, etc.) ou também módulos binários (C/C++), dependendo da extensão.
- Instalação por banco: extensões são instaladas em um banco de dados específico e seus objetos passam a existir naquele banco.
- Controle de versão: cada extensão tem versões e scripts de atualização, permitindo executar
ALTER EXTENSION <extensão> UPDATE TO <versão>para evoluir a extensão sem perder dados. - Dependências: uma extensão pode declarar dependências em outras extensões; o PostgreSQL cuida da ordem de instalação/remoção.
- Schema: por padrão a extensão instala seus objetos em um schema (geralmente
public), mas é possível especificar outro schema ao criar a extensão. - Ciclo de vida:
CREATE EXTENSION/ALTER EXTENSION/DROP EXTENSION(comCASCADEouRESTRICT) são os comandos para instalar, atualizar e remover extensões. - Permissões e segurança: a instalação de algumas extensões podem requerer privilégios elevados (por exemplo, superusuário) porque extensões podem executar código nativo e criar objetos críticos. Se você precisar de uma extensão que requer privilégios elevados, consulte a equipe de suporte da MGC.
- Boas práticas: testar em ambientes de desenvolvimento, ler a documentação da extensão, instalar em schemas controlados quando adequado e versionar mudanças do banco antes de atualizar ou remover extensões.
Habilitar uma extensão em um banco de dados
Verificar disponibilidade:
- Para verificar as extensões que já estão instaladas no seu banco de dados, execute no
psql:\dx+ - Para listar todas extensões disponíveis (instaladas ou não):
SELECT name, default_version, installed_version
FROM pg_available_extensions; - Para lista todas as extensões que ainda não foram instaladas:
SELECT name, default_version
FROM pg_available_extensions
WHERE installed_version IS NULL; - Para verificar se uma extensão específica está disponível:
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name = 'nome_da_extensão';
Instalar no banco de dados atual:
- Comando básico:
CREATE EXTENSION IF NOT EXISTS nome_da_extensão; - Para instalar em um schema específico:
CREATE EXTENSION IF NOT EXISTS nome_da_extensão WITH SCHEMA my_schema;
Verificar as extensões instaladas:
- Extensões instaladas no banco de dados atual:
SELECT * FROM pg_extension;
Extensões disponíveis no DBaaS da Magalu Cloud
As seguintes extensões estão disponíveis para instalação em bancos de dados PostgreSQL no DBaaS da Magalu Cloud:
| Extensão | Descrição | Documentação |
|---|---|---|
| intarray | Versão suportada: 1.5. Tipos e operadores adicionais para arrays de inteiros. | Link |
| postgis_topology-3 | Versão suportada: 3.6.2. Extensão de topologia do PostGIS. | Link |
| earthdistance | Versão suportada: 1.2. Cálculos de distância sobre a Terra usando coordenadas. | Link |
| bloom | Versão suportada: 1.0. Método de índice Bloom para buscas com múltiplas colunas. | Link |
| vector | Versão suportada: 0.8.2. Suporte a vetores e busca por similaridade vetorial. | Link |
| adminpack | Versão suportada: 2.1. Funções administrativas auxiliares para PostgreSQL. | Link |
| tablefunc | Versão suportada: 1.0. Funções adicionais, incluindo tabelas cruzadas (crosstab). | Link |
| moddatetime | Versão suportada: 1.0. Atualiza automaticamente campos de data e hora de modificação. | Link |
| pg_walinspect | Versão suportada: 1.1. Inspeção do Write-Ahead Log (WAL) do PostgreSQL. | Link |
| postgis_raster-3 | Versão suportada: 3.6.2. Suporte raster do PostGIS. | Link |
| postgis_sfcgal-3 | Versão suportada: 3.6.2. Integração do PostGIS com SFCGAL. | Link |
| fuzzystrmatch | Versão suportada: 1.2. Funções para comparação aproximada e similaridade textual. | Link |
| postgis-3 | Versão suportada: 3.6.2. Extensão geoespacial PostGIS. | Link |
| postgis | Versão suportada: 3.6.2. Extensão geoespacial PostGIS. | Link |
| pgaudit | Versão suportada: 16.1. Auditoria detalhada de comandos e sessões no PostgreSQL. | Link |
| postgis_sfcgal | Versão suportada: 3.6.2. Integração do PostGIS com SFCGAL. | Link |
| postgis_tiger_geocoder-3 | Versão suportada: 3.6.2. Geocodificação TIGER para PostGIS. | Link |
| address_standardizer_data_us | Versão suportada: 3.6.2. Dados auxiliares US para padronização de endereços no PostGIS. | Link |
| old_snapshot | Versão suportada: 1.0. Inspeção de snapshots antigos do PostgreSQL. | Link |
| dict_int | Versão suportada: 1.0. Dicionário de busca textual para números inteiros. | Link |
| dict_xsyn | Versão suportada: 1.0. Dicionário de sinônimos estendido para busca textual. | Link |
| pg_visibility | Versão suportada: 1.2. Ferramentas para examinar o mapa de visibilidade das tabelas. | Link |
| file_fdw | Versão suportada: 1.0. Foreign data wrapper para acessar arquivos do sistema. | Link |
| pg_buffercache | Versão suportada: 1.4. Examina o conteúdo do buffer cache compartilhado do PostgreSQL. | Link |
| pglogical_origin | Versão suportada: 1.0.0. Origem lógica auxiliar para ecossistema pglogical. | Link |
| address_standardizer | Versão suportada: 3.6.2. Padronização de endereços no ecossistema PostGIS. | Link |
| intagg | Versão suportada: 1.1. Funções agregadas para inteiros. | Link |
| cube | Versão suportada: 1.5. Tipo de dados multidimensional em forma de cubo. | Link |
| citext | Versão suportada: 1.6. Tipo de texto case-insensitive. | Link |
| pg_trgm | Versão suportada: 1.6. Funções e operadores baseados em trigramas para similaridade textual. | Link |
| tsm_system_rows | Versão suportada: 1.0. Método de amostragem por número aproximado de linhas. | Link |
| btree_gist | Versão suportada: 1.7. Operadores B-tree implementados sobre GiST. | Link |
| lo | Versão suportada: 1.1. Suporte adicional para large objects. | Link |
| address_standardizer_data_us-3 | Versão suportada: 3.6.2. Dados auxiliares US para padronização de endereços no PostGIS. | Link |
| plpgsql | Versão suportada: 1.0. Linguagem procedural padrão do PostgreSQL. | Link |
| amcheck | Versão suportada: 1.3. Verificação da integridade lógica de índices e relações. | Link |
| tsm_system_time | Versão suportada: 1.0. Método de amostragem baseado em tempo de execução. | Link |
| ltree | Versão suportada: 1.2. Tipo de dados para estruturas hierárquicas em árvore. | Link |
| postgres_fdw | Versão suportada: 1.1. Foreign data wrapper para acessar outros bancos PostgreSQL. | Link |
| pg_surgery | Versão suportada: 1.0. Ferramentas de correção de emergência em dados do sistema. | Link |
| pg_prewarm | Versão suportada: 1.2. Pré-carrega dados em cache para acelerar consultas. | Link |
| pglogical | Versão suportada: 2.4.6. Replicação lógica para PostgreSQL. | Link |
| insert_username | Versão suportada: 1.0. Preenche automaticamente o nome do usuário em colunas configuradas. | Link |
| pg_freespacemap | Versão suportada: 1.2. Examina o mapa de espaço livre das relações. | Link |
| xml2 | Versão suportada: 1.1. Funções adicionais para processamento de XML. | Link |
| pg_stat_statements | Versão suportada: 1.10. Rastreia estatísticas de execução de comandos SQL. | Link |
| postgis_topology | Versão suportada: 3.6.2. Extensão de topologia do PostGIS. | Link |
| postgis_raster | Versão suportada: 3.6.2. Suporte raster do PostGIS. | Link |
| unaccent | Versão suportada: 1.1. Remove acentuação de textos para busca e normalização. | Link |
| timescaledb | Versão suportada: 2.25.1. Extensão de banco de dados de séries temporais para PostgreSQL. | Link |
| isn | Versão suportada: 1.2. Tipos de dados para números normalizados internacionais. | Link |
| postgis_tiger_geocoder | Versão suportada: 3.6.2. Geocodificação TIGER para PostGIS. | Link |
| address_standardizer-3 | Versão suportada: 3.6.2. Padronização de endereços no ecossistema PostGIS. | Link |
| refint | Versão suportada: 1.0. Funções auxiliares para integridade referencial. | Link |
| tcn | Versão suportada: 1.0. Notificações de mudanças em linhas de tabelas. | Link |
| dblink | Versão suportada: 1.2. Conexão e consultas a outros bancos PostgreSQL via dblink. | Link |
| hstore | Versão suportada: 1.8. Armazena pares chave-valor em uma única coluna. | Link |
| pg_cron | Versão suportada: 1.6. Agendamento de tarefas SQL dentro do PostgreSQL. | Link |
| autoinc | Versão suportada: 1.0. Funções auxiliares para incremento automático de campos. | Link |
| seg | Versão suportada: 1.4. Tipo de dados para intervalos numéricos. | Link |
| uuid-ossp | Versão suportada: 1.1. Gera UUIDs usando a biblioteca OSSP. | Link |
| pgrowlocks | Versão suportada: 1.2. Exibe informações sobre bloqueios em nível de linha. | Link |
| pgcrypto | Versão suportada: 1.3. Funções criptográficas para PostgreSQL. | Link |
| btree_gin | Versão suportada: 1.3. Operadores B-tree implementados sobre GIN. | Link |
| sslinfo | Versão suportada: 1.2. Informações sobre o certificado SSL da conexão atual. | Link |
| pgstattuple | Versão suportada: 1.5. Mede estatísticas físicas de tabelas e índices. | Link |
Casos de uso comuns
- Análise de desempenho: Usar
pg_buffercachepara entender o uso do cache e otimizar consultas. Por exemplo, identificar tabelas e índices que não estão sendo bem aproveitados. - Replicação: Implementar replicação lógica com
pglogicalpara alta disponibilidade e recuperação de desastres. - Séries temporais: Utilizar
timescaledbpara gerenciar grandes volumes de dados de séries temporais. É muito comum em monitoramento de aplicações, IoT e análise financeira. - Busca de texto: Melhorar a busca de similaridade com
pg_trgm. Por exemplo, implementar buscas "fuzzy" em campos de texto para lidar com erros de digitação. - Identificadores únicos: Gerar UUIDs com
uuid-ossppara garantir unicidade em registros. - Segurança: Proteger dados sensíveis com
pgcrypto, como criptografar colunas de dados pessoais. - Similaridade vetorial: Implementar buscas baseadas em similaridade com
vector, útil em sistemas de recomendação e busca por conteúdo. - Auditoria: Inspecionar o WAL com
pg_walinspectpara auditoria e recuperação.