ADMINISTRAÇÃO DO SGBD POSTGRESQL 4

Prévia do material em texto

Administração 
do SGBD 
PostgreSQL
Camila Freitas Sarmento
Diretor Executivo 
DAVID LIRA STEPHEN BARROS
Gerente Editorial 
CRISTIANE SILVEIRA CESAR DE OLIVEIRA
Projeto Gráfico 
TIAGO DA ROCHA
Autora 
CAMILA FREITAS SARMENTO
A AUTORA
Camila Freitas Sarmento
Olá. Meu nome é 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:
INTRODUÇÃO:
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 ................................................................................ 13
Configuração do PostgreSQL ................................................................. 20
Backup e Recuperação ............................................................................ 29
Backup no PostgreSQL ...............................................................................................................29
O Utilitário pg_dump ................................................................................................. 31
O Utilitário pg_dumpall ............................................................................................37
Restauração no PostgreSQL .................................................................................................. 38
Replicação Nativa ....................................................................................... 42
7
CONFIGURAÇÕES, AJUSTES E SEGURANÇA NO POSTGRESQL
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? 
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 às catástrofes. Lembra-se do ataque às Torres 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 causa 
da política de recuperação dos dados. A área de administração de banco 
de dados tem grandes responsabilidades, e você pode fazer parte disso. 
Ao longo desta unidade letiva, você vai mergulhar neste universo!
Administração do SGBD PostgreSQL
9
OBJETIVOS
Olá. Seja muito bem-vindo à Unidade 4. Nosso propósito é auxiliar 
você no desenvolvimento das seguintes objetivos de aprendizagem 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? 
Ao trabalho! 
Administração do SGBD PostgreSQL
10
Performance Tuning
INTRODUÇÃO:
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 de 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 de acessos. E 
então, motivado para desenvolver essa competência? 
Vamos lá!.
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 eles 
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 será o próximo gargalo até a remoção do atual, e 
tentar conjecturar pode ser um trabalho sem eficácia.
Administração do SGBD PostgreSQL
11
EXPLICANDO MELHOR:
Imagine que há um gargalo no servidor e que 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, como 
alocação de memória em operações de CPU. O mesmo 
acontecerá 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 realizar a correta configuração do banco 
de dados, sendo apenas a primeira etapa no ajuste de desempenho.
NOTA:
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. As etapas são:
 • Transmissão de string de consulta para o backend do banco 
de dados: essa é a etapa do comando SQL digitado a partir do 
aplicativo e do envio para o backend. Para melhorar a performance, 
o desenvolvedor pode inserir as queries que são muito longas no 
banco de dados como um procedimento armazenado e, dessa forma, 
reduzir a transferência de dados ao mínimo.
Administração do SGBD PostgreSQL12
 • 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: 
quando o servidor inicializa o trabalho e verifica se a query já está 
pronta e se a biblioteca do cliente suporta esse recurso, além de 
analisar o SQL para determinar a 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 
(como uso de índice, hash join etc.), vai realizar a obtenção cuja etapa 
depende da configuração de hardware.
 • Transmissão de resultados para o cliente: nesta última etapa 
ocorre a transmissão dos resultados ao cliente. Não há opções de 
ajustes para o administrador do banco de dados, mas uma opção de 
melhorar a performance é minimizar o número de linhas e de colunas 
para apenas as necessárias para o cliente.
A configuração do PostgreSQL que já vem implementada por 
padrão após a instalação é uma estimativa conforme um banco de dados 
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 ininterrupta. 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 de 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:
Administração do SGBD PostgreSQL
13
 • 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 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? Ele analisa tabelas, 
coletando estatísticas delas, e constrói histogramas usando auto-
vacuuming.
A opção 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, calculado levando em consideração as entradas e as 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.
Administração do SGBD PostgreSQL
14
IMPORTANTE:
Você se lembra dos comandos BUFFER e ANALYZE das 
unidades anteriores? 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 
decida 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; a 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 (como DISTINCT, LIMIT, ORDER ou GROUP 
BY), se houver 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 a partir 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 tendem a executar em nível de linha, ao invés 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:
Administração do SGBD PostgreSQL
15
 • 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 as 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
16
Exemplo: Vejamos um exemplo de como observar as consultas que 
estão sendo executadas atualmente na base de dados:
Com tal comando, é possível observar as consultas abertas, ativas, 
ociosas e ociosas na transação. Caso haja muitas transações ociosas, é 
essencial investigar o tempo em que elas foram mantidas abertas. Assim, 
podemos utilizar o seguinte comando:
IMPORTANTE:
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 
Administração do SGBD PostgreSQL
17
PostgreSQL, a partir da versão 9.6, implementou um recurso chamado 
snapshot too old, que permite encerrar tais transações suspeitas. 
Exemplo: Vejamos um exemplo:
Nesse caso, todas as consultas ativassã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 consultas lentas individuais no log; ou (ii) 
procurar tipos de consultas que demoram longos períodos.
Para o primeiro caso, o PostgreSQL está, por padrão de instalação, 
desativado. Para ativar a busca por consultas lentas, teremos que definir a 
variável log_min_duration_statement para especificar um limite desejado, 
e o PostgreSQL começa 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 com exatidão o valor desejado, 
pois dependerá das características do banco de dados, e isso está 
agregado ao perfil da empresa, porque, dependendo da carga de trabalho, 
o tempo desejado pode variar. Então, podemos refinar o comando da 
seguinte forma:
Administração do SGBD PostgreSQL
18
NOTA:
Ao usar o log de consulta lenta, é importante considerar 
que muitas consultas menores também podem causar 
mais carga. Obviamente, sempre faz sentido estar cientes 
de consultas lentas individuais, mas, às vezes, elas 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 a permissões etc. Ainda assim, é possível notar que o 
log sempre conterá erros, mas a presença de várias mensagens de 
erro de sintaxe é um indicativo para uma abordagem mais eficiente.
 • FATAL: usado quando houver problemas de alocação de memória, 
por exemplo, quando a 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: 
Artigo: “TUNING - Técnicas de Otimização de Banco de 
Dados Um Estudo Comparativo: Mysql e Postgresql”. 
CARNEIRO et al. (2009), pelo link: https://bit.ly/2GTXV8v
Administração do SGBD PostgreSQL
https://bit.ly/2GTXV8v
19
RESUMINDO:
E então? Gostou do que mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo. 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, 
porque tais valores geralmente são subestimados ou 
superestimados, cuja estimativa incorreta pode afetar 
todo o algoritmo 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 a 
realizar um overview da base de dados por meio do pg_
stat_activity.
Administração do SGBD PostgreSQL
20
Configuração do PostgreSQL
INTRODUÇÃO:
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 
os 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 o adotam e acabam perdendo desempenho por 
falta de conhecimento específico. E então, motivado para 
desenvolver essa competência? Vamos lá!.
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 configurar seu 
servidor ajustando as configurações no arquivo e ajustando 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.
IMPORTANTE:
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 
Administração do SGBD PostgreSQL
21
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. 
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:
Você se lembra 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.
Administração do SGBD PostgreSQL
22
NOTA:
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 
precise trocar o fuso horário do seu servidor PostgreSQL. Para saber qual 
definir, veja a lista completa de todos os fusos horários do PostgreSQL por 
meio do seguinte comando:
postgres=# SELECT * FROM pg_timezone_names;
Então, a partir dessa lista, 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.
Administração do SGBD PostgreSQL
23
Sendo assim, vejamos como trocar o fuso horário no PostgreSQL. 
Observe novamente a Figura 1, em que temos um TimeZone definido 
como “America/Buenos Aires”. Então, suponhamos que você deseje trocar 
para “Europe/Berlin”. Para isso, 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 é 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 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.
Administração do SGBD PostgreSQL
24
IMPORTANTE:
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, ampliando a visão de leitura com as seguintes especificações: 
listen_addresses, deadlock_timeout, shared_buffers, effective_cache_
size, work_mem, maintenance_work_mem.
Exemplo: Vejamos o comando dessa visãopor meio do pg_settings:
As configurações com contexto de usuário ou de superusuário 
podem ser definidas para um banco de dados específico, um usuário, 
uma sessão e um nível de função.
Administração do SGBD PostgreSQL
25
EXPLICANDO MELHOR:
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 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.
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 o 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 tem 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 que 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 o include_if_exists no postgresql.conf, cujo 
Administração do SGBD PostgreSQL
26
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 
recomendados para um bom desempenho são de 15% a 28% da memória 
RAM total do servidor. 
Lembra-se de que não há uma configuração padrão? 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.
Administração do SGBD PostgreSQL
27
O valor do work_mem é definido no arquivo postgresql.conf ou 
diretamente em uma função. Vejamos as duas formas: 
 • No arquivo postgresql.conf:
work_mem = 4MB
 • Definido 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. 
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 pode fazer é:
Total de memória RAM * 0,05
Neste caso, vejamos a sintaxe do valor no arquivo postgresql.conf:
maintenance_work_mem = 64MB
SAIBA MAIS:
Gostou do assunto? Quer se aprofundar nesse tema? No site 
do EDB Postgres Advanced Server é disponibilizada uma 
lista dos parâmetros mais utilizados para a manutenção 
do desempenho do servidor PostgreSQL. Nela 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. 
Acesse o site pelo link https://bit.ly/2T0viIV
Administração do SGBD PostgreSQL
https://bit.ly/2T0viIV
28
RESUMINDO:
E então? Gostou do que mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo. Você deve ter aprendido que o arquivo 
postgresql.conf controla as configurações da manutenção 
do servidor PostgreSQL e que, 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. Aprendemos a usar 
um arquivo adicional chamado postgresql.auto.conf e 
a modificar por meio do pg_settings. Além disso, vimos 
exemplos práticos de configurações para a melhoria do 
desempenho do servidor PostgreSQL, como work_mem e 
maintenance_work_mem.
Administração do SGBD PostgreSQL
29
Backup e Recuperação
INTRODUÇÃO:
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 a integridade dos 
dados. É muito comum ouvirmos relatos da área em que as 
pessoas que tentaram realizar ajustes indevidos no servidor 
sem terem executado o procedimento de backup perderam 
dados valiosos e irrecuperáveis da empresa. Motivado para 
desenvolver esta competência? Então, vamos lá!.
Backup no PostgreSQL
O 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. Já o backup lógico é usado para fazer a 
cópia de segurança do banco de dados na forma de instruções SQL.
NOTA:
Ao contrário do backup físico, no lógico é 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 fazerem 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.
Administração do SGBD PostgreSQL
30
IMPORTANTE:
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 deles. 
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 (como o número de linhas ativas, as 
varreduras de índice e as 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. 
Vejamosno exemplo de consulta, a seguir, em 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;
Para localizar as colunas vazias ou não utilizadas, é preciso verificar 
o atributo null_fraction da tabela pg_stats. Caso o null_fraction seja igual 
a um, então significa que a coluna está completamente vazia. Vejamos o 
comando:
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 os globais do servidor, use 
Administração do SGBD PostgreSQL
31
pg_dumpall. Já para o backup de disco em nível de sistema de todos os 
bancos de dados, use o pg_basebackup.
NOTA:
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 e o pg_dumpall.
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. Os 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 podem copiar comandos específicos do 
psql. Sendo assim, é possível restaurar executando o arquivo no psql ou 
no pgAdmin.
IMPORTANTE:
Tratando-se de backup de rotina (do dia a dia), o pg_dump 
é mais rápido em relação ao pg_dumpall, porque pode 
realizar backup de tabelas, de esquemas e de bancos de 
dados de forma seletiva.
Administração do SGBD PostgreSQL
32
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 em 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.
IMPORTANTE:
Na atualização para a versão mais recente do PostgreSQL 
(versão 13.0, de 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. 
De acordo com o Grupo de Desenvolvimento PostgreSQL 
(2020), os formatos de arquivos mais saída flexível são custom  (-Fc) 
e directory (-Fd). Esses formatos permitem a seleção e o reordenamento 
de todos os itens arquivados, 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:
Administração do SGBD PostgreSQL
33
 • 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 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
NOTA:
O backup de banco de dados de SQL em texto é bastante 
útil para importar a estrutura e os dados para versões mais 
antigas do PostgreSQL ou de outros bancos de dados.
Administração do SGBD PostgreSQL
34
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
NOTA:
Com o backup de banco de dados paralelo usando –
jobs=3, são executados três backups simultaneamente. O 
ideal para o 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
NOTA:
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 para que 
servem as opções das linhas de comando? Você se perguntou sobre o 
Administração do SGBD PostgreSQL
35
propósito delas e da possibilidade de controlar o conteúdo e o formato 
de saída do backup?
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 o formato de saída na documentação. Vejamos 
as mais importantes que utilizamos em nossos exemplos:
 • 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 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 vão os dados da tabela, os objetos grandes e os 
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: começa a saída com um comando para criar o próprio 
banco de dados e reconecta-se ao banco de dados criado (com um 
script desta forma, não importa a qual banco de dados na instalação 
de destino você se conecta 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, casos em quea saída padrão é usada.  Deve ser fornecido 
para o formato de saída do diretório, mas onde se especifica o diretório 
Administração do SGBD PostgreSQL
36
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, e cujo 
valor pode ser um dos seguintes:
 • p ou plain: produz um  arquivo de script  SQL de  texto simples  (o 
padrão).
 • c ou custom: produz um arquivo de formato personalizado adequado 
para entrada no pg_restore. Junto com o de saída do diretório, este 
é 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. Ele também 
é compactado por padrão.
 • d ou directory: produz 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 usá-lo, 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 dump; e mensagens de progresso para 
o erro padrão.
 • -V ou –version: imprime a versão do pg_dump e sai.
 • -x, --no-privileges ou --no-acl: impede o armazenamento de 
privilégios de acesso (comandos conceder/revogar).
Administração do SGBD PostgreSQL
37
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.
VOCÊ SABIA?
Você sabia que vários especialistas recomendam realizar 
o backup de globais diariamente? 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 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.
NOTA:
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.
Vejamos como fazer o backup de todas as definições globais e de 
apenas espaço de tabela por meio do seguinte comando:
pg_dumpall -h localhost -U postgres --port=5432 -f 
myglobals.sql -- globals-only
Administração do SGBD PostgreSQL
38
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.
NOTA:
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.
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 criados 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. Não é possível escolher objetos 
a menos que queira editar o arquivo manualmente.
Administração do SGBD PostgreSQL
39
NOTA:
Um backup simples de SQL é o 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.
VOCÊ SABIA?
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.
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.
Administração do SGBD PostgreSQL
40
IMPORTANTE:
Além de permitir realizar restaurações seletivas, o pg_
restore também possibilita a criação de um arquivo de 
índice de seu arquivo de backup para confirmar o que 
foi feito de fato e, ainda, pode editar este índice e usar o 
arquivo revisado para controlar quais objetos que devem 
ser restaurados.
Para realizar a restauração usando o pg_restore, temos que 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 DATABASE nomeBanco_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
IMPORTANTE:
Usando a opção --create, o nome do banco de dados 
deve ser o mesmo você nomeou no backup, não podendo 
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
41
SAIBA MAIS:
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, por meio do link 
https://bit.ly/3dzKK8n
RESUMINDO:
E então? Gostou do que mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo. Você deve ter aprendido que há 
várias formas de realizar um backup e uma restauração 
no PostgreSQL. Ensinamos a usar os utilitários pg_dump e 
pg_dumpall para ambos 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 backupsde 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://bit.ly/3dzKK8n
42
Replicação Nativa
INTRODUÇÃO:
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 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, além de ser normalmente utilizada 
em grandes companhias. Motivado para desenvolver essa 
competência? Então, vamos lá!.
Antes de copiar qualquer dado, é essencial determinar quais 
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 entre esses dois cenários, 
porque, depois de fazer isso, é necessário decidir 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 online 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 de assinatura. Eles são gerenciados diretamente pelo analisador de 
Administração do SGBD PostgreSQL
43
sintaxe PostgreSQL como SQL nativo e criam objetos no catálogo do 
sistema que são gerenciados como todo o resto.
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 maneira 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 deste tipo de replicação lógica. 
Ainda, vamos repassar as etapas para replicar todo o cluster de servidor. 
Aproveitaremos a replicação de streaming. 
NOTA:
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).
Passo a passo: 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:
Administração do SGBD PostgreSQL
44
b. Alternativamente, também podemos abrir o arquivo postgresql.conf e 
realizar os ajustes necessário. 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:
2. O próximo passo é criar uma conta de superusuário PostgreSQL 
dedicada e um banco de dados para os dados do repmgr. Vejamos:
Administração do SGBD PostgreSQL
45
sudo -u postgres createuser -s repmgr
e
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:
5. Após isso, deve-se reiniciar o servidor para que as configurações 
sejam aplicadas:
sudo service postgresql restart
6. Reinicializado o servidor, 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:
Administração do SGBD PostgreSQL
46
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:
9. Pronto! Agora vamos inicializar o servidor master para que cada 
servidor no cluster de replicação tenha sua própria gravação, e cuja 
atualização ocorrerá quando o status ou a função forem alterados:
Administração do SGBD PostgreSQL
47
E então, acompanhou bem o passo a passo? Vimos como configurar 
o servidor master. Agora vamos te mostrar como configurar o servidor 
Standby (ou escravo).
Passo a passo: No servidor Standby (ou escravo) vamos fazer 
algumas alterações para efetivar o nó entre os servidores master e 
standby. Vejamos:
1. Criar um arquivo para o repmgr da seguinte forma:
NOTA:
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.
Administração do SGBD PostgreSQL
48
2. O próximo passo é parar o serviço do servidor PostgreSQL no servidor 
escravo e remover o diretório de dados do PostgreSQL, além de 
clonar o banco de dados do servidor master no servidor escravo.
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, vamos 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;
Administração do SGBD PostgreSQL
49
NOTA:
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 os servidores 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:
repmgr=# SELECT * FROM repmgr_teste.repl_nodes ORDER 
BY id;
Prontinho! Finalizamos nossa configuração. Vamos testar se está 
tudo funcionando de forma adequada por meio do seguinte comando:
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
Administração do SGBD PostgreSQL
50
SAIBA MAIS:
Gostou do assunto? Quer se aprofundar neste 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. Acesse o link 
https://bit.ly/355IWQP
RESUMINDO:
E então? Gostou do que mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo. Você deve ter aprendido a criar e a 
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 os servidores master e escravo do PostgreSQL. Além 
do teste de conexão entre os dois servidores, estudamos 
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://bit.ly/355IWQP
51
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: http://www.repositorio.furg.br/bitstream/
handle/1/1692/TUNING.pdf?sequence=1. Acesso em: 20 set. 2020. 
EDB, D. Top Performance Related Parameters. Disponível em: https://www.
enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-
guides/user-guide/10/EDB_Postgres_Advanced_Server_Guide.1.24.html. 
Acesso em: 21 set. 2020.
FRANCO, G.; VALE, L. A Importância e Influência do Setor de Compras nas 
Organizações. TecHoje. (s.d.). Disponível em: http://www.techoje.com.br/
site/techoje/categoria/detalhe_artigo/1004. Acesso em: 04 jul. 2017. 
OBE, R. O.; HSU, L. S. PostgreSQL: Up and Running: a Practical Guide to the 
Advanced Open Source Database. “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. Packt Publishing Ltd, 2020.
Administração do SGBD PostgreSQL
http://pgdocptbr.sourceforge.net/
Administração do 
SGBD PostgreSQL
Camila Freitas Sarmento
	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