Administração do PostgreSQL no Debian 

Sumário Administração do PostgreSQL no Debian Roteiro ● Introdução ● Instalação ● Gerenciamento simplificado ● Otimização de desempenho (tunning) ● Segurança 

Administração do PostgreSQL no Debian Introdução 

Introdução História do PostgreSQL ● Projeto Ingres (1977-1985): nasceu na Universidade de Berkeley, liderado por Michael Stonebraker; ● Postgres (1986): início do projeto, inspirado no Ingres, criado para superá-lo; ● Em 1992 é criada a Illustra, versão comercial do Postgres, depois fundida com o Informix; ● Em 1993 a universidade de Berkely encerra o projeto na versão 4.2 ● Postgres95 (1994 - 1995): versão do Postgres com a linguagem SQL desenvolvido por 2 alunos de Berkeley ● Em 1996 o Postgres passa a se chamar PostgreSQL e começa a ser mantido pelo PGDG: PostgreSQL Global Development Group: ○ (1996 - 1998): Processo de estabilização do servidor ○ (1998 - 2000): Aderência aos padrões SQL ○ (2001 - 2011): Funcionalidades corporativas ○ (2012 em diante): Inovação 

Introdução O banco de dados que mais cresce nos últimos 10 anos, segundo o site DB-Engines Dados coletados em 24/04/2024 

Administração do PostgreSQL no Debian Instalação 

Instalação Instalação ● Instalação via repositório PGDG ● Instalação de mais de uma versão ● Instalação em disco/partição alternativa 

Instalação Instalação via repositório PGDG ● Página oficial de download para Debian (postgresql.org) ● Permite escolher a versão desejada e ter acesso a mais recente ● Configuração do repositório Apt: sudo apt install curl ca-certificates sudo install -d /usr/share/postgresql-common/pgdg sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.or g.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' sudo apt update sudo apt -y install postgresql 

Instalação Instalação via repositório PGDG 

Instalação Instalação de mais de uma versão Utilizar o mesmo comando e especificar a versão desejada: apt install postgresql-15 Checando as versões: dpkg -l | grep postgresql Saída: 

Instalação Instalação em disco/partição alternativa ● Recomendado para ambientes de produção ● Sempre utilizar uma partição separada com os dados (ext4 ou xfs) ● Utilizar uma partição separada para os logs em situações de DEBUG em servidores grandes ● Por padrão, o cluster fica em /var/lib/postgresql ● Para alterar pós-instalação, editar o createcluster.conf (/etc/postgresql-common/), especificamente em: # Default data directory. data_directory = '/mnt/postgresql/%v/%c' ● %v e %c representam a versão e o nome do cluster ● Criar um novo cluster: pg_createcluster 16 --start ; ● Conferir clusters com: pg_lsclusters 

Instalação Instalação em disco/partição alternativa pg_createcluster 16 prod --start 

Instalação Instalação em disco/partição alternativa Lista de clusters antes: Depois da mudança de partição e criação do novo: Para evitar confusões, dropar o cluster antigo: 

Administração do PostgreSQL no Debian Gerenciamento simplificado 

Gerenciamento simplificado Gerenciamento simplificado Utilitarios importantes: ● pg_lsclusters : lista clusters existentes ● pg_createcluster/pg_dropcluster : cria e remove clusters (apaga tudo!) ● pg_backupcluster/pg_restorecluster : backup / restore ● pg_upgradecluster : atualiza a major version de um cluster ● pg_ctlcluster : start / stop / restart / reload / promote Arquivo start.conf: ● Localizado em /etc/postgresql///start.conf ● Define se o cluster deverá ser inicializado automaticamente durante o boot através do systemd ● Opções: ○ auto: inicia automaticamente ○ manual: inicia manualmente ○ disabled: não inicia nem manualmente 

Gerenciamento simplificado pg_createcluster ● Utilizar o parâmetro --start para iniciar o serviço do cluster criado: pg_createcluster 16 dev --start 

Gerenciamento simplificado pg_dropcluster ● Comando sem saída; ● Utilizar o parâmetro --stop para parar o serviço do cluster e removê-lo com sucesso: pg_dropcluster 16 dev --stop 

Gerenciamento simplificado pg_backupcluster ● Cria o arquivo de backup físico do banco; ● Tem diversas opções, como por exemplo, formato de arquivo de saída, host, porta, usuário etc pg_backupcluster -v 16 main basebackup 

Gerenciamento simplificado pg_upgradecluster ● Faz upgrade da major version: ○ Homologue a nova versão antes de colocar em produção ○ Sempre faça um backup antes do upgrade pg_upgradecluster --method=upgrade --link 15 main 

Gerenciamento simplificado pg_restorecluster ● Restaura o backup físico criado pelo pg_backupcluster ● Cria um cluster novo implicitamente pg_restorecluster 16 prod /var/backups/postgresql/16-main/2024-04-29T113928Z.backup/ 

Gerenciamento simplificado pg_ctlcluster ● Opção promote: promove réplica standby para master ● Opções start/stop: melhor usar o systemd para isso! 

Administração do PostgreSQL no Debian Otimização de desempenho 

Otimização de desempenho Otimização de desempenho ● Tuning do sistema operacional ○ Ajustes comuns ○ Ajustes sob demanda ○ Erros comuns ○ Ajustes no Huge Pages e THP ○ Sistemas de Arquivos e particionamento ● Tuning do PostgreSQL (pgconfig.org) ● Pgbench- para teste de ajustes de parâmetros 

Otimização de desempenho Tuning do sistema operacional (sysctl.conf) ● Documentação: Managing Kernel Resources (postgresql.org) ● Método antigo: /etc/sysctl.conf Método novo: /etc/sysctl.d/90_postgresql.conf ● Para aplicar as mudanças: sysctl -p ● Parâmetros principais: ○ vm.dirty_ratio = 10 ○ vm.dirty_background_ratio = 5 ○ vm.overcommit_memory = 2 ○ vm.overcommit_ratio = 95 ○ vm.swappiness=1 ● Parâmetros secundários: ○ kernel.sem = 250 512000 100 2048 ○ net.core.somaxconn = 65535 ○ net.ipv4.tcp_tw_reuse = 1 ○ net.ipv4.tcp_fin_timeout = 5 ○ fs.file-max = 312139770 ○ fs.aio-max-nr = 1048576 

Otimização de desempenho Tuning do sistema operacional Ajustes sob demanda ● shmmax e shmall: ○ Script para calcular a memória compartilhada em ½ da RAM do servidor page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo kernel.shmmax = $shmmax echo kernel.shmall = $shmall ○ Ajustar os valores obtidos no sysctl.conf ● nofile ○ Método antigo: /etc/security/limits.conf Método novo: /etc/security/limits.d/90-postgresql.conf ○ postgres soft nofile 65535 postgres hard nofile 65535 

Sumário Tuning do Sistema Operacional Erros comuns ● Out of Memory: Killed process 12345 (postgres) ○ Ajustar os parâmetros: vm.overcommit_memory vm.overcommit_ratio vm.swappiness ● FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget ○ Ajustar os parâmetros: kernel.shmmax kernel.shmall ● FATAL: could not create semaphores: No space left on device DETAIL: Failed system call was semget(5440126, 17, 03600). ○ Ajustar o parâmetro: kernel.sem 

Sumário Tuning do Sistema Operacional Erros comuns ● LOG: out of file descriptors: Too many open files in system; release and retry PANIC: could not open file (...): Too many open files in system ○ Ajustar o parâmetro: nofile ● LOG: could not fork new process for connection: Resource temporarily unavailable ○ Ajustar os parâmetros: fs.file-max fs.aio-max-nr ● could not connect to server: Resource temporarily unavailable ○ Ajustar o parâmetro: net.core.somaxconn 

Otimização de desempenho Tuning do sistema operacional Ajustes no Huge Pages e THP (servidores com RAM >= 32GB) ● Desligar o THP (Transparent Huge Pages) ○ Adicionar o script em /etc/rc.local: if test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi if test -f /sys/kernel/mm/transparent_hugepage/defrag; then echo never > /sys/kernel/mm/transparent_hugepage/defrag 

Otimização de desempenho Tuning do sistema operacional Servidores com RAM >= 32GB ● Configurar o Huge Pages: ○ O número de páginas deve ser o valor do shared_buffers + 10%, dividido pelo tamanho da página. O valor do huge pages deve ser = (shared_buffers(em MB) * 1,1)/ 2. Ex: shared_buffers = 16GB hugepages = (16 * 1024 *1,1) / 2 = 9011 ○ Editar a linha GRUB_CMDLINE_LINUX no arquivo /etc/default/grub GRUB_CMDLINE_LINUX="... hugepages= 9011 hugepagesz=2M transparent_hugepage=never" ○ Executar como root ou sudo: update-grub ○ Reiniciar o servidor: reboot 

Otimização de desempenho Tuning do sistema operacional Sistemas de arquivos e particionamento ● Utilizar uma partição separada para os logs em situações de DEBUG em servidores grandes ○ Ajustar o parâmetro log_directory no postgresql.conf ● Utilizar uma partição separada para os arquivos temporários em servidores com bases grandes e consultas muito pesadas (Data Warehouse, Data Lake, etc). ○ Ajustar o parâmetro temp_tablespaces no postgresql.conf ● Utilizar uma partição separada para os logs de transação em servidores OLTP com alto volume de transações. ○ Criar um link simbólico para $PGDATA/pg_wal 

Otimização de desempenho Tuning do sistema operacional Sistemas de arquivos e particionamento ● Tablespaces: ○ Criar novos tablespaces para separar dados de discos rápidos de discos lentos (geralmente utilizados para dados históricos) ○ Quando lidar com vários discos idênticos, ao invés de utilizar vários tablespaces, crie um RAID com mais discos. ● Utilizar ext4 ou xfs para os dados: ○ Sempre montar os discos com UUID no /etc/fstab ○ Utilizar a opção noatime com ext4 ● Ajustar o Read-Ahead para o discos dos dados entre 4096 e 16384: ○ Ajustar no /etc/rc.local: blockdev --setra 4096 /dev/sda1 

Otimização de desempenho Tuning do PostgreSQL ● Diferentes formas de ajustar dezenas de parâmetros ○ Ajustes direto em /etc/postgresql///postgresql.conf ○ Ajustes estilo system.d (com o parâmetro include_dir) em /etc/postgresql///conf.d/.conf ○ Ajustes em arquivo aleatório com o parâmetro include ○ Ajustes com comando SQL ALTER SYSTEM, armazenados em /var/lib/postgresql///postg resql.auto.conf (não editar este arquivo manualmente) ○ Utilize um e apenas um método ○ Recomendamos ajustar via comandos SQL 

Otimização de desempenho Recomendação inicial de ajustes no site pgconfig.org 

Otimização de desempenho Tuning do PostgreSQL pgconfig.org Exportando configurações para UNIX 

Otimização de desempenho Tuning do PostgreSQL pgconfig.org Exportando configurações para comandos ALTER SYSTEM 

Administração do PostgreSQL no Debian Segurança 

Segurança Segurança Boas práticas ● Utilizar servidores dedicados em produção: ○ Não instalar interface gráfica ○ Instale apenas o mínimo necessário ● Não utilizar IPs públicos ● Restringir ao máximo as conexões no pg_hba.conf ○ Não use autenticação TRUST ○ Não utilize o intervalo de redes 0.0.0.0/0 ○ Utilize conexões encriptadas com hostssl ● Crie usuários separados no banco para funções separadas: ○ DBAs / administradores ○ Deploy / owner de objetos ○ Replicação / Backup ○ Aplicações 

Segurança Segurança Simplificando a vida: ● Variáveis de ambiente ● .pg_pass ● .pg_service.conf ● psql ○ .psqlrc 

Save the date! 07 e 08 de novembro: PGConf.Brasil2024 PUC Belo Horizonte - MG ● PGConf.Brasil Instagram ● PGConf.Brasil Telegram ● PostgreSQL BR Telegram ● PostgreSQL (English) Telegram 

Dúvidas? Contatos: ● Savepoint ● Slides: Speaker decker (Ludmila Mariana) ● Email profissional: [email protected] ● Email pessoal: [email protected] ● LinkedIn: Ludmila Chagas