Create Postgres password protected backup from C#

Question

I want to create a password protected Postgres backup from C#

I tried next code but I think there is a more secure way: to pass backup directly from pg_dump to arhive without saving it on the disk and to add password form env variable like is done with PGPASSWORD

   string pgDumpPath, outFile, host, port, database, user, password, dumpCommand, batFilePath, zipPth, outPath, tmpPath;

    pgDumpPath = @"d:\_dev_\postgres\pgsql\bin\pg_dump.exe";
    host = "127.0.0.1";
    port = "5432";
    database = "postgres";
    user = "postgres";
    password = "mypass";
    dumpCommand = pgDumpPath + " -Fc" + " -h " + host + " -p " + port + " -d " + database + " -U " + user;
    tmpPath = Path.GetTempPath();
    batFilePath = tmpPath + Guid.NewGuid().ToString() + ".cmd";

    outFile = @"myBackup";

    outPath = @"d:\";

    zipPth = @"c:\Program Files\7-Zip\7z.exe";
    string tmpfile = "\"" + tmpPath + outFile + ".sql\"";
    Environment.SetEnvironmentVariable("PGPASSWORD", password);

    try
    {


        File.WriteAllText(batFilePath,
         "@echo off " + "\n" +
          dumpCommand + "  > " + tmpfile + "\n" + "\"" +
          zipPth + "\"" + " a \"" + outPath + outFile + ".zip\" -p" + password + " " +tmpfile +
           "  &DEL " + tmpfile  +
           "  &DEL \"" + batFilePath + "\"",
         Encoding.ASCII);


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

        var oInfo = new ProcessStartInfo(batFilePath)
        {
            UseShellExecute = false,
            CreateNoWindow = true
        };

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

    }

Does this answer your question? [Creating a ZIP archive in memory using System.IO.Compression](https://stackoverflow.com/questions/17232414/creating-a-zip-archive-in-memory-using-system-io-compression) — Neil, Jul 10 '23 at 08:41
no,the question is not how to create a zip in .net si how to directly pass backup file from pg_dump to 7z/secure achive — mrapi, Jul 10 '23 at 08:58
You'll want to open the pgdump process using `new Process()`, with output redirection enabled, read the dump content into a buffer, then write the buffer to your compressed form. See https://stackoverflow.com/a/29753402/51685 — AKX, Jul 10 '23 at 09:10
The question very much is how to create a 7z archive. Dumps aren't backups, they're database scripts. What you ask is how to create a password-protected ZIP or 7z package containing that dump. You want the contents of that dump to come from `pd_dump`'s standard output. — Panagiotis Kanavos, Jul 10 '23 at 09:10
As [this related if not duplicate question shows](https://stackoverflow.com/questions/1287383/not-able-to-use-7-zip-to-compress-stdin-and-output-with-stdout) the 7z format itself can't work with `stdin` input because it requires seek access to the input. Different formats can be used with the same tool though. — Panagiotis Kanavos, Jul 10 '23 at 09:11
@PanagiotisKanavos `pg_dump` can do either script or a proper backup archive. See the docs https://www.postgresql.org/docs/current/app-pgdump.html — Charlieface, Jul 10 '23 at 13:46
Using the `-Fc` option with `pg_dump` will create an archive, which is compressed by default. Compressing it again using 7z is a waste of time as there will be almost no space savings. — Charlieface, Jul 10 '23 at 13:48
@Charlieface the docs say `or other archive file`. The equivalent to a SQL Server backup is called a physical backup, done using `pg_basebackup`. The OP's question is how to password-protect the backup, not so much how to compress it. — Panagiotis Kanavos, Jul 10 '23 at 14:05

score 0 · Accepted Answer · answered Jul 12 '23 at 04:20

got a working solution:

string database = "postgres",user = "postgres", password = "mypass", archivePassword = "mypass2";

            var outFile = @"d:\postgres_" + DateTime.Now.ToString("yyyy") + "_" + DateTime.Now.ToString("MM") + "_" +
                                     DateTime.Now.ToString("dd") + "_" + DateTime.Now.ToString("hh") + DateTime.Now.ToString("mm") +
                                     DateTime.Now.ToString("ss");

            var sInfo = new ProcessStartInfo()
            {
                FileName = @"d:\_dev_\postgres\pgsql\bin\pg_dump.exe",
                Arguments = $"-U {user} -w {database}",
                UseShellExecute = false,
                RedirectStandardOutput = true,
                CreateNoWindow = true
            };

            sInfo.EnvironmentVariables.Add("PGPASSWORD", password);


            var cInfo = new ProcessStartInfo()
            {

                FileName = @"c:\Program Files\7-Zip\7z.exe",
                Arguments = $"a -p{archivePassword} -si{Path.GetFileNameWithoutExtension(outFile)} {outFile}",
                UseShellExecute = false,
                RedirectStandardInput = true,
                CreateNoWindow = true
            };



            using (var proc = Process.Start(sInfo))
            {
                using (var comp = Process.Start(cInfo))
                {
                    proc.StandardOutput.BaseStream.CopyTo(comp.StandardInput.BaseStream);

                    proc.WaitForExit();
                    comp.StandardInput.BaseStream.Flush();
                    comp.StandardInput.BaseStream.Dispose();
                    comp.WaitForExit();

                }
            }

Create Postgres password protected backup from C#

1 Answers1