Database access issues after upgrade v3.5.2 -> v3.6.0.beta2

Support
1
  • v3.5.2 → v3.5.2
  • v3.6.0.beta2 → v3.6.0.beta2

This thread got me to this point: Upgrade failed. Database stopped. (multisite install)

I now have database access issues:


2025-11-02 17:13:51.212 UTC [1975] postgres@c_discourse LOG:  provided user name (postgres) and authenticated user name (discourse) do not match 
2025-11-02 17:13:51.212 UTC [1975] postgres@c_discourse FATAL:  Peer authentication failed for user "postgres" 
2025-11-02 17:13:51.212 UTC [1975] postgres@c_discourse DETAIL:  Connection matched pg_hba.conf line 89: "local   all             postgres       
                        peer"

postgres=# \l
List of databases
Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-------------±---------±---------±----------------±------------±------------±-----------±----------±-----------------------
b_discourse | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres          +
|          |          |                 |             |             |            |           | postgres=CTc/postgres +
|          |          |                 |             |             |            |           | discourse=CTc/postgres
c_discourse | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres          +
|          |          |                 |             |             |            |           | postgres=CTc/postgres +
|          |          |                 |             |             |            |           | discourse=CTc/postgres
discourse   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres          +
|          |          |                 |             |             |            |           | postgres=CTc/postgres +
|          |          |                 |             |             |            |           | discourse=CTc/postgres
postgres    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
template0   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres           +
|          |          |                 |             |             |            |           | postgres=CTc/postgres
template1   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres           +
|          |          |                 |             |             |            |           | postgres=CTc/postgres
(6 rows)

The multisite.yaml changed between these versions.

Original:
secondsite:
adapter: postgresql
database: b_discourse
pool: 25
timeout: 5000
db_id: 2
host_names:
- ``forum.domain.com

New:
mlp:
adapter: postgresql
database: discourse_mlp
username: discourse_mlp
password: applejack
host: dbhost
pool: 5
timeout: 5000
host_names:
- discourse.nudderdomain.com
- discourse.nudderdomain.internal

I never set any of the passwords or users for the multisite because it wasn’t required or listed in the original template when I set them up.

Initially I couldn’t upgrade because the multi site failed because of permissions on the two sites listed in multisite.yml. Adding postres as the user to multisite.yml didn’t work for the migration. Now I see maybe I should have tried discourse?

Will simply changing owner to discourse fix it? Do I need to add users and passwords for the multisite to make it match the current?

What is the best LONG TERM FIX here.

3

Your post is super hard to read, so I have unlisted it Please fix the formatting and I can list it again.

2 Likes
4

Sadly I went to great lengths to make is as comprehensible/coherent as I could.

I know what I mean. :wink:

EDIT: OK, got it. The other forum I use is a tad different. I use triple tics on the line before and after the block. Now I see what is going on. This one the first set of tics inserts a window to paste into. I couldn’t understand why the triple tics wasn’t working and </> wasn’t giving me what I really wanted.

1 Like
6

You can toggle to the markdown editor if you need to do lots of markdown things in your post.

Your database password issues are strange. Have you considered moving to a new server? That might be quicker and easier than battling with this issue.

7

Did you follow these instructions? (It doesn’t seem like it?)

It’s a pretty good bet, but make sure that you’ve got stuff like in the multisite install topic.

If your sites are working now then I too would encourage you to do a clean multisite install and backup/restore the databases over. You can copy all the other stuff as described in Move a Discourse site to another VPS with rsync

8

I wondered why I named my databases b_discourse and c_discourse. Now I know why. :wink:

## Plugins go here
## see https://meta.discourse.org/t/19157 for details
hooks:
  after_postgres:
     - exec: sudo -u postgres createdb b_discourse || exit 0
     - exec:
          stdin: |
            grant all privileges on database b_discourse to discourse;
          cmd: sudo -u postgres psql b_discourse
          raise_on_fail: false
            
     - exec: sudo -u postgres createdb c_discourse || exit 0
     - exec:
          stdin: |
            grant all privileges on database c_discourse to discourse;
          cmd: sudo -u postgres psql c_discourse
          raise_on_fail: false

     - exec: /bin/bash -c 'sudo -u postgres psql b_discourse <<< "alter schema public owner to discourse;"'
     - exec: /bin/bash -c 'sudo -u postgres psql b_discourse <<< "create extension if not exists hstore;"'
     - exec: /bin/bash -c 'sudo -u postgres psql b_discourse <<< "create extension if not exists pg_trgm;"'

I don’t fully understand how privileges are granted so I wondered about these. (screen shot from above for two problematic databases):

image
image326×205 3.61 KB

9

Well, good news bad news. :frowning:
Now we want :frowning:

2025-11-07 18:05:41.555 UTC [4724] discourse@b_discourse ERROR:  must be owner of extension vector
2025-11-07 18:05:41.555 UTC [4724] discourse@b_discourse STATEMENT:  ALTER EXTENSION vector UPDATE TO '0.8.0';
2025-11-07 18:05:41.752 UTC [4725] discourse@c_discourse ERROR:  must be owner of extension vector
2025-11-07 18:05:41.752 UTC [4725] discourse@c_discourse STATEMENT:  ALTER EXTENSION vector UPDATE TO '0.8.0';

instead of

ALTER EXTENSION vector UPDATE TO ‘0.7.0’;

But:

b_discourse=# ALTER EXTENSION vector UPDATE TO '0.8.0';
ERROR:  extension "vector" has no update path from version "0.7.4" to version "0.8.0"

I’ve been leery about just changing owners on the databases but I guess that might be next.

EDIT: OK. That didn’t work. It seems the extension owner is key here, not the database owner. As of now 0.8.0 doesn’t exist on my container. it tops out at the 0.7.4 :frowning: The ‘base’ seems to have gotten updated during the attempt at installing version 15.

10

Is there a way to get ./launcher to connect as the user postgres? That seems like it would cure all my upgrade issues here.

b_discourse=# select e.extname, u.usename 
             from pg_extension e 
             join pg_user u on e.extowner = u.usesysid;
 extname  |  usename  
----------+-----------
 plpgsql  | postgres
 hstore   | postgres
 pg_trgm  | postgres
 unaccent | discourse
 vector   | postgres
(5 rows)

It seems there are ‘issues’ just trying to change the extension owner. First reference I found was in 2017 and in 2022 still not implemented.

11

I used apt to install the new extention and got it to work. Geez. Now to do proper backups and update to Postges 15. But, not tonight. :wink:

My history seemed to get wiped so I can’t tell you exactly how I did that but beware. It requires postgres 13 and will try and reinstall that.

PS: Turns out I had turned on automatic backups but had forgotten about them. Not that I knew where they were. I’ll set up an rsync process to put them in the directory I do my other server backups too.

2 Likes