Postgresql – pg_dump freezes on windows service application

dumppg-dumppostgresqlwindows

I have a system running over Tomcat which executes a backup batch job every day, it calls pg_dump.exe from Java and stores its output to an SQL file.

When I'm executing Tomcat from the shell, it works perfectly, but when I start Tomcat as a Windows service the pg_dump.exe process stays there doing nothing, the SQL file is never written and the dump is never performed.

Is there any additional parameter or configuration required for Windows services? because it works without problems on a Unix-box.

Update: I'm using a Java class to call the operation.

Process p = new ProcessBuilder(
          "C:/postgres9/bin/pg_dump.exe",
          "-U", "myuser",
          "-h" , "localhost",
          "mydb").start();
storeToFile( p.getInputStream() );

Specs:

  • Tomcat 7.0.23
  • Postgresql 9.1
  • Windows 2008

Best Answer

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment:

 ProcessBuilder builder = new ProcessBuilder(
      "C:/postgres9/bin/pg_dump.exe",
      "-U", "myuser",
      "-h" , "localhost",
      "mydb");

 Map<String, String> env = builder.environment();
 env.put("PGPASSWORD", "supersecret");

 Process p = builder.start();
 storeToFile( p.getInputStream() );

Solution source is here: How to pg_dump from Java

Related Question