PgBackRest

By valbornoz Sept. 7, 2024, 5:23 p.m.

PGBackRest

PgBackRest es una herramienta de respaldo y restauración diseñada especificamente para PostgreSQL. Ofrece una solución robusta y eficiente para gestionar copias de seguridad y restauraciones de bases de datos, especialmente para entornos donde se requiere alta disponibilidad y consistencia de los datos. PgBackRest ofrece muchas funcionalidades, sin embargo en este manual nos centraremos unicamente en 2 principales:

Automaticación de resutaraciones PITR(Point in Time Recovery): Facilita la recuperacion de la base de datos a un punto especifico en el tiempo, utilizando el registro de transacciones(WAL)
Replicacion: De la misma forma que uno puede restaurar un respaldo para iniciar una instancia, es posible restaurar un respaldo para iniciar una réplica que se sincronice activamente con el primario a través de streaming replication.

Requisitos previos

Para el siguiente ejercicio requerimos 4 mv(Maquinas virtuales) donde realizaremos el escenario completo para realizar restauraciones y replicar información.

Los equipos que requeriremos son los siguientes:

ubuntu-ejemplo-restauracion-1 (Servidor BD Principal - 127.0.0.3): Este servidor sera el que contenga nuestra base de datos principal, es decir simulara la base de datos donde se ingresan registros, se realizan cambios y se realizan consultas.
ubuntu-ejemplo-restauracion-2 (Servidor BD Restaurar - 127.0.0.4): Este servidor sera donde realizaremos la restauración de la base de datos principal, es decir, realizaremos una copia de la base de datos principal y la aplicaremos en este servidor.
ubuntu-ejemplo-restauracion-3 (Servidor PgBackRest - 127.0.0.5): Este servidor tendra instalado PgBackRest y sera aquel que gestione los otros 3 servidores para poder realizar todos los procesos de base de datos.
ubuntu-ejemplo-restauracion-4 (Servidor BD Replica - 127.0.0.6): Este servidor seguira los movimientos de la base de datos principal y las copiara, a diferencia de la restauracion esto no copia toda la base de datos, solo copia las acciones aplicadas para mantener una consistencia entre ambas bases de datos.

Configuraciones iniciales para los servidores

Configurar Firewall

Los servidores deben poder conocerse entre y permitr las conecciones por medio de ssh y por medio del puerto de PostgreSQL, por lo que debe permitirse la coneccion de servidores en la misma red.

Instalar PostgreSQL en su version de postgres 13 con los siguientes comandos:

# Creamos el archivo de configuración del repositorio de postgres
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Obtenemos las llaves del repositorio de postgresql y actualizamos los paquetes de ubuntu
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Realizamos update a la lista de paquetes
sudo apt-get update
# Instalamos la ultima version de postgresql 13
sudo apt-get -y install postgresql-13
# Revisamos la version de postgres default
psql -V  # psql (PostgreSQL) 13.6 (Ubuntu 13.6-1.pgdg18.04+1)

Instalar PbBackRest en su version 2.43 en cada servidor

PGBACKREST_VERSION="2.43"
mkdir -p ~/build
wget -q -O - https://github.com/pgbackrest/pgbackrest/archive/release/${PGBACKREST_VERSION}.tar.gz | tar zx -C ~/build
sudo apt-get install make gcc libpq-dev libssl-dev libxml2-dev pkg-config liblz4-dev libzstd-dev libbz2-dev libz-dev libyaml-dev -y
cd ~/build/pgbackrest-release-${PGBACKREST_VERSION}/src && ./configure && make

Creamos usuario PgBackRest en cada servidor

sudo adduser --disabled-password --gecos "" pgbackrest

Creamos Llaves SSH para usuario Postgres en cada servidor

Para poder interactuar entre los diferentes servidores es necesario realizar conecciones SSH, y por esto mismo demos crear las llaves SSH de cada usuario postgres y pgbackrest.

sudo su - postgres
mkdir -m 750 -p /var/lib/postgresql/.ssh
cd /var/lib/postgresql/.ssh
ssh-keygen -C postgres -t rsa -b 4096 -f id_rsa -N ""
ssh-keygen -y -f id_rsa > id_rsa.pub
touch authorized_keys
chmod 0600 authorized_keys

Creamos Llaves SSH para usuario PgBackRest en cada servidor

sudo su - pgbackrest
mkdir -m 750 /home/pgbackrest/.ssh
cd /home/pgbackrest/.ssh
ssh-keygen -C pgbackrest -t rsa -b 4096 -f id_rsa -N ""
ssh-keygen -y -f id_rsa > id_rsa.pub
touch authorized_keys
chmod 0600 authorized_keys

En cada servidor debemos realizar la Configuracion SSH para Aceptar Usuario Postgres y PgBackRest

sudo nano /etc/ssh/sshd_config

Agregamos lo siguiente al final del archivo:

#AllowUsers <usuario-coneccion-local> postgres pgbackrest
AllowUsers root postgres pgbackrest
PubkeyAuthentication yes
Match User pgbackrest
        PasswordAuthentication no

Nota: Es importante que se agregue el usuario con el cual entras por medio de ssh a las maquinas virtuales (<usuario-coneccion-local>), por defecto este es root, sin embargo, si tienes algun otro con el cual realices conecciones a la vm, debes agregarlas en este apartado o no podras volver entrar a las vm por ese medio.</usuario-coneccion-local>

Despues de realizar los cambios debemos reiniciar el servicio de ssh para aplicar los cambios

sudo systemctl restart sshd.service

En cada servidor es necesario configurar postgres

Es necesario tomar en cuanta lo siguiente, los siguientes parametros deben adaptarse a la capacidad de tu vm, es decir si tu servidor tiene 8GB de RAM debemos asignar un 25% al proceso de postgres, en este caso serian 2GB.

Para este ejemplo las maquinas virtuales tienen las siguientes especificaciones:

2 GB RAM 50 GB de memoria interna

Con esto en cuenta realizamos la siguiente configuracion

cd /etc/postgresql/13/main
sudo nano postgresql.conf

Variable	Valor actual	Valor recomendado(2GB RAM)	Valor recomendado(8GB RAM)
shared_buffers	128MB	512MB	2GB
work_mem	4MB	8MB	8MB
maintenance_work_mem	64MB	512MB	1GB
max_wal_size	1GB	1GB	4GB

# Configuración basica
shared_buffers = 512MB
work_mem = 8MB
maintenance_work_mem = 512MB
max_wal_size = 1GB
# Direcciones aceptadas
listen_addresses = '*'

Reiniciamos postgres en cada servidor

sudo systemctl restart postgresql

Configuración de sistema de respaldos (Backups)

Los siguientes pasos son para generar una conexión de nuestra BD principal(ubuntu-ejemplo-restauracion-1) y el servidor que tiene pgbackrest(ubuntu-ejemplo-restauracion-3). En los siguientes pasos se realizara la configuración para poder crear backups de nuestra bd primaria.

Configuracion en servidor BD principal (ubuntu-ejemplo-restauracion-1)

Debemos crear la carpetas para pgBackRest

PGBACKREST_VERSION="2.43"
cd ~/build/pgbackrest-release-${PGBACKREST_VERSION}/src && ./configure && make
sudo cp pgbackrest /usr/bin
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown -R pgbackrest: /var/log/pgbackrest/
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres: /etc/pgbackrest/pgbackrest.conf
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres: /var/lib/pgbackrest

Configurar hosts

# S3 - PgBackRest
127.0.0.5 ubuntu_ejemplo_restauracion_3_pgbackrest

Agregar Stanza en la configuración de PostgreSQL

sudo nano /etc/postgresql/13/main/postgresql.conf

Agregamos lo siguiente

archive_mode = on
archive_command = 'pgbackrest --stanza=localdev archive-push %p'
restore_command = 'pgbackrest --stanza=localdev archive-get %f "%p"'

Reiniciamos postgres

sudo systemctl restart postgresql

Configuramos PgBackRest

sudo nano /etc/pgbackrest/pgbackrest.conf

Agregamos lo siguiente

[localdev]
pg1-path=/var/lib/postgresql/13/main
db-ssh-port=22
[global]
log-level-file=detail
repo1-host=ubuntu_ejemplo_restauracion_3_pgbackrest
repo1-host-port=22
process-max=2
log-level-console=info

Agregar las llaves ssh de los servidores al archivo authorized_keys

sudo su - postgres
cd .ssh
nano authorized_keys

# S3 PgBackRest(ubuntu-ejemplo-restauracion-3) - Llave ssh del usuario pgbackrest
ssh-rsa XXXXXX.......== pgbackrest

Configuracion en servidor BD PgBackRest (ubuntu-ejemplo-restauracion-3)

Debemos crear la carpetas para pgBackRest

PGBACKREST_VERSION="2.43"
cd ~/build/pgbackrest-release-${PGBACKREST_VERSION}/src && ./configure && make
sudo cp pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown -R pgbackrest: /var/log/pgbackrest/
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown pgbackrest: /etc/pgbackrest/pgbackrest.conf
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown pgbackrest: /var/lib/pgbackrest

Configurar hosts

sudo nano /etc/hosts

Agregar el hosts del servidor BD Principal

# Servidor BD Principal (ubuntu-ejemplo-restauracion-1)
127.0.0.3 ubuntu_ejemplo_restauracion_1_pgbackrest

Realizamos la configuración de PgBackRest

[localdev]
pg1-path=/var/lib/postgresql/13/main/
pg1-host=ubuntu_ejemplo_restauracion_1_pgbackrest
db-ssh-port=22
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=10
start-fast=y
process-max=2
log-level-file=detail
[global:archive-push]
compress-level=3

Agregar las llaves ssh de los servidores al archivo authorized_keys

sudo su - postgres
cd .ssh
nano authorized_keys

# S1 - Base de datos principal(ubuntu-ejemplo-restauracion-1) - Llave ssh del usuario postgres
ssh-rsa XXXXXX......== postgres

Crear Stanza

El stanza es la configuración de un cluster, es decir, de los nodos que componen un data. Se pueden configurar réplicas con nombres de stanza distintos, para restaurar en ambientes distintos. La configuración del stanza actual, tiene la intención de poder levantar una réplica, utilizando todas las piezas de respaldo disponibles (full and incremental). También permite realizar PITR (Point in Time Recovery), es decir, restaurar un respaldo a un punto determinado en el tiempo, para cancelar una operación commiteada anteriormente.

Creamos Stanza en el Servidor BD Principal(ubuntu-ejemplo-restauracion-1)

sudo -u postgres pgbackrest --stanza=localdev stanza-create
sudo -u postgres pgbackrest --stanza=localdev check
sudo -u postgres pgbackrest --stanza=localdev --log-level-console=info check

Revisamos el estado de la stanza en Servidor BD Principal(ubuntu-ejemplo-restauracion-1)

sudo -u postgres pgbackrest --stanza=localdev info

Entramos al Servidor PgBackRest (ubuntu-ejemplo-restauracion-3) y creamos el respaldo (Backup)

Nota: Esto puede tardar dependiendo el volumen de tu base de datos

sudo -u pgbackrest time pgbackrest --stanza=localdev --type=full backup

Recibiremos un mensaje similar como el siguiente:

2.87user 2.78system 0:31.39elapsed 18%CPU (0avgtext+0avgdata 20252maxresident)k
424inputs+67992outputs (3major+36289minor)pagefaults 0swaps

Entramos al Servidor PgBackRest (ubuntu-ejemplo-restauracion-3) y creamos el incremental

sudo -u pgbackrest time pgbackrest --stanza=localdev --type=incr backup

Recibiremos un mensaje similar como el siguiente:

0.50user 0.06system 0:09.45elapsed 6%CPU (0avgtext+0avgdata 20632maxresident)k
152inputs+8344outputs (1major+6920minor)pagefaults 0swaps

Entramos al Servidor BD Principal(ubuntu-ejemplo-restauracion-1) y revisamos si el nuevo bakcup fue correctamente creado y se tiene acceso a el

sudo -u postgres pgbackrest --stanza=localdev info

Recibiremos un mensaje similar como el siguiente:

stanza: localdev
    status: ok
    cipher: none
    db (current)
        wal archive min/max (13): 000000010000000000000001/00000001000000000000001A
        full backup: 20240905-180312F
            timestamp start/stop: 2024-09-05 18:03:12 / 2024-09-05 18:03:22
            wal start/stop: 000000010000000000000006 / 000000010000000000000006
            database size: 30.7MB, database backup size: 30.7MB
            repo1: backup set size: 3.8MB, backup size: 3.8MB
        incr backup: 20240905-180312F_20240905-180409I
            timestamp start/stop: 2024-09-05 18:04:09 / 2024-09-05 18:04:14
            wal start/stop: 000000010000000000000008 / 000000010000000000000008
            database size: 30.7MB, database backup size: 8.3KB
            repo1: backup set size: 3.8MB, backup size: 427B
            backup reference list: 20240905-180312F

Configuración de aplicación de Respaldos

Configuracion en servidor BD PgBackRest (ubuntu-ejemplo-restauracion-3)

Agregar la llave publica a authorized_keys en la sesion del usuario pbbackrest

# S1 - Base de datos principal (ubuntu-ejemplo-restauracion-1)
ssh-rsa XXXXX.....== postgres
# S2 - Restauracion (ubuntu-ejemplo-restauracion-2)
ssh-rsa XXXXX.....== postgres

Configurar hosts

sudo nano /etc/hosts

Agregar el hosts del Servidor 2 el cual sera nuestro servidor donde haremos restore de nuestros de Backups

# Servidor BD Principal
127.0.0.3 ubuntu_ejemplo_restauracion_1_pgbackrest
# Servidor Restaurar
127.0.0.4 ubuntu_ejemplo_restauracion_2_pgbackrest

Configuracion en Servidor BD Restaurar(ubuntu-ejemplo-restauracion-2)

Agregar la llave publica a authorized_keys en la sesion del usuario postgres

sudo su - postgres
cd .ssh
nano authorized_keys

# Servidor PgBackRest (ubuntu-ejemplo-restauracion-3)
ssh-rsa XXXX.......== pgbackrest

Agregar la llave publica a authorized_keys en la sesion del usuario pgbackrest en el servidor

sudo su - pgbackrest
cd .ssh
nano authorized_keys

# Servidor PgBackRest (ubuntu-ejemplo-restauracion-3)
ssh-rsa XXXX.......== pgbackrest

Crear carpetas para pgbackrest

PGBACKREST_VERSION="2.43"
cd ~/build/pgbackrest-release-${PGBACKREST_VERSION}/src && ./configure && make
sudo cp pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown -R pgbackrest: /var/log/pgbackrest/
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres: /etc/pgbackrest/pgbackrest.conf
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres: /var/lib/pgbackrest

Configuramos los hosts

sudo nano /etc/hosts

Agregar el hosts del Servidor 4 el cual sera nuestro servidor de pgbackrest

# S3 - PgBackRest (ubuntu-ejemplo-restauracion-3)
127.0.0.5 ubuntu_ejemplo_restauracion_3_pgbackrest

Configuramos PgBackRest

sudo nano /etc/pgbackrest/pgbackrest.conf

Agregamos lo siguiente

[localdev]
pg1-path=/var/lib/postgresql/13/main
db-ssh-port=22
[global]
log-level-file=detail
repo1-host=ubuntu_ejemplo_restauracion_3_pgbackrest
repo1-host-port=22
repo1-path=/var/lib/pgbackrest
repo1-retention-full=1
start-fast=y
process-max=2
log-level-console=info

Agregar Stanza en Configuracion Postgres

sudo nano /etc/postgresql/13/main/postgresql.conf

Cambiar variables

archive_mode = on
restore_command = 'pgbackrest --stanza=localdev archive-get %f "%p"'
listen_addresses = '*'
#max_wal_senders = 3

Revisamos el estado de la stanza

sudo -u postgres pgbackrest --stanza=localdev info

Realizamos la restauración con los siguientes comandos

# Apagamos postgres
sudo systemctl stop postgresql
# Realizamos la restauracion
time sudo -u postgres pgbackrest --stanza=localdev --delta  --log-level-console=detail --process-max=2 restore
# Apagamos postgres
sudo systemctl start postgresql

Debemos poder visualizar un mensaje de salida similar al siguiente

2024-09-07 15:17:25.740 P00 DETAIL: sync path '/var/lib/postgresql/13/main/global'
2024-09-07 15:17:25.741 P00   INFO: restore size = 78.1MB, file total = 5716
2024-09-07 15:17:25.742 P00   INFO: restore command end: completed successfully (6895ms)
real    0m6.920s
user    0m1.224s
sys 0m0.944s

Configuración de aplicación de Resplicación

En estos pasos mostrareos la forma para configurar un servidor para que funcione como replica de la BD Principal

Configuración del servidor PgBackRest (ubuntu-ejemplo-restauracion-3 Servidor)

Debemos agregar la llave del usuario postgres del servidor 1

sudo su - pgbackrest
cd .ssh
nano authorized_keys

Agregamos lo siguiente

# Servidor BD Replica (ubuntu-ejemplo-restauracion-4)
ssh-rsa XXXXXX.....== postgres

Agregamos el hosts

sudo nano /etc/hosts

Agregamos el host de la mv del Servidor BD Replica(ubuntu-ejemplo-restauracion-4)

# Servidor Restaurar
127.0.0.4 ubuntu_ejemplo_restauracion_2_pgbackrest

Configuración del Servidor BD Principal (ubuntu-ejemplo-restauracion-1)

Crear usuario para la replicación

sudo su - postgres
psql
CREATE USER localrep PASSWORD 'temporal1' REPLICATION;

Para evitar utiliza el password en la cadena de conexion en el pgbackrestconf, se puede agregar la siguiente linea en el archivo .pgpass del home del usuario postgres en todos los servidores.

sudo su - postgres
nano .pgpass

Agregamos lo siguiente

*:*:*:localrep:temporal1

Reiniciamos Postgres

sudo systemctl restart postgresql.service

En el Servidor BD Principal (ubuntu-ejemplo-restauracion-1) es necesario agregar la entrada en el pg_hba.conf para aceptar replication desde el Servidor BD Restaurar (ubuntu-ejemplo-restauracion-2) y la Servidor BD Replica (ubuntu-ejemplo-restauracion-4).

sudo nano /etc/postgresql/13/main/pg_hba.conf

Agregar la ip publica del servidor que tiene el Servidor PgBackRest (ubuntu-ejemplo-restauracion-3) Agregar la ip publica del servidor que sera el Servidor BD Replica (ubuntu-ejemplo-restauracion-4).

# Sever PgBackRest
host    replication     localrep        127.0.0.5/32         md5
# Server replicante - server 4
host    replication     localrep        127.0.0.6/32         md5

Reiniciamos postgres

sudo systemctl restart postgresql

Ahora podemos agregar un registro a la base de datos principal y comprobar que se este replicando correctamente

Nota: El archivo .pgpass debe terner permisos de 0600 o menos en el servidor replicante o no se ejecutara la replicacion