Problems creating Postgre backup on Asp.Net MVC

Question

I didn’t find much information about creating Postgre backups with C#, so I created this post. The process is simple... I am creating a process that executes a command and passes some parameters only that everything is running without errors, but the backup is not being created... In my Appsettings, I’m saving the parameters and switching to the function using the Backuprestore class... I’ve tried everything and it doesn’t work. Does anyone have any solution or even any different way if backing up?

"DataConnection": {
    "Servidor": "localhost",
    "Porta": "5432",
    "Usuario": "postgres",
    "Senha": "334522",
    "NomeBancoDados": "Retaguarda",
    "DiretorioComando": "C:\\Program Files\\PostgreSQL\\11\\bin",
    "DiretorioBackup": "C:\\Users\\JALBER\\Downloads\\",
    "NomeArquivo": "Backup_Retaguarda_"
  }

public void CreateBackup(BackupRestore backupRestore)
{
    Environment.SetEnvironmentVariable("PGPASSWORD", backupRestore.Senha);

    string backupFile = backupRestore.DiretorioBackup + backupRestore.NomeArquivo + DateTime.Now.ToString("yyyy") + "_" + DateTime.Now.ToString("MM") + "_" + DateTime.Now.ToString("dd") + ".backup";
    string BackupString = "-ibv -Z3 -f \"" + backupFile + "\" " +
     "-Fc -h " + backupRestore.Servidor + " -U " + backupRestore.Usuario + " -p " + backupRestore.Porta + " " + backupRestore.NomeBancoDados;

    Process proc = new System.Diagnostics.Process();
    proc.StartInfo.FileName = backupRestore.DiretorioComando + "\\pg_dump.exe";
    proc.StartInfo.Arguments = BackupString;

    proc.Start();
    proc.WaitForExit();
    proc.Close();
}

Answer 1

I checked on a website something that might help...

First check the postgre

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");
}

For this function, provide the file name as input argument and the function will search the file on all drives and return the file location. performFileSearchTaskfunction 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, only the Backup / Restore options will be provided by the application. The next operation required is to click on the "Get All Databases" button. Next to this button, the text box of the port number is provided to enter the desired port number to search 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)
{ }

Upon completion of this function, the combo box is updated with the available Postgres databases. User is allowed to select the database in the combo box for which Backup/ Restore operation is performed.

To backup you must select the location where you want to save the backup file by clicking on "Select Location to save the backup file". After 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 can click on the "Backup Database" button. In this function, the script needed for the backup operation is formed as a batch file and runs 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 necessary function to form strPG_dumpPathstring. This string is common for backup and restore functions. We provide the pg_dump.exe path required for 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 backup operation, the output backup file is created to the selected location.

This is the main information on how to back up if you want the full article link ...

https://www.codeproject.com/Articles/360472/Postgres-Database-Backup-Restore-From-Csharp