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

[Carsten Leonhardt]

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:
>
>
>
>  --- /usr/share/bacula-director/update_postgresql_tables.orig    2025-04-24 15:00:09.000000000 +0000
> +++ /usr/share/bacula-director/update_postgresql_tables 2025-09-02 11:44:35.755581836 +0000
> @@ -186,7 +186,7 @@
>     echo " doesn't have write permission on the current directory,"
>     echo " or if the system doesn't have enough space to store a"
>     echo " compressed export of the File table"
> -    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, Ma
> rkId, 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
>
>     if [ $? -ne 0 ]; then
>        echo "Error while dumping file table to $PWD/file1017.data"
> @@ -285,7 +285,7 @@
>     echo " doesn't have write permission on the current directory,"
>     echo " or if the system doesn't have enough space to store a"
>     echo " compressed export of the File table"
> -    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, Ma
> rkId, 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
>
>     if [ $? -ne 0 ]; then
>        echo "Error while dumping file table to $PWD/file1016.data"
> @@ -642,7 +642,7 @@
>
> -- Need to add postgresql-contrib to the dependency list
>
> -do $$
> +do \$\$
> declare
>   selected_ext pg_extension%rowtype;
> begin
> @@ -654,7 +654,7 @@
>   if not found then
>      CREATE EXTENSION pg_trgm;
>   end if;
> -end $$;
> +end \$\$;
>
> CREATE INDEX meta_emailowner on MetaEmail (EmailTenant, EmailOwner);
> CREATE INDEX meta_emailtime on MetaEmail (EmailTime);
>
> --
>
> Regards,
>
>
>
> Joop Boonen.
>
>
> --
>
> Joop Boonen
>
> Professional services consultant
>
> credativ IT Services GmbH
>
>
>
> Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
>
>
> credativ IT Services GmbH, Trompeterallee 108, 41189 Mönchengladbach, Germany
>
> Handelsregister: AG Mönchengladbach HRB 23003
>
> Geschäftsführer: David Brauner
>
> Umsatzsteuer-ID: DE452151696