Preventing client connections for maintenance

[Matt Pavlovich]

We are working through maintenance scenarios, and have hit a roadblock on blocking client connections and re-starting a shutdown database. This is problematic when clients use pools and auto-reconnect.

Scenarios:

1. Blocking new clients from connecting during backup / restore or export / import

2. Re-starting a database after performing a shutdown to compact

Questions:

1. Is there a command to start the database after a shutdown? Or a mechanism where the database.open() could be called again through a user-defined function?

2. Is there a combination of client-side connection flag(s) that would prevent the engine from starting the database if its not already running?  Similar to IFEXISTS=TRUE, but more like IFRUNNING=TRUE

Thanks!

[Noel Grandin]

Startup/shutdown we largely assume is the responsibility of some higher level component.

For backup/restore, what we have is SET EXCLUSIVE
http://h2database.com/html/commands.html#set_exclusive

We have basic infrastructure such code could be built-on, the admin database stuff, which was, I believe, initially
mostly designed around the needs of the cluster functionality, but you'd have to write some code yourself to make that work.

[Matt Pavlovich]

@Noel Grandin-

Yeah, that higher-level component is what we are writing. Maybe we don't need to call shutdown to flush all the buffers?

Q: Will this flow maintain data and transactional consistency?

1. Management-Connection: Create database (and start)

2. Client-Connections: do SQL stuff

3. Management-Connection: Call EXCLUSIVE mode enable

4. Clients-Connections: disconnected.. entry retry state

5. Management-Connection: Call backup (or export)

6. Management-Connection: Call delete database

7. Management-Connection: Call restore database (or import)

8. Management-Connection: Call EXCLUSIVE mode disable

9. Client-Connections: re-connect.. resume doing SQL stuff

Thanks,

-Matt

[Noel Grandin]

You can't go EXCLUSIVE and then delete a database.
But perhaps you mean overwrite, in which case this flow should be fine.

[Matt Pavlovich]

Got it, we'll give that a whirl without the delete for backup/restore.

The thought on doing the delete database ahead of restore was to be sure to clear out any swapped temp files from large queries, etc. Looks like the EXCLUSIVE mode should clean all that up for us.

Question:

For the export/import use case, where the goal is to re-load the data in order to optimize the store (ie.. defrag) after a large number of deletes-- how should that work without doing a delete database? Would we have to drop all the db objects (tables, indexes, sequences, etc) before the import?

Thanks,

Matt