PostgreSQL backend should attempt to reconnect to the database

[brocaar]

Hi, thanks again for your project. I think it is a great addition to Mosquitto!

However, I noticed on issue. I'm using the plugin with a PostgreSQL database. I just rebooted one of my servers and noticed that Mosquitto was not running after the reboot. Looking at the logs, it simply stopped with the following error:

|-- We were unable to connect to the database
|-- *** ABORT.

The reason is that Mosquitto was started before the PostgreSQL database was started. I think it would be better to put the database connection in a loop and log when it can't connect, but keep Mosquitto running. That way, it would recover eventually.

[jasiek]

I think this would be useful - reconnect to the database if the connection is dead.

[brocaar]

That, and when I reboot the server, Mosquitto is started before PostgreSQL. This means that after a boot I have to start Mosquitto manually as it could not start at first.

Hopefully this can be changed so that a broken db connection will never result into Mosquitto aborting.

[jasiek]

@jpmens How do these plugins work? Is there a plugin instance per thread within mosquitto or is there a single instance which is shared?

[jpmens]

@brocaar the order of service starts (mosquitto, pgsql) is set up by your packages/distribution and beyond the scope of this project.

[jpmens]

@jasiek there's a single shared instance.

[jpmens]

I agree that in case the database goes down (short restart), we should attempt to reconnect to the backend database if possible, and I'll take a pull-request which does that.

But please note again: this should not be required to cater for Mosquitto launches before database.

[jasiek]

I'll try to put something together in the next few days.

[tidanilocarvalho]

Hi, I am using the plugin, this is awesome, but the same problem happen when the database is restarted, I like would this feature reconnect database too.

[tmcdos]

This plugin is amazing but I also think the auto-reconnect feature is highly desirable. I vote for it.

[tidanilocarvalho]

I vote for it too, and how you can see, there are many testers to help.

[jpmens]

It's grand you're all voting for it, so I'll add my vote as well.

Now we just need somebody to write the code, eh? ;-)

[tidanilocarvalho]

Well, I just tried help writing here about my point, but, I can help write code too, we are using heavily your plugin.

[jasiek]

If only there was someone to do the actual work...

It's on my list, but pretty low. I might get around to it in ca 3 weeks.

[jklimke]

Is there any progress here?

[jasiek]

Not really. Feel free to pick this up.

[jklimke]

Actually i gave an implementation a try. Since PQreset(conn) tries to restore the database connection, the database connection should now be restored after being lost. It would be nice if anyone could have a look at the pull request, since this missing feature was killing our mosquitto each time when an database upgrade was happening.

[tidanilocarvalho]

Hi, how are you guys?

I know that this topic was closed, but, I've tested the new implementation and it is work very well, perfectly!!!

Thanks a lot!!!