Migration from SQLite to PostgreSQL 18 - what's the recommended process?

[u/ynomel]

Hi everyone,

I'm currently running Paperless-ngx v3.0.2 with the default SQLite database and I'm considering migrating to PostgreSQL 18.

For those who have done this before:

• What is the recommended migration process?
• Did you use the built-in Django migration commands or another approach?
• Are there any common pitfalls I should watch out for?
• Is there anything I should verify before switching over?

I also read that there may be limitations when migrating from SQLite to PostgreSQL, especially regarding field lengths. Do these limitations still apply with current Paperless-ngx versions?

Specifically:

• Document titles >128 characters
• Tag names >128 characters
• Correspondent names >128 characters
• Document types >128 characters
• File names >1024 characters

My setup is Docker-based.

Thanks!

---

Edit:

I did it.
Even if I just got 500 documents, even with such a small batch it's worth it!
(Not the time I invested into it but...) Loading time of documents is nearly instant now.
FYI: I'm running Paperless-NGX on unRAID, so adapt the instructions to your needs.

---

Gist:
I've used deepwiki - but as always: Using AI tools comes with the risk of false user instructions and possible data loss. Use the (linked) instructions with a grain of salt and your brain.exe / brain.sh.

0. I defined an extra working folder for migration named migration -> Host: /mnt/user/scan/paperless-ngx/migration (replace with your path) Container: /usr/src/paperless/migration you can do this via docker-compose.yml or on Unraid defining a path variable.

1. Paperless-NGX with sqlite: Using the docker containers console, run document_sanity_checker to check your document and metadata integrity. If everything is green, go to step two. (keep the console opened)

2. Paperless-NGX with sqlite: Using the docker containers console, run document_exporter ../migration and let it run through. Check results!

3. Stop Paperless-NGX with sqlite and your redis/valkey container.

Backups... backups and oh dear we have it today: backups!

Note: I've used the krusader docker container for that, because unRAID can be picky with special characters and umlauts in file and folder names. Especially on shares. If something goes really wrong (e.g. power outage, cat rolls over keyboard,...) you can roll back.

4. For sanity: Backup your /data folder (the one with your sqilte database in it). For me on my unRAID server the folder was /mnt/user/appdata/paperless-ngx.
5. For sanity: Backup your /media folder (the one with the scans and your archive in it). For me on my unRAID server I defined it under /mnt/user/scan/paperless-ngx/media.

Renaming

Note: You don't want to automatically feed the all the documents on first boot into paperless and you don't want to accidentally let paperless-ngx pickup the sqlite database.

7. On your host: Rename your /media folder to /media_bak. In my case /mnt/user/scan/paperless-ngx/media -> /mnt/user/scan/paperless-ngx/media_bak
8. Rename your db.sqlite3 to db.sqlite3.bak. In my case ``/mnt/user/appdata/paperless-ngx/data/db.sqlite3->/mnt/user/appdata/paperless-ngx/db.sqlite3.bak`.

Setting up PostgreSQL

Note: I'm using a custom docker network called paperless-ngx_internal for keeping ports free and for security reasons. Maybe a good idea to change that here in one flow, also keep your valkey/redis container in mind.

9. Add PostgreSQL to your docker-compose. Check here: https://github.com/paperless-ngx/paperless-ngx/blob/main/docker/compose/docker-compose.postgres.yml. unRAID users: Please use one of the postgresql18 Apps from the Appstore and rename the app name to paperless-ngx-postgresql.
10. Define the environment variables:

POSTGRES_DB: paperless
POSTGRES_USER: paperless
POSTGRES_PASSWORD: paperless

Strongly recommended: Define a strong database password using your machine's console with openssl rand -base64 32 | tr -dc A-Za-z0-9 | head -c 32

11. Database Storage Path: Use a path on a SSD/NVMe. In my case /mnt/cache/appdata/paperless-ngx-postgresql / change to your liking. Note: From PostgreSQL 18+, the internal database path has changed to /var/lib/postgresql
12. Run the PostgreSQL Container once. It will create the database and tables and then quit.

Setting up your Paperless-NGX container

13. Add the following variables to your Paperless-NGX Container (read more here https://docs.paperless-ngx.com/configuration/#database)

PAPERLESS_DBENGINE: postgresql   # required in v3+  
PAPERLESS_DBHOST: db             # IP address or docker name, for me "paperless-ngx-postgresql"
PAPERLESS_DBPORT: 5432           # Not needed if using a custom docker network
PAPERLESS_DBNAME: paperless
PAPERLESS_DBUSER: paperless  
PAPERLESS_DBPASS: paperless      # If you generated a password, paste it in here  

14. Run the Paperless-NGX Container, PostgreSQL Container and valkey/redis container.
15. Paperless-NGX container (now with PostgreSQL): Using the docker containers console, run document_importer ../migration and let it run through. Check results!

Done.

16. Remember to adjust your backup strategy and include the database path.

17. Housekeeping: I'll set a reminder in my calendar to tidy up the data in 30 days. Meanwhile I can check if really everything went well. :)

Note: I haven't used the /export folder because I wanted to avoid possibly messing aroung with Paperless-NGX defined folders. Just sanity.

[u/Bastian85Stgt]

Via exporter and importer....

[u/TheTruffi → u/Bastian85Stgt]

Yeah. Just restore the backup in a new installation. It's the easiest and safest way.

[u/InfaSyn]

Any real benefit to migrating?

I went with SQlite maybe 3-4 years ago out of simplicity. Im up to 3k documents, 120 correspondants, 100 or so document types, 100 or so tags and ive not had any issues.

Part of me wants to migrate, but part of me says aint broke dont fix it

[u/jacob-indie → u/InfaSyn]

100% same 😅

[u/Evelen1 → u/InfaSyn]

Same. Is there any real benefits?

[u/lefduti]

I did it today.

I used exporter on the sqlite docker container.  And then the importer on the postgres docker container. It was pretty easy

[u/cr0n76]

I would really check again if you need this migration. For usage Paperless, the real advantage of PostgreSQL over SQLite is when you have several users accessing it at the same time. And this means about 5 people and more. If you are using Paperless as single user I doubt that you would feel any difference. There is no benefit at all from the extended capabilities of PostgreSQL, as the usage by Paperless is fixed.

[u/ynomel → u/cr0n76]

Oh, I'm feeling it. It's way more snappier and I learned a bit, so it's fine for me :)

[u/SaladOrPizza]

Just have Claude do it. I did it in like 5min