La pérdida de una base de datos puede suponer un coste económico muy alto para las empresas ya que todo su negocio puede depender de ellos, las principales causas de pérdida de datos son:

• Errores manuales, ejecutando un comando incorrecto.
• Bugs en las aplicaciones.
• Parseo incorrecto de la entrada de datos de los usuarios.
• Hackeos.
• Desastres que afectan físicamente a los servidores como terremotos, incendios o inundaciones.

En este artículo abordaremos distintos métodos de backup de bases de datos PostgreSQL.

El artículo se compone de varios apartados:

• Instalación PostgreSQL
• Métodos de backup
• PGDump
• PGDump: Plain SQL
• PGDump: Custom format
• PGDump: Directory format
• PGDump: Compresión
• PGDump: Estadísticas planificación de querys
• File system level backup
• File system level backup, snapshots
• Rsync backup
• Continuous archiving
• Script port FreeBSD
• pgBaseBackup
• pgBackRest
• pgbarman

Instalación PostgreSQL:

Para nuestras pruebas vamos a necesitar cuatro servidores, dos PostgreSQLs a modo PRIMARY-SECONDARY, un servidor de restore y un servidor de backups, los servidores implicados son los siguientes:

PostgreSQL00-test:      192.168.69.26
PostgreSQL01-test:      192.168.69.27
PostgreSQLRestore-test: 192.168.69.28
PostgreSQLBackups-test: 192.168.69.29

Si estamos instalándolos sobre un sistema basado en jails como es mi caso con Bastille, PostgreSQL dará un error sobre sysvipc:

2023-03-01 19:51:17.310 CET [63048] DETAIL:  Failed system call was shmget(key=65282, size=56, 03600).

Permitimos el acceso por jail, también se puede permitir de forma global en el host padre para todas las jails pero es preferible ser mas granular:

NOTA: No utilicéis allow.sysvipc ya que se considera deprecated y otorga mas permisos de los estrictamente necesarios.

Si vamos a realizar backups a nivel de sistema de ficheros y estamos trabajando con uno que soporte snapshots(ZFS) podemos realizar el backup sin tener que parar el servicio, para ello debemos permitir ciertos parámetros en la jail , también se puede permitir de forma global en el host padre para todas las jails pero es preferible ser mas granular:

NOTA: Cabe destacar que la delegación de datasets ZFS a las jails solo funciona correctamente desde FreeBSD-13.2, anteriormente la delegación era posible pero no el borrado de snapshots desde dentro de la jail.

El parámetro de configuración enforce_statfs ya existe previamente con un valor de 2, así que editamos la configuración y le asignamos un valor de 1.

La delegación de dataset a las jails todavía está un poco bugeada por lo que en cada arranque de la jail debemos reasignar la property jailed del dataset y delegar el dataset a la jail mediante el comando exec.created : ‘zfs set jailed=off zroot/data_PostgreSQL00-test && zfs set jailed=on zroot/data_PostgreSQL00-test && zfs jail PostgreSQL00-test zroot/data_PostgreSQL00-test’.

Si no lo hacemos así en el primer arranque de la jail podremos crear y eliminar snapshots, pero tras un reinicio de la jail solo podremos crearlos, no eliminarlos .

PostgreSQL00-test {
  devfs_ruleset = 4;
  enforce_statfs = 1;
  exec.clean;
  exec.consolelog = /var/log/bastille/PostgreSQL00-test_console.log;
  exec.created = 'zfs set jailed=off zroot/data_PostgreSQL00-test && zfs set jailed=on zroot/data_PostgreSQL00-test && zfs jail PostgreSQL00-test zroot/data_PostgreSQL00-test';
  exec.start = '/bin/sh /etc/rc';
  exec.stop = '/bin/sh /etc/rc.shutdown';
  host.hostname = PostgreSQL00-test;
  mount.devfs;
  mount.fstab = /usr/local/bastille/jails/PostgreSQL00-test/fstab;
  path = /usr/local/bastille/jails/PostgreSQL00-test/root;
  securelevel = 2;

  interface = nfe0;
  ip4.addr = 192.168.69.26/24;
  ip6 = disable;
  sysvmsg=new;
  sysvsem=new;
  sysvshm=new;
  allow.mount=1;
  allow.mount.zfs=1;
}

Repetimos el proceso para PostgreSQL01-test y PostgreSQLRestore-test:

PostgreSQL01-test {
  devfs_ruleset = 4;
  enforce_statfs = 1;
  exec.clean;
  exec.consolelog = /var/log/bastille/PostgreSQL01-test_console.log;
  exec.created = 'zfs set jailed=off zroot/data_PostgreSQL01-test && zfs set jailed=on zroot/data_PostgreSQL01-test && zfs jail PostgreSQL01-test zroot/data_PostgreSQL01-test';
  exec.start = '/bin/sh /etc/rc';
  exec.stop = '/bin/sh /etc/rc.shutdown';
  host.hostname = PostgreSQL01-test;
  mount.devfs;
  mount.fstab = /usr/local/bastille/jails/PostgreSQL01-test/fstab;
  path = /usr/local/bastille/jails/PostgreSQL01-test/root;
  securelevel = 2;

  interface = nfe0;
  ip4.addr = 192.168.69.27/24;
  ip6 = disable;
  sysvmsg=new;
  sysvsem=new;
  sysvshm=new;
  allow.mount=1;
  allow.mount.zfs=1;
}

PostgreSQLRestore-test {
  devfs_ruleset = 4;
  enforce_statfs = 1;
  exec.clean;
  exec.consolelog = /var/log/bastille/PostgreSQLRestore-test_console.log;
  exec.created = 'zfs jail PostgreSQLRestore-test zroot/data_PostgreSQLRestore-test';
  exec.created = 'zfs set jailed=off zroot/data_PostgreSQLRestore-test && zfs set jailed=on zroot/data_PostgreSQLRestore-test && zfs jail PostgreSQLRestore-test zroot/data_PostgreSQLRestore-test';
  exec.start = '/bin/sh /etc/rc';
  exec.stop = '/bin/sh /etc/rc.shutdown';
  host.hostname = PostgreSQLRestore-test;
  mount.devfs;
  mount.fstab = /usr/local/bastille/jails/PostgreSQLRestore-test/fstab;
  path = /usr/local/bastille/jails/PostgreSQLRestore-test/root;
  securelevel = 2;

  interface = nfe0;
  ip4.addr = 192.168.69.28/24;
  ip6 = disable;
  sysvmsg=new;
  sysvsem=new;
  sysvshm=new;
  allow.mount=1;
  allow.mount.zfs=1;
}

Creamos los datasets que pasaremos a las jails, indicamos el punto de montaje donde residen los ficheros de PostgreSQL:

Reiniciamos las jails:

Habilitamos el uso de ZFS dentro de las jails.
PostgreSQL00-test:

PostgreSQL01-test:

PostgreSQLRestore-test:

Reiniciamos para asegurarnos de que todo funciona correctamente:

Si entramos en las jails veremos el dataset:

NAME                           USED  AVAIL     REFER  MOUNTPOINT
zroot                          131G   769G       88K  /zroot
zroot/data_PostgreSQL00-test  16.0M   769G     15.8M  /var/db/postgres/data15

Comprobamos que los datasets estén montados:

zroot/bastille/jails/PostgreSQL00-test/root  /
zroot/data_PostgreSQL00-test    /var/db/postgres/data15

Ahora ya podemos instalar PostgreSQL sin problemas:

Habilitamos el servicio:

Inicializamos la base de datos:

Arrancamos el servicio:

Creamos en PostgreSQL00-test un usuario y una base de datos de prueba(PostgreSQL además de estos usuarios tiene el usuario local postgres que tiene acceso como administrador):

Permitimos el acceso a la base de datos test_db:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         kr0m            192.168.69.0/24         password

Recargamos la configuración:

Ahora dejaremos en una sesión de screen un script que inserte datos cada X tiempo:

#!/usr/local/bin/bash

export PGPASSWORD='PASSWORD'; psql -h '192.168.69.26' -U 'kr0m' -d 'test_db' -c "TRUNCATE test;"

i=0
while true; do
	echo "Inserting i: $i"
	export PGPASSWORD='PASSWORD'; psql -h '192.168.69.26' -U 'kr0m' -d 'test_db' -c "INSERT INTO test (ID) VALUES($i);"
	let i=$i+1
	sleep 1
done

Asignamos los permisos y ejecutamos en la sesión de screen:

Podemos ver como se insertan registros con el comando:

El siguiente paso será configurar PostgreSQL00-test como PRIMARY y PostgreSQL01-test como SECONDARY tal como se indica en el artículo sobre replicación PostgreSQL pero en el fichero pg_hba.conf permitiremos el rango LAN entero, no la ip concreta de los dos servidores, de este modo podremos hacer las pruebas desde cualquier servidor de nuestra red.

El servidor de backups no requiere de PostgreSQL funcionando pero para tener el binario pg_basebackup disponible tendremos que instalar el paquete postgresql15-server:

Métodos de backup

Los backups físicos son mucho más rápidos tanto dumpeando como restaurando.

PgDump

Los backups realizados mediante pg_dump son los mas versátiles ya que permiten paralelismo, un mayor control sobre lo que deseamos backupear/restaurar, una mayor compatibilidad entre versiones de PostgreSQL incluso entre arquitecturas distintas además pg_dump no bloqueará nunca las operaciones en la base de datos en el proceso de backup.

pg_dump es capaz de realizar backups de forma remota ya que se trata de un cliente PostgreSQL estándar, pero el usuario de conexión requierirá de acceso a diferentes tablas, información del sistema, usuarios, grants, etc, así que nosotros en este manual haremos los backups en local con el usuario postgresql para una mayor facilidad.

pg_dump permite extraer los dumps en diferentes formatos, según nuestras necesidades elegiremos un formato u otro.

Normalmente elegiremos:

• Plain SQL si queremos manipular manualmente los datos antes de restaurarlos o importarlos en un sistema distinto a PostgreSQL.
• Custom si necesitamos restaurar de forma parcial una base de datos.
• Directory si queremos dumpear rápidamente una base de datos muy grande ya que soporta paralelismo.
• Cualquiera de los dos anteriores(Custom/Directory) si el tiempo de restauración es crítico o si queremos comprimir los backups de forma automática.
• Tar es una versión capada del formato Directory, así que no se utiliza nunca.

PGDump: Plain SQL

Backup Plain base de datos:
Debemos backupear tanto los usuario/grants como los datos de las bases de datos.

Restore Plain base de datos:
Restauramos, como hemos utilizado el parámetro -C en el dump no es necesario crear la base de datos antes de importar ya que el comando CREATE DATABASE está incluído dentro del propio dump.

Backup Plain completo:

Restore Plain completo:

Backup Plain comprimido:

Backup Plain splitteado:
Si tenemos algún tipo de restricción a nivel de ficheros o de cualquier otro tipo, podemos partir el backup en ficheros de X tamaño:

Para restarurarlo simplemente hacemos un cat de los ficheros:

PGDump: Custom format

La gran ventaja de este formato es que nos permite restaurar las tablas que indiquemos, no es necesario restaurar la base de datos entera.

Podemos consultar la información del backup:

;
; Archive created at 2023-09-03 17:04:08 CEST
;     dbname: test_db
;     TOC Entries: 11
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.4
;     Dumped by pg_dump version: 15.4
;
;
; Selected TOC Entries:
;
214; 1259 16385 TABLE public test postgres
3710; 0 0 ACL public TABLE test postgres
215; 1259 16399 TABLE public test2 postgres
3702; 0 16385 TABLE DATA public test postgres
3703; 0 16399 TABLE DATA public test2 postgres
3559; 2606 16403 CONSTRAINT public test2 test2_pkey postgres
3557; 2606 16389 CONSTRAINT public test test_pkey postgres

En el servidor de restauración restauraremos solo la tabla test de la base de datos test_db en una base de datos nueva llamada test_db_restored.
Para ello primero debemos editar la lista de elementos y dejar solo los relacionados con la tabla test, en este caso es tan sencillo como aplicar un grep excluyendo la tabla test2:

Comprobamos que el contenido sea correcto:

;
; Archive created at 2023-09-03 17:04:08 CEST
;     dbname: test_db
;     TOC Entries: 11
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.4
;     Dumped by pg_dump version: 15.4
;
;
; Selected TOC Entries:
;
214; 1259 16385 TABLE public test postgres
3710; 0 0 ACL public TABLE test postgres
3702; 0 16385 TABLE DATA public test postgres
3557; 2606 16389 CONSTRAINT public test test_pkey postgres

Cargamos los grants, creamos la base de datos donde vamos a restaurar y finalmente los elementos de la lista, el formato custom nos permite restaruar utilizando paralelismo , lo aprovechamos(-j 2):

NOTA: No hemos utilizado el parámetro -C para crear la base de datos de forma automática ya que si lo hiciesemos no podríamos restaurar en una base de datos distinta, es una limitación de pg_restore.

Comprobamos que se hayan restaurado correctamente los datos:

$ psql
psql (15.1)
Type "help" for help.

postgres=# \l
                                                 List of databases
       Name       |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
------------------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres         | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
 template0        | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
                  |          |          |         |         |            |                 | postgres=CTc/postgres
 template1        | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
                  |          |          |         |         |            |                 | postgres=CTc/postgres
 test_db_restored | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
(4 rows)

postgres=# \c test_db_restored
You are now connected to database "test_db_restored" as user "postgres".

test_db_restored=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)

test_db_restored=# SELECT * FROM test ORDER BY id DESC LIMIT 3;
  id   
-------
 50871
 50870
 50869
(3 rows)

Esta forma de operar también nos permite alterar el orden en el que se realizan las operaciones de restauración en la base de datos, según el orden de las líneas del fichero db.list.

pg_dumpall no permite especificar el formato de fichero así que si queremos dumpear todas las bases de datos/tablas tendrá que ser de una en una.

PGDump: Directory format

Este formato nos permite restaurar tablas aisladas como custom format pero además podemos dumpear/restaurar varias tablas de forma simultánea, dumpeamos con dos hilos:

Podemos consultar la información del backup:

;
; Archive created at 2023-09-03 17:15:37 CEST
;     dbname: test_db
;     TOC Entries: 11
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: DIRECTORY
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.4
;     Dumped by pg_dump version: 15.4
;
;
; Selected TOC Entries:
;
214; 1259 16385 TABLE public test postgres
3710; 0 0 ACL public TABLE test postgres
215; 1259 16399 TABLE public test2 postgres
3702; 0 16385 TABLE DATA public test postgres
3703; 0 16399 TABLE DATA public test2 postgres
3559; 2606 16403 CONSTRAINT public test2 test2_pkey postgres
3557; 2606 16389 CONSTRAINT public test test_pkey postgres

Vamos a realizar el mismo restore que con el formato custom, en el servidor de restauración restauraremos solo la tabla test de la base de datos test_db en una base de datos nueva llamada test_db_restored:

;
; Archive created at 2023-09-03 17:15:37 CEST
;     dbname: test_db
;     TOC Entries: 11
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: DIRECTORY
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.4
;     Dumped by pg_dump version: 15.4
;
;
; Selected TOC Entries:
;
214; 1259 16385 TABLE public test postgres
3710; 0 0 ACL public TABLE test postgres
3702; 0 16385 TABLE DATA public test postgres
3557; 2606 16389 CONSTRAINT public test test_pkey postgres

Cargamos los grants, creamos la base de datos donde vamos a restaurar y finalmente los elementos de la lista utilizando dos hilos para ello:

NOTA: No hemos utilizado el parámetro -C para crear la base de datos de forma automática ya que si lo hiciesemos no podríamos restaurar en una base de datos distinta, es una limitación de pg_restore.

Comprobamos que se hayan restaurado correctamente los datos:

$ psql
psql (15.1)
Type "help" for help.

postgres=# \l
                                                 List of databases
       Name       |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
------------------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres         | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
 template0        | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
                  |          |          |         |         |            |                 | postgres=CTc/postgres
 template1        | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
                  |          |          |         |         |            |                 | postgres=CTc/postgres
 test_db_restored | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
(4 rows)


postgres=# \c test_db_restored
You are now connected to database "test_db_restored" as user "postgres".

test_db_restored=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)

test_db_restored=# SELECT * FROM test ORDER BY id DESC LIMIT 3;
  id   
-------
 57008
 57007
 57006
(3 rows)

pg_dumpall no permite especificar el formato de fichero así que si queremos dumpear todas las bases de datos/tablas tendrá que ser de una en una.

PGDump: Compresión

La compresión funcionará si el servidor fué compilado con soporte para ello, en FreeBSD viene por defecto con LZ4 y ZSTD:

postgresql15-server-15.4
Name           : postgresql15-server
Version        : 15.4
Installed on   : Sun Sep  3 13:52:23 2023 CEST
Origin         : databases/postgresql15-server
Architecture   : FreeBSD:13:amd64
Prefix         : /usr/local
Categories     : databases
Licenses       : PostgreSQL
Maintainer     : pgsql@FreeBSD.org
WWW            : https://www.postgresql.org/
Comment        : PostgreSQL is the most advanced open-source database available anywhere
Options        :
	DEBUG          : off
	DOCS           : on
	DTRACE         : off
	GSSAPI         : off
	INTDATE        : on
	LDAP           : off
	LLVM           : on
	LZ4            : on
	NLS            : on
	OPTIMIZED_CFLAGS: off
	PAM            : off
	SSL            : on
	TZDATA         : off
	XML            : off
	ZSTD           : on
Shared Libs required:
	libzstd.so.1
	libpq.so.5
	liblz4.so.1
	libintl.so.8
	libicuuc.so.73
	libicui18n.so.73
	libLLVM-15.so

La compresión solo está soportada en los formatos custom y directory, tenemos a nuestro alcance varios niveles de compresión del 0-9.

Para formato custom sería:

 53K	dumpfile

 13K	dumpfile2

Para formato directory:

 58K	dump.dir
 58K	total

 14K	dump.dir
 14K	total

Estadísticas planificación de querys

Si hemos sacado el backup mediante pg_dump debemos tener en cuenta que NO contendrá las estadísticas utilizadas por el optimizer para planificar la ejecución de las querys, por lo tanto debemos ejecutar un ANALYZE en cada una de las bases de datos restauradas para asegurar un rendimiento óptimo.

NOTA: ANALYZE también permite ejecutar el análisis de forma mas granular por tabla.

File system level backup

Este tipo de backups tienen el inconveniente de que hay que parar el servicio tanto cuando se realiza el backup como cuando se restaura a no ser que se disponga de un sistema de ficheros que soporte snapshots, además no soporta el backup/restore parcial, se backupea todo y se restaura todo.
En cuanto a los tiempos de backup, realizar el backup a nivel de filesystem es muy rápido pero también ocupará mas espacio en disco que el resto de métodos.

Realizamos el backup:

Restauramos el backup:

File system level backup, snapshots

Este tipo de backups se pueden realizar sin parar el servicio si el sistema de ficheros soporta snapshots, pero al restaurar la base de datos creerá que se paró de forma abrupta, teniendo que aplicar las querys pendientes del WAL, esto puede tardar.

NAME                                  USED  AVAIL     REFER  MOUNTPOINT
zroot/data_PostgreSQL00-test@backup   112K      -     15.8M  -

Podemos ver el contenido del snapshot:

total 9
dr-xr-xr-x+  3 root      wheel      3 Sep  4 10:25 .
dr-xr-xr-x+  3 root      wheel      3 Sep  3 13:46 ..
drwx------  19 postgres  postgres  26 Sep  4 10:24 backup

Empaquetamos el contenido del snapshot:

Destruímos el snapshot:

Restauramos el backup en el servidor de restauración, tan solo debemos parar el servicio y eliminar cualquier fichero previo:

Luego descomprimir el contenido del backup y arrancar el servicio:

Comprobamos que haya registros:

  id   
-------
 13996
 13995
 13994
(3 rows)

Rsync backup

La idea es realizar un rsync inicial con la base de datos origen en marcha para posteriormente ejecutar un segundo rsync --checksum con la base de datos origen parada.

Permitimos la pubkey de psql del servidor origen en el usuario psql del destino.

Paramos en el destino la base de datos y eliminamos cualquier fichero previo:

Realizamos el rsync inicial en origen:

Ahora el rsync final también en origen:

En el servidor receptor tan solo queda arrancar la base de datos:

Continuous archiving

PostgreSQL genera ficheros WAL como resultado de las operaciones pendientes en la base de datos, si se produce un apagado abrupto, se leen las peticiones y se aplican.

Podemos utilizar estos ficheros para realizar backups incrementales, es decir sacar un backup base y luego ir aplicando ficheros WAL, de este modo podemos backupear bases de datos muy grandes ya que solo vamos a ir copiando los ficheros WAL, no la base de datos entera.

Otra ventaja de este tipo de backups es que no es necesario un backup inicial consistente, es decir podemos copiar los ficheros en caliente sin tener que parar la base de datos, tan solo deberemos aplicar los ficheros WAL como si de un apagado inesperado se tratase.

Además como los ficheros WAL se pueden aplicar hasta X fichero e Y posición podemos tener un sistema de restauración Point-in-Time Recovery (PITR).

El mayor problema que presenta este sistema es la criticidad de la correcta monitorización del archivado de los ficheros generados ya que si se pierde uno de los ficheros WAL intermedio, se romperá la cadena de restauración hasta ese punto en concreto.

PostgreSQL permite especificar que comando o librería debemos ejecutar cuando un fichero WAL está lleno, de esta manera podemos archivarlo a modo de backup antes de que este sea reciclado, los pasos para configurarlo son los siguientes:

• Habilitar el wal_level >= replica
• Habilitar el archive_mode = on
• Especificar el archive_command o archive_library

El archive_command contempla varios parámetros:

• %p: El path del fichero a archivar, este es relativo al data directory de PostgreSQL
• %f: Solo el nombre del fichero a archivar.

NOTA: Si queremos utilizar el carácter % en el comando, debemos escaparlo utilizando %%.

El comando de archivado puede ser cualquiera, es decir podemos hacer un scp, copiar el fichero mediante NFS/SMB o incluso a una unidad de GDrive.

# Copy the file to a safe location (like a mounted NFS volume)
archive_command = 'cp %p /mnt/nfs/%f'

# Not overwriting files is a good practice
archive_command = 'test ! -f /mnt/nfs/%f && cp %p /mnt/nfs/%f'

# Copy to S3 bucket
archive_command = 's3cmd put %p s3://BUCKET/path/%f'

# Copy to Google Cloud bucket
archive_command = 'gsutil cp %p gs://BUCKET/path/%f'

# An external script
archive_command = '/opt/scripts/archive_wal %p'

Tan solo debemos asegurarnos de que los ficheros terminan con los permisos correctos para evitar miradas indiscretas ya que estos ficheros contienen los datos de la base de datos y sería el equivalente a darle acceso a la base de datos directamente.

Otro aspecto importante es que el comando de archivado solo debe retornar 0 cuando haya terminado satisfactoriamente ya que en tal caso PostgreSQL asumirá que se ha archivado correctamente y el fichero será eliminado o reutilizado. Por otro lado si el comando de archivado no retorna 0 PostgreSQL asumirá que no se archivó correctamente y seguirá intentándolo periódicament hasta conseguirlo.

Habilitamos el archivado:
En mi caso voy a realizar una copia vía SCP, siempre se debe comprobar la existencia del fichero para evitar posibles problemas de sobreescritura accidental:

#!/usr/local/bin/bash
function sendTelegram {
    message=${@:1}
    curl -s -X POST https://api.telegram.org/botAPI_KEY/sendMessage -d chat_id=CHAT_ID -d text="$message"
}

WAL_FILE=$1
WAL_DIR="/home/postgres/WAL_FILES/$HOSTNAME"
WAL_SERVER=PostgreSQLBackups-test

N=$(/usr/bin/ssh $WAL_SERVER "/bin/ls -l $WAL_DIR/$WAL_FILE 2>/dev/null | /usr/bin/wc -l")
if [ $N -eq 0 ]; then
  /usr/bin/scp $WAL_FILE $WAL_SERVER:$WAL_DIR
else
  sendTelegram $HOSTNAME WAL: $WAL_FILE already exists in $WAL_SERVER:$WAL_DIR
fi

NOTA: El usuario postgresql debe tener acceso SSH por pubkeys en PostgreSQLBackups-test, además accederemos manualmente para aceptar la pubkey remota.

Asignamos los permisos necesarios:

Configuramos PostgreSQL:

wal_level = replica
archive_mode = on
archive_command = '/var/db/postgres/WALArchive.sh %p'

Reiniciamos el servicio:

Creamos en PostgreSQLBackups-test el usuario de acceso SSH desde los servidores PostgreSQL:

Creamos el directorio en el servidor destino: PostgreSQLBackups-test

Podemos ver como vamos recibiendo los ficheros WAL en destino:

total 4814
drwxr-xr-x  2 postgres  postgres         4 Sep  5 08:06 .
drwxr-xr-x  3 postgres  postgres         3 Sep  4 15:15 ..
-rw-------  1 postgres  postgres  16777216 Sep  5 08:06 000000010000000000000003
-rw-------  1 postgres  postgres  16777216 Sep  5 08:06 000000010000000000000004

Debemos vigilar el uso de disco tanto en el servidor receptor de los ficheros WAL como en el propio servidor PostgreSQL ya que si el comando de archivado falla, los ficheros WAL se almacenarán en local pudiendo dejar fuera de servicio el PostgreSQL hasta que se libere espacio.

También debemos tener en cuenta el tiempo que el comando de archivado precisa, ya que debe ser menor al ritmo con el que se generan ficheros WAL.

Si nuestra base de datos no tiene mucha actividad, los ficheros WAL se archivarán a un ritmo demasiado lento ya que pasará mucho tiempo entre archivado y archivado, para evitar esto podemos configurar el parámetro archive_timeout(segundos) que nos permitirá archivar los ficheros incluso sin haber llegado al tamaño límite, esto por contra tiene el inconveniente de que esté lleno o vacío el fichero WAL, ocupará lo mismo, desperdiciando espacio en el servidor receptor.

archive_timeout = 60

Podemos comprobar como recibimos un fichero cada minuto desde que hicimos el cambio a excepción del primero 000000010000000000000006 que ha tardado 5m por alguna razón desconocida:

total 4924
drwxr-xr-x  2 postgres  postgres         8 Sep  5 08:15 .
drwxr-xr-x  3 postgres  postgres         3 Sep  4 15:15 ..
-rw-------  1 postgres  postgres  16777216 Sep  5 08:06 000000010000000000000003
-rw-------  1 postgres  postgres  16777216 Sep  5 08:06 000000010000000000000004
-rw-------  1 postgres  postgres  16777216 Sep  5 08:08 000000010000000000000005
-rw-------  1 postgres  postgres  16777216 Sep  5 08:13 000000010000000000000006
-rw-------  1 postgres  postgres  16777216 Sep  5 08:14 000000010000000000000007
-rw-------  1 postgres  postgres  16777216 Sep  5 08:15 000000010000000000000008

Si queremos pausar temporalmente el archivado tan solo debemos configurar el archive_command = '', de este modo los ficheros WAL se irán acumulando en /var/db/postgres/data15/pg_wal/ hasta que volvamos a configurar un comando válido.

Realizamos el backup base en el servidor PostgreSQL y consultamos el último registro insertado en la base de datos para tener una referencia del estado de la base de datos en el momento de tomar el backup:

  id   
-------
 32190
(1 row)

NOTA: El backup base se ha realizado mediante pg_basebackup pero podría haberse dumpeado mediante cualquiera de los métodos disponibles.

Copiamos el backup base al servidor de backups:

Si nos fijamos podemos ver en el servidor de backups un fichero generado cuando se realizó el backup, en mi caso estuve probando varias veces por ese motivo vemos 2 backups, el que nos interesa siempre será el último ya que es el mas reciente:

total 5062
drwxr-xr-x   3 postgres  postgres        19 Sep  5 08:20 .
drwxr-xr-x   3 postgres  postgres         3 Sep  4 15:15 ..
-rw-------   1 postgres  postgres  16777216 Sep  5 08:06 000000010000000000000003
-rw-------   1 postgres  postgres  16777216 Sep  5 08:06 000000010000000000000004
-rw-------   1 postgres  postgres  16777216 Sep  5 08:08 000000010000000000000005
-rw-------   1 postgres  postgres  16777216 Sep  5 08:13 000000010000000000000006
-rw-------   1 postgres  postgres  16777216 Sep  5 08:14 000000010000000000000007
-rw-------   1 postgres  postgres  16777216 Sep  5 08:15 000000010000000000000008
-rw-------   1 postgres  postgres  16777216 Sep  5 08:16 000000010000000000000009
-rw-------   1 postgres  postgres  16777216 Sep  5 08:16 00000001000000000000000A
-rw-------   1 postgres  postgres       340 Sep  5 08:16 00000001000000000000000A.00000028.backup
-rw-------   1 postgres  postgres  16777216 Sep  5 08:17 00000001000000000000000B
-rw-------   1 postgres  postgres  16777216 Sep  5 08:18 00000001000000000000000C
-rw-------   1 postgres  postgres  16777216 Sep  5 08:19 00000001000000000000000D
-rw-------   1 postgres  postgres  16777216 Sep  5 08:20 00000001000000000000000E
-rw-------   1 postgres  postgres  16777216 Sep  5 08:20 00000001000000000000000F
-rw-------   1 postgres  postgres  16777216 Sep  5 08:20 000000010000000000000010
-rw-------   1 postgres  postgres       343 Sep  5 08:20 000000010000000000000010.000000D0.backup
drwxr-xr-x  19 postgres  postgres        26 Sep  5 08:20 BACKUP

Donde podemos consultar datos sobre el backup, esto resultará útil para saber que ficheros WAL debemos copiar al servidor de restauración, no es necesario llevarlos todos, solo los generados desde el último backup base hasta la fecha donde queremos restaurar:

START WAL LOCATION: 0/100000D0 (file 000000010000000000000010)
STOP WAL LOCATION: 0/100001A8 (file 000000010000000000000010)
CHECKPOINT LOCATION: 0/10000108
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-09-05 08:20:10 CEST
LABEL: pg_basebackup base backup
START TIMELINE: 1
STOP TIME: 2023-09-05 08:20:11 CEST
STOP TIMELINE: 1

Esperamos un rato a que se generen nuevos ficheros WAL desde que sacamos el backup base, en el servidor PostgreSQL obtenemos la fecha y el último ID insertado en la base de datos, para cuando restauremos a este PIT poder comprobar que los registros restaurados coinciden con los que había en esa fecha en concreto.

Tue Sep  5 08:24:10 CEST 2023
  id   
-------
 32421
(1 row)

Por lo tanto los registros en la base de datos cuando sacamos el backup base y a Sep 5 08:24:10 CEST 2023 eran:

- Base backup:                32190
- Sep  5 08:24:10 CEST 2023:  32421

Copiamos desde el servidor de backups el backup base y los fichero WAL al servidor de restauración: PostgreSQLRestore-test

Paramos el servicio y eliminamos ficheros previos:

Restauramos el backup base:

Deshabilitamos el archivado:

archive_mode = off
# archive_command = '/var/db/postgres/WALArchive.sh %p'
# archive_timeout = 60

NOTA: Hay manuales antiguos donde comentan un tal recovery.conf pero se trata de versiones antiguas de PostgreSQL.

Configuramos el comando a ejecutar para leer los ficheros WAL:

restore_command = 'cp /var/db/postgres/PostgreSQL00-test/%f %p'

Para hacerlo mas interesante no vamos a aplicar todos los ficheros WAL si no hasta una fecha determinada, ‘Sep 5 08:24:10 CEST 2023’, recordemos que a esa fecha el último ID insertado en la base de datos era 32421.
El formato es el siguiente:

recovery_target_time = 'yyyy-mm-dd hh:mm:ss.sss'

Así que nosotros lo indicaremos del siguiente modo:

recovery_target_time = '2023-09-05 08:24:10'

Habilitamos el modo recovery:

Arrancamos el servicio:

Aunque salgan errores sobre ficheros .history, no es realmente un problema , PostgreSQL ha arrancado correctamente:

2023-09-05 08:33:38.217 CEST [43079] LOG:  ending log output to stderr
2023-09-05 08:33:38.217 CEST [43079] HINT:  Future log output will go to log destination "syslog".
cp: /var/db/postgres/PostgreSQL00-test/00000002.history: No such file or directory

Comprobamos el último ID en base de datos, tras la restauración:

  id   
-------
 32421
(1 row)

NOTA: Si no coincidiese exactamente podría ser debido a las décimas de segundo del parámetro recovery_target_time, si lo ajustásemos con decimales si que obtendríamos el ID exacto.

Cuando el servidor haya recuperado hasta el punto indicado este quedará en pausa, si el punto restaurado no es el deseado debemos modificar el punto de restauración y reiniciar el servicio.

recovery_target_time = 'yyyy-mm-dd hh:mm:ss.sss'

Si el punto de restauración si que era correcto deshabilitamos el modo recovery y eliminamos los parámetros restore_command y recovery_target_time:

# restore_command
# recovery_target_time

Si pasa mucho tiempo desde el backup inicial, el aplicar los ficheros de WAL puede llevar mucho tiempo, por lo que puede valer la pena realizar otro backup base cada X días.

Script port FreeBSD(pg_dump)

El propio paquete de PostgreSQL instala un script que es capaz de realizar periódicamente un vacuum y un backup de nuestras bases de datos:

The port is set up to use autovacuum for new databases, but you might also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that you may find useful. You can use it to backup and perform vacuum on all
databases nightly. Per default, it performs `vacuum analyze'. See the script for instructions. For autovacuum settings, please review
~postgres/data/postgresql.conf.

Podemos ver las instrucciones para habilitarlo en los comentarios del propio script, además de los argumentos que podemos pasar al comando de Vacuum y Backup, el directorio de backups y la retención de estos:

# Maintenance shell script to vacuum and backup database
# Put this in /usr/local/etc/periodic/daily, and it will be run
# every night

# Define these variables in either /etc/periodic.conf or
# /etc/periodic.conf.local to override the default values.
#
# daily_pgsql_backup_enable="YES" # do backup of all databases
# daily_pgsql_backup_enable="foo bar db1 db2" # only do backup of a limited selection of databases
# daily_pgsql_vacuum_enable="YES" # do vacuum

# If there is a global system configuration file, suck it in.
#
if [ -r /etc/defaults/periodic.conf ]
then
    . /etc/defaults/periodic.conf
    source_periodic_confs
fi

: ${daily_pgsql_user:="postgres"}
: ${daily_pgsql_port:=5432}
: ${daily_pgsql_vacuum_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -qaz"}
: ${daily_pgsql_pgdump_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -bF c"}
: ${daily_pgsql_pgdumpall_globals_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port}"}
# backupdir is relative to ~pgsql home directory unless it begins with a slash:
: ${daily_pgsql_backupdir:="~${daily_pgsql_user}/backups"}
: ${daily_pgsql_savedays:="7"}

# allow '~' in directory name
eval backupdir=${daily_pgsql_backupdir}

rc=0

pgsql_backup() {
	# daily_pgsql_backupdir must be writeable by user postgres
	# ~postgres is just that under normal circumstances,
	# but this might not be where you want the backups...
	if [ ! -d ${backupdir} ] ; then
	    echo Creating ${backupdir}
	    mkdir -m 700 ${backupdir}; chown ${daily_pgsql_user} ${backupdir}
	fi

	echo
	echo "PostgreSQL backups"

	# Protect the data
	umask 077
	rc=$?
	now=`date "+%Y-%m-%dT%H:%M:%S"`
	file=${daily_pgsql_backupdir}/pgglobals_${now}
	su -l ${daily_pgsql_user} -c \
		"umask 077; pg_dumpall -g ${daily_pgsql_pgdumpall_globals_args} | gzip -9 > ${file}.gz"

	db=$1
	while shift; do
	    echo -n " $db"
	    file=${backupdir}/pgdump_${db}_${now}
	    su -l ${daily_pgsql_user} -c "umask 077; pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}"
	    [ $? -gt 0 ] && rc=3
		db=$1
	done

	if [ $rc -gt 0 ]; then
	    echo
	    echo "Errors were reported during backup."
	fi

	# cleaning up old data
	find ${backupdir} \( -name 'pgdump_*' -o -name 'pgglobals_*' -o -name '*.dat.gz' -o -name 'toc.dat' \) \
	    -a -mtime +${daily_pgsql_savedays} -delete
	echo
}

case "$daily_pgsql_backup_enable" in
    [Yy][Ee][Ss])
	dbnames=`su -l ${daily_pgsql_user} -c "umask 077; psql -U ${daily_pgsql_user} -p ${daily_pgsql_port} -q -t -A -d template1 -c SELECT\ datname\ FROM\ pg_database\ WHERE\ datname!=\'template0\'"`
	pgsql_backup $dbnames
	;;

	[Nn][Oo])
	;;

	"")
	;;

	*)
	pgsql_backup $daily_pgsql_backup_enable
	;;
esac

case "$daily_pgsql_vacuum_enable" in
    [Yy][Ee][Ss])

	echo
	echo "PostgreSQL vacuum"
	su -l ${daily_pgsql_user} -c "vacuumdb ${daily_pgsql_vacuum_args}"
	if [ $? -gt 0 ]
	then
	    echo
	    echo "Errors were reported during vacuum."
	    rc=3
	fi
	;;
esac

exit $rc

El funcionamiento es muy simple:

• Si las variables “daily_pgsql_*” están definidas en el fichero de periodic, se utilizarán dichos valores, en caso contrario los valores por defecto definidos en el script.
• Si la variable daily_pgsql_backup_enable tiene el valor “[Yy][Ee][Ss]”, se hace backup de los global objects y de todas las bases de datos excepto template0.
• Si la variable daily_pgsql_backup_enable tiene el valor distinto a “[Yy][Ee][Ss]” y no es vacío, se hace backup de los global objects y de dichas bases de datos.
• En cuanto al Vacuum el proceso es mucho mas sencillo, si daily_pgsql_vacuum_enable tiene el valor “[Yy][Ee][Ss]”, se realiza el Vacuum.

Comprobamos que el script de periodic /daily/502.pgsql esté instalado:

total 23
drwxr-xr-x  2 root  wheel     5 Aug 28 21:29 .
drwxr-xr-x  5 root  wheel     5 Aug 28 21:24 ..
-rwxr-xr-x  1 root  wheel  2468 Aug  5 03:07 411.pkg-backup
-rwxr-xr-x  1 root  wheel  1954 Aug  5 03:07 490.status-pkg-changes
-r-xr-xr-x  1 root  wheel  3085 Aug 12 03:29 502.pgsql

Habilitamos el Vacuum y el Backup, en mi caso prefiero sacar el backup en formato directory así que retoco la variable daily_pgsql_pgdump_args:

daily_pgsql_backup_enable="YES" # do backup of all databases
daily_pgsql_pgdump_args="-U postgres -p 5432 -b -Fd"
daily_pgsql_vacuum_enable="YES" # do vacuum

Podemos comprobar que el backup se realizó correctamente:

Sep  6 03:01:54 PostgreSQL00-test /usr/sbin/cron[29039]: (root) CMD (periodic daily)

total 23
drwx------  5 postgres  postgres    6 Sep  6 03:04 .
drwxr-xr-x  6 postgres  postgres   10 Sep  6 03:04 ..
drwx------  2 postgres  postgres    3 Sep  6 03:04 pgdump_postgres_2023-09-06T03:04:28
drwx------  2 postgres  postgres    3 Sep  6 03:04 pgdump_template1_2023-09-06T03:04:28
drwx------  2 postgres  postgres    4 Sep  6 03:04 pgdump_test_db_2023-09-06T03:04:28
-rw-------  1 postgres  postgres  532 Sep  6 03:04 pgglobals_2023-09-06T03:04:28.gz

Este script resulta realmente útil cuando el directorio destino del backup está importado por SSHFS/SAMBA/NFS o similar, ya que un backup local en el mismo servidor PostgreSQL no es muy recomendable.

Una manera de debugear el script sin tener que esperar 24h a que periodic lo ejecute es acerlo nosotros mismos manualmente:

pgBaseBackup

Este es el sistema utilizado por la replicación binaria y como base para los backups mediante Continuous archiving .

El backup se realiza mediante una conexión a la base de datos por el protocolo de replicación, por defecto en FreeBSD el wall_level de PostgreSQL ya viene configurado con el nivel replica:

 wal_level 
-----------
 replica
(1 row)

El usuario que conecta debe tener permisos de REPLICATION o SUPERUSER y el fichero pg_hba.conf estar configurado acorde a este usuario, para backups locales no hace falta realizar ninguna configuración en dicho fichero, pero para backups remotos debemos crear y configurar el usuario de replicación.

postgres=# CREATE USER rep_user WITH REPLICATION ENCRYPTED PASSWORD 'POSTGRE_REP_PASS';
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 kr0m      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rep_user  | Replication                                                | {}
postgres=# ALTER ROLE rep_user CONNECTION LIMIT -1;

host    replication     rep_user     SECONDARY_IP/0          md5

Aplicamos la configuración:

pg_basebackup soporta tanto backups locales como remotos además de un amplio abanico de opciones:
-F: Formato de salida, p: Plain, los ficheros en crudo, t: Tar, los ficheros empaquetados en formato tar.
-Z: Tipo de compresión, en mi caso zstd nivel 4 utilizando 2 workers .
-R: Guarda en el backup información útil para montar un SECONDARY desde este backup.
-c: El backup puede realizarse cuando PostgreSQL crea un “checkpoint” lo cual puede llevar su tiempo o de inmediato.
-l: Simplemente indicamos una etiqueta del backup.
-P: Muestra el progreso.
-D: Donde debe escribir el backup extraído.
-X: Los ficheros WAL de las operaciones realizadas en la base de datos mientras sacamos el backup pueden ser backupeados de tres maneras:

• n: none, no se backupean.
• f: fetch, se copiarán al final del backup pero el servidor debe tener un wal_keep_size bastante alto para mantenerlos mientras se realiza el backup.
• s: stream, se realiza una segunda conexión a la base de datos para ir streameando las operaciones en tiempo real.

BACKUP LOCAL:

Realizamos backup en el servidor PostgreSQL.

Comprobamos que haya generado:

• Un fichero manifest: backup_manifest.
• El backup en sí mismo: base.tar.zst.
• Los ficheros WAL: pg_wal.tar.

total 4123
drwxr-xr-x  2 postgres  postgres         5 Sep  6 08:13 .
drwx------  3 postgres  postgres         3 Sep  6 08:13 ..
-rw-------  1 postgres  postgres    181554 Sep  6 08:13 backup_manifest
-rw-------  1 postgres  postgres   4159225 Sep  6 08:13 base.tar.zst
-rw-------  1 postgres  postgres  16778752 Sep  6 08:13 pg_wal.tar

Restauramos en el servidor de restore, para ello primero copiamos el backup a este último:

Comprobamos que haya datos cargados:

   id   
--------
 115554
(1 row)

BACKUP REMOTO:

Realizamos el backup desde el servidor de backups:

Comprobamos el resultado del backup:

total 4139
drwxr-xr-x  2 postgres  postgres         5 Sep  6 08:35 .
drwxr-xr-x  3 root      postgres         3 Sep  6 08:35 ..
-rw-------  1 postgres  postgres    181554 Sep  6 08:35 backup_manifest
-rw-------  1 postgres  postgres   4170480 Sep  6 08:35 base.tar.zst
-rw-------  1 postgres  postgres  16779264 Sep  6 08:35 pg_wal.tar

Restauramos el backup en el servidor de restore, para ello primero copiamos el backup a este último:

Comprobamos que haya datos cargados:

   id   
--------
 116994
(1 row)

pg_basebackup permite realizar el backup remoto cargándolo directamente en un PostgreSQL nuevo como hicimos en el artículo sobre replicación .

pgBackRest

pgBackRest es un software de backups especializado en PostgreSQL, este presenta multitud de opciones como backup y restauración en paralelo, la posibilidad de emplear varios repositorios con distintas políticas de retención, backups diferenciales e incrementales, streaming, compresión, cifrado, backends de almacenamiento comoS3, Azure o GCS y soporte para un amplio número de versiones de PotgreSQL.

En FreeBSD Quaterly(02/09/2023) hay paquete pero nos limita a la versión 2.46 y este a la vez a PostgreSQL13.

Así que vamos a compilar a mano , es recomendable tener un servidor de compilación donde ir actualizando versiones y desde donde copiar el binario al resto de servidores tanto PostgreSQLs/ RepositoryServer /RestoreServers, eso si debemos tener las dependencias de compilación instaladas en todos los servidores para que la ejecución del binario no dé problemas.

Instalamos las dependencias tanto en el servidor de compilación como en los servidores ya sean PostgreSQLs/RepositoryServer(servidor de backups)/RestoreServers:

Consultamos la última release , bajamos el código fuente y compilamos en el servidor de compilación:

Podemos ver el binario:

-rwxr-xr-x  1 root  wheel  977944 Sep  6 08:42 pgbackrest

Lo instalamos en todos los servidores:

-rwxr-xr-x  1 root  wheel  977944 Sep  6 08:47 /usr/local/bin/pgbackrest

Comprobamos que no dé ningún error ejecutándo el binario manualmente:

pgBackRest 2.47 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    annotate        Add or modify backup annotation.
    archive-get     Get a WAL segment from the archive.
    archive-push    Push a WAL segment to the archive.
    backup          Backup a database cluster.
    check           Check the configuration.
    expire          Expire backups that exceed retention.
    help            Get help.
    info            Retrieve information about backups.
    repo-get        Get a file from a repository.
    repo-ls         List files in a repository.
    restore         Restore a database cluster.
    server          pgBackRest server.
    server-ping     Ping pgBackRest server.
    stanza-create   Create the required stanza data.
    stanza-delete   Delete a stanza.
    stanza-upgrade  Upgrade a stanza.
    start           Allow pgBackRest processes to run.
    stop            Stop pgBackRest processes from running.
    verify          Verify contents of the repository.
    version         Get version.

Use 'pgbackrest help [command]' for more information.

Algunos aspecto importantes a tener en cuenta cuando trabajamos con pgBackRest son:

• Una stanza es el conjunto de parámetros con los que se debe backupear una base de datos, localización física de los ficheros, opciones de archivado, etc.
• Un repositorio es donde pgBackRest almacena los backups y los ficheros WAL, si habilitamos el cifrado, debemos saber que SIEMPRE se realiza en la parte del cliente y que NO será posbile cambiar el password a posteriori.
• Los recomendable es tener un servidor de backups en el datacenter donde se tienen los servidores PostgreSQL con un repositorio local y un repositorio remoto como podría ser un S3 para garantizar la redundancia. Además si un almacenamiento es mas barato que el otro también se pueden especificar retenciones distintas.
• Por defecto pgBackRest esperará a que se ejecute un checkpoint para realizar un backup, si no queremos tener que esperar podemos indicarlo mediante start-fast=y.
• El número de procesos a utilizar en paralelo para compresión y transmisión de datos se indicará mediante process-max=3, no se recomienda configurar mas de un 25% de los recursos del servidor.

Si no hemos instalado PostgreSQL en el RepositoryServer lo hacemos ahora, como ya se indicó en la parte de pgbasebackup , no es necesario que arranquemos el servicio, tan solo lo instalamos para tener disponible la herramienta pgbasebackup en caso de necesitarla y para que nos genere el usuario postgres, podríamos utilizar otro usuario pero si todo queda unificado de la misma manera en todos los servidores, mucho mejor.

Creamos el directorio donde almacenaremos los backups en el servidor RepositoryServer:

Creamos los directorios y ficheros necesarios para que pgBackRest funcione, tanto en los servidores PostgreSQL, el servidor RepositoryServer y el servidor RestoreServer:

El acceso entre servidores PostgreSQL RepositoryServer y RestoreServer se configurará mediante SSH o TSL

Yo personalmente prefiero SSH ya que es un procedimiento mas sencillo, generamos las keys SSH tanto en los servidores PostgreSQL, el RepositoryServer y el RestoreServer:

Autorizamos las keys entre RepositoryServer <-> PostgreSQLs, y entre RestoreServer <-> RepositoryServer en ambos sentidos en los dos casos.

Comprobamos los accesos.

Configuramos pgBackRest en el RepositoryServer:

[test]
pg1-host=PostgreSQL00-test
pg1-path=/var/db/postgres/data15/
pg1-host-user=postgres

[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-host-user=postgres
repo1-path=/var/db/postgres/backups
repo1-retention-full=14
start-fast=y
process-max=3

Configuramos pgBackRest en los PostgreSQL y RestoreServer:

[test]
pg1-path=/var/db/postgres/data15/
pg1-host-user=postgres

[global]
log-level-file=detail
repo1-host=PostgreSQLBackups-test
repo1-host-user=postgres

Debemos configurar el archivado en los PostgreSQL y RestoreServer:

archive_command = 'pgbackrest --stanza=test archive-push %p'
archive_mode = on
max_wal_senders = 3
wal_level = replica

Reiniciamos el servicio:

Por alguna razón la replicación entre PRIMARY y SECONDARY se rompe, tuve que volver a sincronizarlo:

Sep  6 11:04:13 PostgreSQL01-test postgres[11043]: [8-1] 2023-09-06 11:04:13.935 CEST [11043] FATAL:  recovery aborted because of insufficient parameter settings

Desde el RepositoryServer creamos la stanza:

Desde el RepositoryServer comprobamos que funcione correctamente:

También lo comprobamos desde los servidores PostgreSQL:

Desde el RepositoryServer realizamos un backup, pero antes consultamos los registros existentes en la basse de datos, para luego poder comprobar si la restauración se ha realizado correctamente.

Consultamos los registros de la base de datos:

Realizamos el backup desde el RepositoryServer:

NOTA: Por defecto los backups son incrementales pero como no existía un backup previo se ha pasado a full de forma automática.

pgBackRest permite los siguientes tipos de backups:

• Full: Backup full.
  
  su -l postgres -c "pgbackrest --stanza=test --type=full --log-level-console=info backup"
• Diferencial: Cambios desde el último Full backup.
  
  su -l postgres -c "pgbackrest --stanza=test --type=diff --log-level-console=info backup"
• Incremental: Cambios desde el último incremental.
  
  su -l postgres -c "pgbackrest --stanza=test --type=incr --log-level-console=info backup"

Ahora que ya tenemos un full-backup vamos realizar un backup diferencial y uno incremental, de este modo tendremos un backup de cada tipo para ir haciendo pruebas.

Consultamos los registros antes de realizar el backup diferencial:

Realizamos el backup diferencial desde el RepositoryServer:

Consultamos los registros antes de realizar el backup incremental:

Realizamos el backup incremental desde el RepositoryServer:

Podemos visualizar el histórico de backups desde el RepositoryServer o los servidores PostgreSQL:

Los identificativos de los backups y el último ID en base de datos cuando se tomaron son los siguientes:

Full backup: 20230906-121338F                   - 129546 registros
Diff backup: 20230906-121338F_20230906-121510D  - 129635 registros
Incr backup: 20230906-121338F_20230906-121542I  - 129666 registros

Para realizar un restore en un servidor PostgreSQL debemos:

• Instalar PostgreSQL.
• Habilitar el archivado de PostgreSQL.
• Configurar pgBackRest como estaba el servidor PostgreSQL original.
• Conceder acceso SSH-postgres entre el RestoreServer <-> RepositoryServer en ambos sentidos.

Ya podemos proceder con la restauración, en mi caso voy a restaurar el backup diferencial de las 2023-09-06 12:15:10+02 que debería de tener 129635 registros.

Ejecutamos en RestoreServer:

Arrancamos PostgreSQL:

Comprobamos que se hayan insertado los datos correctamente:

Mirar porque al restaurar los backups diferenciales carga el último backup

Restore Point-In-Time:
su -l postgres -c “pgbackrest –stanza=test –type=time –target=“2023-09-3 08:18:30.289022-07” –log-level-console=info restore”

https://pgbackrest.org/user-guide.html#replication/streaming
pgbackrest permite montar SECONDARY servers de forma automática si configuramos:

• PRIMARY: Usuario de replicación y ACLs pertinentes.
• SECONDARY: pgbackrest con el usuario de replicación y las credenciales en /var/lib/postgresql/.pgpass

Comentar en el artículo de replicación que existe un tipo de replicación por ficheros WAL pero que no es en tiempo real, espera a que se cambie de WAL para enviarlos al STANDBY server.
streaming replication makes a direct connection to the primary and applies changes as soon as they are made on the primary
A hot standby performs replication using the WAL archive and allows read-only queries

Mirar bien la opción: –archive-mode=off

Hay ocasiones en las que conviene deshabilitar pgBackRest ya sea porque se ha cambiado de un Primary a un Sandby o por la razón que sea.

Para deshabilitarlo debemos ejecutar en el servidor PostgreSQL:

Para rehabilitarlo ejecutaremos en el servidor PostgreSQL:

Mirar en migops si alguna imagen sirve para dejar mas claro como funciona pgBackRest

https://www.migops.com/blog/pgbackrest-running-backup-from-a-standby-server/
https://pgbackrest.org/user-guide.html#standby-backup
https://www.migops.com/blog/wp-content/uploads/2022/04/pgBackRest_Running_Backup_from_a_Standby_Server.png
Es posible realizar backups de un STANDBY, la mayoría de los ficheros se copiarán del STANDBY server y las operaciones WAL que puede que todavía no se hayan replicado desde el PRIMARY server, de este modo los backups son exactamente como si se hubiesen sacado del PRIMARY pero sin sobrecargarlo al copiar el grueso de los ficheros desde el SECONDARY.

https://www.percona.com/blog/pgbackrest-restoration-scenarios/

Monit:
https://access.crunchydata.com/documentation/pgbackrest/latest/pdf/backrest.pdf
pgbackrest info –output=json
https://github.com/pgstef/check_pgbackrest
check_pgbackrest –list
check_pgbackrest –stanza=my_stanza –service=retention –retention-full=1 –output=human

Mirar:
repo1-bundle=y
repo1-block=y
–annotation=source=“demo backup”
repo1-retention-diff=4
restore_command de postgresql

su -l pgbackrest -c “pgbackrest –stanza=test –log-level-console=info stop”
su -l pgbackrest -c “pgbackrest –stanza=test –log-level-console=info stanza-delete –force”
Sin el force obliga a parar el PostgreSQL remoto, un sin sentido:
ERROR: [038]: postmaster.pid exists - looks like PostgreSQL is running. To delete stanza ’test’ on repo1, shut down PostgreSQL for stanza ’test’ and try again, or use –force.

Mirar el backend S3-Minio, repo con 7 días en local y 30 días en S3, se consigur definiendo varias líneas repo1, repo2 …

pgbarman

https://pgbarman.org/

https://code.google.com/archive/p/pg-rman/
https://github.com/omniti-labs/omnipitr

https://repmgr.org/
https://www.enterprisedb.com/postgres-tutorials/how-implement-repmgr-postgresql-automatic-failover

https://clusterlabs.github.io/PAF/
https://github.com/aiven/pglookout

Restore non PostgreSQL system

   --attribute-inserts
       Dump data as INSERT commands with explicit column names (INSERT
       INTO table (column, ...) VALUES ...). This will make restoration
       very slow; it is mainly useful for making dumps that can be loaded
       into non-PostgreSQL databases. Any error during restoring will
       cause only rows that are part of the problematic INSERT to be lost,
       rather than the entire table contents.
   --inserts
       Dump data as INSERT commands (rather than COPY). This will make
       restoration very slow; it is mainly useful for making dumps that
       can be loaded into non-PostgreSQL databases. Any error during
       restoring will cause only rows that are part of the problematic
       INSERT to be lost, rather than the entire table contents. Note that
       the restore might fail altogether if you have rearranged column
       order. The --column-inserts option is safe against column order
       changes, though even slower.
   --rows-per-insert=nrows
       Dump data as INSERT commands (rather than COPY). Controls the
       maximum number of rows per INSERT command. The value specified must
       be a number greater than zero. Any error during restoring will
       cause only rows that are part of the problematic INSERT to be lost,
       rather than the entire table contents.

https://www.postgresql.org/docs/current/monitoring.html
https://www.dbi-services.com/blog/patroni-on-freebsd-13/

Recomendar pgdump+Restic para DBs pequeñas