1

I'm creating a Powershell script to backup 2 PostgreSQL databases using Postgres' pg_dump tool. I'm using Powershell 2.0 on Windows 7 and PostgreSQL 9.3. The simplified script looks like this:

$postgresDir="C:\PostgreSQL\9.3"
$pgUser="postgres"
$pgPort="5432"
$dbName1="db1"
$dbName2="db2"
$currentBackupFile1 = "C:\temp\backup\1.backup"
$currentBackupFile2 = "C:\temp\backup\2.backup"

& ($postgresDir + "\bin\pg_dump.exe") ("-U" + $pgUser) ("--dbname=" + $dbName1) ("--port=" + $pgPort) ("--file=`"" + $currentBackupFile1 + "`"") -v 2>&1 | out-host
& ($postgresDir + "\bin\pg_dump.exe") ("-U" + $pgUser) ("--dbname=" + $dbName2) ("--port=" + $pgPort) ("--file=`"" + $currentBackupFile2 + "`"") -v 2>&1 | out-host

Everything works as expected when the script is run from Windows Powershell IDE. But when the script is started from commandline or via a batch file like this:

powershell -file pg_dump.ps1

, only the first pg_dump gets executed, the second is simply ignored without any errors. Other Powershell cmdlets that follow after these statements are executed normally.

The problem vanishes as soon as I remove the stderr redirection (2>&1) at the end of the statements, making it 

& ($postgresDir + "\bin\pg_dump.exe") ("-U" + $pgUser) ("--dbname=" + $dbName1) ("--port=" + $pgPort) ("--file=`"" + $currentBackupFile1 + "`"") -v | out-host

Also, the problem does not apply to other programs per se. For example, when substituting the pg_dumps with two & dir 2>&1 statements, these statements are both executed when run from a batch script. It may be a pg_dump thing.

Update in reply to Ansgar Wiechers comment.

Using splatting like this:

$exe=($postgresDir + "\bin\pg_dump.exe")
$args1= '-U', $pgUser, '--dbname', $dbName1, '--port', $pgPort, '--file', $currentBackupFile1, '2>&1'
& $exe @args1

leads to pg_dump complaining about having too many command line arguments. Using it like this:

$exe=($postgresDir + "\bin\pg_dump.exe")
$args1 = '-U', $pgUser, '-d', $dbName1, '-p', $pgPort, '-f', $currentBackupFile1
& $exe @args1 2>&1
$args2 = '-U', $pgUser, '-d', $dbName2, '-p', $pgPort, '-f', $currentBackupFile2
& $exe @args2 2>&1

yields the same result as the first example.

user3792852
  • 311
  • 4
  • 14
  • have you tried `Start-Process -RedirectStandardError -RedirectStandardOutput`? – 4c74356b41 Dec 01 '16 at 11:36
  • @4c74356b41 No, I haven't. Can `-RedirectStandardError` actually redirect to Stdout? AFAIK this only redirects to a file. For logging reasons, I need to redirect Stderr to Stdout. – user3792852 Dec 01 '16 at 12:20
  • no, it can only do that to a file (to different files), but you can merge then after quite easily. – 4c74356b41 Dec 01 '16 at 12:27
  • It may not be liking the ampersand in 2>&1, you might need to find a way to escape it, or just use a different method to achieve that result. – campbell.rw Dec 01 '16 at 12:43
  • @campbell.rw I wouldn't know how to escape the ampersand, but in fact it seems to work if you change `2>&1` to `2>logfile.log`. Of course this writes a new logfile for every dump. I'm using `Start-Transcript` to log everything, which keeps the main logfile locked all the time, so I cannot attach the new dump logfiles. Not beautiful to end up with so many logfiles, but I go that way for now. Other solutions still welcome... – user3792852 Dec 01 '16 at 16:11
  • You could avoid using the transcript and just use Out-file -append for every line you want to log. Just an idea. Also, as a side not, I highly recommend upgrading to at least PowerShell 3.0, The jump from version 2.0 to 3.0 was a pretty big jump, and I've found that scripts that are written in 3.0+ are much more compatible with newer versions, whereas the scripts written using 2.0-specific items don't age that well. – campbell.rw Dec 01 '16 at 17:16
  • Have you tried [splatting](http://stackoverflow.com/a/40105646/1630171) your arguments? Also, why the `Out-Host`? – Ansgar Wiechers Dec 01 '16 at 23:55
  • @Ansgar Wiechers Splatting doesn't seem to work (see my edited answer). When I include `2>&1` as a splatted argument, pg_dump complains to have too many command line arguments. When I append it like this: `& $pgExe @args 2>&1`, I'm back to the begining problem. About the `Out-Host`: this must have come in with some copy&paste code, leaving it out has no impact indeed. Sorry, I'm not exactly a long term Powershell user... – user3792852 Dec 02 '16 at 09:07
  • `2>&1` is a redirection operator, so obviously you can't pass it as an argument to the program. As for the arguments, try either `$args1 = '-U', $pgUser, '-d', $dbName1, '-p', $pgPort, '-f', $currentBackupFile1` or `$args1 = '-U', $pgUser, "--dbname=$dbName1", "--port=$pgPort", "--file=$currentBackupFile1"`. – Ansgar Wiechers Dec 02 '16 at 09:31
  • @ Ansgar Wiechers See my updated answer. The problem now is that no output is visible on the console. – user3792852 Dec 02 '16 at 09:52
  • What output would you expect? – Ansgar Wiechers Dec 03 '16 at 13:44
  • @Ansgar Wiechers I log all commandline output of the script on stdout using `Start-Transcript`. pg_dump writes a lot of debug output (~40.000 lines) to stderr. To see it in the transcript, I redirect it to stdout using `2>&1`. I could also make Start-Transcript log stderr, if that's possible (though it wouldn't solve the original question). Sorry if this discussion got a little confusing, I expected my problem to be simpler. – user3792852 Dec 05 '16 at 08:17
  • I don't have a PostgreSQL on Windows at hand, but on my Linux test box `pg_dump` with the parameters you provided did not produce any output (debug or otherwise) aside from the dump file(s). – Ansgar Wiechers Dec 05 '16 at 10:31
  • @Ansgar Wiechers Again I'm sorry, you're right. I forgot to append the `-v` parameter to pg_dump, enabling debug output. Now the results are the same as with my original example: The logging (console and file) works flawlessly, but only the first dump is created. – user3792852 Dec 05 '16 at 10:49
  • 1
    That doesn't make sense unless something causes the script to terminate while/after creating the first dump. What are the last lines of the verbose output? And do you need verbose output in the first place? Because apparently the dumps are created correctly when you run the commands without the parameter `-v`. – Ansgar Wiechers Dec 05 '16 at 13:21
  • @Ansgar Wiechers I can't upload the complete script, but the output shows nothing but the usual debug output of pg_dump. No further errors are reported, no sudden breaks in the output. And, of course I can leave `-v` out, it's not a hard requirement. I'll continue to work without verbose output now. I was just curious to learn the nature of this problem, for it problably would have given some interesting insights of powershell's external command handling. Thanks for your efforts until now. – user3792852 Dec 06 '16 at 10:29

0 Answers0