Trying to rename messages table as messages_backup when messages_backup already exists.

[hsaidc]

I updated my node installed using the nwaku-compose repository to version 0.26.0. After stopping the containers, I pulled commit #74 and restarted them using docker-compose up -d (New version of nwaku image is downloaded). However, upon checking Grafana, I noticed no connected peers. Attempting to restart the containers again, I reviewed the nwaku logs and found the following errors

nwaku-1  | DBG 2024-03-12 12:59:02.393+00:00 Registering RLN validator                  topics="waku node" tid=1 file=waku_node.nim:1001
nwaku-1  | DBG 2024-03-12 12:59:02.427+00:00 starting message store's postgres database migration topics="waku archive migration" tid=1 file=migrations.nim:65
nwaku-1  | INF 2024-03-12 12:59:02.456+00:00 database schema is outdated                topics="waku archive migration" tid=1 file=migrations.nim:75 currentVersion=1 targetVersion=2
nwaku-1  | DBG 2024-03-12 12:59:02.456+00:00 executing migration statement              topics="waku archive migration" tid=1 file=migrations.nim:84 statement="ALTER TABLE messages RENAME TO messages_backup;"
nwaku-1  | ERR 2024-03-12 12:59:02.458+00:00 failed to execute migration statement      topics="waku archive migration" tid=1 file=migrations.nim:87 statement="ALTER TABLE messages RENAME TO messages_backup;" error="error in performWriteQuery: error in asyncpool query: error in dbConnQuery calling waitQueryToFinish: error in query: ERROR:  relation \"messages_backup\" already exists\n"
nwaku-1  | ERR 2024-03-12 12:59:02.458+00:00 4/7 Mounting protocols failed              topics="wakunode main" tid=1 file=wakunode2.nim:150 error="failed to setup archive driver: ArchiveDriver build failed in migration: failed to execute migration statement"
nwaku-1 exited with code 0

Then, I had to manually remove public.messages_backup table to make my node running again (I connected to docker and removed that table.). I wanted to let you know about the issue. You can access my Grafana dashboard via: http://35.199.170.55:3000/d/yns_4vFVk/nwaku-monitoring?orgId=1&refresh=1m

[Ivansete-status]

@hsaidc thanks so much indeed for submitting this issue with such great detail!

I'm suggesting a naïve enhancement that would mitigate this problem.

I could not replicate it. I've tried it many times, with databases bigger than 3GB and it worked well.

[keshzombie]

Is there a guide or instruction to delete the public.messages_backup table?

[Ivansete-status]

Is there a guide or instruction to delete the public.messages_backup table?
Follow these:

You would need to install psql client (https://askubuntu.com/questions/1040765/how-to-install-psql-without-postgres)

Then, open a terminal and run the following to enter the database:
PGPASSWORD=test123 psql -U postgres -h 127.0.0.1 -p 5432 -w

Then, remove the table:
DROP TABLE messages_backup;

[nirnaeth-arnoediad]

PGPASSWORD=test123 psql -U postgres -h 127.0.0.1 -p 5432 -w

ser after command it says wrong password for me.

root@nirnaegen:~# PGPASSWORD=test123 psql -U postgres -h 127.0.0.1 -p 5432 -w
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "postgres"
connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "postgres"