Gerenciamento de extensões do PostgreSQL: instalar, versionar, atualizar e exportar

O que é

Segundo a documentação, uma extensão consiste em um arquivo de script com os comandos SQL que criam seus objetos e em um arquivo de controle com propriedades como a versão padrão, a relocabilidade e se ela é trusted, podendo incluir uma biblioteca compartilhada. CREATE EXTENSION name [SCHEMA s] [VERSION v] [CASCADE] carrega a extensão no banco de dados atual; pg_available_extensions e pg_available_extension_versions mostram o que a instalação do servidor oferece. ALTER EXTENSION name UPDATE [TO v] executa os scripts de atualização do autor, da versão instalada até a versão de destino. DROP EXTENSION remove de uma só vez todos os objetos membros, e o pg_dump grava apenas o comando CREATE EXTENSION, não os membros.

Por que importa

Existem três versões ao mesmo tempo: os arquivos instalados no servidor (normalmente um pacote do sistema operacional), a versão registrada no catálogo do banco de dados e, no caso de extensões com código C, a biblioteca carregada pelo servidor em execução. Elas se desalinham em atualizações de pacotes, no pg_upgrade e quando um dump é restaurado em um servidor cujos arquivos são mais antigos ou estão ausentes. Por isso, um dump depende, sem que isso fique evidente, de os arquivos da extensão estarem presentes no momento da restauração.

Como aplicar

• Instale os arquivos das extensões pelo mesmo caminho usado para o servidor (pacotes do sistema operacional ou a lista de extensões permitidas do serviço gerenciado) e registre as extensões e versões necessárias no histórico de migrações, para que um ambiente novo as recrie.
• Use CREATE EXTENSION IF NOT EXISTS ... SCHEMA extensions nas migrações quando a extensão permitir escolher o schema (uma extensão que define um schema em seu arquivo de controle não pode ter isso sobrescrito), de modo que os objetos membros não ofusquem os objetos da aplicação e os search paths permaneçam limpos.
• A instalação normalmente exige superusuário; uma extensão marcada como trusted pode ser instalada por qualquer pessoa com CREATE no banco de dados. A documentação alerta que um script escrito sem cuidado pode ser explorado por meio de objetos do tipo cavalo de troia no schema de instalação; por isso, instale em schemas nos quais usuários não confiáveis não tenham o privilégio CREATE.
• Depois de atualizações de pacote, compare installed_version com default_version em pg_available_extensions e execute ALTER EXTENSION ... UPDATE em uma janela de manutenção; depois do pg_upgrade, execute o script de atualização que ele gera.
• Módulos que precisam estar em shared_preload_libraries (pg_stat_statements, auto_explain) só passam a valer após a reinicialização do servidor; agende-a.
• Nunca altere objetos membros manualmente com CREATE OR REPLACE FUNCTION; a documentação afirma que essas alterações não são incluídas no dump.

Armadilhas

Os nomes de extensão são únicos por banco de dados, não por schema. CASCADE instala as dependências em suas versões padrão. Extensões que definem tipos de dados usados em tabelas (postgis, hstore, vector) precisam estar presentes antes que as tabelas possam ser restauradas; caso contrário, a restauração falha para essas tabelas, e não apenas para as funções. Serviços gerenciados publicam listas de extensões permitidas; uma extensão fora da lista bloqueia a migração para aquele serviço.