# Trabalhar com extensões e invólucros de dados externos
Para estender a funcionalidade ao cluster de banco de dados da edição compatível com o Aurora PostgreSQL, você pode instalar e usar várias *extensões* do PostgreSQL. Por exemplo, se seu caso de uso exigir muitas entradas de dados em tabelas muito grandes, você poderá instalar a extensão `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` para particionar os dados e, assim, distribuir a workload.
**nota**
A partir do Aurora PostgreSQL 14.5, o Aurora PostgreSQL é compatível com extensões de linguagem confiáveis para PostgreSQL. Esse recurso é implementado como a extensão `pg_tle`, que você pode adicionar ao seu Aurora PostgreSQL. Ao usar essa extensão, os desenvolvedores podem criar suas próprias extensões do PostgreSQL em um ambiente seguro que simplifica os requisitos de instalação e configuração, bem como grande parte dos testes preliminares de novas extensões. Para obter mais informações, consulte [Trabalhar com Trusted Language Extensions para PostgreSQL](PostgreSQL_trusted_language_extension.md).
Em alguns casos, em vez de instalar uma extensão, você pode adicionar um *módulo* específico à lista de `shared_preload_libraries` no grupo de parâmetros do cluster de banco de dados personalizado de seu cluster de banco de dados do Aurora PostgreSQL. Normalmente, o grupo de parâmetros padrão do cluster de banco de dados carrega somente as `pg_stat_statements`, mas vários outros módulos estão disponíveis para serem adicionados à lista. Por exemplo, você pode incluir a capacidade de agendamento adicionando o módulo `pg_cron`, conforme detalhado em [Agendar manutenção com a extensão pg\$1cron do PostgreSQL](PostgreSQL_pg_cron.md). Como outro exemplo, você pode registrar planos de execução de consultas carregando o módulo `auto_explain`. Para saber mais, consulte [Logging execution plans of queries](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#) (Registrar em log planos de execução de consultas) no Centro de Conhecimentos da AWS.
Uma extensão que fornece acesso a dados externos é conhecida mais especificamente como um *invólucro de dados externos* (FDW). Por exemplo, a extensão `oracle_fdw` permite que o cluster de banco de dados do Aurora PostgreSQL funcione com bancos de dados Oracle.
Também é possível especificar com precisão quais extensões podem ser instaladas na instância de banco de dados do Aurora PostgreSQL, listando-as no parâmetro `rds.allowed_extensions`. Para obter mais informações, consulte [Restringir a instalação de extensões do PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction.html).
A seguir, você encontra informações sobre como configurar e usar algumas das extensões, módulos e FDWs disponíveis para o Aurora PostgreSQL. Por uma questão de simplicidade, todas elas são chamadas de “extensões”. Para encontrar listas das extensões que você pode usar com as versões do Aurora PostgreSQL atualmente disponíveis, consulte [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) (Versões de extensão para o Amazon Aurora PostgreSQL) nas *Release Notes for Aurora PostgreSQL* (Notas de versão do Aurora PostgreSQL).
+ [Gerenciar objetos grandes com o módulo lo](PostgreSQL_large_objects_lo_extension.md)
+ [Gerenciar dados espaciais com a extensão PostGIS](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [Gerenciar partições do PostgreSQL com a extensão pg\$1partman](PostgreSQL_Partitions.md)
+ [Agendar manutenção com a extensão pg\$1cron do PostgreSQL](PostgreSQL_pg_cron.md)
+ [Usar pgAudit para registrar a atividade do banco de dados](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [Usar pglogical para sincronizar dados entre instâncias](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [Trabalhar com um banco de dados Oracle usando a extensão oracle\$1fdw](postgresql-oracle-fdw.md)
+ [Trabalhar com bancos de dados do SQL Server usando a extensão tds\$1fdw](postgresql-tds-fdw.md)
# Usar o suporte de extensão delegado do Amazon Aurora para PostgreSQL
Usando o suporte de extensão delegado do Amazon Aurora para PostgreSQL, é possível delegar o gerenciamento de extensões a um usuário que não precisa ser um `rds_superuser`. Com esse suporte de extensão delegado, um novo perfil chamado `rds_extension` é criado, e você deve atribuí-lo a um usuário para gerenciar outras extensões. Esse perfil pode criar, atualizar e eliminar extensões.
É possível especificar quais extensões podem ser instaladas na instância de banco de dados do Aurora PostgreSQL, listando-as no parâmetro `rds.allowed_extensions`. Para obter mais informações, consulte [Usar extensões PostgreSQL com o Amazon RDS para PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html).
É possível restringir a lista de extensões disponíveis que podem ser gerenciadas pelo usuário com o perfil `rds_extension` usando o parâmetro `rds.allowed_delegated_extensions`.
O suporte de extensão delegado está disponível nas seguintes versões:
+ Todas as versões posteriores
+ 15.5 e versões 15 posteriores
+ 14.10 e versões 14 posteriores
+ 13.13 e versões 13 posteriores
+ 12.17 e versões 12 posteriores
**Topics**
+ [Ativar o suporte de extensão delegado a um usuário](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [Configuração usada no suporte de extensão delegado do Aurora para PostgreSQL](#AuroraPostgreSQL.delegated_ext_config)
+ [Desativar o suporte para a extensão delegada](#AuroraPostgreSQL.delegated_ext_disable)
+ [Benefícios de usar o suporte de extensão delegado do Amazon Aurora](#AuroraPostgreSQL.delegated_ext_benefits)
+ [Limitação do suporte de extensão delegado do Aurora para PostgreSQL](#AuroraPostgreSQL.delegated_ext_limit)
+ [Permissões necessárias para determinadas extensões](#AuroraPostgreSQL.delegated_ext_perm)
+ [Considerações sobre segurança](#AuroraPostgreSQL.delegated_ext_sec)
+ [Descartar cascata de extensão desabilitado](#AuroraPostgreSQL.delegated_ext_drop)
+ [Exemplos de extensões que podem ser adicionadas usando o suporte de extensão delegado](#AuroraPostgreSQL.delegated_ext_support)
## Ativar o suporte de extensão delegado a um usuário
É necessário executar o seguinte para habilitar o suporte de extensão delegado a um usuário:
1. **Conceder o perfil `rds_extension` a um usuário**: conecte-se ao banco de dados `rds_superuser` e execute o seguinte comando:
```
Postgres => grant rds_extension to user_name;
```
1. **Definir a lista de extensões disponíveis para usuários delegados gerenciarem**: o `rds.allowed_delegated_extensions` permite que você especifique um subconjunto das extensões disponíveis usando o parâmetro de cluster de banco de dados `rds.allowed_extensions`. É possível fazer isso em um dos seguintes níveis:
+ No cluster ou no grupo de parâmetros da instância, por meio do Console de gerenciamento da AWS ou da API. Para obter mais informações, consulte [Grupos de parâmetros para Amazon Aurora](USER_WorkingWithParamGroups.md).
+ Use o seguinte comando em nível de banco de dados:
```
alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
+ Use o seguinte comando em nível de usuário:
```
alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
**nota**
Não é necessário reiniciar o banco de dados depois de alterar o parâmetro dinâmico `rds.allowed_delegated_extensions`.
1. **Permitir que o usuário delegado acesse os objetos criados durante o processo de criação da extensão**: determinadas extensões criam objetos que exigem que permissões adicionais sejam concedidas para que o usuário com o perfil `rds_extension` possa acessá-los. O `rds_superuser` deve conceder ao usuário delegado acesso a esses objetos. Uma das opções é usar um gatilho de eventos para conceder permissão automaticamente ao usuário delegado.
**Exemplo de gatilho de eventos**
Se quiser permitir que um usuário delegado com `rds_extension` use extensões que exijam permissões de configuração nos objetos criados pela criação da extensão, você poderá personalizar o exemplo abaixo de um gatilho de eventos e adicionar somente as extensões para as quais você deseja que os usuários delegados tenham acesso à funcionalidade completa. Esse gatilho de eventos pode ser criado no modelo 1 (o modelo padrão), portanto, todo banco de dados criado a partir do modelo 1 terá esse gatilho de eventos. Quando um usuário delegado instalar a extensão, esse gatilho concederá automaticamente a propriedade dos objetos criados pela extensão.
```
CREATE OR REPLACE FUNCTION create_ext()
RETURNS event_trigger AS $$
DECLARE
schemaname TEXT;
databaseowner TEXT;
r RECORD;
BEGIN
IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN
RAISE NOTICE 'SECURITY INVOKER';
RAISE NOTICE 'user: %', current_user;
FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands()
LOOP
CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension';
schemaname = (
SELECT n.nspname
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_namespace AS n
ON e.extnamespace = n.oid
WHERE e.oid = r.objid
);
databaseowner = (
SELECT pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d
WHERE d.datname = current_database()
);
RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner;
IF r.object_identity = 'address_standardizer_data_us' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'dict_int' THEN
EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner);
ELSIF r.object_identity = 'pg_partman' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'postgis_topology' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext();
```
## Configuração usada no suporte de extensão delegado do Aurora para PostgreSQL
| Nome da configuração | Descrição | Valor padrão | Observações | Quem pode modificar ou conceder permissão |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | Esse parâmetro limita as extensões que um perfil rds\$1extension pode gerenciar em um banco de dados. Ele deve ser um subconjunto de rds.allowed\$1extensions. | string vazia | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/Aurora_delegated_ext.html) Para saber mais sobre como configurar esse parâmetro, consulte [Ativar o suporte de extensão delegado a um usuário](#AuroraPostgreSQL.delegated_ext_mgmt). | rds\$1superuser |
| `rds.allowed_extensions` | Esse parâmetro permite que o cliente limite as extensões que podem ser instaladas na instância de banco de dados do Aurora PostgreSQL. Para ter mais informações, consulte [Restringir a instalação de extensões do PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction). | "\$1" | Por padrão, esse parâmetro é definido como “\$1”, o que significa que todas as extensões aceitas no RDS para PostgreSQL e no Aurora PostgreSQL podem ser criadas por usuários com os privilégios necessários. Vazio significa que nenhuma extensão pode ser instalada na instância de banco de dados do Aurora PostgreSQL. | administrador |
| `rds-delegated_extension_allow_drop_cascade` | Esse parâmetro controla a capacidade do usuário com `rds_extension` de eliminar a extensão usando uma opção em cascata. | desligar | Por padrão, `rds-delegated_extension_allow_drop_cascade` é definido como `off`. Isso significa que os usuários com `rds_extension` não têm permissão para descartar uma extensão usando a opção em cascata. Para conceder essa capacidade, o parâmetro `rds.delegated_extension_allow_drop_cascade` deve ser definido como `on`. | rds\$1superuser |
## Desativar o suporte para a extensão delegada
**Desativar parcialmente**
Os usuários delegados não podem criar extensões, mas ainda podem atualizar as existentes.
+ Redefina `rds.allowed_delegated_extensions` como o valor padrão no grupo de parâmetros de cluster de banco de dados.
+ Use o seguinte comando em nível de banco de dados:
```
alter database database_name reset rds.allowed_delegated_extensions;
```
+ Use o seguinte comando em nível de usuário:
```
alter user user_name reset rds.allowed_delegated_extensions;
```
**Desativar totalmente**
Revogar o perfil `rds_extension` de um usuário vai restaurar as permissões padrão do usuário. O usuário não pode mais criar, atualizar nem descartar extensões.
```
postgres => revoke rds_extension from user_name;
```
## Benefícios de usar o suporte de extensão delegado do Amazon Aurora
Usando o suporte de extensão delegado do Amazon Aurora para PostgreSQL, é possível delegar, com segurança, o gerenciamento de extensões a usuários que não têm o perfil `rds_superuser`. Esse recurso oferece os seguintes benefícios:
+ É possível delegar facilmente o gerenciamento de extensões aos usuários de sua escolha.
+ Isso não exige o perfil `rds_superuser`.
+ Oferece a capacidade de oferecer compatibilidade com um conjunto diferente de extensões para bancos de dados diferentes no mesmo cluster de banco de dados.
## Limitação do suporte de extensão delegado do Aurora para PostgreSQL
+ Objetos criados durante o processo de criação da extensão podem exigir privilégios adicionais para que a extensão funcione corretamente.
## Permissões necessárias para determinadas extensões
Para criar, usar ou atualizar as extensões a seguir, o usuário delegado deve ter os privilégios necessários nestas funções, tabelas e esquemas.
| Extensões que precisam de propriedade ou permissões | Função | Tabelas | Schema | Dicionário de pesquisa de texto | Comment |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | | us\$1gaz, us\$1lex, us\$1lex, I.us\$1rules | | | |
| amcheck | bt\$1index\$1check, bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions, part\$1config, part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | topologia, camada | topologia | | o usuário delegado deve ser o proprietário do banco de dados |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default, geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## Considerações sobre segurança
Lembre-se de que um usuário com o perfil `rds_extension` poderá gerenciar extensões em todos os bancos de dados nos quais tiver o privilégio de conexão. Se a intenção for fazer com que um usuário delegado gerencie a extensão em um único banco de dados, uma prática recomendada é revogar todos os privilégios públicos em cada banco de dados e, depois, conceder explicitamente o privilégio de conexão desse banco de dados específico ao usuário delegado.
Existem várias extensões que podem permitir que um usuário acesse informações de vários bancos de dados. Garanta que os usuários que receberem `rds_extension` tenham recursos de vários bancos de dados antes de adicionar essas extensões a `rds.allowed_delegated_extensions`. Por exemplo, `postgres_fdw` e `dblink` oferecem a capacidade de consultar vários bancos de dados na mesma instância ou em instâncias remotas. `log_fdw` lê os arquivos de log do mecanismo postgres, relacionados a todos os bancos de dados na instância, possivelmente contendo consultas lentas ou mensagens de erro de vários bancos de dados. `pg_cron` permite a execução de trabalhos agendados em segundo plano na instância de banco de dados e pode configurar trabalhos para execução em um banco de dados diferente.
## Descartar cascata de extensão desabilitado
A capacidade de descartar a extensão com a opção em cascata por um usuário com o perfil `rds_extension` é controlada pelo parâmetro `rds.delegated_extension_allow_drop_cascade`. Por padrão, `rds-delegated_extension_allow_drop_cascade` é definido como `off`. Isso significa que os usuários com o perfil `rds_extension` não têm permissão para descartar uma extensão usando a opção em cascata, conforme mostrado na consulta a seguir.
```
DROP EXTENSION CASCADE;
```
Pois isso descartará automaticamente os objetos que dependem da extensão e, por sua vez, todos os objetos que dependem desses objetos. A tentativa de usar a opção em cascata gerará um erro.
Para conceder essa capacidade, o parâmetro `rds.delegated_extension_allow_drop_cascade` deve ser definido como `on`.
Alterar o parâmetro dinâmico `rds.delegated_extension_allow_drop_cascade` não requer a reinicialização do banco de dados. É possível fazer isso em um dos seguintes níveis:
+ No cluster ou no grupo de parâmetros da instância, por meio do Console de gerenciamento da AWS ou da API.
+ Usar o seguinte comando em nível de banco de dados:
```
alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on';
```
+ Usar o seguinte comando em nível de usuário:
```
alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on';
```
## Exemplos de extensões que podem ser adicionadas usando o suporte de extensão delegado
+ `rds_tools`
```
extension_test_db=> create extension rds_tools;
CREATE EXTENSION
extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files';
ERROR: permission denied for function role_password_encryption_type
```
+ `amcheck`
```
extension_test_db=> CREATE TABLE amcheck_test (id int);
CREATE TABLE
extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000));
INSERT 0 100000
extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id);
CREATE INDEX
extension_test_db=> create extension amcheck;
CREATE EXTENSION
extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_check
extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_parent_check
```
+ `pg_freespacemap`
```
extension_test_db=> create extension pg_freespacemap;
CREATE EXTENSION
extension_test_db=> SELECT * FROM pg_freespace('pg_authid');
ERROR: permission denied for function pg_freespace
extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0);
ERROR: permission denied for function pg_freespace
```
+ `pg_visibility`
```
extension_test_db=> create extension pg_visibility;
CREATE EXTENSION
extension_test_db=> select * from pg_visibility('pg_database'::regclass);
ERROR: permission denied for function pg_visibility
```
+ `postgres_fdw`
```
extension_test_db=> create extension postgres_fdw;
CREATE EXTENSION
extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432');
ERROR: permission denied for foreign-data wrapper postgres_fdw
```
# Gerenciar objetos grandes com o módulo lo
O módulo lo (extensão) é para usuários de banco de dados e desenvolvedores que trabalham com bancos de dados PostgreSQL por meio de drivers JDBC ou ODBC. Tanto no caso do JDBC quanto no caso do ODBC, é esperado que o banco de dados processe a exclusão de objetos grandes quando as referências a eles mudam. No entanto, o PostgreSQL não funciona dessa maneira. No PostgreSQL, não se espera que um objeto seja excluído quando sua referência é alterada. O resultado é que os objetos permanecem no disco, sem referência. A extensão lo inclui uma função usada para acionar alterações de referência para excluir objetos, se necessário.
**dica**
Para determinar se seu banco de dados pode se beneficiar da extensão lo, use o utilitário `vacuumlo` para verificar se há objetos grandes órfãos. Para obter contagens de objetos grandes órfãos sem realizar nenhuma ação, execute o utilitário com a opção `-n` (no-op). Para saber como, consulte [vacuumlo utility](#vacuumlo-utility) a seguir.
O módulo lo está disponível para Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 e versões secundárias superiores.
Para instalar o módulo (extensão), você precisa de privilégios `rds_superuser`. Instalar a extensão lo adiciona o seguinte ao seu banco de dados:
+ `lo`: é um tipo de dados de objeto grande (lo) que você pode usar para objetos grandes binários (BLOBs) e outros objetos grandes. O tipo de dados `lo` é um domínio do tipo de dados `oid`. Em outras palavras, é um identificador de objeto com restrições opcionais. Para saber mais, consulte [Identificadores de objeto](https://www.postgresql.org/docs/14/datatype-oid.html) na documentação do PostgreSQL. Em termos simples, você pode usar o tipo de dados `lo` para distinguir suas colunas de banco de dados que contêm referências de objetos grandes de outros identificadores de objeto (OIDs).
+ `lo_manage`: é uma função que você pode usar em gatilhos em colunas de tabela que contêm referências a objetos grandes. Sempre que você excluir ou modificar um valor que faça referência a um objeto grande, o gatilho desvincula o objeto (`lo_unlink`) de sua referência. Use o gatilho em uma coluna somente se a coluna for a única referência de banco de dados ao objeto grande.
Para obter mais informações sobre o módulo de objetos grandes, consulte [lo](https://www.postgresql.org/docs/current/lo.html) na documentação do PostgreSQL.
## Instalar a extensão lo
Antes de instalar a extensão lo, verifique se você tem privilégios `rds_superuser`.
**Como instalar a extensão lo**
1. Use o `psql` para conectar-se à instância de banco de dados primária do cluster de banco de dados Aurora PostgreSQL.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Insira sua senha quando for solicitado. O cliente `psql` conecta-se e exibe o banco de dados de conexão administrativa padrão, `postgres=>`, como o prompt.
1. Instale a extensão da forma a seguir.
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
Agora é possível usar o tipo de dados `lo` para definir colunas em suas tabelas. Por exemplo, você pode criar uma tabela (`images`) que contém dados de imagem raster. Você pode usar o tipo de dados `lo` para uma coluna `raster`, conforme mostrado no exemplo a seguir, que cria uma tabela.
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## Usar a função de gatilho lo\$1manage para excluir objetos
É possível usar a função `lo_manage` em um gatilho em uma `lo` ou outras colunas de objetos grandes para limpar (e evitar objetos órfãos) quando a `lo` é atualizada ou excluída.
**Como configurar gatilhos em colunas que fazem referência a objetos grandes**
+ Execute um destes procedimentos:
+ Crie um gatilho BEFORE UPDATE OR DELETE em cada coluna para conter referências exclusivas a objetos grandes, usando o nome da coluna como argumento.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ Aplique um gatilho somente quando a coluna estiver sendo atualizada.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
A função de gatilho `lo_manage` funciona apenas no contexto de inserção ou exclusão de dados de colunas, dependendo de como você define o gatilho. Isso não tem efeito quando você executa uma operação `DROP` ou `TRUNCATE`em um banco de dados. Isso significa que é necessário excluir colunas de objeto de qualquer tabela antes de soltá-las, para evitar a criação de objetos órfãos.
Por exemplo, suponha que você queira descartar o banco de dados que contém a tabela `images`. Exclua a coluna da maneira a seguir.
```
postgres=> DELETE FROM images COLUMN raster
```
Supondo que a função `lo_manage` seja definida nessa coluna para lidar com exclusões, agora você pode descartar a tabela com segurança.
## Remoção de objetos grandes órfãos usando o `vacuumlo`
O utilitário `vacuumlo` identifica e remove objetos grandes órfãos dos bancos de dados. Esse utilitário está disponível desde o PostgreSQL 9.1.24. Se os usuários do banco de dados trabalham rotineiramente com objetos grandes, recomendamos executar o `vacuumlo` ocasionalmente para limpar objetos grandes órfãos.
Antes de instalar a extensão lo, você pode usar o `vacuumlo` para avaliar se o cluster de banco de dados do Aurora PostgreSQL pode se beneficiar. Para isso, execute `vacuumlo` com a opção `-n` (no-op) para mostrar o que seria removido, conforme mostrado no seguinte:
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
Como mostra a saída, objetos grandes órfãos não são um problema para esse banco de dados específico.
Para obter mais informações sobre esse utilitário, consulte [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html) na documentação do PostgreSQL.
## Como funciona o `vacuumlo`
O comando `vacuumlo` remove objetos grandes órfãos (LOs) do seu banco de dados PostgreSQL sem afetar ou entrar em conflito com suas tabelas de usuário.
O comando funciona assim:
1. `vacuumlo` começa criando uma tabela temporária contendo todos os IDs de objeto (OIDs) dos objetos grandes em seu banco de dados.
1. Depois, o `vacuumlo` examina todas as colunas do banco de dados que usam os tipos de dados `oid` ou `lo`. Se o `vacuumlo` encontrar um OID correspondente nessas colunas, ele removerá o OID da tabela temporária. O `vacuumlo` verifica somente colunas com os nomes específicos `oid` ou `lo`, não domínios com base nesses tipos.
1. As entradas restantes na tabela temporária representam LOs órfãos, que o `vacuumlo` remove com segurança.
## Aprimorar a performance do `vacuumlo`
Você pode melhorar o desempenho do `vacuumlo` aumentando o tamanho do lote usando a opção `-l`. Isso permite que o `vacuumlo` processe mais LOs de uma só vez.
Se o sistema tiver memória suficiente e você puder acomodar a tabela temporária completamente na memória, aumentar a configuração `temp_buffers` no nível do banco de dados poderá melhorar o desempenho. Isso permite que a tabela resida inteiramente na memória, o que pode melhorar o desempenho geral.
A consulta a seguir estima o tamanho da tabela temporária:
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## Considerações para objetos grandes
A seguir, você encontrará algumas considerações importantes a serem observadas ao trabalhar com objetos grandes:
+ `Vacuumlo` é a única solução, pois atualmente não há outro método para remover LOs órfãos.
+ Ferramentas como pglogical, replicação lógica nativa e AWS DMS que usam tecnologias de replicação não oferecem suporte à replicação de objetos grandes.
+ Ao projetar seu esquema de banco de dados, evite usar objetos grandes sempre que possível e considere usar tipos de dados alternativos, como `bytea`.
+ Execute o `vacuumlo` regularmente, pelo menos semanalmente, para evitar problemas com LOs órfãos.
+ Use um gatilho com a função `lo_manage` em tabelas que armazenam objetos grandes para ajudar a evitar a criação de LOs órfãos.
# 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](https://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.
**Topics**
+ [Etapa 1: Criar um usuário (função) para gerenciar a extensão PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [Etapa 2: Carregar as extensões PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [Etapa 3: Transferir a propriedade dos esquemas de extensão](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [Etapa 4: Transferir a propriedade dos objetos PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [Etapa 5: Testar as extensões](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [Etapa 6: Atualize a extensão PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [Versões de extensão PostGIS](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [Upgrade do PostGIS 2 para o PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-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](https://postgis.net/workshops/postgis-intro/security.html).
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](https://postgis.net/docs/Extras.html#Tiger_Geocoder) 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\$1new\$1topo) com o identificador de referência espacial especificado (26986) e a tolerância padrão (0,5). Para saber mais, consulte [CreateTopology](https://postgis.net/docs/CreateTopology.html) 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](#CHAP_PostgreSQL.Extensions.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](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
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 obter mais informações, consulte [Upgrade do PostGIS 2 para o PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-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](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
## 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”](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) (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*:
+ [Versões de extensões para o Aurora PostgreSQL 14](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Versões de extensões para a edição do Aurora compatível com PostgreSQL 13](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Versões de extensões para a edição do Aurora compatível com PostgreSQL 12](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Versões de extensões para a edição do Aurora compatível com PostgreSQL 11](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Versões de extensões para a edição do Aurora compatível com PostgreSQL 10](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Versões de extensões para a edição do Aurora compatível com PostgreSQL 9.6](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## 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**
1. 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)
```
1. 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
```
1. 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();
```
1. 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;
```
# Gerenciar partições do PostgreSQL com a extensão pg\$1partman
O particionamento de tabelas PostgreSQL fornece um framework para processamento de alta performance de entrada e relatórios de dados. Use o particionamento para bancos de dados que exigem entrada muito rápida de grandes quantidades de dados. O particionamento também fornece consultas mais rápidas de tabelas grandes. O particionamento ajuda a manter os dados sem afetar a instância do banco de dados, pois requer menos recursos de E/S.
Ao usar o particionamento, você pode dividir dados em blocos de tamanho personalizado para processamento. Por exemplo, você pode particionar dados de séries temporais para intervalos como por hora, diário, semanal, mensal, trimestral, anual, personalizado ou qualquer combinação destes. Para um exemplo de dados de séries temporais, se você particionar a tabela por hora, cada partição conterá uma hora de dados. Se você particionar a tabela de séries temporais por dia, as partições manterão dados de um dia e assim por diante. A chave de partição controla o tamanho de uma partição.
Quando você usa um comando SQL `INSERT` ou `UPDATE` em uma tabela particionada, o mecanismo de banco de dados roteia os dados para a partição apropriada. As partições de tabela PostgreSQL que armazenam os dados são tabelas filhas da tabela principal.
Durante as leituras de consulta de banco de dados, o otimizador PostgreSQL examina a cláusula `WHERE` da consulta e, se possível, direciona a verificação do banco de dados apenas para as partições relevantes.
A partir da versão 10, o PostgreSQL usa o particionamento declarativo para implementar o particionamento de tabela. Isso também é conhecido como particionamento nativo do PostgreSQL. Antes do PostgreSQL versão 10, você usou gatilhos para implementar partições.
O particionamento de tabelas PostgreSQL fornece os seguintes recursos:
+ Criação de novas partições a qualquer momento.
+ Intervalos variáveis de partição.
+ Partições destacáveis e reanexáveis usando instruções DDL (Data Definition Language, linguagem de definição de dados).
Por exemplo, partições destacáveis são úteis para remover dados históricos da partição principal, mas manter dados históricos para análise.
+ Novas partições herdam as propriedades da tabela do banco de dados pai, incluindo:
+ Índices
+ Chaves primárias, que devem incluir a coluna de chave de partição
+ Chaves externas
+ Restrições de verificação
+ Referências
+ Criação de índices para a tabela completa ou cada partição específica.
Você não pode alterar o esquema de uma partição individual. No entanto, você pode fazer uma alteração na tabela pai (adicionando uma nova coluna, por exemplo) que se propaga para as partições.
**Topics**
+ [Visão geral da extensão pg\$1partman do PostgreSQL](#PostgreSQL_Partitions.pg_partman)
+ [Ativar a extensão pg\$1partman](#PostgreSQL_Partitions.enable)
+ [Configurar partições usando a função create\$1parent](#PostgreSQL_Partitions.create_parent)
+ [Configurar a manutenção da partição usando a função run\$1maintenance\$1proc](#PostgreSQL_Partitions.run_maintenance_proc)
## Visão geral da extensão pg\$1partman do PostgreSQL
Você pode usar a extensão `pg_partman` do PostgreSQL para automatizar a criação e a manutenção de partições de tabelas. Para obter mais informações gerais, consulte [PG Partition Manager](https://github.com/pgpartman/pg_partman) na documentação `pg_partman`.
**nota**
A extensão `pg_partman` é compatível com o Aurora PostgreSQL versões 12.6 e posteriores.
Em vez de ter que criar manualmente cada partição, você ajusta o `pg_partman` com as seguintes configurações:
+ Tabela a ser particionada
+ Tipo de partição
+ Chave de partição
+ Granularidade de partição
+ Opções de pré-criação e gerenciamento de partições
Depois de criar uma tabela particionada do PostgreSQL, registre-a com `pg_partman` chamando a função `create_parent`. Fazer isso cria as partições necessárias com base nos parâmetros que você passa para a função.
A extensão `pg_partman` também fornece a função `run_maintenance_proc`, que você pode chamar de maneira programada para gerenciar as partições automaticamente. Para garantir que as partições adequadas sejam criadas conforme necessário, agende essa função para ser executada periodicamente (por hora, por exemplo). Você também pode garantir que as partições sejam descartadas automaticamente.
## Ativar a extensão pg\$1partman
Se você tiver vários bancos de dados dentro da mesma instância de banco de dados PostgreSQL para a qual deseja gerenciar partições, habilite a extensão `pg_partman` separadamente para cada banco de dados. Para habilitar a extensão `pg_partman` para um banco de dados específico, crie o esquema de manutenção de partição e crie a extensão `pg_partman` da maneira a seguir.
```
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
```
**nota**
Para criar a extensão `pg_partman`, certifique-se de que você tenha privilégios `rds_superuser`.
Se você receber um erro como o seguinte, conceda os privilégios de `rds_superuser` à conta ou use sua conta de superusuário.
```
ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.
```
Para conceder privilégios de `rds_superuser`, conecte-se à sua conta de superusuário e execute o seguinte comando.
```
GRANT rds_superuser TO user-or-role;
```
Para os exemplos que mostram usando a extensão pg\$1partman, usamos a seguinte tabela de banco de dados de amostra e partição. Esse banco de dados usa uma tabela particionada com base em um carimbo de data/hora. Um esquema `data_mart` contém uma tabela chamada `events` com uma coluna chamada `created_at`. As seguintes configurações estão incluídas na tabela `events`:
+ Chaves primárias `event_id` e `created_at`, que devem ter a coluna usada para orientar a partição.
+ Uma restrição de verificação `ck_valid_operation` para impor valores para uma coluna `operation` da tabela.
+ Duas chaves estrangeiras, onde uma (`fk_orga_membership)` aponta para a tabela externa `organization` e a outra (`fk_parent_event_id`) é uma chave estrangeira autorreferenciada.
+ Dois índices, onde um (`idx_org_id`) é para a chave estrangeira e o outro (`idx_event_type`) é para o tipo de evento.
As declarações DDL a seguir criam esses objetos, que serão incluídos automaticamente em cada partição.
```
CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
org_name TEXT,
CONSTRAINT pk_organization PRIMARY KEY (org_id)
);
CREATE TABLE data_mart.events(
event_id BIGSERIAL,
operation CHAR(1),
value FLOAT(24),
parent_event_id BIGINT,
event_type VARCHAR(25),
org_id BIGSERIAL,
created_at timestamp,
CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at),
CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'),
CONSTRAINT fk_orga_membership
FOREIGN KEY(org_id)
REFERENCES data_mart.organization (org_id),
CONSTRAINT fk_parent_event_id
FOREIGN KEY(parent_event_id, created_at)
REFERENCES data_mart.events (event_id,created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX idx_org_id ON data_mart.events(org_id);
CREATE INDEX idx_event_type ON data_mart.events(event_type);
```
## Configurar partições usando a função create\$1parent
Depois de habilitar a extensão `pg_partman`, use a função `create_parent` para configurar partições dentro do esquema de manutenção de partição. Aqui é usado o exemplo de tabela `events` criado em [Ativar a extensão pg\$1partmanConfigurar a manutenção da partição usando a função run\$1maintenance\$1proc](#PostgreSQL_Partitions.enable). Chame a função `create_parent` da seguinte forma:
```
SELECT partman.create_parent(
p_parent_table => 'data_mart.events',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 day',
p_premake => 30);
```
Os parâmetros são os seguintes:
+ `p_parent_table` – a tabela particionada pai. Essa tabela já deve existir e estar totalmente qualificada, incluindo o esquema.
+ `p_control` – a coluna na qual o particionamento deve ser baseado. O tipo de dados deve ser um inteiro ou baseado em tempo.
+ `p_type`: o tipo é `'range'` ou `'list'`.
+ `p_interval` – o intervalo ou a faixa de inteiros para cada partição. Os valores de exemplo incluem `1 day`, `1 hour` e assim por diante.
+ `p_premake` – o número de partições para criar antecipadamente a fim de dar suporte a novas inserções.
Para obter uma descrição completa da função `create_parent`, consulte [Funções de criação](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions) na documentação do `pg_partman`..
## Configurar a manutenção da partição usando a função run\$1maintenance\$1proc
Você pode executar operações de manutenção de partição para criar automaticamente novas partições, desanexar partições ou remover partições antigas. A manutenção da partição depende da função `run_maintenance_proc` da extesão `pg_partman` e da extensão `pg_cron`, que inicia um programador interno. O agendador `pg_cron` executa automaticamente instruções SQL, funções e procedimentos definidos em seus bancos de dados.
A seguir, será usado o exemplo de tabela `events` criado em [Ativar a extensão pg\$1partmanConfigurar a manutenção da partição usando a função run\$1maintenance\$1proc](#PostgreSQL_Partitions.enable) para definir que as operações de manutenção de partição serão executadas automaticamente. Como pré-requisito, adicione `pg_cron` ao parâmetro `shared_preload_libraries` no grupo de parâmetros da instância de banco de dados.
```
CREATE EXTENSION pg_cron;
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
```
A seguir, você pode encontrar uma explicação detalhada do exemplo anterior:
1. Modifique o grupo de parâmetros associado à sua instância de banco de dados e adicione `pg_cron` ao valor do parâmetro `shared_preload_libraries`. Essa alteração exige a reinicialização da instância de banco de dados para que tenha efeito. Para obter mais informações, consulte [Modificar parâmetros em um grupo de parâmetros de banco de dados no Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
1. Execute o comando `CREATE EXTENSION pg_cron;` usando uma conta que tenha as permissões de `rds_superuser`. Isso habilita a extensão `pg_cron`. Para obter mais informações, consulte [Agendar manutenção com a extensão pg\$1cron do PostgreSQL](PostgreSQL_pg_cron.md).
1. Execute o comando `UPDATE partman.part_config` para ajustar as configurações `pg_partman` para a tabela `data_mart.events`.
1. Execute o comando `SET` . . . para configurar a tabela `data_mart.events`, com estas cláusulas:
1. `infinite_time_partitions = true,` – configura a tabela para poder criar novas partições automaticamente, sem qualquer limite.
1. `retention = '3 months',` – configura a tabela para ter uma retenção máxima de três meses.
1. `retention_keep_table=true `– Configura a tabela para que, quando o período de retenção for devido, a tabela não seja excluída automaticamente. Em vez disso, as partições que são mais antigas do que o período de retenção são apenas separadas da tabela pai.
1. Execute o comando `SELECT cron.schedule` . . . para fazer uma chamada da função `pg_cron`. Esta chamada define com que frequência o programador executa o procedimento de manutenção `pg_partman`, `partman.run_maintenance_proc`. Para este exemplo, o procedimento é executado a cada hora.
Para obter uma descrição completa da função `run_maintenance_proc`, consulte [Funções de manutenção](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions) na documentação do `pg_partman`.
# Agendar manutenção com a extensão pg\$1cron do PostgreSQL
Você pode utilizar a extensão `pg_cron` do PostgreSQL para programar comandos de manutenção dentro de um banco de dados do PostgreSQL. Para obter mais informações sobre a extensão, consulte [O que é pg\$1cron?](https://github.com/citusdata/pg_cron) na documentação do pg\$1cron.
A extensão `pg_cron` é compatível com o mecanismo do Aurora PostgreSQL versões 12.6 e posteriores
Para saber mais sobre como usar `pg_cron`, consulte [Programar trabalhos com pg\$1cron em bancos de dados do RDS para PostgreSQL ou compatíveis com o Aurora PostgreSQL](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/).
**nota**
A versão da extensão `pg_cron` é exibida como uma versão de dois dígitos, por exemplo, 1.6, na visualização pg\$1available\$1extensions. Embora você possa ver versões de três dígitos, por exemplo, 1.6.4 ou 1.6.5, listadas em alguns contextos, você deve especificar a versão de dois dígitos ao realizar uma atualização de extensão.
**Topics**
+ [Configurar a extensão pg\$1cron](#PostgreSQL_pg_cron.enable)
+ [Conceder permissões de banco de dados para usar pg\$1cron](#PostgreSQL_pg_cron.permissions)
+ [Agendar trabalhos de pg\$1cron](#PostgreSQL_pg_cron.examples)
+ [Referência para a extensão pg\$1cron](#PostgreSQL_pg_cron.reference)
## Configurar a extensão pg\$1cron
Configure a extensão `pg_cron` da seguinte forma:
1. Modifique o grupo de parâmetros personalizado associado à sua instância de banco de dados do PostgreSQL adicionando `pg_cron` ao valor do parâmetro `shared_preload_libraries`.
Reinicie a instância de banco de dados do PostgreSQL para que as alterações no grupo de parâmetros entrem em vigor. Para saber mais sobre como trabalhar com grupos de parâmetros, consulte [Amazon Aurora PostgreSQL parameters](AuroraPostgreSQL.Reference.ParameterGroups.md).
1. Após a reinicialização da instância de banco de dados do PostgreSQL, execute o comando a seguir usando uma conta que tenha permissões `rds_superuser`. Por exemplo, se você usou as configurações padrão ao criar o cluster de banco de dados do Aurora PostgreSQL, conecte-se como o usuário `postgres` e crie a extensão.
```
CREATE EXTENSION pg_cron;
```
O agendador do `pg_cron` é definido no banco de dados PostgreSQL padrão chamado `postgres`. Os objetos `pg_cron` são criados neste banco de dados `postgres` e todas as ações de agendamento são executadas neste banco de dados.
1. Você pode usar as configurações padrão ou programar trabalhos para serem executados em outros bancos de dados dentro de sua instância de banco de dados PostgreSQL. Para programar trabalhos a serem executados em outros bancos de dados em sua instância de banco de dados PostgreSQL, consulte o exemplo em [Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão](#PostgreSQL_pg_cron.otherDB).
## Conceder permissões de banco de dados para usar pg\$1cron
A instalação da extensão `pg_cron` requer privilégios de `rds_superuser`. No entanto, as permissões para usar `pg_cron` podem ser concedidas (por um membro do grupo/perfil de `rds_superuser`) para outros usuários do banco de dados, para que eles possam programar seus próprios trabalhos. Recomendamos que você conceda permissões para o esquema `cron` somente conforme necessário se ele melhorar as operações do ambiente de produção.
Para conceder permissão a um usuário do banco de dados no esquema `cron`, execute o seguinte comando:
```
postgres=> GRANT USAGE ON SCHEMA cron TO db-user;
```
Isso concede a *db-user* permissão para acessar o esquema `cron` para programar trabalhos cron para os objetos que o usuário tem permissão para acessar. Se o usuário do banco de dados não tiver permissões, o trabalho falhará após a publicação da mensagem de erro no arquivo `postgresql.log`, conforme mostrado a seguir:
```
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
```
Em outras palavras, certifique-se de que os usuários do banco de dados que tenham permissões no esquema `cron` também tenham permissões nos objetos (tabelas, esquemas e assim por diante) que planejam programar.
Os detalhes do trabalho cron e seu sucesso ou falha também são capturados na tabela `cron.job_run_details`. Para obter mais informações, consulte [Tabelas para agendar trabalhos e capturar status](#PostgreSQL_pg_cron.tables).
## Agendar trabalhos de pg\$1cron
As seções a seguir mostram como você pode agendar várias tarefas de gerenciamento usando trabalhos `pg_cron`.
**nota**
Ao criar trabalhos `pg_cron`, verifique se a configuração `max_worker_processes` a configuração é maior do que o número de `cron.max_running_jobs`. Um trabalho `pg_cron` falhará se ficar sem processos de operador em segundo plano. O número padrão de trabalhos `pg_cron` é `5`. Para obter mais informações, consulte [Parâmetros para gerenciar a extensão pg\$1cron](#PostgreSQL_pg_cron.parameters).
**Topics**
+ [Vacuum de tabelas](#PostgreSQL_pg_cron.vacuum)
+ [Limpar a tabela de histórico de pg\$1cron](#PostgreSQL_pg_cron.job_run_details)
+ [Registrar em log erros somente no arquivo postgresql.log](#PostgreSQL_pg_cron.log_run)
+ [Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão](#PostgreSQL_pg_cron.otherDB)
### Vacuum de tabelas
O autovacuum lida com manutenção de vacuum para a maioria dos casos. No entanto, você pode agendar o vacuum de uma tabela específica quando quiser.
Veja a seguir um exemplo de uso da função `cron.schedule` para configurar um trabalho a ser usado `VACUUM FREEZE` em uma tabela específica todos os dias às 22:00 (GMT).
```
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
```
Após o exemplo anterior ser executado, você pode verificar o histórico na tabela `cron.job_run_details` da seguinte forma.
```
postgres=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)
```
A seguir está uma consulta à tabela `cron.job_run_details` para ver os trabalhos que falharam.
```
postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)
```
Para obter mais informações, consulte [Tabelas para agendar trabalhos e capturar status](#PostgreSQL_pg_cron.tables).
### Limpar a tabela de histórico de pg\$1cron
A tabela `cron.job_run_details` contém um histórico de trabalhos cron que podem se tornar muito grandes ao longo do tempo. Recomendamos que você agende um trabalho que limpe essa tabela. Por exemplo, manter uma semana de registros pode ser suficiente para fins de solução de problemas.
O exemplo a seguir usa a função [cron.schedule](#PostgreSQL_pg_cron.schedule) para agendar um trabalho que é executado todos os dias à meia-noite para limpar a tabela `cron.job_run_details`. O trabalho mantém apenas os últimos sete dias. Use sua `rds_superuser` para agendar o trabalho da seguinte forma.
```
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
```
Para obter mais informações, consulte [Tabelas para agendar trabalhos e capturar status](#PostgreSQL_pg_cron.tables).
### Registrar em log erros somente no arquivo postgresql.log
Para impedir a gravação na tabela `cron.job_run_details`, modifique o grupo de parâmetros associado à instância de banco de dados do PostgreSQL e defina o parâmetro `cron.log_run` como desativado. A extensão `pg_cron` não gravará mais na tabela e vai capturar erros somente no arquivo `postgresql.log`. Para obter mais informações, consulte [Modificar parâmetros em um grupo de parâmetros de banco de dados no Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
Use o comando a seguir para verificar o valor do parâmetro `cron.log_run`.
```
postgres=> SHOW cron.log_run;
```
Para obter mais informações, consulte [Parâmetros para gerenciar a extensão pg\$1cron](#PostgreSQL_pg_cron.parameters).
### Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão
Os metadados para `pg_cron` são todos mantidos no banco de dados padrão PostgreSQL chamado `postgres`. Como os operadores em segundo plano são usados para executar os trabalhos cron de manutenção, você pode agendar um trabalho em qualquer um dos seus bancos de dados dentro da instância de banco de dados do PostgreSQL.
**nota**
Somente usuários com o perfil `rds_superuser` ou privilégios `rds_superuser` podem listar todos os trabalhos cron no banco de dados. Outros usuários podem visualizar somente seus próprios trabalhos na tabela `cron.job`.
1. No banco de dados cron, agende o trabalho como você normalmente faria usando a [cron.schedule](#PostgreSQL_pg_cron.schedule).
```
postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
```
1. Como um usuário com a função `rds_superuser`, atualize a coluna do banco de dados para o trabalho que você acabou de criar para que ele seja executado em outro banco de dados dentro de sua instância de banco de dados do PostgreSQL.
```
postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
```
1. Verifique consultando a tabela `cron.job`.
```
postgres=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)
```
**nota**
Em algumas situações, você pode adicionar um cron job que você pretende executar em um banco de dados diferente. Nesses casos, o job pode tentar executar no banco de dados padrão (`postgres`) antes de atualizar a coluna correta do banco de dados. Se o nome de usuário tiver permissões, o trabalho será executado com êxito no banco de dados padrão.
## Referência para a extensão pg\$1cron
Você pode usar os seguintes parâmetros, funções e tabelas com a extensão `pg_cron`. Para obter mais informações, consulte [O que é pg\$1cron?](https://github.com/citusdata/pg_cron) na documentação do pg\$1cron.
**Topics**
+ [Parâmetros para gerenciar a extensão pg\$1cron](#PostgreSQL_pg_cron.parameters)
+ [Referência da função: cron.schedule](#PostgreSQL_pg_cron.schedule)
+ [Referência da função: cron.schedule](#PostgreSQL_pg_cron.unschedule)
+ [Tabelas para agendar trabalhos e capturar status](#PostgreSQL_pg_cron.tables)
### Parâmetros para gerenciar a extensão pg\$1cron
Veja a seguir uma lista de parâmetros que controlam o comportamento da extensão `pg_cron`.
| Parâmetro | Descrição |
| --- | --- |
| cron.database\$1name | O banco de dados em que os metadados de `pg_cron` são mantidos. |
| cron.host | O nome do host para se conectar ao PostgresSQL. Não é possível modificar esse valor. |
| cron.log\$1run | Registre todos os trabalhos executados na tabela `job_run_details`. Os valores são `on` ou `off`. Para obter mais informações, consulte [Tabelas para agendar trabalhos e capturar status](#PostgreSQL_pg_cron.tables). |
| cron.log\$1statement | Registre todas as instruções cron antes de executá-las. Os valores são `on` ou `off`. |
| cron.max\$1running\$1jobs | O número máximo de trabalhos que podem ser executados simultaneamente. |
| cron.use\$1background\$1workers | Use trabalhadores em segundo plano em vez de sessões de cliente. Não é possível modificar esse valor. |
Use o seguinte comando SQL para exibir esses parâmetros e seus valores.
```
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
```
### Referência da função: cron.schedule
Essa função agenda um trabalho cron. Inicialmente, o trabalho é agendado no banco de dados `postgres` padrão. A função retorna um valor `bigint` que representa o identificador de trabalho. Para agendar trabalhos a serem executados em outros bancos de dados em sua instância de banco de dados PostgreSQL, consulte o exemplo em [Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão](#PostgreSQL_pg_cron.otherDB).
A função tem dois formatos de sintaxe.
**Sintaxe**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**Parâmetros**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**Exemplos**
```
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)
postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
```
### Referência da função: cron.schedule
Esta função exclui um trabalho cron. Você pode especificar `job_name` ou `job_id`. Uma política garante que você seja o proprietário para remover a programação do trabalho. A função retorna um booleano indicando êxito ou falha.
A função tem os seguintes formatos de sintaxe.
**Sintaxe**
```
cron.unschedule (job_id);
cron.unschedule (job_name);
```
**Parâmetros**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**Exemplos**
```
postgres=> SELECT cron.unschedule(108);
unschedule
------------
t
(1 row)
postgres=> SELECT cron.unschedule('test');
unschedule
------------
t
(1 row)
```
### Tabelas para agendar trabalhos e capturar status
As tabelas a seguir são usadas para agendar os trabalhos cron e registrar como os trabalhos foram concluídos.
| Tabela | Descrição |
| --- | --- |
| cron.job | Contém os metadados sobre cada trabalho agendado. A maioria das interações com esta tabela deve ser feita por meio das funções `cron.schedule` e `cron.unschedule`. Não recomendamos conceder privilégios de atualização ou inserção diretamente a essa tabela. Isso permitiria que o usuário atualizasse a coluna `username` para ser executada como `rds-superuser`. |
| cron.job\$1run\$1details | Contém informações históricas sobre trabalhos agendados passados que foram executados. Isso é útil para investigar o status, as mensagens de retorno e as horas de início e término do trabalho executado. Para evitar que esta tabela cresça indefinidamente, purgue-a regularmente. Para ver um exemplo, consulte [Limpar a tabela de histórico de pg\$1cron](#PostgreSQL_pg_cron.job_run_details). |
# Usar pgAudit para registrar a atividade do banco de dados
Instituições financeiras, agências governamentais e muitos setores precisam manter *registros de auditoria* para atender aos requisitos regulatórios. Ao usar a extensão do PostgreSQL Audit (pgAudit) com seu cluster de banco de dados do Aurora PostgreSQL, você pode capturar os registros detalhados que normalmente são necessários aos auditores ou para atender aos requisitos regulatórios. Por exemplo, você pode configurar a extensão pgAudit para monitorar alterações feitas em tabelas e bancos de dados específicos, registrar o usuário que fez a alteração e muitos outros detalhes.
A extensão pgAudit se baseia na funcionalidade da infraestrutura de registro em log nativa do PostgreSQL, estendendo as mensagens de log com mais detalhes. Em outras palavras, é usada a mesma abordagem para visualizar o log de auditoria e quaisquer mensagens de log. Para obter mais informações sobre o registro em log do PostgreSQL, consulte [Arquivos de log do banco de dados Aurora PostgreSQL](USER_LogAccess.Concepts.PostgreSQL.md).
A extensão pgAudit retira dados confidenciais, como senhas de texto não criptografado, dos logs. Se seu cluster de banco de dados do Aurora PostgreSQL estiver configurado para registrar declarações de linguagem de manipulação de dados (DML) conforme detalhado em [Ativar o registro em log de consultas para o cluster de banco de dados do Aurora PostgreSQL](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md), você poderá evitar o problema de senha de texto não criptografado usando a extensão do PostgreSQL Audit.
Você pode configurar a auditoria em suas instâncias de banco de dados com um alto grau de especificidade. É possível auditar todos os bancos de dados e todos os usuários. Ou você pode optar por auditar somente determinados bancos de dados, usuários e outros objetos. Também é possível excluir explicitamente da auditoria determinados usuários e bancos de dados. Para obter mais informações, consulte [Excluir usuários ou bancos de dados do registro em log de auditoria](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md).
Dada a quantidade de detalhes que podem ser capturados, recomendamos que, se você usar pgAudit, monitore seu consumo de armazenamento.
A extensão pgAudit é compatível com todas as versões disponíveis do Aurora PostgreSQL. Para obter uma lista de versões de pgAudit compatíveis com a versão do Aurora PostgreSQL, consulte [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) (Versões de extensão para o Amazon Aurora PostgreSQL) em *Release Notes for Aurora PostgreSQL* (Notas de versão do Aurora PostgreSQL).
**Topics**
+ [Configurar a extensão pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)
+ [Auditar objetos de banco de dados](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md)
+ [Excluir usuários ou bancos de dados do registro em log de auditoria](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)
+ [Referência para a extensão pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md)
# Configurar a extensão pgAudit
Para configurar a extensão pgAudit em , seu cluster de banco de dados do Aurora PostgreSQL, primeiro adicione pgAudit às bibliotecas compartilhadas no grupo de parâmetros de cluster de banco de dados personalizado para seu cluster de banco de dados do Aurora PostgreSQL. Para obter informações sobre como criar um grupo de parâmetros de cluster de banco de dados, consulte [Grupos de parâmetros para Amazon Aurora](USER_WorkingWithParamGroups.md). Depois, instale a extensão pgAudit. Por fim, especifique os bancos de dados e os objetos que deseja auditar. Os procedimentos nesta seção mostram o procedimento. É possível usar o Console de gerenciamento da AWS ou a AWS CLI.
Você deve ter permissões como a função `rds_superuser` para realizar todas essas tarefas.
As etapas a seguir pressupõem que seu cluster de banco de dados do Aurora PostgreSQL esteja associado a um grupo de parâmetros de cluster de banco de dados.
## Console
**Como configurar a extensão pgAudit**
1. Faça login no Console de gerenciamento da AWS e abra o console do Amazon RDS em [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. No painel de navegação, selecione sua instância do gravador do cluster de banco de dados do Aurora PostgreSQL.
1. Abra a guia **Configuration** (Configuração) para sua instância do gravador de cluster de banco de dados do Aurora PostgreSQL. Entre os detalhes da instância, encontre o link **Parameter group** (Grupo de parâmetros).
1. Clique no link para abrir os parâmetros personalizados associados ao seu cluster de banco de dados do Aurora PostgreSQL.
1. No campo **Parameters** (Parâmetros), digite `shared_pre` para encontrar o parâmetro `shared_preload_libraries`.
1. Selecione **Edit parameters** (Editar parâmetros) para acessar os valores das propriedades.
1. Adicione `pgaudit` à lista no campo **Values** (Valores). Use uma vírgula para separar itens na lista de valores.
![\[Imagem do parâmetro shared_preload_libaries com pgAudit adicionada.\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Reinicie a instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL para que a alteração no parâmetro `shared_preload_libraries` tenha efeito.
1. Quando a instância estiver disponível, verifique se a pgAudit foi inicializada. Use `psql` para se conectar à instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL e depois execute o comando a seguir.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. Com a pgAudit inicializada, agora você pode criar a extensão. Você precisa criar a extensão depois de inicializar a biblioteca porque a extensão `pgaudit` instala acionadores de eventos para auditar declarações de linguagem de definição de dados (DDL).
```
CREATE EXTENSION pgaudit;
```
1. Feche a sessão `psql`.
```
labdb=> \q
```
1. Faça login no Console de gerenciamento da AWS e abra o console do Amazon RDS em [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Encontre o parâmetro `pgaudit.log` na lista e defina como o valor apropriado para o caso de uso. Por exemplo, definir o parâmetro `pgaudit.log` como `write` conforme mostrado na imagem a seguir captura inserções, atualizações, exclusões e alguns outros tipos de alterações no log.
![\[Imagem do parâmetro pgaudit.log com a configuração.\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/images/rpg_set_pgaudit-log-level.png)
Você também pode selecionar um dos valores a seguir para o parâmetro `pgaudit.log`.
+ none: esse é o valor padrão. Nenhuma alteração no banco de dados é registrada.
+ all: registra tudo (read, write, function, role, ddl, misc).
+ ddl: registra todas as instruções de linguagem de definição de dados (DDL) não incluídas na classe `ROLE`.
+ function: registra chamadas de função e blocos de `DO`.
+ misc: registra comandos diversos, como `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` e `SET`.
+ read: registra `SELECT` e `COPY` quando a fonte é uma relação (como uma tabela) ou uma consulta.
+ role: registra declarações relacionadas a funções e privilégios, como `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` e `DROP ROLE`.
+ write: registra `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` e `COPY` quando o destino é uma relação (tabela).
1. Escolha **Salvar alterações**.
1. Abra o console do Amazon RDS em [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Selecione a instância do gravador do cluster de banco de dados do Aurora PostgreSQL na lista Bancos de dados.
## AWS CLI
**Como configurar a pgAudit**
Para configurar a pgAudit usando a AWS CLI, chame a operação [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) para modificar os parâmetros do log de auditoria em seu grupo de parâmetros personalizado, conforme mostrado no procedimento a seguir.
1. Use o comando AWS CLI a seguir para adicionar `pgaudit` ao parâmetro `shared_preload_libraries`.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use o comando AWS CLI a seguir para reinicializar a instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL para que a biblioteca da pgaudit seja inicializada.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Quando a instância estiver disponível, verifique se a `pgaudit` foi inicializada. Use `psql` para se conectar à instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL e, depois, execute o comando a seguir.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
Com a pgAudit inicializada, agora você pode criar a extensão.
```
CREATE EXTENSION pgaudit;
```
1. Feche a sessão `psql` para que você possa usar a AWS CLI.
```
labdb=> \q
```
1. Use o comando AWS CLI a seguir para especificar as classes de declaração que devem ser registradas pelo registro em log de auditoria da sessão. O exemplo define o parâmetro `pgaudit.log` como `write`, que captura inserções, atualizações e exclusões no log.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
--region aws-region
```
Você também pode selecionar um dos valores a seguir para o parâmetro `pgaudit.log`.
+ none: esse é o valor padrão. Nenhuma alteração no banco de dados é registrada.
+ all: registra tudo (read, write, function, role, ddl, misc).
+ ddl: registra todas as instruções de linguagem de definição de dados (DDL) não incluídas na classe `ROLE`.
+ function: registra chamadas de função e blocos de `DO`.
+ misc: registra comandos diversos, como `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` e `SET`.
+ read: registra `SELECT` e `COPY` quando a fonte é uma relação (como uma tabela) ou uma consulta.
+ role: registra declarações relacionadas a funções e privilégios, como `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` e `DROP ROLE`.
+ write: registra `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` e `COPY` quando o destino é uma relação (tabela).
Reinicie a instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL usando o comando AWS CLI a seguir.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Auditar objetos de banco de dados
Com a pgAudit configurada em seu cluster de banco de dados do Aurora PostgreSQL e configurada para seus requisitos, informações mais detalhadas são capturadas no log do PostgreSQL. Por exemplo, enquanto a configuração de registro em padrão do PostgreSQL identifica a data e a hora em que uma alteração foi feita em uma tabela do banco de dados, com a extensão pgAudit, a entrada do log pode incluir o esquema, o usuário que fez a alteração e outros detalhes, dependendo de como os parâmetros da extensão estão configurados. Você pode configurar a auditoria para monitorar as alterações das maneiras a seguir.
+ Para cada sessão, por usuário. Para o nível da sessão, você pode capturar o texto do comando totalmente qualificado.
+ Para cada objeto, por usuário e por banco de dados.
O recurso de auditoria de objetos é ativado quando você cria a função `rds_pgaudit` no sistema e depois a adiciona ao parâmetro `pgaudit.role` no grupo de parâmetros personalizado. Por padrão, o parâmetro `pgaudit.role` não está definido e o único valor permitido é `rds_pgaudit`. As etapas a seguir pressupõem que a `pgaudit` tenha sido inicializada e que você tenha criado a extensão `pgaudit` seguindo o procedimento em [Configurar a extensão pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md).
![\[Imagem do arquivo de log do PostgreSQL depois de configurar a pgAudit.\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/images/pgaudit-log-example.png)
Conforme mostrado neste exemplo, a linha “LOG: AUDIT: SESSION” fornece informações sobre a tabela e o respectivo esquema, entre outros detalhes.
**Como configurar a auditoria de objetos**
1. Use `psql` para se conectar à instância do gravador do cluster de banco de dados do Aurora PostgreSQL.
```
psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
```
1. Crie uma função de banco de dados chamada `rds_pgaudit` usando o comando a seguir.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=>
```
1. Feche a sessão `psql`.
```
labdb=> \q
```
Nas próximas etapas, use a AWS CLI para modificar os parâmetros de log de auditoria no grupo de parâmetros personalizado.
1. Use o comando AWS CLI a seguir para definir o parâmetro `pgaudit.role` como `rds_pgaudit`. Por padrão, esse parâmetro está vazio, e `rds_pgaudit` é o único valor permitido.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use o comando AWS CLI a seguir para reinicializar a instância do gravador do cluster de banco de dados do Aurora PostgreSQL para que as alterações nos parâmetros tenham efeito.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Execute o comando a seguir para confirmar que `pgaudit.role` está definido como `rds_pgaudit`.
```
SHOW pgaudit.role;
pgaudit.role
------------------
rds_pgaudit
```
Para testar o registro em log da extensão pgAudit, execute vários comandos de exemplo semelhantes ao que você deseja auditar. Por exemplo, você pode executar os seguintes comandos.
```
CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id
----
(0 rows)
```
Os logs do banco de dados devem conter uma entrada semelhante à seguinte.
```
...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...
```
Para obter informações sobre como visualizar os logs, consulte [Monitorar arquivos de log do Amazon Aurora](USER_LogAccess.md).
Para saber mais sobre a extensão pgAudit, consulte [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md) no GitHub.
# Excluir usuários ou bancos de dados do registro em log de auditoria
Conforme discutido em [Arquivos de log do banco de dados Aurora PostgreSQL](USER_LogAccess.Concepts.PostgreSQL.md), os logs do PostgreSQL consomem espaço de armazenamento. O uso da extensão pgAudit aumenta o volume de dados reunidos nos logs em vários graus, dependendo das alterações monitoradas. Talvez você não precise auditar todos os usuários nem bancos de dados no cluster de banco de dados do Aurora PostgreSQL.
Para minimizar os impactos no armazenamento e evitar a captura desnecessária de registros de auditoria, você pode excluir usuários e bancos de dados da auditoria. Você também pode alterar o registro em log em determinada sessão. Os exemplos a seguir mostram o procedimento.
**nota**
As configurações de parâmetros no nível da sessão têm precedência sobre as configurações no grupo de parâmetros de cluster de banco de dados personalizado para a instância do gravador do cluster de banco de dados do Aurora PostgreSQL. Se você não quiser que os usuários do banco de dados ignorem suas configurações de registro em log de auditoria, não se esqueça de alterar as permissões.
Suponha que seu cluster banco de dados do Aurora RDS PostgreSQL esteja configurado ) para auditar o mesmo nível de atividade para todos os usuários e bancos de dados. Depois, decida que não quer auditar o usuário `myuser`. Você pode desativar a auditoria para `myuser` com o comando SQL a seguir.
```
ALTER USER myuser SET pgaudit.log TO 'NONE';
```
Depois, você pode usar a consulta a seguir para conferir a coluna `user_specific_settings` para `pgaudit.log` a fim de confirmar se o parâmetro está definido como `NONE`.
```
SELECT
usename AS user_name,
useconfig AS user_specific_settings
FROM
pg_user
WHERE
usename = 'myuser';
```
Você deve ver a saída da forma a seguir.
```
user_name | user_specific_settings
-----------+------------------------
myuser | {pgaudit.log=NONE}
(1 row)
```
Você pode desativar o registro em log de determinado usuário no meio da sessão com o banco de dados com o comando a seguir.
```
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';
```
Use a consulta a seguir para conferir a coluna de configurações de pgaudit.log para uma combinação específica de usuário e banco de dados.
```
SELECT
usename AS "user_name",
datname AS "database_name",
pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
usename = 'myuser'
AND datname = 'mydatabase'
ORDER BY
1,
2;
```
Você verá uma saída semelhante à seguinte.
```
user_name | database_name | settings
-----------+---------------+------------------
myuser | mydatabase | pgaudit.log=none
(1 row)
```
Depois de desativar a auditoria de `myuser`, você decide que não deseja monitorar as alterações em `mydatabase`. Você pode desativar a auditoria para esse banco de dados específico usando o comando a seguir.
```
ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';
```
Depois, use a consulta a seguir para conferir a coluna database\$1specific\$1settings a fim de confirmar se pgaudit.log está definido como NONE.
```
SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';
```
Você deve ver a saída da forma a seguir.
```
database_name | database_specific_settings
---------------+----------------------------
mydatabase | {pgaudit.log=NONE}
(1 row)
```
Para restaurar as configurações padrão para myuser, use o seguinte comando:
```
ALTER USER myuser RESET pgaudit.log;
```
Para restaurar as configurações padrão para um banco de dados, use o comando a seguir.
```
ALTER DATABASE mydatabase RESET pgaudit.log;
```
Para restaurar as configurações padrão de usuário e banco de dados, use o comando a seguir.
```
ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;
```
Você também pode capturar eventos específicos no log definindo `pgaudit.log` como um dos outros valores permitidos para o parâmetro `pgaudit.log`. Para ter mais informações, consulte [Lista de configurações permitidas para o parâmetro `pgaudit.log`](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings).
```
ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'
```
# Referência para a extensão pgAudit
Você pode especificar o nível de detalhes que deseja para o log de auditoria alterando um ou mais dos parâmetros listados nesta seção.
## Controlar o comportamento da pgAudit
Você pode controlar o registro em log de auditoria alterando um ou mais dos parâmetros listados na tabela a seguir.
| Parameter | Descrição |
| --- | --- |
| `pgaudit.log` | Especifica as classes de declaração que serão registradas pelo registro em log de auditoria de sessão. Os valores permitidos incluem ddl, function, misc, read, role, write, none, all. Para obter mais informações, consulte [Lista de configurações permitidas para o parâmetro `pgaudit.log`](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings). |
| `pgaudit.log_catalog` | Quando ativado (definido como 1), adiciona declarações à trilha de auditoria se todas as relações em uma declaração estiverem em pg\$1catalog. |
| `pgaudit.log_level` | Especifica o nível de log que será usado para entradas de log. Valores permitidos: debug5, debug4, debug3, debug2, debug1, info, notice, warning, log |
| `pgaudit.log_parameter` | Quando ativado (definido como 1), os parâmetros passados com a declaração são capturados no log de auditoria. |
| `pgaudit.log_relation` | Quando ativado (definido como 1), o log de auditoria da sessão cria uma entrada de log separada para cada relação (TABLE, VIEW etc.) referenciada em uma declaração SELECT ou DML. |
| `pgaudit.log_statement_once` | Especifica se o registro incluirá o texto e os parâmetros da instrução com a primeira entrada de log para uma combinação de instrução/subinstrução ou com cada entrada. |
| `pgaudit.role` | Especifica a função primária a ser usada para o registro em log de auditoria de objetos. A única entrada permitida é `rds_pgaudit`. |
## Lista de configurações permitidas para o parâmetro `pgaudit.log`
| Valor | Descrição |
| --- | --- |
| nenhuma | Esse é o padrão. Nenhuma alteração no banco de dados é registrada. |
| todas | Registra tudo (read, write, function, role, ddl, misc). |
| ddl | Registra todas as declarações de linguagem de definição de dados (DDL) não incluídas na classe `ROLE`. |
| função | Registra chamadas de função e blocos de `DO`. |
| misc | Registra comandos diversos, como `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` e `SET`. |
| leitura | Registra `SELECT` e `COPY` quando a fonte é uma relação (como uma tabela) ou uma consulta. |
| perfil | Registra declarações relacionadas a funções e privilégios, como `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` e `DROP ROLE`. |
| write | Registra `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` e `COPY` quando o destino é uma relação (tabela). |
Para registrar vários tipos de eventos com auditoria de sessões, use uma lista separada por vírgulas. Para registrar todos os tipos de eventos, defina `pgaudit.log` para `ALL`. Reinicie a instância de banco de dados para aplicar as alterações.
Com a auditoria de objetos, você pode refinar o registro em log de auditoria para trabalhar com relações específicas. Por exemplo, você pode especificar que deseja o registro em log de auditoria para operações `READ` em uma ou mais tabelas.
# Usar pglogical para sincronizar dados entre instâncias
Todas as versões do Aurora PostgreSQL atualmente disponíveis são compatíveis com a extensão `pglogical`. A extensão pglogical é anterior ao recurso de replicação lógica funcionalmente similar que foi introduzido pelo PostgreSQL na versão 10. Para obter mais informações, consulte [Visão geral da replicação lógica do PostgreSQL com o Aurora](AuroraPostgreSQL.Replication.Logical.md).
A extensão `pglogical` é compatível com a replicação lógica entre dois ou mais clusters de banco de dados Aurora PostgreSQL. Ela também é compatível com a replicação entre diferentes versões do PostgreSQL e entre bancos de dados executados em instâncias de banco de dados RDS para PostgreSQL e clusters de banco de dados Aurora PostgreSQL. A extensão `pglogical` usa um modelo de publicação e assinatura para replicar alterações em tabelas e outros objetos, como sequências, de um editor para um assinante. Ela depende de um slot de replicação para garantir que as alterações sejam sincronizadas de um nó do editor para um nó assinante, definido da seguinte forma.
+ O *nó do editor* é o cluster de banco de dados Aurora PostgreSQL, que é a fonte de dados a serem replicados para outros nós. O nó do editor define as tabelas a serem replicadas em um conjunto de publicações.
+ O *nó do assinante* é o cluster de banco de dados RDS para PostgreSQL que recebe atualizações WAL do editor. O assinante cria uma assinatura para se conectar ao editor e obter os dados WAL decodificados. Quando o assinante cria a assinatura, o slot de replicação é criado no nó do editor.
Depois, você pode encontrar informações sobre a configuração da extensão `pglogical`.
**Topics**
+ [Requisitos e limitações da extensão pglogical](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [Configurar a extensão pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [Configurar a replicação lógica para o cluster de banco de dados Aurora PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [Restabelecer a replicação lógica após uma atualização principal](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [Gerenciar slots de replicação lógica para o Aurora PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [Referência de parâmetros da extensão pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## Requisitos e limitações da extensão pglogical
Todas as versões atualmente disponíveis do Aurora PostgreSQL são compatíveis com a extensão `pglogical`.
Tanto o nó do editor quanto o do assinante devem estar configurados para replicação lógica.
As tabelas que você deseja replicar de um publicador para um assinante devem ter os mesmos nomes e o mesmo esquema. Essas tabelas também devem conter as mesmas colunas, e as colunas devem usar os mesmos tipos de dados. As tabelas de editores e assinantes devem ter as mesmas chaves primárias. Recomendamos que você use somente a CHAVE PRIMÁRIA como restrição exclusiva.
As tabelas no nó do assinante podem ter mais restrições permissivas do que as do nó do editor para restrições CHECK e NOT NULL.
A extensão `pglogical` fornece recursos como replicação bidirecional que não são compatíveis com o recurso de replicação lógica incorporado ao PostgreSQL (versão 10 e superior). Para obter mais informações, consulte [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/) (Replicação bidirecional do PostgreSQL usando pglogical).
# Configurar a extensão pglogical
Para configurar a extensão `pglogical` em seu cluster de banco de dados Aurora PostgreSQL, adicione `pglogical` às bibliotecas compartilhadas no grupo de parâmetros de cluster de banco de dados personalizado para seu cluster de banco de dados Aurora PostgreSQL. Você também precisa definir o valor do parâmetro `rds.logical_replication` como `1`, para ativar a decodificação lógica. Finalmente, você cria a extensão no banco de dados. Você pode usar o Console de gerenciamento da AWS ou a AWS CLI para essas tarefas.
Você deve ter permissões como a função `rds_superuser` para realizar essas tarefas.
As etapas a seguir pressupõem que o cluster de banco de dados do Aurora PostgreSQL está associado a um grupo de parâmetros de cluster de banco de dados. Para obter informações sobre como criar um grupo de parâmetros de cluster de banco de dados, consulte [Grupos de parâmetros para Amazon Aurora](USER_WorkingWithParamGroups.md).
## Console
**Como configurar a extensão pglogical**
1. Faça login no Console de gerenciamento da AWS e abra o console do Amazon RDS em [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. No painel de navegação, selecione sua instância do gravador do cluster de banco de dados do Aurora PostgreSQL.
1. Abra a guia **Configuration** (Configuração) para sua instância do gravador de cluster de banco de dados do Aurora PostgreSQL. Entre os detalhes da instância, encontre o link **Parameter group** (Grupo de parâmetros).
1. Clique no link para abrir os parâmetros personalizados associados ao seu cluster de banco de dados do Aurora PostgreSQL.
1. No campo **Parâmetros**, digite `shared_pre` para encontrar o parâmetro `shared_preload_libraries`.
1. Selecione **Edit parameters** (Editar parâmetros) para acessar os valores das propriedades.
1. Adicione `pglogical` à lista no campo **Values** (Valores). Use uma vírgula para separar itens na lista de valores.
![\[Imagem do parâmetro shared_preload_libraries com pglogical adicionada.\]](http://docs.aws.amazon.com/pt_br/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. Encontre o parâmetro `rds.logical_replication` e defina-o como `1` para ativar a replicação lógica.
1. Reinicialize a instância do gravador do cluster de banco de dados do Aurora PostgreSQL para que suas alterações tenham efeito.
1. Quando a instância estiver disponível, você poderá usar `psql` (ou pgAdmin) para se conectar à instância do gravador do cluster de banco de dados Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Para verificar se pglogical foi inicializada, execute o comando a seguir.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pglogical
(1 row)
```
1. Verifique a configuração que permite a decodificação lógica da forma a seguir.
```
SHOW wal_level;
wal_level
-----------
logical
(1 row)
```
1. Crie a extensão da forma a seguir.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Escolha **Salvar alterações**.
1. Abra o console do Amazon RDS em [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Selecione a instância do gravador do cluster de banco de dados do Aurora PostgreSQL na lista de bancos de dados para selecioná-la e depois selecione **Reboot** (Reinicializar) no menu Actions (Ações).
## AWS CLI
**Como configurar a extensão pglogical**
Para configurar a pglogical usando a AWS CLI, chame a operação [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) para modificar determinados parâmetros em seu grupo de parâmetros personalizado, conforme mostrado no procedimento a seguir.
1. Use o comando AWS CLI a seguir para adicionar `pglogical` ao parâmetro `shared_preload_libraries`.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use o comando AWS CLI a seguir para definir `rds.logical_replication` como `1` a fim de ativar o recurso de decodificação lógica para a instância de gravador do cluster de banco de dados do Aurora PostgreSQL.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use o comando AWS CLI a seguir para reinicializar a instância do gravador de seu cluster de banco de dados Aurora PostgreSQL para que a biblioteca da pglogical seja inicializada.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Quando a instância estiver disponível, use `psql` para se conectar à instância do gravador do cluster de banco de dados Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Crie a extensão da forma a seguir.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Reinicie a instância do gravador de seu cluster de banco de dados do Aurora PostgreSQL usando o comando AWS CLI a seguir.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Configurar a replicação lógica para o cluster de banco de dados Aurora PostgreSQL
O procedimento a seguir mostra como iniciar a replicação lógica entre dois clusters de banco de dados Aurora PostgreSQL. As etapas pressupõem que tanto a fonte (editor) quanto o destino (assinante) tenham a extensão `pglogical` configurada conforme detalhado em [Configurar a extensão pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
**nota**
O `node_name` de um nó assinante não pode começar com `rds`.
**Como criar o nó do editor e definir as tabelas a serem replicadas**
Estas etapas pressupõem que o cluster de banco de dados do Aurora PostgreSQL tenha uma instância de gravador com um banco de dados com uma ou mais tabelas que você deseja replicar para outro nó. Você precisa recriar a estrutura da tabela do editor no assinante, então, primeiro, obtenha a estrutura da tabela, se necessário. Você pode fazer isso usando o metacomando `psql` `\d tablename` e criando a mesma tabela na instância do assinante. O procedimento a seguir cria uma tabela de exemplo no editor (fonte) para fins de demonstração.
1. Use `psql` para se conectar à instância que tem a tabela que você deseja usar como fonte para assinantes.
```
psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
Se você não tiver uma tabela, crie uma tabela de exemplo da forma a seguir.
1. Crie uma tabela de exemplo usando a declaração SQL a seguir.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Preencha a tabela com dados gerados usando a instrução SQL a seguir.
```
INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
INSERT 0 5000
```
1. Verifique se os dados existem na tabela usando a declaração SQL a seguir.
```
SELECT count(*) FROM docs_lab_table;
```
1. Identifique esse cluster de banco de dados do Aurora PostgreSQL como o nó do editor da forma a seguir.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_provider',
dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
create_node
-------------
3410995529
(1 row)
```
1. Adicione a tabela que você deseja replicar ao conjunto de replicação padrão. Para obter mais informações sobre conjuntos de replicação, consulte [Replication sets](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets) (Conjuntos de replicação) na documentação da pglogical.
```
SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
replication_set_add_table
---------------------------
t
(1 row)
```
A configuração do nó do editor está concluída. Agora você pode configurar o nó de assinante para receber as atualizações do editor.
**Como configurar o nó de assinante e criar uma assinatura para receber atualizações**
Estas etapas pressupõem que o cluster de banco de dados do Aurora PostgreSQL tenha sido configurado com a extensão `pglogical`. Para obter mais informações, consulte [Configurar a extensão pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
1. Use `psql` para se conectar à instância em que você deseja receber atualizações do editor.
```
psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. No cluster de banco de dados do Aurora PostgreSQL do assinante, crie a mesma tabela que existe no editor. Neste exemplo, a tabela é `docs_lab_table`. Você pode criar a tabela da seguinte maneira.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Verifique se essa tabela está vazia.
```
SELECT count(*) FROM docs_lab_table;
count
-------
0
(1 row)
```
1. Identifique esse cluster de banco de dados Aurora PostgreSQL como o nó do assinante da forma a seguir.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_target',
dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
create_node
-------------
2182738256
(1 row)
```
1. Crie a assinatura.
```
SELECT pglogical.create_subscription(
subscription_name := 'docs_lab_subscription',
provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
replication_sets := ARRAY['default'],
synchronize_data := true,
forward_origins := '{}' );
create_subscription
---------------------
1038357190
(1 row)
```
Ao concluir essa etapa, os dados da tabela no editor são criados na tabela no assinante. Se você quiser verificar se isso ocorreu, verifique a consulta SQL a seguir.
```
SELECT count(*) FROM docs_lab_table;
count
-------
5000
(1 row)
```
Desse ponto em diante, as alterações feitas na tabela do editor são replicadas na tabela do assinante.
# Restabelecer a replicação lógica após uma atualização principal
Antes de realizar uma atualização de versão principal de um cluster de banco de dados do Aurora PostgreSQL que está sendo configurado como um nó de editor para replicação lógica, você deve eliminar todos os slots de replicação, mesmo aqueles que não estão ativos. Recomendamos que você desvie temporariamente as transações do banco de dados do nó do editor, elimine os slots de replicação, atualize o cluster de banco de dados do Aurora PostgreSQL, e, depois, restabeleça e reinicie a replicação.
Os slots de replicação são hospedados somente no nó do editor. O nó de assinante do Aurora PostgreSQL em um cenário de replicação lógica não tem slots a serem descartados. O processo de atualização da versão principal do Aurora PostgreSQL é compatível com a atualização do assinante para uma nova versão principal do PostgreSQL, independente do nó do editor. No entanto, o processo de atualização interrompe o processo de replicação e interfere na sincronização dos dados WAL entre o nó do editor e o nó do assinante. Você precisa restabelecer a replicação lógica entre o editor e o assinante depois de atualizar o editor, o assinante ou ambos. O procedimento a seguir mostra como determinar se a replicação foi interrompida e como resolver o problema.
## Determinar se a replicação lógica foi interrompida
Você pode determinar se o processo de replicação foi interrompido consultando o nó do editor ou o nó do assinante da forma a seguir.
**Como conferir o nó do editor**
+ Use `psql` para se conectar ao nó do editor e, depois, consultar a função `pg_replication_slots`. Observe o valor na coluna ativa. Normalmente, isso retornará `t` (true) mostrando que a replicação está ativa. Se a consulta retornar `f` (false), é uma indicação de que a replicação para o assinante foi interrompida.
```
SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f
(1 row)
```
**Como conferir o nó do assinante**
No nó do assinante, você pode conferir o status da replicação de três maneiras diferentes.
+ Examine os logs do PostgreSQL no nó do assinante para encontrar mensagens de falha. O log identifica falhas com mensagens que incluem o código de saída 1, conforme mostrado a seguir.
```
2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
```
+ Consulte a função `pg_replication_origin`. Conecte-se ao banco de dados no nó do assinante usando `psql` e consulte a função `pg_replication_origin` da forma a seguir.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+--------
(0 rows)
```
O conjunto de resultados vazio significa que a replicação foi interrompida. Normalmente, você deve ver a saída da forma a seguir.
```
roident | roname
---------+----------------------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
+ Consulte a função `pglogical.show_subscription_status` conforme exibido no exemplo a seguir.
```
SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
---====----------------+--------+-------------------------------------
docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
Essa saída mostra que a replicação foi interrompida. Seu status é `down`. Normalmente, a saída mostra o status como `replicating`.
Se seu processo de replicação lógica tiver sido interrompido, você poderá restabelecer a replicação seguindo estas etapas.
**Como restabelecer a replicação lógica entre os nós do editor e do assinante**
Para restabelecer a replicação, primeiro você desconecta o assinante do nó do editor e depois restabelece a assinatura, conforme descrito nestas etapas.
1. Conecte-se ao nó do assinante usando `psql` da forma a seguir.
```
psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Desative a assinatura usando a função `pglogical.alter_subscription_disable`.
```
SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
1. Obtenha o identificador do nó do editor consultando a `pg_replication_origin` da forma a seguir.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+-------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
1. Use a resposta da etapa anterior com o comando `pg_replication_origin_create` para atribuir o identificador que pode ser usado pela assinatura quando restabelecida.
```
SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
pg_replication_origin_create
------------------------------
1
(1 row)
```
1. Ative a assinatura passando seu nome com um status de `true`, conforme exibido no exemplo a seguir.
```
SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
alter_subscription_enable
---------------------------
t
(1 row)
```
Consulte o status do nó. Seu status deve ser `replicating` conforme mostrado neste exemplo.
```
SELECT subscription_name,status,slot_name
FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
-------------------------------+-------------+-------------------------------------
docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)
```
Confira o status do slot de replicação do assinante no nó do editor. A coluna `active` do slot deve retornar `t` (true), indicando que a replicação foi restabelecida.
```
SELECT slot_name,plugin,slot_type,active
FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t
(1 row)
```
# Gerenciar slots de replicação lógica para o Aurora PostgreSQL
Antes de realizar uma atualização de versão principal de uma instância de gravador do cluster de banco de dados do Aurora PostgreSQL que está atuando como um nó de editor em um cenário de replicação lógica, você deve eliminar os slots de replicação na instância. O processo de pré-conferência da atualização da versão principal notifica você de que a atualização não pode continuar até que os slots sejam eliminados.
Para identificar os slots de replicação que foram criados usando a extensão `pglogical`, faça login em cada banco de dados e obtenha o nome dos nós. Ao consultar o nó do assinante, você obtém os nós do editor e do assinante na saída, conforme mostrado neste exemplo.
```
SELECT * FROM pglogical.node;
node_id | node_name
------------+-------------------
2182738256 | docs_lab_target
3410995529 | docs_lab_provider
(2 rows)
```
Você pode obter os detalhes sobre a assinatura com a consulta a seguir.
```
SELECT sub_name,sub_slot_name,sub_target
FROM pglogical.subscription;
sub_name | sub_slot_name | sub_target
----------+--------------------------------+------------
docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)
```
Agora você pode cancelar a assinatura da forma a seguir.
```
SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
drop_subscription
-------------------
1
(1 row)
```
Depois de cancelar a assinatura, você pode excluir o nó.
```
SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
drop_node
-----------
t
(1 row)
```
Você pode verificar se o nó não existe mais da forma a seguir.
```
SELECT * FROM pglogical.node;
node_id | node_name
---------+-----------
(0 rows)
```
# Referência de parâmetros da extensão pglogical
Na tabela, você pode encontrar parâmetros associados à extensão `pglogical`. Parâmetros como `pglogical.conflict_log_level` e `pglogical.conflict_resolution` são usados para lidar com conflitos de atualização. Podem surgir conflitos quando alterações são feitas localmente nas mesmas tabelas que estão inscritas para receber alterações do editor. Os conflitos também podem ocorrer durante vários cenários, como replicação bidirecional ou quando vários assinantes estão se replicando do mesmo editor. Para obter mais informações, consulte [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/) (Replicação bidirecional do PostgreSQL usando pglogical).
| Parameter | Descrição |
| --- | --- |
| pglogical.batch\$1inserts | Inserções em lote, se possível. Não definido por padrão. Mude para “1” para ativar, “0” para desativar. |
| pglogical.conflict\$1log\$1level | Define o nível de log a ser usado para registrar em log conflitos resolvidos. Os valores de string compatíveis são debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic. |
| pglogical.conflict\$1resolution | Define o método a ser usado para resolver conflitos quando eles podem ser resolvidos. Os valores de string compatíveis são error, apply\$1remote, keep\$1local, last\$1update\$1wins, first\$1update\$1wins. |
| pglogical.extra\$1connection\$1options | Opções de conexão para adicionar a todas as conexões de nó de pares. |
| pglogical.synchronous\$1commit | Valor de confirmação síncrona específica do pglogical |
| pglogical.use\$1spi | Use a SPI (interface de programação de servidores) em vez da API de baixo nível para aplicar alterações. Defina como “1” para ativar, “0” para desativar. Para obter mais informações sobre a SPI, consulte [Server Programming Interface](https://www.postgresql.org/docs/current/spi.html) (Interface de programação de servidores) na documentação do PostgreSQL. |
# Trabalhar com os invólucros de dados externos compatíveis do Amazon Aurora PostgreSQL
Um Foreign Data Wraper (FDW – Empacotador de dados externos) é um tipo específico de extensão que fornece acesso a dados externos. Por exemplo, a extensão `oracle_fdw` permite que a instância de banco de dados do Aurora PostgreSQL funcione com bancos de dados Oracle.
A seguir, você encontrará informações sobre vários invólucros de dados externos do PostgreSQL compatíveis.
**Topics**
+ [Usar a extensão log\$1fdw para acessar o log de banco de dados usando SQL](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [Usar a extensão postgres\$1fdw para acessar dados externos](postgresql-commondbatasks-fdw.md)
+ [Trabalhar com bancos de dados MySQL usando a extensão mysql\$1fdw](postgresql-mysql-fdw.md)
+ [Trabalhar com um banco de dados Oracle usando a extensão oracle\$1fdw](postgresql-oracle-fdw.md)
+ [Trabalhar com bancos de dados do SQL Server usando a extensão tds\$1fdw](postgresql-tds-fdw.md)
# Usar a extensão log\$1fdw para acessar o log de banco de dados usando SQL
O cluster do Aurora PostgreSQL DB é compatível com a extensão `log_fdw`, que permite acessar o log do mecanismo de banco de dados usando uma interface SQL. A extensão `log_fdw` apresenta duas novas funções que facilitam a criação de tabelas externas para logs de banco de dados:
+ `list_postgres_log_files` – lista os arquivos no diretório do log do banco de dados e o tamanho do arquivo em bytes.
+ `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – cria uma tabela externa para o arquivo especificado no banco de dados atual.
Todas as funções criadas por `log_fdw` são de propriedade do `rds_superuser`. Os membros da função `rds_superuser` podem conceder acesso a essas funções para outros usuários do banco de dados.
Por padrão, os arquivos de log são gerados pelo Amazon Aurora no formato (erro padrão) `stderr`, conforme especificado no parâmetro `log_destination`. Existem apenas duas opções para esse parâmetro, `stderr` e `csvlog` (valores separados por vírgula, CSV). Se você adicionar a opção `csvlog` ao parâmetro, o Amazon Aurora gerará os logs `stderr` e `csvlog`. Isso pode afetar a capacidade de armazenamento em seu cluster de banco de dados. Portanto, você precisa estar ciente dos outros parâmetros que afetam o processamento de logs. Para obter mais informações, consulte [Definir o destino dos logs (`stderr`, `csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format).
Um benefício de gerar logs do `csvlog` é que a extensão `log_fdw` permite criar tabelas externas com dados divididos ordenadamente em várias colunas. Para fazer isso, sua instância precisa estar associada a um grupo de parâmetros de banco de dados personalizado para que você possa alterar a configuração para `log_destination`. Para obter mais informações sobre como fazer isso, consulte [Grupos de parâmetros para Amazon Aurora](USER_WorkingWithParamGroups.md).
O exemplo a seguir pressupõe que o parâmetro `log_destination` inclua `cvslog`.
**Para usar a extensão log\$1fdw**
1. Instale a extensão `log_fdw`.
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. Crie o servidor de log como um wrapper externo de dados.
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. Selecione todos os arquivos de log na lista.
```
postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;
```
A seguir você encontra um exemplo de resposta.
```
file_name | file_size_bytes
------------------------------+-----------------
postgresql.log.2023-08-09-22.csv | 1111
postgresql.log.2023-08-09-23.csv | 1172
postgresql.log.2023-08-10-00.csv | 1744
postgresql.log.2023-08-10-01.csv | 1102
(4 rows)
```
1. Crie uma tabela com uma única coluna "log\$1entry" para o arquivo selecionado.
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
A resposta não fornece nenhum detalhe além de que a tabela agora existe.
```
-----------------------------------
(1 row)
```
1. Selecione um exemplo de arquivo de log. O código a seguir recupera o horário do log e a descrição da mensagem de erro.
```
postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
```
A seguir você encontra um exemplo de resposta.
```
log_time | message
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)
```
# Usar a extensão postgres\$1fdw para acessar dados externos
Você pode acessar dados em uma tabela em um servidor de banco de dados remoto com a extensão [postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html). Se você configurar uma conexão remota usando a instância de banco de dados do PostgreSQL, o acesso também estará disponível para a réplica de leitura.
**Para usar postgres\$1fdw para acessar um servidor de banco de dados remoto**
1. Instale a extensão postgres\$1fdw.
```
CREATE EXTENSION postgres_fdw;
```
1. Crie um servidor de dados externo usando CREATE SERVER.
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. Crie um mapeamento de usuário para identificar a função a ser usada no servidor remoto.
**Importante**
Para ocultar a senha a fim de que ela não apareça nos logs, defina `log_statement=none` no nível da sessão. A configuração no nível do parâmetro não oculta a senha.
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. Crie uma tabela que mapeia para a tabela no servidor remoto.
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# Trabalhar com bancos de dados MySQL usando a extensão mysql\$1fdw
Para acessar um banco de dados compatível com MySQL pelo cluster de banco de dados do Aurora PostgreSQL, você pode instalar e usar a extensão `mysql_fdw`. Esse invólucro de dados externos permite que você trabalhe com o RDS for MySQL, o Aurora MySQL, o MariaDB e outros bancos de dados compatíveis com MySQL. A conexão do cluster de banco de dados do RDS para PostgreSQL ao banco de dados MySQL é criptografada com base no melhor esforço, dependendo das configurações do cliente e do servidor. No entanto, você pode impor a criptografia, se quiser. Para obter mais informações, consulte [Usar criptografia em trânsito com a extensão](#postgresql-mysql-fdw.encryption-in-transit).
A extensão `mysql_fdw` é compatível com o Amazon Aurora PostgreSQL versões 15.4, 14.9, 13.12, 12.16, e posteriores. Ela é compatível com seleções, inserções, atualizações e exclusões de um banco de dados do RDS for PostgreSQL para tabelas em uma instância de banco de dados compatível com MySQL.
**Topics**
+ [Configurar um banco de dados Aurora PostgreSQL para usar a extensão mysql\$1fdw](#postgresql-mysql-fdw.setting-up)
+ [Exemplo: trabalhar com um banco de dados do Aurora MySQL pelo Aurora PostgreSQL](#postgresql-mysql-fdw.using-mysql_fdw)
+ [Usar criptografia em trânsito com a extensão](#postgresql-mysql-fdw.encryption-in-transit)
## Configurar um banco de dados Aurora PostgreSQL para usar a extensão mysql\$1fdw
Para configurar a extensão `mysql_fdw` no cluster de banco de dados do Aurora PostgreSQL, é necessário carregar a extensão no cluster de banco de dados e, em seguida, criar o ponto de conexão com a instância de banco de dados do MySQL. Para essa tarefa, você precisa ter os seguintes detalhes sobre a instância de banco de dados do MySQL:
+ Nome de host ou endpoint. Para um cluster de banco de dados do Aurora MySQL, é possível encontrar o endpoint usando o console. Escolha a guia “Connectivity & security” (Conectividade e segurança) e procure na seção “Endpoint and port” (Endpoint e porta).
+ Número da porta. O número da porta padrão do MySQL é 3306.
+ O nome do banco de dados. O identificador do banco de dados.
Você também precisa fornecer acesso no grupo de segurança ou na lista de controle de acesso (ACL) para a porta 3306 do MySQL. Tanto o cluster de banco de dados do Aurora PostgreSQL como o cluster de banco de dados do Aurora . Se o acesso não estiver configurado corretamente, ao tentar se conectar à tabela compatível com o MySQL, será exibida uma mensagem de erro semelhante à seguinte:
```
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)
```
No procedimento a seguir, você (como a conta `rds_superuser`) cria o servidor externo. Depois, você concede acesso ao servidor externo a usuários específicos. Em seguida, esses usuários criam seus próprios mapeamentos para as contas de usuário apropriadas do MySQL para trabalhar com a instância de banco de dados do MySQL.
**Para usar mysql\$1fdw a fim de acessar um servidor de banco de dados MySQL**
1. Conecte-se à instância de banco de dados do PostgreSQL usando uma conta que tenha a função `rds_superuser`. Se tiver aceitado os padrões ao criar o cluster de banco de dados do Aurora PostgreSQL, o nome de usuário será `postgres` e você poderá se conectar usando a ferramenta da linha de comando `psql` da seguinte forma:
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. Instale a extensão `mysql_fdw` da seguinte forma:
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
Depois que a extensão é instalada no cluster de banco de dados do Aurora PostgreSQL, é possível configurar o servidor externo que fornece a conexão com um banco de dados MySQL.
**Para criar o servidor externo**
Execute essas tarefas no cluster de banco de dados do Aurora PostgreSQL. As etapas presumem que você esteja conectado como usuário com privilégios `rds_superuser`, como `postgres`.
1. Crie um servidor externo no cluster de banco de dados do Aurora PostgreSQL:
```
postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Conceda aos usuários apropriados acesso ao servidor externo. Eles devem ser usuários não administradores, ou seja, usuários sem a função `rds_superuser`.
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
Os usuários do PostgreSQL criam e gerenciam suas próprias conexões com o banco de dados do MySQL por meio do servidor externo.
## Exemplo: trabalhar com um banco de dados do Aurora MySQL pelo Aurora PostgreSQL
Suponha que você tenha uma tabela simples em uma instância de banco de dados do Aurora PostgreSQL. Os usuários do Aurora PostgreSQL querem consultar os itens `SELECT`, `INSERT`, `UPDATE` e `DELETE` nessa tabela. Suponha que a extensão `mysql_fdw` foi criada na instância de banco de dados do RDS for PostgreSQL, conforme detalhado no procedimento anterior. Depois de se conectar à instância de banco de dados do RDS for PostgreSQL como um usuário com privilégios `rds_superuser`, é possível prosseguir com as etapas abaixo.
1. Crie um servidor externo no cluster de banco de dados do Aurora PostgreSQL:
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Conceda uso a um usuário que não tenha permissões `rds_superuser`; por exemplo, `user1`:
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. Conecte-se como *user1* e, em seguida, crie um mapeamento para o usuário do MySQL:
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. Crie uma tabela externa vinculada a uma tabela do MySQL:
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. Execute uma consulta simples na tabela externa:
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. Você pode adicionar, alterar e remover dados da tabela do MySQL. Por exemplo:
```
test=> INSERT INTO mytab values (2, 'mango');
INSERT 0 1
```
Execute a consulta `SELECT` novamente para ver os resultados:
```
test=> SELECT * FROM mytab ORDER BY 1;
a | b
---+-------
1 | apple
2 | mango
(2 rows)
```
## Usar criptografia em trânsito com a extensão
A conexão com o MySQL pelo Aurora PostgreSQL usa criptografia em trânsito (TLS/SSL) por padrão. No entanto, a conexão volta para não criptografada quando a configuração do cliente e do servidor é diferente. Você pode impor a criptografia para todas as conexões de saída especificando a opção `REQUIRE SSL` nas contas de usuário do RDS for MySQL. Essa mesma abordagem também funciona para contas de usuário do MariaDB e do Aurora MySQL.
Para contas de usuário do MySQL configuradas como `REQUIRE SSL`, a tentativa de conexão falhará se não for possível estabelecer uma conexão segura.
Para aplicar criptografia a contas de usuário do banco de dados do MySQL existentes, você pode usar o comando `ALTER USER`. A sintaxe varia, dependendo da versão do MySQL, conforme mostrado na tabela a seguir. Para obter mais informações, consulte [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html) no *Manual de referência do MySQL*.
| MySQL 5.7, MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
Para obter mais informações sobre a extensão `mysql_fdw`, consulte a documentação do [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw).
# Trabalhar com um banco de dados Oracle usando a extensão oracle\$1fdw
Para acessar um banco de dados Oracle pelo cluster de banco de dados do Aurora PostgreSQL , você pode instalar e usar a extensão `oracle_fdw`. Essa extensão é um invólucro de dados externos para bancos de dados Oracle. Para saber mais sobre a extensão, consulte a documentação do [oracle\$1fdw](https://github.com/laurenz/oracle_fdw).
A extensão `oracle_fdw` é compatível com o Aurora PostgreSQL 12.7 (Amazon Aurora versão 4.2) e versões posteriores.
**Topics**
+ [Ativação da extensão oracle\$1fdw](#postgresql-oracle-fdw.enabling)
+ [Exemplo: usar um servidor externo vinculado a um banco de dados Amazon RDS for Oracle](#postgresql-oracle-fdw.example)
+ [Como trabalhar com criptografia em trânsito](#postgresql-oracle-fdw.encryption)
+ [Noções básicas da visualização e das permissões de pg\$1user\$1mappings](#postgresql-oracle-fdw.permissions)
## Ativação da extensão oracle\$1fdw
Para usar a extensão oracle\$1fdw, realize o procedimento a seguir.
**Para ativar a extensão oracle\$1fdw**
+ Execute o comando a seguir usando uma conta que tenha as permissões de `rds_superuser`.
```
CREATE EXTENSION oracle_fdw;
```
## Exemplo: usar um servidor externo vinculado a um banco de dados Amazon RDS for Oracle
O exemplo a seguir mostra o uso de um servidor externo vinculado a um banco de dados Amazon RDS for Oracle.
**Para criar um servidor externo vinculado a um banco de dados do RDS for Oracle**
1. Na instância de banco de dados do RDS for Oracle, observe:
+ Endpoint
+ Port
+ Database name
1. Crie um servidor externo.
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. Conceda uso a um usuário que não tenha privilégios `rds_superuser`, por exemplo `user1`.
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. Conecte-se como `user1` e crie um mapeamento para um usuário Oracle.
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. Crie uma tabela estrangeira vinculada a uma tabela Oracle.
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Consulte a tabela externa.
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
Se a consulta relatar o seguinte erro, verifique seu grupo de segurança e a lista de controle de acesso (ACL) para verificar se as duas instâncias podem se comunicar.
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## Como trabalhar com criptografia em trânsito
A criptografia PostgreSQL-to-Oracle em trânsito é baseada em uma combinação de parâmetros de configuração de cliente e servidor. Para obter um exemplo usando o Oracle 21c, consulte [About the Values for Negotiating Encryption and Integrity](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5) (Sobre os valores para negociar criptografia e integridade) na documentação do Oracle. Como o cliente usado para oracle\$1fdw no Amazon RDS está configurado com `ACCEPTED`, a criptografia depende da configuração do servidor de banco de dados do Oracle e utiliza a Oracle Security Library (libnnz) para isso.
Se o banco de dados estiver no RDS for Oracle, consulte [Criptografia de rede nativa do Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html) para configurar a criptografia.
## Noções básicas da visualização e das permissões de pg\$1user\$1mappings
O catálogo PostgreSQL `pg_user_mapping` armazena o mapeamento de um usuário do Aurora PostgreSQL para o usuário em um servidor de dados externo (remoto). O acesso ao catálogo é restrito, mas você usa a visualização `pg_user_mappings` para ver os mapeamentos. Veja a seguir um exemplo que mostra como as permissões se aplicam a um banco de dados Oracle de exemplo, mas essas informações se aplicam de forma mais geral a qualquer wrapper de dados externo.
Na saída a seguir, você pode encontrar funções e permissões mapeadas para três usuários de exemplo diferentes. Usuários eo `rdssu1` e `rdssu2` são membros da função `rds_superuser`, e `user1` não é. O exemplo usa o metacomando `\du` do `psql` para listar as funções existentes.
```
test=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
rdssu1 | | {rds_superuser}
rdssu2 | | {rds_superuser}
user1 | | {}
```
Todos os usuários, incluindo aqueles com privilégios `rds_superuser`, têm permissão para visualizar seus próprios mapeamentos de usuário (`umoptions`) na tabela `pg_user_mappings`. Como mostrado no exemplo a seguir, quando `rdssu1` tenta obter todos os mapeamentos do usuário, é gerado um erro, mesmo com privilégios `rdssu1``rds_superuser`:
```
test=> SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
```
Veja a seguir alguns exemplos:
```
test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd}
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd}
(3 rows)
test=> SET SESSION AUTHORIZATION user1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+--------------------------------
16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd}
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
```
Devido às diferenças na implementação de `information_schema._pg_user_mappings` e `pg_catalog.pg_user_mappings`, um `rds_superuser` criado manualmente requer outras permissões para visualizar senhas no `pg_catalog.pg_user_mappings`.
Nenhuma permissão adicional é necessária para um `rds_superuser` visualizar senhas no `information_schema._pg_user_mappings`.
Usuários que não tenham a função `rds_superuser` poderão visualizar senhas em `pg_user_mappings` somente nas seguintes condições:
+ O usuário atual é o usuário que está sendo mapeado e é proprietário do servidor ou mantém o privilégio `USAGE` sobre ele.
+ O usuário atual é o proprietário do servidor, e o mapeamento é para `PUBLIC`.
# Trabalhar com bancos de dados do SQL Server usando a extensão tds\$1fdw
Você pode usar a extensão `tds_fdw` do PostgreSQL para acessar bancos de dados compatíveis com o protocolo de fluxo de dados tabular (TDS), como bancos de dados do Sybase e do Microsoft SQL Server. Esse invólucro de dados externos permite que você se conecte pelo cluster de banco de dados do Aurora PostgreSQL a bancos de dados que usam o protocolo TDS, incluindo o Amazon RDS for Microsoft SQL Server. Para obter mais informações, consulte a documentação do [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) no GitHub.
A extensão `tds_fdw` é compatível com o Amazon Aurora PostgreSQL versão 13.6 e posterior.
## Configurar o banco de dados do RDS for PostgreSQL para usar a extensão tds\$1fdw
Nos procedimentos a seguir, você pode encontrar um exemplo de configuração e uso de `tds_fdw` com um cluster de banco de dados do Aurora PostgreSQL. Antes de se conectar a um banco de dados do SQL Server usando `tds_fdw`, é necessário obter os seguintes detalhes da instância:
+ Nome de host ou endpoint. Para uma instância de banco de dados do RDS for SQL Server, você pode encontrar o endpoint usando o console. Escolha a guia “Connectivity & security” (Conectividade e segurança) e procure na seção “Endpoint and port” (Endpoint e porta).
+ Número da porta. O número da porta padrão para o Microsoft SQL Server é 1433.
+ O nome do banco de dados. O identificador do banco de dados.
Você também precisa fornecer acesso no grupo de segurança ou na lista de controle de acesso (ACL) para a porta 1433 do SQL Server. Tanto o cluster de banco de dados do Aurora PostgreSQL como a instância de banco de dados do RDS for SQL Server precisam de acesso à porta 1433. Se o acesso não estiver configurado corretamente, ao tentar consultar o Microsoft SQL Server, será exibida a seguinte mensagem de erro:
```
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
```
**Para usar tds\$1fdw a fim de se conectar a um banco de dados do SQL Server**
1. Conecte-se à instância primária do cluster de banco de dados do Aurora PostgreSQL usando uma conta que tenha a função `rds_superuser`:
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. Instale a extensão `tds_fdw`:
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
Depois que a extensão for instalada no cluster de banco de dados do Aurora PostgreSQL , configure o servidor externo.
**Para criar o servidor externo**
Execute essas tarefas no cluster de banco de dados do Aurora PostgreSQL usando uma conta com privilégios `rds_superuser`.
1. Crie um servidor externo no cluster de banco de dados do Aurora PostgreSQL:
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing');
CREATE SERVER
```
Para acessar dados não ASCII do lado do SQLServer, crie um link de servidor com a opção character\$1set no cluster de banco de dados do Aurora PostgreSQL:
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing', character_set 'UTF-8');
CREATE SERVER
```
1. Conceda uso a um usuário que não tenha permissões da função `rds_superuser`; por exemplo, `user1`:
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. Conecte-se como user1 e, em seguida, crie um mapeamento para um usuário do SQL Server:
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. Crie uma tabela externa vinculada a uma tabela do SQL Server:
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Consulte a tabela externa:
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
### Usar criptografia em trânsito para a conexão
A conexão do Aurora PostgreSQL com o SQL Server usa criptografia em trânsito (TLS/SSL), dependendo da configuração do banco de dados do SQL Server. Se o SQL Server não estiver configurado para criptografia, o cliente do RDS for PostgreSQL que faz a solicitação ao banco de dados do SQL Server retornará ao estado de não criptografado.
Você pode aplicar a criptografia para a conexão com instâncias de banco de dados do RDS for SQL Server definindo o parâmetro `rds.force_ssl`. Para saber como, consulte [Forçar conexões com a instância de banco de dados para usar SSL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing). Para obter mais informações sobre a configuração de SSL/TLS para o RDS for SQL Server, consulte [Usar SSL com uma instância de banco de dados do Microsoft SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html).