ADMINISTRAÇÃO DO SGBD POSTGRESQL 3

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
Conexão do PostgreSQL a Aplicações ................................................10
Conectando ao PostgreSQL Remotamente ................................................................ 10
Conectando ao Banco de dados PostgreSQL no Windows .......... 11
Conectando ao Banco de Dados PostgreSQL no Linux ................. 16
Conectando ao Banco de Dados PostgreSQL via PgAdmin ........................... 17
Componentes do PostgreSQL ................................................................21
Componentes de Acesso a Dados do PostgreSQL ............................................... 21
Componentes do Banco de Dados do PostgreSQL ..............................................23
Schemas .............................................................................................................................23
Tabelas .................................................................................................................................26
Componentes do Sistema de Arquivos do PostgreSQL.....................................27
DRBD .....................................................................................................................................27
LVM .........................................................................................................................................28
XFS ..........................................................................................................................................29
Buffering e Sincronização ........................................................................31
PostgreSQL Buffer........................................................................................................................... 31
Sincronização .................................................................................................................................... 36
Procedimento de Fallback ...................................................................... 40
Point in Time Recovery (PITR) no PostgreSQL ........................................................... 41
Replicação Física dos Dados do PostgreSQL ........................................................... 46
7
USANDO POSTGRESQL EM APLICAÇÕES
UNIDADE
03
Administração do SGBD PostgreSQL
8
INTRODUÇÃO
Que o PostgreSQL é o Sistema Gerenciador de Banco de Dados 
de código aberto mais utilizado no mundo você já sabe, não é mesmo? 
Agora, você sabia que uma das maiores preocupações nas empresas 
é como manter a integridade dos dados? Além de conectar o banco 
a aplicações, é preciso que os dados estejam seguros e garantidos 
quanto à recuperação de falhas e, ainda, levando em consideração as 
possibilidades de melhorar o desempenho e o tempo de resposta do 
sistema de gerenciamento do banco de dados. Você, como profissional 
da área, tem a responsabilidade de compreender e gerir essa questão 
da forma mais eficiente possível. Ao longo desta unidade letiva você vai 
mergulhar neste universo! Vamos lá!
Administração do SGBD PostgreSQL
9
OBJETIVOS
Olá. Seja muito bem-vindo à Unidade 3. Nosso propósito é auxiliar 
você no desenvolvimento das seguintes objetivos de aprendizagem até o 
término desta etapa de estudos:
1. Conectar aplicações ao PostgreSQL;
2. Utilizar os principais componentes de PostgreSQL em chamadas 
SQL dentro de aplicações;
3. Realizar os procedimentos de buffering e sincronização de dados 
com o PostgreSQL;
4. Reaver dados perdidos por meio do procedimento de fallback. 
Então? Preparado para uma viagem sem volta rumo ao conhecimento? 
Ao trabalho! 
Administração do SGBD PostgreSQL
10
Conexão do PostgreSQL a Aplicações
INTRODUÇÃO:
 Ao término deste capítulo, você será capaz de entender 
como funciona a conexão do PostgreSQL a aplicações, o 
que será fundamental para o exercício de sua profissão. Sem 
a conexão do PostgreSQL configurada adequadamente, o 
banco de dados só poderá ser acessado localmente. Dessa 
forma, o acesso remoto torna-se inviável. Para evitar esse 
transtorno e habilitar o acesso remoto realizando a conexão 
do PostgreSQL a aplicações, é necessário seguir algumas 
estratégias. E então, motivado para desenvolver essa 
competência? Vamos lá!.
Conectando ao PostgreSQL Remotamente
Nas etapas anteriores, você viu como conectar-se ao PostgreSQL a 
partir da linha de comando usando o psql. Vamos relembrar? É bem simples! 
Na linha de comando, é só digitar: psql nomeDoBanco nomeDoUsuário
. Lembre-se de substituir o nomeDoBanco pelo nome do banco de 
dados e nomeDoUsuário pelo nome do usuário. Em seguida, será 
solicitada a senha do usuário do banco de dados, então, ao digitá-la 
corretamente, o prompt do psql é exibido.
Após a instalação do PostgreSQL, é necessário ajustar dois arquivos 
de configuração para que seja permitido o acesso remoto, pois, por 
padrão, o PostgreSQL vem configurado apenas para acesso local.
Sendo assim, veremos como realizar a conexão configurando 
o acesso ao banco de dados PostgreSQL por meio do pgAdmin, no 
Windows e no Linux.
Administração do SGBD PostgreSQL
11
Conectando ao Banco de dados PostgreSQL no 
Windows
Por padrão, ao instalar o PostgreSQL, é criado o arquivo pg_hba.
conf, utilizado para autenticar os usuários. Não exige uma senha, mas está 
configurado apenas para conexão local.
O pg_hba.conf normalmente fica no seguinte diretório: C:\Program 
Files\PostgreSQL\12\data, cujo arquivo final, a partir da linha 77, deve 
seguir o padrão conforme a Figura 1.
Figura 1 : Arquivo pg_hba.conf
Fonte: Elaborado pela autora.
Vamos compreenderos campos arquivo: 
 • Type – especifica o tipo da conexão, que pode ser: Local (especifica 
que é um host local); Host (pode ser uma conexão criptografada ou 
não); Hostssl (permite apenas conexão criptografada); e Hotnossl 
(permite conexão apenas para texto).
 • Database – é o banco de dados que será liberado para a conexão. 
Seus termos são: sameuser (quando o usuário tiver o mesmo nome 
do banco); samerole (quando o usuário pertencer ao mesmo role do 
banco); e, como o exemplo da Figura 1, all (que são todos os bancos 
de dados locais). Para alguma base de dados específica, basta inserir 
o nome.
 • User – é o usuário que terá acesso ao banco de dados. Neste caso, 
all, significa que todos os usuários podem acessar o banco.
 • Address – é o campo que indica o endereço IP ou a rede na qual há 
a permissão para o acesso ao banco de dados que, no exemplo, é a 
conexão local.
Administração do SGBD PostgreSQL
12
Method – é o método de autenticação do usuário. Trust para 
conexões confiáveis sem senha, e md5 para a solicitação de senha que já 
foi cadastrada previamente no banco de dados.
Exemplo: Vamos tomar como exemplo a seguinte situação: 
você é o administrador do SGBD e precisa disponibilizar o acesso com 
autenticação para o seu colega, Dulbodore, que é do marketing e está na 
rede 10.10.10.7. O que você faria? A resposta é simples! É só acrescentar 
uma linha ao arquivo pg_hba.conf, conforme a Figura 2.
Figura 2 : Disponibilização de acesso de usuário por meio do pg_hba.conf
Fonte: Elaborado pela autora.
Passo a passo: Para a liberação no Windows, é necessário autorizar 
a porta do postgresql no firewall. Lembra-se do número padrão da porta 
do postgres que criamos na unidade anterior? Se você disse 5432, acertou! 
Agora, vamos ao passo a passo para a configuração.
1. Acesse Painel de controle > Sistema e Segurança > Windows Defender 
Firewall (ver Figura 3) e clique em Configurações Avançadas (Figura 4).
2. Ao abrir a nova janela, conforme observado na Figura 5, clique em 
regras de entrada e, depois, em Nova Regra.
3. Na nova janela, escolha a opção Porta. Selecione TCP, digite 5432 (ver 
Figura 6) em Portas Locais Específicas e clique em avançar.
4. Em seguida, escolha a opção Permitir Conexão e clique em avançar.
5. Na nova janela, escolha qual regra se aplica:
a. Domínio: aplica-se quando um computador está conectado ao seu 
domínio corporativo.
Administração do SGBD PostgreSQL
13
b. Particular: aplica-se quando um computador está conectado a um 
local de rede privada, como residência ou local de trabalho.
c. Pública: aplica-se quando um computador está conectado a um local 
de rede pública.
6. Escolha um nome para a nova regra (ex., Acesso externo postgresql) 
e, opcionalmente, uma descrição, então, clique em finalizar para 
confirmar a nova regra criada.
7. Por fim, é só reiniciar ou dar um reload no serviço PostgreSQL (Iniciar > 
executar:services.msc) para ativar as configurações realizadas.
Figura 3 : Caminho para configurações avançadas do firewall
Fonte: Elaborado pela autora.
Administração do SGBD PostgreSQL
14
Figura 4 : Janela de configurações avançadas do firewall do Windows
 
Fonte: Elaborado pela autora.
Figura 5 : Criando uma nova regra
Fonte: Elaborado pela autora.
Administração do SGBD PostgreSQL
15
Figura 6 : Especificando o número da porta para o firewall
Fonte: Elaborado pela autora.
Figura 7 : Confirmação da configuração 
Fonte: Elaborado pela autora.
Administração do SGBD PostgreSQL
16
Conectando ao Banco de Dados PostgreSQL no 
Linux
Para habilitar o acesso remoto ao banco de dados PostgreSQL no 
Linux, é muito simples. Assim como o Windows, o Linux também requer 
a configuração do arquivo pg_hba.conf e do postgresql.conf. Para isso, 
vamos utilizar o editor vi do Linux para editar o primeiro arquivo, pg_hba.
conf, por meio do seguinte comando:
vi /var/lib/pgsql/data/pg_hba.conf
Passo a passo: Agora que você tem o arquivo aberto, siga um passo 
a passo para a configuração remota:
1. Você pode dar permissão para todos os acessos remotos ou para um 
IP específico. Vejamos os dois casos:
a. Para todas as conexões remotas e todos os usuários, iremos 
acrescentar ao documento a seguinte linha:
host all all 0.0.0.0/0 md5
b. Para dar permissão apenas para um usuário e para IP específicos, 
podemos utilizar o seguinte exemplo: adicionar o usuário Dulbodore 
do setor de marketing, cujo IP é 10.10.10.7/24:
host marketing dulbodore 10.10.10.7/24 md5
2. Agora, basta salvar o arquivo e fechar o editor. Assim, nosso próximo 
passo será a edição do arquivo postgresql.conf por meio do seguinte 
comando:
vi /var/lib/pgsql/data/postgresql.conf
a. Ao abrir o arquivo de edição, você irá fazer a busca por listen_
address=‘localhost‘ e remover o comentário (#), se houver. Em 
seguida, poderá trocar o ‘localhost‘ pelo IP desejado ou inserir ‘*‘ para 
habilitar para todos. Vejamos o exemplo com um IP:
listen_address=‘192.168.12.7‘
Administração do SGBD PostgreSQL
17
b. Ainda no mesmo documento, procure a porta e remova o comentário (#):
port = 5432
3. No último passo, salve o documento e reinicie o serviço do PostgreSQL 
por meio do seguinte comando:
service postgresql restart
Conectando ao Banco de Dados 
PostgreSQL via PgAdmin
Passo a passo: Até agora, já sabemos que o pgAdmin é compatível 
com os sistemas operacionais Windows, Linux e Mac. Sendo assim, vamos 
mostrar como se realiza uma conexão ao banco de dados PostgreSQL por 
meio do pgAdmin. 
1. No menu iniciar, busque PgAdmin e abra-o.
NOTA:
Para isso, você precisa ter o pgAdmin instalado no seu 
computador. Como já mostramos o processe de instalação 
na Unidade anterior, vamos considerar que você já o tem 
disponível em sua máquina. Além disso, lembre-se da 
senha que você utilizou. 
2. Na tela inicial, clique com o botão direito em Servers, depois escolha 
Create > Server. Você também poderá utilizar o link de atalho do 
pgAdmin, como pode ser observado na Figura 8.
Figura 8 : Criar uma nova conexão remota
Administração do SGBD PostgreSQL
18
Fonte: Elaborado pela autora.
3. Agora, na aba Connection, basta preencher dos dados de configuração. 
Na Figura 9 podemos observar o seu preenchimento.
Figura 9 : Preenchendo dados de conexão
Fonte: Elaborado pela autora.
Administração do SGBD PostgreSQL
19
4. Pronto! Agora é só clicar no servidor ao lado esquerdo do painel do 
pgAdmin (ver Figura 10) que você deseja manipular.
Figura 10 : Localizando a conexão criada
Fonte: Elaborado pela autora.
SAIBA MAIS:
Quer se aprofundar neste tema? Recomendamos o acesso 
à seguinte fonte de consulta: Artigo: “Como alterar a senha 
de uma base de dados” (BRASIL, 2020), acessível pelo link: 
https://bit.ly/3dtHhZ7
Administração do SGBD PostgreSQL
https://bit.ly/3dtHhZ7
20
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 podemos 
realizar a conexão do PostgreSQL a aplicações por diversas 
formas, seja por linha de comando ou ambiente gráfico. 
Vimos também que, além do Windows e do Linux, é 
possível configurar por meio do pgAdmin, pois é compatível 
com Windows, Linux e Mac. Também, aprendemos a inserir 
permissões de conexão para um usuário específico em um 
ambiente, um grupo ou um endereço IP. Ainda, mostramos 
como realizar a configuração no arquivo postgresql.conf e 
no pg_hba.conf, nos ambientes Windows e Linux.
Administração do SGBD PostgreSQL
21
Componentes do PostgreSQL
INTRODUÇÃO:
Agora que você já sabe realizar conexão do PostgreSQL 
a aplicações, vamos para o próximo passo. Ao término 
deste capítulo você conhecerá os principais componentes 
do PostgreSQL, além de aprender a escolher quais 
componentes serão instalados. Você sabia que esse 
tipo de trabalho é extremamente importante para uma 
empresa? Sabendo a funcionalidadedos componentes e 
sua utilidade junto à empresa, é o que garante o sucesso 
de uma boa administração do sistema de gerenciamento 
de banco de dados. Isso é uma grande responsabilidade, 
não é mesmo? Então, vamos lá! .
Componentes de Acesso a Dados do 
PostgreSQL
DEFINIÇÃO:
O componente de acesso a dados PostgreSQL, do inglês 
PostgreSQL Data Access Components (PgDAC) é uma 
biblioteca de componentes que fornece conectividade 
nativa para PostgreSQL de Delphi e C++ Builder, incluindo 
Community Edition, bem como Lazarus (e Free Pascal) no 
Windows, Linux, macOS, iOS e Android para plataformas 
de 32 e de 64 bits. O PgDAC foi projetado para ajudar os 
programadores a desenvolverem aplicativos de banco de 
dados PostgreSQL realmente leves, mais rápidos e mais 
limpos, sem implantar quaisquer bibliotecas adicionais. 
Ele fornece acesso direto ao PostgreSQL sem o cliente 
PostgreSQL.
O PostgreSQL Data Access Components vem em duas edições: 
Standard, que inclui os componentes básicos e conectividade do 
PgDAC, e Profissional, que oferece suporte para recursos avançados de 
Administração do SGBD PostgreSQL
22
gerenciamento de conjunto de dados. Sendo assim, temos os seguintes 
componentes de acesso a dados:
 • TPgConnection – é usado para manter a conexão com um banco de 
dados PostgreSQL. Depois da definição das propriedades de nome de 
usuário, senha, servidor e base de dados, é possível estabelecer uma 
conexão com o banco de dados chamando o método Connect ou 
definindo a propriedade Connect como true. Existem também muitas 
propriedades no nível da conexão que afetam o comportamento 
padrão das consultas executadas nesta conexão.
 • TPgQuery – é utilizado para realizar busca, inserção, exclusão e 
atualização de registro por instruções SQL geradas dinamicamente. 
Para modificar registros, você pode especificar KeyFields. Se eles 
não forem especificados, TPgQuery irá recuperar as chaves primárias 
para UpdatingTable dos metadados. O TPgQuery pode atualizar 
automaticamente apenas uma tabela. A atualização da tabela é 
definida pela propriedade UpdatingTable se essa propriedade estiver 
configurada. Caso contrário, a tabela cujo campo é o primeiro na 
lista de campos na cláusula SELECT, é usada como uma tabela de 
atualização.
 • TPgSQL – é utilizado quando um aplicativo cliente precisa executar 
a instrução SQL ou o bloco PL/SQL e chamar o procedimento 
armazenado no servidor de banco de dados. A instrução SQL não 
deve recuperar linhas do banco de dados.
 • TPgTable – é utilizado para recuperar e atualizar dados em uma única 
tabela sem escrever instruções SQL.
 • TPgStoredProc – é útil para acessar procedimentos armazenados 
no servidor de banco de dados. Para isso, é necessário definir a 
propriedade StoredProcName, e a instrução SQL para chamar o 
procedimento armazenado será gerada automaticamente.
 • TPgUpdateSQL – permite ajustar as operações de atualização para o 
componente DataSet.
Componentes do Banco de Dados do 
Administração do SGBD PostgreSQL
23
PostgreSQL
DEFINIÇÃO:
De acordo com Juba e Volkov (2019, p. 75), os componentes 
de um banco de dados do PostgreSQL podem ser 
considerados um contêiner para schemas de banco de 
dados que são usados para organizar tabelas (como um 
objeto central do banco de dados). 
Dessa forma, o banco de dados deve conter pelo menos um 
schema de banco de dados que é usado para organizar os objetos de 
maneira semelhante a namespaces em linguagens de programação de 
alto nível. Vejamos melhor os schemas e as tabelas:
Schemas
Na Unidade anterior, aprendemos que um schema (esquema) é 
um namespace que organiza o banco de dados em grupos lógicos que 
pertence a um usuário do banco de dados. Agora vamos conhecer os 
objetos que são nomeados pelo banco de dados e que estão contidos 
em um schema. 
Os nomes dos objetos podem ser reutilizados em diferentes schemas 
sem conflito. O esquema contém todos os objetos nomeados pelo banco 
de dados, incluindo tabelas, visualizações, funções, agregações, índices, 
sequências, gatilhos, tipos de dados, domínios e intervalos. Vejamos a 
estrutura na Figura 11.
Administração do SGBD PostgreSQL
24
Figura 11 : Objetos do schema
Fonte: Elaborado pela autora.
Por padrão, existe um esquema chamado public no modelo do 
banco de dados. Isso significa que todos os bancos de dados recém-
criados também contêm esse esquema, ou seja, todos os usuários, 
por padrão, podem acessá-lo implicitamente. Permitir esse padrão de 
acesso simula uma situação em que o servidor não reconhece o schema, 
tornando-se útil em pequenas empresas em que não há a necessidade 
de uma segurança complexa. Além disso, permite uma transição suave 
de bancos de dados sem reconhecimento de schema.
Além disso, segundo Juba e Volkov (2019, p. 75), quando um usuário 
deseja acessar determinado objeto, é necessário especificar o nome 
do esquema e o nome do objeto separados por um ponto (.). Caso a 
configuração search_path do banco de dados não contenha esse nome, 
ou se o desenvolvedor prefere usar nomes completos (por exemplo, para 
Administração do SGBD PostgreSQL
25
selecionar todas as entradas em pg_database no schema pg_catalog), é 
necessário escrever o seguinte comando:
SELECT * FROM pg_catalog.pg_database;
Como alternativa, você também pode usar este comando:
TABLE pg_catalog.pg_database;
Muito desenvolvedores deixam de escrever nomes de objetos 
de banco de dados qualificados por serem tediosos de escrever, então, 
preferem usar o nome de objeto não qualificado, composto apenas pelo 
nome do objeto, sem o schema. Para isso, o PostgreSQL fornece uma 
configuração search_path semelhante à diretiva using na linguagem C++.
O caminho de pesquisa é composto de schemas que são usados 
pelo servidor para pesquisar o objeto. O caminho de pesquisa padrão, 
conforme mostrado no código a seguir, é $user,public. 
SHOW search_path; 
 -------------- 
 $user,public
IMPORTANTE:
Caso tenha um schema com o mesmo nome do usuário, ele 
será usado primeiro para pesquisar objetos ou criar novos 
objetos, e se o objeto não for encontrado nos schemas 
especificados, será gerado um erro no search_path.
Os esquemas são usados pelos seguintes motivos: 
 • Autorizar o controle: em um ambiente de banco de dados 
multiusuário, pode-se usar esquemas para agrupar objetos com base 
em funções.
 • Organizar objetos de banco de dados: pode-se organizar os objetos 
de banco de dados em grupos com base na lógica de negócios. Por 
exemplo, dados históricos e de auditoria podem ser agrupados e 
organizados logicamente em um esquema específico.
 • Manter código SQL de terceiros: as extensões disponíveis no pacote 
de contribuição podem ser usadas com vários aplicativos. Manter 
Administração do SGBD PostgreSQL
26
essas extensões em esquemas separados permite ao desenvolvedor 
reutilizá-las e atualizá-las facilmente.
Tabelas
A instrução CREATE TABLE SQL pode ser usada para diversos fins, 
como clonar uma tabela, o que é útil para refatoração de banco de dados 
para criar o script de desinstalação para reverter as alterações. Além disso, 
pode ser usado para materializar o resultado da instrução SELECT SQL 
para aumentar o desempenho ou temporariamente armazenar os dados 
para uso posterior.
Juba e Volkov (2019) afirmam que, no PostgreSQL, as tabelas podem 
ser de diferentes tipos:
 • Tabela permanente: seu ciclo de vida começa com a criação e 
termina com a eliminação da tabela.
 • Tabela temporária: seu ciclo de vida é a sessão do usuário. Isso é 
frequentemente usado com linguagens procedurais para modelar 
alguma lógica de negócios.
 • Tabela não registrada: as operações em tabelas não registradas são 
muito mais rápidas do que em tabelas permanentes, porque os dados 
não são gravados nos arquivos WAL (Write-Ahead Log). Tabelas não 
registradas não são seguras contra travamentos. Além disso, como a 
replicação de streaming é baseada no envio de arquivosde log, as 
tabelas não registradas não podem ser replicadas para o nó escravo.
 • Tabela filha: é uma tabela que herda uma ou mais tabelas. A 
herança é frequentemente usada com exclusão de restrição para 
particionar fisicamente os dados no disco rígido e obter desempenho 
na recuperação de um subconjunto de dados que contém um 
determinado valor.
Administração do SGBD PostgreSQL
27
Componentes do Sistema de Arquivos do 
PostgreSQL
Você sabia que mesmo tendo uma estrutura completa de hardware 
e software dedicados, os servidores ainda não trabalharão corretamente 
sem usar os componentes especificados pelo administrador? De acordo 
com Thomas (2020, p. 477), os servidores ainda não têm três importantes 
funções:
 • Capacidade de sincronizar dados para dois servidores 
simultaneamente.
 • Capacidade de congelar dados para evitar alterações para fins de 
backup.
 • Um sistema de arquivos durável e projetado para multiprocessamento 
de Entrada/Saída.
Há no mercado várias abordagens para cada um desses elementos 
não suportados automaticamente pelo servidor. Contudo, seguindo as 
diretrizes de Thomas (2020, p. 477), vamos apresentar três abordagens: 
DRBD, LVM e XFS.
DRBD
DEFINIÇÃO:
Do inglês Distributed Replicated Block Device, o DRBD 
é um dispositivo de bloco de replicação distribuída que 
opera abaixo da camada do sistema de arquivos e espelha 
todo o conteúdo armazenado no servidor para outro nível 
de bloco, ou seja, o sistema operacional não enxerga que 
os dados estão localizados em outro servidor. Para uma 
melhor compreensão, vejamos a Figura 12.
Administração do SGBD PostgreSQL
28
Figura 12 : Estrutura do DRBD
Fonte: Thomas, 2020, p. 477 (Adaptado).
O DRBD atua como uma abstração do dispositivo de disco que 
normalmente hospeda o banco de dados PostgreSQL. A vantagem 
principal é que os dados estão sempre localizados em pelo menos dois 
servidores em tempo integral. Se um servidor falhar e seu armazenamento 
tornar-se inutilizável, haverá um backup disponível.
LVM
DEFINIÇÃO:
Do inglês Logical Volume Manager, o LVM é uma camada 
de abstração que fica entre o sistema de arquivos e 
os dispositivos de disco subjacentes, possibilitando o 
gerenciamento de forma dinâmica dos dispositivos de 
disco como uma única peça contínua de armazenamento 
e, assim, permitindo estender, agrupar, congelar ou 
reorganizar arbitrariamente de forma online.
Com o LVM, é possível mover dados de um dispositivo para outro 
após uma atualização, ou seja, permite adicionar armazenamento a um 
sistema de arquivos sem links simbólicos. 
Dessa forma, usando um servidor moderno com discos hot-
swappable ou uma rede de área de armazenamento (SAN), exclui a 
necessidade de reinicializar o servidor para reconfigurar completamente 
seus dispositivos de disco. Ainda assim, diante de todo o processo de 
Administração do SGBD PostgreSQL
29
quase qualquer alteração do LVM, o PostgreSQL pode permanecer online 
e solicitar serviços.
XFS
DEFINIÇÃO:
Do inglês Extents File System, o XFS tem a estrutura 
do sistema de arquivos do tipo journaling e fornece 
crescimento online, congelamento de LVM e vários tipos 
de manutenção e de ferramentas de reparo.
Apesar de o XFS e o EXT4 serem sistemas de arquivos com 
journaling, o XFS tem a vantagem de realizar grupos de alocação. Por um 
lado, o EXT4 contém apenas uma única tabela de alocação do arquivo 
para todo o dispositivo formatado; já o XFS permite dividir a tabela de 
alocação em vários segmentos.
Com a possibilidade da divisão da tabela de alocação no XFS, 
então, vários processos de CPU independentes podem gravar no 
disco simultaneamente e, consequentemente, possibilita que grandes 
servidores com múltiplas CPUs e gravações aleatórias, como um banco 
de dados PostgreSQL, tenham um melhor desempenho.
SAIBA MAIS:
O criador do DRBD disponibilizou uma documentação 
bastante detalhada sobre soluções de problemas do 
sistema: “The DRBD User’s Guide”, acessível pelo link: 
https://bit.ly/2H3VRun
Administração do SGBD PostgreSQL
https://bit.ly/2H3VRun
30
RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que 
mostramos? Aprendeu mesmo tudinho? Agora, só para 
ter certeza de que você realmente entendeu o tema de 
estudo deste capítulo, vamos resumir tudo. Você deve 
ter aprendido que há várias abordagens quando se trata 
de componentes do PostgreSQL. Apresentamos os 
componentes de acesso a dados do PostgreSQL, que é 
uma biblioteca de componentes que fornece conectividade 
nativa para PostgreSQL de Delphi e C++ Builder incluindo 
Community Edition, bem como Lazarus (e Free Pascal) no 
Windows, Linux, macOS, iOS e Android, para plataformas 
de 32 e de 64 bits. Também vimos com mais detalhes dois 
principais componentes do banco de dados: schemas e 
tabelas. Além disso, aprendemos três abordagens: DRBD, 
LVM e XFS, que melhoram o desempenho do servidor.
Administração do SGBD PostgreSQL
31
Buffering e Sincronização
INTRODUÇÃO:
Agora que você já sabe quais são os componentes 
do PostgreSQL (componentes de acesso a dados, 
componentes do banco de dados e os componentes do 
sistema de arquivos), vamos para o próximo passo. Ao 
término deste capítulo você irá compreender o processo 
de buffering e sincronização do PostgreSQL. Além disso, 
aprenderá a implementar o controle transacional dos dados 
com comandos como commit, checkpoint etc. Você sabia 
que a configuração padrão do PostgreSQL não é ajustada 
para nenhuma carga de trabalho específica? Ajustar o 
PostgreSQL de acordo com a carga de trabalho do sistema 
é uma das principais responsabilidades do administrador 
ou desenvolvedor do banco de dados, pois é com isso que 
garantimos o melhor desempenho para o servidor. É uma 
grande responsabilidade, não é mesmo? Então, vamos lá 
PostgreSQL Buffer
O PostgreSQL usa seu próprio buffer e o processo de Entrada/Saída 
com buffer de kernel, isto é, os dados são armazenados na memória duas 
vezes, primeiro no buffer do PostgreSQL e depois no buffer do kernel. 
Ao contrário de outros bancos de dados, o PostgreSQL não fornece 
o processo de Entrada/Saída direto. Isso é chamado de buffer duplo. Logo, 
o buffer PostgreSQL é chamado de shared_buffer, que é o parâmetro 
ajustável mais eficaz para a maioria dos sistemas operacionais, ou seja, é 
a quantidade de memória que o servidor de banco de dados PostgreSQL 
usa para cache com o propósito de acesso rápido aos dados.
Na maioria das máquinas modernas, é necessário aumentar o valor 
padrão do buffer para obter um desempenho ideal; o valor padrão para 
buffers compartilhados é 128 MB, porém, é recomendável defini-lo para 
cerca de 25% da memória total.
Administração do SGBD PostgreSQL
32
Às vezes, aumentar o shared_buffer para um valor muito alto pode 
elevar o desempenho, porque o banco de dados pode ser completamente 
armazenado em cache na RAM. Contudo, a desvantagem de aumentar 
demais esse valor é que ele pode ficar impossibilitado de alocar memória 
para operações de CPU, como classificação e hash.
A configuração realmente depende do hardware e do conjunto de 
dados que são manipulados. Caso esse conjunto de dados de trabalho 
caiba facilmente na memória RAM, é possível que o valor de shared_
buffer seja elevado, para conter seu banco de dados inteiro, de forma que 
todo o conjunto de dados de trabalho resida no cache.
Em ambientes de produção, é observado que um grande valor para 
shared_buffer dá um desempenho realmente bom, embora você deva 
sempre fazer benchmark para encontrar o equilíbrio certo.
Ainda se lembra de como abrir o psql? Vamos precisar dele. Caso 
não se lembre: no Windows, basta pesquisar por SQL Shell e abrir o 
terminal; no Linux, você pode digitar sudo sudo su - postgres e depois 
psql no terminal.
Com isso, podemos verificar o valor alocado para o shared_buffer 
no sistema (vejamos melhor na Figura 13) a partir do seguinte comando:
SHOW shared_buffers;
Figura 13 : Tamanho alocado para o shared_bufferFonte: Elaborado pela autora.
Administração do SGBD PostgreSQL
33
Você também se perguntou como obter uma estimativa da memória 
disponível para cache de disco? Para isso existe uma orientação, não a 
memória alocada exata ou o tamanho do cache. 
O effective_cache_size fornece a quantidade de memória 
disponível para cache de disco, mas ele apresenta apenas uma estimativa, 
e não a memória alocada exata ou o tamanho do cache. Ainda, não é 
alocada a memória real, mas informa ao otimizador a quantidade de 
cache disponível no kernel. Se o valor dele for definido como muito baixo, 
o planejador de consulta pode decidir não usar alguns índices, mesmo 
que eles sejam úteis.
A configuração work_mem é usada para a classificação complexa, 
e, para esses casos, sugere-se aumentar o valor dela para obter bons 
resultados, além de suas classificações serem mais rápidas em relação às 
transmitidas para o disco. 
Definir um valor muito alto de work_mem pode causar um gargalo 
de memória para o ambiente de implementação, porque esse parâmetro 
é por operação de classificação do usuário. Portanto, se houver muitos 
usuários tentando executar operações de classificação, o sistema alocará 
work_mem * total sort operations para todos os usuários. Ainda assim, 
definir esse parâmetro de forma global pode causar uso de memória muito 
alto. Então, é altamente recomendável modificá-lo no nível da sessão.
Exemplo: Vejamos um exemplo de como aumentar o valor do 
work_mem comparando o retorno da consulta query:
Administração do SGBD PostgreSQL
34
O nó de classificação da consulta inicial tem um custo estimado 
de 514431,86. Custo é uma unidade de cálculo arbitrária. Para a consulta 
acima, temos um work_mem de apenas 2 MB. Para fins de teste, vamos 
aumentar para 256 MB e ver se há algum impacto no custo. Vejamos o 
comando:
Administração do SGBD PostgreSQL
35
Dessa forma, diante dos exemplos, podemos observar que o custo 
da consulta foi reduzido de 514431,86 para 360617,36 - uma redução 
média de 30%.
O maintenance_work_mem é uma configuração de memória 
usada para tarefas de manutenção. O valor padrão é 64 MB. Definir um 
valor alto ajuda em tarefas como: VACUUM, RESTORE, CREATE INDEX, 
ADD FOREIGN KEY e ALTER TABLE. Uma vez que apenas uma dessas 
operações pode ser executada por vez por uma sessão de banco de dados, 
e uma instalação normalmente não tem muitas delas sendo executadas 
simultaneamente, é seguro definir esse valor significativamente maior do 
que work_mem. Configurações maiores podem melhorar o desempenho 
para limpar e restaurar dumps do banco de dados.
NOTA:
Perceba que, quando autovacuum é executado, até 
autovacuum_max_workers pode ser alocada, então, tome 
cuidado para não definir o valor padrão muito alto.
Exemplo: Vejamos um exemplo do retorno aumentando o valor de 
maintenance_work_mem de 10 MB pra 256 MB, observando o tempo de 
resposta:
Administração do SGBD PostgreSQL
36
O que você concluiu mediante os dois retornos? Perceba que, 
quando definimos 10 MB, o tempo de criação do índice é 170091,371 ms, 
e quando aumentamos a configuração para 256 MB, é reduzido para 
111274,903 ms.
Sincronização
IMPORTANTE:
Às vezes, para fornecer a durabilidade aceitável de dados, 
uma configuração de alta disponibilidade deve utilizar 
confirmações síncronas. A partir do PostgreSQL 9.1, os 
servidores de banco de dados agora podem se recusar 
a confirmar uma transação até que os dados estejam 
localizados em pelo menos um servidor alternativo. Ao 
contrário da replicação assíncrona, em que isso é opcional, 
as réplicas síncronas impõem esse requisito a uma falha.
Nesta seção teremos como exemplo a forma de alterar a 
configuração de espera para incluir a replicação de streaming síncrono, 
isto é, vamos mostrar como configurar um servidor assíncrono em espera 
para o modo síncrono.
Inicialmente, devemos modificar a configuração primary_conninfo 
no arquivo postgresql.conf ou recovery.conf do standby para incluir o valor 
application_name. O PostgreSQL diferencia as réplicas por seu nome de 
aplicativo declarado, portanto, se mudarmos isso, poderemos ter como 
alvo especificamente aquela réplica em particular. Além disso, quaisquer 
outros nós de espera síncronos devem receber nomes diferentes.
Administração do SGBD PostgreSQL
37
Neste caso, quando reiniciamos o servidor PostgreSQL no modo de 
espera de streaming, ele se reconecta ao servidor primário com o novo 
valor de application_name que atribuímos.
A partir disso, podemos nos referir ao servidor em espera como 
servha2 e, portanto, quando alteramos as variáveis synchronous_
standby_names e synchronous_commit no arquivo postgresql.conf do 
servidor primário, devemos usar o mesmo nome no local.
Sempre que desejamos alterar a variável synchronous_standby_
names, precisamos apenas dizer ao PostgreSQL para recarregar seus 
arquivos de configuração. Isso irá fazer com que o nó primário considere 
o servha2 um servidor em espera síncrono, e qualquer transação só será 
confirmada se puder gravar neste servidor, assim como no primário.
IMPORTANTE:
Essa é uma compensação entre desempenho e 
confiabilidade. Se seu aplicativo for projetado de forma que o 
desempenho seja mais importante do que a confiabilidade, 
desative o synchronous_commit. Isso significa que haverá 
um intervalo de tempo entre o status de sucesso e uma 
gravação garantida no disco. No caso de falha do servidor, 
os dados podem ser perdidos mesmo que o cliente receba 
uma mensagem de sucesso na confirmação.
Depois de recarregar os arquivos de configuração do servidor 
primário, podemos verificar a exibição pg_stat_replication para observar 
como o streaming está funcionando e, depois de executar a consulta, 
veremos algo assim:
Administração do SGBD PostgreSQL
38
Pronto! Como podemos ver neste exemplo, o servidor primário vê o 
servha2 como uma réplica de streaming síncrono.
Agora, para confirmar se a replicação de streaming funcionou 
corretamente, é preciso desligar o servidor em espera. Para isso, usamos 
o seguinte comando:
sudo systemctl stop postgresql-12
Em seguida, para testar, tente gravar uma instrução qualquer 
no servidor principal. Esta simples instrução SQL deve esperar 
indefinidamente:
CREATE TABLE teste (bar INT);
Se reiniciarmos a réplica de streaming usando o seguinte comando, 
veremos a transação concluída:
sudo systemctl start postgresql-12
E então? O que você concluiu desse teste que realizamos? Perceba 
que esse procedimento pode ser problemático, principalmente em 
servidores que abrangem a maior parte dos clusters altamente disponíveis. 
Se quisermos tentar o experimento novamente, podemos primeiro 
emitir esta instrução SQL antes de tentar uma consulta de gravação básica:
SET synchronous_commit TO false;
Esse procedimento desativa temporariamente a replicação síncrona 
para a sessão atual. As consultas de gravação subsequentes nessa 
conexão devem ser bem-sucedidas normalmente, como se o servidor 
remoto fosse uma cópia assíncrona padrão.
Outra abordagem são os checkpoints, que são parte integrante 
de um servidor PostgreSQL. Os dados da tabela não são modificados 
durante a execução da consulta até que as linhas modificadas, páginas de 
índice e outras estruturas sejam confirmadas no Log Write-Ahead (WAL). 
Arquivos WAL também são conhecidos como segmentos de checkpoint. 
Quando o tamanho cumulativo desses arquivos excede max_wal_size, ou 
o tempo desde o último ponto de verificação excede checkpoint_timeout, 
os arquivos de dados são modificados para refletir as mudanças.
Administração do SGBD PostgreSQL
39
Contudo, essa operação envolve grandes quantidades de entrada/
saída e várias operações de leitura/escrita em disco. Para realizar tal 
procedimento, os usuários podem emitir CHECKPOINT sempre que 
houver necessidade de automatizar o sistema a partir dos parâmetros 
checkpoint_timeout e checkpoint_completion_target do PostgreSQL.O parâmetro checkpoint_timeout é usado para definir o tempo 
entre os pontos de verificação WAL. Definir um valor muito baixo diminui o 
tempo de recuperação de falha, pois mais dados são gravados no disco, 
mas também prejudica o desempenho, pois cada ponto de verificação 
acaba consumindo recursos valiosos do sistema. 
O checkpoint_completion_target é a fração de tempo entre os 
pontos de verificação para a conclusão do ponto de verificação. Uma alta 
frequência de pontos de verificação pode afetar o desempenho. Para um 
ponto de verificação suave, checkpoint_timeout deve ser um valor baixo. 
Caso contrário, o sistema operacional acumulará todas as páginas sujas até 
que a proporção seja atingida e, em seguida, fará uma grande descarga.
SAIBA MAIS:
Assunto maravilhoso, não é mesmo? Ficou curioso para 
conhecer mais? A documentação do PostgreSQL oferece 
ótimos recursos pelo link: https://bit.ly/31b6x1g
RESUMINDO:
Chegamos ao fim do nosso capítulo. 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 os conceitos essenciais de buffering e de 
sincronização em um servidor PostgreSQL, bem como a 
importância e os exemplos. Apresentamos a você como 
configurar o shared_buffer e como implementar o controle 
transacional dos dados com comandos como commit, 
checkpoint etc. Não esquecendo que também aprendemos 
a executar e desativar a replicação síncrona.
Administração do SGBD PostgreSQL
https://bit.ly/31b6x1g
40
Procedimento de Fallback
INTRODUÇÃO:
Agora que você já sabe tudo sobre buffering e 
sincronização, vamos para o próximo passo. Ao término 
deste capítulo você conhecerá os principais componentes 
do PostgreSQL. Além disso, irá compreender o processo de 
log de transações, aplicando comandos de recuperação de 
dados por meio de fallbacks. Você sabia que o PostgreSQL 
tem mais de duzentas configurações que controlam o 
comportamento do banco de dados? Está tudo registrado 
em pg_settings. Ainda assim, é extremamente importante 
para o profissional administrador do banco de dados 
compreender o processo de log de transações, pois é 
ele quem garante a durabilidade dos dados, isto é, um 
menor risco de perder os dados. Você também já sabe 
da importância dos dados, não é mesmo? Então, vamos 
conhecer mais sobre esse assunto fantástico!.
Você já ouviu falar em log de transações? Sabe para que serve? 
Todos os Sistemas de Gerenciamento de Banco de Dados Relacional 
usam logs de transação que mudam de codinome conforme o SGBD. No 
PostgreSQL, chamamos de WAL (Write-Ahead Log). 
Os logs de transação possuem um trade-off entre vantagem e 
desvantagem, cuja problemática baseia-se no gargalo de desempenho 
do Sistema de Gerenciamento de Banco de Dados.
Os logs de transação trabalham de forma incremental, mas não 
indefinidamente, pois, quando atinge seu tamanho limite, é aberto um 
novo arquivo para o registro dos logs.
Um fallback é uma operação de contingência, isto é, caso ocorra 
algum problema com os dados, alguns procedimentos são usados 
automaticamente para assegurar a manutenção adequada do servidor, 
garantindo sua integridade e oferecendo suporte a algumas operações, 
tais como: recuperação de transações individuais, recuperação de 
Administração do SGBD PostgreSQL
41
transações incompletas, suporte à replicação transacional, suporte à 
recuperação de desastre etc. Sendo assim, vejamos alguns comandos 
para a recuperação de dados nas subseções seguintes.
Point in Time Recovery (PITR) no 
PostgreSQL
DEFINIÇÃO:
O Point in Time Recovery permite, além da recuperação 
dos dados em situações de falhas, voltar a um determinado 
ponto no tempo, além de possibilitar a remoção de uma 
tabela.
A documentação do grupo PostgreSQL afirma que, em todos 
os momentos, o PostgreSQL mantém o WAL - Registro de Gravação 
Antecipada (do inglês Write-Ahead Log) no subdiretório pg_xlog do 
diretório de dados do cluster, cujo log registra todas as alterações 
realizadas nos arquivos de dados do banco de dados. 
Dessa forma, se a recuperação for necessária, será possível restaurar 
o backup do sistema de arquivos e, em seguida, reproduzir os arquivos 
WAL de backup para trazer o sistema a um estado atual e, ainda, poderá 
dar suporte à restauração de um cluster de banco de dados inteiro, não 
só de um subconjunto.
Administração do SGBD PostgreSQL
42
Passo a passo: Os arquivos WAL, por padrão, ocupam 16 MB. Sendo 
assim, vejamos um passo a passo de uma transferência para um diretório.
1. Inicialmente, vamos montar um cluster por meio do initdb em um 
diretório vazio. Vejamos o comando:
2. Agora, no arquivo postgresql.conf, devemos alterar a variável archive_
mode para on. Vejamos:
archive mode = on
3. Posteriormente, devemos configurar a variável chamada archive_
command que apontará para onde os arquivos WAL serão 
direcionados. Observe que o símbolo %p representa o arquivo de 
origem sem a exigência do caminho específico; já %f é o arquivo de 
origem precedido. 
archive_command = ‘rsync %p diretorio_destino/%f’
NOTA:
De regra geral, para ambientes em modo produção, 
aconselha-se o armazenamento dos arquivos de log em 
hardware separado de onde os arquivos são gerados.
4. Nesse momento, vamos alterar a estrutura do arquivo WAL:
wal_level = archive
5. Então, para garantir as aplicações, reiniciaremos o servidor:
Administração do SGBD PostgreSQL
43
postgres =# /usr/lib/postgresql/12/bin/pg_ctl -D teste -l teste/teste.
log restart
6. Para os testes, iremos gerar uma tabela com números sequenciais:
postgres =# psql
teste=# CREATE TABLE tbl_teste AS SELECT * FROM generate_series 
(1, 1000000);
7. Nosso próximo importante passo é a ativação do PITR. Para isso, 
é necessário habilitar no arquivo que já conhecemos: pg_hba.
conf. Então, vamos habilitar para aceitar as conexões iniciadas pelo 
endereço IP 192.168.0.N, em que N representa as demais combinações 
de endereço, e também para o localhost.
8. Finalizada a configuração, agora carregamos as modificações no psql.
postgres =# psql
teste=# SELECT pg_reload_conf ();
9. O próximo passo será a execução do backup. Note na linha de 
comando que <dir_backup> aponta onde os dados serão salvos. O 
diretório escolhido para esse exemplo foi backup. Veja também que o 
<host> significa o endereço a ser estabelecido pelo pg_basebackup.
postgres =# /usr/lib/postgresql/12/bin/pg_basebackup -D 
<diretorio_backup> -h <host>
Administração do SGBD PostgreSQL
44
10. Agora, o comando será para o processo de recuperação do backup. 
No PostgreSQL tal procedimento é realizado no arquivo recovery.conf 
em que, na inicialização, o modo de espera inicia restaurando todos os 
WAL disponíveis no local do arquivo, chamando restore_command, e 
recovery_target_time para indicar a data e a hora dos dados a serem 
recuperados.
IMPORTANTE:
A localização do recovery.conf deve ser no diretório principal 
do backup. Como já falamos, nosso diretório escolhido foi 
nomeado backup.
EXPLICANDO MELHOR:
Uma vez que atinge o final do WAL disponível e o 
restore_command falha, ele tenta restaurar qualquer WAL 
disponível no diretório pg_xlog. Se isso falhar, e a replicação 
de streaming tiver sido configurada, o standby tenta se 
conectar ao servidor primário e iniciar o streaming do WAL 
a partir do último registro válido encontrado no arquivo ou 
pg_xlog. Se isso falhar ou a replicação de streaming não 
estiver configurada, ou se a conexão for desconectada 
posteriormente, o modo de espera volta para a etapa inicial 
e tenta restaurar o arquivo do archive novamente. Este 
loop de novas tentativas do arquivo pg_xlog e por meio 
da replicação de streaming continua até que o servidor 
seja interrompido ou que o failover seja disparado por um 
arquivo acionador.
Administração do SGBD PostgreSQL
45
11. É conveniente realizar a limpeza dos logs de transação após aconsumação pelo backup por meio do parâmetro chamado archive_
cleanup_command.
12. Devemos notar que é necessário alterar as permissões de leitura, de 
escrita e de execução para o diretório que contém o backup. Senão, 
o PostgreSQL emitirá um erro.
13. Apesar de não haver necessidade em caso real, como estamos 
realizando um teste na mesma máquina, é preciso que a porta padrão 
(5432) do postgres seja diferente para não ocasionar conflito. Sendo 
assim, vamos configurar para 5433 no arquivo postgresql.conf.
14. Pronto! Agora será o nosso comando final para realizar o procedimento 
de recuperação a partir de todos os arquivos WAL, armazenando em 
backup/backup.log.
Administração do SGBD PostgreSQL
46
Replicação Física dos Dados do PostgreSQL
DEFINIÇÃO:
O processo de transferência de entradas de log de 
transações e a aplicação delas em outro servidor é 
chamado de replicação física. É chamado de físico porque 
a recuperação do log de transações funciona no nível 
dos arquivos de dados, e a réplica do banco de dados 
no servidor em espera será uma cópia exata de bytes do 
banco de dados mestre.
Também conhecido como backup off line, a replicação física torna-
se essencial tratando-se de recuperação de desastres. Contudo, exige 
bastante atenção, pois é uma recuperação demorada.
As entradas do log de transações podem ser obtidas de um servidor 
de banco de dados, o servidor mestre, e aplicadas aos arquivos de dados 
em outro servidor, o servidor em espera. Nesse caso, o servidor em espera 
terá a réplica exata do banco de dados do servidor mestre.
A replicação física funciona para todo o cluster de banco de 
dados, e quando um novo banco de dados é criado, é refletido no log de 
transações e, portanto, replicado para o servidor em espera. O servidor 
em espera pode ser configurado para permitir consultas somente leitura; 
nesse caso, seria denominado hot standby.
Passo a passo: Uma das maneiras de configurar a replicação física 
é enviar constantemente novos arquivos WAL do servidor mestre para o 
standby e aplicá-los lá para obter a cópia sincronizada do banco de dados. 
Esse cenário é denominado envio de log. Sendo assim, para configurar 
o envio de log, algumas ações devem ser tomadas. Vejamos o passo a 
passo:
No servidor mestre, faça o seguinte:
1. Certifique-se de que os arquivos WAL tenham informações suficientes 
para replicação. No arquivo postgresql.conf, é necessário definir o 
parâmetro de configuração wal_level do tipo replica ou lógico.
Administração do SGBD PostgreSQL
47
2. Habilite o arquivamento dos arquivos WAL configurando o parâmetro 
archive_mode de configuração ativado.
3. Realize o arquivamento dos arquivos WAL em um local seguro 
especificado usando o parâmetro de configuração archive_command. 
O PostgreSQL aplicará esse comando do sistema operacional a cada 
arquivo WAL que precisa ser arquivado. Esse comando pode, por 
exemplo, compactar o arquivo e copiá-lo para uma unidade de rede. 
Se o comando estiver vazio, mas o arquivamento de arquivos WAL 
estiver habilitado, esses arquivos serão acumulados na pasta pg_wal.
No servidor em espera, faça o seguinte:
1. Restaure o backup básico obtido do servidor mestre. A maneira mais 
fácil de fazer isso é usar a ferramenta pg_basebackup. O comando 
executado na máquina em espera pode ter a seguinte aparência:
postgres =# pg_pasebackup -D / var / lib / postgresql / 12 / main -h 
master -U postgres
Há outro cenário de replicação física que pode funcionar além do 
envio de log ou sem envio de log: replicação de streaming. A replicação de 
streaming implica uma conexão entre os servidores em espera e mestre. 
O mestre enviaria todas as entradas do log de transações diretamente 
para o modo de espera. Ao fazer isso, o modo de espera terá todas as 
alterações recentes sem esperar que os arquivos WAL sejam arquivados.
SAIBA MAIS:
Quer saber mais sobre o assunto? Deseja mais detalhes 
sobre o comando? Na documentação do PostgreSQL, 
é possível acessar o comando em detalhes: https://bit.
ly/3lUA9bh
Administração do SGBD PostgreSQL
https://bit.ly/3lUA9bh
https://bit.ly/3lUA9bh
48
RESUMINDO:
Chegamos ao fim do nosso capítulo. 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. 
Nesse material, você deve ter aprendido a realizar 
os procedimentos de fallbacks aplicando comandos 
de recuperação de dados em log de transações. Viu 
também um exemplo prático de como realizar um 
PITR e uma replicação física dos dados no PostgreSQL. 
Agora já sabe que é possível restaurar o backup do sistema 
de arquivos e, em seguida, reproduzir os arquivos WAL de 
backup para trazer o sistema a um estado atual e, ainda, 
poder dar suporte à restauração de um cluster de banco de 
dados inteiro, não só de um subconjunto.
Administração do SGBD PostgreSQL
49
REFERÊNCIAS
JUBA, Salahaldin; VOLKOV, Andrey. Learning PostgreSQL 11: a beginner’s 
guide to building high-performance PostgreSQL database solutions. Packt 
Publishing Ltd, 2019.
THOMAS, Shaun.  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.
BRASIL, André. Como alterar a senha de uma base de dados. Kinghost, 
2020. Disponível em: https://king.host/wiki/artigo/alterar-senha-de-
uma-base-de-dados/. Acesso em: 11 set. 2020.
LIMBIT. The DRBD User’s Guide. Disponível em: https://www.linbit.com/
drbd-user-guide/users-guide-drbd-8-4/. Acesso em: 02 set. 2020.
POSTGRESQL. Documentação. Disponível em: <https://www.postgresql.
org/docs/current/runtime-config-wal.html>. Acesso em: 08 set. 2020.
Administração do SGBD PostgreSQL
Administração do 
SGBD PostgreSQL
Camila Freitas Sarmento
	Conexão do PostgreSQL a Aplicações
	Conectando ao PostgreSQL Remotamente
	Conectando ao Banco de dados PostgreSQL no Windows
	Conectando ao Banco de Dados PostgreSQL no Linux
	Conectando ao Banco de Dados PostgreSQL via PgAdmin
	Componentes do PostgreSQL
	Componentes de Acesso a Dados do PostgreSQL
	Componentes do Banco de Dados do PostgreSQL
	Schemas
	Tabelas
	Componentes do Sistema de Arquivos do PostgreSQL
	DRBD
	LVM
	XFS
	Buffering e Sincronização
	PostgreSQL Buffer
	Sincronização
	Procedimento de Fallback
	Point in Time Recovery (PITR) no PostgreSQL
	Replicação Física dos Dados do PostgreSQL