Database error message after a long run backup job - ERROR: relation "batch" does not exist

[Marcio Feldmann]

I've been using Bareos for a short time, moving backups from Bacula to it. It's working fine, writing files to an S3 Bucket without issues.

When I added my File Server to be backed up by Bareos, at the end of a long run Full Backup Job (about 3 TB in space) it shows this error below. I tried lots of changes, but without fixing it.

I also looked for that over the Internet and found the same issue from Bacula 14 years ago, but couldn't find a solution.

My Postgres 16 Database server has a lot of free space and does not have any restrictions on the pg_temp, so I hope someone here has any idea to solve this problem.

```

bareos-dir JobId 1439: Fatal error: cats/sql_create.cc:800 Fill Path table Query failed: INSERT INTO Path (Path) SELECT a.Path FROM ( SELECT DISTINCT Path FROM batch ) AS a WHERE NOT EXISTS ( SELECT Path FROM Path WHERE Path = a.Path ) : ERR=ERROR: relation "batch" does not exist
LINE 1: ...h) SELECT a.Path FROM ( SELECT DISTINCT Path FROM batch ) AS...
```

[Andreas Rogge]

Am 12.06.24 um 00:57 schrieb Marcio Feldmann:
> ```

> bareos-dir JobId 1439: Fatal error: cats/sql_create.cc:800 Fill Path
> table Query failed: INSERT INTO Path (Path) SELECT a.Path FROM ( SELECT
> DISTINCT Path FROM batch ) AS a WHERE NOT EXISTS ( SELECT Path FROM Path
> WHERE Path = a.Path ) : ERR=ERROR: relation "batch" does not exist
> LINE 1: ...h) SELECT a.Path FROM ( SELECT DISTINCT Path FROM batch ) AS...
> ```

The table "batch" is a temporary per-job table. When Bareos loses the
connection to the database, it will automatically re-establish the
connection. However, all temporary tables will be gone and you will then
eventually run into the issue you're seeing.
Did you - by any chance - restart the database server during the backup
job or was there another reason why the connection would have been broken?

Best Regards,
Andreas
--
Andreas Rogge andrea...@bareos.com
Bareos GmbH & Co. KG Phone: +49 221-630693-86
http://www.bareos.com

Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646
Komplementär: Bareos Verwaltungs-GmbH
Geschäftsführer: Stephan Dühr, Jörg Steffens, Philipp Storz

[Marcio Feldmann]

Hi Andreas,

Thanks for your explanation around the way Bareos works with the Database. Because of it I was able to fix the problem and now it's working!

It was an architectural problem from my side. I'll briefly explain it:

I have an EC2 Instance running Bareos and another EC2 Instance running Postgres. For recovery reasons, I placed an Load Balancer between Bareos and Postgres, and this Load Balancer was causing that issue, because it was shutting down the current session and creating another TCP session during the backup job.
I didn't experience that problem in other backup jobs because they are much faster than this one, but with your explanation, I removed this LB and let Bareos connect directly to the Postgres EC2 instance and Voilá!

Thank you so much for your help!!!