Tunning em banco de dados Postgres

1. Editando as configurações do banco.

Edite o arquivo postgresql.conf:

# nano /usr/local/pgsql/data/postgresql.conf .

Altere as sentenças nos seguintes grupos para:

# CONNECTIONS AND AUTHENTICATION (refere-se a parte de conexões e autenticação do banco)

listen_addresses = '*' #ip que ele está escutando
port = 5432 #porta postgres
max_connections = 40 #número de conexões ao banco, depende da quantidade de memória deste

# RESOURCE USAGE (esse grupo se refere ao uso de recursos utilizados pelo banco)

# Memory
shared_buffers = 8192 #min 16MB ou 2x o max_connections
temp_buffers = 8192 #min 100, 8KB each
work_mem = 65536 #min 64, size in KB
maintenance_work_mem = 524288 #min 1024, size in KB
max_stack_depth = 8192 #min 100, size in KB

# Free Space Map
max_fsm_pages = 120000 #min max_fsm_relations*16, 6 bytes each


# WRITE AHEAD LOG (refere-se a escrita dos logs na base do banco)

# Settings
wal_buffers = 512 #min 4, 8KB each


# QUERY TUNING (refere-se à otimização das querys do banco, lembrando que habilitar alguma tag nesse grupo não significa que você melhorá-lo)

# Planner Cost Constants
effective_cache_size = 8192000 #typically 8KB each
random_page_cost = 2.0 #units are one sequential page fetch


# ERROR REPORTING AND LOGGING (refere-se a coleta e ao armazenamento dos logs de erros do banco)

log_destination = 'stderr'
redirect_stderr = on #log do banco em um arquivo separado
log_directory = '/var/log/pgsql' #diretório dos logs
log_filename = '%Y-%m-%d_%H%M%S.log' #nome do log
log_rotation_size = 10240 #rotação automatica dos logs
silent_mode = off #habilita os logs no terminal


#Query/Index Statistics Collector
stats_start_collector = on
stats_row_level = on


# AUTOVACUUM PARAMETERS (refere-se ao vaccuum do banco, rotina que serve para otimizar e limpar o lixo deixado na base do banco)

autovacuum = on #habilitar o autovaccum
autovacuum_naptime = 600 #Tempo entre rodar os autovaccums em segundos
autovacuum_vacuum_threshold = 1000 #min de updates depois do vaccum
autovacuum_analyze_threshold = 500 #min de updates depois da analize

Crie uma pasta para armazenar os logs e após altere suas permissões:

# mkdir /var/log/pgsql
# chmod -R 775 /var/log/pgsql
# chown -R pgsql:pgsql /var/log/pgsql

2. Adicionando Segurança.

Edite o arquivo pg_hba.conf para liberação de acesso externo ao banco:

# vi /usr/local/pgsql/data/pg_hba.conf

Caso outro servidor ou rede precise, acesse esse banco, adicione os IPs dos mesmos no grupo IPv4 local connections (Não retirar a permissão do 127.0.0.1).

Segue o formato:

a) liberação para de uma rede especifica: 

host all all 192.168.0.0/24 trust  

b) liberação para um determinado IP:

host all all 192.168.0.1/32 trust

3. Crie um usuário no banco para acessar ao banco.

Para aumentar a segurança usaremos um usuário próprio no banco de dados:

# createuser -U pgsql (nome do usuário que você quer criar) -W