Postgres Database Backup/Restore From C#

• Download source code - 515 KB

Introduction

This article provides Postgres database backup/restore functionality. By using this application we can check whether a Postgres database is installed or not in a system. If a Postgres database is installed then the Postgres databases list is provided. From the list, the user is allowed to select the required database for backup/restore operations.

For my project, I had used Postgres as the database. Here I am listing out some of the Advantages of Postgres.

• More profitable business models with wide-scale deployment.
• No possibility of being audited for license compliance at any stage.
• Flexibility to do concept research and trial deployments without needing to include additional licensing costs.

Required DLLs

The required DLLs to access Postgres form Visual Studio are Mono.Security.dll, Npgsql.dll. I had copied these DLLs for the bin directory of the application. It is advised to copy the required DLLs for the bin directory only. If the application is a Release configuration, then we can delete the Debug folder (same applies for Debug configuration).

Then add these references for the application:

Inside the Application

Checking for Postgres Installation

Using this application, first we can check for Postgres database installation. By clicking the “Check for Postgres Installation button”, it checks for the Postgres service for the services list of the system. If a Postgres service is found, then check for “pg_dump.exe”. If the exe is found, then the application assumes that Postgres is installed for the system, and the button background color is changed to green, the database location is provided besides the button.

This the code to check for the Postgres service.

bool bPostgresService = false;
ServiceController[] services = ServiceController.GetServices();
// try to find service name
foreach (ServiceController service in services)
{
    if (service.ServiceName.Contains("postgre") == true)
    {
        bPostgresService = true;
        break;
    }
              }
if (bPostgresService == true)
{
    PG_DumpExePath();
    objProcess.Kill();
    if (sbPG_dumpPath.Length != 0)
    {
        MessageBox.Show("Your System is INSATALLED with Postgres");
        labelLocation.Text = "Installation Location is " + strInstallLocation;
        labelLocation.Visible = true;
                 btnCheckPostgres.BackColor = Color.Green;
        panel1.Enabled = true;
    }
    
}
else
{
    objProcess.Kill();
    MessageBox.Show("Your System is NOT INSATALLED with Postgres");
}

Below is the code to search a file. For this function, provide the file name as input argument, and the function searches for the file in all the drives, and returns the location of the file. The performFileSearchTask function is an iterative file search function.

private string LookForFile(string strFileName)
{
    string strPG_dumpPath = string.Empty;
    try
    {
        DriveInfo[] drives = DriveInfo.GetDrives();
        
        foreach (DriveInfo drive in drives)
        {
            strPG_dumpPath = performFileSearchTask(drive.Name, strFileName);
            if (strPG_dumpPath.Length != 0)
                break;
        }
        
    }
    catch (Exception ex)
      { }
    return strPG_dumpPath;
}

private string performFileSearchTask(string dirName, string strfileName)
{
    try
    {
        if (strPG_dumpPath.Length == 0)
        {
            try
            {

                foreach (string ddir in Directory.GetDirectories(dirName))
                {
                    System.Security.Permissions.FileIOPermission ReadPermission =
                        new System.Security.Permissions.FileIOPermission(
                        System.Security.Permissions.FileIOPermissionAccess.Write, ddir);
                    if (System.Security.SecurityManager.IsGranted(ReadPermission))
                    {
                        try
                        {
                            foreach (string dfile in Directory.GetFiles(ddir, strfileName))
                            {
                                strPG_dumpPath = ddir + "\\";
                                if (strPG_dumpPath.Length > 0)
                                {
                                    strInstallLocation = strPG_dumpPath;
                                    break;
                                }
                            }
                            if (strPG_dumpPath.Length == 0)
                                performFileSearchTask(ddir, strfileName);
                        }
                        catch (Exception ex)
                        { }
                    }
                    if (strPG_dumpPath != string.Empty)
                        break;
                }
            }
            catch (Exception ex)
            { }

        }
        
    }
    catch (Exception ex)
    { }
    return strPG_dumpPath;
}

If the system is installed with the Postgres database, then only the Backup/Restore options are provided by the application. The next operation required is clicking the “Get all Databases” button. Beside this button the port number textbox is provided to enter the desired port number to search for the databases (in my case, it is 5432).

try
{
    comboBox1.Items.Clear();
    comboBox1.Text = string.Empty;
    DataSet dsDB = new DataSet();
    strPort = txtPort.Text;
    strConnection = "Server=localhost;Port=" + strPort + 
      ";Database=postgres;Userid=postgres;Password=postgres;";

    dsDB = GetData("SELECT datname FROM pg_database WHERE " + 
      "datistemplate IS FALSE AND datallowconn IS TRUE AND datname!='postgres';");
    if (dsDB != null)
    {
        if (dsDB.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < dsDB.Tables[0].Rows.Count; i++)
            {
                comboBox1.Items.Add(dsDB.Tables[0].Rows[i][0].ToString());
            }
            comboBox1.SelectedIndex = 0;
            strDatabaseName = comboBox1.Text;
            butSelectLoc.Enabled = true;
        }
        else
        {
            MessageBox.Show("No Database is existing");
            }
    }
}
catch (Exception ex)
{ }

After completion of this function, the combo box is updated with the available Postgres databases. The user is allowed to select the database from the combo box for which the Backup/Restore operation is performed.

Backup Operation

First, select the location where you want to save the backup file by clicking the “Select the Location to Save the Backup file” button. After the selection, the text box below the button is updated with the location of the backup file. The backup file format is DbName_Backup_Date_HoursMin.Backup.

Then the user is allowed to click the “Take Backup of DB” button. In this function, the script required for the backup operation is formed as a batch file, and it is executed as a process.

private void butBackup_Click(object sender, EventArgs e)
{
    try
    {
        if (textBox1.Text == "-------")
        {
            MessageBox.Show("Select the location to save");
            return;
        }
        StreamWriter sw = new StreamWriter("DBBackup.bat");
        // Do not change lines / spaces b/w words.
        StringBuilder strSB = new StringBuilder(strPG_dumpPath);

        if (strSB.Length != 0)
        {
            strSB.Append("pg_dump.exe --host " + strServer + " --port " + strPort + 
              " --username postgres --format custom --blobs --verbose --file ");
            strSB.Append("\"" + textBox1.Text + "\"");
            strSB.Append(" \"" + strDatabaseName + "\r\n\r\n");
            sw.WriteLine(strSB);
            sw.Dispose();
            sw.Close();
            Process processDB = Process.Start("DBBackup.bat");
            do
            {//dont perform anything
            }
            while (!processDB.HasExited);
            {
                MessageBox.Show(strDatabaseName + " Successfully Backed up at " + textBox1.Text);
            }
        }
        else
        {
            MessageBox.Show("Please Provide the Location to take Backup!");
        }
    }
    catch (Exception ex)
    { }
}

This is the function required to form the strPG_dumpPath string. This string is common for the Backup and Restore functions. We provide the path of pg_dump.exe required for the Backup and Restore operations.

private void PG_DumpExePath()
{
    try
    {
        // Do not change lines / spaces b/w words.
        if (sbPG_dumpPath.Length == 0)
        {
            //string strPG_dumpPath = string.Empty;
            if (strPG_dumpPath == string.Empty)
            {
                strPG_dumpPath = LookForFile("pg_dump.exe");
                if (strPG_dumpPath == string.Empty)
                {
                    MessageBox.Show("Postgres is not installed");
                }
            }

            int a = strPG_dumpPath.IndexOf(":\\", 0);
            a = a + 2;
            string strSub = strPG_dumpPath.Substring(0, (a - 2));
            strPG_dumpPath = strPG_dumpPath.Substring(a, (strPG_dumpPath.Length - a));

            StringBuilder sbSB1 = new StringBuilder(strPG_dumpPath);
            sbSB1.Replace("\\", "\r\n\r\ncd ");

            StringBuilder sbSB2 = new StringBuilder("cd /D ");
            sbSB2.Append(strSub);
            sbSB2.Append(":\\");

            sbSB1 = sbSB2.Append(sbSB1);
            sbSB1 = sbSB1.Remove((sbSB1.Length - 3), 3);
            sbPG_dumpPath = sbSB1;
            strPG_dumpPath = sbSB1.ToString();
        }
    }
    catch (Exception ex)
    { }
}

After completion of the backup operation the output backup file is created for the selected location.

Restore Operation

First select the location from where you want to select a backup file by clicking the “Select the Select Backup File Path” button. After the selection, the text box below the button is updated with the location of the backup file name.

Then the user is allowed to click the “Restore DB” button. In this function, the script required for the Restore operation is formed as a batch file, and it is executed as a process.

private void butRestore_Click(object sender, EventArgs e)
{
    try
    {
        if (txtBackupFilePath.Text == string.Empty)
        {
            MessageBox.Show("Select backup file");
            return;
        }
        //check for the pre-requisites before restoring the database.*********
        if (strDatabaseName != "")
        {
            if (txtBackupFilePath.Text != "")
            {
                StreamWriter sw = new StreamWriter("DBRestore.bat");
                // Do not change lines / spaces b/w words.
                StringBuilder strSB = new StringBuilder(strPG_dumpPath);
                if (strSB.Length != 0)
                {
                    checkDBExists(strDatabaseName);
                    strSB.Append("pg_restore.exe --host " + strServer + 
                       " --port " + strPort + " --username postgres --dbname");
                    strSB.Append(" \"" + strDatabaseName + "\"");
                    strSB.Append(" --verbose ");
                    strSB.Append("\"" + txtBackupFilePath.Text + "\"");
                    sw.WriteLine(strSB);
                    sw.Dispose();
                    sw.Close();
                    Process processDB = Process.Start("DBRestore.bat");
                    do
                    {//dont perform anything
                    }
                    while (!processDB.HasExited);
                    {
                        MessageBox.Show("Successfully restored " + 
                           strDatabaseName + " Database from " + txtBackupFilePath.Text);
                    }
                }
                else
                {
                    MessageBox.Show("Please enter the save path to get the backup!");
                }
            }
        }
        else
        {
            MessageBox.Show("Please enter the Database name to Restore!");
        }
    }
    catch (Exception ex)
    { }
}

Conclusion

This article was meant to demonstrate how to take a Postgres database backup file or to restore a database from a backup file.