Unable to upgrade from Trac 1.0.1 to Trac 1.6

[eric@…]

I'm trying to upgrade my Trac instance from 1.0.1 (yes!) to Trac 1.6. The upgrade instructions don't seem to suggest that this is not possible. When I attempt the upgrade, I get this error:

rac-admin ./multics-trac upgrade
/Users/eswenson/src/trac/venv/lib/python3.14/site-packages/trac/__init__.py:14: UserWarning: pkg_resources is deprecated as an API. See https://setuptools.pypa.io/en/latest/pkg_resources.html. The pkg_resources package is slated for removal as early as 2025-11-30. Refrain from using this package or pin to Setuptools<81.
  from pkg_resources import DistributionNotFound, get_distribution
Error: Unable to check for upgrade of trac.db.api.DatabaseManager: UndefinedTable: relation "system" does not exist
LINE 2:                     SELECT value FROM "system" WHERE name='d...
                                              ^

I'm using a database setting like:

postgres://multicstrac@localhost:5432/multics_trac?schema=trac

When I connect to the multics_trac database using psql, I can see the system table with \dt trac.* — in otherwise the schema is trac and not public. I've specified schema=trac in the database url, so why is it not using that schema?

[Jun Omae]

What version of PostgreSQL are you using?

Please let us know results of the following command, after connecting the database with psql --host=localhost --user=multicstrac --dbname=multics_trac:

\dn
set search_path to "trac";
\dt
select * from "system";

[eric@…]

multics_trac=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | eswenson
 trac   | postgres
(2 rows)

multics_trac=> set search_path to "trac";
SET
multics_trac=> \dt
                   List of relations
 Schema |         Name          | Type  |    Owner     
--------+-----------------------+-------+--------------
 trac   | attachment            | table | multics_trac
 trac   | auth_cookie           | table | multics_trac
 trac   | cache                 | table | multics_trac
 trac   | component             | table | multics_trac
 trac   | enum                  | table | multics_trac
 trac   | milestone             | table | multics_trac
 trac   | node_change           | table | multics_trac
 trac   | notify_subscription   | table | multics_trac
 trac   | notify_watch          | table | multics_trac
 trac   | permission            | table | multics_trac
 trac   | report                | table | multics_trac
 trac   | repository            | table | multics_trac
 trac   | revision              | table | multics_trac
 trac   | session               | table | multics_trac
 trac   | session_attribute     | table | multics_trac
 trac   | spamfilter_bayes      | table | multics_trac
 trac   | spamfilter_log        | table | multics_trac
 trac   | spamfilter_report     | table | multics_trac
 trac   | spamfilter_statistics | table | multics_trac
 trac   | system                | table | multics_trac
 trac   | ticket                | table | multics_trac
 trac   | ticket_change         | table | multics_trac
 trac   | ticket_custom         | table | multics_trac
 trac   | version               | table | multics_trac
 trac   | wiki                  | table | multics_trac
(25 rows)

multics_trac=> select * from "system";
           name           |   value    
--------------------------+------------
 initial_database_version | 29
 spamfilter_lastclean     | 1612966934
 spamfilter_version       | 4
 database_version         | 45
(4 rows)

multics_trac=> 

[Jun Omae]

Thanks for the feedback.

Replying to eric@…:

multics_trac=> select * from "system";
           name           |   value    
--------------------------+------------
 initial_database_version | 29
 spamfilter_lastclean     | 1612966934
 spamfilter_version       | 4
 database_version         | 45
(4 rows)

The database_version is 45, so that it seems the upgrading to Trac 1.6 is already done.

multics_trac=> \dt
                   List of relations
 Schema |         Name          | Type  |    Owner     
--------+-----------------------+-------+--------------
 trac   | attachment            | table | multics_trac
 trac   | auth_cookie           | table | multics_trac
 trac   | cache                 | table | multics_trac
 trac   | component             | table | multics_trac
 trac   | enum                  | table | multics_trac
 trac   | milestone             | table | multics_trac
 trac   | node_change           | table | multics_trac
 trac   | notify_subscription   | table | multics_trac
 trac   | notify_watch          | table | multics_trac
 trac   | permission            | table | multics_trac
 trac   | report                | table | multics_trac
 trac   | repository            | table | multics_trac
 trac   | revision              | table | multics_trac
 trac   | session               | table | multics_trac
 trac   | session_attribute     | table | multics_trac
 trac   | spamfilter_bayes      | table | multics_trac
 trac   | spamfilter_log        | table | multics_trac
 trac   | spamfilter_report     | table | multics_trac
 trac   | spamfilter_statistics | table | multics_trac
 trac   | system                | table | multics_trac
 trac   | ticket                | table | multics_trac
 trac   | ticket_change         | table | multics_trac
 trac   | ticket_custom         | table | multics_trac
 trac   | version               | table | multics_trac
 trac   | wiki                  | table | multics_trac
(25 rows)

I feed strange that the table owner is multics_trac, but the user in the connection string is multicstrac.

-postgres://multicstrac@localhost:5432/multics_trac?schema=trac
+postgres://multics_trac@localhost:5432/multics_trac?schema=trac

[anonymous]

Yes, sorry. I am not sure I have the original database accessible any more. I may have deleted it, repopulated it, and changed the schema to public before being able to run the upgrade with the ?schema=trac in the database schema. I did eventually succeed by hacking.

I think the bug that I was reporting was the fact that when the database used the trac schema, and the database URL included ?schema=trac, I was unable to get the upgrade to work. So my hack was to move the database from the trac schema to the public schema. Then (still claiming ?schema=trac in the database url), I was able to upgrade. I must no longer have the old database.

And yes, I changed the user and database names to multics_trac in my successful run and then deleted the multicstrac database — which is why I can't provide you the metadata from that failed upgrade.

If you don't have enough to go on from the schema issue, I could start over with a newly copied 1.0.1 database in the trac schema and have it fail the upgrade again so that we could collect forensics from it.

[Jun Omae]

Hmm, trying to reproduce this issue with fresh Trac 1.0.20 and PostgreSQL database, but unable to reproduce. Works fine on test environment:

$ sudo -u postgres createdb -l en_US.UTF-8 -E UTF8 -T template0 multics_trac
$ sudo -u postgres psql
psql (14.23 (Ubuntu 14.23-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# \l
                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 multics_trac | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres     | postgres | UTF8     | de_DE.utf8  | de_DE.utf8  |
 template0    | postgres | UTF8     | de_DE.utf8  | de_DE.utf8  | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 template1    | postgres | UTF8     | de_DE.utf8  | de_DE.utf8  | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 trac         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

postgres=# create user multics_trac with password 'password';
CREATE ROLE
postgres=# GRANT CREATE ON DATABASE multics_trac TO multics_trac;
GRANT
postgres=# \du
                                     List of roles
  Role name   |                         Attributes                         | Member of
--------------+------------------------------------------------------------+-----------
 multics_trac |                                                            | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tracuser     |                                                            | {}

$ ~/venv/trac/1.0.20/bin/trac-admin /dev/shm/ticket13910
Welcome to trac-admin 1.0.20
Interactive Trac administration console.
Copyright (C) 2003-2020 Edgewall Software

Type:  '?' or 'help' for help on commands.

Trac [/dev/shm/ticket13910]> initenv ticket13910 postgres://multics_trac:password@localhost:5432/multics_trac?schema=trac
Creating and Initializing Project
...
Congratulations!

Trac [/dev/shm/ticket13910]>

$ psql -h 127.0.0.1 -U multics_trac multics_trac
Password for user multics_trac:
psql (14.23 (Ubuntu 14.23-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

multics_trac=> \dn
    List of schemas
  Name  |    Owner
--------+--------------
 public | postgres
 trac   | multics_trac
(2 rows)

multics_trac=> \dt
Did not find any relations.
multics_trac=> set search_path to "trac";
SET
multics_trac=> \d
                  List of relations
 Schema |       Name        |   Type   |    Owner
--------+-------------------+----------+--------------
 trac   | attachment        | table    | multics_trac
 trac   | auth_cookie       | table    | multics_trac
 trac   | cache             | table    | multics_trac
 trac   | component         | table    | multics_trac
 trac   | enum              | table    | multics_trac
 trac   | milestone         | table    | multics_trac
 trac   | node_change       | table    | multics_trac
 trac   | permission        | table    | multics_trac
 trac   | report            | table    | multics_trac
 trac   | report_id_seq     | sequence | multics_trac
 trac   | repository        | table    | multics_trac
 trac   | revision          | table    | multics_trac
 trac   | session           | table    | multics_trac
 trac   | session_attribute | table    | multics_trac
 trac   | system            | table    | multics_trac
 trac   | ticket            | table    | multics_trac
 trac   | ticket_change     | table    | multics_trac
 trac   | ticket_custom     | table    | multics_trac
 trac   | ticket_id_seq     | sequence | multics_trac
 trac   | version           | table    | multics_trac
 trac   | wiki              | table    | multics_trac
(21 rows)

$ ~/venv/trac/1.6/bin/trac-admin /dev/shm/ticket13910
Welcome to trac-admin 1.6
Interactive Trac administration console.
Copyright (C) 2003-2023 Edgewall Software

Type:  '?' or 'help' for help on commands.

Trac [/dev/shm/ticket13910]> upgrade
Upgrade done.

You may want to upgrade the Trac documentation now by running:

  trac-admin "/dev/shm/ticket13910" wiki upgrade
Trac [/dev/shm/ticket13910]> quit

$ psql -h 127.0.0.1 -U multics_trac multics_trac
Password for user multics_trac:
psql (14.23 (Ubuntu 14.23-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

multics_trac=> set search_path to "trac";
SET
multics_trac=> select * from "system";
           name           | value
--------------------------+-------
 initial_database_version | 29
 database_version         | 45
(2 rows)

multics_trac=> \dt
                  List of relations
 Schema |        Name         | Type  |    Owner
--------+---------------------+-------+--------------
 trac   | attachment          | table | multics_trac
 trac   | auth_cookie         | table | multics_trac
 trac   | cache               | table | multics_trac
 trac   | component           | table | multics_trac
 trac   | enum                | table | multics_trac
 trac   | milestone           | table | multics_trac
 trac   | node_change         | table | multics_trac
 trac   | notify_subscription | table | multics_trac
 trac   | notify_watch        | table | multics_trac
 trac   | permission          | table | multics_trac
 trac   | report              | table | multics_trac
 trac   | repository          | table | multics_trac
 trac   | revision            | table | multics_trac
 trac   | session             | table | multics_trac
 trac   | session_attribute   | table | multics_trac
 trac   | system              | table | multics_trac
 trac   | ticket              | table | multics_trac
 trac   | ticket_change       | table | multics_trac
 trac   | ticket_custom       | table | multics_trac
 trac   | version             | table | multics_trac
 trac   | wiki                | table | multics_trac
(21 rows)

[anonymous]

I just tried a migration from Trac 1.0.1 to Trac 1.6 — this time on Linux and with a different version of pgloader (apparently). When I loaded the mysql database for my Trac 1.0.1 instance into postgres with pgloader, it ended up in the public schema, rather than the trac schema. I'm not sure why — but I did see a pgloader ticket that suggested this behavior may have changed — and the AI summary in a Google web search indicated that pgloader, by default, puts the database in a schema with the same name as the database. In any case, my postgres database ended up in the public schema, and I had absolutely no issues upgrading.

So I'm going to try this again to see how I ended up with the multics_trac database in the trac schema when I first tried this. Apparently, I can append #trac to the postgres URL in the pgloader command — and maybe that was what I did the first time. I'll try that and report on the results.

[anonymous]

After doing a little reading about postgresql, databases, and schemas, I realize my wording, above, was not accurate. One doesn't create a database *in* a schema, one creates a schema *within* a database. So when I created the database, using "create database multics_trac;", something after that step (pgloader?) must have created the "trac" schema and then the various tables within that schema.

Without creating a pgloader options file, I don't see how to cause it to create the tables within a non-public schema — at least using current versions of pgloader. I'm therefore at a loss to know how I came by the strange setup that resulted in the upgrade failure.

I apologize for wasting your time and for not keeping better track of my conversion steps from mysql to postgres. I cannot reproduce the problem now either.

[Jun Omae]

I've tested upgrading Trac 1.0.1 to 1.6 with PostgreSQL and confirmed works fine. I consider that this issue is an InstallationIssue, not a Trac issue. Feel free to reopen it if reproduced only with Trac.