Usar pgAudit para registrar a atividade do banco de dados Usar pglogical para sincronizar dados

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_partman 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.

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_cron do PostgreSQL. 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 (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.

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 (Versões de extensão para o Amazon Aurora PostgreSQL) nas Release Notes for Aurora PostgreSQL (Notas de versão do Aurora PostgreSQL).

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 do Aurora PostgreSQL.

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 seu cluster de banco de dados do Aurora PostgreSQL, 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.

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 (Versões de extensão para o Amazon Aurora PostgreSQL) em Release Notes for Aurora PostgreSQL (Notas de versão do Aurora PostgreSQL).

Tópicos

Configurar a extensão pgAudit
Auditar objetos de banco de dados
Excluir usuários ou bancos de dados do registro em log de auditoria
Referência para a extensão pgAudit

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 Trabalhar com grupos de parâmetros. 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 AWS Management Console 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.

Como configurar a extensão pgAudit

Faça login no AWS Management Console e abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.
No painel de navegação, selecione sua instância do gravador do cluster de banco de dados do Aurora PostgreSQL.
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).
Clique no link para abrir os parâmetros personalizados associados ao seu cluster de banco de dados do Aurora PostgreSQL.
No campo Parameters (Parâmetros), digite shared_pre para encontrar o parâmetro shared_preload_libraries.
Selecione Edit parameters (Editar parâmetros) para acessar os valores das propriedades.
Adicione pgaudit à lista no campo Values (Valores). Use uma vírgula para separar itens na lista de valores.
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.
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. 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;
```
Feche a sessão psql.
```
labdb=> \q
```
Faça login no AWS Management Console e abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.
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.

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).
Escolha Save changes (Salvar alterações).
Abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.
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).

Como configurar a pgAudit

Para configurar a pgAudit usando a AWS CLI, chame a operação modify-db-parameter-group para modificar os parâmetros do log de auditoria em seu grupo de parâmetros personalizado, conforme mostrado no procedimento a seguir.

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

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
```
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;
```
Feche a sessão psql para que você possa usar a AWS CLI.
```
labdb=> \q
```
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.

Imagem do arquivo de log do PostgreSQL depois de configurar a pgAudit.

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

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

Crie uma função de banco de dados chamada rds_pgaudit usando o comando a seguir.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=> 
```
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.

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

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
```
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.

Para saber mais sobre a extensão pgAudit, consulte pgAudit 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 do Aurora PostgreSQL, 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_specific_settings 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 obter mais informações, consulte Lista de configurações permitidas para o parâmetro pgaudit.log.


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.

Parâmetro	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.
`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_catalog.
`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
none	Esse é o padrão. Nenhuma alteração no banco de dados é registrada.
tudo	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`.
read	Registra `SELECT` e `COPY` quando a fonte é uma relação (como uma tabela) ou uma consulta.
função	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 Usar a replicação lógica do PostgreSQL com o Aurora.

A extensão pglogical é compatível com a replicação lógica entre dois ou mais clusters de banco de dados do 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 do RDS para PostgreSQL e clusters de banco de dados do 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 do 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 do 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.

Tópicos

Requisitos e limitações da extensão pglogical
Configurar a extensão pglogical
Configurar a replicação lógica para o cluster de banco de dados do Aurora PostgreSQL
Restabelecer a replicação lógica após uma atualização principal
Gerenciar slots de replicação lógica para Aurora PostgreSQL
Referência de parâmetros da extensão pglogical

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 assinante para editor 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 (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 do 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 do 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 AWS Management Console 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 seu cluster de banco de dados do Aurora PostgreSQL esteja 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 Trabalhar com grupos de parâmetros.

Como configurar a extensão pglogical

Faça login no AWS Management Console e abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.
No painel de navegação, selecione sua instância do gravador do cluster de banco de dados do Aurora PostgreSQL.
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).
Clique no link para abrir os parâmetros personalizados associados ao seu cluster de banco de dados do Aurora PostgreSQL.
No campo Parameters (Parâmetros), digite shared_pre para encontrar o parâmetro shared_preload_libraries.
Selecione Edit parameters (Editar parâmetros) para acessar os valores das propriedades.
Adicione pglogical à lista no campo Values (Valores). Use uma vírgula para separar itens na lista de valores.
Encontre o parâmetro rds.logical_replication e defina-o como 1 para ativar a replicação lógica.
Reinicialize a instância do gravador do cluster de banco de dados do Aurora PostgreSQL para que suas alterações tenham efeito.
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 do Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```

Para verificar se pglogical foi inicializada, recomendamos que siga este comando.


SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pglogical
(1 row)

Verifique a configuração que permite a decodificação lógica, da forma a seguir.
```
SHOW wal_level;
wal_level
-----------
 logical
(1 row)
```

Crie a extensão da forma a seguir.


CREATE EXTENSION pglogical;
EXTENSION CREATED

Escolha Save changes (Salvar alterações).
Abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.
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).

Como configurar a extensão pglogical

Para configurar a pglogical usando a AWS CLI, chame a operação modify-db-parameter-group para modificar determinados parâmetros em seu grupo de parâmetros personalizado, conforme mostrado no procedimento a seguir.

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

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

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 pglogical seja inicializada.
```
aws rds reboot-db-instance \
    --db-instance-identifier writer-instance \
    --region aws-region
```
Quando a instância estiver disponível, use psql para se conectar à instância do gravador do cluster de banco de dados do Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```

Crie a extensão da forma a seguir.


CREATE EXTENSION pglogical;
EXTENSION CREATED

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 do Aurora PostgreSQL

O procedimento a seguir mostra como iniciar a replicação lógica entre dois clusters de banco de dados do 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.

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 psq1 \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.

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);
```
2. 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
```
3. Verifique se os dados existem na tabela usando a declaração SQL a seguir.
```
SELECT count(*) FROM docs_lab_table;
```

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)

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 (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.

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

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);
```

Verifique se essa tabela está vazia.


SELECT count(*) FROM docs_lab_table;
 count
-------
  0
(1 row)

Identifique esse cluster de banco de dados do 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)

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á 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.

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

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)

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)

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)

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 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 (Replicação bidirecional do PostgreSQL usando pglogical).

Parâmetro	Descrição
pglogical.batch_inserts	Inserções em lote, se possível. Não definido por padrão. Mude para “1” para ativar, “0” para desativar.
pglogical.conflict_log_level	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_resolution	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_remote, keep_local, last_update_wins, first_update_wins.
pglogical.extra_connection_options	Opções de conexão para adicionar a todas as conexões de nó de pares.
pglogical.synchronous_commit	Valor de confirmação síncrona específica do pglogical
pglogical.use_spi	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 (Interface de programação de servidores) na documentação do PostgreSQL.

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

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

Convenções do documento

Referência da visualização apg_plan_mgmt.dba_plans

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