Issues with migration from Default SQLite to PostgreSQL in n8n Docker Deployment

[tungnguyen249]

Hi n8n Community,

I’m currently facing multiple challenges with my n8n deployment in a Docker environment, and I’m seeking assistance to resolve these issues. Here’s a summary of the problems I’m encountering:

1. SQLite Dump Error:

• I used sqlite3 to dump my SQLite database to an SQL file.
• When attempting to open the SQL file using the sqlite command, I receive the error: “Error: unable to open database ‘./var/lib/docker/volumes/n8n_data/_data/database.sqlite’: unable to open database file.”

2. SQLite Database Password:

• I attempted to open the SQL file with DB Browser for SQLite, and it prompts for a password.
• I couldn’t find any information on the default password for the SQLite database.

3. PostgreSQL Restore Error:

• I’m trying to migrate from SQLite to PostgreSQL using Docker Compose.
• The command docker exec -i postgres_container_name psql -U postgres_user -d database < /path/to/sqlite_dump.sql results in the error: “ERROR: current transaction is aborted, commands ignored until end of transaction block.”

4. Failed Attempts:

• I have tried removing the old database, recreating it, and restoring the data, but the error persists.

I would greatly appreciate any insights, suggestions, or guidance from the community on how to troubleshoot and resolve these issues.

Thank you in advance for your assistance!

Information on your n8n setup

• n8n version: n8n/1.22.6 linux-x64 node-v18.18.2
• Database (default: SQLite): SQLite
• n8n EXECUTIONS_PROCESS setting (default: own, main): default
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
• Operating system: Ubuntu

[Jon]

Hey @tungnguyen249,

The way we currently recommend migrating is…

Use the CLI to export all workflows and credentials
Update the n8n env options to use your new database
Use the CLI to import all workflows and credentials

If you are tryting to take the output from sqlite and importing it directly into postgres there is a good chance you will get some errors as they are not the same. I believe there are some tools out there that try to make this easier but they are not something we directly support so it would be at your own risk.

We also don’t set a password for the sqlite database you should be able to just open it but before you try it would be worth making sure that n8n isn’t running first.