Backup PostgreSQL-Bareos bajo FreeBSD/Linux

Continuando con la serie de artículos sobre Bareos vamos a ver como hacer backups de PostgreSQL bajo FreeBSD y Linux.

El artículo se compone de las siguientes secciones:

Introducción
Escenario FreeBSD/Linux
Instalación de PostgreSQL
RunScript
Bpipe
Postgres Python plugin
Postgres Python plugin PITR
Postgres Python plugin PITR Timelines
Backup Incremental standby server
Debug

Introducción:

Bareos nos permite realizar los backups de PostgreSQL de tres maneras distintas.

RunScript(backup lógico): pg_dumpall, se dumpea la DB y se copia el dump, se necesita el doble de capacidad de almacenamiento.
Bpipe(backup lógico): pg_dumpall pero se pasa el backup por streaming a Bareos, además la restauración puede ser en un fichero o en la propia DB directamente.
Postgres Python plugin(backup físico): Backup físico de los ficheros DATA_DIR/pg_wal, permite PITR (Point in time Recovery).

Escenario FreeBSD/Linux:

Si estamos instalando PostgreSQL bajo FreeBSD este requiere de acceso a sysvipc, en mi caso se trata de una jail mediante Bastille .

Si la jail no tiene acceso a sysvipc obtendremos el siguiente error:

running bootstrap script ... 2024-11-01 07:11:16.834 CEST [23722] FATAL:  could not create shared memory segment: Function not implemented

Generamos la jail y realizamos la configuración básica del sistema mediante un template de Bastille propio .

bastille create -T BareosClient 14.1-RELEASE 192.168.69.31 nfe0
bastille template BareosClient datadyne.alfaexploit.com/bastille-basicconfiguration

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

bastille config BareosClient set sysvmsg=new
bastille config BareosClient set sysvsem=new
bastille config BareosClient set sysvshm=new

bastille stop BareosClient
bastille start BareosClient

NOTA: No debemos utilizar allow.sysvipc ya que se considera deprecated y otorga mas permisos de los estrictamente necesarios.

En cuanto a Linux vamos a montarlo sobre un contenedor LXD que no requiere acceso a ningún recurso en particular.

lxc launch ubuntu:noble/amd64 sys-bareos-client

Instalación de PostgreSQL:

Procedemos con la instalación del paquete, es preferible instalar la versión 16 de PostgreSQL ya que la 17 es parcialmente compatible con el plugin-python de Bareos solo permitiendo backups Full.

pkg install postgresql16-server

Habilitamos el servicio:

sysrc postgresql_enable=yes

Inicializamos la base de datos:

service postgresql initdb

Arrancamos el servico:

service postgresql start

apt install postgresql postgresql-contrib

Creamos una base de datos y una tabla:

su postgres -c 'psql -c "CREATE DATABASE testkr0m;"'
su postgres -c 'psql -d testkr0m -c "CREATE TABLE employees (id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);"'

Insertamos datos y comprobamos los resultados:

su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('John', 'Doe', 30);\""
su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('Jane', 'Smith', 25);\""
su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'
 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-02 10:33:12.5319
  2 | Jane       | Smith     |  25 | 2024-11-02 10:33:12.555151
(2 rows)

RunScript:

La manera mas sencilla de realizar un backup es con RunScript , simplemente hay que configurar un comando de dump cuando se ejecute el job, copiar el fichero generado y eliminar el dump.

En el director
Creamos un FileSet que backupeará /etc, /root, /home y los ficheros de configuración de PostgreSQL además del dump:

vi /usr/local/etc/bareos/bareos-dir.d/fileset/sys-postgres.conf

 FileSet {
     Name = "sys-postgres"
     Include {
         Options {
             Signature = XXH128
             Compression = LZ4
         }
         # System files:
         File = /etc/
         File = /root/
         File = /home/
         # Database config files:
         File = /var/db/postgres/data16/postgresql.conf
         File = /var/db/postgres/data16/pg_hba.conf
         File = /var/db/postgres/data16/pg_ident.conf
         # Database dump file:
         File = "/var/tmp/postgresql_dump.sql"
     }
 }

vi /etc/bareos/bareos-dir.d/fileset/sys-postgres.conf

 FileSet {
     Name = "sys-postgres"
     Include {
         Options {
             Signature = XXH128
             Compression = LZ4
         }
         # System files:
         # Linux: PostgreSQL config files postgresql.conf, pg_hba.conf and pg_ident.conf under /etc
         File = /etc/
         File = /root/
         File = /home/
         # Database dump file:
         File = "/var/tmp/postgresql_dump.sql"
     }
 }

Cambiamos el FileSet y añadimos RunScript-Before y RunScript-After al job.

NOTA: Cuando Bareos ejecuta el script de dump en FreeBSD parece no encontrar el path al binario pg_dumpall, así que debemos indicarle el path completo, en Linux no hay problema.

vi /usr/local/etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

Job {
  Name = "CLIENT_NAME-job"
  Client = "CLIENT_NAME-fd"
  JobDefs = "DefaultJob"
  Level = Full

  FileSet="sys-postgres"
  # This creates a dump of our database in the local filesystem on the client
  RunScript {
      FailJobOnError = Yes
      RunsOnClient = Yes
      RunsWhen = Before
      # FreeBSD pg_dumpall absolute path
      Command = "su postgres -c '/usr/local/bin/pg_dumpall > /var/tmp/postgresql_dump.sql'"
  }

  # This deletes the dump in our local filesystem on the client
  RunScript {
      RunsOnSuccess = Yes
      RunsOnClient = Yes
      RunsWhen = After
      # FreeBSD bash absolute path
      Command = "/usr/local/bin/bash -c 'rm /var/tmp/postgresql_dump.sql'"
  }
}

vi /etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

Job {
  Name = "CLIENT_NAME-job"
  Client = "CLIENT_NAME-fd"
  JobDefs = "DefaultJob"
  Level = Full

  FileSet="sys-postgres"
  # This creates a dump of our database in the local filesystem on the client
  RunScript {
      FailJobOnError = Yes
      RunsOnClient = Yes
      RunsWhen = Before
      # Linux
      Command = "su postgres -c 'pg_dumpall > /var/tmp/postgresql_dump.sql'"
  }

  # This deletes the dump in our local filesystem on the client
  RunScript {
      RunsOnSuccess = Yes
      RunsOnClient = Yes
      RunsWhen = After
      Command = "bash -c 'rm /var/tmp/postgresql_dump.sql'"
  }
}

Reiniciamos el director:

service bareos-dir restart

systemctl restart bareos-dir

Procedemos a realizar el backup desde la interfaz web indicando el FileSet: Jobs -> Run

Debería de terminar sin dar problemas:

Eliminamos la base de datos desde la CLI de PostgresSQL:

su postgres -c 'psql -c "DROP DATABASE testkr0m;"'
su postgres -c 'psql -c "\l"'
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(3 rows)

Procedemos con la restauración: Restore
Es muy important deshabilitar la opción “Merge all client filesets” ya que si no lo hacemos el árbol de ficheros a restaurar de la derecha será una vista de la combinación de todos los backups de este cliente, nosotros solo queremos los ficheros del backup indicado.

El job de restore debería de terminar correctamente:

Y en el servidor deberíamos de tener el fichero de backup:

ls -la /tmp/bareos-restores/var/tmp/postgresql_dump.sql
-rw-r-----  1 postgres wheel 4234 Nov  2 10:34 /tmp/bareos-restores/var/tmp/postgresql_dump.sql

Cargamos dicho dump a la DB:

su postgres -c psql < /tmp/bareos-restores/var/tmp/postgresql_dump.sql

Consultamos los datos restaurados:

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

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'
 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-02 10:33:12.5319
  2 | Jane       | Smith     |  25 | 2024-11-02 10:33:12.555151
(2 rows)

Bpipe:

Mediante Bpipe podremos dumpear la DB y streamear el dump directamente a Bareos, por lo tanto no es necesario almacenar el fichero de backup en local.

Este plugin viene instalado por defecto con el filedaemon tanto en FreeeBSD como en Linux:

file /usr/local/lib/bareos/plugins/bpipe-fd.so
/usr/local/lib/bareos/plugins/bpipe-fd.so: ELF 64-bit LSB shared object, x86-64, version 1 (FreeBSD), dynamically linked, for FreeBSD 14.0 (1400097), stripped

file /usr/lib/bareos/plugins/bpipe-fd.so
/usr/lib/bareos/plugins/bpipe-fd.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=4e5dbae63a927968fe8626446740457a252ad1ee, stripped

En el cliente:

Para que Bpipe funcione debemos reconfigurar la parte relacionada con los plugins en el filedaemon:

vi /usr/local/etc/bareos/bareos-fd.d/client/myself.conf

Client {
  Name = CLIENT_NAME-fd
  Maximum Concurrent Jobs = 20

  # remove comment from "Plugin Directory" to load plugins from specified directory.
  # if "Plugin Names" is defined, only the specified plugins will be loaded,
  # otherwise all filedaemon plugins (*-fd.so) from the "Plugin Directory".
  #
  # FreeBSD:
  Plugin Directory = "/usr/local/lib/bareos/plugins"
  Plugin Names = "bpipe"
}

Reiniciamos el filedaemon:

service bareos-fd restart

vi /etc/bareos/bareos-fd.d/client/myself.conf

Client {
  Name = CLIENT_NAME-fd
  Maximum Concurrent Jobs = 20

  # remove comment from "Plugin Directory" to load plugins from specified directory.
  # if "Plugin Names" is defined, only the specified plugins will be loaded,
  # otherwise all filedaemon plugins (*-fd.so) from the "Plugin Directory".
  #
  # Linux:
  Plugin Directory = "/usr/lib/bareos/plugins"
  Plugin Names = "bpipe"
}

Reiniciamos el filedaemon:

systemctl restart bareos-fd

Aunque el backup se haga por streaming, se precisa de un directorio “virtual” para que Bareos pueda escribir/leer el backup.

mkdir /POSTGRESQL

En el director:
Bpipe precisa de tres parámetros:

file: Path de un fichero que se utilizará temporalmente como pipe. Cuando restauremos debemos indicar este fichero.
reader: Comando a ejecutar para obtener la salida que Bareos leerá y guardará en el backup.
writer: Comando al que empiparemos la salida del restore.

Creamos un FileSet con la configuración de Bpipe.

vi /usr/local/etc/bareos/bareos-dir.d/fileset/sys-postgresBpipeDB.conf

FileSet {
    Name = "sys-postgresBpipeDB"
    Include {
        Options {
            Signature = XXH128
            Compression = LZ4
        }
        # System files:
        File = /etc/
        File = /root/
        File = /home/
        # Database config files:
        File = /var/db/postgres/data16/postgresql.conf
        File = /var/db/postgres/data16/pg_hba.conf
        File = /var/db/postgres/data16/pg_ident.conf
        # Bpipe database dump
        # FreeBSD pg_dumpall absolute path
        Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c '/usr/local/bin/pg_dumpall --clean':writer=su postgres -c /usr/local/bin/psql"
    }
}

vi /etc/bareos/bareos-dir.d/fileset/sys-postgresBpipeDB.conf

FileSet {
    Name = "sys-postgresBpipeDB"
    Include {
        Options {
            Signature = XXH128
            Compression = LZ4
        }
        # System files:
        # Linux: PostgreSQL config files postgresql.conf, pg_hba.conf and pg_ident.conf under /etc
        File = /etc/
        File = /root/
        File = /home/
        # Bpipe database dump
        # Linux
        Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c 'pg_dumpall --clean':writer=su postgres -c psql"
    }
}

NOTA: Backupeamos con el --clean. Esta opción agrega instrucciones SQL en el volcado para eliminar las bases de datos, roles y otros objetos antes de recrearlos.

Cambiamos el FileSet del job, tampoco hará falta ningún RunScript-Before ni RunScript-After:

vi /usr/local/etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

vi /etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

Job {
  Name = "CLIENT_NAME-job"
  Client = "CLIENT_NAME-fd"
  JobDefs = "DefaultJob"
  Level = Full

  FileSet="sys-postgresBpipeDB"
}

Reiniciamos el director:

service bareos-dir restart

systemctl restart bareos-dir

Procedemos a realizar el backup desde la interfaz web indicando el FileSet: Jobs -> Run

Debería de terminar sin dar problemas:

Eliminamos la base de datos desde la CLI de PostgresSQL:

su postgres -c 'psql -c "DROP DATABASE testkr0m;"'
su postgres -c 'psql -c "\l"'
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(3 rows)

El job de restore debería de terminar correctamente:

Si consultamos el PostgreSQL la base de datos debería de estar restaurada:

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

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'
 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-02 10:33:12.5319
  2 | Jane       | Smith     |  25 | 2024-11-02 10:33:12.555151
(2 rows)

También puede resultar interesante empipar la salida del restore a un fichero y no directamente a la CLI de PostgreSQL para guardar el backup en un fichero sin tocar la DB:

writer=sh -c 'cat >/var/tmp/postgres.sql'

En el director:
Creamos un FileSet nuevo con otra configuración de Bpipe.

vi /usr/local/etc/bareos/bareos-dir.d/fileset/sys-postgresBpipeFile.conf

FileSet {
    Name = "sys-postgresBpipeFile"
    Include {
        Options {
            Signature = XXH128
            Compression = LZ4
        }
        # System files:
        File = /etc/
        File = /root/
        File = /home/
        # Database config files:
        File = /var/db/postgres/data16/postgresql.conf
        File = /var/db/postgres/data16/pg_hba.conf
        File = /var/db/postgres/data16/pg_ident.conf
        # Bpipe database dump
        # FreeBSD pg_dumpall absolute path
        Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c '/usr/local/bin/pg_dumpall --clean':writer=sh -c 'cat >/var/tmp/postgres.sql'"
    }
}

vi /etc/bareos/bareos-dir.d/fileset/sys-postgresBpipeFile.conf

FileSet {
    Name = "sys-postgresBpipeFile"
    Include {
        Options {
            Signature = XXH128
            Compression = LZ4
        }
        # System files:
        # Linux: PostgreSQL config files postgresql.conf, pg_hba.conf and pg_ident.conf under /etc
        File = /etc/
        File = /root/
        File = /home/
        # Bpipe database dump
        # Linux
        Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c 'pg_dumpall --clean':writer=sh -c 'cat >/var/tmp/postgres.sql'"
    }
}

Cambiamos el FileSet:

vi /usr/local/etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

vi /etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

Job {
    Name = "CLIENT_NAME-job"
    Client = "CLIENT_NAME-fd"
    JobDefs = "DefaultJob"
    Level = Full

    FileSet="sys-postgresBpipeFile"
}

Reiniciamos el director:

service bareos-dir restart

systemctl restart bareos-dir

Procedemos a realizar el backup desde la interfaz web indicando el FileSet, es necesario realizar un backup de nuevo ya que en los restores se utilizan los parámetros del momento del backup: Jobs -> Run

Debería de terminar sin dar problemas:

Eliminamos la base de datos desde la CLI de PostgresSQL:

su postgres -c 'psql -c "DROP DATABASE testkr0m;"'
su postgres -c 'psql -c "\l"'
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | postgres=CTc/postgres+
           |          |          |                 |         |         |            |           | =c/postgres
(3 rows)

El job de restore debería de terminar correctamente:

Pero esta vez no ha restaurado el backup en la DB, si no que ha generado el fichero en el directorio indicado en la config del FileSet sys-postgresBpipeFile:

ls -la /var/tmp/postgres.sql
-rw-r-----  1 root wheel 6644 Nov  2 11:04 /var/tmp/postgres.sql

Cargamos el fichero en la DB:

su postgres -c psql < /var/tmp/postgres.sql

Consultamos los datos restaurados:

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

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'
 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-02 10:33:12.5319
  2 | Jane       | Smith     |  25 | 2024-11-02 10:33:12.555151
(2 rows)

Mucho cuidado porque cuando restauramos se aplican los parámetros de Bpipe del momento en el que se tomó el backup, no el que haya en el FileSet en ese momento(incluso habiendo reiniciado el director) cuando se hace el restore, estos datos fueron guardados en el catalog cuando se hizo el backup.

Se pueden consultar los parámetros del FileSet de un backup desde la bconsole en el director:

bconsole

list jobs
+-------+------------------+-----------------+---------------------+----------+------+-------+----------+------------+-----------+
| jobid | name             | client          | starttime           | duration | type | level | jobfiles | jobbytes   | jobstatus |
+-------+------------------+-----------------+---------------------+----------+------+-------+----------+------------+-----------+
|     1 | BareosClient-job | BareosClient-fd | 2024-11-02 09:52:24 | 00:00:01 | B    | F     |        8 | 38,963,186 | T         |
|     2 | RestoreFiles     | BareosClient-fd | 2024-11-02 09:54:03 | 00:00:02 | R    | F     |        1 |  4,040,720 | T         |
|     3 | BareosClient-job | BareosClient-fd | 2024-11-02 10:23:02 | 00:00:01 | B    | F     |      797 |  3,239,901 | T         |
|     4 | RestoreFiles     | BareosClient-fd | 2024-11-02 10:28:12 | 00:00:02 | R    | F     |        1 |      4,235 | T         |
|     5 | BareosClient-job | BareosClient-fd | 2024-11-02 10:34:26 | 00:00:01 | B    | F     |      797 |  3,239,899 | T         |
|     6 | RestoreFiles     | BareosClient-fd | 2024-11-02 10:37:20 | 00:00:03 | R    | F     |        1 |      4,234 | T         |
|     7 | BareosClient-job | BareosClient-fd | 2024-11-02 10:47:31 | 00:00:02 | B    | F     |      794 |  3,221,993 | T         |
|     8 | RestoreFiles     | BareosClient-fd | 2024-11-02 10:51:33 | 00:00:05 | R    | F     |        1 |      6,644 | T         |
|     9 | BareosClient-job | BareosClient-fd | 2024-11-02 10:59:59 | 00:00:02 | B    | F     |      794 |  3,221,993 | T         |
|    10 | RestoreFiles     | BareosClient-fd | 2024-11-02 11:04:28 | 00:00:03 | R    | F     |        1 |      6,644 | T         |
+-------+------------------+-----------------+---------------------+----------+------+-------+----------+------------+-----------+

sqlquery
SELECT Job.JobId, Job.Name, FileSet.FileSet, FileSet.FileSetId FROM Job, FileSet WHERE Job.FileSetId = FileSet.FileSetId AND Job.JobId = XX;
SELECT Job.JobId, Job.Name, FileSet.FileSet, FileSet.FileSetId FROM Job, FileSet WHERE Job.FileSetId = FileSet.FileSetId AND Job.JobId = 9;
+-------+------------------+-----------------------+-----------+
| jobid | name             | fileset               | filesetid |
+-------+------------------+-----------------------+-----------+
|     9 | BareosClient-job | sys-postgresBpipeFile |         4 |
+-------+------------------+-----------------------+-----------+
Enter SQL query: ENTER
End query mode.

list filesets
|         4 | sys-postgresBpipeFile | L9Ng76h0Mx/Og0gHH8/ZfD | 2024-11-02 10:59:57 | FileSet {
  Name = "sys-postgresBpipeFile"
  Include {
    Options {
      Signature = "XXH128"
      Compression = "LZ4"
      HardLinks = No
      AclSupport = Yes
      XattrSupport = Yes
    }
    File = "/etc/"
    File = "/root/"
    File = "/home/"
    Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c '/usr/local/bin/pg_dumpall --clean':writer=sh -c 'cat >/var/tmp/postgres.sql'"
  }
}
 |

Si en un momento dado modificar la orden indicada en el FileSet, podemos sobreescribirla desde la interfaz web, en este caso quiero restaurar el dump con un nombre distinto postgres2.sql. Pero si pegamos los parámetros tal cual no parsea correctamente los argumentos:

bpipe:file=/POSTGRESQL/dump.sql:reader=su postgres -c 'pg_dumpall --clean':writer=sh -c 'cat >/var/tmp/postgres2.sql'

Obtendremos un error:

Invalid keyword: postgres

En cambio si preparamos un script, no se quejará.
En el cliente:

vi dumpToFile.sh

#!/usr/local/bin/bash
cat >/var/tmp/postgres2.sql

#!/bin/bash
cat >/var/tmp/postgres2.sql

chmod 700 dumpToFile.sh

También podemos dejar preparado el script de volcado directo a la DB:

vi dumpToDB.sh

#!/usr/local/bin/bash
su postgres -c /usr/local/bin/psql

#!/bin/bash
su postgres -c /usr/local/bin/psql

chmod 700 dumpToDB.sh

Y en la interfaz web le pasamos solo el writer ya que estamos restaurando:

bpipe:file=/POSTGRESQL/dump.sql:writer=/root/dumpToFile.sh
bpipe:file=/POSTGRESQL/dump.sql:writer=/root/dumpToDB.sh

Podemos ver el fichero restaurado:

ls -la /var/tmp/postgres2.sql
-rw-r-----  1 root wheel 0 Nov  2 11:26 /var/tmp/postgres2.sql

Bpipe también podría ser utilizado para backupear PostgresSQLs remotos desde un servidor que tenga acceso a dicha base de datos:

FileSet {
  Name = "postgresqlBpipeRemote"
  Include {
    Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=pg_dumpall -h <hostname> -U <username> -W <password>:writer=psql -h <hostname> -U <username> -W <password>"
    Options {
      Signature = XXH128
      Compression = LZ4
    }
  }
}

PostgreSQL Python plugin:

El plugin en Python de PostgreSQL nos permitirá realizar backups físicos, esto implica una mayor velocidad tanto de dump como de restore, pero por contraparte ocupará mas espacio en el servidor de Bareos y no podremos restaurar solo partes de la DB.

El único inconveniente es que por el momento(2024/20/29) no soporta PostgreSQL17 aunque están en ello , en mis pruebas los backups Full funcionaron sin problemas, pero los backups Incrementales no debido a unos cambios en la gestión de los ficheros WAL, el error mostrado con la versión 17 es el siguiente:

Fatal error: python3-fd-mod: Timeout waiting 60 sec. for wal file 000000120000000000000047 to be archived

Por suerte el plugin funciona perfectamente con PostgreSQL16 que es la versión que instala por defecto en Ubuntu24.04 y en FreeBSD también la tenemos disponible de forma binaria sin problema.

Otro aspecto a tener en cuenta es que este plugin no soporta backups Diferenciales aunque parece ser porque PostgreSQL directamente no los soporta :

Differential backups are not supported! Only Full and Incremental

Y si se actualiza entre major versions los backups anteriores no servirán para la versión actualizada. Esto obliga a reiniciar el histórico de backups.

Las principales ventajas que este plugin presenta son:

Mayor velocidad de backup ya que es un backup físico.
No necesita el doble de espacio en disco en el servidor PostgreSQL.
Point-In-Time-Restore (PITR), restauración a una fecha y hora determinada.
Si hacemos backups Ìncrementales se pueden backupear bases de datos muy grandes, ya que solo se copian los ficheros WAL nuevos.

Antes de empezar debemos tener claros algunos conceptos:

En todo servidor PostgreSQL las querys siempre se escriben en un fichero WAL antes de ser ejecutadas en la DB(independientemente de si hay replicación o backups configurados) este fichero se escribe en el directorio DATA_DIR/pg_wal.
PostgreSQL genera ficheros WAL como resultado de las operaciones pendientes en la base de datos, si se produce un apagado abrupto, se leen las operaciones 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. Pero debemos tener en cuenta que cuanto mas espaciemos los Full backups mas tardará un restore, ya que se tendrán que aplicar muchos mas WAL files sobre esa base.
Los backups Incrementales solo tienen sentido en bases de datos grandes donde solo se modifica una pequeña parte de estas. Para bases de datos pequeñas o bases de datos grandes con muchos cambios PostgreSQL recomienda Full backups .
Para poder almacenar los ficheros WAL(DATA_DIR/pg_wal) se debe configurar el archive_command, que simplemente copia el fichero DATA_DIR/pg_wal a otro directorio antes de que sea reutilizado, manteniendo de este modo un histórico de operaciones realizadas en la DB.
El DATA_DIR se restaura por un lado y los ficheros WALL archivados por otro, estos últimos mediante el comando restore_command configurado en PostgreSQL.
Si queremos restaurar hasta X PITR debemos indicarlo mediante el parámetro: recovery_target_time = ‘yyyy-mm-dd hh:mm:ss.sss’

Con estas imágenes quedará mas claro la diferencia entre DATA_DIR/pg_wal y WALLs archivados.

DATA_DIR/pg_wal	Archived WALLs

El proceso de backup variará según el tipo de backup:

Full backup	Incremental Backup

Por otro lado el proceso de restauración es el siguiente:

También podemos ver su funcionamiento en este video.

Para que el plugin funcione debemos habilitar el WAL archiving en los servidores PostgreSQL.

mkdir -p /var/lib/pgsql/wal_archive
chown postgres:postgres /var/lib/pgsql/wal_archive

vi /var/db/postgres/data16/postgresql.conf

vi /etc/postgresql/16/main/postgresql.conf

wal_level = replica
archive_mode = on
# If file doesnt exist in destination, copy it:
# Archive commands and libraries should generally be designed to refuse to overwrite any pre-existing archive file.
archive_command = 'test ! -f /var/lib/pgsql/wal_archive/%f && cp %p /var/lib/pgsql/wal_archive/%f'

El archive_command contempla varios parámetros:

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

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

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 WAL(DATA_DIR/pg_wal) 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, esto es peligroso ya que los ficheros WAL(DATA_DIR/pg_wal) nunca serán reciclados, consumiendo mucho espacio en el servidor y pudiendo dejar sin servicio el PostgreSQL.

Si la velocidad de archivado no es suficiente y estamos haciendo backups Incremetales el margen de pérdida de datos en caso de catástrofe es mayor, ya que solo estamos copiando los ficheros WAL archivados. Este retraso de archivado también implica que le directorio WAL(DATA_DIR/pg_wal) crecerá.
En estos casos podemos sustituir el comando de archivado por una archive_library escrita en C lo que nos proporcionará un rendimiento mucho mayor que un script de shell.

Si por el motivo que sea queremos dejar de archivar temporalmente, se puede definir archive_command a empty ‘’, esto hará que los ficheros WALque debían ser archivados se acumulen en DATA_DIR/pg_wal hasta que se reasigne el archive_command.

Client:
Instalamos el plugin:

pkg install bareos.com-filedaemon-python-plugins-common bareos.com-filedaemon-python3-plugin py311-dateutils py311-pg8000

El plugin no tiene paquete para FreeBSD, pero podemos descargarlo del repo de GitHub:

wget https://raw.githubusercontent.com/bareos/bareos/602dbc78286fdd823ddf856fe31191c03269be66/core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py
cp bareos-fd-postgresql.py /usr/local/lib/bareos/plugins/

NOTA: El otro fichero que se instala en Linux bareos-fd-postgres.py parece ser una version vieja que se pone por compatibilidad que será eliminada en Bareos2.4, pero no hace falta instalarla en FreeBSD.

apt install bareos-filedaemon-postgresql-python-plugin python3-pg8000

Indicamos el plugin a utilizar en la configuración del FileDaemon:

vi /usr/local/etc/bareos/bareos-fd.d/client/myself.conf

Client {
  Name = CLIENT_NAME-fd
  Maximum Concurrent Jobs = 20

  # remove comment from "Plugin Directory" to load plugins from specified directory.
  # if "Plugin Names" is defined, only the specified plugins will be loaded,
  # otherwise all filedaemon plugins (*-fd.so) from the "Plugin Directory".
  #
  # FreeBSD:
  Plugin Directory = "/usr/local/lib/bareos/plugins"
  Plugin Names = "python3"
}

vi /etc/bareos/bareos-fd.d/client/myself.conf

Client {
  Name = CLIENT_NAME-fd
  Maximum Concurrent Jobs = 20

  # remove comment from "Plugin Directory" to load plugins from specified directory.
  # if "Plugin Names" is defined, only the specified plugins will be loaded,
  # otherwise all filedaemon plugins (*-fd.so) from the "Plugin Directory".
  #
  # Linux:
  Plugin Directory = /usr/lib/bareos/plugins
  Plugin Names = "python3"
}

Reiniciamos el FileDaemon:

service bareos-fd restart

systemctl restart bareos-fd

Director:
Creamos un FileSet llamado sys-postgresPlugin que backupee los directorios /etc, /root, /home y los ficheros de configuración de PostgreSQL además de la DB mediante el plugin de PostgreSQL.

vi /usr/local/etc/bareos/bareos-dir.d/fileset/sys-postgresPlugin.conf

FileSet {
    Name = "sys-postgresPlugin"
    Include {
        Options {
            Signature = XXH128
            Compression = LZ4
        }
        # System files:
        File = /etc/
        File = /root/
        File = /home/
        # Database config files:
        File = /var/db/postgres/data16/postgresql.conf
        File = /var/db/postgres/data16/pg_hba.conf
        File = /var/db/postgres/data16/pg_ident.conf
        # PostgreSQL plugin
        # FreeBSD: grep unix_socket_directories /var/db/postgres/data16/postgresql.conf
        # /tmp
        Plugin = "python"
                ":module_name=bareos-fd-postgresql"
                ":db_host=/tmp"
                ":db_user=postgres"
                ":wal_archive_dir=/var/lib/pgsql/wal_archive/"
                ":ignore_subdirs=pg_replslot,pg_dynshmem,pg_notify,pg_serial,pg_snapshots,pg_stat_tmp,pg_subtrans,pgsql_tmp"
                ":switch_wal=true"
                ":start_fast=true"
                ":stop_wait_wal_archive=true"
    }
}

vi /etc/bareos/bareos-dir.d/fileset/sys-postgresPlugin.conf

FileSet {
    Name = "sys-postgresPlugin"
    Include {
        Options {
            Signature = XXH128
            Compression = LZ4
        }
        # System files:
        # Linux: PostgreSQL config files postgresql.conf, pg_hba.conf and pg_ident.conf under /etc
        File = /etc/
        File = /root/
        File = /home/
        # PostgreSQL plugin
        # Linux: grep unix_socket_directories /etc/postgresql/16/main/postgresql.conf
        # /var/run/postgresql
        Plugin = "python"
                ":module_name=bareos-fd-postgresql"
                ":db_host=/var/run/postgresql/"
                ":db_user=postgres"
                ":wal_archive_dir=/var/lib/pgsql/wal_archive/"
                ":ignore_subdirs=pg_replslot,pg_dynshmem,pg_notify,pg_serial,pg_snapshots,pg_stat_tmp,pg_subtrans,pgsql_tmp"
                ":switch_wal=true"
                ":start_fast=true"
                ":stop_wait_wal_archive=true"
    }
}

La lista de parámetros que soporta el plugin podemos encontrarla en la web oficial de Bareos .

El parámetro ignore_subdirs podemos averiguarlo de la documantación de PostgreSQL .

El parámetro start_fast puede resultar útil sobretodo mientras hagamos prubeas:

By default, the backup will start after a checkpoint which can take some time.
If start_fast_start is true, pg_backup_start will be executed as quickly as possible.
This enforces an immediate checkpoint which can cause a spike in I/O operations and slowing any concurrently executing queries. Default: False

Otra opción si el servidor tiene poca actividad sería configurar archive_timeout para forzar un cambio de WAL segment cada X tiempo, pero hay que tener en cuenta que estos ficheros WAL aunque se hayan “forzado” ocuparán lo mismo que los ficheros WAL normales, por lo tanto no se debe poner un número muy bajo ya que el servidor PostgreSQL podría quedarse sin espacio fácilmente.

Podemos leer la recomendación de la documantación oficial de PostgreSQL :

archive_timeout settings of a minute or so are usually reasonable.

Debemos tener en cuenta que cuando se hace un backup Bareos copia el DATA_DIR y los WALs archivados, una vez realizado el backup estos WALs archivados ya no son necesarios pero no los elimina automáticamente.

While the PostgreSQL plugin backups only the required files from the WAL archive directory, old files are not removed automatically.

La idea es crear un RunScript-Before que liste los WALs existentes antes del backup y un RunScript-After que elimine los WALs de la lista generada por el RunScript-Before.

Cambiamos el FileSet a sys-postgresPlugin y ejecutamos los scripts RunScript-Before y RunScript-After:

vi /usr/local/etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

vi /etc/bareos/bareos-dir.d/job/CLIENT_NAME-job.conf

 Job {
     Name = "CLIENT_NAME-job"
     Client = "CLIENT_NAME-fd"
     JobDefs = "DefaultJob"
     Level = Full

     FileSet="sys-postgresPlugin"
     RunScript {
         FailJobOnError = Yes
         RunsOnClient = Yes
         RunsWhen = Before
         Command = "/root/listWALs.sh"
     }

     RunScript {
         RunsOnSuccess = Yes
         RunsOnClient = Yes
         RunsWhen = After
         Command = "/root/clearWALs.sh"
     }
 }

Reiniciamos el Director:

service bareos-dir restart

systemctl restart bareos-dir

Cliente:
El script de listar los WALs sería el siguiente:

vi /root/listWALs.sh

#!/usr/local/bin/bash
ls -l /var/lib/pgsql/wal_archive > /tmp/WALLlist

#!/bin/bash
ls -l /var/lib/pgsql/wal_archive > /tmp/WALLlist

El script de limpiar los WALs sería el siguiente:

vi /root/clearWALs.sh

#!/usr/local/bin/bash
for WALL in `cat /tmp/WALLlist | awk '{print $9}'`; do
    rm -f /var/lib/pgsql/wal_archive/$WALL
done;
rm /tmp/WALLlist

#!/bin/bash
for WALL in `cat /tmp/WALLlist | awk '{print $9}'`; do
    rm -f /var/lib/pgsql/wal_archive/$WALL
done;
rm /tmp/WALLlist

Asignamos los permisos correctos:

chmod 700 listWALs.sh
chmod 700 clearWALs.sh

PostgreSQL permite definir reglas de acceso a las bases de datos. Estas son definidas en el fichero de configuración pg_hba.conf:

TYPE            DATABASE        USER            ADDRESS                 METHOD
local/host      DB_NAME         USERNAME        ADDRESS                 trust/peer/scram-sha-256...

Type:
- local: Acceso de usuarios locales al sistema.
- host: Acceso de usuarios remotos al sistema.
Database:
- DB_NAME: Nombre de la base de datos a la que podrá acceder el usuario.
User:
- USERNAME: Nombre de usuario.
Address:
- ADDRESS: Dirección IP o nombre de host desde donde conectará el usuario en caso de no ser local.
Method:
- trust: Acceso por parte de cualquier usuario local sin autenticación, es decir, sin password. El único requisito es que el usuario debe conectar con un ROLE de PostgreSQL existente, por defecto postgres.
- peer: Acceso por parte de cualquier usuario local sin autenticación, es decir, sin password. Pero el nombre del usuario del sistema debe coincidir con el nombre del ROLE con el que conecte.
- scram-sha-256: Acceso con autenticación.

Por defecto PostgreSQL genera el ROLE postgres, veamos un ejemplo de usuario local con método trust indicando un ROLE existente:

local   all             all                                     trust

BareosClient # ~> id
uid=0(root) gid=0(wheel) groups=0(wheel),5(operator)

BareosClient # ~> psql -U postgres
psql (16.4)
Type "help" for help.

postgres=#

Ahora un ejemplo con método peer también con un ROLE existente:

local   all             all                                     peer

root@sys-bareos-client:~# id
uid=0(root) gid=0(root) groups=0(root)

root@sys-bareos-client:~# psql -U postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

Si pasamos al usuario postgres este por defecto conecta con el ROLE postgres por lo tanto el usuario y el ROLE coinciden y permite el acceso a la DB:

root@sys-bareos-client:~# su postgres -c psql
psql (16.4)
Type "help" for help.

postgres=#

En FreeBSD por defecto permite el acceso a cualquier usuario local utiilizando trust, simplemente debe conectar con un ROLE existente. Como en el director hemos configurado el FileSet con el plugin db_user=postgres utilizará el usuario root con el que corre Bareos y el ROLE postgres indicado en el plugin.

En Linux en cambio solo se permite el acceso a los usuarios locales mediante peer, este método requiere que el usuario del sistema coincida con el ROLE, pero root no coincide con postgres.

Para que funcione debemos hacer que el usuario root pueda conectar con el ROLE postgres, para ello modificamos el fichero de configuración de PostgreSQL del siguiente modo:

vi /etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
#local   all             postgres                                peer
local   all             postgres                                trust

Reiniciamos el PostgreSQL:

systemctl restart postgresql

Podemos comprobar el acceso desde la shell de root tal como hará Bareos:

psql -U postgres -h /tmp

psql -U postgres -h /var/run/postgresql

Client:
Configuramos el comando a ejecutar para restaurar la DB desde los ficheros WAL.

vi /var/db/postgres/data16/postgresql.conf

vi /etc/postgresql/16/main/postgresql.conf

restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'

Reiniciamos PostgreSQL:

service postgresql restart

systemctl restart postgresql

Realizamos un backup desde la interfaz web indicando el FileSet: Jobs -> Run

Debería de terminar sin dar problemas:

Eliminamos la base de datos desde la CLI de PostgresSQL:

su postgres -c 'psql -c "DROP DATABASE testkr0m;"'
su postgres -c 'psql -c "\l"'
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C.UTF-8 |            |           | postgres=CTc/postgres+
           |          |          |                 |         |         |            |           | =c/postgres
(3 rows)

Procedemos con la restauración, es muy important deshabilitar la opción “Merge all client filesets” en la restauración de Bareos ya que si no lo hacemos el árbol de ficheros a restaurar de la derecha será una vista de la combinación de todos los backups de este cliente, nosotros solo queremos los ficheros del backup indicado.

service postgresql stop
rm -rf /var/db/postgres/*
rm -rf /var/lib/pgsql/wal_archive/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/db/postgres
    - /var/lib/pgsql/wal_archive/LAST_WALL_FILE+LAST_WALL_FILE.history

Seguimos los consejos de restauración de PostgreSQL :

rm /var/db/postgres/data16/postmaster.pid
rm /var/db/postgres/data16/postmaster.opts
rm /var/db/postgres/data16/pg_internal.init 2>/dev/null

service postgresql start

systemctl stop postgresql
rm -rf /var/lib/postgresql/*
rm -rf /var/lib/pgsql/wal_archive/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/postgresql
    - /var/lib/pgsql/wal_archive/LAST_WALL_FILE+LAST_WALL_FILE.history

Seguimos los consejos de restauración de PostgreSQL :

rm /var/lib/postgresql/16/main/postmaster.pid
rm /var/lib/postgresql/16/main/postmaster.opts
rm /var/lib/postgresql/16/main/pg_internal.init 2>/dev/null

systemctl start postgresql

En la siguiente tabla podemos ver las opciones de restauración indicadas:

FreeBSD	Linux

Al arrancar PostgreSQL veremos tanto el FreeBSD como el Linux errores que pueden ser ignorados:

cp: /var/lib/pgsql/wal_archive/00000002.history: No such file or directory
cp: /var/lib/pgsql/wal_archive/000000010000000000000006: No such file or directory

En la documentación oficial de PostgreSQL también hablan de ello:

A normal recovery will end with a “file not found” message, the exact text of the error message depending upon your choice of restore_command.
You may also see an error message at the start of recovery for a file named something like 00000001.history.
This is also normal and does not indicate a problem in simple recovery situations

Consultamos los datos restaurados:

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

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'
 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-02 10:33:12.5319
  2 | Jane       | Smith     |  25 | 2024-11-02 10:33:12.555151
(2 rows)

PostgreSQL Python plugin PITR:

Antes de nada debemos saber que PITR no es compatible con “Instrucciones no transaccionales”:

BEGIN, COMMIT, ROLLBACK, CREATE DATABASE, DROP DATABASE, ALTER SYSTEM, CREATE TABLESPACE, DROP TABLESPACE, TRUNCATE TABLE...

Si se ejecuta alguna de estas instrucciones tendremos que restaurar a un backup “normal”, no podremos hacer uso del PITR.

Para restaurar un backup utilizando PITR debemos variar ligeramente el proceso, pero primero necesitamos entender como PostgreSQL va almacenando los datos en DATA_DIR y como archiva los WALs.

Como ya hemos comentado con anterioridad, las operaciones en PostgreSQL primero se ejecutan en el fichero DATA_DIR/pg_wal, luego en la base de datos y finalmente el DATA_DIR/pg_wal es archivado.

En la línea temporal del esquema de abajo podemos ver que los WALs archivados son los datos mas antiguos mientras que el DATA_DIR/pg_wal son los mas recientes.

En Temp1 se crea un backup mediante Bareos donde se copia DATA_DIR y los ficheros WAL archivados, acto seguido se eliminan los WAL archivados que se acaban de backupear mediante RunScript-After.

Estos WALs backupeados y eliminados son las operaciones que la DB ha realizado desde Start hasta Temp1-(DATA_DIR/pg_wal), de Temp1 a Temp2 ocurre exactamente igual.

Ahora en Temp2 alguien borra datos críticos y queremos restaurarlos, el backup fué tomado en Temp1 pero de Temp1 a Temp2 hay datos que no queremos perder y sabemos que esos datos están en los WAL archivados+DATA_DIR/pg_wal generados de Temp1 a Temp2.

Los pasos de restauración son:

Parar el servidor PostgreSQL.
Copiar los ficheros WAL del DATA_DIR.
Limpiar el DATA_DIR.
NO eliminar los ficheros de /var/lib/pgsql/wal_archive.
Restaurar el backup de DATA_DIR realizado en Temp1.
Restaurar el backup de los ficheros WAL del DATA_DIR copiado en el segundo paso.
Indicar un PITR justo antes de Temp2.
Pasar la DB a modo R/W.

service postgresql stop
rm -rf /root/pg_wal 2>/dev/null
cp -r /var/db/postgres/data16/pg_wal /root/
rm -rf /var/db/postgres/*

Restaurar desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/db/postgres

rm -rf /var/db/postgres/data16/pg_wal/*
cp -r /root/pg_wal/* /var/db/postgres/data16/pg_wal
chown -R postgres:postgres /var/db/postgres/data16/pg_wal/*

Indicamos la hora de restauración, mucho cuidado con las zonas horarias:

vi /var/db/postgres/data16/postgresql.conf

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

Arrancamos el servicio:

service postgresql start

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_timey habilitamos las escrituras en la DB.

vi /var/db/postgres/data16/postgresql.conf

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

Habilitamos las escrituras en la DB:

psql -U postgres -c "SELECT pg_wal_replay_resume()"

systemctl stop postgresql
rm -rf /root/pg_wal 2>/dev/null
cp -r /var/lib/postgresql/16/main/pg_wal /root/
rm -rf /var/lib/postgresql/*

Restaurar desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/postgresql

rm -rf /var/lib/postgresql/16/main/pg_wal/*
cp -r /root/pg_wal/* /var/lib/postgresql/16/main/pg_wal
chown -R postgres:postgres /var/lib/postgresql/16/main/pg_wal/*

Indicamos la hora de restauración, mucho cuidado con las zonas horarias:

vi /etc/postgresql/16/main/postgresql.conf

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

Arrancamos el servicio:

systemctl start postgresql

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_timey habilitamos las escrituras en la DB.

vi /etc/postgresql/16/main/postgresql.conf

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

Habilitamos las escrituras en la DB:

psql -U postgres -c "SELECT pg_wal_replay_resume()"

Pongamos un ejemplo práctico:

Sat Nov 2 12:23:04 CET 2024: Se hace un backup.

Sat Nov 2 12:36:01 CET 2024: Se insertan datos importantes.

su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('Joe', 'Macmillan', 40);\""

Sat Nov 2 12:40:01 CET 2024: Accidentalmente se eliminan todos los datos de la tabla.
```
su postgres -c "psql -d testkr0m -c \"DELETE FROM employees;\""
```

Seguimos los pasos de restauración indicando este PITR:

recovery_target_time = '2024-11-02 12:39:00.000'

Comprobamos los datos restaurados:

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'
 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-02 10:33:12.5319
  2 | Jane       | Smith     |  25 | 2024-11-02 10:33:12.555151
  3 | Joe        | Macmillan |  40 | 2024-11-02 12:36:01.579415
(3 rows)

PostgreSQL Python plugin PITR Timelines:

Al realizar restores PITR se introduce el concepto de Timeline , esto no es mas que una segunda línea temporal desde un restore PITR. PostgreSQL permite ir saltando de una Timeline a otra siempre y cuando se tengan los ficheros WAL de cada una de ellas, tan solo debemos indicar la fecha a la que restaurar y el Timeline en el que estaba ese punto.

Pongamos un ejemplo y se verá mas fácilemnte, imaginemos el siguiente escenario donde tenemos los siguientes registros:

su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('John', 'Doe', 30);\""
su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('Jane', 'Smith', 25);\""

Realizamos un backup en Temp1: Mon Nov 4 09:03:00 CET 2024

Seguimos realizando INSERTs: Mon Nov 4 09:05:41 CET 2024

su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('Joe', 'Macmillan', 40);\""

Segumios realizando INSERTs: Mon Nov 4 09:07:43 CET 2024

su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('Cameron', 'Howe', 23);\""

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
  4 | Cameron    | Howe      |  23 | 2024-11-04 09:07:43.140263
(4 rows)

Hasta que alguien accidentalmente borra todos los datos de la tabla en Temp2: Mon Nov 4 09:11:45 CET 2024

su postgres -c "psql -d testkr0m -c \"DELETE FROM employees;\""

Restauramos al punto PITR: 09:05:42-TL1 de donde nace la Timeline2:

Paramos el servicio:

service postgresql stop

Backupeamos el DATA_DIR/pg_wal de la Timeline1:

rm -rf /root/pg_wal_TL1 2>/dev/null
cp -r /var/db/postgres/data16/pg_wal /root/pg_wal_TL1

Limpiamos el directorio DATA_DIR:

rm -rf /var/db/postgres/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/db/postgres

Seguimos los consejos de restauración de PostgreSQL :

rm /var/db/postgres/data16/postmaster.pid
rm /var/db/postgres/data16/postmaster.opts
rm /var/db/postgres/data16/pg_internal.init 2>/dev/null

Restauramos los ficheros de DATA_DIR/pg_wal de la Timeline1:

rm -rf /var/db/postgres/data16/pg_wal/*
cp -r /root/pg_wal_TL1/* /var/db/postgres/data16/pg_wal
chown -R postgres:postgres /var/db/postgres/data16/pg_wal/*

Indicamos el punto hasta donde debe restaurar:

vi /var/db/postgres/data16/postgresql.conf

recovery_target_time = '2024-11-04 09:05:42.000'

NOTA: Cuando solo ha existido una Timeline no hace falta indicarla, con la fecha y la hora es suficiente.

Reiniciamos el servicio:

service postgresql start

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_time y habilitamos las escrituras en la DB.

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
(3 rows)

vi /var/db/postgres/data16/postgresql.conf

#recovery_target_time = '2024-11-04 09:05:42.000'

psql -U postgres -c "SELECT pg_wal_replay_resume()"

Consultamos la Timeline actual:

psql -U postgres -c "SELECT timeline_id FROM pg_control_checkpoint()"

 timeline_id
-------------
           2
(1 row)

Paramos el servicio:

systemctl stop postgresql

Backupeamos el DATA_DIR/pg_wal de la Timeline1:

rm -rf /root/pg_wal_TL1 2>/dev/null
cp -r /var/lib/postgresql/16/main/pg_wal /root/pg_wal_TL1

Limpiamos el directorio DATA_DIR:

rm -rf /var/lib/postgresql/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/postgresql

Seguimos los consejos de restauración de PostgreSQL :

rm /var/lib/postgresql/16/main/postmaster.pid
rm /var/lib/postgresql/16/main/postmaster.opts
rm /var/lib/postgresql/16/main/pg_internal.init 2>/dev/null

Restauramos los ficheros de DATA_DIR/pg_wal de la Timeline1:

rm -rf /var/lib/postgresql/16/main/pg_wal/*
cp -r /root/pg_wal_TL1/* /var/lib/postgresql/16/main/pg_wal
chown -R postgres:postgres /var/lib/postgresql/16/main/pg_wal/*

Indicamos el punto hasta donde debe restaurar:

vi /etc/postgresql/16/main/postgresql.conf

recovery_target_time = '2024-11-04 09:05:42.000'

NOTA: Cuando solo ha existido una Timeline no hace falta indicarla, con la fecha y la hora es suficiente.

Reiniciamos el servicio:

systemctl start postgresql

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_time y habilitamos las escrituras en la DB.

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
(3 rows)

vi /etc/postgresql/16/main/postgresql.conf

#recovery_target_time = '2024-11-04 09:05:42.000'

psql -U postgres -c "SELECT pg_wal_replay_resume()"

Consultamos la Timeline actual:

psql -U postgres -c "SELECT timeline_id FROM pg_control_checkpoint()"

 timeline_id
-------------
           2
(1 row)

Seguimos realizando INSERTs: Mon Nov 4 09:15:52 CET 2024

su postgres -c "psql -d testkr0m -c \"INSERT INTO employees (first_name, last_name, age) VALUES ('Gordon', 'Clark', 39);\""

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
 36 | Gordon     | Clark     |  39 | 2024-11-04 09:15:51.910027
(4 rows)

Pero nos damos cuenta de que Cameron no está por lo que decidimos volver a PITR2: 09:07:44-TL1 de donde nace la Timeline3.

Paramos el servicio:

service postgresql stop

Backupeamos el DATA_DIR/pg_wal de la Timeline2:

rm -rf /root/pg_wal_TL2 2>/dev/null
cp -r /var/db/postgres/data16/pg_wal /root/pg_wal_TL2

Limpiamos el directorio DATA_DIR:

rm -rf /var/db/postgres/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/db/postgres

Seguimos los consejos de restauración de PostgreSQL :

rm /var/db/postgres/data16/postmaster.pid
rm /var/db/postgres/data16/postmaster.opts
rm /var/db/postgres/data16/pg_internal.init 2>/dev/null

Restauramos los ficheros de DATA_DIR/pg_wal de la Timeline1:

rm -rf /var/db/postgres/data16/pg_wal/*
cp -r /root/pg_wal_TL1/* /var/db/postgres/data16/pg_wal
chown -R postgres:postgres /var/db/postgres/data16/pg_wal/*

Indicamos el punto hasta donde debe restaurar:

vi /var/db/postgres/data16/postgresql.conf

recovery_target_timeline = '1'
recovery_target_time = '2024-11-04 09:07:44.000'

Iniciamos el servicio:

service postgresql start

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_time y habilitamos las escrituras en la DB.

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
  4 | Cameron    | Howe      |  23 | 2024-11-04 09:07:43.140263
(4 rows)

vi /var/db/postgres/data16/postgresql.conf

#recovery_target_timeline = '1'
#recovery_target_time = '2024-11-04 09:07:44.000'

psql -U postgres -c "SELECT pg_wal_replay_resume()"

Consultamos la Timeline actual:

psql -U postgres -c "SELECT timeline_id FROM pg_control_checkpoint()"

 timeline_id
-------------
           3
(1 row)

Paramos el servicio:

systemctl stop postgresql

Backupeamos el DATA_DIR/pg_wal de la Timeline2:

rm -rf /root/pg_wal_TL2 2>/dev/null
cp -r /var/lib/postgresql/16/main/pg_wal /root/pg_wal_TL2

Limpiamos el directorio DATA_DIR:

rm -rf /var/lib/postgresql/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/postgresql

Seguimos los consejos de restauración de PostgreSQL :

rm /var/lib/postgresql/16/main/postmaster.pid
rm /var/lib/postgresql/16/main/postmaster.opts
rm /var/lib/postgresql/16/main/pg_internal.init 2>/dev/null

Restauramos los ficheros de DATA_DIR/pg_wal de la Timeline1:

rm -rf /var/lib/postgresql/16/main/pg_wal/*
cp -r /root/pg_wal_TL1/* /var/lib/postgresql/16/main/pg_wal
chown -R postgres:postgres /var/lib/postgresql/16/main/pg_wal/*

Indicamos el punto hasta donde debe restaurar:

vi /etc/postgresql/16/main/postgresql.conf

recovery_target_timeline = '1'
recovery_target_time = '2024-11-04 09:07:44.000'

Iniciamos el servicio:

systemctl start postgresql

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_time y habilitamos las escrituras en la DB.

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
  4 | Cameron    | Howe      |  23 | 2024-11-04 09:07:43.140263
(4 rows)

vi /etc/postgresql/16/main/postgresql.conf

#recovery_target_timeline = '1'
#recovery_target_time = '2024-11-04 09:07:44.000'

psql -U postgres -c "SELECT pg_wal_replay_resume()"

Consultamos la Timeline actual:

psql -U postgres -c "SELECT timeline_id FROM pg_control_checkpoint()"

 timeline_id
-------------
           3
(1 row)

Ahora vemos que preferimos seguir por la Timeline2, PITR3: Final-TL2.

Paramos el servicio:

service postgresql stop

Backupeamos el DATA_DIR/pg_wal de la Timeline3:

rm -rf /root/pg_wal_TL3 2>/dev/null
cp -r /var/db/postgres/data16/pg_wal /root/pg_wal_TL3

Limpiamos el directorio DATA_DIR:

rm -rf /var/db/postgres/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/db/postgres

Seguimos los consejos de restauración de PostgreSQL :

rm /var/db/postgres/data16/postmaster.pid
rm /var/db/postgres/data16/postmaster.opts
rm /var/db/postgres/data16/pg_internal.init 2>/dev/null

Restauramos los ficheros de DATA_DIR/pg_wal de la Timeline2:

rm -rf /var/db/postgres/data16/pg_wal/*
cp -r /root/pg_wal_TL2/* /var/db/postgres/data16/pg_wal
chown -R postgres:postgres /var/db/postgres/data16/pg_wal/*

Si queremos ir al final de una Timeline como es el caso, NO hay que indicar fecha, solo la Timeline, en caso contrario obtendremos errores como estos:

2024-11-04 08:46:11.187 CET [50052] LOG:  last completed transaction was at log time 2024-11-04 09:15:51.910801+01
2024-11-04 08:46:11.187 CET [50052] FATAL:  recovery ended before configured recovery target was reached

vi /var/db/postgres/data16/postgresql.conf

recovery_target_timeline = '2'

Reiniciamos el servicio:

service postgresql start

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_time.

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
 36 | Gordon     | Clark     |  39 | 2024-11-04 09:15:51.910027
(4 rows)

vi /var/db/postgres/data16/postgresql.conf

#recovery_target_timeline = '2'

NOTA: No hace falta ejecutar “SELECT pg_wal_replay_resume()” por haber restaurado hasta el final de la Timeline.

Paramos el servicio:

systemctl stop postgresql

Backupeamos el DATA_DIR/pg_wal de la Timeline3:

rm -rf /root/pg_wal_TL3 2>/dev/null
cp -r /var/lib/postgresql/16/main/pg_wal /root/pg_wal_TL3

Limpiamos el directorio DATA_DIR:

rm -rf /var/lib/postgresql/*

Restauramos desde Bareos: Restore

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/postgresql

Seguimos los consejos de restauración de PostgreSQL :

rm /var/lib/postgresql/16/main/postmaster.pid
rm /var/lib/postgresql/16/main/postmaster.opts
rm /var/lib/postgresql/16/main/pg_internal.init 2>/dev/null

Restauramos los ficheros de DATA_DIR/pg_wal de la Timeline2:

rm -rf /var/lib/postgresql/16/main/pg_wal/*
cp -r /root/pg_wal_TL2/* /var/lib/postgresql/16/main/pg_wal
chown -R postgres:postgres /var/lib/postgresql/16/main/pg_wal/*

Si queremos ir al final de una Timeline como es el caso, NO hay que indicar fecha, solo la Timeline, en caso contrario obtendremos errores como estos:

2024-11-04 08:46:11.187 CET [50052] LOG:  last completed transaction was at log time 2024-11-04 09:15:51.910801+01
2024-11-04 08:46:11.187 CET [50052] FATAL:  recovery ended before configured recovery target was reached

vi /etc/postgresql/16/main/postgresql.conf

recovery_target_timeline = '2'

Reiniciamos el servicio:

systemctl start postgresql

Comprobamos que se haya restaurado correctamente, si es así comentamos el recovery_target_time.

su postgres -c 'psql -d testkr0m -c "SELECT * FROM employees;"'

 id | first_name | last_name | age |         created_at
----+------------+-----------+-----+----------------------------
  1 | John       | Doe       |  30 | 2024-11-04 09:02:34.703267
  2 | Jane       | Smith     |  25 | 2024-11-04 09:02:34.722347
  3 | Joe        | Macmillan |  40 | 2024-11-04 09:05:41.786131
 36 | Gordon     | Clark     |  39 | 2024-11-04 09:15:51.910027
(4 rows)

vi /etc/postgresql/16/main/postgresql.conf

#recovery_target_timeline = '2'

NOTA: No hace falta ejecutar “SELECT pg_wal_replay_resume()” por haber restaurado hasta el final de la Timeline.

Backup Incremental standby server:

En la documentación de PostgreSQL podemos leer que para que los backups Incrementales funcionen el backup actual debe ser tomado existiendo un checkpoint posterior al backup anterior, esto siempre pasa en un primary ya que el propio backup lo provoca.

Pero en un standby con poca actividad puede que no se haya generado un checkpoint nuevo desde el último backup, provocando el fallo del backup Incremental.

An incremental backup is only possible if replay would begin from a later checkpoint than for the previous backup upon which it depends.
If you take the incremental backup on the primary, this condition is always satisfied, because each backup triggers a new checkpoint. 
On a standby, replay begins from the most recent restartpoint. Therefore, an incremental backup of a standby server can fail if there has been
very little activity since the previous backup, since no new restartpoint might have been created.

Bareos mostrará este tipo de errores:

BareosClient2-fd JobId 80: Warning: python3-fd-mod: Could not get current LSN, last LSN was: 0/8000498: {'S': 'ERROR', 'V': 'ERROR', 'C': '55000',
'M': 'recovery is in progress', 'H': 'WAL control functions cannot be executed during recovery.', 'F': 'xlogfuncs.c', 'L': '287', 'R': 
'pg_current_wal_lsn'}

Este problema solo afecta a los modos de backup que utilizan el archivado de ficheros WAL:

Postgres Python plugin.
Postgres Python plugin PITR.
Postgres Python plugin PITR Timelines.

Tenemos dos opciones:

Hacer solo backups Full ya sea en el primary o en el standby.
Tener dos schedules en Bareos, uno que haga backups Full e Incremental y otro solo con Full. Según sea el servidor primary o standby configurar el job para que utilice un schedule u otro. Pero esto implica que si se hace un cambio de role, tener que reconfigurar el sistema de backups.

Debug:

Para un debug de Bareos podemos consultar el artículo sobre Bareos, sección Debug

Fichero de configuración:
A veces no vemos ciertos cambios aplicados en la base de datos simplemente porque no estamos modificando el fichero correcto, con SHOW config_file podemos ver el fichero vigente.

su postgres -c 'psql -d testkr0m -c "SHOW config_file;"'

               config_file
-----------------------------------------
 /var/db/postgres/data16/postgresql.conf
(1 row)

Habilitar logs:

mkdir /var/log/postgresql
chown postgres:postgres /var/log/postgresql

vi /var/db/postgres/data16/postgresql.conf

vi /etc/postgresql/16/main/postgresql.conf

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 10MB

Reiniciamos PostgreSQL:

service postgresql restart

systemctl restart postgresql

tail -f /var/log/postgresql/postgresql.log

PITR:
Hay que tener en cuenta que la restauración PITR no funciona si se ejecutan “Instrucciones no transaccionales”.

BEGIN, COMMIT, ROLLBACK, CREATE DATABASE, DROP DATABASE, ALTER SYSTEM, CREATE TABLESPACE, DROP TABLESPACE, TRUNCATE TABLE...

Con DROP DATABASE veremos el siguiente error:

psql -U postgres -d testkr0m -c "SELECT * FROM employees"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  cannot connect to invalid database "testkr0m"
HINT:  Use DROP DATABASE to drop invalid databases.

Con DROP/TRUNCATE TABLEsimplemente la query no terminará nunca:

psql -U postgres -d testkr0m -c "SELECT * FROM employees"
Nunca termina

Añálisis logs:
Cuando se hacen restauraciones PITR puede resultar útil cierta información de los logs.

Cuando se realizó el backup:

grep 'database system was interrupted' /var/log/postgresql/postgresql*.log|tail -n 1|grep 'database system was interrupted'

PITR donde restaurar:

grep 'starting point-in-time recovery to ' /var/log/postgresql/postgresql*.log|tail -n 1|grep 'starting point-in-time recovery to '

Punto de parada de la lectura de WALs:

grep 'recovery stopping before commit of transaction' /var/log/postgresql/postgresql*.log|tail -n 1|grep 'recovery stopping before commit of transaction'

Dump de los WALs:
Obtener las instrucciones contenidas en los ficheros WAL puede darnos alguna pista de porque se están ejecutando ciertas instrucciones en la base de datos.

/usr/local/bin/pg_waldump /var/lib/pgsql/wal_archive/000000010000000000000002

/usr/lib/postgresql/16/bin/pg_waldump /var/lib/pgsql/wal_archive/000000030000000000000007

Encontrar DELETEs en los WAL files:

for WAL_FILE in /root/pg_wal/*; do echo "WAL: $WAL_FILE" && /usr/local/bin/pg_waldump $WAL_FILE 2>/dev/null|grep DELETE; done
for WAL_FILE in /var/db/postgres/data16/pg_wal/*; do echo "WAL: $WAL_FILE" && /usr/local/bin/pg_waldump $WAL_FILE 2>/dev/null|grep DELETE; done
for WAL_FILE in /var/lib/pgsql/wal_archive/*; do echo "WAL: $WAL_FILE" && /usr/local/bin/pg_waldump $WAL_FILE 2>/dev/null|grep DELETE; done

for WAL_FILE in /root/pg_wal/*; do echo "WAL: $WAL_FILE" && /usr/lib/postgresql/16/bin/pg_waldump $WAL_FILE 2>/dev/null|grep DELETE; done
for WAL_FILE in /var/lib/postgresql/16/main/pg_wal/*; do echo "WAL: $WAL_FILE" && /usr/lib/postgresql/16/bin/pg_waldump $WAL_FILE 2>/dev/null|grep DELETE; done
for WAL_FILE in /var/lib/pgsql/wal_archive/*; do echo "WAL: $WAL_FILE" && /usr/lib/postgresql/16/bin/pg_waldump $WAL_FILE 2>/dev/null|grep DELETE; done

Grepear los “nombres de WAL con DELETES” del paso anterior en los logs de PostgreSQLpara ver si los ejecuta por error.

Ficheros WAL faltantes:
Podemos comprobar mas o menos que no nos falte ningún WAL al restaurar, si no hay huecos seguramente estén todos los ficheros necesarios:

ls -la /root/pg_wal*
ls -la /var/lib/postgresql/16/main/pg_wal
ls -la /var/lib/postgresql/16/main/pg_wal/archive_status
ls -la /var/lib/pgsql/wal_archive/

ls -la /root/pg_wal*
ls -la /var/db/postgres/data16/pg_wal/
ls -la /var/db/postgres/data16/pg_wal/archive_status
ls -la /var/lib/pgsql/wal_archive/

Integridad WALs:

for f in /var/lib/pgsql/wal_archive/[0-9A-F][0-9A-F]*[^.backup][^.history]; do
    /usr/local/bin/pg_waldump $f > /dev/null 2>&1
    if [ $? -eq 0 ]; then
        echo "$f OK"
    else
        echo "$f CORRUPTED"
    fi
done

for f in /var/lib/pgsql/wal_archive/[0-9A-F][0-9A-F]*[^.backup][^.history]; do
    /usr/lib/postgresql/16/bin/pg_waldump $f > /dev/null 2>&1
    if [ $? -eq 0 ]; then
        echo "$f OK"
    else
        echo "$f CORRUPTED"
    fi
done

Timelines:
Si queremos ir al final de una Timeline como es el caso, NO hay que indicar fecha, solo la Timeline, en caso contrario obtendremos errores como estos:

2024-11-04 08:46:11.187 CET [50052] LOG:  last completed transaction was at log time 2024-11-04 09:15:51.910801+01
2024-11-04 08:46:11.187 CET [50052] FATAL:  recovery ended before configured recovery target was reached

Podemos consultar la Timeline actual:

psql -U postgres -c "SELECT timeline_id FROM pg_control_checkpoint()"

También podemos ver los puntos de divergencia en las Timelines:

ls -la /var/db/postgres/data16/pg_wal/*.history
cat /var/db/postgres/data16/pg_wal/XX.history

ls -la /var/lib/pgsql/wal_archive/*.history
cat /var/lib/pgsql/wal_archive/XX.history

ls -la /var/lib/postgresql/16/main/pg_wal/*.history
cat /var/lib/postgresql/16/main/pg_wal/XX.history

ls -la /var/lib/pgsql/wal_archive/*.history
cat /var/lib/pgsql/wal_archive/XX.history

Servidor sin espacio:
Lo primero será eliminar los ficheros WALs archivados, estos no son estrictamente necesarios para el funcionamiento de la DB, si hace falta restaurar por PITR hasta algún punto de esos ficheros, no será posible pero al menos la DB seguirá funcionando y siempre podremos restaurar hasta el último backup de Bareos(ya sea Full o Incremental).

service postgresql stop
rm -f /var/lib/pgsql/wal_archive/*
service postgresql start

systemctl stop postgresql
rm -f /var/lib/pgsql/wal_archive/*
systemctl start postgresql

Otra posibilidad para el problema del espacio sería comprimir los WALs archivados :

archive_command = 'gzip < %p > /mnt/server/archivedir/%f.gz'
restore_command = 'gunzip < /mnt/server/archivedir/%f.gz > %p'

WAL paths:
No es posible restaurar backups físicos en un servidor con PATHs distintos a los que tenía configurado el servidor desde el que se realizó el backup:

CREATE TABLESPACE commands are WAL-logged with the literal absolute path, and will therefore be replayed as tablespace creations with the same absolute path.

Community:
https://discord.com/channels/710918545906597938/710918545906597941