Postgresql PITR - Backup Incremental

1-pitr_conf.sh

#!/bin/bash

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                                 #
# Postgresql PITR - Backup Incremental                                            #
#                                                                                 #
# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = #
#                                                                                 #
# Parte 1 - Configuração                                                          #
#                                                                                 #
#     DESCRIÇÃO:                                                                  #
#         AUTOR:                                                                  #
#     CRIADO EM:                                                                  #
#   REVISADO EM:                                                                  #
#                                                                                 #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

export PGPASSWORD=postgres

# --------------------------------------------------------------------------------
# 1 - Definir diretórios dos backups, archivelog/wal e logs
# --------------------------------------------------------------------------------

BACKUP_DIR=/home/projetos/rh/database/backup
BACKUP_FISICO_DIR=$BACKUP_DIR/fisico
SCRIPT_DIR=$BACKUP_DIR/scripts
WAL_DIR=$BACKUP_DIR/wal_archive
LOG_DIR=$BACKUP_DIR/logs

# --------------------------------------------------------------------------------
# 2 - Criar diretórios, caso não existam
# --------------------------------------------------------------------------------

mkdir -p $BACKUP_DIR
mkdir -p $BACKUP_FISICO_DIR
mkdir -p $SCRIPT_DIR
mkdir -p $WAL_DIR
mkdir -p $LOG_DIR

# --------------------------------------------------------------------------------
# 3 - Definir grupo/dono dos diretórios
# --------------------------------------------------------------------------------

# Define o usuário 'postgres' como dono da pasta de destino do backup
chown -R postgres:postgres $BACKUP_DIR

# Define o usuário 'postgres' como dono da pasta de destino do backup físico
chown -R postgres:postgres $BACKUP_FISICO_DIR

# Define o usuário 'postgres' como dono da pasta de script's
chown -R postgres:postgres $SCRIPT_DIR

# Define o usuário 'postgres' como dono da pasta de destino dos arquivos wal (archives)
chown -R postgres:postgres $WAL_DIR

# Define o usuário 'postgres' como dono da pasta de logs
chown -R postgres:postgres $LOG_DIR

# --------------------------------------------------------------------------------
# 4 - Definir permissões nos diretórios
# --------------------------------------------------------------------------------

# Dá permissão total, apenas ao dono do diretório
chmod -R 700 $BACKUP_DIR
# chmod -R 744 $BACKUP_DIR

# --------------------------------------------------------------------------------
# 5 - Alterar o arquivo de configuração do postgresql "postgresql.conf" para habilitar o WAL:
#    Caso não saiba a localização do arquivo de configuração do postgresql "postgresql.conf", use o psql ou um cliente GUI, e rode a query:
#    ```
#    SHOW config_file
#    ```
# --------------------------------------------------------------------------------

# archive_mode = [ on, always ]
psql -h localhost -U postgres --command "ALTER SYSTEM SET archive_mode TO 'on'"

# archive_command = '/scripts/archive.sh %p %f' ou 'rsync %p /wal_archive/%f' ou 'cp %p /wal_archive/%f'
# Indica como e onde os arquivos WALs serão armazenados
# A variável %p simboliza o arquivo de origem e não requer o caminho. Já %f, representa o arquivo de origem, precedido pelo diretório de nossa escolha.
# Em ambiente de produção, o ideal é armazenar estes arquivos em hardware diferente do servidor PostgreSQL principal onde os arquivos são gerados.
psql -h localhost -U postgres --command "ALTER SYSTEM SET archive_command TO 'test ! -f $WAL_DIR/%f && gzip < %p > $WAL_DIR/%f.gz'"

# wal_level = [minimal, replica, logical, "archive, hot_standby"]
psql -h localhost -U postgres --command "ALTER SYSTEM SET wal_level TO 'hot_standby'"

# max_wal_senders = 2
psql -h localhost -U postgres --command "ALTER SYSTEM SET max_wal_senders TO '2'"

# archive_timeout
# Define o intervalo de tempo (em segundos) para geração de wal_archive
# Esta configuração é importante para que o tempo de uma possível perda de dados possa ser limitado a esta quantidade de segundos.
psql -h localhost -U postgres --command "ALTER SYSTEM SET archive_timeout TO '1800'"

### Outras configurações, não sei a importancia e quais valores são interessantes
# hot_standby_feedback = on
# max_standby_archive_delay = -1
# max_standby_streaming_delay = -1
# wal_buffers = 4MB
# wal_writer_delay = 200ms
# wal_sender_timeout = 5000
# wal_receiver_status_interval = 2s
# wal_keep_segments = 32
# synchronous_commit = on

# --------------------------------------------------------------------------------
# 6 - Reiniciar PostgreSQL
# --------------------------------------------------------------------------------

# systemctl restart postgresql-13
/etc/init.d/postgresql restart

# --------------------------------------------------------------------------------
# 7 - Forçar geração do archivelog
# --------------------------------------------------------------------------------

psql -h localhost -U postgres --command "select pg_switch_wal();"

# --------------------------------------------------------------------------------
# 8 - Extras: Verificar configuraçãos dos parâmetros necessarios para uso do PITR
# --------------------------------------------------------------------------------

# postgres=# SHOW archive_command
# psql -h localhost -U postgres --command "SHOW archive_command"

# postgres=# SHOW archive_mode
# psql -h localhost -U postgres --command "SHOW archive_mode"

# postgres=# SHOW max_wal_senders
# psql -h localhost -U postgres --command "SHOW max_wal_senders"

# postgres=# SHOW wal_level
# psql -h localhost -U postgres --command "SHOW wal_level"

# postgres=# SHOW archive_timeout
# psql -h localhost -U postgres --command "SHOW archive_timeout"

2-pitr_backup.sh

#!/bin/bash

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                                 #
# Postgresql PITR - Backup Incremental                                            #
#                                                                                 #
# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = #
#                                                                                 #
# Parte 2 - Backup Físico (cluster)                                               #
#                                                                                 #
#     DESCRIÇÃO:                                                                  #
#         AUTOR:                                                                  #
#     CRIADO EM:                                                                  #
#   REVISADO EM:                                                                  #
#                                                                                 #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

# OBS: Deve ser executado pelo usuário 'postgres'


# --------------------------------------------------------------------------------
# 1 - Diretórios definidos em "1-pitr_conf.sh"
# --------------------------------------------------------------------------------

BACKUP_DIR=/home/projetos/rh/database/backup
BACKUP_FISICO_DIR=$BACKUP_DIR/fisico
SCRIPT_DIR=$BACKUP_DIR/scripts
WAL_DIR=$BACKUP_DIR/wal_archive
LOG_DIR=$BACKUP_DIR/logs

# --------------------------------------------------------------------------------
# 2 - Definir diretório individual do backup, pasta temporária, e nomes para arquivo de log e log de erro
# --------------------------------------------------------------------------------

DATE_TIME=$(date +%Y-%m-%d_%T)
INDIVIDUAL_BACKUP_DIR=$BACKUP_FISICO_DIR/$DATE_TIME
LOG_FILE=backup_fisico_$DATE_TIME.log
LOG_ERROR_FILE=/backup_fisico_$DATE_TIME.err
TEMP_DIR=/tmp/rh/$DATE_TIME

# Criar pasta temporária do backup
mkdir -p $TEMP_DIR

# --------------------------------------------------------------------------------
# 2 - Execução do backup
# --------------------------------------------------------------------------------

# Realiza o backup
# pg_basebackup -Ft -X none --verbose --progress --compress=9 --pgdata $TEMP_DIR 2>>$LOG_DIR/$LOG_FILE 1>>$LOG_DIR/$LOG_ERROR_FILE
pg_basebackup --format=tar --gzip --compress=9 --progress --pgdata $TEMP_DIR 2>>$LOG_DIR/$LOG_FILE 1>>$LOG_DIR/$LOG_ERROR_FILE

# Verifica a execução do backup
if [ $? -eq 0 ]; then
    # Cria pasta de destino final para o backup
    mkdir -p $INDIVIDUAL_BACKUP_DIR
    # Move backup da pasta temporária para a pasta definitiva
    mv $TEMP_DIR/* $INDIVIDUAL_BACKUP_DIR
    # Listar arquivos gerados
    ls -lat $INDIVIDUAL_BACKUP_DIR
    echo "Backup realizado com sucesso!!"
else
    echo "Falha ao realizar backup físico do banco "
fi

# Remove pasta temporária de backup
rm -rf $TEMP_DIR


# --------------------------------------------------------------------------------
# FUTURO
# enviar notificação/log's por email

3-pitr_cleaner.sh

#!/bin/bash

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                                 #
# Postgresql PITR - Backup Incremental                                            #
#                                                                                 #
# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = #
#                                                                                 #
# Parte 3 - Limpeza de backup's, wal files e log's antidos                        #
#                                                                                 #
#     DESCRIÇÃO:                                                                  #
#         AUTOR:                                                                  #
#     CRIADO EM:                                                                  #
#   REVISADO EM:                                                                  #
#                                                                                 #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

4-pitr_restore.sh

#!/bin/bash

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                                 #
# Postgresql PITR - Backup Incremental                                            #
#                                                                                 #
# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = #
#                                                                                 #
# Parte 4 - Restauração                                                           #
#                                                                                 #
#     DESCRIÇÃO:                                                                  #
#         AUTOR:                                                                  #
#     CRIADO EM:                                                                  #
#   REVISADO EM:                                                                  #
#                                                                                 #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #