Etapa 1: Criar um usuário (função) para gerenciar a extensão PostGIS Etapa 2: Carregar as extensões PostGIS Etapa 3: Transferir a propriedade dos esquemas de extensão Etapa 4: Transferir a propriedade dos objetos PostGIS Etapa 5: Testar as extensões Etapa 6: Atualize a extensão PostGIS Versões de extensão PostGIS Upgrade do PostGIS 2 para o PostGIS 3

Gerenciar dados espaciais com a extensão PostGIS

PostGIS é uma extensão do PostgreSQL para armazenar e gerenciar informações espaciais. Para saber mais sobre a extensão PostGIS, consulte Postgis.net.

Desde a versão 10.5, o PostgreSQL é compatível com a biblioteca libprotobuf 1.3.0 usada pelo PostGIS para trabalhar com dados de blocos vetoriais do Mapbox.

A configuração da extensão PostGIS exige privilégios rds_superuser. Recomendamos criar um usuário (perfil) para gerenciar a extensão PostGIS e os dados espaciais. A extensão PostGIS e seus componentes relacionados adicionam milhares de funções ao PostgreSQL. Considere criar a extensão PostGIS em seu próprio esquema se isso fizer sentido para o seu caso de uso. O exemplo a seguir mostra como instalar a extensão em seu próprio banco de dados, mas isso não é necessário.

Tópicos

Etapa 1: Criar um usuário (função) para gerenciar a extensão PostGIS
Etapa 2: Carregar as extensões PostGIS
Etapa 3: Transferir a propriedade dos esquemas de extensão
Etapa 4: Transferir a propriedade dos objetos PostGIS
Etapa 5: Testar as extensões
Etapa 6: Atualize a extensão PostGIS
Versões de extensão PostGIS
Upgrade do PostGIS 2 para o PostGIS 3

Etapa 1: Criar um usuário (função) para gerenciar a extensão PostGIS

Primeiro, conecte-se a uma instância de banco de dados do RDS para PostgreSQL como um usuário que tem privilégios rds_superuser. Se você manteve o nome padrão ao configurar a instância, se conectará como postgres.


psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

Crie um perfil separado (usuário) para administrar a extensão PostGIS.


postgres=>  CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE

Conceda a esse perfil privilégios rds_superuser para permitir que ele instale a extensão.


postgres=> GRANT rds_superuser TO gis_admin;
GRANT

Crie um banco de dados a ser usado para seus artefatos PostGIS. Esta etapa é opcional. Como alternativa, você pode criar um esquema em seu banco de dados de usuário para as extensões PostGIS, mas isso também não é necessário.


postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE

Conceda a gis_admin todos os privilégios no banco de dados lab_gis.


postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT

Saia da sessão e reconecte-se a uma instância de banco de dados do RDS para PostgreSQL como gis_admin.


postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>

Continue a configurar a extensão conforme detalhado nas próximas etapas.

Etapa 2: Carregar as extensões PostGIS

A extensão PostGIS inclui várias extensões relacionadas que funcionam juntas para fornecer funcionalidade geoespacial. Dependendo do seu caso de uso, talvez você não precise de todas as extensões criadas nesta etapa.

Use instruções CREATE EXTENSION para carregar as extensões PostGIS.


CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION

É possível verificar os resultados executando a consulta SQL mostrada no exemplo a seguir, que lista as extensões e seus proprietários.


SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;
List of schemas
     Name     |   Owner
--------------+-----------
 public       | postgres
 tiger        | rdsadmin
 tiger_data   | rdsadmin
 topology     | rdsadmin
(4 rows)

Etapa 3: Transferir a propriedade dos esquemas de extensão

Use as instruções ALTER SCHEMA para transferir a propriedade dos esquemas à função gis_admin.


ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin; 
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA

Se você quiser confirmar a alteração de propriedade, realize a consulta SQL a seguir. Ou é possível usar o metacomando \dn na linha de comando do psql.


SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;

       List of schemas
     Name     |     Owner
--------------+---------------
 public       | postgres
 tiger        | gis_admin
 tiger_data   | gis_admin
 topology     | gis_admin
(4 rows)

Etapa 4: Transferir a propriedade dos objetos PostGIS

nota

Não altere a propriedade das funções do PostGIS. A operação adequada e as futuras atualizações do PostGIS exigem que essas funções retenham a propriedade original. Para ter mais informações sobre permissões do PostGIS, consulte Segurança do PostgreSQL.

Use a seguinte função para transferir a propriedade das tabelas do PostGIS ao perfil gis_admin. Execute a seguinte instrução no prompt psql para criar a função.


CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION

Depois, execute a consulta a seguir para executar a função exec que, por sua vez, executa as instruções e altera as permissões.


SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

Etapa 5: Testar as extensões

Para evitar a necessidade de especificar o nome do esquema, adicione o esquema tiger ao seu caminho de pesquisa usando o seguinte comando.


SET search_path=public,tiger;
SET

Teste o esquema tiger usando a seguinte instrução SELECT.


SELECT address, streetname, streettypeabbrev, zip
 FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev |  zip
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row)

Para saber mais sobre essa extensão, consulte Geocodificador Tiger na documentação do PostGIS.

Teste o acesso ao esquema topology usando a seguinte instrução SELECT. Isso chama a função createtopology para registrar um novo objeto de topologia (my_new_topo) com o identificador de referência espacial especificado (26986) e a tolerância padrão (0,5). Para saber mais, consulte CreateTopology na documentação do PostGIS.


SELECT topology.createtopology('my_new_topo',26986,0.5);
 createtopology
----------------
              1
(1 row)

Etapa 6: Atualize a extensão PostGIS

Cada nova versão do PostgreSQL oferece suporte a uma ou mais versões da extensão PostGIS compatíveis com essa versão. A atualização do mecanismo PostgreSQL para uma nova versão não atualiza automaticamente a extensão PostGIS. Antes de atualizar o mecanismo PostgreSQL, faça upgrade do PostGIS para a versão mais recente disponível para a versão atual do PostgreSQL. Para obter detalhes, consulte Versões de extensão PostGIS.

Após a atualização do mecanismo PostgreSQL, faça upgrade da extensão PostGIS novamente, desta vez para a versão compatível com a versão recém-atualizada do mecanismo PostgreSQL. Para obter mais informações sobre como fazer upgrade do mecanismo PostgreSQL, consulte Testar um upgrade de cluster de banco de dados de produção para uma nova versão principal.

Você pode verificar as atualizações de versão da extensão PostGIS disponíveis no seu cluster de banco de dados do Aurora PostgreSQL a qualquer momento. Para fazer isso, execute o comando a seguir. Esta função está disponível com PostGIS 2.5.0 e versões posteriores.


SELECT postGIS_extensions_upgrade();

Se a sua aplicação não oferecer suporte à versão mais recente do PostGIS, você poderá instalar uma versão mais antiga do PostGIS que esteja disponível na sua versão principal conforme o exposto a seguir.


CREATE EXTENSION postgis VERSION "2.5.5";

Se quiser fazer upgrade para uma versão específica do PostGIS usando uma versão mais antiga, também poderá usar o comando a seguir.


ALTER EXTENSION postgis UPDATE TO "2.5.5";

Dependendo de sua versão atual antes do upgrade, talvez você precise usar essa função novamente. O resultado da primeira execução da função determina a necessidade de uma função de atualização adicional. Por exemplo, isso acontece em caso de upgrade do PostGIS 2 para o PostGIS 3. Para ter mais informações, consulte Upgrade do PostGIS 2 para o PostGIS 3.

Se você atualizou essa extensão para se preparar para uma atualização da versão principal do mecanismo PostgreSQL, poderá continuar com outras tarefas preliminares. Para obter mais informações, consulte Testar um upgrade de cluster de banco de dados de produção para uma nova versão principal.

Versões de extensão PostGIS

Recomendamos que você instale as versões de todas as extensões, como PostGIS, conforme listado em “Extension versions for Aurora PostgreSQL-Compatible Edition” (Versões de extensões para a edição compatível com Aurora PostgreSQL) nas Notas de lançamento do Aurora PostgreSQL. Você pode conferir quais versões estão disponíveis na sua versão usando o comando a seguir.


SELECT * FROM pg_available_extension_versions WHERE name='postgis';

Informações sobre versões estão disponíveis nas seções a seguir das Notas de lançamento do Amazon RDS para PostgreSQL:

Upgrade do PostGIS 2 para o PostGIS 3

A partir da versão 3.0, a funcionalidade de rasterização do PostGIS é uma extensão separada, postgis_raster. Essa extensão tem seu próprio caminho de instalação e upgrade. Isso remove dezenas de funções, tipos de dados e outros artefatos necessários para o processamento de imagens rasterizadas da extensão postgis principal. Isso significa que, se o seu caso de uso não exigir processamento de rasterização, você não precisará instalar a extensão postgis_raster.

No exemplo de upgrade a seguir, o primeiro comando de upgrade extrai a funcionalidade de rasterização na extensão postgis_raster. Um segundo comando de upgrade é necessário para atualizar postgis_raster para a nova versão.

Como fazer upgrade do PostGIS 2 para o PostGIS 3

Identifique a versão padrão do PostGIS que está disponível para a versão do PostgreSQL em seucluster de banco de dados do Aurora PostgreSQL. Para fazer isso, execute a consulta a seguir.


SELECT * FROM pg_available_extensions
    WHERE default_version > installed_version;
  name   | default_version | installed_version |                          comment
---------+-----------------+-------------------+------------------------------------------------------------
 postgis | 3.1.4           | 2.3.7             | PostGIS geometry and geography spatial types and functions
(1 row)

Identifique as versões do PostGIS instaladas em cada banco de dados na instância de leitor do seu cluster de banco de dados do Aurora PostgreSQL. Em outras palavras, consulte cada banco de dados do usuário da seguinte forma.


SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
    AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname LIKE '%postgis%'
ORDER BY
    1;
  Name   | Version | Schema |                             Description
---------+---------+--------+---------------------------------------------------------------------
 postgis | 2.3.7   | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)

Essa divergência entre a versão padrão (PostGIS 3.1.4) e a versão instalada (PostGIS 2.3.7) significa que você precisa atualizar a extensão PostGIS.


ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged

Execute a consulta a seguir para verificar se a funcionalidade de rasterização já está em seu próprio pacote.
```
SELECT
    probin,
    count(*)
FROM
    pg_proc
WHERE
    probin LIKE '%postgis%'
GROUP BY
    probin;
          probin          | count
--------------------------+-------
 $libdir/rtpostgis-2.3    | 107
 $libdir/postgis-3        | 487
(2 rows)
```
O resultado mostra que ainda há uma diferença entre as versões. As funções do PostGIS são da versão 3 (postgis-3), enquanto as funções de rasterização (rtpostgis) são da versão 2 (rtpostgis-2.3). Para concluir a atualização, execute o comando de upgrade novamente, da seguinte forma.
```
postgres=> SELECT postgis_extensions_upgrade();
```
Você pode ignorar as mensagens de aviso. Execute a consulta a seguir novamente para verificar se a atualização foi concluída. A atualização é concluída quando o PostGIS e todas as extensões relacionadas deixam de estar sinalizadas como necessitando de atualização.
```
SELECT postgis_full_version();
```

Use a consulta a seguir para ver o processo de atualização concluído e as extensões empacotadas separadamente, e verifique se as versões correspondem.


SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
        AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname LIKE '%postgis%'
ORDER BY
    1;
      Name      | Version | Schema |                             Description
----------------+---------+--------+---------------------------------------------------------------------
 postgis        | 3.1.5   | public | PostGIS geometry, geography, and raster spatial types and functions
 postgis_raster | 3.1.5   | public | PostGIS raster types and functions
(2 rows)

A saída mostra que a extensão PostGIS 2 foi atualizada para PostGIS 3, e tanto postgis quanto a extensão postgis_raster agora separada estão na versão 3.1.5.

Depois que essa atualização for concluída, se você não planejar usar a funcionalidade de rasterização, poderá descartar a extensão da seguinte forma.


DROP EXTENSION postgis_raster;

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Gerenciar objetos grandes com maior eficiência com o módulo lo

Gerenciar partições com a extensão pg_partman