How to Backup/Restore PostgreSQL using code?

Question

i have tried this method but it doesn't working anyone can correct it or share some tutorial for Backup/Restore PostgreSQL using VB.NET

and these methods are using to backup/restore here commandType = pg_dump and commandSentence = -i -h localhost -p 5432 -U postgres -F c -b -v -f C:\Documents and Settings\GDS\Desktop\backup\RStar.backup RStar but returns nothing in the folder where i am trying to place the backup file

 private void executeCommand(string commandType,string commandSentence )
    {
        try
        {
            System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();
            info.FileName = "C:\\Program Files\\PostgreSQL\\9.2\\bin\\" + commandType + ".exe ";
            info.Arguments = commandSentence;
            info.CreateNoWindow = true  ;
            info.UseShellExecute = false;
            System.Diagnostics.Process proc = new System.Diagnostics.Process();
            proc.StartInfo = info;
            proc.Start();
            proc.WaitForExit();

            if (commandType == "pg_dump")
                toolStripStatusLabel1.Text = "Backup successfuly created";
            else if (commandType == "pg_restore")
                toolStripStatusLabel1.Text = "Restore successfuly executed";
            else if(commandType=="shp2pgsql")
                toolStripStatusLabel1.Text = "Your selected shape file successfuly transfered to PostGIS";
            else if (commandType == "pgsql2shp")
                toolStripStatusLabel1.Text = "Your selected layer from PostGIS successfuly converted to shape file";

        }
        catch (Exception ex)
        {
            toolStripStatusLabel1.Text = ex.ToString();
        }
    }

Answer 1

Just to enhance byte response and Working with Net Core 3.1 Linux and Windows System

You could use PGPASSWORD instead PGPASSFILE, so you can omit create a intermediate file for credentials.

For linux you need to consider how to run sh script in linux with Process: Shell Script File(.sh) does not run from c# core on linux

To set a variable in linux you should use export instead set.

Here my example for Restore database in linux and windows OS system (Net Core 3.1):

string Set = RuntimeInformation.IsOSPlatform(OSPlatform.Windows) ? "set " : "export ";

public async Task PostgreSqlRestore(
           string inputFile,
           string host,
           string port,
           string database,
           string user,
           string password)
        {
            string dumpCommand = $"{Set}PGPASSWORD={password}\n" +
                                 $"psql -h {host} -p {port} -U {user} -d {database} -c \"select pg_terminate_backend(pid) from pg_stat_activity where datname = '{database}'\"\n" +
                                 $"dropdb -h " + host + " -p " + port + " -U " + user + $" {database}\n" +
                                 $"createdb -h " + host + " -p " + port + " -U " + user + $" {database}\n" +
                                 "pg_restore -h " + host + " -p " + port + " -d " + database + " -U " + user + "";

//psql command disconnect database
//dropdb and createdb  remove database and create.
//pg_restore restore database with file create with pg_dump command
            dumpCommand = $"{dumpCommand} {inputFile}";

            await Execute(dumpCommand);
        }

Execute Method

private Task Execute(string dumpCommand)
        {
            return Task.Run(() =>
            {

                string batFilePath = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}." + (RuntimeInformation.IsOSPlatform(OSPlatform.Windows) ? "bat" : "sh"));
                try
                {
                    string batchContent = "";
                    batchContent += $"{dumpCommand}";

                    File.WriteAllText(batFilePath, batchContent, Encoding.ASCII);

                    ProcessStartInfo info = ProcessInfoByOS(batFilePath);

                    using System.Diagnostics.Process proc = System.Diagnostics.Process.Start(info);


                    proc.WaitForExit();
                    var exit = proc.ExitCode;


                    ... ommit error handler code ...



                    proc.Close();
                }
                catch (Exception e)
                {
                    // Your exception handler here.

                }
                finally
                {
                    if (File.Exists(batFilePath)) File.Delete(batFilePath);
                }
            });
        }

ProcessInfoByOS Method

private static ProcessStartInfo ProcessInfoByOS(string batFilePath)
        {
            ProcessStartInfo info;
            if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
            {
                info = new ProcessStartInfo(batFilePath)
                {
                };
            }
            else
            {
                info = new ProcessStartInfo("sh")
                {
                    Arguments = $"{batFilePath}"
                };
            }

            info.CreateNoWindow = true;
            info.UseShellExecute = false;
            info.WorkingDirectory = AppDomain.CurrentDomain.BaseDirectory;
            info.RedirectStandardError = true;

            return info;
        }

And here the Dump Method

  public async Task PostgreSqlDump(
            string outFile,
            string host,
            string port,
            string database,
            string user,
            string password)
        {
            string dumpCommand =
                 $"{Set}PGPASSWORD={password}\n" +
                 $"pg_dump" + " -Fc" + " -h " + host + " -p " + port + " -d " + database + " -U " + user + "";

            string batchContent = "" + dumpCommand + "  > " + "\"" + outFile + "\"" + "\n";
            if (File.Exists(outFile)) File.Delete(outFile);

            await Execute(batchContent);
        }

Answer 2

   public void { BackupDatabase(server,port, user,password, dbname, "backupdir", dbname, "C:\\Program Files\\PostgreSQL\\11\\bin\\");
}

public static string BackupDatabase(
            string server,
            string port,
            string user,
            string password,
            string dbname,
            string backupdir,
            string backupFileName,
            string backupCommandDir)
{
    try
    {

        Environment.SetEnvironmentVariable("PGPASSWORD", password);

        string backupFile = backupdir + backupFileName + "_"+DateTime.Now.ToString("yyyy") + "_" + DateTime.Now.ToString("MM") + "_" + DateTime.Now.ToString("dd") + ".backup";

        string BackupString = " -f \"" + backupFile + "\" -F c"+
          " -h "  + server + " -U " + user + " -p " + port + " -d " + dbname;


        Process proc = new System.Diagnostics.Process();
        proc.StartInfo.FileName = backupCommandDir + "\\pg_dump.exe";

        proc.StartInfo.Arguments = BackupString;

        proc.StartInfo.RedirectStandardOutput = true;//for error checks BackupString
        proc.StartInfo.RedirectStandardError = true;


        proc.StartInfo.UseShellExecute = false;//use for not opening cmd screen
        proc.StartInfo.CreateNoWindow = true;//use for not opening cmd screen


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

        return backupFile;
    }
    catch (Exception ex)
    {
        return null;
    }

https://sagartajpara.blogspot.com/2017/03/postgres-database-backup-in-c.html

Answer 3

Method for dump (where pgDumpPath is path to pg_dump.exe and outFile is output file path):

    public void PostgreSqlDump(
        string pgDumpPath,
        string outFile,
        string host,
        string port,
        string database,
        string user,
        string password)
    {
        String dumpCommand = "\"" + pgDumpPath + "\"" + " -Fc" + " -h " + host + " -p " + port + " -d " + database + " -U " + user + "";
        String passFileContent = "" + host + ":" + port + ":" + database + ":" + user + ":" + password + "";

        String batFilePath = Path.Combine(
            Path.GetTempPath(),
            Guid.NewGuid().ToString() + ".bat");

        String passFilePath = Path.Combine(
            Path.GetTempPath(),
            Guid.NewGuid().ToString() + ".conf");

        try
        {
            String batchContent = "";
            batchContent += "@" + "set PGPASSFILE=" + passFilePath + "\n";
            batchContent += "@" + dumpCommand + "  > " + "\"" + outFile + "\"" + "\n";

            File.WriteAllText(
                batFilePath,
                batchContent,
                Encoding.ASCII);

            File.WriteAllText(
                passFilePath,
                passFileContent,
                Encoding.ASCII);

            if (File.Exists(outFile))
                File.Delete(outFile);

            ProcessStartInfo oInfo = new ProcessStartInfo(batFilePath);
            oInfo.UseShellExecute = false;
            oInfo.CreateNoWindow = true;

            using (Process proc = System.Diagnostics.Process.Start(oInfo))
            {
                proc.WaitForExit();
                proc.Close();
            }
        }
        finally
        {
            if (File.Exists(batFilePath))
                File.Delete(batFilePath);

            if (File.Exists(passFilePath))
                File.Delete(passFilePath);
        }
    }

Answer 4

public void Backup()
{
    try
    {
        DateTime Time = DateTime.Now;
        int year = Time.Year;
        int month = Time.Month;
        int day = Time.Day;
        int hour = Time.Hour;
        int minute = Time.Minute;
        int second = Time.Second;
        int millisecond = Time.Millisecond;
        //Save file to C:\ with the current date as a filename
        string path;
        path = "D:\\" + year + "-" + month + "-" + day + "-" + hour + "-" + minute + ".sql";
        StreamWriter file = new StreamWriter(path);              
        ProcessStartInfo psi = new ProcessStartInfo();
        psi.FileName = "mysqldump";
        psi.RedirectStandardInput = false;
        psi.RedirectStandardOutput = true;
        psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
        psi.UseShellExecute = false;
        Process process = Process.Start(path);
        string output;
        output = process.StandardOutput.ReadToEnd();
        file.WriteLine(output);
        process.WaitForExit();
        file.Close();
        process.Close();
    }
    catch (IOException ex)
    {
        MessageBox.Show("Error , unable to backup!");
    }
}