Joplin cannot login after PostgreSQL major update

Support
1

Operating system

Linux

Joplin version

2.14.2

Desktop version info

Joplin Desktop 2.14.20 (prod, win32)

Sync target

Joplin Server

Editor

Markdown Editor

What issue do you have?

Hello,

I'm running Joplin Server on my Raspberry Pi using Docker with the following docker-compose.yml:

services:
  joplin_app:
    image: florider89/joplin-server:latest
    container_name: joplin_app
    restart: unless-stopped
    environment:
      - APP_BASE_URL=https://myRaspi/joplinServer
      - APP_PORT=22300
      - POSTGRES_PASSWORD=SuperSecret
      - POSTGRES_DATABASE=joplinServerDb
      - POSTGRES_USER=joplinServer
      - POSTGRES_PORT=5432
      - POSTGRES_HOST=joplin_db
      - DB_CLIENT=pg
    depends_on:
      - joplin_db
    networks:
      - nginx_network
      - joplin_network
  joplin_db:
    image: postgres:13.1
    container_name: joplin_db
    restart: unless-stopped
    environment:
      - POSTGRES_PASSWORD=SuperSecret
      - POSTGRES_USER=joplinServer
      - POSTGRES_DB=joplinServerDb
    volumes:
      - ./data:/var/lib/postgresql/data
    networks:
      - joplin_network
networks:
  nginx_network:
    external: true
  joplin_network:

The output of ls -a data/ gives:

.   base  .gitignore  pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
..  core  global      pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid

Beside the data itself, notice the additional file .gitignore: it relates to the fact that the directory is under version control (Git) and the content of the data directory (but not the dir itself) is excluded as I do not put data under version control (backups are done differently).

As you can see, I'm using PostgreSQL 13 and would like to upgrade to 16. So I followed this blog post and first of all, created a backup of my data:

docker exec -it joplin_db pg_dumpall -U joplinServer -l joplinServerDb > ./backup.sql

The backup backup.sql is 541 MB big and manually checking the content looks okay.

Next I stopped the Docker stack (docker compose down) and modified docker-compose.yml to use PostgreSQL 16:

services:
  joplin_db:
    image: postgres:16
    container_name: joplin_db
    restart: unless-stopped
    environment:
      - POSTGRES_PASSWORD=SuperSecret
      - POSTGRES_USER=joplinServer
      - POSTGRES_DB=joplinServerDb
    volumes:
      - ./data:/var/lib/postgresql/data
    networks:
      - joplin_network
networks:
  nginx_network:
    external: true
  joplin_network:

When trying to start this stack with docker compose up, it fails because PostgreSQL does not accept other files inside its data directory (remember my .gitignore file). So I changed to folder structure to:

myUser@myRaspi:~/Development/temp/40-JoplinServer $ ls -a data
.  ..  data  .gitignore

The new docker-compose.yml now uses the new volume mount path:

services:
  joplin_db:
    image: postgres:16
    container_name: joplin_db
    restart: unless-stopped
    environment:
      - POSTGRES_PASSWORD=SuperSecret
      - POSTGRES_USER=joplinServer
      - POSTGRES_DB=joplinServerDb
    volumes:
      - ./data/data:/var/lib/postgresql/data
    networks:
      - joplin_network
networks:
  nginx_network:
    external: true
  joplin_network:

Now I can successfully start the Docker stack and import the data:

cat backup.sql  | docker exec -i joplin_db psql -U joplinServer -d joplinServerDb

This takes some time but finishes successfully. Now let's enable Joplin Server again:

services:
  joplin_app:
    image: florider89/joplin-server:latest
    container_name: joplin_app
    restart: unless-stopped
    environment:
      - APP_BASE_URL=https://myRaspi/joplinServer
      - APP_PORT=22300
      - POSTGRES_PASSWORD=SuperSecret
      - POSTGRES_DATABASE=joplinServerDb
      - POSTGRES_USER=joplinServer
      - POSTGRES_PORT=5432
      - POSTGRES_HOST=joplin_db
      - DB_CLIENT=pg
    depends_on:
      - joplin_db
    networks:
      - nginx_network
      - joplin_network
  joplin_db:
    image: postgres:16
    container_name: joplin_db
    restart: unless-stopped
    environment:
      - POSTGRES_PASSWORD=SuperSecret
      - POSTGRES_USER=joplinServer
      - POSTGRES_DB=joplinServerDb
    volumes:
      - ./data/data:/var/lib/postgresql/data
    networks:
      - joplin_network
networks:
  nginx_network:
    external: true
  joplin_network:

When starting the Docker stack, Joplin Server cannot connect to the database:

09:53:14 0|app    | 2024-05-31 09:53:14: App: Content driver: { type: 1 }
09:53:14 0|app    | 2024-05-31 09:53:14: App: Content driver (fallback): null
09:53:14 0|app    | 2024-05-31 09:53:14: App: Trying to connect to database...
09:53:14 0|app    | 2024-05-31 09:53:14: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:15 0|app    | 2024-05-31 09:53:15: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:16 0|app    | 2024-05-31 09:53:16: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:17 0|app    | 2024-05-31 09:53:17: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:18 0|app    | 2024-05-31 09:53:18: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:19 0|app    | 2024-05-31 09:53:19: db: Could not connect. Will try again. password authentication failed for user "joplinServer"

Here are the complete logs from Joplin's Docker container:

Logs 
yarn run v1.22.19
$ pm2 kill && pm2 start --no-daemon --exp-backoff-restart-delay=1000 dist/app.js

                      -------------

__/\\\\\\\\\\\\\____/\\\\____________/\\\\____/\\\\\\\\\_____
_\/\\\/////////\\\_\/\\\\\\________/\\\\\\__/\\\///////\\\___
_\/\\\_______\/\\\_\/\\\//\\\____/\\\//\\\_\///______\//\\\__
 _\/\\\\\\\\\\\\\/__\/\\\\///\\\/\\\/_\/\\\___________/\\\/___
  _\/\\\/////////____\/\\\__\///\\\/___\/\\\________/\\\//_____
   _\/\\\_____________\/\\\____\///_____\/\\\_____/\\\//________
    _\/\\\_____________\/\\\_____________\/\\\___/\\\/___________
     _\/\\\_____________\/\\\_____________\/\\\__/\\\\\\\\\\\\\\\_
      _\///______________\///______________\///__\///////////////__


                        Runtime Edition

      PM2 is a Production Process Manager for Node.js applications
                   with a built-in Load Balancer.

              Start and Daemonize any application:
              $ pm2 start app.js

              Load Balance 4 instances of api.js:
              $ pm2 start api.js -i 4

              Monitor in production:
              $ pm2 monitor

              Make pm2 auto-boot at server restart:
              $ pm2 startup

              To go further checkout:
              http://pm2.io/


                      -------------

[PM2] Spawning PM2 daemon with pm2_home=/home/joplin/.pm2
[PM2] This PM2 is not UP TO DATE
[PM2] Upgrade to version 5.4.0
[PM2] PM2 Successfully daemonized
[PM2][WARN] No process found
[PM2] [v] All Applications Stopped
[PM2] [v] PM2 Daemon Stopped
pm2 launched in no-daemon mode (you can add DEBUG="*" env variable to get more messages)
2024-05-31T09:52:21: PM2 log: Launching in no daemon mode
2024-05-31T09:52:21: PM2 log: [PM2] Starting /home/joplin/packages/server/dist/app.js in fork_mode (1 instance)
2024-05-31T09:52:21: PM2 log: App [app:0] starting in -fork mode-
2024-05-31T09:52:21: PM2 log: App [app:0] online
2024-05-31T09:52:21: PM2 log: [PM2] Done.
2024-05-31T09:52:21: PM2 log: ┌────┬────────┬─────────────┬─────────┬─────────┬──────────┬────────┬──────┬───────────┬──────────┬──────────┬──────────┬──────────┐
│ id │ name   │ namespace   │ version │ mode    │ pid      │ uptime │ ↺    │ status    │ cpu      │ mem      │ user     │ watching │
├────┼────────┼─────────────┼─────────┼─────────┼──────────┼────────┼──────┼───────────┼──────────┼──────────┼──────────┼──────────┤
│ 0  │ app    │ default     │ 2.14.2  │ fork    │ 62       │ 0s     │ 0    │ online    │ 0%       │ 36.2mb   │ joplin   │ disabled │
└────┴────────┴─────────────┴─────────┴─────────┴──────────┴────────┴──────┴───────────┴──────────┴──────────┴──────────┴──────────┘
2024-05-31T09:52:21: PM2 log: [--no-daemon] Continue to stream logs
2024-05-31T09:52:21: PM2 log: [--no-daemon] Exit on target PM2 exit pid=51
09:52:32 0|app  | 2024-05-31 09:52:32: App: Starting server v2.14.2 (prod) on port 22300 and PID 62...
09:52:32 0|app  | 2024-05-31 09:52:32: App: Checking for time drift using NTP server: pool.ntp.org:123
09:52:33 0|app  | 2024-05-31 09:52:33: App: NTP time offset: -43ms
09:52:33 0|app  | 2024-05-31 09:52:33: App: Running in Docker: true
09:52:33 0|app  | 2024-05-31 09:52:33: App: Public base URL: https://myRaspi/joplinServer
09:52:33 0|app  | 2024-05-31 09:52:33: App: API base URL: https://myRaspi/joplinServer
09:52:33 0|app  | 2024-05-31 09:52:33: App: User content base URL: https://myRaspi/joplinServer
09:52:33 0|app  | 2024-05-31 09:52:33: App: Log dir: /home/joplin/packages/server/logs
09:52:33 0|app  | 2024-05-31 09:52:33: App: DB Config: {
09:52:33 0|app  |   client: 'pg',
09:52:33 0|app  |   name: 'joplinServerDb',
09:52:33 0|app  |   slowQueryLogEnabled: false,
09:52:33 0|app  |   slowQueryLogMinDuration: 1000,
09:52:33 0|app  |   autoMigration: true,
09:52:33 0|app  |   user: 'joplinServer',
09:52:33 0|app  |   password: '********',
09:52:33 0|app  |   port: 5432,
09:52:33 0|app  |   host: 'joplin_db'
09:52:33 0|app  | }
09:52:33 0|app  | 2024-05-31 09:52:33: App: Mailer Config: {
09:52:33 0|app  |   enabled: false,
09:52:33 0|app  |   host: '',
09:52:33 0|app  |   port: 465,
09:52:33 0|app  |   security: 'tls',
09:52:33 0|app  |   authUser: '',
09:52:33 0|app  |   authPassword: '********',
09:52:33 0|app  |   noReplyName: '',
09:52:33 0|app  |   noReplyEmail: ''
09:52:33 0|app  | }
09:52:33 0|app  | 2024-05-31 09:52:33: App: Content driver: { type: 1 }
09:52:33 0|app  | 2024-05-31 09:52:33: App: Content driver (fallback): null
09:52:33 0|app  | 2024-05-31 09:52:33: App: Trying to connect to database...
09:52:33 0|app  | 2024-05-31 09:52:33: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:34 0|app  | 2024-05-31 09:52:34: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:35 0|app  | 2024-05-31 09:52:35: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:36 0|app  | 2024-05-31 09:52:36: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:37 0|app  | 2024-05-31 09:52:37: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:38 0|app  | 2024-05-31 09:52:38: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:39 0|app  | 2024-05-31 09:52:39: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:40 0|app  | 2024-05-31 09:52:40: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:41 0|app  | 2024-05-31 09:52:41: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:42 0|app  | 2024-05-31 09:52:42: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:43 0|app  | 2024-05-31 09:52:43: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:44 0|app  | 2024-05-31 09:52:44: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:45 0|app  | 2024-05-31 09:52:45: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:46 0|app  | 2024-05-31 09:52:46: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:47 0|app  | 2024-05-31 09:52:47: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:48 0|app  | 2024-05-31 09:52:48: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:49 0|app  | 2024-05-31 09:52:49: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:50 0|app  | 2024-05-31 09:52:50: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:51 0|app  | 2024-05-31 09:52:51: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:52 0|app  | 2024-05-31 09:52:52: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:53 0|app  | 2024-05-31 09:52:53: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:54 0|app  | 2024-05-31 09:52:54: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:56 0|app  | 2024-05-31 09:52:56: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:57 0|app  | 2024-05-31 09:52:57: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:58 0|app  | 2024-05-31 09:52:58: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:52:59 0|app  | 2024-05-31 09:52:59: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:00 0|app  | 2024-05-31 09:53:00: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:01 0|app  | 2024-05-31 09:53:01: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:02 0|app  | 2024-05-31 09:53:02: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:03 0|app  | 2024-05-31 09:53:03: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:03 0|app  | 2024-05-31 09:53:03: [error] db: Timeout trying to connect to database: error: password authentication failed for user "joplinServer"
09:53:03 0|app  |     at Parser.parseErrorMessage (/home/joplin/packages/server/node_modules/pg-protocol/src/parser.ts:369:69)
09:53:03 0|app  |     at Parser.handlePacket (/home/joplin/packages/server/node_modules/pg-protocol/src/parser.ts:188:21)
09:53:03 0|app  |     at Parser.parse (/home/joplin/packages/server/node_modules/pg-protocol/src/parser.ts:103:30)
09:53:03 0|app  |     at Socket.<anonymous> (/home/joplin/packages/server/node_modules/pg-protocol/src/index.ts:7:48)
09:53:03 0|app  |     at Socket.emit (node:events:517:28)
09:53:03 0|app  |     at addChunk (node:internal/streams/readable:368:12)
09:53:03 0|app  |     at readableAddChunk (node:internal/streams/readable:341:9)
09:53:03 0|app  |     at Socket.Readable.push (node:internal/streams/readable:278:10)
09:53:03 0|app  |     at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
09:53:03 0|app  |   length: 108,
09:53:03 0|app  |   severity: 'FATAL',
09:53:03 0|app  |   code: '28P01',
09:53:03 0|app  |   detail: undefined,
09:53:03 0|app  |   hint: undefined,
09:53:03 0|app  |   position: undefined,
09:53:03 0|app  |   internalPosition: undefined,
09:53:03 0|app  |   internalQuery: undefined,
09:53:03 0|app  |   where: undefined,
09:53:03 0|app  |   schema: undefined,
09:53:03 0|app  |   table: undefined,
09:53:03 0|app  |   column: undefined,
09:53:03 0|app  |   dataType: undefined,
09:53:03 0|app  |   constraint: undefined,
09:53:03 0|app  |   file: 'auth.c',
09:53:03 0|app  |   line: '323',
09:53:03 0|app  |   routine: 'auth_failed'
09:53:03 0|app  | }
09:53:03 0|app  | Error: Timeout trying to connect to database. Last error was: password authentication failed for user "joplinServer"
09:53:03 0|app  |     at /home/joplin/packages/server/src/db.ts:119:10
09:53:03 0|app  |     at Generator.next (<anonymous>)
09:53:03 0|app  |     at fulfilled (/home/joplin/packages/server/dist/db.js:5:58)
09:53:03 0|app  |     at processTicksAndRejections (node:internal/process/task_queues:95:5)
2024-05-31T09:53:03: PM2 log: App [app:0] exited with code [1] via signal [SIGINT]
2024-05-31T09:53:03: PM2 log: App [app:0] will restart in 1000ms
09:53:03 PM2      | App [app:0] exited with code [1] via signal [SIGINT]
09:53:03 PM2      | App [app:0] will restart in 1000ms
2024-05-31T09:53:04: PM2 log: App [app:0] starting in -fork mode-
09:53:04 PM2      | App [app:0] starting in -fork mode-
2024-05-31T09:53:04: PM2 log: App [app:0] online
09:53:04 PM2      | App [app:0] online
09:53:14 0|app    | 2024-05-31 09:53:14: App: Starting server v2.14.2 (prod) on port 22300 and PID 77...
09:53:14 0|app    | 2024-05-31 09:53:14: App: Checking for time drift using NTP server: pool.ntp.org:123
09:53:14 0|app    | 2024-05-31 09:53:14: App: NTP time offset: -9ms
09:53:14 0|app    | 2024-05-31 09:53:14: App: Running in Docker: true
09:53:14 0|app    | 2024-05-31 09:53:14: App: Public base URL: https://myRaspi/joplinServer
09:53:14 0|app    | 2024-05-31 09:53:14: App: API base URL: https://myRaspi/joplinServer
09:53:14 0|app    | 2024-05-31 09:53:14: App: User content base URL: https://myRaspi/joplinServer
09:53:14 0|app    | 2024-05-31 09:53:14: App: Log dir: /home/joplin/packages/server/logs
09:53:14 0|app    | 2024-05-31 09:53:14: App: DB Config: {
09:53:14 0|app    |   client: 'pg',
09:53:14 0|app    |   name: 'joplinServerDb',
09:53:14 0|app    |   slowQueryLogEnabled: false,
09:53:14 0|app    |   slowQueryLogMinDuration: 1000,
09:53:14 0|app    |   autoMigration: true,
09:53:14 0|app    |   user: 'joplinServer',
09:53:14 0|app    |   password: '********',
09:53:14 0|app    |   port: 5432,
09:53:14 0|app    |   host: 'joplin_db'
09:53:14 0|app    | }
09:53:14 0|app    | 2024-05-31 09:53:14: App: Mailer Config: {
09:53:14 0|app    |   enabled: false,
09:53:14 0|app    |   host: '',
09:53:14 0|app    |   port: 465,
09:53:14 0|app    |   security: 'tls',
09:53:14 0|app    |   authUser: '',
09:53:14 0|app    |   authPassword: '********',
09:53:14 0|app    |   noReplyName: '',
09:53:14 0|app    |   noReplyEmail: ''
09:53:14 0|app    | }
09:53:14 0|app    | 2024-05-31 09:53:14: App: Content driver: { type: 1 }
09:53:14 0|app    | 2024-05-31 09:53:14: App: Content driver (fallback): null
09:53:14 0|app    | 2024-05-31 09:53:14: App: Trying to connect to database...
09:53:14 0|app    | 2024-05-31 09:53:14: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:15 0|app    | 2024-05-31 09:53:15: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:16 0|app    | 2024-05-31 09:53:16: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:17 0|app    | 2024-05-31 09:53:17: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:18 0|app    | 2024-05-31 09:53:18: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:19 0|app    | 2024-05-31 09:53:19: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:20 0|app    | 2024-05-31 09:53:20: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:21 0|app    | 2024-05-31 09:53:21: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:22 0|app    | 2024-05-31 09:53:22: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:23 0|app    | 2024-05-31 09:53:23: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:24 0|app    | 2024-05-31 09:53:24: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:25 0|app    | 2024-05-31 09:53:25: db: Could not connect. Will try again. password authentication failed for user "joplinServer"
09:53:26 0|app    | 2024-05-31 09:53:26: db: Could not connect. Will try again. password authentication failed for user "joplinServer"

What's going wrong here and how can I migrate my Joplin data from PostgreSQL 13 to 16?

Thank you!

2

Hello,

are you sure your backup actually contained not just data, but also the users?
Have you tried connecting to the DB manually and verifying that?

That's the first thing I'd check.

3

Thx for helping me with this issue :muscle:t2:

I can successfully connect to the DB inside the container using psql -U joplinServer -d joplinServerDb. Do you know which table I'd have to check?

4

@zblesk here's what I tried:

myUser@myRaspi:~/Development/Gitea/RaspiConfig/Docker/40-JoplinServer $ docker exec -it joplin_db bash
root@592c9e2eec79:/# psql -U joplinServer -d joplinServerDb
joplinServerDb=# select count(*) from public.users;
 count
-------
     6
(1 row)

So it seems that the users were imported successfully.

5

No, my bad. I meant postgres users, not joplin users.

But looking at the -U joplinServer there it seems that you do indeed have the user working. If you can connect from within the container and using the same password that's in your dockerfile, then I'm afraid I'm out of ideas, sorry. :frowning:

6

@laurent: do you have an idea?

7

No idea

8

Any other ideas, e.g. increasing log levels, creating a dump, etc.? At the moment, I'm completely blocked with the migration and therefore bound to PostgreSQL 13 which is really old :sleepy:

9

Could it be an issue with your Joplin-Server Container? I've done my Upgrade with the original Joplin-Server Container and it works.

Maybe you find a way with this Post. I was just copying Code snippets and don't know realy what I do. :sweat_smile:
But it works.

Edit:
It looks like I've used another command to restore the Database:

docker exec joplin_db pg_restore --clean -U joplin -d joplin /var/lib/postgresql/data/backup.dump

"joplin" hast to be changed by you.
-U joplinServer -d joplinServerDb

Oh. And I have used a custom Format for the Backup. "-Fc" *.dump. I don't know If this is important.

Edit 2:
And I've made no dumpall. :thinking: I only saved the Database.

1 Like
10

Thx for your reply! I will give it a try and report back :upside_down_face:

11

Thank you very very very much for your help - your approach worked like a charm! :clap:t2:

1 Like
12

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.