postgres postgresql extension

Extensões são pacotes que trazem funcionalidades adicionais variadas, desde novos tipos de dados, estratégias de indexação, linguagens de programação, assim como funções administrativas para backup, replicação e auditoria.

Várias delas são desenvolvidas e distribuídas junto com o próprio PostgreSQL, conhecidas como contrib e presentes em um pacote próprio em muitas distribuições (por exemplo, postgresql14-contrib em CentOS/RHEL). Outras são funcionalidades tão complexas que exigem uma equipe de desenvolvimento e manutenção dedicada. Muitas dezenas de extensões já estão disponíveis para instalação a partir do repositório do PGDG (PostgreSQL Development Group).

As maior parte das extensões é instalada e fica contida em cada banco de dados independentemente dos outros. Aquelas que trazem funcionalidades para a aplicação devem ser instaladas nos bancos de dados da aplicação (não no postgres) ou, possivelmente, nos templates que serão usados para instanciar bancos de dados de aplicação.

Alguns exemplos de extensões úteis contidas no próprio PostgreSQL:

• auto_explain: Quando consultas de longa duração forem executadas, coloca nos logs o EXPLAIN delas no momento em que estavam lentas, com métricas de timing de I/O e de hit/miss de páginas em shared_buffers.
• citext: Tipo de dados de texto insensível a caixa.
• pgcrypto: Funções de hashing, encriptação e desencriptação de dados.
• pg_buffercache: Visões sobre o uso de páginas de shared_buffers como cache de disco.
• pg_stat_statements: Coleta automática de estatísticas de execuções de consultas, com contagens, tempos e muito mais.
• pg_trgm: Tratamento de texto com trígramos, que permite, entre muitas outras coisas, indexação de consultas com expressões regulares e LIKE.
• tablefunc: Funções de tratamento de consultas e resultados, que permitem pivoteamento (conversão de resultados em formato long para wide) e consultas em árvores.
• unaccent: Funções de remoção de acentos em textos.
• ...

A lista de extensões disponíveis no sistema pode ser obtida pela visão pg_available_extensions e a lista de extensões atualmente instaladas em um banco de dados pode ser obtida pelo metacomando \dx:

[[local]:5432] app@appdb=# \dx
                                        List of installed extensions
╔══════════════╤═════════╤════════════╤════════════════════════════════════════════════════════════════════╗
║     Name     │ Version │   Schema   │                            Description                             ║
╠══════════════╪═════════╪════════════╪════════════════════════════════════════════════════════════════════╣
║ btree_gin    │ 1.3     │ dba_schema │ support for indexing common datatypes in GIN                       ║
║ btree_gist   │ 1.5     │ dba_schema │ support for indexing common datatypes in GiST                      ║
║ citext       │ 1.6     │ public     │ data type for case-insensitive character strings                   ║
║ hstore       │ 1.7     │ public     │ data type for storing sets of (key, value) pairs                   ║
║ intarray     │ 1.3     │ public     │ functions, operators, and index support for 1-D arrays of integers ║
║ ltree        │ 1.2     │ public     │ data type for hierarchical tree-like structures                    ║
║ pageinspect  │ 1.8     │ dba_schema │ inspect the contents of database pages at a low level              ║
║ pg_trgm      │ 1.5     │ dba_schema │ text similarity measurement and index searching based on trigrams  ║
║ pgcrypto     │ 1.3     │ public     │ cryptographic functions                                            ║
║ plpgsql      │ 1.0     │ pg_catalog │ PL/pgSQL procedural language                                       ║
║ postgres_fdw │ 1.0     │ public     │ foreign-data wrapper for remote PostgreSQL servers                 ║
║ unaccent     │ 1.1     │ public     │ text search dictionary that removes accents                        ║
║ uuid-ossp    │ 1.1     │ public     │ generate universally unique identifiers (UUIDs)                    ║
╚══════════════╧═════════╧════════════╧════════════════════════════════════════════════════════════════════╝
(13 rows)

Para a instalação e uso de cada uma, é importante ler a sua respectiva documentação.

Aquelas extensões que apenas afetam o comportamento do servidor através de hooks podem ser carregadas com o comando LOAD. As que precisam ser trazidas em tempo de inicialização do postmaster precisam ser carregadas por ele através da configuração shared_preload_libraries. E as que trazem funções, visões e outros objetos que podem ser usados diretamente pelo usuário e aplicação devem ter seus objetos criados pelo comando CREATE EXTENSION:

[[local]:5432] app@appdb=# CREATE EXTENSION pg_trgm;

A própria extensão não está restrita a um schema, sendo um objeto do banco de dados inteiro. Mas os objetos contidos nela podem ser colocados em um schema para melhorar a organização:

[[local]:5432] app@appdb=# CREATE SCHEMA utilitarios;
[[local]:5432] app@appdb=# CREATE EXTENSION pg_trgm SCHEMA utilitarios;

Também podemos consultar a versão da extensão com \dx e listar os objetos trazidos por ela com \dx+:

[[local]:5432] app@appdb=# \dx unaccent
                        List of installed extensions
╔══════════╤═════════╤════════╤═════════════════════════════════════════════╗
║   Name   │ Version │ Schema │                 Description                 ║
╠══════════╪═════════╪════════╪═════════════════════════════════════════════╣
║ unaccent │ 1.1     │ public │ text search dictionary that removes accents ║
╚══════════╧═════════╧════════╧═════════════════════════════════════════════╝
(1 row)

[[local]:5432] app@appdb=# \dx+ unaccent
                 Objects in extension "unaccent"
╔═══════════════════════════════════════════════════════════════╗
║                      Object description                       ║
╠═══════════════════════════════════════════════════════════════╣
║ function unaccent(regdictionary,text)                         ║
║ function unaccent(text)                                       ║
║ function unaccent_init(internal)                              ║
║ function unaccent_lexize(internal,internal,internal,internal) ║
║ text search dictionary unaccent                               ║
║ text search template unaccent                                 ║
╚═══════════════════════════════════════════════════════════════╝
(6 rows)

As extensões têm versões próprias que podem acompanhar a versão do PostgreSQL ou serem completamente independentes. Então as versões das extensões precisam ser atualizadas com cuidado junto com os upgrades do próprio PostgreSQL.

Na maioria dos casos, a atualização das extensões é trivial, com o comando ALTER EXTENSION:

[[local]:5432] app@appdb=# ALTER EXTENSION hstore UPDATE;

Também é possível atualizar para alguma versão específica da extensão, dentro das possibilidades listadas na visão pg_available_extension_versions, mas esse caso é menos comum.

Algumas extensões precisam de um pouco mais de atenção na atualização, então uma revisão das notas é sempre recomendado para todo upgrade.

Como exemplo, o PostgreSQL 9.5 adicionou algumas colunas a mais na tabela do pg_stat_statements. Isso não afetará o ambiente produtivo, mas é possível que sistemas de monitoramento externos precisem de ajustes antes que a extensão seja atualizada no PostgreSQL.

Outro caso é o PostgreSQL 12, no qual o unaccent passou a tratar mais caracteres de símbolos e pontuações; então a saída da função mudou, o que significa que consultas e índices retornariam tuplas diferentes após o upgrade do PostgreSQL. Nesse caso, é importante identificar índices funcionais, valores DEFAULT de colunas, visões materializadas e todo outro uso da função unaccent() na sua aplicação e avaliar quais precisam ser recriados (REINDEX e REFRESH MATERIALIZED VIEW) após o upgrade da extensão.

Casos mais complexos acontecem com extensões mais complexas, como o PostGIS, mais adiante.

Algumas funcionalidades são mais complexas, desenvolvidas por equipes diferentes e precisam de atenção especial.

O PostGIS é a extensão que permite que as consultas trabalhem com dados de SIGs (Sistemas de Informação Geográfica), ou GIS em inglês (Geographic Information System). Com ele, podemos representar dados vetoriais como formatos de países, estados, municípios, terrenos e logradouros e então consultá-los em função de proximidade, distâncias e até mesmo encontrar rotas entre pontos no mapa (com outra extensão pgRouting). Também podemos representar dados rasterizados, como relevo e mensurações atmosféricas.

A instalação do PostGIS começa após os passos de instalação do PostgreSQL, com a instalação dos pacotes do próprio PostGIS. Eles precisam ser escolhidos com base tanto na versão majoritária do PostgreSQL quanto na versão do próprio PostGIS, com algumas combinações suportadas. Por exemplo, para instalar o PostGIS 3.1 no PostgreSQL 14:

Apenas em CentOS/RHEL 8 precisamos habilitar o repositório PowerTools, que contém as bibliotecas de processamento matemático e geográfico sobre as quais o PostGIS se apoia:

[root@pg-1 ~]# dnf config-manager --set-enabled PowerTools

E em todos os CentOS/RHEL podemos fazer a instalação do pacote específico:

[root@pg-1 ~]# yum -y install postgis31_14

root@pg-1:~# apt install postgresql-14-postgis-3

Como o PostGIS é composto por uma grande quantidade de funcionalidades e não é toda aplicação que usaria todas elas, elas são disponibilizadas através de várias extensões, assim podemos importar apenas as que nos interessam.

Elas são:

• postgis: extensão base, com os tipos de dados, funções, operadores e índices comuns a todas as outras;
• postgis_raster: extensão com funções de conversão entre dados rasterizados e vetoriais;
• postgis_topology: extensão com funções de topologia;
• postgis_sfcgal: extensão com funções espaciais avançadas (2D e 3D) importadas da biblioteca CGAL;
• postgis_tiger_geocoder: extensão com o geocoder TIGER (precisa de address_standardizer e fuzzystrmatch);
• address_standardizer: extensão com um padronizador de endereços, que separa componentes como logradouro, país e número; e
• address_standardizer_data_us: extensão com dados de exemplo para o address_standardizer.

Para instalarmos elas, seguiremos a ordem da documentação do PostGIS, mas nem todas as extensões são necessárias para todas as aplicações:

-- Enable PostGIS (as of 3.0 contains just geometry/geography)
CREATE EXTENSION postgis;

-- enable raster support (for 3+)
CREATE EXTENSION postgis_raster;

-- Enable Topology
CREATE EXTENSION postgis_topology;

-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;

-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;

-- rule based standardizer
CREATE EXTENSION address_standardizer;

-- example rule data set
CREATE EXTENSION address_standardizer_data_us;

-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

É importante garantir que existam backups válidos dos dados antes de prosseguir.

Quando fazemos uma atualização apenas da versão de bugfix do PostGIS, que afeta apenas os executáveis e funções do sistema, é suficiente atualizarmos as extensões. Para isso, um usamos um ALTER EXTENSION ... UPDATE em cada extensão instalada, seguido de um SELECT postgis_extensions_upgrade() (a partir do PostGIS 2.5).

Então um SELECT postgis_full_version() mostrará a versão atualizada. Em algumas combinações de versão de PostgreSQL e PostGIS, a saída dessa função pode dizer que o upgrade não foi completo porque algumas estruturas de objetos do PostGIS não podem ser atualizadas e precisam ser recriadas. Nesses casos, é necessário seguir os passos de upgrade através de dump/restore.