Administração do SGBD PostgreSQL - Unidade 4 - Configurações, ajustes e segurança no PostgreSQL

Prévia do material em texto

Administração do 
SGBD PostgreSQL
Unidade 4
Configurações, ajustes e 
segurança no PostgreSQL
Diretor Executivo 
DAVID LIRA STEPHEN BARROS
Gerente Editorial 
CRISTIANE SILVEIRA CESAR DE OLIVEIRA
Projeto Gráfico 
TIAGO DA ROCHA
Autoria 
CAMILA FREITAS SARMENTO
AUTORIA
Camila Freitas Sarmento
Sou formada em Telemática com mestrado em Ciência da 
Computação e, atualmente, doutoranda na área de Engenharia de Software, 
com uma experiência técnico-profissional na área de Soluções Digitais 
(Redes e Programação Back-End) de mais de sete anos. Atualmente sou 
Analista de Informática – Programadora Web Back-End no Instituto Senai de 
Tecnologia em Automação Industrial (IST-Senai). Fui professora substituta 
na Universidade Estadual da Paraíba (UEPB) e atuei como professora 
substituta e, posteriormente, como tutora no Instituto Federal de Educação, 
Ciência e Tecnologia da Paraíba (IFPB). Sou apaixonada pelo que faço e 
adoro transmitir minha experiência àqueles que estão iniciando em suas 
profissões. Por isso fui convidada pela Editora Telesapiens a integrar seu 
elenco de autores independentes. Estou muito feliz em poder ajudar você 
nesta fase de muito estudo e trabalho. Conte comigo!
ICONOGRÁFICOS
Olá. Esses ícones irão aparecer em sua trilha de aprendizagem toda vez 
que:
OBJETIVO:
para o início do 
desenvolvimento de 
uma nova compe-
tência;
DEFINIÇÃO:
houver necessidade 
de se apresentar um 
novo conceito;
NOTA:
quando forem 
necessários obser-
vações ou comple-
mentações para o 
seu conhecimento;
IMPORTANTE:
as observações 
escritas tiveram que 
ser priorizadas para 
você;
EXPLICANDO 
MELHOR: 
algo precisa ser 
melhor explicado ou 
detalhado;
VOCÊ SABIA?
curiosidades e 
indagações lúdicas 
sobre o tema em 
estudo, se forem 
necessárias;
SAIBA MAIS: 
textos, referências 
bibliográficas e links 
para aprofundamen-
to do seu conheci-
mento;
REFLITA:
se houver a neces-
sidade de chamar a 
atenção sobre algo 
a ser refletido ou dis-
cutido sobre;
ACESSE: 
se for preciso aces-
sar um ou mais sites 
para fazer download, 
assistir vídeos, ler 
textos, ouvir podcast;
RESUMINDO:
quando for preciso 
se fazer um resumo 
acumulativo das últi-
mas abordagens;
ATIVIDADES: 
quando alguma 
atividade de au-
toaprendizagem for 
aplicada;
TESTANDO:
quando o desen-
volvimento de uma 
competência for 
concluído e questões 
forem explicadas;
SUMÁRIO
Performance tuning .................................................................................... 10
Configuração de tuning no PostgreSQL ......................................................................... 10
Performance de tuning para leitura ................................................................................... 12
Configuração do PostgreSQL ................................................................. 18
Backup e recuperação ..............................................................................26
Backup no PostgreSQL ...............................................................................................................26
O utilitário pg_dump ..................................................................................................28
O utilitário pg_dumpall.............................................................................................32
Restauração no PostgreSQL ...................................................................................................34
Replicação nativa ........................................................................................ 37
7
UNIDADE
04
Administração do SGBD PostgreSQL
8
INTRODUÇÃO
Você sabia que para muitas empresas, independentemente do 
tamanho, as perdas dos dados podem ser fatais para os negócios? Isso 
mesmo. Sendo assim, além da garantia de um bom desempenho, o 
backup e a restauração são tarefas essenciais na indústria. Por isso, os 
backups periódicos são tão importantes para uma pronta recuperação 
devido às falhas e catástrofes. Lembra-se do ataque às Torre Gêmeas 
do complexo empresarial do World Trade Center, na cidade de Nova 
Iorque, em 11 de setembro de 2001? Apesar de os danos terem sido 
devastadores, muitas empresas já estavam em operação em menos de 
24h por conta da política de recuperação dos dados. Pois é! A área de 
administração de banco de dados possui grandes responsabilidades 
e você também pode fazer parte disso. Ao longo desta unidade letiva 
você vai mergulhar nesse universo!
Administração do SGBD PostgreSQL
9
OBJETIVOS
Olá. Seja muito bem-vindo à Unidade IV - Configurações, 
Ajustes e Segurança no PostgreSQL. Nosso objetivo é auxiliar você no 
desenvolvimento das seguintes competências profissionais até o término 
desta etapa de estudos:
1. Ajustar a performance do PostgreSQL por meio do processo de 
tuning.
2. Configurar as preferências e parâmetros de ajustes do PostgreSQL.
3. Realizar cópias de segurança de banco de dados e recuperar 
essas cópias quando necessário, salvaguardando a segurança e 
integridade dos dados no PostgreSQL.
4. Efetuar o procedimento de replicação de dados que já vem 
disponível nativamente no PostgreSQL.
Então, preparado para uma viagem sem volta rumo ao 
conhecimento? Vamos conhecer mais desse assunto incrível e mais 
utilizado no mundo! Você não vai ficar de fora dessa, não é? Ao trabalho!
Administração do SGBD PostgreSQL
10
Performance tuning
OBJETIVO:
Ao término deste capítulo você será capaz de entender 
como funciona uma performance tuning por meio de 
procedimentos de ajuste fino no PostgreSQL, envolvendo 
recursos de software e hardware. Isso será fundamental para 
o exercício de sua profissão, pois uma performance tuning 
requer um alto nível de conhecimento e prática. As pessoas 
que tentaram realizar ajustes no servidor sem a devida 
instrução ocasionaram problemas e mau funcionamento 
no SGBD, pois tal procedimento é um recurso avançado 
e praticado em grandes servidores que trabalham com 
altos volumes de dados e acessos. E então, motivado para 
desenvolver essa competência? Então vamos lá. Avante!
Configuração de tuning no PostgreSQL
Em uma base de dados PostgreSQL, os valores de configuração 
padrão não são apropriados em um ambiente de produção, pois tais valores 
geralmente são subdimensionados. Ainda assim, em modo produção, o 
impacto da informação da observação pode não ser preciso o suficiente 
para aumentar o desempenho. Um dos princípios de ajuste de desempenho 
é que, em geral, não há como supor qual o próximo gargalo até a remoção 
do atual. E tentar conjecturar pode ser um trabalho sem eficácia. 
Imagine que há um gargalo no servidor e você ajuste o shared_buffers 
(lembra-se dele da Unidade anterior?) aumentando-o significativamente. 
Isso pode fazer com que aumente a memória usada para armazenar as 
leituras do banco de dados. Contudo, haverá sérios prejuízos, tais como 
alocação de memória em operações de CPU. Da mesma forma se for 
definido com um valor demasiadamente pequeno.
Diante desse contexto, torna-se responsabilidade do administrador 
do banco de dados realizar os ajustes do SGBD PostgreSQL necessários 
conforme a carga de trabalho e, ainda, realizar a correta configuração do 
banco de dados é apenas a primeira etapa no ajuste de desempenho.
Administração do SGBD PostgreSQL
11
Tratando-se de performance, o ideal será realizar a configuração 
controlando as mudanças e observando e analisando o trade-off das 
decisões, isto é, as vantagens e desvantagens que ocorrerão a cada 
configuração realizada. Dessa forma, o primeiro passo para o aprendizado 
de como realizar a configuração de tuning no PostgreSQL é compreender 
o ciclo de vida de uma consulta, cujas etapas são:
 • Transmissão de string de consulta para o backend do banco de 
dados – esta é a etapa do comando SQL digitado por meio do 
aplicativo e envio para o backend. E para melhorar a performance, 
o desenvolvedor pode inserir as queries que são muito longas no 
banco dedados como um procedimento armazenado e, dessa 
forma, reduzir a transferência de dados ao mínimo.
 • Análise de string de consulta – nesse passo do ciclo de vida da 
consulta, quando a string está armazenada no servidor PostgreSQL, 
ela é analisada em tokens.
 • Planejamento de consulta para otimizar a recuperação de 
dados – esta etapa é na qual o servidor inicializa o trabalho e 
verifica se a query já está pronta e se a biblioteca do cliente suporta 
esse recurso e analisa o SQL para determinar qual abordagem 
mais eficiente na recuperação dos dados.
 • Recuperação de dados do hardware – após a tomada de decisão 
do PostgreSQL sobre a melhor abordagem na recuperação dos 
dados (tais como: uso de índice, hash join etc.), então irá realizar a 
obtenção, cuja etapa depende da configuração de hardware.
 • Transmissão de resultados para o cliente – nesta última 
etapa do ciclo de vida da consulta, ocorre a transmissão dos 
resultados ao cliente. Nesta etapa, não há opções de ajustes para 
o administrador do banco de dados. Contudo, uma opção de 
melhorar a performance é minimizar o número de linhas e colunas 
para apenas as necessárias para o cliente.
A configuração do PostgreSQL na qual já vem implementada por 
padrão após a instalação é uma estimativa conforme um banco de dados 
Administração do SGBD PostgreSQL
12
de porte médio, pois cada banco de dados não é somente diferente no 
seu projeto, mas também nos seus requisitos.
Sendo assim, alguns sistemas de banco de dados são usados para 
registrar altos volumes de dados que raramente são consultados, outros 
já podem ser acessados de forma ininterruptamente. Dessa forma, sua 
configuração conforme o perfil de uso torna-se essencial.
O alto desempenho no PostgreSQL pode ser obtido por meio de 
boas definições de configuração e esquemas físicos adequados, incluindo 
índices. Ainda, os planos de execução dependem das estatísticas 
reunidas nas tabelas; felizmente, no PostgreSQL, você pode controlar 
o comportamento da coleção de estatísticas. Ao lidar com chaves 
estrangeiras, Juba e Volkov (2019, p. 391) apresentam duas recomendações 
para aumentar o desempenho, que são as seguintes:
 • Sempre indexe chaves estrangeiras – indexar uma tabela para 
chaves estrangeiras permite que o PostgreSQL busque dados da 
tabela usando uma varredura de índice.
 • Aumente a meta da estatística da coluna nas chaves estrangeiras – 
isso também se aplica a todos os predicados porque permite que 
o PostgreSQL tenha uma estimativa melhor do número de linhas. 
A meta estatística padrão é 100 e o máximo é 10.000. Aumentar 
o destino das estatísticas torna o comando ANALYZE mais lento.
Ambas as abordagens anteriores requerem a identificação de 
chaves estrangeiras. A tabela pg_catalog.pg_constraint pode ser usada 
para consultar as restrições da tabela. Para saber sobre as restrições de 
chave estrangeira, você pode simplesmente executar a seguinte consulta:
SELECT * FROM pg_constraint WHERE contype = 'f';
Performance de tuning para leitura
Você sabia que o PostgreSQL fornece os meios para descobrir o 
porquê de uma determinada consulta estar lenta? Pois é! O PostgreSQL 
analisa tabelas, coleta estatísticas dessas tabelas e constrói histogramas 
usando auto-vacuuming.
Administração do SGBD PostgreSQL
13
Auto-vacuuming é normalmente usada para recuperar espaços 
em disco, atualizar estatísticas de tabela e executar outras tarefas de 
manutenção, por exemplo, evitar o retorno de ID da transação. Dessa 
forma, as estatísticas da tabela realizadas pelo PostgreSQL permitem que 
seja escolhido um plano de execução com o menor custo possível. O custo 
é calculado levando em consideração as entradas e saídas, além do custo 
da CPU. Além disso, o PostgreSQL permite que os usuários vejam o plano de 
execução gerado, fornecendo o comando EXPLAIN.
Você está lembrado(a) dos comandos BUFFER e ANALYZE das 
unidades anteriores? Pois é! O comando EXPLAIN pode mostrar o porquê 
de uma determinada consulta estar lenta, especialmente se as opções 
BUFFER e ANALYZE são usadas. 
De acordo com os autores Juba e Volkov (2019, p. 410), existem 
algumas dicas que permitem que o administrador do banco de dados 
possa decidir se o plano de execução é bom ou não. Essas dicas são as 
seguintes:
 • O número estimado da linha em comparação com o número de 
linhas reais – este parâmetro define o método de execução da 
consulta. Contudo, há duas vertentes: o número de linhas pode 
ser subestimado ou superestimado, e estimativa incorreta pode 
afetar todo o algoritmo que é usado para: buscar dados do disco 
rígido; classificar; juntar etc. No caso em que o número de linhas 
for subestimado, o impacto no desempenho será mais evidente.
 • Operação de ordenação em memória ou em disco – ao realizar 
uma operação de ordenação (tais como: DISTINCT, LIMIT, ORDER 
ou GROUP BY), se houve memória suficiente, ela será realizada na 
memória RAM, caso contrário o disco rígido será utilizado.
 • Buffer cache – sugere-se verificar a quantidade de dados 
armazenados em buffer, pois para a leitura dos dados por meio de 
buffers, sempre aumentará o desempenho.
Ainda, existem alguns erros comuns e más práticas que os 
desenvolvedores podem executar. Por exemplo: um banco de dados 
relacional baseia-se na teoria dos conjuntos, então alguns desenvolvedores 
Administração do SGBD PostgreSQL
14
tendem a executar em nível de linha em vez de manipulação de conjunto. 
Além disso, muitas pessoas criam designs físicos inadequados porque não 
estão familiarizadas com a modelagem de banco de dados relacional. Em 
geral, existem vários problemas que podem levar a um mau desempenho:
 • Estatísticas incorretas – isso pode acontecer se houver 
correlações cruzadas entre predicados ou se os predicados 
tiverem uma função imutável.
 • Recuperação de dados desnecessária: quando há dados 
desnecessários, por exemplo: selecionar todas as linhas e colunas, 
mesmo se apenas um subconjunto for necessário.
 • Manipulação de dados desnecessária – isso é bastante comum, 
como aplicar classificação aos dados quando não é necessário.
Ainda, para problemas de modelagem, o ideal é verificar a 
normalização do banco de dados porque ela também pode aumentar 
o desempenho reduzindo o tamanho dos dados e aprimorando as 
estatísticas. No caso de Queries lentas, a primeira recomendação é 
verificar o pg_stat_statements respondendo às seguintes questões:
 • Quantas consultas simultâneas estão sendo executadas no 
sistema?
 • É possível visualizar tipos semelhantes de consulta aparecendo na 
coluna de consulta de forma contínua?
 • É possível visualizar as consultas que estão em execução há muito 
tempo?
 • Existem bloqueios que não foram concedidos?
 • Visualizam-se conexões de hosts suspeitos?
A visualização pg_stat_activity deve sempre ser verificada primeiro 
porque possibilitará um overview do sistema, apesar de o monitoramento 
gráfico fornecer uma primeira impressão do sistema, mas tudo se resume 
às consultas que estão sendo executadas no servidor. Portanto, uma boa 
visão geral do sistema fornecida por pg_stat_activity é essencial para o 
rastreamento de problemas. 
Administração do SGBD PostgreSQL
15
Vejamos um exemplo de como observar as consultas que estão 
sendo executadas atualmente na base de dados:
postgres=# SELECT datname, 
count (*) AS open, 
count (*) FILTER (WHERE state = 'active') AS active, 
count (*) FILTER (WHERE state = 'idle') AS idel, 
count (*) FILTER (WHERE state = 'idle in transaction') 
AS idle_in_trans 
FROM pq_stat_activity WHERE backend_type = 'client backend' 
GROUP BY ROLLUP (1);
Com tal comando, é possível observar as consultas abertas, ativas, 
ociosas e ociosas na transação. Caso tenha muitas transações ociosas, 
é essencial investigar o tempo em que tais transações foram mantidas 
abertas. Assim, podemos utilizar o seguinte comando:
postgres=# SELECT pid, xact_start, now() -xact_start AS duration
FROM pg_stat_activity 
WHERE state LIKE '%transaction%' 
ORDER BY 3 DESC;
A partir do comando, é possível observar no retorno a duração, e 
caso uma transação seja muito demorada, isto é, está há mais de 22h, é 
altamente suspeito e potencialmente perigoso. 
Dessa forma, o ideal é assegurar que transações com duração 
muito longas sejam monitoradas ou excluídas. Diante desse contexto, o 
PostgreSQL, a partir da versão 9.6, implementou um recurso chamado 
snapshot too old, que permite encerrar tais transações suspeitas. 
Vejamos um exemplo:
postgres=# SELECT now() - query_start AS duration, datname, query 
FROM pg_stat_activity WHERE state = 'active'
ORDER BY 1 DESC;
Administração do SGBD PostgreSQL
16
Neste caso, todas as consultas ativas são realizadas e as instruções 
calculam há quanto tempo cada consulta já está ativa. Frequentemente, 
vemos consultas semelhantes surgindo no topo, o que pode nos dar 
algumas pistas valiosas sobre o que está acontecendo em seu sistema.
Após a inspeção no pg_stat_activity, é coerente realizar a verificação 
das consultas que estão lentas/demoradas. Para isso, podemos abordar 
de duas formas: (i) procurando por consultas lentas individuais no log; ou 
(ii) procurando por tipos de consultas que demoram longos períodos.
Para o primeiro caso, o PostgreSQL está, por padrão de instalação, 
desativado para a busca por consultas lentas. Para ativá-la, teremos de 
definir a variável log_min_duration_statement para especificar um limite 
desejado e o PostgreSQL começar a escrever uma linha de log para cada 
consulta que exceder tal limite. Vejamos a linha de comando:
postgres=# SHOW log_min_duration_statement;
Não obstante, não é possível afirmar um valor desejado com 
exatidão, pois dependerá das características do banco de dados e 
isso está agregado ao perfil da empresa, pois, dependendo da carga 
de trabalho, o tempo desejado pode variar, então podemos refinar o 
comando da seguinte forma:
postgres=# ALTER DATABASE teste 
SET log_min_duration_statement TO 10000; 
Ao usar o log de consulta lenta, é importante considerar que muitas 
consultas menores também podem causar mais carga. Obviamente, 
sempre faz sentido estarmos cientes de consultas lentas individuais, mas 
às vezes essas consultas não são o real problema.
Outra recomendação importante é inspecionar o log a fim de 
verificar o que está ocorrendo. Uma alternativa é investigar as entradas de 
registro que variam, hierarquicamente, em três níveis:
 • ERRO – usado para problemas como erros de sintaxe, problemas 
relacionados às permissões etc. Ainda assim, é possível notar 
Administração do SGBD PostgreSQL
17
que o log sempre conterá erros, contudo, a presença de várias 
mensagens de erro de sintaxe é um indicativo para uma abordagem 
mais eficiente.
 • FATAL – usado quando houve problemas de alocação de memória, 
por exemplo, cuja presença é mais significativa e alarmante em 
relação ao ERRO.
 • PANIC – indica os maiores sinais de alarme no banco de dados 
e carece de uma abordagem com urgência por parte do 
administrador. Um exemplo dessa entrada de registro são as 
tabelas de bloqueio corrompidas.
SAIBA MAIS:
Gostou do assunto? Quer se aprofundar neste tema? 
Recomendamos o acesso à seguinte fonte de consulta e 
aprofundamento: artigo “TUNING – Técnicas de otimização 
de banco de dados um estudo comparativo: Mysql e 
Postgresql” (CARNEIRO et al., 2009). Disponível aqui.
RESUMINDO:
E então? Gostou do que lhe mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo o que vimos. Você deve ter aprendido 
que em uma base de dados PostgreSQL, os valores 
de configuração padrão não são apropriados em um 
ambiente de produção, pois tais valores geralmente são 
subestimados ou superestimados, cuja estimativa incorreta 
pode afetar todo o algoritmo que é usado para: buscar 
dados do disco rígido; classificar; juntar etc. Vimos o ciclo 
de vida de uma consulta SQL e comandos para a inspeção 
e posterior abordagem de melhoria de desempenho, além 
de aprendermos como realizar um overview da base de 
dados por meio do pg_stat_activity.
Administração do SGBD PostgreSQL
http://www.repositorio.furg.br/bitstream/handle/1/1692/TUNING.pdf?sequence=1
18
Configuração do PostgreSQL
OBJETIVO:
Ao término deste capítulo você será capaz configurar o 
arquivo postgresql.conf de modo a otimizar o desempenho 
do PostgreSQL. Isso será fundamental para o exercício de 
sua profissão, pois, a otimização do desempenho em uma 
base de dados é essencial para garantir a disponibilidade 
dos dados de forma eficiente. Nas Unidades anteriores, 
vimos em teoria algumas funcionalidades de melhoria 
do desempenho do servidor do postgresql.conf. Agora, 
vamos ver em detalhes com exemplos práticos. Muitos 
administradores de banco de dados têm a possibilidade de 
otimizar o acesso e o desempenho apenas realizando uma 
configuração adequada, mas, por ser um recurso avançado, 
poucos administradores de banco de dados adotam e 
acabam perdendo desempenho por falta de conhecimento 
específico. E então, motivado para desenvolver essa 
competência? Então vamos lá. Avante!
O arquivo postgresql.conf controla as configurações da manutenção 
do servidor PostgreSQL e, por meio dele, é possível substituir muitas 
configurações no banco de dados, função, sessão e até mesmo níveis de 
função. Você encontrará muitos detalhes sobre como ajustar seu servidor 
fazendo as configurações no arquivo e no seu servidor PostgreSQL. No 
entanto, a partir da versão 9.4, o PostgreSQL disponibilizou uma alteração 
a fim de facilitar o trabalho do administrador. Dessa nova versão (9.4) em 
diante, é possível evitar a edição diretamente no postgresql.conf usando 
um arquivo adicional chamado postgresql.auto.conf. 
Além disso, Obe e Hsu (2017, p. 41), em seu guia prático de banco 
de dados PostgreSQL avançado, recomendam que o administrador do 
banco de dados não modifique o arquivo postgresql.conf, mas que realize 
quaisquer configurações personalizadas em postgresql.auto.conf.
Um dos métodos que possibilita ao administrador realizar a leitura 
das configurações atuais do servidor PostgreSQL sem abrir o arquivo 
postgresql.conf é consultar a visão chamada pg_settings. 
Administração do SGBD PostgreSQL
19
A primeira coisa que um administrador precisa aprender é como 
descobrir de onde vêm os parâmetros de configuração. Para isso, vejamos 
o pg_settings com o seguinte comando:
postgres=# \x 
Expanded display is on. 
postgres=# SELECT * FROM pg_settings WHERE name = 'TimeZone';
Está lembrado(a) do psql não é mesmo? Vamos visualizar o retorno 
na tela do psql por meio da figura 1:
Figura 1 – pg_settings por TimeZone
Fonte: Elaborado pela autora (2021).
Administração do SGBD PostgreSQL
20
As configurações de superusuário podem ser alteradas apenas por 
um superusuário e serão aplicadas a todos os usuários que se conectarem 
após uma recarga. Os usuários não podem substituir individualmente a 
configuração. Agora que você já sabe selecionar o fuso horário, vamos 
supor que você precise trocar o do seu servidor PostgreSQL. Para saber 
qual o fuso horário definir, você pode obter a lista completa de todos os 
fusos horários do PostgreSQL por meio do seguinte comando:
postgres=# SELECT * FROM pg_timezone_names;
Então, com base na lista apresentada, você poderá verificar o 
TimeZone que deseja trocar. Vejamos uma parte desse retorno da lista 
na figura 2.
Figura 2 – Lista de TimeZone do PostgreSQL
Fonte: Elaborado pela autora (2021).
Administração do SGBD PostgreSQL
21
Sendo assim, vejamos como trocar o fuso horário no PostgreSQL. 
Observe novamente a Figura 1. Veja que temos um TimeZone definido 
como “America/Buenos Aires”. Então, suponhamos que você deseje trocar 
para “Europe/Berlin”. Para isso, você precisa executar o seguinte comando:
postgres=#ALTER SYSTEM SET timezone to ‘Europe/Berlin’;
Caso você execute novamente o comando para verificar o fuso 
horário atual, verá que o TimeZone definido está o mesmo. Então, para 
que as alterações sejam aplicadas, é fundamental reiniciar o serviço do 
PostgreSQL. Lembra-se do comando das Unidades anteriores que lhe 
apresentamos para reiniciar o serviço PostgreSQL? Vejamos:
postgres=# SELECT pg_reload_conf();
Após a execução, podemos listar o fuso horário e verificar a troca. 
Vejamos o retorno do procedimento executado na figura 3.
Figura 3 – Mudança da TimeZone do PostgreSQL
Fonte: Elaborado pela autora (2021).
Administração do SGBD PostgreSQL
22
As configurações do Postmaster afetam todo o servidor (postmaster 
representa o serviço PostgreSQL) e entram em vigor somente após a 
reinicialização.
Agora que você está expert vamos mergulhar mais um pouco no 
assunto. Vamos ampliar a visão de leitura com as seguintes especificações: 
listen_addresses, deadlock_timeout, shared_buffers, effective_cache_
size, work_mem, maintenance_work_mem.
Vejamos o comando dessa visão por meio do pg_settings:
SELECT 
name, 
context , 
unit , 
setting, 
boot_val, 
reset_val 
FROM pg_settings 
WHERE name IN ('listen_addresses','deadlock_timeout','shared_
buffers', 'effective_cache_size','work_mem','maintenance_work_mem') 
ORDER BY context, name;
As configurações com contexto de usuário ou superusuário podem 
ser definidas para um banco de dados específico, usuário, sessão e nível 
de função. 
Suponha que você pode querer definir work_mem mais alto para 
um usuário de nível guru SQL que escreve consultas incompreensíveis. 
Então, se você tem uma função que faz uso intensivo de classificação, 
pode gerar work_mem apenas para ela. As configurações definidas nos 
níveis de banco de dados, usuário, sessão e função não requerem uma 
recarga e as configurações definidas no nível do banco de dados entram 
em vigor na próxima conexão com o banco de dados e as configurações 
definidas para a sessão ou função entram em vigor imediatamente.
Administração do SGBD PostgreSQL
23
Ainda assim, faz-se necessário que o administrador do banco de 
dados sempre realize uma segunda verificação das configurações do 
servidor PostgreSQL.
O comando ALTER SYSTEM do PostgreSQL e postgresql.auto.conf 
fornecem uma maneira conveniente de alterar a configuração de todo 
o cluster de banco de dados sem que o administrador precise editar o 
arquivo postgresql.conf manualmente. 
No PostgreSQL é possível usar o postgresql.auto.conf para 
sobrescrever os parâmetros de configuração em postgresql.conf. Dessa 
forma, o postgresql.auto.conf possui uma prioridade mais alta em relação 
ao postgresql.conf.
Sendo assim, vamos usar o ALTER SYSTEM para melhorar o 
desempenho do servidor PostgreSQL definindo a quantidade de memória 
disponível para as operações internas de classificação. Lembrando 
sempre de executar o comando de reinicialização do servidor:
postgres=# ALTER SYSTEM SET work_mem = '500MB'; 
postgres=# SELECT pg_reload_conf();
Se você tiver de rastrear várias configurações, é possível organizá-
las em vários arquivos de configuração e, em seguida, vinculá-los de volta 
usando o include ou include_if_exists no postgresql.conf, cujo argumento 
do nome do arquivo pode ser um caminho absoluto ou relativo do arquivo 
postgresql.conf. Vejamos a sintaxe do comando:
include 'filename'
Caso você edite o arquivo postgresql.conf e o servidor esteja com 
problemas para iniciar, você poderá verificar o ocorrido examinando o 
arquivo de log. Tal arquivo está localizado na raiz da pasta de dados ou na 
subpasta chamada pg_log. Para isso, abra o arquivo mais recente e leia o 
que diz a última linha. O erro levantado geralmente é autoexplicativo.
Uma das maiores causas de erros na inicialização do servidor 
PostgreSQL é a definição de shared_buffers muito altos, cujos valores 
Administração do SGBD PostgreSQL
24
recomendados para um bom desempenho são de 15% a 28% da memória 
RAM total do servidor. 
Lembra que te falamos que não há uma configuração padrão? Pois 
é! Cada servidor precisa ser configurado conforme suas necessidades. 
Por exemplo: se o tamanho da memória RAM for de 32 GB, então o valor 
recomendado para o shared_buffers é de 8 GB. No arquivo postgresql.conf 
podemos editar e especificar o valor na seguinte linha:
shared_buffers = 8GB
Outro procedimento de configuração para melhorar o desempenho 
do servidor PostgreSQL é o parâmetro work_mem que, se bem configurado, 
poderá resultar em menos trocas de disco e, consequentemente, as 
consultas SQL serão mais rápidas. 
Todavia, para saber o valor ideal do work_mem a ser configurado, 
fazemos a seguinte fórmula:
Total de memória RAM * 0,25 / max_connections
Em que o max_connections é o número máximo de conexões 
simultâneas que, por padrão do PostgreSQL, é de 100 conexões.
O valor do work_mem é definido no arquivo postgresql.conf ou 
diretamente a uma função. Vejamos as duas formas: 
 • No arquivo postgresql.conf:
work_mem = 4MB
 • Atribuído diretamente na função:
postgres=# alter user teste set work_mem = ‘4MB’; 
ALTER ROLE
No caso de melhorar o desempenho do vacuuming, alteração de 
tabelas e criação de índices, recomenda-se a configuração do parâmetro 
maintenance_work_mem, que especifica o limite máximo de memória a 
ser utilizada. 
Administração do SGBD PostgreSQL
25
Apesar de o valor do maintenance_work_mem já vir especificado 
com um padrão do PostgreSQL, o ideal é defini-lo mais alto do que o 
work_mem, cujo cálculo do melhor valor que o administrador fizer é:
Total de memória RAM * 0,05
Nesse caso, vejamos a sintaxe do valor no arquivo postgresql.conf:
maintenance_work_mem = 64MB
SAIBA MAIS:
Gostou do assunto? Quer se aprofundar neste tema? No 
site do EDB Postgres Advanced Server é disponibilizada 
uma lista de parâmetros mais utilizados para a manutenção 
do desempenho do servidor PostgreSQL. Nele, você pode 
verificar os valores padrão dos parâmetros e as requisições 
necessárias para os efeitos de mudança no servidor, que 
podem ser: imediatos, após a reinicialização ou com reload. 
Disponível aqui.
RESUMINDO:
E então, gostou do que lhe mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo o que vimos. Você deve ter aprendido 
que o arquivo postgresql.conf controla as configurações 
da manutenção do servidor PostgreSQL e, por meio dele, 
é possível substituir muitas configurações no banco de 
dados, função, sessão e até mesmo níveis de função. Vimos 
como usar um arquivo adicional chamado postgresql.auto.
conf e como modificar por meio do pg_settings. Além disso, 
vimos exemplos práticos de configurações para a melhoria 
do desempenho do servidor PostgreSQL, tais como: work_
mem e maintenance_work_mem.
Administração do SGBD PostgreSQL
https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user
26
Backup e recuperação
OBJETIVO:
Ao término deste capítulo você será capaz de entender 
como realizar backup para garantir integridade dos dados 
no PostgreSQL, utilizando várias estratégias. Isso será 
fundamental para o exercício de sua profissão, pois, após 
alguma execução errada no servidor que corrompeu ou 
perdeu dados, o backup e a recuperação dos dados são 
práticas essenciais para a proteção e integridade dos 
dados. É muito comum ouvirmos relatos da área em que 
as pessoas que tentaram realizar ajustes indevidos no 
servidor sem ter executado o procedimento de backup 
perderam dados valiosos e irrecuperáveis da empresa. 
E então, motivado para desenvolver essa competência? 
Então vamos lá. Avante!
Backup no PostgreSQL
PostgreSQL suporta backups físicos e lógicos. O backup físico é 
executado copiando os arquivos do banco de dados e os arquivos WAL 
acumulados durante a cópia dos arquivos. Já o backup lógico é usado para 
fazer a cópiade segurança do banco de dados na forma de instruções 
SQL. Ao contrário do backup físico, é possível descartar e restaurar um 
único banco de dados, uma tabela ou até mesmo um conjunto de dados 
específico.
Frequentemente, um banco de dados pode conter vários objetos 
não utilizados ou dados muito antigos. Limpar esses objetos ajuda os 
administradores a fazer backup de imagens mais rapidamente. Do ponto 
de vista do desenvolvimento, os objetos não utilizados são semelhantes 
ao ruído silencioso porque eles afetam o processo de refatoração.
Para o caso de aplicativos de banco de dados, é necessário manter 
o banco de dados limpo, pois os objetos de banco de dados não utilizados 
podem impedir o desenvolvimento rápido por causa das dependências 
desses objetos. 
Administração do SGBD PostgreSQL
27
Diante desse contexto, para limpar o banco de dados, é essencial 
a identificação dos objetos de banco de dados não usados, incluindo 
tabelas, visualizações, índices e funções. 
As estatísticas da tabela (tais como: o número de linhas ativas, 
varreduras de índice e varreduras sequenciais) podem ajudar a identificar 
tabelas vazias e não utilizadas. Observe que as consultas a seguir são 
baseadas em estatísticas, portanto, os resultados precisam ser validados. 
Para isso, a tabela pg_stat_user_tables fornece essas informações. 
Vejamos no exemplo de consulta, a seguir, que é possível verificar tabelas 
vazias por meio do número de tuplas:
SELECT relname FROM pg_stat_user_tables WHERE n_live_tup= 0;
No caso das colunas vazias ou não utilizadas, para localizar, é 
preciso verificar o atributo null_fraction da tabela pg_stats. Caso o null_
fraction seja igual a 1, então significa que a coluna está completamente 
vazia. Vejamos o comando:
SELECT schemaname, tablename, attname 
FROM pg_stats 
WHERE null_frac= 1 and schemaname 
NOT IN ('pg_catalog', 'information_schema');
O PostgreSQL vem com três utilitários para backup dentro da pasta 
bin: pg_dump, pg_dumpall e pg_basebackup. Use o pg_dump para fazer 
backup de bancos de dados específicos. No caso do backup de todos os 
bancos de dados em texto simples e junto aos globais do servidor, use 
pg_dumpall. Já para o backup de disco em nível de sistema de todos os 
bancos de dados, use o pg_basebackup.
O pg_dumpall precisa ser executado em uma conta de superusuário 
para que faça backup de todos os bancos de dados.
O pg_basebackup é a forma mais completa de fazer um backup de 
todo o cluster do servidor postgresql. Contudo, será uma discussão para 
o nosso próximo capítulo sobre replicação. Sendo assim, falaremos sobre 
o pg_dump, pg_dumpall.
Administração do SGBD PostgreSQL
28
O utilitário pg_dump
De acordo com a documentação do PostgreSQL, o pg_dump é um 
utilitário que realiza backup em SQL simples, e, também, em formatos 
compactados, TAR e de diretório. Backups compactados, TAR e de diretório 
podem aproveitar o recurso de restauração paralela do pg_restore. 
Considerando um banco de dados de grande porte, os backups 
de diretório são os ideais, pois permitem que o pg_dump trabalhe em 
paralelo. O backup em texto simples é um arquivo com os comandos 
SQL CREATE e INSERT padrão que pode copiar comandos específicos 
do psql. Sendo assim, é possível restaurar executando o arquivo no psql 
ou pgAdmin. Tratando-se de backup de rotina (do dia a dia), o pg_dump é 
mais rápido em relação ao pg_dumpall porque o pg_dump pode realizar 
backup de tabelas, esquemas e bancos de dados de forma seletiva.
O pg_dump faz o procedimento em apenas um único banco de 
dados. Para fazer backup de um cluster inteiro ou de objetos globais que 
são comuns a todos os bancos de dados em um cluster (como funções e 
espaços de tabela), o ideal é o pg_dumpall.
Os dumps (ou cópias de segurança) podem ser restaurados a partir 
de dump scripts enviados para o psql. Os arquivos de script podem ser 
usados para reconstruir o banco de dados mesmo em outras máquinas 
e outras arquiteturas; com algumas modificações, mesmo em outros 
produtos de banco de dados SQL.
Os formatos alternativos de arquivo devem ser usados com pg_
restore para reconstruir o banco de dados. Eles permitem que o pg_restore 
seja seletivo sobre o que é restaurado, ou mesmo reordene os itens antes 
de serem restaurados. Os formatos de arquivo são projetados para serem 
portáveis em todas as arquiteturas
PostgreSQL (versão 13.0 em 24 de setembro de 2020) do Grupo de 
Desenvolvimento PostgreSQL sobre o pg_dump, afirma-se que ele pode 
ser usado para fazer o backup de um banco de dados inteiro em conjunto 
com o pg_restore para examinar o arquivo e/ou selecionar quais partes do 
banco de dados devem ser restauradas. 
Administração do SGBD PostgreSQL
29
De acordo com o Grupo de Desenvolvimento PostgreSQL (2020), 
os formatos de arquivos mais saída flexível são o formato custom (-Fc) 
e o formato directory (-Fd). Esses formatos permitem a seleção e o 
reordenamento de todos os itens arquivados. Ainda, oferecem suporte à 
restauração paralela e são compactados por padrão. O formato directory é 
o único que suporta dumps paralelos. Vejamos algumas formas para criar 
backups de banco de dados:
 • Backup de banco de dados comprimido e único:
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f nomeBanco_
bd.backup nomeBanco_bd
 • Backup de banco de dados único de texto simples, incluindo uma 
instrução do tipo CREATE DATABASE, podemos usar o seguinte 
comando:
pg_dump -h localhost -p 5432 -U postgres -C -F p -b -v -f 
nomeBanco_bd.backup nomeBanco_bd
 • Backup de banco de dados comprimido de tabelas. Vejamos um 
exemplo de backup de todas as tabelas do banco de dados que 
comecem com a palavra “filmes”:
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -t *.filmes* -f 
filmes.backup nomeBanco_bd
 • Backup de banco de dados comprimido de todos os objetos em 
todos os esquemas, exceto para o esquema público:
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -N public -f 
todos_sch_exceto_pub.backup nomeBanco_bd
 • Backup de banco de dados de SQL em texto simples de tabelas 
selecionadas:
pg_dump -h localhost -p 5432 -U postgres -F p --column-inserts -f 
select_tabelas.backup nomeBanco_bd
Administração do SGBD PostgreSQL
30
O backup de banco de dados de SQL em texto é bastante útil para 
importar a estrutura e dados para versões mais antigas do PostgreSQL ou 
de outros bancos de dados.
Dessa forma, é possível escolher o diretório de backup de cada 
tabela, por exemplo: um arquivo separado por pasta. Consequentemente, 
evitará possíveis limitações de espaço no sistema de arquivos.
 • Backup de banco de dados paralelo com uso do Jobs --jobs (-j):
pg_dump -h localhost -p 5432 -U postgres -j 3 -Fd -f 
diretorioEscolhido/ 
nomeBanco_bd
Com o backup de banco de dados paralelo usando –jobs=3, é 
executado três backups simultaneamente. O ideal para o backup paralelo 
é fazer o uso da opção de formato de diretório, pois cada gravação deverá 
ser salva em um arquivo separado.
 • Backup de banco de dados para gerar novo diretório e preencher 
com um arquivo gzip para cada tabela, isto é, irá criar diversos 
arquivos de backup:
pg_dump -h localhost -p 5432 -U postgres -F d -f diretorioEscolhido/ 
nomeBanco_bd
Nesse tipo de backup, caso o diretório já exista, o comando irá 
enviar um erro para a saída.
Depois de todos esses comandos, você ficou curioso(a) para saber 
para que servem as opções das linhas de comando? Você se perguntou 
sobre o propósito delas e da possibilidade de controlar o conteúdo e o 
formato de saída do backup?
Pois bem! Como falamos anteriormente, o Grupo de Desenvolvimento 
PostgreSQL lançou sua mais nova versão (13.0 de 24 de setembro de 
2020) elencando as opções de linha de comando e a funcionalidade para 
o controle do conteúdo e formato de saída na documentação. Vejamos as 
mais importantes que utilizamos em nossos exemplos:
Administração do SGBD PostgreSQL
31• Dbname – especifica o nome do banco de dados a ser salvo. Se 
isso não for especificado, a variável de ambiente pgdatabase será 
usada. Se isso não for definido, o nome de usuário especificado 
para a conexão será usado.
 • --a ou --data-only – descarrega apenas os dados, excluindo os 
schemas. Nele irá os dados da tabela, objetos grandes e valores 
de sequência.
 • -b ou –blobs – descarrega os objetos grandes, exceto quando 
--shema, -- table, ou --schema-only são especificados.
 • -B ou --no-blobs – exclui os objetos grandes do dump.
 • -c ou –clean – comandos de saída para limpar (descartar) objetos 
de banco de dados antes de enviar os comandos para criá-los.
 • -C ou –create – comece a saída com um comando para criar o 
próprio banco de dados e reconecte-se ao banco de dados criado. 
(Com um script dessa forma, não importa a qual banco de dados 
na instalação de destino você se conecte antes de executar o 
script.) Se --clean também for especificado, o script descarta e 
recria o banco de dados de destino antes de se reconectar a ele.
 • -E encoding ou –encoding=encoding – cria o dump na codificação 
do conjunto de caracteres especificado. Por padrão, o dump é 
criado na codificação do banco de dados.
 • -f file ou --file=file – envia a saída para o arquivo especificado. Esse 
parâmetro pode ser omitido para formatos de saída baseados em 
arquivo, caso em que a saída padrão é usada. Deve ser fornecido 
para o formato de saída do diretório, no entanto, onde especifica o 
diretório de destino em vez de um arquivo. Nesse caso, o diretório 
é criado por pg_dump e não deve existir outro diretório com o 
mesmo nome.
 • -F format ou --format=format – seleciona o formato da saída, cujo 
valor de format pode ser um dos seguintes:
 • p ou plain – produz um arquivo de script SQL de texto simples (o 
padrão).
Administração do SGBD PostgreSQL
32
 • c ou custom – produz um arquivo de formato personalizado 
adequado para entrada no pg_restore. Junto com o formato de saída 
do diretório, esse é o formato de saída mais flexível, pois permite 
a seleção manual e a reordenação de itens arquivados durante a 
restauração. Esse formato também é compactado por padrão.
 • d ou directory – produza um arquivo em formato de diretório 
adequado para entrada no pg_restore. Isso criará um diretório com 
um arquivo para cada tabela e blob sendo salvos, além de um 
arquivo de índice que descreve os objetos salvos em um formato 
legível por máquina que o pg_restore pode ler.
 • t ou tar – produz um arquivo -format adequado para entrada no 
pg_restore. No entanto, o formato tar não suporta compactação. 
Além disso, ao usar o formato tar, a ordem relativa dos itens de 
dados da tabela não pode ser alterada durante a restauração.
 • -t pattern ou --table=pattern – descarrega apenas as tabelas com 
nomes correspondentes pattern. Para esse propósito, “tabela” inclui 
views, views materializadas, sequências e tabelas estrangeiras. 
Ainda, várias tabelas podem ser selecionadas escrevendo vários -t. 
 • -v ou –verbose – especifica o modo detalhado. Isso fará com que 
o pg_dump exiba: comentários detalhados do objeto; horários de 
início/parada para o arquivo de dump; e mensagens de progresso 
para o erro padrão.
 • -V ou –version – imprime a versão do pg_dump e sai.
 • -x ou --no-privileges ou --no-acl – impede o armazenamento de 
privilégios de acesso (comandos conceder/revogar).
O utilitário pg_dumpall
O utilitário pg_dumpall cria um dump lógico para todo o cluster 
PostgreSQL, isto é, faz um backup de todos os bancos de dados em 
um servidor em um único arquivo de texto simples. Esse tipo de backup 
abrangente inclui, automaticamente, os globais do servidor, como 
definições e funções de espaço de tabela. 
Administração do SGBD PostgreSQL
33
VOCÊ SABIA?
Você sabia que vários especialistas recomendam realizar 
o backup de globais diariamente? Pois é! E embora você 
também possa usar o pg_dumpall para fazer backup dos 
bancos de dados, também é possível fazer backup dos 
bancos de dados individualmente usando pg_dump ou 
usando pg_basebackup para fazer um backup de nível de 
serviço PostgreSQL.
Tal procedimento também é bastante utilizado por vários 
especialistas, pois restaurar a partir de um enorme backup de texto 
simples consome mais tempo no processo de backup. 
No caso de recuperação de dados, usar pg_basebackup em 
conjunto com a replicação de streaming é a maneira mais rápida de se 
recuperar de uma falha grave do servidor.
pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql -- 
globals-only
Vejamos como fazer o backup de todas as definições globais e de 
apenas espaço de tabela por meio do seguinte comando:
No caso da realização do backup de configurações globais 
específicas, use o seguinte:
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql 
--rolesonly
Uma vez que o pg_dumpall lê as tabelas de todos os bancos de 
dados, é provável que seja necessária a conexão como um superusuário 
do banco de dados para produzir uma cópia de segurança completa. 
Além disso, precisará de privilégios de superusuário para executar o script 
salvo para poder adicionar funções e criar bancos de dados.
O pg_dumpall precisa se conectar várias vezes ao servidor 
PostgreSQL (uma vez por banco de dados). Se você usar autenticação de 
senha, ela solicitará uma senha todas as vezes.
Administração do SGBD PostgreSQL
34
Restauração no PostgreSQL
Assim como fizemos com os backups no PostgreSQL, vamos 
aprender sobre restauração no servidor PostgreSQL, cujo processo é tão 
simples quanto o que mostramos com os backups.
Há duas formas de restaurar os dados do PostgreSQL: (i) usando o 
psql para restaurar backups de texto simples que foram gerados pelos 
pg_dump e pg_dumpall; e (ii) usando o utilitário pg_restore para restaurar 
backups TAR, comprimidos e do diretório criado pelo pg_dump.
De acordo com Obe e Hsu (2017, p. 79), um backup simples de SQL 
é um arquivo de texto que contém um script SQL robusto. Com o backup 
SQL, você deve executar o script inteiro. Você não pode escolher objetos 
a menos que queira editar o arquivo manualmente. Um backup simples 
de SQL é o backup menos conveniente de se ter, mas é o mais versátil.
Vamos à restauração usando psql? Para isso, vejamos os exemplos 
a seguir. Você poderá executar no console do sistema operacional ou no 
psql:
 • Para restaurar um backup completo e ignorar erros:
psql -h localhost -U postgres --port=5432 -f myglobals.sql
 • Para restaurar, parando se algum erro for encontrado:
psql -h localhost -U postgres --port=5432 --set ON_ERROR_STOP=on 
-f myglobals.sql
 • Para restaurar um banco de dados específico:
sql -h localhost -U postgres --port=5432 -d mydb -f select_objects.sql
Agora que você já conhece a restauração usando psql, vejamos 
como realizar usando o utilitário pg_restore. 
Agora, com o pg_restore, é possível realizar algumas operações em 
paralelo para melhorar a velocidade de carregamento em sistemas que 
são limitados pela velocidade da CPU em vez do rendimento do disco.
Administração do SGBD PostgreSQL
35
Para realizar as operações de restauração em paralelo, você pode 
executar a opção -j para controlar o número de threads que serão usados 
e, com isso, diminuir o tempo de execução do processo.
Além de permitir realizar restaurações seletivas, o pg_restore 
também permite que você possa criar um arquivo de índice de seu 
arquivo de backup para confirmar o que foi feito de fato e, ainda, pode 
editar esse índice e usar o arquivo revisado para controlar quais objetos 
devem ser restaurados.
Para realizar a restauração usando o pg_restore, temos de criar um 
banco de dados em SQL, seja de forma separada ou em uma única etapa. 
Vejamos os exemplos a seguir das duas formas de restauração:
 • Na restauração de forma separada, primeiro criamos o banco de 
dados e depois usamos o comando de restauração:
CREATE DATABASEnomeBanco_bd;
pg_restore -h localhost -p 5432 -U postgres --dbname=nomeBanco_
bd --jobs=3 --verbose nomeBanco_bd.backup
 • Na restauração do banco de dados em etapa única:
pg_restore -h localhost -p 5432 -U postgres --dbname =postgres 
--create --jobs=4 --verbose nomeBanco_bd.backup
Usando a opção --create, o nome do banco de dados deve ser o 
mesmo que você nomeou no backup e não pode renomeá-lo. Se você 
também estiver usando a opção --dbname, nesse caso, o nome do banco 
de dados deve ser diferente do nome do banco de dados que está sendo 
restaurado.
Administração do SGBD PostgreSQL
36
VOCÊ SABIA?
Gostou do assunto? Quer se aprofundar nesse tema? No 
site do Grupo de Desenvolvimento do PostgreSQL você 
pode acessar a documentação atualizada (24 de setembro 
de 2020) da versão 13.0 do PostgreSQL. Disponível aqui.
RESUMINDO:
E então, gostou do que lhe mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo o que vimos. Você deve ter aprendido 
que há várias formas de realizar um backup e restauração no 
PostgreSQL. Vimos como usar os utilitários pg_dump e pg_
dumpall para ambos os backups no servidor PostgreSQL. 
Além disso, vimos como usar o utilitário pg_restore para 
realizar a restauração de duas formas: (i) usando o psql para 
restaurar backups de texto simples que foram gerados 
pelos pg_dump e pg_dumpall; e (ii) usando o utilitário 
pg_restore para restaurar backups TAR, comprimidos e do 
diretório criado pelo pg_dump.
Administração do SGBD PostgreSQL
https://www.postgresql.org/docs/current/app-pg-dumpall.html
37
Replicação nativa
OBJETIVO:
Ao término deste capítulo você será capaz de compreender 
como criar e replicar servidores escravos (standby) no 
PostgreSQL, criando redundâncias e espelhamento de 
banco de dados. Isso será fundamental para o exercício 
de sua profissão, pois a replicação dos dados é uma das 
formas de garantir a alta disponibilidade e confiabilidade 
dos dados. Duplicar e fazer backup dos dados é sempre 
uma boa prática, mas quando se trata de disponibilidade, 
precisamos de cópias do banco de dados. Muitas empresas 
se reestabelecem rapidamente após algum desastre/
fatalidade com a ajuda da replicação dos dados. Nesse 
aspecto, a replicação é essencial para uma recuperação 
eficaz e é normalmente utilizada em grandes companhias. 
E então, motivado(a) para desenvolver essa competência? 
Então vamos lá. Avante!
Antes de copiar qualquer dado, é essencial determinar quais dados 
queremos copiar. Em alguns casos, pode ser necessário copiar todo o 
banco de dados para fins de recuperação de desastres. Já em outras 
ocasiões, essa cópia desperdiçaria recursos. 
Ainda assim, devemos diferenciar esses dois cenários, pois, depois 
de fazer isso, é necessário que decidamos o que fazer quando não 
quisermos copiar todo o banco de dados. Precisamos saber quais tabelas 
copiar e para onde enviá-las. 
O PostgreSQL nos fornece métodos para construir e manter uma 
cópia totalmente on-line do banco de dados primário. Além disso, existem 
utilitários para duplicar tabelas quando não precisamos de uma cópia de 
todo o banco de dados. 
Thomas (2020, p. 332) afirma que a partir da versão 10, o PostgreSQL 
oferece suporte nativo à replicação lógica usando estruturas de publicação 
e assinatura. Eles são gerenciados diretamente pelo analisador de sintaxe 
PostgreSQL como SQL nativo e criam objetos no catálogo do sistema que 
são gerenciados como todo o resto.
Administração do SGBD PostgreSQL
38
Dessa forma, facilita a criação de listas de tabelas para transmitir 
aos servidores PostgreSQL destinatários localizados em outro lugar. Isso 
também significa que as ferramentas PostgreSQL padrão podem interagir 
com os conjuntos de assinaturas e tabelas de maneiras que as extensões 
não podem reproduzir. Esse procedimento irá criar um conjunto básico de 
tabelas e explicar um pouco sobre as limitações desse tipo de replicação 
lógica. Ainda, vamos repassar as etapas para replicar todo o cluster de 
servidor. Aproveitaremos a replicação de streaming. 
Lembre-se de que a replicação de streaming requer apenas 
conexões no nível do banco de dados PostgreSQL entre o mestre e 
os escravos (standby). Os mestres podem enviar dados, enquanto os 
standbys (ou escravos) são sempre receptores de dados replicados. 
Vejamos o passo a passo para a configuração do servidor master:
1. Na máquina master, iremos realizar as seguintes configurações:
a. Vamos alterar as seguintes configurações em postgresql.conf. Isso 
pode ser feito usando ALTER SYSTEM set variable = value seguido 
por SELECT pg_reload_conf (); sem a necessidade de abrir o 
arquivo de configuração físico:
listen_addresses = * 
wal_level = hot_standby 
archive_mode = on 
max_wal_senders = 5 
wal_keep_segments = 10
b. Alternativamente, também podemos abrir o arquivo postgresql.
conf e realizar os ajustes necessários. Para isso, podemos aplicar o 
seguinte comando:
sudo -u postgres vim /etc/postgresql/12/main/postgresql.conf
Dentro do arquivo postgresql.conf, vamos alterar as seguintes 
configurações:
Administração do SGBD PostgreSQL
39
listen_addresses = '*' 
wal_level = 'hot_standby' 
max_wal_senders = 10 
wal_keep_segments = 100 
hot_standby = on 
archive_mode = on 
archive_command = '/bin/true' 
max_replication_slots = 5
2. O próximo passo é criar uma conta de superusuário PostgreSQL 
dedicada e um banco de dados para os dados do repmgr. 
Vejamos:
sudo -u postgres createuser -s repmgr 
sudo -u postgres createdb repmgr -O repmgr
3. O próximo passo da configuração é a atribuição das permissões 
necessárias no arquivo pg_hba.conf para poder realizar a conexão 
em modo replicação:
sudo -u postgres vim /etc/postgresql/12/main/pg_hba.conf
4. Ao final do documento pg_hba.conf, a seguinte linha deverá 
ser adicionada ao final do arquivo para que seja concedida a 
permissão de acesso à replicação:
Administração do SGBD PostgreSQL
40
5. Após isso, deve-se reiniciar o servidor para que as configurações 
sejam aplicadas:
sudo service postgresql restart
6. Reinicializado o servidor, agora o próximo passo é a criação do 
arquivo de configuração do repmgr. Vejamos o comando:
sudo vim /etc/repmgr.conf
7. Criado o arquivo, vamos adicionar a seguinte configuração:
# An arbitrary name for the replication cluster; 
# This must be identical on all nodes 
cluster = 'teste' 
# A unique integer identifying the node 
node = 1 
# A unique string identifying the node; 
# Avoid names indicating the current replication role like 'master' or 
'standby' 
# as the server's role could change. 
node_name = 'node1'
 # A valid connection string for the repmgr database on the current 
server. conninfo = 'host=pgmaster user=repmgr dbname=repmgr' 
# Use replication slot if you enable replication slots in 
# PostgreSQL configuration.
 use_replication_slots = 1
8. A partir de então, o próximo passo será a criação de um caminho 
de pesquisa do usuário do repmgr para incluir o nome de schema 
com o seguinte comando:
su – postgres
psql
ALTER USER repmgr SET search_path TO repmgr_teste, "$user", 
public;
Administração do SGBD PostgreSQL
41
9. Pronto! Agora vamos inicializar o servidor master para que cada 
servidor no cluster de replicação tenha sua própria gravação, 
cuja atualização ocorrerá quando o status ou a função forem 
alterados:
sudo -u postgres repmgr -f /etc/repmgr.conf master register
su -postgres
psql
postgres =# \c repmgr
repmgr=# SELECT * FROM repmgr_teste.repl_nodes;
E então? Acompanhou bem o passo a passo? Vimos como configurar 
o servidor master. Agora, iremos te mostrar como configurar o servidor 
standby (ou escravo).
No servidor standby (ou escravo) vamos fazer algumas alterações 
para efetivar o nó entre o servidor master e standby. Vejamos:
1. Criar um arquivo para o repmgr da seguinte forma:
sudo vim /etc/repmgr.confcluster = 'teste' 
node = 2
node_name = 'node2' 
conninfo = 'host=pgslave user=repmgr dbname=repmgr' use_
replication_slots = 1
Para fins de teste, o ideal é fazer um ping para o slave. Para testar 
podemos dar o comando: ping pgslave e observar se está se comunicando 
com o IP válido da rede.
2. O próximo passo que iremos te mostrar é como parar o serviço 
do servidor PostgreSQL no servidor escravo e remover o diretório 
de dados do PostgreSQL e, ainda, clonando o banco de dados 
do servidor master no servidor escravo.
Administração do SGBD PostgreSQL
42
sudo service postgresql stop 
sudo -u postgres rm -r /var/lib/postgresql/12/main 
sudo -u postgres repmgr -h pgmaster -U repmgr -d repmgr -D /var/
lib/postgresql/12/main -f /etc/repmgr.conf standby clone 
sudo mv /var/lib/postgresql/12/main/pg_hba.conf /etc/
postgresql/12/main/pg_hba.conf 
sudo mv /var/lib/postgresql/12/main/postgresql.conf /etc/
postgresql/12/main/postgresql.conf 
sudo mv/var/lib/postgresql/12/main/pg_ident.conf/etc/
postgresql/12/main/pg_ident.conf 
sudo service postgresql restart
sudo service postgresql status
3. O próximo passo é o registro do servidor para o modo standby:
sudo -u postgres repmgr -f /etc/repmgr.conf standby register
4. Pronto! Agora, iremos realizar a conexão do banco de dados 
repmgr do servidor escravo e verificar a tabela repl_nodes:
repmgr=# SELECT * FROM repmgr_teste.repl_nodes ORDER BY id;
Nesse momento, o servidor escravo possui uma cópia dos registros 
para todos os servidores no cluster de replicação. Note que a relação 
entre o servidor master e o escravo é explicitamente definida por meio do 
valor upstream_node_id, 
5. Agora, vamos mostrar que o servidor do escravo é o “mestre” do 
cluster de replicação. Vejamos:
repmgr=# SELECT * FROM repmgr_teste.repl_nodes ORDER BY id;
Prontinho! Finalizamos nossa configuração. Agora, vamos testar 
se está tudo funcionando de forma adequada por meio do seguinte 
comando:
Administração do SGBD PostgreSQL
43
# su - postgres 
$ psql 
=# \c repmgr 
SELECT * FROM pg_stat_replication;
Agora, vamos testar a replicação com a criação de banco de dados 
e verificar se está realizando a replicação. Vejamos:
 • Criando uma tabela para o teste de replicação:
postgres=# CREATE DATABASE teste;
 • Verificação no servidor escravo:
\list
SAIBA MAIS:
Gostou do assunto? Quer se aprofundar nesse tema? No 
site do Grupo de Desenvolvimento do PostgreSQL você 
pode acessar a documentação e verificar as configurações 
que controlam o comportamento do recurso de replicação 
de streaming integrado e seus parâmetros. Disponível aqui.
RESUMINDO:
E então? Gostou do que lhe mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo o que vimos. Você deve ter aprendido 
como criar e replicar servidores escravos (standby) no 
PostgreSQL, criando redundâncias e espelhamento de 
banco de dados. Vimos também como testar a efetividade 
do nó realizado entre o servidor master e escravo do 
PostgreSQL. Além do teste de conexão entre os dois 
servidores, vimos também como testar a replicação de 
uma tabela com a criação de banco de dados e verificar se 
está realizando a replicação.
Administração do SGBD PostgreSQL
https://www.postgresql.org/docs/current/runtime-config-replication.html
44
REFERÊNCIAS
CARNEIRO, A. P.; MOREIRA, J. L; FREITAS, A. L C. de. TUNING-
técnicas de otimização de banco de dados um estudo comparativo: 
mysql e postgresql. 2009. Disponível em: https://bit.ly/3y1Mu4u. Acesso 
em: 20 set. 2020. 
EDB, D. Top Performance Related Parameters. Disponível em: 
https://bit.ly/3rKrVsj. Acesso em: 21 set. 2020.
FRANCO, G.; VALE, L. A importância e influência do setor de compras 
nas organizações. TecHoje. Disponível em: http://bit.ly/35gA60Q. Acesso 
em: 4 jul. 2017. 
OBE, R. O.; HSU, L. S. PostgreSQL: Up and runnin - a practical guide 
to the advanced open source database. [s. l.] O’Reilly Media, Inc., 2017.
The PostgreSQL Global Development Group. Documentação do 
PostgreSQL. 2020. Disponível em: http://pgdocptbr.sourceforge.net/. 
Acesso em: 26 set. 2020.
THOMAS, S. PostgreSQL 12 High Availability Cookbook: Over 100 
recipes to design a highly available server with the advanced features of 
PostgreSQL 12. [s. l.] Packt Publishing Ltd., 2020.
Administração do SGBD PostgreSQL
	Performance tuning
	Configuração de tuning no PostgreSQL
	Performance de tuning para leitura
	Configuração do PostgreSQL
	Backup e recuperação
	Backup no PostgreSQL
	O utilitário pg_dump
	O utilitário pg_dumpall
	Restauração no PostgreSQL
	Replicação nativa