It is a good idea to back up your databases or DB tables at regular intervals. You should

• back up a complete database (DB schema and data of all DB tables) or
• back up a DB table of a specific database or
• back up only the schema of a database or DB table.

This chapter presents a project that backs up a complete database of the DBMS SQLite, PostgreSQL and MySQL in an SQL file. The chosen format has the advantage that you can easily restore the backup if necessary.

Attention:

You must adapt the project to the DBMS used! You will not be able to avoid commenting out or deleting the source code sections that are not required. The MDBS module, in which you define the access data for the database to be backed up and its name, requires particular care.

Here is the source code excerpt from the MDBS.module for the DBMS PostgreSQL:

If Not $DBConPG1.Opened Then
   $DBConPG1.Type = "postgresql" 
   $DBConPG1.Port = 5432
   $DBConPG1.User = "test"
   $DBConPG1.Password = "test"
   $DBConPG1.Host = "localhost"     '-- DBHost is `localhost ` or  an IP adddress
   $DBConPG1.Name = "test"          '-- Name of the database
   If $DBConPG1.User = Null Or If $DBConPG1.Password = Null Then
      Error.Raise(("No database credentials"))
   Endif
   $DBConPG1.Open()
Endif

The programme interface is simply structured:

Figure 22.12.1: Programme interface

The programme has a programme help function. It also checks whether the programme can access the network. This may be necessary for PostgreSQL and MySQL if you specify an IP address for the host property. It also checks whether the database servers for PostgreSQL and MySQL are installed and started. Syntax errors in the shell statements are also detected and displayed.

Figure 22.12.2: Error message: PostgreSQL server not started

Figure 22.12.3: Error message due to a syntax error

The main load in the programme is carried by the procedure DBDumpShell(argDBType As String, argDBName As String) in the module MDBS.module, whose source code is given in full and then commented:

[1]  ' Gambas class file
[2] 
[3]  Private $hProcess As Process
[4]  Private $aErrorMessages As String[]
[5]  ...
[6] 
[7]  '' Argument 1:argDBType -> DBMS type<br>''Argument 2:argDBName -> Database-Name
[8]  Public Sub DBDumpShell(argDBType As String, argDBName As String)
[9]   
[10] Dim sShellCommand, sMessage As String
[11] Dim sDumpFilePath, sDatabasePath As String
[12]     
[13] $aErrorMessages = New String[]
[14] 
[15] Select Case Lower(argDBType)
[16]   Case "sqlite"
[17]     MAdditional.IsInstalled("sqlite3")
[18]     sDatabasePath = Shell$(MCreateDir.DBHost &/ argDBName)
[19]     sDumpFilePath = Shell$(MCreateDir.DataDir &/ "dump_sqlite." & argDBName & ".sql")
[20]     If Exist(sDumpFilePath) Then Kill sDumpFilePath
[21]     
[22]     sShellCommand = Subst("&1 &2 &3 &4 &5", "sqlite3", sDatabasePath, ".dump", " > ", sDumpFilePath)
[23]     
[24]     $hProcess = Shell sShellCommand For Read As "ShellProcess"
[25]     $hProcess.Wait()
[26]         
[27]     If $hProcess.State = 0 And $hProcess.Value = 0 Then 
[28]        sMessage = Subst(("The backup of the SQLite3 database &1 was successful!"), "<br><b>" & argDBName & "</b><br>")
[29]        Message.Info(sMessage)
[30]     Else 
[31]        If $aErrorMessages.Count > 0 Then Message.Error($aErrorMessages.Join("\n"))
[32]     Endif       
[33] '---------------------------------------------------------------------------------------------------------------------- 
[34]   Case "postgresql"
[35]     MAdditional.IsInstalled("pg_dump")
[36]     sDumpFilePath = Shell$(MCreateDir.DataDir &/ "dump_pg_" & argDBName & ".sql")
[37]     If Exist(sDumpFilePath) Then Kill sDumpFilePath
[38] 
[39]     sShellCommand = Subst("PGPASSWORD=&1 pg_dump --blobs --column-inserts", CDBS.DBConPG1.Password)
[40]     sShellCommand &= Subst(" --dbname=&1 --host=&2", argDBName, CDBS.DBConPG1.Host)
[41]     sShellCommand &= Subst(" --port=&1", CDBS.DBConPG1.Port)
[42]     sShellCommand &= Subst(" --username=&1 --file=&2", CDBS.DBConPG1.User, sDumpFilePath)
[43] 
[44]     $hProcess = Shell sShellCommand For Read As "ShellProcess"
[45]     $hProcess.Wait()
[46]         
[47]     If $hProcess.State = 0 And $hProcess.Value = 0 Then 
[48]        sMessage = Subst(("The backup of the PG database &1 was successful!"), "<br><b>" & argDBName & "</b><br>")
[49]        Message.Info(sMessage)
[50]     Else 
[51]        If $aErrorMessages.Count > 0 Then Message.Error($aErrorMessages.Join("\n"))
[52]     Endif
[53]         
[54] '-- Only deletes the history of the *current* session!
[55]     Shell "unset HISTFILE && exit"
[56] '---------------------------------------------------------------------------------------------------------------------
[57]   Case "mysql"
[58]     MAdditional.IsInstalled("mysqldump")
[59]     sDumpFilePath = Shell$(MCreateDir.DataDir &/ "dump_mysql_" & argDBName & ".sql")
[60]     If Exist(sDumpFilePath) Then Kill sDumpFilePath
[61]         
[62]     sShellCommand = "mysqldump --comments --dump-date --no-tablespaces"
[63]     sShellCommand &= Subst(" --host=&1 --port=&2", CDBS.DBConMySQL1.Host, CDBS.DBConMySQL1.Port)
[64]     sShellCommand &= Subst(" --user=&1 -p&2", CDBS.DBConMySQL1.User, CDBS.DBConMySQL1.Password)
[65]     sShellCommand &= Subst(" &1 &2 &3", argDBName, ">", sDumpFilePath)
[66] 
[67]     $hProcess = Shell sShellCommand For Read As "ShellProcess"
[68]     $hProcess.Wait()
[69]         
[70]     If $hProcess.State = 0 And $hProcess.Value = 0 Then
[71]        sMessage = Subst(("The backup of the MySQL database &1 was successful!"), "<br><b>" & argDBName & "</b><br>")
[72]        Message.Info(sMessage)
[73]     Else 
[74]        If $aErrorMessages.Count > 0 Then Message.Error($aErrorMessages.Join("\n"))
[75]     Endif
[76] 
[77] '-- Only deletes the history of the *current* session!
[78]     Shell "unset HISTFILE && exit"
[79]         
[80] End Select 
[81]    If $hProcess Then $hProcess.Kill()  
[82] End
[83] 
[84] Public Sub ShellProcess_Error(argError As String)
[85] 
[86] '-- Print argError
[87]     $aErrorMessages.Push(argError)
[88]     
[89] End 

Comment - related to the DBMS PostgreSQL:

• Line 35 checks whether the backup programme `pg_dump` is installed for the DBMS PostgreSQL. If the programme is not installed, a message is displayed and the programme is terminated.

Figure 22.12.4: Error message: The pg_dump programme is not installed

• In my opinion, line 37 is superfluous, as the backup file *.sql is completely overwritten with every backup.
• The shell command is defined in lines 39 to 42. The parameters and options used are tried and tested.
• The shell process is started in line 44.
• If the process was completed successfully, a success message is displayed. Otherwise, an error message is displayed.

Figure 22.12.5: The backup of the PostgreSQL database ‘test’ was successful

• The shell instruction in line 55 ensures that the shell history for the current process is not updated. The reason for this is that the password is in the shell command and therefore cannot be read out later.

It would go beyond the scope of this chapter if further variants of exporting and importing DB data were to be described in detail here. This is also due to the fact that the individual commands for exporting and importing differ considerably for the various DBMSs that are available for backing up complete databases, individual DB tables, schemas and for different backup file formats such as dump*.sql or dump*.csv.