Posted on: December 02, 2021 09:51 PM
Posted by: Renato
Categories: database postgresql dba sql
Views: 1104
Recomendações PostgreSQL
Para requisitos que se aplicam a todos os tipos de banco de dados, consulte Preparar um banco de dados para Deep Security Manager. Prepare a database for Deep Security Manager.
1 - Para preparar um banco de dados PostgreSQL para Deep Security Manager, crie sua conta de usuário do banco de dados e conceda permissões:
CREATE DATABASE "<database-name>";
CREATE ROLE "<dsm-username>" WITH PASSWORD '<password>' LOGIN;
GRANT ALL ON DATABASE "<database-name>" TO "<dsm-username>";
GRANT CONNECT ON DATABASE "<database-name>" TO "<dsm-username>";
.Se o Deep Security Manager tiver vários locatários, conceda também o direito de criar novos bancos de dados e funções para os locatários:
ALTER ROLE <dsm-username> CREATEDB CREATEROLE;
2 - Se as conexões entre o Deep Security Manager e o PostgreSQL usarem uma rede não confiável, considere o uso de TLS para melhorar a segurança. Consulte Criptografar a comunicação entre o Deep Security Manager e o banco de dados.
between Deep Security Manager and the database.
3 - Defina as configurações de rotação e desempenho do log do banco de dados.
Para melhores práticas, consulte as configurações de registro, gerenciamento de bloqueio, conexões máximas, configurações de Autovacuum, etc.
As etapas variam de acordo com a distribuição e hospedagem gerenciada:
* Banco de dados auto-hospedado: os padrões são valores genéricos da distribuição central do PostgreSQL. Alguns padrões não são apropriados para centros de dados ou instalações de nuvem personalizadas, especialmente em implantações maiores.
Para alterar as configurações:
- In a plain text editor, open the postgresql.conf file.
- Edit the parameters.
- Save the file.
- Restart the PostgreSQL service.
* Amazon RDS: os padrões variam de acordo com o tamanho da instância. Freqüentemente, você só precisa ajustar autovacuuming, max_connections e effective_cache_size. Para alterar as configurações, use os grupos de parâmetros do banco de dados e reinicie a instância do banco de dados. database parameter groups
* Amazon Aurora: os padrões variam de acordo com o tamanho da instância. Freqüentemente, você só precisa ajustar autovacuuming, max_connections e effective_cache_size. Para alterar as configurações, use os grupos de parâmetros do banco de dados e reinicie a instância do banco de dados. database parameter groups
=> Ao ajustar o desempenho, verifique as configurações monitorando o IOPS do seu banco de dados com um serviço como o Amazon CloudWatch.
Se você precisar de ajuda adicional, o PostgreSQL oferece suporte profissional.
Ajustando as configurações do PostgreSQL
Configurações de registro
Por padrão, os arquivos de log do PostgreSQL não são girados, o que pode fazer com que os arquivos de log usem uma grande quantidade de espaço em disco. Ao usar PostgreSQL com Deep Security, recomendamos que você use estes quatro parâmetros no arquivo postgresql.conf para configurar a rotação de log:
- log_filename
- log_rotation_age
- log_rotation_size
- log_truncate_on_rotation
log_rotation_age e log_rotation_size controlam quando um novo arquivo de log é criado. Por exemplo, definir log_rotation_age como 1440 criará um novo arquivo de log a cada 1440 minutos (1 dia) e definir log_rotation_size como 10000 criará um novo arquivo de log quando o anterior atingir 10.000 KB.
log_filename controla o nome dado a cada arquivo de log. Você pode usar a conversão de formato de data e hora no nome. Para obter uma lista completa, consulte https://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html.
Quando log_truncate_on_rotation é definido como "on", ele sobrescreverá qualquer arquivo de log que tenha o mesmo nome de um arquivo de log recém-criado.
Existem várias combinações de parâmetros que você pode usar para obter uma rotação de log para atender aos seus requisitos. Aqui está um exemplo:
- log_filename = 'postgresql-%a.log' (every log file has the first 3 letters of the weekday in its name)
- log_rotation_age = 1440 (a new log file is created daily)
- log_rotation_size = 0. (setting is disabled to prevent the overwriting of the daily log file every time this limit is exceeded)
- log_truncate_on_rotation = on (enable log file overwrite)
Gerenciamento de bloqueio
Por padrão, a configuração deadlock_timeout no arquivo postgresql.conf é configurada para 1 segundo. Isso significa que toda vez que uma consulta espera por um bloqueio por mais de 1 segundo, o PostgreSQL iniciará uma verificação de condição de deadlock e registrará um erro se a configuração de registro tiver sido configurada dessa forma (por padrão, é). Isso pode levar à degradação do desempenho em sistemas maiores, onde pode ser normal que as consultas aguardem mais de 1 segundo durante o tempo de carregamento. Em sistemas grandes, considere aumentar a configuração de deadlock_timeout. A documentação do PostgreSQL contém esta recomendação: "Idealmente, a configuração deve exceder o tempo normal de transação [...]".
Conexões máximas
A configuração max_connections no arquivo postgresql.conf especifica o número máximo de conexões abertas com o banco de dados. O valor padrão é 100. Recomendamos aumentar esse valor para 500.
Buffers compartilhados
A configuração shared_buffers no arquivo postgresql.conf especifica quanta memória o PostgreSQL pode usar para armazenar dados em cache. Um sistema com 1 GB de RAM deve ter um quarto de seu valor de memória para buffer compartilhado, o que significa que o buffer compartilhado deve ser definido para 256 MB (o padrão é 32 MB).
Memória de trabalho e memória de trabalho de manutenção
A configuração work_mem no arquivo postgresql.conf especifica a quantidade de memória que pode ser usada por operações de classificação interna e tabelas de hash antes de gravar em arquivos de disco temporários. O valor padrão é 1 MB, mas deve ser aumentado ao executar consultas complexas. A configuração maintenance_work_mem determina a quantidade máxima de memória usada para operações de manutenção, como ALTER TABLE.
Tamanho efetivo do cache
A configuração effective_cache_size no arquivo postgresql.conf é usada para estimar os efeitos do cache por uma consulta. Essa configuração afeta apenas as estimativas de custo durante o planejamento da consulta e não resulta em maior consumo de memória. Considere aumentar essa configuração.
Pontos de verificação
Checkpoints
Os pontos de verificação geralmente são a principal fonte de gravações em arquivos de dados. Para obter o desempenho mais suave, a maioria dos pontos de verificação deve ser "cronometrada" (acionada por checkpoint_timeout) e não "solicitada" (acionada pelo preenchimento de todos os segmentos WAL disponíveis ou por um comando CHECKPOINT explícito). Recomendamos enfaticamente que você torne os pontos de verificação menos frequentes.
| Parameter name | Recommended value |
|---|---|
| checkpoint_timeout | 15min |
| checkpoint_completion_target | 0.9 |
| max_wal_size | 16GB |
Log de write-ahead (WAL)
Se você usar a replicação de banco de dados, considere usar wal_level = replica.
Configurações de Autovacuum
O PostgreSQL requer manutenção periódica chamada "aspiração". Normalmente, você não precisa alterar o valor padrão para autovacuum_max_workers.
Nas tabelas de entidade e attribute2s, se as gravações frequentes causam a alteração frequente de muitas linhas (como em grandes implantações com instâncias de nuvem de curta duração), o autovacuum deve ser executado com mais frequência para minimizar o uso do espaço em disco e manter o desempenho. Os parâmetros devem ser definidos no banco de dados geral e nas tabelas específicas.
| Database-level parameter name | Recommended value |
|---|---|
| autovacuum_work_mem |
1GB |
| Table-level parameter name | Recommended value |
|---|---|
| autovacuum_vacuum_cost_delay |
10 |
| autovacuum_vacuum_scale_factor |
0.01 |
| autovacuum_analyze_scale_factor |
0.005 |
Para alterar a configuração no nível do banco de dados, você deve editar o arquivo de configuração ou o grupo de parâmetros do banco de dados e, em seguida, reinicializar o servidor de banco de dados. Os comandos não podem alterar essa configuração enquanto o banco de dados está em execução.
Para alterar as configurações no nível da tabela, você pode editar o arquivo de configuração ou o grupo de parâmetros do banco de dados ou inserir estes comandos:
ALTER TABLE public.entitys SET (autovacuum_enabled = true, autovacuum_vacuum_cost_delay = 10, autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005);
ALTER TABLE public.attribute2s SET (autovacuum_enabled = true, autovacuum_vacuum_cost_delay = 10, autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005);
Alta disponibilidade
A alta disponibilidade (HA) não é definida por padrão e não foi habilitada em nosso ambiente de teste, mas é altamente recomendável para garantir a continuidade dos negócios no caso de mau funcionamento do banco de dados ou inacessibilidade do servidor. Consulte a documentação do PostgreSQL para obter informações sobre como habilitar e configurar HA.
Restaurar e recuperar
O backup e a recuperação não são definidos por padrão, mas são absolutamente essenciais em um ambiente de produção.
Note: Ferramentas básicas como pg_dump ou pg_basebackup não são adequadas para backups em um ambiente corporativo. Considere o uso de outras ferramentas como o Barman (https://www.pgbarman.org/index.html) para backup e recuperação.
Recomendações Linux
Páginas enormes transparentes (Linux)
Transparent Huge Pages (THP) é um sistema de gerenciamento de memória Linux que reduz a sobrecarga de pesquisas de Translation Lookaside Buffer (TLB) em máquinas com grandes quantidades de memória usando páginas de memória maiores. O THP é habilitado por padrão no Linux, mas não é recomendado para computadores que executam um banco de dados e deve ser desabilitado se o PostgreSQL estiver instalado em um computador Linux. Consulte a documentação do fornecedor do sistema operacional para obter detalhes.
Reforçar a autenticação baseada em host (Linux)
Por padrão, o Linux não possui autenticação baseada em host (HBA) restrita para bancos de dados. Fortalecer as configurações de HBA em um dispositivo de banco de dados ajuda a prevenir o acesso não autorizado de hosts externos. As configurações do HBA restringem o acesso a um intervalo de endereços IP para que apenas os hosts dentro desse intervalo tenham acesso. As configurações de HBA não foram usadas em nosso ambiente de teste e não as recomendamos.
Fonte:
https://help.deepsecurity.trendmicro.com/11_0/on-premise/postgresql-tuning.html
https://www.postgresql.org/docs/current/runtime-config-file-locations.html
Donate to Site
Renato
Developer