Hi there,
I maintain an internet server for syncing data among the family.
For joplin I installed joplin-server (using docker compose). It uses postgresql. as database.
Now (for some reason) I try to move from one server to another one.
So I need to backup the data on the server and restore it on the new one.
BUT, ... (for some other reasons) I'd like to use a different db_name, db_user and db_password.
Now I struggle with backup and restore...
For the backup file I use the command:
pg_dump -U {{ joplin.db_user }} --clean --no-owner --no-privileges -d {{ joplin.db_name }} > db-dump.sql
To restore the backup on the new server (with a fresh joplin database using the new credentials) I do a:
psql -U {{ joplin.new_db_user }} {{ joplin.new_db_name }} < /tmp/db-dump.sql
It seems to work (partly...).
In the joplin app, I can use my old credentials and synchronize with the new server.
But I can see only a few notebooks and only a shared folder and nothing more.
Only 9 of 26 notes are there...
Is there a better backup and restore strategy for joplin server?
Little update:
I found an error during the restore:
2023-11-20 22:39:16.402 UTC [69] ERROR: relation "knex_migrations" does not exist at character 20
2023-11-20 22:39:16.402 UTC [69] STATEMENT: select "name" from "knex_migrations" order by "id" desc limit $1
So I added a "--if-exists" to the backup command, which is now:
pg_dump -U {{ joplin.db_user }} --clean --if-exists --no-owner --no-privileges -d {{ joplin.db_name }} > db-dump.sql
Result is, that there is no error during restore, but after a sync to a client, I see not all notebooks and only some of the notes...
so, nobody was able to answer my question, so I do...
There was no problem in the database backup and restore commands, but somehow my notebooks where not correctly synchronized to the server.
I found the Button "Re-upload local data to sync target" in the Preferences > Synchronization > Show Advanced Settings.
This forced the client to sync everything to the server.
Then the backup and restore commands did their job and the test installation of joplin that synchronized with the new server found them all.
Backup is done using command:
pg_dump -U {{ joplin.db_user }} --no-owner -d {{ joplin.db_name }} > db-dump.sql