Bacula: Moving from 9.x to 13.x and upgrading the PostgreSQL database

2 Comments / Bacula, FreeBSD, Open Source, PostgreSQL / By

I have been using Bacula since 2004. It is my backup solution of choice, not only because it has a PostgreSQL backend, but it is flexible, robust, and reliable.

I, on the other hand, have long ignored recent releases. I’m using Bacula 9.6.7 (released on 2021-01-26), roughly 3 years ago. Today, I started the upgrade to 13.0.1 (still not the latest release, but that’s because of a minor compile issue, not yet resolved; I’m the maintainer, so I am to blame for that).

With the upgrade in software comes an upgrade to the database format. I have recently moved my Bacula database from PostgreSQL 12.16 to PostgreSQL 16.1. Today, I started the update_bacula_tables script. While that process is straight forward when running, the process to get there is not well covered by the documentation.

In this post:

  • FreeBSD 13.2
  • PostgreSQL 16.1
  • Bacula 9.6.7 (source)
  • Bacula 13.0.1 (target)

Overview

The upgrade script is distributed with Bacula. You might have to search for it within your installed files.

I copy this script to the PostgreSQL server. In fact, I copy the entire directory. I run the script on the database server because it simplifies the database connection.

Get the right directory

The script you want is update_bacula_tables. On FreeBSD, the default location of that file is /usr/local/share/bacula. I copied that directory to my PostgreSQL (same location: /usr/local/share/bacula).

NOTE: I modified the copy of update_postgresql_tables appending -x to the first line:

#!/bin/sh -x

This just adds more output as it provides some debug tracing. I like that output.

Run as the right user

The script wants to connect to the database as the PostgreSQL user. The actual user differs from OS to OS. On FreeBSD, this user is postgres:

[13:05 pg03 dan ~] % grep post /etc/passwd 
postgres:*:770:770:PostgreSQL Daemon:/var/db/postgres:/bin/sh
[13:06 pg03 dan ~] %

More on this later.

Run it

This script can take a long time to run. I started a tmux session. Use that or something similar, such as screen. This ensures that your command line does not go away while the script is running.

I because the PostgreSQL user (use the actual user used by your OS):

[12:22 pg03 dan /usr/local/share/bacula] % sudo su - postgres
$ cd  /usr/local/share/bacula

Here is my run of the script:

$ sh -x ./update_bacula_tables 
+ pre_command='sh -c'
+ default_db_type=postgresql
+ [ 0 -gt 0 ]
+ [ -z '' ]
+ db_type=postgresql
+ [ postgresql '=' postgresql -a '' '=' 0 ]
+ echo 'Altering postgresql tables'
Altering postgresql tables
+ sh -c '/usr/local/share/bacula/update_postgresql_tables '
+ OLDVERSION=1022
+ NEWVERSION=1024
+ echo ' '
 
+ echo 'This script will update a Bacula PostgreSQL database'
This script will update a Bacula PostgreSQL database
+ echo ' from any from version 12-16 or 1014-1022 to version 1024'
 from any from version 12-16 or 1014-1022 to version 1024
+ echo ' which is needed to convert from any Bacula Communty to version 11.4.x'
 which is needed to convert from any Bacula Communty to version 11.4.x
+ echo ' '
 
+ bindir=/usr/local/bin
+ PATH=/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin:/var/db/postgres/bin
+ db_name=bacula
+ [ '' '=' --stop1015 ]
+ [ '' '=' --stop1016 ]
+ ARGS=''
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=16
+ [ x16 '=' x ]
+ [ 16 -lt 1014 -o 16 -gt 1022 ]
+ [ 16 -lt 12 -o 16 -gt 16 ]
+ [ 16 -eq 12 ]
+ [ 16 -eq 13 ]
+ [ 16 -eq 14 ]
+ [ 16 -eq 1014 ]
+ [ 16 -eq 15 -o 16 -eq 16 ]
+ [ 16 -eq 16 ]
+ SKIP1018=1
+ WORKMEM=1GB
+ which pigz
+ COMP=''
+ [ '' '=' '' ]
+ which pbzip2
+ COMP=''
+ [ '' '=' '' ]
+ which lzop
+ COMP=''
+ [ '' '=' '' ]
+ which gzip
+ COMP=/usr/bin/gzip
+ [ /usr/bin/gzip '=' '' ]
+ echo 'Dumping File table to /usr/local/share/bacula/file1017.data. '
Dumping File table to /usr/local/share/bacula/file1017.data. 
+ echo ''

+ echo 'The process may fail if the current user'
The process may fail if the current user
+ echo $' doesn\'t have write permission on the current directory,'
 doesn't have write permission on the current directory,
+ echo $' or if the system doesn\'t have enough space to store a'
 or if the system doesn't have enough space to store a
+ echo ' compressed export of the File table'
 compressed export of the File table
+ psql --set 'ON_ERROR_STOP=1' -d bacula -c $'set work_mem=\'1GB\';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'
+ /usr/bin/gzip -1 -c
/usr/local/share/bacula/update_postgresql_tables: cannot create file1017.data: Permission denied
+ [ 2 -ne 0 ]
+ echo 'Error while dumping file table to /usr/local/share/bacula/file1017.data'
Error while dumping file table to /usr/local/share/bacula/file1017.data
+ exit 1
$ cd /tmp
$ mkdir postgresql_upgrade
$ cd postgresql_upgrade/
$ sh -x /usr/local/share/bacula/update_bacula_tables
+ pre_command='sh -c'
+ default_db_type=postgresql
+ [ 0 -gt 0 ]
+ [ -z '' ]
+ db_type=postgresql
+ [ postgresql '=' postgresql -a '' '=' 0 ]
+ echo 'Altering postgresql tables'
Altering postgresql tables
+ sh -c '/usr/local/share/bacula/update_postgresql_tables '
+ OLDVERSION=1022
+ NEWVERSION=1024
+ echo ' '
 
+ echo 'This script will update a Bacula PostgreSQL database'
This script will update a Bacula PostgreSQL database
+ echo ' from any from version 12-16 or 1014-1022 to version 1024'
 from any from version 12-16 or 1014-1022 to version 1024
+ echo ' which is needed to convert from any Bacula Communty to version 11.4.x'
 which is needed to convert from any Bacula Communty to version 11.4.x
+ echo ' '
 
+ bindir=/usr/local/bin
+ PATH=/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin:/var/db/postgres/bin
+ db_name=bacula
+ [ '' '=' --stop1015 ]
+ [ '' '=' --stop1016 ]
+ ARGS=''
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=16
+ [ x16 '=' x ]
+ [ 16 -lt 1014 -o 16 -gt 1022 ]
+ [ 16 -lt 12 -o 16 -gt 16 ]
+ [ 16 -eq 12 ]
+ [ 16 -eq 13 ]
+ [ 16 -eq 14 ]
+ [ 16 -eq 1014 ]
+ [ 16 -eq 15 -o 16 -eq 16 ]
+ [ 16 -eq 16 ]
+ SKIP1018=1
+ WORKMEM=1GB
+ which pigz
+ COMP=''
+ [ '' '=' '' ]
+ which pbzip2
+ COMP=''
+ [ '' '=' '' ]
+ which lzop
+ COMP=''
+ [ '' '=' '' ]
+ which gzip
+ COMP=/usr/bin/gzip
+ [ /usr/bin/gzip '=' '' ]
+ echo 'Dumping File table to /tmp/postgresql_upgrade/file1017.data. '
Dumping File table to /tmp/postgresql_upgrade/file1017.data. 
+ echo ''

+ echo 'The process may fail if the current user'
The process may fail if the current user
+ echo $' doesn\'t have write permission on the current directory,'
 doesn't have write permission on the current directory,
+ echo $' or if the system doesn\'t have enough space to store a'
 or if the system doesn't have enough space to store a
+ echo ' compressed export of the File table'
 compressed export of the File table
+ psql --set 'ON_ERROR_STOP=1' -d bacula -c $'set work_mem=\'1GB\';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'
+ /usr/bin/gzip -1 -c

That’s where it sits now. I typed the above after running that command. That’s an indication of how long it will take.

FYI, my Bacula database dump is about 148G when. That might help you figure out how long it will take for you.

For the fun-lovers out there, and I know that’s you, here’s what just appeared:

+ [ 0 -ne 0 ]
+ psql --set 'ON_ERROR_STOP=1' -f - -d bacula
BEGIN
DROP TABLE
DROP TABLE
CREATE TABLE
COMMIT
+ echo 'Loading the File table from /tmp/postgresql_upgrade/file.26318.data...'
Loading the File table from /tmp/postgresql_upgrade/file.26318.data...
+ cat file1017.data
+ /usr/bin/gzip -d
+ psql --set 'ON_ERROR_STOP=1' -d bacula -c $'BEGIN; TRUNCATE File; COPY File FROM STDIN; set maintenance_work_mem=\'2000MB\'; CREATE INDEX file_jpfid_idx on File (JobId, PathId, Filename text_pattern_ops); ALTER TABLE ONLY File ADD CONSTRAINT file_pkey PRIMARY KEY (FileId); COMMIT;'
BEGIN
TRUNCATE TABLE

Exciting, I know!

Finally, I got:

TRUNCATE TABLE
ERROR:  invalid input syntax for type bigint: "SET"
CONTEXT:  COPY file, line 1, column fileid: "SET"
+ [ 1 -ne 0 ]
+ echo 'Inserting File data from file.26318.data failed.'
Inserting File data from file.26318.data failed.
+ exit 1
$

This is not good.

The fix, as posted to the Bacula Users Mailing list involves these two patches:

  • https://github.com/dlangille/bacula-community/commit/0200b1461310b4f5d4cdb89aedaa2ca9c1e1ad77
  • https://github.com/dlangille/bacula-community/commit/716952b15b71be44f152bfb522666514422acc95

First, file.26318.data is not the correct file name. The code is wrong. The correct filename is file1017.data.

Let’s look at that file:

$ cat file1017.data | /usr/bin/gzip -d | head -20
SET
SET
SET
290658834       47767   129209  442701  next_vol.patch  0       0       Bs jlhS IGk B A A COQKg ro EAA I BRYSdD BLhUbc BLk9ZE A A C     fSmIlVrj9x4ETUxuH9PAIQ
290658835       50573   129209  237850  part_preface.xml.svn-base       0       0       Bs n0Gc IEk B Pp Pp CfDWu i EAA E BRYSdQ BHf/U1 BHf/U2 A A C    JebC91WLdIQADU0JDepbkg
290658836       44185   129209  227351  019.jpg 0       0       Bs P0IB IGk B Pp Pp /XtI xL1 EAA HA BRYScm BFx9n8 BFx9n8 A A C  G4caSihayh/RmpHvhl+wew
290658837       54991   129209  438580  T137.ithmb      0       0       Bs 34gB IHk B Pp Pp Dfgkg Cj9w EAA VA BRYSeJ BJwsMf BLUldS A A C        CpSByTuNV1ZXrVej/29vfg
290658838       44189   129209  227351  023.jpg 0       0       Bs P0IF IGk B Pp Pp /X0o y9Q EAA HA BRYScm BFx9n8 BFx9n8 A A C  V0wtD4fr4t4GGYpcZNrkYA
290658839       44092   129209  227351  019--thumb.jpg  0       0       Bs P0Gk IGk B Pp Pp /Wl4 EAZ EAA k BRYScm BFx9n4 BFx9n4 A A C   ufFcqL3JsasSXNL8dz2OBg

See those three SET commands? They should absolutely not be there. The fix is to use psql -q.

However, rather than rerun that entire script, let’s hack apart the script. First, to get this working, was to modify the script, removing things already done. I removed everything from line 39 (if [ “$DBVERSION” -eq 12 ] ; then) to line 151 (just before # Upgrade from the community edition). Then I modified this line, which has been wrapped for visibility).

    cat file1017.data | $COMP -d | tail -n +4 | psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c
"BEGIN; TRUNCATE File; COPY File FROM STDIN; set maintenance_work_mem='2000MB'; CREATE INDEX file_jpfid_idx 
on File (JobId, PathId, Filename text_pattern_ops); ALTER TABLE ONLY File ADD CONSTRAINT file_pkey 
PRIMARY KEY (FileId); COMMIT;"

I added the tail -n +4 | part to strip the first four lines from the archive.

It worked:

$ sh -x /usr/local/share/bacula/update_bacula_tables
+ pre_command='sh -c'
+ default_db_type=postgresql
+ [ 0 -gt 0 ]
+ [ -z '' ]
+ db_type=postgresql
+ [ postgresql '=' postgresql -a '' '=' 0 ]
+ echo 'Altering postgresql tables'
Altering postgresql tables
+ sh -c '/usr/local/share/bacula/update_postgresql_tables '
+ OLDVERSION=1022
+ NEWVERSION=1024
+ echo ' '
 
+ echo 'This script will update a Bacula PostgreSQL database'
This script will update a Bacula PostgreSQL database
+ echo ' from any from version 12-16 or 1014-1022 to version 1024'
 from any from version 12-16 or 1014-1022 to version 1024
+ echo ' which is needed to convert from any Bacula Communty to version 11.4.x'
 which is needed to convert from any Bacula Communty to version 11.4.x
+ echo ' '
 
+ bindir=/usr/local/bin
+ PATH=/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin:/var/db/postgres/bin
+ db_name=bacula
+ [ '' '=' --stop1015 ]
+ [ '' '=' --stop1016 ]
+ ARGS=''
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=16
+ [ x16 '=' x ]
+ [ 16 -lt 1014 -o 16 -gt 1022 ]
+ [ 16 -lt 12 -o 16 -gt 16 ]
+ [ 16 -eq 15 -o 16 -eq 16 ]
+ [ 16 -eq 16 ]
+ SKIP1018=1
+ WORKMEM=1GB
+ which pigz
+ COMP=''
+ [ '' '=' '' ]
+ which pbzip2
+ COMP=''
+ [ '' '=' '' ]
+ which lzop
+ COMP=''
+ [ '' '=' '' ]
+ which gzip
+ COMP=/usr/bin/gzip
+ [ /usr/bin/gzip '=' '' ]
+ echo 'Loading the File table from /tmp/postgresql_upgrade/file.16319.data...'
Loading the File table from /tmp/postgresql_upgrade/file.16319.data...
+ cat file1017.data
+ /usr/bin/gzip -d
+ tail -n +4
+ psql --set 'ON_ERROR_STOP=1' -d bacula -c $'BEGIN; TRUNCATE File; COPY File FROM STDIN; set maintenance_work_mem=\'2000MB\'; CREATE INDEX file_jpfid_idx on File (JobId, PathId, Filename text_pattern_ops); ALTER TABLE ONLY File ADD CONSTRAINT file_pkey PRIMARY KEY (FileId); COMMIT;'
BEGIN
TRUNCATE TABLE
COPY 1207159576
SET
CREATE INDEX
ALTER TABLE
COMMIT
+ [ 0 -ne 0 ]
+ echo 'Creation of indexes and PK on the File table...'
Creation of indexes and PK on the File table...
+ psql --set 'ON_ERROR_STOP=1' -f - -d bacula
SET
BEGIN
CREATE SEQUENCE
ALTER SEQUENCE
   setval   
------------
 3699065614
(1 row)

ALTER TABLE
ANALYZE
ALTER TABLE
ALTER TABLE
ALTER TABLE
UPDATE 2
COMMIT
+ echo 'Upgrade of the File table succeeded. Version 1017'
Upgrade of the File table succeeded. Version 1017
+ rm -f file1017.data
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1017
+ [ '' '=' '' -a 1017 -eq 1015 ]
+ [ '' '=' '' -a 1017 -eq 1016 ]
+ [ 1017 -eq 1017 ]
+ psql -f - -d bacula
BEGIN
CREATE TABLE
CREATE INDEX
UPDATE 2
COMMIT
+ echo 'Update of Bacula PostgreSQL tables 1017 to 1018 succeeded.'
Update of Bacula PostgreSQL tables 1017 to 1018 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1018
+ [ 1018 -eq 1018 -a 1 '=' 1 ]
+ psql -f - -d bacula
UPDATE 2
+ echo 'Update of Bacula PostgreSQL tables 1018 to 1019 succeeded.'
Update of Bacula PostgreSQL tables 1018 to 1019 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1019
+ [ 1019 -eq 1018 ]
+ [ 1019 -eq 1019 ]
+ psql -f - -d bacula
BEGIN
ALTER TABLE
ALTER TABLE
ALTER TABLE
UPDATE 2
COMMIT
+ echo 'Update of Bacula PostgreSQL tables 1019 to 1020 succeeded.'
Update of Bacula PostgreSQL tables 1019 to 1020 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1020
+ [ 1020 -eq 1020 ]
+ psql -f - -d bacula
BEGIN
UPDATE 2
COMMIT
+ echo 'Update of Bacula PostgreSQL tables 1020 to 1021 succeeded.'
Update of Bacula PostgreSQL tables 1020 to 1021 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1021
+ [ 1021 -eq 1021 ]
+ psql -f - -d bacula
BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
UPDATE 2
COMMIT
+ echo 'Update of Bacula PostgreSQL tables 1021 to 1022 succeeded.'
Update of Bacula PostgreSQL tables 1021 to 1022 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1022
+ [ 1022 -eq 1022 ]
+ psql -f - -d bacula
BEGIN
ALTER TABLE
ALTER TABLE
CREATE INDEX
INSERT 0 1
UPDATE 2
COMMIT
+ echo 'Update of Bacula PostgreSQL tables 1022 to 1023 succeeded.'
Update of Bacula PostgreSQL tables 1022 to 1023 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1023
+ [ 1023 -eq 1023 ]
+ psql -f - -d bacula
BEGIN
ALTER TABLE
ALTER TABLE
CREATE INDEX
INSERT 0 1
UPDATE 2
COMMIT
+ echo 'Update of Bacula PostgreSQL tables 1023 to 1024 succeeded.'
Update of Bacula PostgreSQL tables 1023 to 1024 succeeded.
+ getVersion
+ psql -d bacula -t --pset 'format=unaligned' -c 'select VersionId from Version LIMIT 1'
+ DBVERSION=1024
+ psql -f - -d bacula
+ exit 0

I ran a test backup. I got errors:

24-Nov 01:02 bacula-sd-04 JobId 361289: Recycled volume "IncrAuto-04-14456" on File device "vDrive-IncrFile-0" (/usr/local/bacula/volumes/IncrFile), all previous data lost.
24-Nov 01:02 bacula-dir JobId 361289: Max Volume jobs=1 exceeded. Marking Volume "IncrAuto-04-14456" as Used.
24-Nov 01:02 bacula-sd-04 JobId 361289: Elapsed time=00:00:01, Transfer rate=11.06 M Bytes/second
24-Nov 01:02 bacula-sd-04 JobId 361289: Sending spooled attrs to the Director. Despooling 6,230 bytes ...
24-Nov 01:02 bacula-dir JobId 361289: Fatal error: sql_create.c:894 Fill File table Query failed: INSERT INTO File (FileIndex, JobId, PathId, Filename, LStat, MD5, DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, batch.Name, batch.LStat, batch.MD5, batch.DeltaSeq FROM batch JOIN Path ON (batch.Path = Path.Path) : ERR=ERROR:  permission denied for table file


24-Nov 01:02 bacula-dir JobId 361289: Error: Bacula bacula-dir 13.0.1 (05Aug22):

I ran the permissions script and that was fixed up. I suspect the permissions on my 19 year old database did not conform.

$ sh -x /usr/local/share/bacula/grant_bacula_privileges
+ pre_command='sh -c'
+ default_db_type=postgresql
+ [ 0 -gt 0 ]
+ [ -z '' ]
+ db_type=postgresql
+ [ postgresql '=' postgresql -a '' '=' 0 ]
+ echo 'Granting postgresql privileges'
Granting postgresql privileges
+ sh -c '/usr/local/share/bacula/grant_postgresql_privileges '
psql::2: ERROR:  role "bacula" already exists
ALTER DATABASE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
Privileges for user bacula granted on database bacula.
$

All good now.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

2 thoughts on “Bacula: Moving from 9.x to 13.x and upgrading the PostgreSQL database”

Leave a Comment

Scroll to Top