#1113777 Bacula update script /usr/share/bacula-director/update_postgresql_tables is broken

Package:
bacula-director-pgsql
Source:
bacula-director-pgsql
Submitter:
Joop Boonen
Date:
2026-10-09 09:39:02 UTC
Severity:
normal
#1113777#5
Date:
2025-09-02 13:20:45 UTC
From:
To:
 Version: 15.0.3-3

When I invoke

 sudo -u postgres /usr/share/bacula-director/update_postgresql_tables -d <bacula_database>
The Baculadatabase isnt' upgraded due to a few "errors" in the script.



The First error the psql dump is created without a -q, this causes that header data ends up in the file. Because of this the restore fails.



Secondly the postgresql do $$ is not correctly escaped. Therefore bash replaces the $$ to the current process id.








  I suggest the following patch:
#1113777#10
Date:
2025-09-02 15:45:55 UTC
From:
To:
Hi Joop,

thanks for your report. We have already addressed this in version
15.0.3-4, which unfortunately is not in the current Debian stable
release (c.f. bug #1108272).

We highly recommend letting dbconfig-common handle the databases.

Regards

Carsten


Joop Boonen <joop.boonen@credativ.de> writes:
#1113777#25
Date:
2025-09-02 16:03:07 UTC
From:
To:
Hi Joop,

actually, we fixed the escaping, but this is the first time we hear
about the "-q" parameter problem.

We'll need to investigate that.

Regards

Carsten


Carsten Leonhardt <leo@debian.org> writes:
#1113777#36
Date:
2026-10-09 08:52:45 UTC
From:
To:
Hi Carsten,

I had the same problems as Joop, here, too, the psql -q helped.

See this post for another one who had the problem. Strangely enough I could
not find any hint in the bacula community repositories.

Why does it take so long (over a year)

Regards,
Sebastian
-d <bacula_database>
that header data ends up in the file. Because of this the restore fails.
replaces the $$ to the current process id.
#1113777#41
Date:
2026-10-09 09:37:31 UTC
From:
To:
Sorry for the uncomplete message.

I also tried to upgrade a version 16 DB dump from bacula 9.6.7 (debian 12)
to version 1025 (bacula 15.0.x) by using the script. I also had to add the
-q parameter to the two psql commands.

I found the solution on this website:
https://dan.langille.org/2023/11/23/bacula-moving-from-9-x-to-13-x-and-upgrading-the-postgresql-database/
Apparently he had the same problems.

Do you have any idea, when your patched version of the package  15.0.3-4 /
15.0.3-5 will be accepted in stable?

Here is my patch:
189c189
<     psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "set
work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to
off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name,
DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO
STDOUT' | $COMP -1 -c > file1017.data
---
work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to
off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name,
DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO
STDOUT' | $COMP -1 -c > file1017.data
222c222
<     echo "Loading the File table from $PWD/file.$$.data..."
---
227c227
<       echo "Inserting File data from file.$$.data failed."
---
288c288
<     psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "set
work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to
off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name,
DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO
STDOUT' | $COMP -1 -c > file1016.data
---
work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to
off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name,
DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO
STDOUT' | $COMP -1 -c > file1016.data
321c321
<     echo "Loading the File table from $PWD/file.$$.data..."
---

Regards,
Sebastian
-d <bacula_database>
that header data ends up in the file. Because of this the restore fails.
replaces the $$ to the current process id.