I switched to postgres after using sqlite about a month ago. I rebooted a few times and everything seemed to be working fine. Then a few days ago we had a really long power outage due to severe weather. After booting I noticed that all of my workflows were missing. I checked the database and the tables are still there but they are now empty. Is there something I missed while setting it up? I’m not really familiar with postgres.
Welcome to the community @OverHeal!
So just to understand you correctly. You did switch from SQLite to Postgres but you did not actually copy or export/import the workflows and credentials manually.
You did simply switch over (or at least you thought you switched) and they were still there?
I backed them up manually and then imported them again after a clean install via the web app.
Ah ok, then this makes sense now.
Are you sure that it actually used Postgres? Does it now use Postgres? So if you save a workflow does it show up correctly in the table?
I am asking because I can currently not think of a way how data would be deleted from the database. The only thing I can think of is that it actually never used Postgres and it rather somehow saved it still in SQLite somewhere but did not persist that data because of the docker-setup (assuming you use docker, but without it I can even less explain it).
After looking again at the workflow_entity table it seems i just neglected to scroll down, and when I do, I can see the data from my original workflows but not the test workflow I just made. It looks like n8n might be using sqlite again instead of postgres for some reason though. I see the database.sqlite file exists and is being written to.
Reloading with --update-env worked. I assume it’s because pm2 starts n8n before the environment variables are set. So that is the problem I need to correct.
Ah glad to hear that you could figure out the issue.