Our database ( "voc" ) is just a folder inside of the DBfarm folder. We can backup our database by making a copy of the "voc" folder. The problem is that before doing this we have to wait for the users to finish their queries, and then to prevent them from running new queries.
Disconnecting Users
We can check other users by reading form the sessions table. – Column "idle" is timestamp for session "1". This is the moment when that session finished running its last statement. – Column "idle" is null for session "0". In this session we are running current "SELECT … FROM sessions" statement, so this session is busy.
select sessionid, username, idle, login from sys.sessions;
If we estimate that database is not overloaded, then we can assume that all of the existing queries will end in 30 seconds. We will let existing transaction to run 30 seconds more, and then we will close all the connections.
call sys.shutdown(30); This procedure can be called only by the administrator.
If the "voc" user try to run a query, he will get "unexpected end of file" and his session will end. He will not be allowed to login again.
Shutdown procedure is using SIGTERM command. This procedure will ask database to shutdown, but the database can refuse to do it. In such circumstances we can use the second argument "call sys.shutdown(30, true);". Now we use SIGKILL command, and database will not be able to refuse shutting down.
After calling "sys.shutdown()", admin will still be able to run statements, not only during 30 seconds, but until he decides to leave the session.
If admin quit the session, he will not be able to log into the server again.
At this moment, our database is not locked, but no one can connect to it.
monetdb status -l voc
Connection to this database will be possible only after the restart. But, after stopping the database, we want to prevent users from starting it again. We will lock our database so that only admin can start it again. monetdb lock voc
The last step is to fully stop our database: monetdb stop voc
Restoring Backuped File
We now just have to create a copy of the database folder. This copy can be placed on the same computer or we can move it to some other computer.
We can now release the new database and we can start it. monetdb release vocCopy mclient -u monetdb -d vocCopy
Cleaning Up After File Backup
We will quit and stop database vocCopy. quit monetdb stop vocCopy Now we can delete this vocCopy database: monetdb destroy vocCopy
Original "voc" database is still in the maintenance mode. We will release it with "monetdb release voc".
Hot Backup of a Database
A hot backup is the same as a file backup, but we don't have to stop the database. We'll make a copy of the database files while our database is still running.
Local Copy
As admin, we will call "hot_snapshot" procedure. The result will be our "voc" folder packed inside of the "tar" format. mclient -u monetdb -d voc #password monetdb call sys.hot_snapshot('/home/fffovde/Desktop/backup.tar');
It is also possible to create compressed tar file. We can use ".lz4, .gz, .bz2, .xz" compression. call sys.hot_snapshot('/home/fffovde/Desktop/backup.tar.lz4');
We can grant privileges of using our procedures to any user.
grant execute on procedure sys.hot_snapshot(string) to voc; grant execute on procedure sys.shutdown(tinyint) to voc;
Remote Copy
We will now backup database from the green server. Green server allows external access from the network. I explained how to create green server in this blog post "link".
We will open the green Database, on the green server. monetdbd start /home/sima/monetdb/DBfarmG monetdb start DatabaseG
From the "voc" server, we will login to the green server: mclient -h 192.168.100.145 -p 50000 -u monetdb -d DatabaseG
We will call "hot_snapshot", but we will provide second argument "false". That means that backup shouldn't be created on the server, but on the client. Backup of the green database will appear on the "voc" server. callsys.hot_snapshot('/home/fffovde/Desktop/backupGreen.tar', false);
Restoring Hot Backup
First, we will extract "voc" folder from the "tar" or compressed file.
I will place folder "DatabaseG" into farm folder on the "voc" server, and I will log into it. mclient -u monetdb -d DatabaseG#password monetdb Now, we have green database on the "voc" server.
Cleaning Up After Hot Backup
I will quit green database session, and I will delete this database on the "voc" server. quit monetdb stop DatabaseG monetdb destroy DatabaseG
