Migrate from sqlite to PostgreSQL

tom · 6 September 2023 12:55

Hello,

I use Joplin Server as a Docker container and I am very satisfied.

I entered credentials for the postgreSQL-database during the installation, as recommended. However, as the postgreSQL-database hadn't been created, I am sure that this is not used.

I have two questions regarding this:

How can I switch from SQLite to PostgreSQL?
And where can I find the SQLite database in my container? I just can't find it

I have many *.md-files on the server in the "items" folder. Is it possible that I don't use a database at all?

On the other hand I have a "joplin-db-1" (postgres:15) container running within docker. I'm confused...?

Thanks in advance

andrewdolphin · 7 September 2023 19:34

How exactly did you create the server docker container?

Did you use docker-compose? If so can you copy/paste your compose document here (obfuscating passwords etc)?

tom · 8 September 2023 07:06

Thanks for the quick reply and sorry for not posting this configuration data from the beginning.

I installed this via a "stacks" in Portainer, via this tutorial with this configuration data:

version: '3'

services:
    db:
        image: postgres:15
        volumes:
            - /docker/joplindb:/var/lib/postgresql/data
        ports:
            - "5432:5432"
        restart: unless-stopped
        environment:
            - POSTGRES_PASSWORD=postgres
            - POSTGRES_USER=postgres
            - POSTGRES_DB=joplin
    app:
        image: joplin/server:latest
        depends_on:
            - db
        ports:
            - "22300:22300"
        restart: unless-stopped
        environment:
            - APP_PORT=22300
            - APP_BASE_URL=https://websiteurl.com
            - DB_CLIENT=pg
            - POSTGRES_PASSWORD=postgres
            - POSTGRES_DATABASE=joplin
            - POSTGRES_USER=postgres
            - POSTGRES_PORT=5432
            - POSTGRES_HOST=db
            - MAILER_ENABLED=1
            - MAILER_HOST=smtp.gmail.com
            - MAILER_PORT=465
            - MAILER_SECURE=1
            - MAILER_AUTH_USER=youremail@gmail.com
            - MAILER_AUTH_PASSWORD=Y0urP@ssw0rd
            - MAILER_NOREPLY_NAME=Joplin
            - MAILER_NOREPLY_EMAIL=email@email.com
            
volumes:
  joplindb:

Joplin Server works now.

I now can see there two containers:

But still I have the impression, that Joplin is not using sqlite/postgreSQL an still is uploading *md-files to my server:

I cannot find a SQLite file on the server, nor a PostgreSQL database (postgre not installed on system, /var/lib/postgresql/data doesn't exist.)

However, if I understood correctly, the database solution is recommended for productive use. Therefore I would like to change this very much and would be very happy about a tip how I can do this.

Thank you so much for your support.

andrewdolphin · 8 September 2023 08:56

Thanks,

Can you post the logs from the joplin server container? Preferably what happens at start up.

The postgres container logs would be useful also.

That docker compose should be using the postgres database but I suspect you have a volume issue.

Does the directory "/docker/joplindb" exist on your filesystem?

tom · 8 September 2023 10:37

Thank you so much for your support, I really your help!

Here are the log files:

JoplinDB:

2023-09-08 10:19:34.286 UTC [1] LOG:  received fast shutdown request
2023-09-08 10:19:34.289 UTC [1] LOG:  aborting any active transactions
2023-09-08 10:19:34.295 UTC [141] FATAL:  terminating connection due to administrator command
2023-09-08 10:19:34.304 UTC [1] LOG:  background worker "logical replication launcher" (PID 31) exited with exit code 1
2023-09-08 10:19:34.304 UTC [32] FATAL:  terminating connection due to administrator command
2023-09-08 10:19:34.309 UTC [26] LOG:  shutting down
2023-09-08 10:19:34.311 UTC [26] LOG:  checkpoint starting: shutdown immediate
2023-09-08 10:19:34.320 UTC [26] LOG:  checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.002 s, total=0.011 s; sync files=2, longest=0.002 s, average=0.001 s; distance=5 kB, estimate=78 kB
2023-09-08 10:19:34.326 UTC [1] LOG:  database system is shut down
PostgreSQL Database directory appears to contain a database; Skipping initialization
2023-09-08 10:19:36.870 UTC [1] LOG:  starting PostgreSQL 15.4 (Debian 15.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-09-08 10:19:36.870 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-09-08 10:19:36.870 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-09-08 10:19:36.873 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-08 10:19:36.883 UTC [28] LOG:  database system was shut down at 2023-09-08 10:19:34 UTC
2023-09-08 10:19:36.892 UTC [1] LOG:  database system is ready to accept connections

JoplinApp:

2023-09-08 10:27:13: App: Content driver: { type: 1 }
2023-09-08 10:27:13: App: Content driver (fallback): null
2023-09-08 10:27:13: App: Trying to connect to database...
2023-09-08 10:27:13: App: Connection check: {
  latestMigration: { name: '20221020143305_task_states.js', done: true },
  isCreated: true,
  error: null
}
2023-09-08 10:27:13: App: Auto-migrating database...
2023-09-08 10:27:13: App: Latest migration: { name: '20221020143305_task_states.js', done: true }
2023-09-08 10:27:13: App: Performing main storage check...
2023-09-08 10:27:13: App: Database storage is special and cannot be checked this way. If the connection to the database was successful then the storage driver should work too.
2023-09-08 10:27:13: App: Starting services...
2023-09-08 10:27:13: ShareService: Starting maintenance...
2023-09-08 10:27:13: EmailService: Starting maintenance...
2023-09-08 10:27:13: TaskService: Scheduling #1 (Delete expired tokens): 0 */6 * * *
2023-09-08 10:27:13: TaskService: Scheduling #2 (Update total sizes): 0 * * * *
2023-09-08 10:27:13: TaskService: Scheduling #3 (Process oversized accounts): 30 */2 * * *
2023-09-08 10:27:13: TaskService: Scheduling #6 (Delete expired sessions): 0 */6 * * *
2023-09-08 10:27:13: TaskService: Scheduling #7 (Compress old changes): 0 0 */2 * *
2023-09-08 10:27:13: TaskService: Scheduling #8 (Process user deletions): 10 * * * *
2023-09-08 10:27:13: TaskService: Scheduling #10 (Process orphaned items): 15 * * * *
2023-09-08 10:27:13: App: Call this for testing: `curl https://domain.tld/api/ping`
2023-09-08 10:27:13: ShareService: Maintenance completed in 46ms
2023-09-08 10:27:14: EmailService: Transporter is operational - service will be enabled
2023-09-08 10:27:14: EmailService: Maintenance completed in 238ms

When I connect via SSH, I don't see any folder under /docker, or /docker/volumes:

There is also no volume in Portainer:

Did I miss something regarding creating a volume during the setup?

Thank you so much in advance!

tom · 8 September 2023 15:23

What I recignized:

Ist it relevant that I'm using Ubuntu? Why does it say Debian?

Also: I've created a volume called "joplindb"...not sure yet if that's necessary but the stack-code seems tor require it?

andrewdolphin · 8 September 2023 19:21

You're mounting the database location to /docker/joplindb, that would be a docker directory in your root directory on your local filesystem, is there anything there?

The volume section in your compose file isn't needed as you are mounting to the above location and not an alias.

tom · 8 September 2023 20:05

Oh yes! There is such a folder!

I was always looking in /var/lib/docker

Does that mean, that I'm currently using postgresql? I'm wondering, as I haven't created a database at thr beginning?

So there is no necessity to create that volume?

tom · 10 September 2023 11:35

The thing that I don't understand is:

why do I have so many *.md files to be found via the web interface? These files should be in the postgresql-database, shouldn't they?
that I am not able to do a database-backup via console. How do you guys do that?

Thanks!

dpoulton · 10 September 2023 22:06

You are using the admin account as your sync account. I believe that only the admin account has an items view and what it is showing are the sync files stored in your Postgresql database.

tom · 11 September 2023 10:00

Thank you so much, that makes sense!

I would like to make a backup of the PostgreSQL database. I was thankfully tipped here in the thread where to find the database folder:

/docker/joplindb

However, I am not sure how to backup the database. Is it enough to backup the folder?

Do you have a hint for me?

andrewdolphin · 11 September 2023 12:35

You have a few options but backing up the folder is one of them yes.

tom · 11 September 2023 15:28

Thanks a lot!

Something strange that caught my eye:

I changed the name of the folder "joplindb" and still everything works and the data is accessible and it synchronizes.

Somehow I'm still not really convinced that I'm using the right database...

That, hopefully, is my last question. Thanks a lot in advance!

apj68 · 12 September 2023 15:26

Have you tried using the postgres credentials and trying to logon and poke around?

tom · 12 September 2023 18:57

What do you mean by that? Where should I use the postgre-credentials?

apj68 · 13 September 2023 00:26

It seems that you ought to be able to use any database client to connect to your database and check around the tables and whatnot to see if you can validate/invalidate your theory that the db is not being used. Note that I've not done this, I'm just another Joe User like yourself but from your logs it does appear that the database is being started so provided it's not encrypted I'm guessing you can logon to it.

tom · 15 September 2023 08:19

Sorry, but I still don't understand this properly

I can change the PostgreSQL folder and still log in completely and also use Joplin via the apps.

Does this mean that the database path doesn't seem to be used at all?

Likewise, I can't reach the database via shell, so I can't make a backup. How can that be?

Or is it really enough that I simply save the folder as a ZIP file and then have a working backup?

kja999 · 15 September 2023 12:59

as you have docker already, use the pgAdmin4 docker image, it's a tool for postgres db admin.
details here: Container Deployment — pgAdmin 4 7.6 documentation

After launching it (it creates a web admin page), add a server connection to your joplin database using the server db credentials in your original docker command for the db.
Then you can browse the database and see what its up to...

(it also helps you run the backups)

Hint: In portainer I added the pgAdmin docker container to the joplin server db network so they could talk easier...

EDIT: I should add.. messing around in there potentially is dangerous to your data ... be careful