Powershell script hangs when running a psql script

Question

_{Friday, January 28, 2011 5:28 AM}

Ok, this one really has me stumped. I have a simple psql (PostGres SQL) dos cmd line that works fine under DOS Cmd window, but when I run this using powershell I get no output and powershell hangs until I stop the script. I believe it's probably waiting for some response (which isn't required in CMD Window).

$DBPort = '5421'
$DBName = 'SPY 1 Min'
$DBUser = 'postgres'

$psqlExe = 'C:\Program Files\PostgreSQL\9.0\bin\psql.exe'

& $psqlExe "-p $DBPort" "-U $DBUser" '-s' "-d $DBName" '-f test.sql'

What do I need to do to get this to run (aka; execute the test.sql file) and return control back to the powershell IDE env?

Many thanks in advance for any help here.

All replies (5)

_{Friday, January 28, 2011 9:16 PM ✅Answered}

I don't have a Postgres database to test this on, but the trick with start-process usually solves many issues in running exe's from PowerShell. I can suggest a few things. When a process starts using start-process PowerShell doesn't wait for the process to end, for example: start-process notepad.exe retuns immediately while start-process notepad.exe -wait waits. So try using the -wait parameter.

There's also a -RedirectStandardOutput parmeter that may be worth checking out.

In some GUI apps start-process | out-null also triggers a wait.

If none of the these things doesn't work you can call cmd from PowerShell with a string to execute, although I prefer start-process.

$psqlExe = 'C:\Program Files\PostgreSQL\9.0\bin\psql.exe'

$stringtoexec =  "$psqlExe "-p $DBPort" "-U $DBUser" '-s' "-d $DBName" '-f test.sql'"

cmd /c $stringtoexec

_{Friday, January 28, 2011 10:14 PM ✅Answered}

You should pass arguments to the -ArgumentList as comma-delimited list like this

Start-Process "C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -ArgumentList "-p $DBPort","-U $DBUser","-s","-d $DBName","-f test.sql"

Also you can use -RedirectStandardOutput and -RedirectStandardError parameters to specify files (file names) to redirect appropriate streams. After the cmdlet will end you can read contents of those files.

_{Friday, January 28, 2011 12:34 PM}

You could try using start-process:

start-process 'C:\Program Files\PostgreSQL\9.0\bin\psql.exe' -ArgumentList "-p $DBPort" "-U $DBUser" '-s' "-d $DBName" '-f test.sql'"

_{Friday, January 28, 2011 5:32 PM}

Thanks a ton - you're wonderful.

Had to make a slight modification. First tired the following

   Start-Process $psqlExe -ArguementList "-p $DBPort" "-U $DBUser" "-d $DBName" '-f test.sql'

What I get back is the following

Start-Process : A positional parameter cannot be found that accepts argument '-U postgres'.
At C:\Users\Administrator\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1:28 char:14

• Start-Process <<<<  $psqlExe "-p $DBPort" "-U $DBUser" "-d $DBName" '-f test.sql'
      + CategoryInfo          : InvalidArgument: (:) [Start-Process], ParameterBindingException
      + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.StartProcessCommand

When I changed the arguementlist to a single string

   Start-Process $psqlExe -ArguementList "-p $DBPort -U $DBUser -d $DBName -f test.sq"

It ran, and I got back to the powershell prompt.

No, for my next question. I'm not seeing expected output (and thus not entirely sure it's working correcting - i.e. running the sql script). When I run under DOS Cmd window (see below) I see a response

========

...\bin>psql -p 5433 -U postgres -f test.sql -d "SPY
 1 Min"
      quote_tic

 2010-01-06 09:00:00
(1 row)

...\bin>

=====================

I've tried piping the start-process to Write-Host, but still don't see response to query on the powershell console. Any thoughts or suggestions here?

Bill

_{Wednesday, February 2, 2011 3:43 AM}

Sorry for delayed response - out of town for a bit.

Yes, using -ArgumentList with a correctly configured string worked. 

Again, many thanks.