SQL SHUTDOWN Command or Endpoint?

[lvca]

Discussed in #694

^{Originally posted by gramian December 20, 2022}
Dear Developers,

as far as I can see there is no way to remotely shutdown the database server, apart from SSHing into the machine, correct?

If this is the case, I would like to propose either a SQL SHUTDOWN command or alternatively an /api/v1/shutdown endpoint. I think it would be useful to have such a command, like for example Redis has, so one can safely shutdown a server. However, maybe such a command needs to be limited to the root user?

Thank you for considering this proposal

PS: See also: #641

[gramian]

Adding my dicussion comments here too:

I guess to better limit this command to the root user(s), a shutdown interface should rather be a (POST) endpoint than a (SQL) command.
One argument for a SHUTDOWN endpoint is that database lock files could be cleaned up.

PS: Should a timeout be included if triggered?

PPS: See also also #694

[lvca]

This comment is just to report my recent activity on the server HTTP layer, studio, and new HTTP commands to control the server. The first one will be the shutdown :-)

The prototype I have is a unique POST /server handler that executes pretty much all the commands instead of creating N HTTP endpoints. The syntax will be very easy and it will be in the JSON payload. Examples:

{ "command": "shutdown" }

I'm going to test with automatic integration tests, and also by adding a shutdown button in Studio.

Other commands will be available soon, especially to control the cluster, election, quorum, connect/disconnect, etc.

As always, any idea during and after the design and development is very welcomed!!!

[gramian]

I really like this approach of one server endpoint.

With regard to the shutdown command, what is your stance on adding a "timeout": "X" (seconds) argument to the payload?

Also: could/should this endpoint replace existing endpoints ie for creating/dropping databases and users?

Let me know if you need help updating the docu and console once its done.

[lvca]

It's a very good idea to use a timeout in the payload!

Also, converting existing create/drop databases and users would simplify the API.

[lvca]

Ok, the shutdown is implemented and the studio already supports the shutdown of the current server + shutdown of a replica. No need for a timeout in a shutdown because the command shuts down the current server (and therefore the HTTP request will die if succeed) or is totally asynchronous in case of shutdown of replicas.