PostgreSQL: administração e otimização do banco

Papel do DBA - Introdução

Sejam muito bem-vindos à Alura. Meu nome é Vinicius Dias, eu vou acompanhar vocês neste treinamento de administração e um pouco de otimização de bancos de dados PostgreSQL.

Neste treinamento vamos conversar bastante, vou falar muito, então espero que você esteja com paciência, mas vamos falar de coisas muito legais. Para começar, vamos bater um papo sobre qual o papel de uma pessoa que é DBA, ou seja, administradora de um banco de dados. Qual o papel dessa pessoa, o que ela tem que fazer, o que ela tem que conhecer.

A partir disso vamos começar nossos estudos entendendo como funcionam conexões ao banco de dados, e depois vamos levantar realmente um banco de dados, separando um espaço para isso, levantando um servidor, levantando um serviço do Postgre, e depois manipulando esse serviço, seja interrompendo ele, recuperando status, etc.

Vamos conversar um pouco sobre os papeis do DBA também, quando falamos de manutenção do banco de dados. Vamos ver sobre rotinas de manutenção, limpeza de disco, reindexação, etc, vamos falar bastante coisa, bastante termos difíceis também.

Vamos ver um pouco sobre backups, vamos ver a importância, obviamente, mas principalmente mão na massa. Vamos fazer backups, vamos restaurar backups, vamos nos deparar com problemas na restauração. Vai ser bem interessante também.

Um pouco de otimização, que falei, vamos entender como funciona bem por alto o query planner do PostgreSQL. Vamos entender que ele planeja uma query e vamos aprender a ver como ele está planejando antes mesmo de executar uma query. E com isso vamos entender um pouco do conceito de índices e como eles podem ajudar ou atrapalhar em determinados cenários.

No final vamos conversar sobre uma parte que é bem legal, que é justamente essa que está aparecendo na tela, sobre autenticação. Vamos limitar acesso de determinados hosts, de determinados IPs, vamos criar usuários específicos, vamos entender que não é usuário propriamente dito o nome.

Vamos limitar e muito o acesso de alguns desses usuários para podermos ter mais segurança. Vai ser um treinamento bem interessante, espero que você esteja animado como eu estou, e caso você tenha alguma dúvida no processo não hesite, abra uma questão lá no fórum.

Eu tento responder pessoalmente sempre que possível, mas quando não consigo temos uma comunidade de alunos, moderadores e instrutores muito solícita, então com certeza alguém vai poder te ajudar.

Papel do DBA - DBA: DataBase Administrator

Vamos conversar um pouco sobre as responsabilidades que um DBA tem. É como sua função. O que ele precisa fazer no dia a dia na empresa. Basicamente, a primeira coisa que um DBA precisa fazer, precisa saber como fazer, é avaliar e instalar o ambiente do PostgreSQL.

O que isso quer dizer, avaliar e instalar? Porque instalar já vimos como instalar, no Windows é só dar dois cliques, no Linux é um comando, se você usa docker é mais fácil ainda, mas além disso ele precisa avaliar o ambiente onde ele vai instalar o PostgreSQL.

Durante vários momentos deste treinamento vou citar a importância de um DBA conhecer o ambiente, o sistema operacional onde ele está trabalhando. Inclusive, vamos comentar um pouco sobre o que podemos fazer no sistema operacional, mas um DBA precisa conhecer muito bem o sistema operacional onde ele vai trabalhar.

Se ele trabalha, o que é muito comum, no ambiente Linux, ele precisa conhecer bastante os comandos do Linux para gerenciar serviços, etc. Ele precisa avaliar esse ambiente para tomar as melhores decisões e instalar o sistema de banco de dados em si. Essa é a primeira responsabilidade e a mais básica de um DBA.

Além disso, ele precisa manter o banco de dados performático. O que isso quer dizer? Ele precisa, mais uma vez, conhecer o sistema onde o banco está instalado para que consiga gerenciar configurações, manipular as coisas de forma com que o banco de dados consiga trabalhar na sua melhor forma.

Além do sistema operacional, ele precisa conhecer, obviamente, o banco de dados em si. Para mexer em algumas configurações, limitar alguns acessos. Ele garante que o banco de dados vai estar pronto para usar os recursos que ele precisa, mas garantindo que respeita qualquer limitação que o sistema tenha.

Além disso, ele pode acabar ajudando de alguma forma na otimização de algumas queries, por exemplo, e também vamos conversar um pouco mais sobre isso.

Mas continuando, ele precisa manter os dados salvos em algum lugar através de processos de backup. Backup é uma das responsabilidades do DBA também. Ele precisa saber fazer backup, precisa saber do que fazer backup, como fazer backup, e obviamente precisa saber restaurar esse backup, e também vamos bater um papo sobre isso.

Como comentei, também faz parte do trabalho do DBA auxiliar os analistas, os desenvolvedores, quem estiver criando queries, para que consigamos otimizar algumas queries, para que consigamos utilizar o banco para executar algumas queries. Então faz parte também do trabalho do DBA trabalhar em conjunto com a galera de análise, de desenvolvimento, para conseguir manter tudo funcionando da melhor forma. Inclusive na hora de manter os dados seguros, consistentes, etc.

Obviamente também faz parte do DBA monitorar todo o ambiente de instalação do Postgre. Como assim monitorar o ambiente? Garantir, por exemplo, que os logs estejam sendo salvos, visualizar os logs de vez em quando, garantir que os recursos que o Postgre está consumindo não excedem nenhum limite. Vigiar, basicamente, o que está acontecendo.

Configurar o ambiente, obviamente, para fazer com que todos esses pontos anteriores se tornem verdade, precisamos configurar o ambiente para manter ele rápido, para manter ele seguro, para manter uma pasta de backup, esse tipo de coisa.

Configurar o acesso à base de dados. Basicamente, qual IP pode acessar esse banco de dados, qual rede pode acessar, quais usuários, e com isso entra também uma outra responsabilidade do DBA de saber administrar os usuários. Esse usuário aqui, essa pessoa é desenvolvedora, então essa pessoa vai ter acesso somente ao comando select, ela não vai poder inserir dados, não vai poder remover dados, remover tabelas. Ela tem acesso somente a isso.

Essa outra pessoa é analista de infraestrutura, então ela vai ter acesso a algumas funcionalidades a mais dentro do banco de dados. Esse tipo de permissão, gerenciamento de usuários, esse tipo de coisa também faz parte do trabalho do DBA.

Durante o treinamento vamos conversar um pouco sobre cada parte dessa. Obviamente não vamos conseguir passar por tudo em um treinamento só. Seria impossível, mas vamos pincelar várias áreas que foram citadas aqui.

Papel do DBA - Conexões com o banco

Bem-vindos de volta. Vamos conversar um pouco sobre o modelo no qual os bancos de dados relacionais no geral, e não seria diferente com o Postgre, funcionam. Os bancos de dados funcionam no modelo cliente servidor. Talvez você já tenha isso muito claro na sua mente, mas só para garantir vamos passar bem rápido por isso.

Quando estamos com o pgAdmin aberto estamos conectados a um servidor Postgre SQL que está rodando. E esse servidor, normalmente, está rodando em uma máquina, é um processo que está rodando em um computador, e o cliente, ou seja, o pgAdmin ou datagrip, ou qualquer ferramenta, vai estar no computador das pessoas que vão acessar o banco de dados.

Para realizar esse treinamento, tanto o servidor quanto cliente estão instalados na minha máquina, rodando no meu mesmo computador, mas normalmente, via de regra, são computadores diferentes.

Só para entendermos mais ou menos como essa parte funciona, vou passar o olho pelo cliente. Tenho o pgAdmin e tenho uma conexão com o servidor que o nome dele padrão é Postgre. Se eu clicar com o botão direito e vier em properties, tenho uma aba conexão.

Em conexão vejo o host, ou seja, o endereço do meu banco de dados, o ip dele, que no caso é minha própria máquina, por isso esse 0000, a porta com a qual vou me conectar, porque caso você já tenha estudado um pouco de redes, inclusive caso não tenha estudado é bastante importante que um DBA conheça bastante de redes, a porta com a qual vou me conectar é a 5432.

Essa é a porta padrão do Postgre, mas podemos configurar alguma outra sem problema nenhum. Por padrão, essa é a porta. O meu usuário é o Postgre e a minha senha se não me engano é Postgre também.

Tenho os dados de conexão. É muito importante saber que embora eu tenha um único servidor, posso ter várias conexões, vários clientes me conectando a esse servidor. Para exemplificar isso, estou conectado com o cliente, e vou abrir outro cliente, com um programa chamado datagrip.

Comentei um pouco sobre ele no curso de programação, de pl, mas vamos lá. Vou criar uma nova conexão, um novo data source, com PostgreSQL. Posso dar um nome para essa conexão, Conexão teste, posso colocar um comentário, mas isso não significa nada.

Agora que entram os detalhes de conexão. O host é o IP e localhost ou 0000 vai dar no mesmo. A porta, repare que os programas já reconhecem como padrão do PostgreSQL, e a autenticação vou utilizar usuário e senha. Meu usuário é Postgre e se não me engano a senha também é Postgre.

Preciso selecionar o banco de dados com o qual vou me conectar. Se dermos uma olhada no pgAdmin, o nome do banco de dados que tenho criado é Alura. Então no datagrip vou me conectar com o banco de dados Alura. Posso testar essa conexão para garantir que está tudo ok, que minha senha foi digitada corretamente.

Está ok. Ele conseguiu se conectar. Já consigo dar uma olhada que conseguido selecionar quais schemas vou exibir, quais não vou. Repare que ele já se conectou realmente, mas no nosso caso só tem o schema público, então não vou nem mexer nessa parte.

O ponto é que estamos conectados. Vou clicar em aplicar, não quero cuidar da parte de criptografia. Tenho agora dois clientes conectados ao mesmo servidor. Tenho uma conexão com o datagrip, tenho outra conexão utilizando pgAdmin.

“Ah, Vinicius, você mostrou dois clientes, mas onde esse servidor está rodando?”, tenho o servidor rodando no meu terminal. E como eu, Vinicius, estou fazendo? Caso vocês estejam utilizando Windows, por exemplo, vão ter rodado o programa do Postgre, que já sobe o servidor, um serviço do Windows por baixo dos panos.

Como estou no Linux utilizei uma abordagem um pouco diferente. Estou usando o docker. Estou rodando uma imagem oficial do Postgre chamada postgres, estou liberando a porta padrão do Postgre, definindo o banco de dados, a senha, e então tenho um container do docker rodando.

Caso você não entenda de docker não tem problema nenhum. Basicamente o que fiz é sem instalar o Postgre na minha máquina rodar um servidor do Postgre. Conseguimos ter várias informações e durante o treinamento vamos precisar de algumas dessas informações que são exibidas aqui. Mas basicamente tenho meu servidor rodando.

Repare que foi executado direto na hora que subiu esse servidor, ele já criou o banco de dados para nós, o banco de dados Alura. Ele rodou alguns comandos que talvez comentemos sobre alguns deles. Agora no final ele diz que esse banco de dados, esse sistema de banco de dados está pronto para aceitar conexões.

Com isso temos um servidor rodando, um cliente conectado, dois clientes conectados. Esse modelo cliente servidor, embora seja básico, talvez você até já entenda sobre isso, é muito importante para entendermos o trabalho do database administrator. Do DBA. O DBA vai cuidar principalmente dessa parte do servidor.

Ele vai passar a maior parte do tempo brincando no servidor. Quer dizer que ele nunca vai usar um cliente para se conectar no banco de dados, rodar alguns comandos lá? Obviamente que ele vai, mas grande parte dele é gerenciar o servidor, o sistema onde roda o servidor do PostgreSQL.

Com isso entendido, com o papel do DBA entendido, com esse esquema cliente servidor entendido, estando claro que o DBA vai trabalhar bastante nessa parte do servidor, nos encontramos no próximo capítulo para começar a botar um pouco mais a mão na massa.

Sobre o curso PostgreSQL: administração e otimização do banco

O curso PostgreSQL: administração e otimização do banco possui 150 minutos de vídeos, em um total de 55 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:

• Papel do DBA
• Servidor Postgres
• Tuning de banco
• Processos de manutenção
• Backup e restauração
• Planejamento de queries
• Usuários e permissões