PostgreSQL database migration issues

[Ali Alnubani]

Hello,

Restoring Bugzilla from a backup or upgrading to a newer version fails when the db driver is PostgreSQL. To reproduce:

Initialize a new Bugzilla instance:
$ sudo -u postgres -- createdb bugzilla --encoding='UTF-8' --locale=en_US.UTF-8 --owner=bugzilla

Run checksetup.pl:
$ sudo /path/to/bugzilla/checksetup.pl
* This is Bugzilla 5.1.2 on perl 5.32.1
* Running on Linux 5.10.0-9-amd64 #1 SMP Debian 5.10.70-1 (2021-09-30)
[..]
checksetup.pl complete.

Backup the DB (https://bugzilla.readthedocs.io/en/latest/installing/backups.html#postgresql):
$ sudo -u postgres -- pg_dump --no-privileges --no-owner bugzilla > bugzilla.sql

Drop and restore DB from backup:
$ sudo -u postgres -- dropdb bugzilla
$ sudo -u postgres -- createdb bugzilla --encoding='UTF-8' --locale=en_US.UTF-8 --owner=bugzilla
$ sudo -u postgres -- psql --single-transaction -d bugzilla -f bugzilla.sql -v ON_ERROR_STOP=1
$ echo $?
0

Rerun checksetup.pl:
$ sudo /path/to/bugzilla/checksetup.pl
[..]
Adding new table bz_schema...
DBD::Pg::db do failed: ERROR:  relation "bz_schema" already exists at Bugzilla/DB.pm line 818.
	Bugzilla::DB::_bz_add_table_raw(Bugzilla::DB::Pg=HASH(0x55d32cb8e638), "bz_schema") called at Bugzilla/DB.pm line 1350
	Bugzilla::DB::_bz_init_schema_storage(Bugzilla::DB::Pg=HASH(0x55d32cb8e638)) called at Bugzilla/DB.pm line 478
	Bugzilla::DB::bz_setup_database(Bugzilla::DB::Pg=HASH(0x55d32cb8e638)) called at Bugzilla/DB/Pg.pm line 237
	Bugzilla::DB::Pg::bz_setup_database(Bugzilla::DB::Pg=HASH(0x55d32cb8e638)) called at /var/www/html/bugzilla/checksetup.pl line 167

I looked at a related thread (https://lists.bugzilla.org/pipermail/support-list/2021-September/000042.html), but this isn’t caused by an incomplete db import, it’s probably about missing permissions, but that’s not documented, and the error is misleading.

Upgrading from 5.1.2 to 5.2 also fails due to permission issues:
$ sudo /path/to/bugzilla/checksetup.pl
[..]
Updating column cookie in table logincookies ...
Old: varchar(22) PRIMARY KEY NOT NULL
New: varchar(16) PRIMARY KEY NOT NULL
DBD::Pg::db do failed: ERROR:  must be owner of table logincookies at Bugzilla/DB.pm line 64.
        Bugzilla::DB::__ANON__(Bugzilla::DB::Pg=HASH(0x559d54b1f818), "ALTER TABLE logincookies ALTER COLUMN cookie\x{a}                "...) called at Bugzilla/DB.pm line 789
        Bugzilla::DB::bz_alter_column_raw(Bugzilla::DB::Pg=HASH(0x559d54b1f818), "logincookies", "cookie", HASH(0x559d54e69ea0), HASH(0x559d54c50e18), undef) called at Bugzilla/DB.pm line 748
        Bugzilla::DB::bz_alter_column(Bugzilla::DB::Pg=HASH(0x559d54b1f818), "logincookies", "cookie", HASH(0x559d54e69ea0)) called at Bugzilla/Install/DB.pm line 437
        Bugzilla::Install::DB::update_table_definitions(HASH(0x559d4e4446b8)) called at ./checksetup.pl line 175

Am I missing something?

OS: Debian 11 (bullseye)
Bugzilla: release-5.1.2 (3c74d53fb)
PostgreSQL: 13.5 (Debian 13.5-0+deb11u1)
libdbd-pg-perl: 3.14.2-1+b1

Thanks,
Ali