PostgreSQL: administração e monitoramento

Apresentação e configuração do PostgreSQL - Apresentação

Olá, meu nome é Victorino Vila e estou muito feliz em ter você para aprender mais sobre administração de bancos de dados PostgreSQL.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode e está com uma camiseta azul-marinho. Ao fundo, parede azul sem decorações.

Neste curso de administração e monitoramento, abordaremos desde a instalação e configuração inicial até a administração avançada e otimização de performance, utilizando sempre a empresa fictícia Frutally como nosso estudo de caso.

O que vamos aprender?

Começaremos com uma instalação detalhada do PostgreSQL em ambiente Windows, utilizando a base de dados Frutally como exemplo prático. Aprenderemos também os procedimentos para recuperação e restauração da base de dados utilizando arquivos externos CSV e como ajustar os parâmetros básicos do PostgreSQL logo após a instalação.

Na parte de administração, entenderemos como gerenciar a instância do PostgreSQL. Também exploraremos ajustes e parâmetros de performance para otimização, bem como técnicas de configuração de conexão e pooling para melhorar a eficiência do banco de dados.

Em seguida, entraremos no monitoramento, onde usaremos ferramentas nativas para monitorar os recursos de sistema, como CPU, memória e I/O de disco.

Especificamente para o PostgreSQL, aprenderemos a utilizar as visões pg_stat_activity e pg_stat_statements para monitorar e identificar gargalos e problemas relacionados com as conexões. Conheceremos métodos de identificação de gargalos dentro do PostgreSQL e técnicas para resolver problemas comuns, utilizando sempre a base de dados Frutally.

Exploraremos o uso do pgAdmin para administração e monitoramento, incluindo a visualização de métricas e logs. Além disso, aprenderemos a utilizar ferramentas de linha de comando, como PL/SQL e scripts em PowerShell, para a administração diária e a automação das tarefas.

Finalmente, na parte de manutenção, discutiremos a importância da manutenção preventiva, usando o comando VACUUM para remover as tuplas mortas e liberar espaço em disco. Também discutiremos como usar o comando ANALYZE para coletar estatísticas sobre o conteúdo das tabelas, melhorando o desempenho das consultas.

Ademais, abordaremos o planejamento e execução de atualizações de versões e patches de segurança, além das estratégias para o planejamento de capacidade e escalabilidade, garantindo que o PostgreSQL possa lidar com o aumento de carga de trabalho, sem comprometer a performance.

Público-alvo

Este curso é uma oportunidade valiosa para qualquer profissional de TI que busca aprimorar suas habilidades em administração de bancos de dados PostgreSQL. Ao dominar essas técnicas, você será capaz de garantir a eficiência e a segurança dos dados em qualquer organização.

Vamos seguir juntos nessa nova jornada de aprendizado, começando com a configuração do PostgreSQL no próximo vídeo!

Apresentação e configuração do PostgreSQL - Estudo de caso e instalando o produto

Para começar este curso, vamos falar sobre o estudo de caso que utilizaremos em toda a formação de PostgreSQL, além de instalar o banco de dados PostgreSQL.

Conhecendo o estudo de caso

Durante este curso e em toda a formação, assumiremos o papel de DBA (administrador de banco de dados) da empresa Frutally.

A Frutally é uma das principais empresas de sucos no mercado, especializada na produção e venda de sucos de frutas naturais de diversos sabores, além de águas aromatizadas e mate gelado. A Frutally está presente nos principais estados do Brasil e vende diretamente ao público em geral.

À medida que avançamos no curso e na formação, compreenderemos melhor a base de dados da empresa Frutally.

A nossa primeira tarefa na empresa, como pessoa DBA, foi realizar uma concorrência para decidir qual banco de dados utilizar. Optamos pelo PostgreSQL, o qual será responsável por gerenciar a base de dados de vendas da Frutally.

Agora, a pessoa administradora do banco deve instalar o produto, não apenas em um computador simples, mas também em ambientes de servidores corporativos.

Como não estamos trabalhando com um servidor corporativo, instalaremos o PostgreSQL em nosso computador pessoal. Contudo, durante a instalação, explicaremos todos os modos disponíveis, pois, é essencial que o DBA entenda cada cenário que pode ser encontrado ao instalar o PostgreSQL em um ambiente corporativo.

Instalando PostgreSQL

Vamos iniciar a instalação do PostgreSQL. Abriremos uma nova sessão do navegador e buscar pelo site PostgreSQL Global Development, o grupo que gerencia o código aberto desse banco de dados. Esse grupo é composto por várias pessoas desenvolvedoras que contribuem para o desenvolvimento do PostgreSQl.

Na página inicial, podemos fazer o download para instalar o produto em nossa máquina, apertando o botão de "Download". Em seguida, devemos escolher o sistema operacional em que o produto será instalado. No nosso caso, selecionamos "Windows".

Depois, clicamos no link "Download the installer" para conferir a lista de versões do PostgreSQL disponíveis para download. No momento da gravação deste vídeo, a versão mais recente é a 16.3. Se outras versões estiverem disponíveis quando você assistir esse vídeo, sempre instale a versão mais atual.

Clicamos no botão para instalar a versão para Windows 64 bits e aguardamos o download. Após o download do instalador, vamos executá-lo.

Local da instalação

Na página de boas-vindas, clicamos no botão "Next" para seguir para a próxima etapa. A primeira pergunta do instalador será sobre o diretório onde o produto será instalado.

Em um ambiente corporativo, é importante consultar a área de infraestrutura para determinar o melhor local para a instalação, tanto do produto quanto dos dados. O DBA e a infraestrutura da empresa devem selecionar o local ideal para a instalação desses componentes.

Nesse caso, utilizaremos o caminho padrão, que é no diretório C:\Program Files\PostgreSQL\16, sendo o número da versão. Podemos apertar em "Next".

Componentes

Nessa etapa, podemos instalar quatro módulos:

• PostgreSQL Server: o servidor é o principal componente do PostgreSQL, que instalará o núcleo do sistema de banco de dados em nossa máquina, possibilitando sua manipulação;
• pgAdmin 4: ferramenta de administração e desenvolvimento do PostgreSQL, que facilita a administração do banco de dados por meio de uma interface gráfica;
• Stack Builder: ferramenta que facilita a instalação de componentes adicionais do PostgreSQL, como componentes ligados a dados geoespaciais, linguagem procedural, e drivers de comunicação para trabalhar com programas em Python, Java, .NET, entre outros.
• Command Line Tools: oferece ferramentas de linha de comando para trabalhar com o PostgreSQL, incluindo o utilitário PL/SQL, pg_dump para backup, pg_restore para restauração e pg_ctl para gerenciar o servidor PostgreSQL.

Nesta instalação, não instalamos nenhum componente adicional, apenas os quatro módulos principais. Caso você precise de uma funcionalidade especial, abra o Stack Builder e escolha o componente desejado.

Como queremos fazer uma instalação completa, vamos deixar marcado todos os quatro componentes e clicar no botão "Next".

Diretório de dados

Na próxima tela, escolha o local do disco onde os dados serão armazenados. Em um ambiente corporativo, esse diretório é crucial. Sempre devemos escolher um local com um disco SSD para processos de escrita e leitura mais rápidos, além de ter espaço para crescimento, pois a base de dados aumentará.

Normalmente, em uma empresa, o disco onde a base de dados é criada é diferente do disco onde o programa está instalado, podendo estar em outra máquina ou em um servidor de armazenamento de arquivos. Consulte a infraestrutura para determinar o melhor local para o diretório de dados.

Para esse curso, manteremos o padrão e clicaremos em "Next".

Senha

Na etapa de "Password", precisamos inserir uma senha do superusuário chamado postgres.

Em um ambiente real, é importante definir uma senha forte e segura, pois esse superusuário tem permissões totais sobre o sistema de banco de dados. Uma senha fraca pode comprometer a segurança dos dados.

Para nossos propósitos educacionais, utilizaremos uma senha simples. Usaremos o mesmo nome do usuário, postgres. Devemos repetir a senha e clicar em "Next".

Porta

A próxima informação é o número da porta, que é o caminho onde o servidor PostgreSQL escutará as conexões dos clientes. A porta padrão é 5432. Quase todas as instalações do PostgreSQL utilizam essa porta.

Em um ambiente corporativo, verifique se a porta está disponível para uso pelo PostgreSQL. Caso esteja em uso por outro aplicativo, escolha uma porta diferente.

Usar a porta padrão tem vantagens e desvantagens. Uma vantagem é que é uma porta conhecida, facilitando para outro DBA identificar que o PostgreSQL está escutando por ela. No entanto, por ser amplamente conhecida, pode ser uma vulnerabilidade para ataques, pois pessoas mal-intencionadas podem tentar usá-la para acessar o banco de dados sem permissão.

Para esse curso, manteremos o padrão e clicaremos em "Next".

Opções avançadas

Na janela de "Advanced Options", podemos escolher o locale que se refere às configurações regionais que determinam como o PostgreSQL formatará os dados. Podemos escolher um país específico ou manter a opção "default locale", que utiliza as configurações regionais do sistema operacional.

Escolher corretamente essa informação é importante, pois determina, por exemplo, o formato de data (dia/mês/ano ou mês/ano/dia) e até o separador decimal e milhar. Em alguns idiomas, a ordenação dos dados por letras é diferente.

Tenha cuidado se o PostgreSQL for utilizado em um ambiente multilinguagem ou acessado por diversos países.

No nosso caso, manteremos a opção "default locale" e clicaremos no botão "Next".

Conclusão

Em seguida, o instalador apresenta um resumo de tudo que será instalado. Podemos clicar em "Next" duas vezes e aguardar a conclusão da instalação.

Após a instalação, a caixa de diálogo final pergunta se desejamos acessar o Stack Builder, a ferramenta que permite baixar e instalar módulos adicionais, drivers e aplicativos que complementam a instalação do PostgreSQL, como extensões e ferramentas administrativas. É importante que o DBA conheça todos esses componentes.

Neste momento, não instalaremos nenhum componente adicional, mas disponibilizaremos uma atividade de "Para Saber Mais" sobre cada um deles.

Caso queiramos conferir os componentes, basta manter a opção marcada e clicar no botão "Finish". No Stack Builder, devemos escolher a instância do banco e clicar no botão "Next" para visualizar os componentes extras disponíveis.

No entanto, clicaremos em "Cancel", pois não instalaremos nenhum componente adicional agora. Fecharemos a caixa de diálogo e, assim, concluímos a instalação do PostgreSQL.

Apresentação e configuração do PostgreSQL - Recuperação da base de dados

Vamos acessar o PostgreSQL e, para isso, vamos abrir o aplicativo pgAdmin.

Acessando PostgreSQL

No menu do Windows, navegamos até a letra P para encontrar o aplicativo do "PostgreSQL 16", onde clicaremos em "pgAdmin 4" com o botão direito do mouse e escolheremos "Mais > Abrir local do arquivo".

Vamos copiar o arquivo pgAdmin, que é o executável da aplicação, e colá-lo como um atalho na área de trabalho, pois acessaremos esse aplicativo frequentemente durante toda a formação.

Para garantir que executaremos o programa corretamente, clicamos com o botão direito do mouse sobre o ícone do pgAdmin, acessamos as "Propriedades". Na aba "Atalho", clicamos no botão "Avançados", selecionamos a opção para sempre executar como administrador da máquina e clicamos "OK". Em seguida, clicamos no botão "Aplicar" e em "OK".

Feito isso, podemos dar um duplo clique sobre o pgAdmin para abrir a aplicação.

A interface do pgAdmin é constituída de uma barra de menu superior e uma janela dividida em dois painéis: o explorador de objetos à esquerda e um navegador com guias à direita.

À direita, a página de boas-vindas contém alguns links interessantes: "Add New Server" é um link rápido para adicionar um novo servidor; "Configure pgAdmin" permite configurar temas e opções de comportamento da interface, além de preferências pessoais para quando utilizarmos o pgAdmin.

Abaixo, encontramos a documentação do Postgres, um recurso valioso para aprender mais sobre o banco de dados e suas funcionalidades. O "pgAdmin Website" é um link para a página oficial do pgAdmin, onde podemos encontrar mais informações sobre a ferramenta, tutoriais, atualizações e outras informações. O "Planet PostgreSQL" é um agregador de blogs e notícias da comunidade PostgreSQL. Finalmente, o "Community Support" é um link para acessar recursos de suporte, como fórum, lista de e-mails e outras formas de obter ajuda.

Criando banco de dados de vendas

No painel do lado esquerdo, temos um nó chamado Servers, que é o servidor onde estamos trabalhando. Clicando na seta à esquerda para expandir o nó, aparece uma caixa de diálogo onde devemos inserir a senha do superusuário que criamos ao instalar o PostgreSQL.

No nosso caso, colocamos postgres e clicamos na opção "Save Password" para guardar a senha. Assim, não precisaremos inserir a senha toda vez. Clicamos no botão "OK" e nos conectamos ao banco.

Em "PostgreSQL 16", contamos com três nós principais:

• Databases: permite acessar os bancos de dados existentes e criar, modificar ou excluir bancos de dados;
• Login/Groups Roles: permite criar usuários, regras, modificar papéis dos usuários, dar permissões e criar grupos de usuários;
• Tablespaces: permite acessar, criar, modificar e excluir as tablespaces do banco de dados.

As Tablespaces são a estrutura de armazenamento dos dados, e a relação entre tablespaces e banco de dados não é necessariamente 1:1 (lê-se um para um).

Vamos expandir a parte de "Database" e encontramos um banco de dados chamado postgres, que foi instalado junto com o banco de dados. Esse banco existe para facilitar a administração e manutenção do servidor, permitindo que as pessoas administradores realizem tarefas sem interferir nas bases de dados existentes. Sua existência garante que sempre exista um banco de dados disponível para evitar erros de conexão.

Como estamos trabalhando na Frutally, precisamos criar o banco de dados de vendas da empresa. Clicando sobre "Databases", usamos o botão direito do mouse e selecionamos "Create > Database".

Na janela que se abre, na aba "General", definiremos seu nome como FRUTALLY_VENDAS no campo "Database". No campo "Owner", mantemos o dono da base como o usuário "postgres", pois estamos conectados com esse usuário e criando essa base.

Podemos definir outros parâmetros específicos importantes. Por exemplo, na aba "Definition", podemos escolher a tablespace que utilizaremos. Já temos uma tablespace criada automaticamente ao instalar o produto, chamada pg_default. Vamos associar nosso banco a essa tablespace e clicar no botão "Save".

PostgreSQL 16/FRUTALLY_VENDAS - Database connected.

O banco de dados é criado e aparece listado no painel lateral esquerdo, dentro de "PostgreSQL 16 > Databases". Dentro de FRUTALLY_VENDAS, temos uma série de informações relacionadas ao banco de dados, como casts, catalogs, event triggers, extensions e mais.

Expandindo a opção de "Schemas", encontramos outras informações, sendo a mais importante "Tables". Ao abrir "Tables", notamos que ainda não há tabelas nesse banco de dados.

Criando tabelas

Vamos agora criar as tabelas e carregar os dados da base de dados de vendas da Frutally. Para isso, clicamos no banco de dados FRUTALLY_VENDAS com o botão direito do mouse e escolhemos a opção "Query Tools". Desse modo, abre-se uma área do lado direito onde podemos trabalhar com os comandos SQL do PostgreSQL.

Sempre que você abra uma área para editar comandos, é importante observar o nome da aba que deve conter a base de dados conectada, o usuário e o servidor. Nesse caso, FRUTALLY_VENDAS/postgres@PostgreSQL 16. Antes de rodar um comando, é importante verificar onde você está localizado.

Associado a este vídeo, disponibilizaremos o link para download de um arquivo compactado com o projeto inicial do curso.

Vamos criar um diretório de trabalho no nosso computador, onde iremos salvar o arquivo e descompactá-lo.

Sugerimos que você crie esse diretório em um caminho curto (no drive C ou D), e com um nome curto. Por exemplo, vamos nomeá-lo de "CURSOPOST". O nome pode ser qualquer um, desde que seja curto, para facilitar as edições durante os exercícios práticos do curso.

Após descompactar, encontramos 5 arquivos CSV e 2 arquivos TXT que contêm scripts:

• Base_dados_frutally.txt;
• importar_tabela.txt;
• itens_notas_fiscais.csv;
• notas_fiscais.csv;
• tabela_de_clientes.csv;
• tabela_de_produtos.csv;
• tabela_de_vendedores.csv.

Primeiro, vamos executar o script para criar a base de dados Frutally. Abrimos o arquivo Base_dados_frutally.txt com o editor de texto de sua preferência. Você pode usar o Notepad, mas neste curso usaremos o Notepad++, que mostra comandos de maneira mais clara e com cores.

Base_dados_frutally.txt:

DROP TABLE IF EXISTS tabela_de_vendedores;

CREATE TABLE tabela_de_vendedores (
  MATRICULA varchar(5) NOT NULL,
  NOME varchar(100),
  PERCENTUAL_COMISSAO real,
  DATA_ADMISSAO date,
  DE_FERIAS boolean,
  BAIRRO varchar(50)
);

DROP TABLE IF EXISTS tabela_de_produtos;

CREATE TABLE tabela_de_produtos (
  CODIGO_DO_PRODUTO varchar(10) NOT NULL,
  NOME_DO_PRODUTO varchar(50),
  EMBALAGEM varchar(20),
  TAMANHO varchar(10),
  SABOR varchar(20),
  PRECO_DE_LISTA real NOT NULL
);

DROP TABLE IF EXISTS tabela_de_clientes;

CREATE TABLE tabela_de_clientes (
  CPF varchar(11) NOT NULL,
  NOME varchar(100),
  ENDERECO_1 varchar(150),
  ENDERECO_2 varchar(150),
  BAIRRO varchar(50),
  CIDADE varchar(50),
  ESTADO varchar(2),
  CEP varchar(8),
  DATA_DE_NASCIMENTO date,
  IDADE smallint,
  SEXO varchar(1),
  LIMITE_DE_CREDITO real,
  VOLUME_DE_COMPRA real,
  PRIMEIRA_COMPRA boolean
);

DROP TABLE IF EXISTS notas_fiscais;

CREATE TABLE notas_fiscais (
  CPF varchar(11) NOT NULL,
  MATRICULA varchar(5) NOT NULL,
  DATA_VENDA date,
  NUMERO serial PRIMARY KEY,
  IMPOSTO real NOT NULL
);

DROP TABLE IF EXISTS itens_notas_fiscais;

CREATE TABLE itens_notas_fiscais (
  NUMERO int NOT NULL,
  CODIGO_DO_PRODUTO varchar(10) NOT NULL,
  QUANTIDADE int NOT NULL,
  PRECO real NOT NULL
);

Copiamos todos os comandos do script e os colamos na área de edição do pgAdmin. Em seguida, selecionamos todas as linhas e clicamos no botão "Execute script" (ou atalho "F5") para executar.

No painel inferior chamado "Messages", conferimos que a consulta foi bem-sucedida:

Query returned successfully in 46 msec.

Dentro da base de dados no painel esquerdo, vamos clicar em "Tables" com o botão direito do mouse e selecionar a opção "Refresh" para atualizar as tabelas. Agora, existem cinco tabelas associadas ao banco de dados, mas que ainda estão vazias. Precisamos carregar dados nelas.

Carregando dados

No diretório "CURSOPOST", vamos abrir um segundo script chamado importar_tabela.txt com o editor de texto.

Lembre-se de editar o caminho após FROM, indicando o diretório onde você descompactou os arquivos. No nosso caso, vamos clicar na opção "Localizar > Substituir" (atalho "Ctrl + H) no menu superior do editor para substituir todas as ocorrências de "POSTCUR" por "CURSOPOST".

importar_tabela.txt:

COPY tabela_de_clientes (CPF, NOME, ENDERECO_1, ENDERECO_2, BAIRRO, CIDADE, ESTADO, CEP, DATA_DE_NASCIMENTO, IDADE, SEXO, LIMITE_DE_CREDITO, VOLUME_DE_COMPRA, PRIMEIRA_COMPRA)
FROM 'C:/CURSOPOST/tabela_de_clientes.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ';', QUOTE '"');

COPY tabela_de_produtos (CODIGO_DO_PRODUTO, NOME_DO_PRODUTO, EMBALAGEM, TAMANHO, SABOR, PRECO_DE_LISTA)
FROM 'C:/CURSOPOST/tabela_de_produtos.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ';', QUOTE '"');

COPY tabela_de_vendedores (MATRICULA, NOME, PERCENTUAL_COMISSAO, DATA_ADMISSAO, DE_FERIAS, BAIRRO)
FROM 'C:/CURSOPOST/tabela_de_vendedores.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ';', QUOTE '"');

COPY notas_fiscais (CPF, MATRICULA, DATA_VENDA, NUMERO, IMPOSTO)
FROM 'C:/CURSOPOST/notas_fiscais.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ';', QUOTE '"');

COPY itens_notas_fiscais (NUMERO, CODIGO_DO_PRODUTO, QUANTIDADE, PRECO)
FROM 'C:/CURSOPOST/itens_notas_fiscais.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ';', QUOTE '"');

Esses comandos vão buscar os dados nos arquivos CSV e gravar diretamente nas tabelas. Copiamos os comandos e os colamos na área de edição do pgAdmin. Feito isso, selecionamos as linhas e as executamos com "F5".

Query returned successfully in 2 secs 799 msec.

Podemos fazer uma conferência rápida para verificar o sucesso da consulta. Basta fazer um SELECT de uma das tabelas, por exemplo, itens_notas_fiscais.

SELECT * FROM public.itens_notas_fiscais;

Após executar, os dados aparecem no painel "Data Output" na parte inferior do programa.

Assim, conferimos que as informações estão carregadas no banco de dados da Frutally.

Até o próximo vídeo!

Sobre o curso PostgreSQL: administração e monitoramento

O curso PostgreSQL: administração e monitoramento possui 219 minutos de vídeos, em um total de 59 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Apresentação e configuração do PostgreSQL
• Administração do PostgreSQL
• Monitoramento e performance
• Ferramentas de administração e monitoramento
• Manutenção e otimização