Este projeto aborda a configuração de um ambiente de alta disponibilidade para PostgreSQL, incorporando replicação mestre-escravo, separação de leitura/escrita com Pgpool-II e estratégias de backup e recuperação utilizando pg_dumpall.

Objetivos de Gestão

Backup programado: Realizar backups completos e incrementais, armazenando arquivos em local remoto.
Testes de recuperação: Restaurar backups periodicamente em ambientes de teste para validar a integridade.
Controle de acesso: Implementar permissões estritas para evitar operações indevidas.
Gerenciamento de alterações: Submeter todas as mudanças no banco de dados a aprovação e executá-las em horários de baixa demanda.
Monitoramento e alertas: Acompanhar em tempo real o estado do banco de dados, configurando alertas para detecção e tratamento de problemas.
Sincronização: Monitorar atrasos na replicação entre mestre e escravos para garantir consistência de dados.
Validação de backups: Verificar regularmente a integridade e a capacidade de recuperação dos arquivos de backup.
Segurança: Reforçar proteções contra ataques e vazamentos de dados.

Ambiente

Sistema operacional: Debian 12.8
Planejamento de nós:
Mestre: 192.168.100.1
Escravo 1: 192.168.100.2
Escravo 2: 192.168.100.3
Nó de gerenciamento MHA: 192.168.100.4
Middleware de separação leitura/escrita: 192.168.100.5
Servider de backups: 192.168.100.6
Ambiante de teste: 192.168.100.7
(Considera-se que o SSH com autenticação por chave foi configurado previamente via ssh-copy-id)

Instalação do PostgreSQL por Compilação

Em todos os nós, instalar as dependências necessárias e compilar o PostgreSQL:

sudo apt-get update && sudo apt-get install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev
wget https://ftp.postgresql.org/pub/source/v17.2/postgresql-17.2.tar.gz
tar -zxvf postgresql-17.2.tar.gz && cd postgresql-17.2
./configure --prefix=/opt/pg/install --with-openssl
make && sudo make install
sudo adduser postgres
sudo mkdir -p /opt/pg/data && sudo chown postgres:postgres /opt/pg/data

No nó mestre, inicializar o banco de dados:

sudo -u postgres /opt/pg/bin/initdb -D /opt/pg/data

Iniciar o serviço PostgreSQL:

sudo -u postgres /opt/pg/bin/pg_ctl -D /opt/pg/data -l /var/log/pg.log start

Configuração da Replicação Mestre-Escravo

Nos escravos (192.168.100.2 e 192.168.100.3), parar o serviço e limpar o diretório de dados:

sudo systemctl stop postgresql && sudo rm -rf /var/lib/postgresql/15/main/*

Sincronizar dados do mestre usando pg_basebackup:

pg_basebackup -h 192.168.100.1 -U replica_user -D /var/lib/postgresql/15/main -P -R

Editar o arquivo de configuração /etc/postgresql/15/main/postgresql.conf para habilitar modo de leitura apenas:

hot_standby = on

Reiniciar o serviço nos escravos:

sudo systemctl start postgresql

Verificar o estado da replicação no mestre:

SELECT * FROM pg_stat_replication;

Verificar a recepção de WAL nos escravos:

SELECT * FROM pg_stat_wal_receiver;

Separador de Leitura/Escrita com Pgpool-II

No nó intermediário (192.168.100.5), compilar e instalar Pgpool-II:

sudo apt-get install -y libpq-dev libssl-dev
wget https://www.pgpool.net/download.php?f=pgpool-II-4.5.2.tar.gz
tar -xvf pgpool-II-4.5.2.tar.gz && cd pgpool-II-4.5.2
./configure --prefix=/opt/pgpool/install
make && sudo make install

Configurar /opt/pgpool/etc/pgpool.conf:

listen_addresses = '*'
backend_hostname0 = '192.168.100.1'
backend_port0 = 5432
backend_weight0 = 0
backend_hostname1 = '192.168.100.2'
backend_port1 = 5432
backend_weight1 = 1
backend_hostname2 = '192.168.100.3'
backend_port2 = 5432
backend_weight2 = 1

Iniciar o Pgpool-II:

/opt/pgpool/bin/pgpool -n -D -d > /var/log/pgpool.log 2>&1 &

Testar a separação conectando via Pgpool-II e executando comandos SQL. Operações de escrita devem ser roteadas para o mestre, e leituras para os escravos.

Estratégias de Backup e Recuperação

Backup completo usando pg_dumpall:

pg_dumpall -U postgres -f /mnt/backup/pg_full_$(date +%F).sql

Backup incremental via arquivos de WAL. Configurarr no postgresql.conf:

archive_mode = on
archive_command = 'cp %p /mnt/backup/wal_archive/%f'

Recuperação completa restaurando o arquivo de backup completo. Recuperação incremental aplicando os logs de WAL copiando-os para o diretório pg_wal.

Script automatizado para restauração em ambiente de teste (/usr/local/bin/pg_restore_script.sh):

#!/bin/bash
# Script de restauração para ambiente de teste
SRC_BACKUP="/mnt/backup"
TEST_HOST="192.168.100.7"
TEST_USER="postgres"
LOG="/var/log/pg_restore.log"

log() { echo "$(date '+%F %T') - $1" >> $LOG; }

log "Iniciando restauração completa..."
psql -h $TEST_HOST -U $TEST_USER -f $SRC_BACKUP/pg_full_$(date +%F).sql
if [ $? -ne 0 ]; then log "Erro na restauração completa."; exit 1; fi

log "Aplicando logs de WAL..."
cp $SRC_BACKUP/wal_archive/* /var/lib/postgresql/15/main/pg_wal/
if [ $? -ne 0 ]; then log "Erro ao copiar logs WAL."; exit 1; fi

log "Restauração concluída com sucesso."

Agendar execução diária via cron:

chmod +x /usr/local/bin/pg_restore_script.sh
crontab -e
0 3 * * * /usr/local/bin/pg_restore_script.sh

Procedimentos de Atualização Manual

Fluxo de atualização: Solicitação via sistema de tickets com detalhes da alteração, aprovação por DBA, execução em horário não crítico após backup, verificação e registro. Em caso de falha, executar rollback imediato usando backups.

Regulamentação: Acesso restrito a operadores autorizados, documentação detalhada de todas as mudanças, e treinamento periódico para garantir conformidade e segurança.