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 ~] %
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.
