Administração do SGBD PostgreSQL - Unidade 3 - Usando PostgreSQL em Aplicações

Prévia do material em texto

Administração do 
SGBD PostgreSQL
Unidade 3
Usando PostgreSQL em Aplicações
Diretor Executivo 
DAVID LIRA STEPHEN BARROS
Gerente Editorial 
CRISTIANE SILVEIRA CESAR DE OLIVEIRA
Projeto Gráfico 
TIAGO DA ROCHA
Autoria 
CAMILA FREITAS SARMENTO
AUTORIA
Camila Freitas Sarmento
Sou formada em Telemática com mestrado em Ciência da 
Computação e, atualmente, doutoranda na área de Engenharia de Software, 
com uma experiência técnico-profissional na área de Soluções Digitais 
(Redes e Programação Back-End) de mais de sete anos. Atualmente sou 
Analista de Informática – Programadora Web Back-End no Instituto Senai de 
Tecnologia em Automação Industrial (IST-Senai). Fui professora substituta 
na Universidade Estadual da Paraíba (UEPB) e atuei como professora 
substituta e, posteriormente, como tutora no Instituto Federal de Educação, 
Ciência e Tecnologia da Paraíba (IFPB). Sou apaixonada pelo que faço e 
adoro transmitir minha experiência àqueles que estão iniciando em suas 
profissões. Por isso fui convidada pela Editora Telesapiens a integrar seu 
elenco de autores independentes. Estou muito feliz em poder ajudar você 
nesta fase de muito estudo e trabalho. Conte comigo!
ICONOGRÁFICOS
Olá. Esses ícones irão aparecer em sua trilha de aprendizagem toda vez 
que:
OBJETIVO:
para o início do 
desenvolvimento de 
uma nova compe-
tência;
DEFINIÇÃO:
houver necessidade 
de se apresentar um 
novo conceito;
NOTA:
quando forem 
necessários obser-
vações ou comple-
mentações para o 
seu conhecimento;
IMPORTANTE:
as observações 
escritas tiveram que 
ser priorizadas para 
você;
EXPLICANDO 
MELHOR: 
algo precisa ser 
melhor explicado ou 
detalhado;
VOCÊ SABIA?
curiosidades e 
indagações lúdicas 
sobre o tema em 
estudo, se forem 
necessárias;
SAIBA MAIS: 
textos, referências 
bibliográficas e links 
para aprofundamen-
to do seu conheci-
mento;
REFLITA:
se houver a neces-
sidade de chamar a 
atenção sobre algo 
a ser refletido ou dis-
cutido sobre;
ACESSE: 
se for preciso aces-
sar um ou mais sites 
para fazer download, 
assistir vídeos, ler 
textos, ouvir podcast;
RESUMINDO:
quando for preciso 
se fazer um resumo 
acumulativo das últi-
mas abordagens;
ATIVIDADES: 
quando alguma 
atividade de au-
toaprendizagem for 
aplicada;
TESTANDO:
quando o desen-
volvimento de uma 
competência for 
concluído e questões 
forem explicadas;
SUMÁRIO
Conexão do PostgreSQL a aplicações ................................................ 10
Conectando ao PostgreSQL remotamente .................................................................. 10
Conectando ao banco de dados PostgreSQL no Windows ......... 10
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 .................................................................................................................................25
Componentes do sistema de arquivos do PostgreSQL ......................................26
DRBD .....................................................................................................................................26
LVM .........................................................................................................................................27
XFS ..........................................................................................................................................28
Buffering e sincronização .........................................................................29
PostgreSQL buffer ...........................................................................................................................29
Sincronização .....................................................................................................................................33
Procedimento de fallback........................................................................37
Point in Time Recovery (PITR) no PostgreSQL .......................................................... 38
Replicação física dos dados do PostgreSQL .................................................. 41
7
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 umas das maiores preocupações nas 
empresas é como manter a integridade dos dados? Isso mesmo. 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 possibilidade de melhorar o desempenho e tempo 
de resposta do sistema de gerenciamento do banco de dados. E você 
como profissional da área possui a responsabilidade de compreender 
e gerir da forma mais eficiente possível. Não é mesmo? 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 III. Nosso objetivo é auxiliar 
você no desenvolvimento das seguintes competências profissionais 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? Vamos conhecer mais desse assunto incrível e mais 
utilizado no mundo! Você não vai ficar de fora dessa, não é? Ao trabalho! 
Administração do SGBD PostgreSQL
10 Administração do SGBD PostgreSQL
Conexão do PostgreSQL a aplicações
OBJETIVO:
Ao término deste capítulo você será capaz de entender 
como funciona a conexão do PostgreSQL a aplicações. Isso 
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? Então vamos lá. Avante!
Conectando ao PostgreSQL remotamente
Nas etapas anteriores, você viu como conectar-se ao PostgreSQL 
com base na 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 do banco de 
dados. Em seguida, será solicitada a senha do usuário do banco de dados 
para que, ao digitar corretamente, o prompt do psql seja 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, Windows e Linux. Vamos 
nessa!
Conectando ao banco de dados PostgreSQL no 
Windows
Por padrão, ao instalar o PostgreSQL é criado o arquivo pg_hba.conf 
que é utilizado para autenticar os usuários. Sem a exigência de senha, 
contudo, configurado apenas para conexão local.
11Administração do SGBD PostgreSQL
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 (2021).
Vamos compreender os campos arquivo: 
 • Type – especifica o tipo da conexão que pode ser: Local, especifica 
que é um host local; Host, pode ser conexão criptografada ou não; 
Hostssl, que permite apenas conexão criptografada; e Hotnossl, 
permite conexão para apenas texto.
 • Database – é o banco de dados que será liberado para a conexão. 
Cujos 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; no nosso exemplo da figura 1, temos 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 rede na qual há 
a permissão para o acesso ao banco de dados, no exemplo, é a 
conexão local.
 • 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.
12 Administração do SGBD PostgreSQL
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 figura 2.
Figura 2 – Disponibilização de acesso de usuário por meio do pg_hba.conf 
Fonte: Elaborado pela autora (2021).
Ainda, 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. Em Painel de controle>Sistema e Segurança>Windows Defender 
Firewall (ver figura 3) 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 e em Portas 
Locais Específicas, digite 5432 (ver figura 6) 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.
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.
13Administração do SGBD PostgreSQL
6. Escolha um nome para a nova regra (p. ex., Acesso externo 
postgresql) e, opcionalmente, uma descrição e clique em finalizar. 
7. Por fim, é só reiniciar ou dar um reload no serviço PostgreSQL 
(pode achar o serviço em iniciar>executar:services.msc) para 
ativar as configurações realizadas.
Figura 3 – Caminho para configurações avançadas do firewall 
Fonte: Elaborado pela autora (2021).
14 Administração do SGBD PostgreSQL
Figura 4 – Janela de configurações avançadas do firewall do Windows
Fonte: Elaborado pela autora (2021).
Figura 5 – Criando uma nova regra
Fonte: Elaborado pela autora (2021).
15Administração do SGBD PostgreSQL
Figura 6 – Especificando o número da porta para o firewall
Fonte: Elaborado pela autora (2021).
Figura 7 – Confirmação da configuração
Fonte: Elaborado pela autora (2021).
16 Administração do SGBD PostgreSQL
Conectando ao banco de dados PostgreSQL no Linux
Para habilitar o acesso remoto ao banco de dados PostgreSQL no 
Linux, é muito simples. Da mesma forma do Windows, o Linux também 
requer a configuração do arquivo pg_hba.conf e, ainda, o postgresql.conf.
Para isso, vamos utilizar o editor vi do Linux para editarmos o 
primeiro arquivo que é o pg_hba.conf, por meio do seguinte comando:
vi /var/lib/pgsql/data/pg_hba.conf
Agora que você tem o arquivo aberto, vamos seguir um passo a 
passo para a configuração remota:
1. Você pode permitir 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 permitir apenas para um usuário e IP específicos, podemos 
utilizar o seguinte exemplo: adicionar o usuário stark do setor de 
marketing, cujo IP é 10.10.10.7/24: 
host marketing stark 10.10.10.7/24 md5
2. Agora, basta salvar o arquivo e fechar o editar. 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
17Administração do SGBD PostgreSQL
a. Ao abrir o arquivo de edição, você irá fazer a busca por listen_
address=‘localhost‘ e, se houve, remover o comentário (#). Em 
seguida, você 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‘
b. Ainda no mesmo documento, procure pela porta e remova o 
comentário (#):
port = 5432
3. Para o ú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
Até agora, já sabemos que o pgAdmin é compatível com os 
Sistemas Operacionais Windows, Linux e Mac. Sendo assim, vamos te 
mostrar como realizar uma conexão ao banco de dados PostgreSQL por 
meio do pgAdmin. Vamos ao passo a passo!
1. No menu iniciar, busque por PgAdmin e abra-o.
Para isso, você precisa ter o pgAdmin instalado no seu computador, 
como já te mostramos na Unidade anterior como realizar essa instalação, 
vamos considerar que você já o tem disponível em sua máquina. Também, 
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.
18 Administração do SGBD PostgreSQL
Figura 8 – Criar uma nova conexão remota
Fonte: Elaborado pela autora (2021).
3. Agora, na aba connection, basta preencher os dados de 
configuração. Na figura 9 podemos observar o seu preenchimento.
Figura 9 – Preenchendo dados de conexão
Fonte: Elaborado pela autora (2021).
19Administração do SGBD PostgreSQL
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 (2021).
SAIBA MAIS:
Quer se aprofundar neste tema? Recomendamos o acesso 
à seguinte fonte de consulta e aprofundamento: Artigo: 
“Como alterar a senha de uma base de dados” (BRASIL, 
2020). Disponível aqui.
https://king.host/wiki/artigo/alterar-senha-de-uma-base-de-dados/.
20 Administração do SGBD PostgreSQL
RESUMINDO:
E então? Gostou do que lhe mostramos? Aprendeu mesmo 
tudinho? Agora, só para termos certeza de que você 
realmente entendeu o tema de estudo deste capítulo, 
vamos resumir tudo o que vimos. Você deve ter aprendido 
que podemos realizar 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 Linux, 
podemos configurar meio do pgAdmin que é compatível 
com Windows, Linux e Mac. Também vimos como inserir 
permissões de conexão para um usuário específico em 
um ambiente, grupo ou endereço IP. Ainda, apresentamos 
como realizar a configuração no arquivo postgresql.conf e 
no pg_hba.conf, nos ambientes Windows e Linux.
21Administração do SGBD PostgreSQL
Componentes do PostgreSQL
OBJETIVO:
Agora que você já sabe como 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 disso, irá aprender 
como escolher quais componentes serão instalados. Você 
sabia que esse tipo de trabalho é extremamente importante 
para uma empresa? Pois é! Saber a funcionalidade dos 
componentes e sua utilidade junto à empresa é o que 
garante o sucesso de uma boa administração do sistema 
de gerenciamento de banco de dados. E isso é uma grande 
responsabilidade, não é mesmo? Então vamos lá. Rumo ao 
avanço!
Componentes de acesso a dados do 
PostgreSQL
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 64 
bits. O PgDAC foi projetado para ajudar os programadores a desenvolver 
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 
gerenciamento de conjunto de dados. Sendo assim, temos os seguintes 
componentes de acesso a dados:
22 Administração do SGBD PostgreSQL
 • TPgConnection – o componente 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. 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 precisar 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 – tal componente é 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.
23Administração do SGBD PostgreSQL
Componentes do banco de dados do 
PostgreSQL
De acordo com Juba e Volkov (2019, p. 75), os componentes de 
um banco de dados do PostgreSQL podem ser considerados como 
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, cujo schema é usado para organizar os objetos de maneira 
semelhante a namespaces em linguagens de programação de alto nível. 
Vejamos melhor os schemas e tabelas.
Schemas
Na Unidade anterior, soubemos que um schema (ou esquema) é 
um namespace que organiza o banco de dados em grupos lógicos que 
pertence a um usuário do banco de dados. Entretanto, 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.
Figura 11 – Objetos do Schema 
Range
Table
View
Index
Function
Type
...
Domain
Schema
Fonte: Elaborado pela autora (2021).
24 Administração do SGBD PostgreSQL
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 acessar esse schema 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.
Ainda, de acordo com Juba e Volkov (2019, p. 75), quando um 
usuário deseja acessar um 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 
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 o seguinte comando:
TABLE pg_catalog.pg_database;
Muitos 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 que é composto apenas 
pelo nome do objeto, sem o schema. Para isso, o PostgreSQL fornece 
uma configuração search_path que é 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
25Administração do SGBD PostgreSQL
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 no search_path, 
será gerado um erro. Os esquemas são usados pelos seguintes motivos: 
 • Autorização de controle – em um ambiente de banco de dados 
multiusuário, podem-se usar esquemas para agrupar objetos com 
base em funções.
 • Organizar objetos de banco de dados – podem-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 essas extensões em esquemas separados permite ao 
desenvolvedor reutilizar essas extensões 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 – o ciclo de vida da tabela começa com a 
criação da tabela e termina com a eliminação da tabela.
 • Tabela temporária – o ciclo de vida da tabela é a sessão do 
usuário. Isso é frequentemente usado com linguagensprocedurais 
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). 
26 Administração do SGBD PostgreSQL
Tabelas não registradas não são seguras contra travamentos. 
Além disso, como a replicação de streaming é baseada no envio 
de arquivos de log, as tabelas não registradas não podem ser 
replicadas para o nó escravo.
 • Tabela filha – uma 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 possui um determinado valor.
Componentes do sistema de arquivos do 
PostgreSQL
Você sabia que mesmo que tenhamos uma estrutura completa 
de hardware e software dedicados, os servidores ainda não trabalharão 
corretamente sem usar os componentes especificados pelo administrador? 
Pois é! De acordo com Thomas (2020, p. 477), os servidores ainda não 
possuem 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
Do inglês, Distributed Replicated Block Device – (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 
27Administração do SGBD PostgreSQL
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.
Figura 12 – Estrutura do DRBD 
Fonte: Elaborada pela autora com base em Thomas (2020, p. 477).
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
Do inglês, LVM – Logical Volume Manager, é uma camada de 
abstração que fica entre o sistema de arquivos e os dispositivos de disco 
subjacentes, possibilitado 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 on-line. 
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 
quase qualquer alteração do LVM, o PostgreSQL pode permanecer on-
line e solicitar serviços.
28 Administração do SGBD PostgreSQL
XFS
Do inglês, XFS – Extents File System, possui a estrutura do sistema de 
arquivos do tipo jounaling e fornece crescimento on-line, congelamento 
de LVM e vários tipos de manutenção e ferramentas de reparo. Apesar 
de o XFS e o EXT4 serem sistemas de arquivos com jounaling, o XFS 
possui a vantagem de realizar grupos de alocação. Por um lado, o EXT4 
possui apenas uma 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, possam ter um melhor desempenho.
SAIBA MAIS:
O criador do DRBD disponibilizou uma documentação 
bastante detalhada sobre soluções de problemas do 
sistema: artigo – “The DRBD User’s Guide” Disponível aqui. 
RESUMINDO:
Agora, chegamos ao fim do nosso capítulo. Gostou do que 
lhe mostramos? Aprendeu mesmo tudinho? Agora, só para 
termos certeza de que você realmente entendeu o tema 
de estudo deste capítulo, vamos resumir tudo o que vimos. 
Você deve ter aprendido que há várias abordagens quando 
se trata de componentes do PostgreSQL. Apresentamos a 
você 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 64 bits. Também vimos com mais 
detalhes, dois principais componentes do banco de dados: 
schemas e tabelas. Também, vimos três abordagens: DRBD, 
LVM e XFS, que melhoram o desempenho do servidor.
https://www.linbit.com/drbd-user-guide/users-guide-drbd-8-4/
29Administração do SGBD PostgreSQL
Buffering e sincronização
OBJETIVO:
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, irá 
aprender como 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? Pois é! 
E é uma das principais responsabilidades do administrador 
ou desenvolvedor do banco de dados ajustar o PostgreSQL 
de acordo com a carga de trabalho do sistema, é com isso 
que garantimos o melhor desempenho para o servidor. E 
isso é uma grande responsabilidade, não é mesmo? Então 
vamos lá.
PostgreSQL buffer
O PostgreSQL usa seu próprio buffer e também usa 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, cujo valor padrão para 
buffers compartilhados é 128 MB; no entanto, é recomendável defini-lo 
para cerca de 25% da memória total.
30 Administração do SGBD PostgreSQL
Às vezes, aumentar o shared_buffer para um valor muito alto pode 
elevar o aumento no desempenho, porque o banco de dados pode ser 
completamente armazenado em cache na RAM. Contudo, a desvantagem 
de aumentar demais esse valor é poder 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 trabalhados. Caso esse conjunto de dados de trabalho 
possa caber facilmente na memória RAM, então é possível que eleve o 
valor de shared_buffer para conter seu banco de dados inteiro, de forma 
que todo o conjunto de dados de trabalho possa residir 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 benchmarkpara encontrar o equilíbrio certo.
Ainda se lembra de como abrir o psql? Pois é! Vamos precisar dele. 
Caso você não esteja lembrando: no Windows, basta pesquisar por SQL 
shell e abrir o terminal; no Linux, você pode digitar 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) com base no seguinte comando:
Figura 13 – Tamanho alocado para o shared_buffer
Fonte: Elaborado pela autora (2021).
31Administração do SGBD PostgreSQL
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. 
Entretanto, ele apresenta apenas uma estimativa, não a memória alocada 
exata ou o tamanho do cache. Ainda, não é alocada à 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.
No caso da configuração work_mem, é usada para a classificação 
complexa e, para esses casos, sugere-se aumentar o valor de work_mem 
para fins de bons resultados, e suas classificações são 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.
Vejamos um exemplo de como aumentar o valor do work_mem 
comparando o retorno da consulta query:
postgres =# SET work_mem TO "2MB";
postgres =# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
 QUERY PLAN 
---------------------------------------------------------------------------
Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24)
 Workers Planned: 4
 -> Sort (cost=508181.79..514431.86 rows=2500029 width=24)
 Sort Key: b
 -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 
width=24)
(5 rows)
32 Administração do SGBD PostgreSQL
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 
mostrada, 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:
postgres=# SET work_mem TO "256MB";
postgres=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
 QUERY PLAN 
---------------------------------------------------------------------------
Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24)
 Workers Planned: 4
 -> Sort (cost=354367.29..360617.36 rows=2500029 width=24)
 Sort Key: b
 -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 
width=24)
Dessa forma, diante dos exemplos, podemos observar que o custo 
da consulta foi reduzido para 360617,36 de 514431,86 – 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.
Perceba que quando autovacuum é executado, então, até 
autovacuum_max_workers pode ser alocada, então tome cuidado para 
não definir o valor padrão muito alto.
Vejamos um exemplo do retorno aumentando o valor de 
maintenance_work_mem de 10 MB pra 256 MB e vamos observar o 
tempo de resposta:
33Administração do SGBD PostgreSQL
postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';
postgres=# CREATE INDEX new_idx ON new (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)
postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';
postgres=# CREATE INDEX new_idx ON new (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)
O que você concluiu mediante os dois retornos? Perceba que 
quando definimos em 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
Às vezes, para fornecer a durabilidade de dados aceitável, uma 
configuração de alta disponibilidade deve utilizar confirmações síncronas. 
Por meio 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, onde 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 é, iremos te mostrar como configurar um servidor em espera 
assíncrono para o modo síncrono. Vamos ver!
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. Também, quaisquer 
outros nós de espera síncronos devem receber nomes diferentes.
34 Administração do SGBD PostgreSQL
Nesse 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, deveremos usar o mesmo nome no local.
Sempre que desejarmos alterar a variável synchronous_standby_
names, precisamos apenas dizer ao PostgreSQL para recarregar seus 
arquivos de configuração. Dessa forma, será feito 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 nesse servidor, assim como 
no primário.
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 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:
E pronto! Como podemos ver nesse 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:
35Administração do SGBD PostgreSQL
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 essa instrução SQL antes de tentar uma consulta de gravação básica:
SET synchronous_commit TO false;
Esse procedimento desativa a replicação síncrona temporariamente 
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.
Contudo, essa operação envolve grandes quantidades de entrada/
saída e várias operações de leitura/escrita em disco. Para realizar tal 
36 Administração do SGBD PostgreSQL
procedimento, os usuários podem emitir CHECKPOINT sempre que 
houver necessidade de automatizar o sistema com base nos 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 com curiosidade 
pra conhecer mais? A documentação do PostgreSQL 
oferece ótimos recursos e está disponível aqui.
RESUMINDO:
Agora, chegamos ao fim do nosso capítulo. Gostou do 
que lhe mostramos? Aprendeu mesmo tudinho? Agora, só 
para termos certeza de que você realmente entendeu o 
tema de estudo deste capítulo, vamos resumir tudo o que 
vimos. Você deve ter aprendido os conceitos essenciais 
de buffering e sincronização em um servidor PostgreSQL, 
bem como a importância e exemplos. Apresentamos a 
você como configurar o shared_buffer. Também vimos 
como implementar o controle transacional dos dados com 
comandos como commit, checkpoint etc. Não esquecendo 
que também aprendemos como executar e desativar a 
replicação síncrona.
https://www.postgresql.org/docs/current/runtime-config-wal.html
37Administração do SGBD PostgreSQL
Procedimento de fallback
OBJETIVO:
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 
possui mais de 200 configurações que controlam o 
comportamento do banco de dados? Pois é! 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. E 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 e sabe para que serve? 
Pois bem. 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 
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.
38 Administração do SGBD PostgreSQL
Point in Time Recovery (PITR) no 
PostgreSQL
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. E, 
ainda, 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 – Write-Ahead Log (do inglês, 
Registro de Gravação Antecipada) 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, é 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, pode 
dar suporte à restauração de um cluster de banco de dados inteiro, não 
só de um subconjunto. 
Os arquivos WAL, por padrão, ocupam 16 MB. Sendo assim, vejamos 
um passo a passo como exemplo 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:
postgres =# mkdir teste 
postgres =# /usr/lib/postgresql/12/bin/initdb -D teste 
postgres =# /usr/lib/postgresql/12/bin/pg_ctl -D teste -l teste/teste.
log start
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 
39Administração do SGBD PostgreSQL
origem sem a exigência do caminho específico; já %f é o arquivo 
de origem precedido. 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:
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. E, para 
isso, é necessário habilitá-lo 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. Finalizadaa configuração, agora carregamos as modificações no 
psql.
postgres =# psql 
teste=# SELECT pg_reload_conf ();
40 Administração do SGBD PostgreSQL
9. O próximo passo será a execução do backup. Nota na linha de 
comando que <dir_backup> aponta onde os dados serão salvos. 
E 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>
10. Agora, o comando será para o processo de recuperação do 
backup. No PostgreSQL tal procedimento é realizado no arquivo 
recovery.conf que, na inicialização, o modo de espera inicia 
restaurando todos os WAL disponíveis no local do arquivo, 
chamando restore_command. E o recovery_target_time indica 
a data e hora dos dados a serem recuperados.
A localização do recovery.conf deve ser no diretório principal do 
backup. Como já falamos, nosso diretório escolhido foi nomeado como 
backup. Uma vez que atinge o final do WAL disponível lá 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 com base no ú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. Esse 
loop de novas tentativas do arquivo, pg_xlog e por meio da replicação de 
streaming continua até que o servidor seja interrompido ou o failover seja 
disparado por um arquivo acionador.
//no arquivo recovery.conf 
restore_command = 'rsync diretorio_destino/%f %p' 
recovery_target_time = '2020-03-06 15:46:18'
11. É conveniente realizar a limpeza dos logs de transação após a 
consumação pelo backup por meio do parâmetro chamado 
archive_cleanup_command.
41Administração do SGBD PostgreSQL
//no arquivo recovery.conf 
archive_cleanup_command = '/usr/lib/postgresql/12/bin/pg_
archivecleanup diretorio_destino %r'
12. Devemos notar que é necessário alterar as permissões de leitura, 
escrita e execução para o diretório que contém o backup. Senão 
o PostgreSQL emitirá um erro.
//no arquivo recovery.conf 
Sudo chmod 700 <dir_backup>
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.
//no arquivo postgresql.conf 
port = 5433
14. Pronto! Agora será o nosso comando final para realizar o 
procedimento de recuperação com base em todos os arquivos 
WAL e armazenando em backup/backup.log.
postgres =# /usr/lib/postgresql/12/bin/pg_ctl -D backup -l backup/
backup.log start
Replicação física dos dados do PostgreSQL
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.
42 Administração do SGBD PostgreSQL
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 de leitura; nesse 
caso, seria denominado hot standby.
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.
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á este 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.
43Administração do SGBD PostgreSQL
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 aqui.
RESUMINDO:
Agora, chegamos ao fim do nosso capítulo. Gostou do que 
lhe mostramos? Aprendeu mesmo tudinho? Agora, só para 
termos certeza de que você realmente entendeu o tema 
de estudo deste capítulo, vamos resumir tudo o que vimos. 
Nesse material, você deve ter aprendido como realizar 
os procedimentos de fallbacks aplicando comandos 
de recuperação de dados em log de transações. Vimos 
também um exemplo prático de como realizar um PITR e 
uma replicação física dos dados no PostgreSQL. E agora 
já sabemos 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.
https://www.postgresql.org/docs/current/app-pgbasebackup.html
44 Administração do SGBD PostgreSQL
REFERÊNCIAS
BRASIL, A. Como alterar a senha de uma base de dados. Kinghost, 
2020. Disponível em: https://bit.ly/3Etxcbm. Acesso em: 11 set. 2020.
JUBA, S.; VOLKOV, A. Learning PostgreSQL 11: a beginner’s guide 
to building high-performance PostgreSQL database solutions. [s. l.] Packt 
Publishing Ltd., 2019.
LIMBIT. The DRBD User’s Guide. Disponível em: https://bit.
ly/3dv5tv2. Acesso em: 2 set. 2020.
POSTGRESQL. Documentação. Disponível em: https://bit.ly/3Et7uUh. 
Acesso em: 8 set. 2020.
THOMAS, S. PostgreSQL 12 High Availability Cookbook: Over 100 
recipes to design a highly available server with the advanced features of 
PostgreSQL 12. [s. l.] Packt Publishing Ltd., 2020.
	_Hlk49204831
	_Hlk50732224
	Conexão do PostgreSQL a aplicações
	Conectandoao 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