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: DEFINIÇÃO:
para o início do houver necessidade
desenvolvimento de de se apresentar um
uma nova compe- novo conceito;
tência;

NOTA: IMPORTANTE:
quando forem as observações
necessários obser- escritas tiveram que
vações ou comple- ser priorizadas para
mentações para o você;
seu conhecimento;
EXPLICANDO VOCÊ SABIA?
MELHOR: curiosidades e
algo precisa ser indagações lúdicas
melhor explicado ou sobre o tema em
detalhado; estudo, se forem
necessárias;
SAIBA MAIS: REFLITA:
textos, referências se houver a neces-
bibliográficas e links sidade de chamar a
para aprofundamen- atenção sobre algo
to do seu conheci- a ser refletido ou dis-
mento; cutido sobre;
ACESSE: RESUMINDO:
se for preciso aces- quando for preciso
sar um ou mais sites se fazer um resumo
para fazer download, acumulativo das últi-
assistir vídeos, ler mas abordagens;
textos, ouvir podcast;
ATIVIDADES: TESTANDO:
quando alguma quando o desen-
atividade de au- volvimento de uma
toaprendizagem for competência for
aplicada; 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

 Administração do SGBD PostgreSQL 7

03
UNIDADE
8 Administração do SGBD PostgreSQL

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!
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.
 Administração do SGBD PostgreSQL 11

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 [Link].
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.
 Administração do SGBD PostgreSQL 13

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:[Link]) 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).

 Administração do SGBD PostgreSQL 15

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 [Link].

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 [Link]/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 é [Link]/24:

host marketing stark [Link]/24 md5

2. Agora, basta salvar o arquivo e fechar o editar. Assim, nosso

próximo passo será a edição do arquivo [Link] por meio
do seguinte comando:

vi /var/lib/pgsql/data/[Link]
 Administração do SGBD PostgreSQL 17

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=‘[Link]‘

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).

 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 (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.
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 [Link] e
no pg_hba.conf, nos ambientes Windows e Linux.
 Administração do SGBD PostgreSQL 21

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.
 Administração do SGBD PostgreSQL 23

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
Schema
Function

Type

...

Domain

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
 Administração do SGBD PostgreSQL 25

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 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).
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
 Administração do SGBD PostgreSQL 27

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.
 Administração do SGBD PostgreSQL 29

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 benchmark para 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).

 Administração do SGBD PostgreSQL 31

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:
 Administração do SGBD PostgreSQL 33

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 [Link] ou [Link] 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 [Link] 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:
 Administração do SGBD PostgreSQL 35

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.
 Administração do SGBD PostgreSQL 37

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 [Link], 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
 Administração do SGBD PostgreSQL 39

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. Finalizada a 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
[Link] 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 [Link] 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 [Link]

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.
 Administração do SGBD PostgreSQL 41

//no arquivo [Link]

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 [Link]

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
[Link].

//no arquivo [Link]

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/[Link].

postgres =# /usr/lib/postgresql/12/bin/pg_ctl -D backup -l backup/

[Link] 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 [Link], é
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.
 Administração do SGBD PostgreSQL 43

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.
44 Administração do SGBD PostgreSQL

REFERÊNCIAS
BRASIL, A. Como alterar a senha de uma base de dados. Kinghost,
2020. Disponível em: [Link] 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: [Link]

ly/3dv5tv2. Acesso em: 2 set. 2020.

POSTGRESQL. Documentação. Disponível em: [Link]

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.