2

I need to execute an SQL script on a remote database in Gradle (Version 4.2.1). So I tried it with the Exec task. The task works, when the database is on my local machine. With a remote host the task is not starting.

These are the tasks (the first one works perfectly).

task psqlLocalExec(type: Exec) {
  commandLine 'cmd', '/c', 'psql -f myscript.sql'
}

task psqlRemoteExec(type: Exec) {
  commandLine 'cmd', '/c', 'psql -h my.remote.host -f myscript.sql' // not working
}

The command line output (I cancelled it after some minutes):

C:\myproject>gradlew :proj:psqlRemoteExec --stacktrace
<-------------> 0% EXECUTING [3m 43s]
> :proj:psqlRemoteExec

On cmd (without Gradle) the command works.

deve
  • 487
  • 1
  • 6
  • 24
  • Try `commandLine 'cmd', '/c', 'psql', '-h', 'my.remote.host', '-f', 'myscript.sql'` or `commandLine 'cmd /c psql -h my.remote.host -f myscript.sql'.split(' ')` – lance-java Oct 19 '17 at 08:44
  • I tried that already, unfortunately with the same negative result. – deve Oct 19 '17 at 08:51
  • Can you run the command from command line without gradle? – lance-java Oct 19 '17 at 08:57
  • Yes, on the command line it works like expected. – deve Oct 19 '17 at 10:11
  • So you type `cmd /c psql -h my.remote.host -f myscript.sql` and it works fine? – lance-java Oct 19 '17 at 10:13
  • No, `cmd /c` is only for Gradle. The command itself `psql -h my.remote.host -f myscript.sql` works in the command line. The problem is the remote host. My first task (see in the question) works if the database is on the local machine. Only if I try to connect to the remote database (with `-h my.remote.host`) the command is not starting. – deve Oct 19 '17 at 10:18
  • 1
    Gradle will send the whole command (including `cmd /c`) to the operating system to execute. Make sure you include this in your command line testing – lance-java Oct 19 '17 at 10:21
  • I just tried the whole command in the cmd and its working. So I assume the problem is on Gradle side. – deve Oct 19 '17 at 10:26
  • 1
    I'm guessing you need to pass an option to `psql` to quit after executing the script. I see [here](https://stackoverflow.com/questions/9463318/how-to-exit-from-postgresql-command-line-utility-psql) that you type `\q and ENTER` to quit from command line. I'm guessing there's either a command line option to automatically quit, or something you can add to the end of your SQL script – lance-java Oct 19 '17 at 10:39
  • It is not the `\q` at the end. This is not necessary if I pass a file (`-f myscript.sql`) in the `psql` command. – deve Oct 19 '17 at 11:16
  • I figured it out. My [Password File](https://www.postgresql.org/docs/current/static/libpq-pgpass.html) configuration was wrong and the script did not proceed because of the password prompt. – deve Oct 19 '17 at 11:18
  • Glad you figured it out – lance-java Oct 19 '17 at 12:04

1 Answers1

1

My Password File Configuration was wrong in the script. To fix that an environment variable can be set:

task psqlRemoteExec(type: Exec) {
  environment 'PGPASSFILE', 'path/to/pgpass.conf'
  commandLine 'cmd', '/c', 'psql -h my.remote.host -f myscript.sql'
}
deve
  • 487
  • 1
  • 6
  • 24