Operação e alta disponibilidade do PostgreSQL

Introdução

Nos dois posts anteriores instalamos o PostgreSQL, criamos usuários, bancos e tabelas, e depois fechamos o acesso com o pg_hba.conf, contas separadas no Linux e TLS. Agora o foco é o dia a dia de um servidor em produção: saber se ele está saudável, ajustar a memória, entender o VACUUM, fazer backups que permitam voltar a um ponto exato no tempo, atualizar versões e manter uma réplica pronta para assumir.

Os comandos seguem o mesmo padrão da série, com Debian 12 usando o pacote postgresql-17 e Rocky Linux ou Oracle Linux 9 usando o pacote do AppStream. Quando um caminho ou comando muda entre as distribuições, os dois aparecem lado a lado.

• Debian: configuração em /etc/postgresql/17/main/ e dados em /var/lib/postgresql/17/main/

• Rocky/Oracle Linux: configuração e dados juntos em /var/lib/pgsql/data/

O que precisa rodar como o usuário do serviço usa sudo -u postgres, seguindo a separação de contas que montamos no post anterior.

Monitorar o servidor

O primeiro sinal de vida é o pg_isready, que já usamos no post 1. Ele responde se o servidor aceita conexões, sem precisar de usuário nem senha:

pg_isready -h localhost -p 5432

Para ver o que está rodando agora, a view pg_stat_activity mostra cada conexão, o estado e a consulta atual. Esta consulta lista só as conexões ativas, das mais antigas para as mais novas:

SELECT pid, usename, datname, client_addr, state,

       now() - query_start AS duracao,

       left(query, 60) AS consulta

FROM pg_stat_activity

WHERE state <> 'idle'

ORDER BY duracao DESC;

Uma consulta presa há muito tempo pode ser cancelada pelo pid. Se ela não responder ao cancelamento, pg_terminate_backend encerra a conexão inteira:

SELECT pg_cancel_backend(12345);

SELECT pg_terminate_backend(12345);

Conexões em uso comparadas com o limite configurado:

SELECT count(*) AS conexoes,

       current_setting('max_connections') AS limite

FROM pg_stat_activity;

Taxa de acerto do cache (cache hit ratio). Em um banco transacional, valores abaixo de 99% indicam que o shared_buffers pode estar pequeno ou que há consultas lendo tabelas inteiras sem índice:

SELECT datname,

       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit

FROM pg_stat_database

WHERE datname IS NOT NULL;

Tamanho de cada banco, do maior para o menor:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS tamanho

FROM pg_database

ORDER BY pg_database_size(datname) DESC;

Registrar consultas lentas

O parâmetro log_min_duration_statement grava no log toda consulta que passar do tempo definido. 500 ms é um bom ponto de partida, e o valor pode ser reduzido depois que as piores consultas forem resolvidas:

# Debian: /etc/postgresql/17/main/postgresql.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/postgresql.conf

log_min_duration_statement = 500    # em milissegundos

log_lock_waits = on                 # registra quem ficou esperando por lock

log_temp_files = 0                  # registra ordenações que foram para o disco

Esses três parâmetros não exigem restart. Um reload aplica a mudança sem derrubar conexões:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Estatísticas de consultas com pg_stat_statements

O log mostra consultas lentas uma a uma. A extensão pg_stat_statements soma tudo: quantas vezes cada consulta rodou, o tempo total e a média. Às vezes o problema não é uma consulta de 5 segundos, e sim uma de 20 ms que roda um milhão de vezes por dia.

No Debian a extensão já vem no pacote principal. No Rocky/Oracle Linux é preciso instalar o contrib:

# Rocky/Oracle Linux

sudo dnf install postgresql-contrib

Ela precisa ser carregada na inicialização do servidor:

# Debian: /etc/postgresql/17/main/postgresql.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = top

Esse parâmetro exige restart. O comando é o mesmo nas duas distribuições:

sudo systemctl restart postgresql

Depois do restart, crie a extensão no banco que será analisado:

sudo -u postgres psql -d app_db -c "CREATE EXTENSION pg_stat_statements;"

As cinco consultas que mais consumiram tempo desde o último reset:

SELECT calls,

       round(total_exec_time::numeric, 1) AS total_ms,

       round(mean_exec_time::numeric, 1) AS media_ms,

       left(query, 80) AS consulta

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 5;

Depois de um ajuste, SELECT pg_stat_statements_reset(); zera as estatísticas para medir de novo a partir de um ponto limpo.

Verificação automática com systemd

Consultar tudo isso à mão não escala. Um script curto, rodando como postgres por um timer do systemd, verifica se o servidor responde e se as conexões estão perto do limite. Se algo estiver errado, o script termina com erro e a unidade fica marcada como falha, o que aparece em systemctl --failed e no journal.

#!/bin/bash

# /usr/local/bin/pg-health.sh (Debian e Rocky/Oracle Linux)

# Verifica se o PostgreSQL responde e se as conexões estão abaixo do limite

LIMITE=80   # alerta acima de 80% de max_connections

if ! pg_isready -q; then

    echo "ERRO: PostgreSQL não responde"

    exit 1

fi

USO=$(psql -XAtc "SELECT round(100.0 * count(*) / current_setting('max_connections')::int) FROM pg_stat_activity;")

if [ "$USO" -ge "$LIMITE" ]; then

    echo "ALERTA: $USO% das conexões em uso"

    exit 1

fi

echo "OK: servidor respondendo, $USO% das conexões em uso"

A unidade de serviço roda o script com o usuário postgres, que se autentica pelo socket local via peer, sem senha em arquivo:

# /etc/systemd/system/pg-health.service (Debian e Rocky/Oracle Linux)

[Unit]

Description=Verificação de saúde do PostgreSQL

[Service]

Type=oneshot

User=postgres

ExecStart=/usr/local/bin/pg-health.sh

E o timer, que dispara a verificação a cada 5 minutos:

# /etc/systemd/system/pg-health.timer (Debian e Rocky/Oracle Linux)

[Unit]

Description=Roda pg-health a cada 5 minutos

[Timer]

OnBootSec=2min

OnUnitActiveSec=5min

[Install]

WantedBy=timers.target

Ative o timer e confira o resultado:

sudo chmod 755 /usr/local/bin/pg-health.sh

sudo systemctl daemon-reload

sudo systemctl enable --now pg-health.timer

systemctl list-timers pg-health.timer

journalctl -u pg-health.service -n 20

Para receber um aviso quando a verificação falhar, adicione OnFailure= apontando para uma unidade que envie e-mail, ou faça a sua ferramenta de monitoramento ler o journal. Em ambientes maiores, Zabbix ou Prometheus com o postgres_exporter (ambos open source) fazem esse papel com gráficos e histórico.

Ajustar memória e conexões

Os valores padrão do PostgreSQL foram pensados para rodar em qualquer máquina, inclusive nas pequenas. Em um servidor dedicado vale ajustar alguns parâmetros. Os números abaixo são um ponto de partida para um servidor com 8 GB de RAM dedicado ao banco e disco SSD:

# Debian: /etc/postgresql/17/main/postgresql.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/postgresql.conf

shared_buffers = 2GB              # cerca de 25% da RAM, exige restart

effective_cache_size = 6GB        # cerca de 75% da RAM, não reserva memória

work_mem = 16MB                   # por operação de ordenação ou hash

maintenance_work_mem = 512MB      # usado por VACUUM e CREATE INDEX

max_connections = 100             # exige restart

random_page_cost = 1.1            # para SSD, o padrão 4 supõe disco rotacional

O que cada um faz e onde está o cuidado:

• shared_buffers: a memória própria do PostgreSQL para guardar páginas em cache. Acima de 25% a 40% da RAM o ganho costuma ser pequeno, porque o cache do sistema operacional também trabalha para o banco.

• effective_cache_size: não aloca nada. Só informa ao planejador quanto cache existe no total, o que pesa na escolha entre usar um índice ou ler a tabela inteira.

• work_mem: cuidado ao aumentar. O limite vale para cada operação, então uma consulta complexa pode usar várias vezes esse valor, e cada conexão tem as suas. 100 conexões com 4 operações de 64MB cada já somam 25 GB.

• max_connections: cada conexão é um processo no servidor. Se a aplicação precisa de centenas de conexões, o caminho é um pool como o PgBouncer (open source) na frente do banco, e não aumentar esse número.

• random_page_cost: em SSD, valores perto de 1.1 fazem o planejador usar índices com mais frequência, o que normalmente é o certo nesse tipo de disco.

Para conferir os valores em uso, quais pedem restart (context igual a postmaster) e se há mudança pendente:

SELECT name, setting, unit, context, pending_restart

FROM pg_settings

WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem',

               'maintenance_work_mem', 'max_connections', 'random_page_cost');

Outra forma de mudar parâmetros é o comando ALTER SYSTEM, que grava no arquivo postgresql.auto.conf dentro do diretório de dados. Esse arquivo tem prioridade sobre o postgresql.conf. Escolha um dos dois métodos e registre qual a equipe usa, porque misturar os dois é a causa clássica de mudar o arquivo e nada acontecer.

Para gerar um primeiro conjunto de valores a partir da RAM, do número de CPUs e do tipo de disco, o PGTune (open source) é um bom ponto de partida. Depois disso, ajuste com base no que o pg_stat_statements e o cache hit ratio mostrarem.

VACUUM e autovacuum

No PostgreSQL, um UPDATE não sobrescreve a linha. Ele grava uma versão nova e marca a antiga como morta, e o DELETE também só marca. É esse mecanismo, chamado MVCC, que permite que leituras não bloqueiem escritas. O custo é que as linhas mortas (dead tuples) continuam ocupando espaço até o VACUUM liberar esse espaço para reuso.

O autovacuum faz esse trabalho sozinho, em segundo plano. A regra é simples: nunca desligue o autovacuum. Se ele estiver atrapalhando, ajuste os parâmetros dele, mas não o desative. Sem ele as tabelas incham, as consultas ficam lentas e, no limite, o banco para de aceitar escritas.

Tabelas com mais linhas mortas e a última vez que o autovacuum passou por elas:

SELECT relname, n_live_tup, n_dead_tup,

       last_autovacuum, last_autoanalyze

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 10;

Para rodar manualmente em uma tabela, por exemplo depois de uma carga ou limpeza grande:

VACUUM (VERBOSE, ANALYZE) pedidos;

As variações que aparecem na documentação e em fóruns:

• VACUUM: libera o espaço das linhas mortas para reuso dentro da própria tabela. Não bloqueia leituras nem escritas, mas o arquivo não diminui no disco.

• VACUUM FULL: reescreve a tabela inteira e devolve o espaço ao sistema operacional, mas trava a tabela por completo durante o processo e precisa de espaço livre igual ao tamanho dela. Use só em janela de manutenção. A extensão pg_repack (open source) faz o mesmo trabalho travando a tabela só por um instante.

• ANALYZE: atualiza as estatísticas que o planejador usa para escolher o plano de execução. O autovacuum também roda o ANALYZE automaticamente.

Wraparound de transações

Cada transação recebe um número de 32 bits, o que dá cerca de 2 bilhões de transações antes de o contador dar a volta. O VACUUM congela as linhas antigas para que esse limite nunca seja atingido. Se o congelamento ficar para trás, o PostgreSQL força um VACUUM agressivo e, em último caso, para de aceitar escritas para proteger os dados.

Esta consulta mostra a idade de cada banco e quanto falta para o limite:

SELECT datname, age(datfrozenxid) AS idade,

       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_limite

FROM pg_database

ORDER BY idade DESC;

Idades abaixo de 200 milhões, o padrão de autovacuum_freeze_max_age, são normais. Se a idade sobe sem parar, procure o que está impedindo o VACUUM de avançar:

• Conexões no estado idle in transaction há muito tempo em pg_stat_activity. O parâmetro idle_in_transaction_session_timeout encerra essas sessões automaticamente.

• Slots de replicação abandonados, que aparecem mais adiante neste post.

• Transações preparadas esquecidas, visíveis em pg_prepared_xacts.

Backup físico com pg_basebackup

O pg_dump do post 1 gera um backup lógico, ótimo para copiar um banco ou uma tabela. Mas o restore de um banco grande demora, e o ponto de recuperação é sempre o momento em que o dump rodou. O backup físico copia os arquivos do cluster inteiro e, junto com o arquivamento do WAL (o log de transações), permite voltar a qualquer momento entre dois backups. Esse recurso se chama PITR, point-in-time recovery.

Comece criando os diretórios de backup, com acesso só para o postgres:

sudo mkdir -p /backup/pg/base /backup/pg/wal

sudo chown -R postgres:postgres /backup/pg

sudo chmod 700 /backup/pg

No Rocky/Oracle Linux o SELinux impede o processo do PostgreSQL de gravar em diretórios fora dos dele. Marque o diretório de backup com o contexto do PostgreSQL:

# Rocky/Oracle Linux

sudo semanage fcontext -a -t postgresql_db_t "/backup/pg(/.*)?"

sudo restorecon -Rv /backup/pg

Arquivar o WAL

Cada arquivo de WAL completo é copiado para /backup/pg/wal pelo archive_command. O teste antes do cp impede que um arquivo já arquivado seja sobrescrito:

# Debian: /etc/postgresql/17/main/postgresql.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/postgresql.conf

wal_level = replica      # já é o padrão, mas deixe explícito

archive_mode = on        # exige restart

archive_command = 'test ! -f /backup/pg/wal/%f && cp %p /backup/pg/wal/%f'

archive_timeout = 300    # fecha um arquivo de WAL a cada 5 minutos mesmo com pouco movimento

Reinicie o serviço, force a troca de arquivo de WAL e confira se o arquivamento funcionou:

sudo systemctl restart postgresql

sudo -u postgres psql -c "SELECT pg_switch_wal();"

sudo -u postgres psql -c "SELECT archived_count, last_archived_wal, failed_count, last_failed_wal FROM pg_stat_archiver;"

Fique de olho no failed_count. Se o archive_command falhar, o PostgreSQL não apaga o WAL que ainda não foi arquivado, e o diretório pg_wal cresce até lotar o disco. Inclua essa consulta na verificação automática.

Backup base diário

O backup base é o ponto de partida do restore. O comando abaixo gera um arquivo compactado com os dados (base.tar.gz) e outro com o WAL necessário para que ele seja consistente (pg_wal.tar.gz):

sudo -u postgres pg_basebackup -D /backup/pg/base/$(date +%F) -Ft -z -X stream -c fast -P

Para rodar todo dia com retenção, o script abaixo faz o backup, apaga os backups base mais antigos que 7 dias e remove o WAL arquivado que já não serve para nenhum backup restante:

#!/bin/bash

# /usr/local/bin/pg-basebackup.sh (Debian e Rocky/Oracle Linux)

# Backup físico diário com retenção de 7 dias

set -euo pipefail

DESTINO=/backup/pg/base

WAL=/backup/pg/wal

RETENCAO=7

# Backup base do dia, com o WAL necessário incluído

pg_basebackup -D "$DESTINO/$(date +%F)" -Ft -z -X stream -c fast

# Remove backups base mais antigos que a retenção

find "$DESTINO" -mindepth 1 -maxdepth 1 -type d -mtime +$RETENCAO -exec rm -rf {} +

# Remove WAL com um dia de folga além da retenção

find "$WAL" -type f -mtime +$((RETENCAO + 1)) -delete

echo "Backup concluído em $DESTINO/$(date +%F)"

A unidade de serviço roda o script como postgres:

# /etc/systemd/system/pg-basebackup.service (Debian e Rocky/Oracle Linux)

[Unit]

Description=Backup físico diário do PostgreSQL

[Service]

Type=oneshot

User=postgres

ExecStart=/usr/local/bin/pg-basebackup.sh

E o timer agenda para as 2h30 todos os dias. Com Persistent=true, se o servidor estiver desligado nesse horário o backup roda assim que ele voltar:

# /etc/systemd/system/pg-basebackup.timer (Debian e Rocky/Oracle Linux)

[Unit]

Description=Backup físico diário do PostgreSQL

[Timer]

OnCalendar=*-*-* 02:30:00

Persistent=true

[Install]

WantedBy=timers.target

Ative da mesma forma que o pg-health:

sudo chmod 755 /usr/local/bin/pg-basebackup.sh

sudo systemctl daemon-reload

sudo systemctl enable --now pg-basebackup.timer

O /backup/pg está no mesmo servidor, então ainda é preciso copiar esse diretório para outro lugar (outro servidor, NAS ou armazenamento de objetos). Para produção, vale conhecer o pgBackRest e o Barman, ambos open source. Eles fazem backups incrementais e paralelos, verificam checksums, controlam a retenção sozinhos e enviam direto para um repositório remoto.

Restaurar para um ponto no tempo

Imagine que alguém rodou um DELETE sem WHERE às 14h35 e o último backup base é da madrugada. Com o WAL arquivado, dá para restaurar o backup base e reaplicar as transações até 14h30, antes do erro.

Defina o diretório de dados conforme a distribuição. Os próximos comandos usam essa variável:

# Debian

DADOS=/var/lib/postgresql/17/main

# Rocky/Oracle Linux

DADOS=/var/lib/pgsql/data

Pare o serviço e mova o diretório atual para o lado, sem apagar. Se algo der errado no restore, ele ainda está lá:

sudo systemctl stop postgresql

sudo mv $DADOS $DADOS.antigo

sudo -u postgres mkdir -m 700 $DADOS

Extraia o backup base e o WAL que veio com ele:

sudo -u postgres tar -xzf /backup/pg/base/2026-10-21/base.tar.gz -C $DADOS

sudo -u postgres tar -xzf /backup/pg/base/2026-10-21/pg_wal.tar.gz -C $DADOS/pg_wal

Informe de onde buscar o WAL arquivado e até onde reaplicar. No Rocky/Oracle Linux, edite o postgresql.conf que acabou de ser extraído do backup:

# Debian: /etc/postgresql/17/main/postgresql.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/postgresql.conf

restore_command = 'cp /backup/pg/wal/%f %p'

recovery_target_time = '2026-10-21 14:30:00'

recovery_target_action = 'promote'

O arquivo recovery.signal avisa o PostgreSQL que ele deve iniciar em modo de recuperação:

sudo -u postgres touch $DADOS/recovery.signal

sudo restorecon -Rv $DADOS      # só no Rocky/Oracle Linux

sudo systemctl start postgresql

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Quando o retorno for f, a recuperação terminou e o servidor voltou a aceitar escritas. O PostgreSQL apaga o recovery.signal sozinho. Confira os dados, remova as linhas recovery_target_time e recovery_target_action do arquivo de configuração e, só depois de validar com a área responsável pela aplicação, apague o diretório .antigo.

Um backup que nunca foi restaurado não é um backup. Repita esse procedimento em outro servidor com alguma regularidade, por exemplo a cada trimestre, e registre a data, o backup usado, o tempo que levou e o resultado. Em auditoria, esse registro vale tanto quanto o backup em si.

Atualizar o PostgreSQL

O PostgreSQL tem dois tipos de atualização, e o procedimento é bem diferente entre eles.

• Versão menor (17.5 para 17.6): só correções de bugs e de segurança. O formato dos dados não muda, então basta atualizar o pacote e reiniciar.

• Versão maior (16 para 17): o formato interno dos dados muda, e o diretório de dados precisa ser convertido com o pg_upgrade ou recarregado a partir de um dump.

Versão menor

Leia as release notes antes, porque algumas versões menores pedem um passo extra, como recriar um tipo específico de índice. Depois, atualize o pacote:

# Debian

sudo apt update

sudo apt install --only-upgrade postgresql-17

# Rocky/Oracle Linux

sudo dnf upgrade postgresql-server

Reinicie o serviço e confirme a versão em uso:

sudo systemctl restart postgresql

sudo -u postgres psql -c "SELECT version();"

Versão maior

Antes de qualquer atualização maior, tenha um backup base recente e um dump lógico de cada banco, e teste o procedimento inteiro em outro servidor com uma cópia dos dados.

No Debian, o pacote postgresql-common traz o pg_upgradecluster, que cuida da conversão e da configuração. A instalação da versão nova cria um cluster vazio, que precisa ser removido antes:

# Debian: atualização da versão 16 para a 17

sudo apt install postgresql-17

sudo pg_dropcluster 17 main --stop

sudo pg_upgradecluster -m upgrade 16 main

pg_lsclusters

O cluster 17 assume a porta 5432, e o 16 fica parado na 5433, intacto. Esse é o caminho de volta: se algo der errado, pare o 17, volte o 16 para a porta 5432 e inicie. Depois de validar a aplicação, remova o antigo com sudo pg_dropcluster 16 main.

No Rocky/Oracle Linux, as versões do PostgreSQL vêm em streams de módulo do AppStream, e o pacote postgresql-upgrade traz os binários da versão anterior para o pg_upgrade. Confira os streams disponíveis na sua versão com dnf module list postgresql:

# Rocky/Oracle Linux: troca de stream do módulo

sudo systemctl stop postgresql

sudo dnf module switch-to postgresql:16

sudo dnf install postgresql-upgrade

sudo postgresql-setup --upgrade

sudo systemctl start postgresql

O postgresql-setup guarda os dados antigos em /var/lib/pgsql/data-old, que é o caminho de volta nesse caso. Revise os arquivos de configuração novos, porque ajustes como os de memória e o pg_hba.conf precisam ser conferidos.

Nas duas distribuições, as estatísticas do planejador não são copiadas. Rode o ANALYZE em todos os bancos logo depois da atualização:

sudo -u postgres vacuumdb --all --analyze-in-stages

Se a janela de parada precisa ser mínima, outra opção é montar um servidor novo já na versão nova, copiar os dados com replicação lógica e trocar a aplicação de servidor no final. Dá mais trabalho, mas a parada se resume à troca.

Para auditoria, registre a versão de origem e de destino, a data, o backup usado, o resultado dos testes e o caminho de volta escolhido.

Replicação streaming

Na replicação streaming, um segundo servidor (a réplica) recebe o WAL do servidor principal em tempo real e reaplica as mudanças. A réplica aceita consultas somente leitura e pode ser promovida a principal se o servidor original cair. Nos exemplos, o principal é 192.168.10.10 e a réplica é 192.168.10.11, os dois com a mesma versão maior do PostgreSQL.

No servidor principal

Crie uma role exclusiva para a replicação, sem acesso a nenhum banco:

CREATE ROLE replicador WITH REPLICATION LOGIN PASSWORD 'troque-esta-senha';

Libere a conexão de replicação só para o IP da réplica e só com TLS. A palavra replication no lugar do banco é obrigatória, porque a conexão de replicação não pertence a nenhum banco:

# Debian: /etc/postgresql/17/main/pg_hba.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/pg_hba.conf

hostssl  replication  replicador  192.168.10.11/32  scram-sha-256

Como um slot de replicação faz o principal guardar o WAL até a réplica receber, limite quanto ele pode reter. Sem isso, uma réplica desligada por dias lota o disco do principal:

# Debian: /etc/postgresql/17/main/postgresql.conf

# Rocky/Oracle Linux: /var/lib/pgsql/data/postgresql.conf

max_slot_wal_keep_size = 50GB

O listen_addresses e o firewall na porta 5432 precisam aceitar a réplica, como no cenário 2 do post anterior. Recarregue a configuração:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Na réplica

Instale a mesma versão do PostgreSQL e pare o serviço. No Rocky/Oracle Linux não rode o postgresql-setup --initdb, porque o diretório de dados virá do principal. A senha da role de replicação fica no arquivo .pgpass do postgres, que precisa ter permissão 600:

# Debian: /var/lib/postgresql/.pgpass

# Rocky/Oracle Linux: /var/lib/pgsql/.pgpass

192.168.10.10:5432:replication:replicador:troque-esta-senha

Defina a variável DADOS como na seção de restore e mova o diretório criado pela instalação para o lado (no Debian, o pacote cria um cluster vazio). Depois, copie os dados do principal:

sudo systemctl stop postgresql

sudo mv $DADOS $DADOS.inicial

sudo -u postgres pg_basebackup -h 192.168.10.10 -U replicador -D $DADOS -R -X stream -C -S replica1 -P

O que cada opção faz:

• -R: cria o arquivo standby.signal e grava a conexão com o principal no postgresql.auto.conf, deixando a réplica pronta para iniciar.

• -X stream: copia o WAL gerado durante a cópia, para que os dados fiquem consistentes.

• -C -S replica1: cria no principal um slot de replicação chamado replica1, que garante que o WAL não seja apagado antes de a réplica recebê-lo.

Inicie a réplica:

sudo restorecon -Rv $DADOS      # só no Rocky/Oracle Linux

sudo systemctl start postgresql

Verificar a replicação

No principal, pg_stat_replication mostra cada réplica conectada e o atraso em bytes:

SELECT client_addr, state, sent_lsn, replay_lsn,

       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS atraso

FROM pg_stat_replication;

Na réplica, a primeira coluna deve ser t, e a segunda mostra há quanto tempo foi reaplicada a última transação:

SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS atraso;

No principal, confira também quanto WAL cada slot está segurando:

SELECT slot_name, active,

       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retido

FROM pg_replication_slots;

Se uma réplica for desativada de vez, apague o slot dela, ou ele continua segurando WAL e atrapalhando o VACUUM:

SELECT pg_drop_replication_slot('replica1');

Promover a réplica

Se o principal cair e não houver previsão de volta, promova a réplica. Ela sai do modo de recuperação e passa a aceitar escritas:

sudo -u postgres psql -c "SELECT pg_promote();"

Depois da promoção:

• A aplicação precisa apontar para o novo principal. Troque o DNS ou o IP na configuração, use um IP virtual com o keepalived, ou liste os dois servidores na string de conexão com target_session_attrs=read-write, que a libpq e o PDO aceitam.

• O antigo principal não pode simplesmente voltar como estava. Ele precisa ser reconstruído como réplica do novo principal com pg_basebackup, ou ressincronizado com o pg_rewind.

• Evite ter dois servidores aceitando escritas ao mesmo tempo. Garanta que o antigo principal fique parado até ser reconstruído.

A promoção manual é suficiente para muitos ambientes. Se precisar de failover automático, o Patroni (open source) gerencia a eleição do principal e a reconfiguração das réplicas, normalmente junto com etcd e HAProxy.

Checklist de operação

• Timer pg-health ativo e sem falhas no journal

• log_min_duration_statement configurado e log de consultas lentas revisado

• pg_stat_statements revisado depois de cada deploy grande

• Autovacuum ligado e nenhuma tabela com n_dead_tup crescendo sem parar

• Idade de datfrozenxid estável, sem crescer continuamente

• pg_stat_archiver sem falhas e WAL chegando em /backup/pg/wal

• Backup base diário copiado para fora do servidor

• Restore testado em outro servidor pelo menos uma vez por trimestre, com registro

• Atraso da réplica e WAL retido pelos slots monitorados

• Procedimento de promoção documentado e ensaiado

• Toda mudança de parâmetro registrada com arquivo, valor anterior, valor novo, data e responsável

Para fechar

Com este post a série sobre PostgreSQL fica completa. No primeiro instalamos e aprendemos o básico, no segundo fechamos o acesso e agora temos um servidor monitorado, ajustado, com backups que voltam a qualquer ponto no tempo e uma réplica pronta para assumir.

Nada disso precisa ser implantado de uma vez. Uma boa ordem é começar pelo backup e pelo teste de restore, depois o monitoramento, e só então partir para os ajustes de desempenho e a replicação. Teste cada passo em laboratório antes de levar para produção.

Para saber mais

• Estatísticas e monitoramento

• Rotina de VACUUM

• Backup contínuo e PITR

• Servidores standby e replicação

• Atualizar um cluster

Trilha de PostgreSQL

Esta série tem três posts. Siga na ordem para ir da instalação até um servidor pronto para produção.

1. Gerenciamento básico do PostgreSQL

2. Segurança e acesso ao PostgreSQL

3. Operação e alta disponibilidade do PostgreSQL (você está aqui)