Continuing with the series of articles on Bareos , we’ll look at how to perform PostgreSQL backups on FreeBSD and Linux.

The article is composed of the following sections:

• Introduction
• FreeBSD/Linux Scenario
• PostgreSQL Installation
• RunScript
• Bpipe
• Postgres Python plugin
• Postgres Python plugin PITR
• Postgres Python plugin PITR Timelines
• Incremental Backup Standby Server
• Debug

Introduction:

Bareos allows us to perform PostgreSQL backups in three different ways:

• RunScript (logical backup): pg_dumpall dumps the database and copies the dump, requiring double the storage capacity.
• Bpipe (logical backup): pg_dumpall with the backup streamed to Bareos, and the restoration can either be to a file or directly back into the database.
• Postgres Python plugin (physical backup): Physical backup of the DATA_DIR/pg_wal files, allowing for PITR (Point in Time Recovery).

FreeBSD/Linux Scenario:

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

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

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

bastille stop BareosClient
bastille start BareosClient

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

PostgreSQL Installation:

We proceed with the installation of the package. It’s preferable to install version 16 of PostgreSQL, as version 17 is only partially compatible with the Bareos Python plugin, allowing only Full backups.

pkg install postgresql16-server

sysrc postgresql_enable=yes

service postgresql initdb

service postgresql start

apt install postgresql postgresql-contrib

We create a database and a table:

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);"'

We insert data and check the results:

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:

The simplest way to perform a backup is with RunScript . You simply need to configure a dump command to run when the job executes, copy the generated file, and delete the dump.

On the Director
We create a FileSet that will back up /etc, /root, /home, and the PostgreSQL configuration files as well as the 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"
     }
 }

We modify the FileSet and add RunScript-Before and RunScript-After to the job.

NOTE: When Bareos runs the dump script on FreeBSD, it seems unable to locate the path to the pg_dumpall binary, so we must specify the full path. On Linux, this is not an issue.

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 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
      Command = "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'"
  }
}

We restart the Director:

service bareos-dir restart

systemctl restart bareos-dir

We proceed to run the backup from the web interface by specifying the FileSet: Jobs -> Run

It should complete without issues:

We delete the database from the PostgreSQL CLI:

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)

We proceed with the restore: Restore
It is very important to disable the “Merge all client filesets” option, as otherwise the file tree on the right side will be a view of all combined backups for this client. We only want the files from the specified backup.

The restore job should complete successfully:

And on the server, we should have the backup file:

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

We load the dump into the database:

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

We check the restored data:

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:

With Bpipe , we can dump the database and stream the dump directly to Bareos, so there is no need to store the backup file locally.

This plugin is installed by default with the file daemon on both FreeBSD and 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

On the client:

For Bpipe to work, we need to reconfigure the plugin-related section in the file daemon:

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 file daemon plugins (*-fd.so) from the "Plugin Directory".
  #
  # FreeBSD:
  Plugin Directory = "/usr/local/lib/bareos/plugins"
  Plugin Names = "bpipe"
}

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 file daemon plugins (*-fd.so) from the "Plugin Directory".
  #
  # Linux:
  Plugin Directory = "/usr/lib/bareos/plugins"
  Plugin Names = "bpipe"
}

systemctl restart bareos-fd

Although the backup is streamed, a “virtual” directory is required so Bareos can write/read the backup.

mkdir /POSTGRESQL

On the Director:
Bpipe requires three parameters:

• file: Path to a file that will be used temporarily as a pipe. When restoring, we must specify this file.
• reader: Command to execute to obtain the output that Bareos will read and save in the backup.
• writer: Command to which the restore output will be piped.

We create a FileSet with the Bpipe configuration.

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 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"
    }
}

NOTE: We back up with --clean. This option adds SQL instructions in the dump to delete databases, roles, and other objects before recreating them.

We update the FileSet in the job, and there’s no need for any RunScript-Before or 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"
}

Restart the Director:

service bareos-dir restart

systemctl restart bareos-dir

We proceed to perform the backup from the web interface by selecting the FileSet: Jobs -> Run

It should complete without issues:

We delete the database from the PostgreSQL CLI:

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)

We proceed with the restore: Restore
It’s important to disable the “Merge all client filesets” option because, otherwise, the right-side file tree will be a combined view of all the client’s backups. We only want the files from the indicated backup. We select the virtual file /POSTGRESQL/dump.sql in the right-hand tree.

The restore job should complete successfully:

If we check PostgreSQL, the database should be restored:

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)

It may also be useful to pipe the restore output to a file instead of directly to the PostgreSQL CLI to save the backup to a file without touching the DB:

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

On the Director:
We create a new FileSet with another Bpipe configuration.

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 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'"
    }
}

We change the 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"
}

Restart the Director:

service bareos-dir restart

systemctl restart bareos-dir 

We proceed to perform the backup from the web interface by selecting the FileSet, and we need to perform a new backup since the restore uses the parameters from the time of the backup: Jobs -> Run

It should complete without issues:

We delete the database from the PostgreSQL CLI:

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)

We proceed with the restore: Restore
It’s important to disable the “Merge all client filesets” option because, otherwise, the right-side file tree will be a combined view of all the client’s backups. We only want the files from the indicated backup. We select the virtual file /POSTGRESQL/dump.sql in the right-hand tree.

The restore job should complete successfully:

But this time, it didn’t restore the backup in the DB. Instead, it generated the file in the directory specified in the sys-postgresBpipeFile FileSet config:

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

We load the file into the DB:

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

We check the restored data:

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)

Be careful, as when restoring, the Bpipe parameters are applied from the time the backup was taken, not the current FileSet parameters (even if the director is restarted) when performing the restore. This information was saved in the catalog when the backup is made.

The FileSet parameters of a backup can be checked from the bconsole in the 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'"
  }
}
 |

If at any time we modify the command indicated in the FileSet, we can overwrite it from the web interface. In this case, I want to restore the dump with a different name, postgres2.sql. However, if we paste the parameters as they are, it will not parse the arguments correctly:

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

We will get an error:

Invalid keyword: postgres

However, if we prepare a script, it won’t complain.
On the client:

vi dumpToFile.sh

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

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

chmod 700 dumpToFile.sh

We can also prepare the script for direct output to the 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

And in the web interface, we only pass the writer since we’re restoring:

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

We can see the restored file:

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

Bpipe could also be used to back up remote PostgreSQL databases from a server with access to that database:

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:

The PostgreSQL Python plugin allows for physical backups, providing faster dump and restore processes. However, it requires more storage on the Bareos server and does not support restoring only parts of the database.

The only drawback is that, for now (2024/10/29), it does not support PostgreSQL 17, even though they are working on it . In my tests, Full backups worked without issues, but Incremental backups failed due to changes in the management of WAL files. The error shown with version 17 is as follows:

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

Fortunately, the plugin works perfectly with PostgreSQL 16, which is the default version installed on Ubuntu 24.04, and it is also available in binary form on FreeBSD without issues.

Another point to note is that this plugin does not support Differential backups, apparently because PostgreSQL itself does not support them :

Differential backups are not supported! Only Full and Incremental

Additionally, if a major version update occurs, previous backups won’t be compatible with the updated version. This requires resetting the backup history.

The main advantages of this plugin are:

• Faster backup speeds due to being a physical backup.
• It does not need double disk space on the PostgreSQL server.
• Point-In-Time-Restore (PITR), allowing restoration to a specific date and time.
• If using Incremental backups, large databases can be backed up since only the new WAL files are copied.

Before starting, we should understand some concepts:

• On every PostgreSQL server, queries are always written to a WAL file before they are executed in the DB (whether or not replication or backups are configured); this file is written to the DATA_DIR/pg_wal directory.
• PostgreSQL generates WAL files as a result of pending operations in the database. If there is an abrupt shutdown, the operations are read and applied.
• These files can be used to perform Incremental backups, meaning a base backup is created, followed by applying WAL files, allowing large databases to be backed up by only copying WAL files instead of the entire database. However, keep in mind that the longer the interval between Full backups, the more WAL files will need to be applied during restoration.
• Incremental backups are only useful for large databases where only a small portion is modified. For small databases or large databases with many changes, PostgreSQL recommends Full backups .
• To store the WAL(DATA_DIR/pg_wal) files, the archive_command must be configured, which simply copies the DATA_DIR/pg_wal file to another directory before it’s reused, thus maintaining a historical log of operations in the DB.
• The DATA_DIR is restored on its own, and archived WAL files are restored separately, the latter using the restore_command configured in PostgreSQL.
• If we want to restore up to a specific PITR, we need to indicate it with the parameter: recovery_target_time = ‘yyyy-mm-dd hh:mm:ss.sss’

These images clarify the difference between DATA_DIR/pg_wal and archived WALs.

DATA_DIR/pg_wal	Archived WALs

The backup process will vary depending on the backup type:

Full backup	Incremental Backup

Meanwhile, the restoration process is as follows:

You can also see its operation in this video.

For the plugin to work, WAL archiving must be enabled on the PostgreSQL servers.

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 doesn't 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'

The archive_command has several parameters:

• %p: The path of the file to archive, which is relative to PostgreSQL’s DATA_DIR.
• %f: Only the name of the file to archive.

NOTE: If we want to use the % character in the command, we must escape it by using %%.

Another important point is that the archive command should only return 0 when it has completed successfully. If it returns 0, PostgreSQL will assume it was archived correctly, and the WAL(DATA_DIR/pg_wal) file will be deleted or reused. If the command does not return 0, PostgreSQL will assume it was not archived correctly and will keep retrying until successful. This is risky because WAL(DATA_DIR/pg_wal) files will never be recycled, consuming large amounts of space on the server and potentially disabling PostgreSQL.

If the archiving speed is insufficient and we’re doing Incremental backups, the margin for data loss in a disaster is greater since only the archived WAL files are being copied. This archiving delay also means that the WAL(DATA_DIR/pg_wal) directory will grow.
In such cases, the archive command can be replaced with an archive_library written in C, providing much higher performance than a shell script.

If we want to stop archiving temporarily, we can set archive_command to empty ‘’, causing WAL files that should be archived to accumulate in DATA_DIR/pg_wal until archive_command is reassigned.

Client:
Install the plugin:

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

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/

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

Specify the plugin to use in the FileDaemon configuration:

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"
}

Restart the FileDaemon:

service bareos-fd restart

systemctl restart bareos-fd

Director:
Create a FileSet named sys-postgresPlugin to back up the /etc, /root, /home directories, the PostgreSQL configuration files, and the database using the PostgreSQL plugin.

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=/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"
    }
}

You can find the list of supported plugin parameters on the official Bareos website .

The ignore_subdirs parameter can be determined from the PostgreSQL documentation .

The start_fast parameter can be useful, especially while testing:

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 slow any concurrently executing queries. Default: False

Another option, if the server has low activity, is to configure archive_timeout to force a WAL segment change every X period. However, note that these forced WAL files will occupy the same space as regular WAL files, so setting a very low value could easily exhaust disk space on the PostgreSQL server.

You can read the recommendation from the official PostgreSQL documentation :

archive_timeout settings of a minute or so are usually reasonable.

It’s important to note that when Bareos performs a backup, it copies both the DATA_DIR and the archived WALs. Once the backup is completed, these archived WALs are no longer necessary, but Bareos does not delete them automatically.

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

The idea is to create a RunScript-Before to list existing WALs before the backup and a RunScript-After to delete the WALs from the list generated by the RunScript-Before.

Change the FileSet to sys-postgresPlugin and set up RunScript-Before and RunScript-After scripts:

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"
     }
 }

Restart the Director:

service bareos-dir restart

systemctl restart bareos-dir   

Client:
The script to list WALs is as follows:

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

The script to clear the WALs is as follows:

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

Set the correct permissions:

chmod 700 listWALs.sh
chmod 700 clearWALs.sh

PostgreSQL allows defining access rules for databases. These are defined in the pg_hba.conf configuration file:

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

• Type:
  • local: Access for local system users.
  • host: Access for remote system users.
• Database:
  • DB_NAME: The name of the database the user can access.
• User:
  • USERNAME: Username.
• Address:
  • ADDRESS: IP address or hostname from where the user will connect if not local.
• Method:
  • trust: Access for any local user without authentication (no password required). The only requirement is that the user must connect with an existing PostgreSQL ROLE, default is postgres.
  • peer: Access for any local user without authentication (no password required). However, the system username must match the name of the ROLE with which they connect.
  • scram-sha-256: Access with authentication.

By default, PostgreSQL generates the ROLE postgres. Here is an example of a local user with the trust method, specifying an existing ROLE:

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=#

Now an example with the peer method, also with an existing ROLE:

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"

If we switch to the postgres user, it defaults to connecting with the ROLE postgres, so the username and ROLE match, allowing access to the DB:

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

postgres=#

In FreeBSD, it defaults to allowing access for any local user using trust, provided they connect with an existing ROLE. Since we configured the FileSet in the director with the plugin db_user=postgres, it will use the root user running Bareos and the ROLE postgres specified in the plugin.

In Linux, however, only local users are allowed access using peer, which requires the system username to match the ROLE, but root does not match postgres.

To make it work, we need to allow root to connect with the ROLE postgres by modifying the PostgreSQL configuration file as follows:

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

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

Restart PostgreSQL:

systemctl restart postgresql

We can verify access from the root shell as Bareos will do:

psql -U postgres -h /tmp

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

Client:
Set the command to execute for restoring the DB from WAL files.

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

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

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

Restart PostgreSQL:

service postgresql restart

systemctl restart postgresql

Perform a backup from the web interface, specifying the FileSet: Jobs -> Run

It should complete without issues:

Delete the database from the PostgreSQL CLI:

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)

Proceed with restoration; it’s very important to disable the “Merge all client filesets” option in the Bareos restoration, as otherwise, the file tree on the right will show a combined view of all this client’s backups, and we only want files from the specified backup.

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

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

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/*

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

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

The following table shows the restoration options:

FreeBSD	Linux

When starting PostgreSQL, errors may appear on both FreeBSD and Linux, but these can be ignored:

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

The official PostgreSQL documentation also mentions this:

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

Verify the restored data:

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:

First, it’s important to know that PITR is not compatible with “Non-transactional instructions”:

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

If any of these instructions are executed, we’ll need to restore a “normal” backup and won’t be able to use PITR.

To restore a backup using PITR, we need to slightly modify the process. However, first, we must understand how PostgreSQL stores data in DATA_DIR and how it archives WALs.

As previously mentioned, operations in PostgreSQL are first executed in the DATA_DIR/pg_wal file, then in the database, and finally, the DATA_DIR/pg_wal is archived.

In the timeline of the diagram below, the archived WALs represent the oldest data, while the DATA_DIR/pg_wal files are the most recent.

In Temp1, a backup is created using Bareos, copying DATA_DIR and the archived WAL files. The archived WALs that were backed up are then deleted via RunScript-After.

These backed-up and deleted WALs represent operations that the DB performed from Start to Temp1-(DATA_DIR/pg_wal). The process from Temp1 to Temp2 follows the same pattern.

Now, in Temp2, someone accidentally deletes critical data, and we want to restore it. The backup was taken at Temp1, but there is data from Temp1 to Temp2 that we don’t want to lose, and we know that this data is in the archived WALs+DATA_DIR/pg_wal files generated from Temp1 to Temp2.

The restoration steps are:

• Stop the PostgreSQL server.
• Copy the WAL files from DATA_DIR.
• Clear the DATA_DIR.
• DO NOT delete the /var/lib/pgsql/wal_archive files.
• Restore the DATA_DIR backup taken at Temp1.
• Restore the backup of WAL files from the DATA_DIR copied in the second step.
• Specify a PITR just before Temp2.
• Set the database to R/W mode.

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/*

- 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/*

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

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

service postgresql start

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

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

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/*

- 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/*

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

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

systemctl start postgresql

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

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

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

Here’s a practical example:

• Sat Nov 2 12:23:04 CET 2024: A backup is taken.
• Sat Nov 2 12:36:01 CET 2024: Important data is inserted.

  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: All data in the table is accidentally deleted.

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

Follow the restoration steps, specifying this PITR:

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

Verify the restored data:

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:

When performing PITR restores, a Timeline concept is introduced. This represents an alternate timeline after a PITR restore. PostgreSQL allows jumping between Timelines as long as the WAL files for each one are available. You only need to specify the restore date and the Timeline that contains that point.

Let’s go through an example to clarify this concept, imagining the following scenario where we have these records:

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);\""

We take a backup at Temp1: Mon Nov 4 09:03:00 CET 2024

Continuing with 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);\""

More 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)

Until someone accidentally deletes all the table data at Temp2: Mon Nov 4 09:11:45 CET 2024

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

We restore to PITR point: 09:05:42-TL1, creating Timeline2:

service postgresql stop

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

rm -rf /var/db/postgres/*

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

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

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/*

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

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

service postgresql start

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()"

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

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

systemctl stop postgresql

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

rm -rf /var/lib/postgresql/*

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/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

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/*

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

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

systemctl start postgresql

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()"

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

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

We continue making 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)

But we realize that Cameron is missing, so we decide to return to PITR2: 09:07:44-TL1 from where Timeline3 originates.

service postgresql stop

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

rm -rf /var/db/postgres/*

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

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

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/*

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

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

service postgresql start

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()"

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

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

systemctl stop postgresql

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

rm -rf /var/lib/postgresql/*

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/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

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/*

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

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

systemctl start postgresql

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()"

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

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

Now we see that we prefer to continue with Timeline2, PITR3: Final-TL2.

service postgresql stop

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

rm -rf /var/db/postgres/*

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

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

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/*

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'

service postgresql start

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'

systemctl stop postgresql

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

rm -rf /var/lib/postgresql/*

- Merge all client filesets: No
- Restore location on client: /
    - /var/lib/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

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/*

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'

systemctl start postgresql

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'

Incremental Backup on Standby Server:

In the PostgreSQL documentation , we can read that for Incremental backups to work, the current backup must be taken after a checkpoint following the previous backup. This always happens on a primary since each backup triggers a checkpoint.

However, on a standby with low activity, a new checkpoint might not have been generated since the last backup, causing the Incremental backup to fail.

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 will display errors like:

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'}

This issue only affects backup modes that use WAL file archiving:

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

We have two options:

• Only do Full backups on either the primary or standby.
• Use two Bareos schedules: one for Full and Incremental backups and one only for Full. Depending on whether the server is primary or standby, configure the job to use one schedule or the other. However, this requires reconfiguring the backup system if a role change occurs.

Debug:

For Bareos debugging, see the Bareos article, Debug section .

Configuration File:
Sometimes, changes are not reflected in the database simply because we are not modifying the correct file. Use SHOW config_file to view the active configuration file.

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

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

Enable 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

Restart PostgreSQL:

service postgresql restart

systemctl restart postgresql

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

PITR:
Be aware that PITR restoration does not work if “Non-transactional instructions” are executed.

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

For DROP DATABASE, the following error will appear:

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.

With DROP/TRUNCATE TABLE, the query simply never completes:

psql -U postgres -d testkr0m -c "SELECT * FROM employees"
It never completes

Log Analysis:
When performing PITR restorations, certain log information may be helpful.

• When the backup was performed:

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

• PITR restoration point:

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

• WAL reading stopping point:

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

Dump WALs:
Obtaining instructions from WAL files can provide clues about why certain database commands are executed.

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

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

Finding DELETEs in 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

Grep the “WAL names with DELETES” from the previous step in PostgreSQL logs to see if they were executed by mistake.

Missing WAL Files:
We can verify that no WAL files are missing during restoration. If there are no gaps, all necessary files are likely present.

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/

WAL Integrity:

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:
If we want to go to the end of a Timeline, as in this case, there’s no need to specify a date, just the Timeline. Otherwise, errors like these might occur:

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

We can check the current Timeline:

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

We can also view the divergence points in the 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

Server Out of Space:
The first step is to delete archived WAL files, as they are not strictly necessary for the database to function. If PITR restoration to a specific point in those files is required, it won’t be possible, but the database will remain operational. Restoration to the latest Bareos backup (either Full or Incremental) is still possible.

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

Another option to address space issues is to compress archived WAL files :

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

WAL Paths:
Physical backups cannot be restored on a server with different paths than those configured on the server where the backup was taken:

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