Lost everything after upgrade from 1.9.3 to 1.17.1

Hello,

I did upgrade as usually with docker-compose.

I was running version 1.9.3 and the lastet image pull was 1.17.1.

When I did restart, it went to create user and when I was finally connected, all my workflows were lost.

Here is my docker-compose.yml

version: '3.1'

services:
  postgres:
    image: postgres:14
    restart: always
    environment:
      - POSTGRES_USER
      - POSTGRES_PASSWORD
      - POSTGRES_DB
      - POSTGRES_NON_ROOT_USER
      - POSTGRES_NON_ROOT_PASSWORD
    ports:
      - 5432:5432
    volumes:
      - /home/admin/docker/n8n/init-data.sh:/docker-entrypoint-initdb.d/init-dat
a.sh

  n8n:
    image: docker.n8n.io/n8nio/n8n
    restart: always
    environment:
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=${POSTGRES_DB}
      - DB_POSTGRESDB_USER=${POSTGRES_NON_ROOT_USER}
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_NON_ROOT_PASSWORD}
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER
      - N8N_BASIC_AUTH_PASSWORD
      - GENERIC_TIMEZONE=Europe/Paris
      - EXECUTIONS_DATA_PRUNE=true
      - EXECUTIONS_DATA_MAX_AGE=168
      - EXECUTIONS_DATA_PRUNE_MAX_COUNT=50000
      - EXECUTIONS_MODE=queue
      - QUEUE_BULL_REDIS_HOST=n8n_redis
      - QUEUE_BULL_REDIS_PORT=6379
      - N8N_ENCRYPTION_KEY=Qx3uMeeZv07Fmg5rphLQIBUTKTNMxhKt
    ports:
      - 5678:5678
    links:
      - postgres
      - redis
      - n8n_worker
    volumes:
      - /home/admin/docker/n8n/data:/home/node/.n8n
    # Wait 5 seconds to start n8n to make sure that PostgreSQL is ready
    # when n8n tries to connect to it
    # command: /bin/sh -c "sleep 5; n8n start"

  n8n_worker:
    image: docker.n8n.io/n8nio/n8n
    command: worker
    restart: always
    environment:
      # ----- redis ----- #
      - QUEUE_BULL_REDIS_HOST=n8n_redis
      - QUEUE_BULL_REDIS_PORT=6379
      # ----- postgres ----- #
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_DATABASE=${POSTGRES_DB}
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_USER=${POSTGRES_NON_ROOT_USER}
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_NON_ROOT_PASSWORD}
      - N8N_ENCRYPTION_KEY=zzzz
    ports:
      - 5676:5678
    links:
      - postgres
      - redis

  redis:
    container_name: n8n_redis
    image: redis:latest
    restart: always
    ports:
      - 6379:6379

I did try to revert to the previous image, but still the same issue. Any idea to make it work again ?

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:
  • n8n version: 1.17.1
  • Database (default: SQLite): PostgreSQL 14
  • n8n EXECUTIONS_PROCESS setting (default: own, main): queue
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: Debian GNU/Linux 11 (bullseye)

Any suggestion are welcomed :wink:

Hey @mcc37,
I am really sorry for you. There is no chance that you get your data back.

The configuration of the postgres DB is not correct to store data persistently.

Every data in docker containers is lost after restarting them. That is a core concept of docker. That’s why it is crucial to store all important data in docker volumes. This is missing in your compose file for the postgres service.

volumes:
  # In this example, we share the folder `db-data` in our root repository, with the default PostgreSQL data path
  # It means that every time the repository is modifying the data inside
  # of `/var/lib/postgresql/data/`, automatically the change will appear in `db-data`
  # You don't need to create the `db-data` folder. Docker Compose will do it for you
  - ./db-data/:/var/lib/postgresql/data/

This is from the official postgres documentation. And it is missing in your config.

It is always good to create a full backup before updating any system. You’ll find backup workflow in the template section of n8n.

Cheers

1 Like

I know this, but this configuration (docker-compose.yml) was the one recommended 2 years ago by N8N, and I have been upgrading quite a lot of time with no issue at all, including postgresql container image upgrade and migrating to 1.x version of N8N.

So it means something has changed at a moment on N8N side ?

Also, I do have backup of PostreSQL backup, is there an easy way to extract workflow and credentials ? I don’t matter about past execution.

Hey @mcc37,

I am not aware of any changes our side but if your database container updated at the same time it will remove your data. From what I can see our examples include the binary data option for the database although we also still use Postgres 11 in most of out examples.

Do you have a link to the guide you followed so I can make sure it is updated? As for your workflows if you have a backup of the databse you could try restoring that and you should be good to go.

Hi Jon, thanks for your reply.

I used the previous docker-compose file that were available on GitHub. It seems they have changed.

What I don’t explain is why I have been able to do at least 30 upgrades without any issue and why I have this issue now ?

Hey @mcc37,

I suspect someone changed the postgres version in your docker compose file, I have done some digging and we have used version 11 since 2019 and the volume was only added in 2022 which should have been there from the start.

If your postgres version changed that would have resulted in the data being lost when the new version of postgres was downloaded, Sadly this wouldn’t have been a change that we made but I would recommend taking some time now to configure your postgres database to use a volume to store the data so this doesn’t happen again in the future.

I did not change the version in my docker file, so pretty strange. But I think the issue is about this original file from 2019, I did start using the solution in 2021, so it matches.

Hey @mcc37,

It could be with that file but I would suggest double checking your server as looking at the history of that file we have not used version 14 for Postgres so that change could have been something you initially did or someone else has done it.

Not that it matters though it looks like Postgres was not correctly configured from the start and we have now fixed that in our examples which will prevent it from happening in the future, As long as your backup is good a restore should get you back up and running.

I struggle to restore my postgresql backup, but I can see the n8n stuff in the backup.sql file.

Is there any simple way to extract all workflows code from it directly from sql file ?

Another question @Jon, with the new docker file in queue mode, I can not connect anymore via the network to the PostgreSQL server, is there any reason ? I would like to backup the new one too :sweat_smile:

Hey @mcc37,

You should still be able to connect to the database, Have you configured the docker network to allow access?

Hi @Jon

It was so obvious … that it takes me a while to find the issue !

In fact, your default docker-compose template for queue mode does not expose the database port. It is missing

ports:
      - 5432:5432

Just added this in my docker-compose file and evrything is working well now !

2 Likes