Click here to Skip to main content
15,860,859 members
Articles / Database Development / PostgreSQL
Article

Postgres Database Backup/Restore From C#

Rate me:
Please Sign up or sign in to vote.
4.75/5 (7 votes)
4 Apr 2012CPOL3 min read 67.8K   4.8K   14   7
Postgres database backup/restore functionality implementation.

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).

Image 1

Then add these references for the application:

Image 2

Image 3

Image 4

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.

Image 5

Image 6

This the code to check for the Postgres service.

C#
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.

C#
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).

C#
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.

Image 7

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.

Image 8

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.

C#
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.

C#
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)
    { }
}

Image 9

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

Image 10

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.

Image 11

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.

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralAgain it is asking password in commmand prompt Pin
Member 997488118-Aug-15 1:16
Member 997488118-Aug-15 1:16 
GeneralRe: Again it is asking password in commmand prompt Pin
JJChen4012-Oct-16 8:33
JJChen4012-Oct-16 8:33 
We have faced the same problem and we found out the solution for postqre 9.3.
ref link for postqre password file: https://www.postgresql.org/docs/9.0/static/libpq-pgpass.html

There are two solutions:
1) if you can store password in pgadmin III, using pg_dump.exe to backup database, it should not ask you for password.
2) if you can not store password in pgadmin III, try provide password file.

we create a file call "pgpass.conf" and content as following and copy this file to
postqre bin folder where PG_dump.exe located.
localhost:5432:*:postgres:xxxxxx (xxxxxx is password)

run the script file as following:
-----------------
cd /D C:\Program Files (x86)
rem cd /D C:\Program Files

cd PostgreSQL
cd 9.3
cd bin

SET PGPASSFILE=pgpass.conf

echo on
pg_dump.exe -w --host localhost --port 5432 --username postgres --format custom --blobs --verbose --file "C:\Temp\database_Version_1_16_Backup_06102016_1806.backup" "DatabaseName"
PAUSE
-------------------------------------
the above batch file allows us to back database without having to provide password.

-- modified 12-Oct-16 14:45pm.
Questionplease Pin
DansZavach19-Dec-13 20:14
DansZavach19-Dec-13 20:14 
QuestionOLAS Pin
DansZavach19-Dec-13 20:08
DansZavach19-Dec-13 20:08 
QuestionPostgres 9.1 Pin
ronaldovn9-May-12 23:10
ronaldovn9-May-12 23:10 
QuestionThanks Pin
Jasim Shah4-Apr-12 21:45
Jasim Shah4-Apr-12 21:45 
QuestionWell explained article Pin
bilal hashmi4-Apr-12 18:51
bilal hashmi4-Apr-12 18:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.