Tt-rss fails to start after migrating postgresql 11 to 15

Support
1

Along with the upgrade to Bookworm, Postgresql was upgraded from version 13 to 15. As it turned out, I was still running version 11 from Buster! As I didn’t realize it at the time I manually finished the upgrade I allowed version 11 to be removed. Well, I installed the packages manually and tt-rss works fine with it.

I decided to try to migrate the database to version 15 using the following command run from /tmp:

sudo -H -u postgres /usr/lib/postgresql/15/bin/pg_upgrade \
-b /usr/lib/postgresql/11/bin \
-B /usr/lib/postgresql/15/bin \
-d /var/lib/postgresql/11/main \
-D /var/lib/postgresql/15/main \
-o ' -c config_file=/etc/postgresql/11/main/postgresql.conf' \
-O ' -c config_file=/etc/postgresql/15/main/postgresql.conf'

This command is a modified version of that shown on the Postgresql Wiki and referenced from the Debian Wiki.

The command appears to have been successful:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Creating dump of global objects                             ok
Creating dump of database schemas                           
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster               
                                                            ok
Copying user relation files                                 
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Also from /tmp ran the following:

sudo -H -u postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "ttrss": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "ttrss": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "ttrss": Generating default (full) optimizer statistics

Starting tt-rss fails quickly:

Jun 25 20:55:33 freedombox systemd[1]: Started tt-rss.service - Update the Tiny Tiny RSS subscribed syndication feeds.
Jun 25 20:55:35 freedombox systemd[1]: tt-rss.service: Main process exited, code=exited, status=101/n/a
Jun 25 20:55:35 freedombox systemd[1]: tt-rss.service: Failed with result 'exit-code'.
Jun 25 20:55:35 freedombox systemd[1]: tt-rss.service: Consumed 1.190s CPU time.

Trying to load the tt-rss Web page results in seeing:

connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "ttrss"

Now I’m a bit stumped because so far as I know, there shouldn’t have been any changes to the DB. As if to prove the point I reverted back to PG 11 and tt-rss is working fine. My SQL is next to non-existent so any tips on what to look for in PG 15 would be appreciated.

2

I had similar trouble when updating to Bookworm from Bullseye.
Is there a specific reason you want to update the database?
I found it easier to just export an .opml file from a working tt-rss and import that back after the upgrade.

3

I am intending to upgrade the DB as I presume that eventually tt-rss will depend on functionality present in some newer version of postgresql. In others words, I doubt I can run posgresql version 11 forever.

Does exporting to OPML preserve the starred articles–articles I’ve saved for future reference?

I presume that I would have to remove tt-rss from plinth and then reinstall it with the newer version of postgresql running so the passwords and such can be setup?

4

opml did get all my feeds though I dont know if it will save your starred articles.

I came across this when doing an online search. Seems like a similar question was raised in the past.

Hope it helps.

5

My upgrade to bookworm was not graceful so my experience may not be representative, but I ended up with two versions of PostgreSQL running, 13 (on port 5432, the default port), and 15 (on port 5433). tt-rss, since it connects to the default port, was still using 13. I used pg_dumpall and psql to migrate data from 13 to 15 (being careful to specify the right port for the target instance of each command), but still ended up with “authentication failure for user ‘ttrss’”. I ended up taking the value for dbc_dbpass in /etc/dbconfig-common/tt-rss.conf and updating the password for user ttrss in 15 with it (in postgres database, not ttrss database) using psql (ALTER USER ttrss PASSWORD ‘xyz’;). Then I used systemctl to stop both 13 and 15 (and also disable and mask 13 to prevent it from starting during boot), changed the port for 15 from 5433 to 5432 in /etc/postgresql/15/main/postgresql.conf, and started 15 again with systemctl. Now I have just one instance of PostgreSQL running and tt-rss seems to be running normally with all data intact, including starred articles. Maybe in a day or two I’ll feel brave enough to remove 13.

I don’t recall seeing in bullseye the massive numbers of tt-rss PHP-related warnings that are showing up in bookworm, but there is an outstanding bug report about that which dates back to March 2022.

6

Thank you so much! Altering the password in the V 15 DB works like a charm.

Here are a couple of things I found in working through all of this.

The tt-rss DB password is also found in /etc/tt-rss/database.php.

Instead of using systemd masking the individual postgresql versions allow for precise control of which version is started via the setting in the file /etc/postgresql/$VERSION/main/start.conf. As I have both 11 and 15 installed I have 11 set to disabled and 15 set to auto.

It seems that as Debian allows multiple versions to be installed, in my case 11 was on port 5432, 13 on 5433, and 15 on 5434 reflecting suxxessive upgrades from Buster, to Bullseye, to Bookworm. Editing the port= parameter in /etc/postgresql/15/main/postgresql.conf is all that is needed. Of course, I stopped the daemon, edited, and then started the daemon for the correct port to take effect. This can be verified with netstat -tunlp.

Thanks for all the help!