[pkg-bacula-devel] Bug#1113777: Bacula update script /usr/share/bacula-director/update_postgresql_tables is broken

[Sebastian Neustein]

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
---
>     psql -q --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
222c222
<     echo "Loading the File table from $PWD/file.$$.data..."
---
>     echo "Loading the File table from $PWD/file1017.data..."
227c227
<       echo "Inserting File data from file.$$.data failed."
---
>       echo "Inserting File data from file1017.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
---
>     psql -q --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
321c321
<     echo "Loading the File table from $PWD/file.$$.data..."
---
>     echo "Loading the File table from $PWD/file1016.data..."

Regards,
Sebastian


On Tue, 02 Sep 2025 18:03:07 +0200 Carsten Leonhardt <leo at debian.org> wrote:
> Control: unmerge -1
> Control: reopen -1
>
> 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 at debian.org> writes:
>
> > Control: forcemerge 1108272 -1
> >
> > 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 at credativ.de> writes:
> >
> >> Package: bacula-director-pgsql
> >>
> >>  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:
> >>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/pkg-bacula-devel/attachments/20261009/35df7607/attachment.htm>