Re: Novice! How to run pg_dump from within Java?

Hello,

We have a Java project that is developing a small business app and
using Hibernate & Postgres for persistence. It all works like a dream
until we need to know anything about Postgres!! It is obviously a
"feather in the cap" of Postgres that "so many (Java developers) can
do so much while knowing so little".

I need to perform the world's simplest task: Create a pg dump from within Java.

Of course we do this easily using pgAdminIII, but I need to automate
it for our Users. I notice that pgAdmin uses a command line like this
...
<snip>
C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -h localhost -p
5432 -U pgsuperuser -F c -b -v -f "D:\PG_26jul06.backup" devdb
</snip>

We use "ProcessBuilder" to launch the "pg_dump.exe" program. It takes
an List of Strings in its constructor (including appropriate escape
characters) ...
<snip>
List<String> cmds = new ArrayList<String>();
cmds.add("C:\\Program Files\\PostgreSQL\\8.1\\bin\\pg_dump.exe");
cmds.add("-i");
cmds.add("-h");
cmds.add("localhost");
cmds.add("-p");
cmds.add("5432");
cmds.add("-U");
cmds.add("pgsuperuser");
cmds.add("-F");
cmds.add("c");
cmds.add("-b");
cmds.add("-v");
cmds.add("-f");
cmds.add("\"D:\\PG_26jul06A.backup\"");
cmds.add("devdb");
Process process = new ProcessBuilder(cmds).start();
</snip>

Now this DOES work in that pg_dump.exe is invoked, and a new file is
created at the correct location. However, the file size is zero
(instead of the pgAdminIII version that is 47K)

Any suggestions on what I'm doing wrong,
or any pointers for a better way to do this??

Many thanks,
-Damian

> On 7/25/06, Damian C <jamianb(at)gmail(dot)com> wrote:
> Hello,
>
> We have a Java project that is developing a small business app and
> using Hibernate & Postgres for persistence. It all works like a dream
> until we need to know anything about Postgres!! It is obviously a
> "feather in the cap" of Postgres that "so many (Java developers) can
> do so much while knowing so little".
>
> I need to perform the world's simplest task: Create a pg dump from within Java.
>
> Of course we do this easily using pgAdminIII, but I need to automate
> it for our Users. I notice that pgAdmin uses a command line like this
> ...
> <snip>
> C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -h localhost -p
> 5432 -U pgsuperuser -F c -b -v -f "D:\PG_26jul06.backup" devdb
> </snip>
>
> We use "ProcessBuilder" to launch the "pg_dump.exe" program. It takes
> an List of Strings in its constructor (including appropriate escape
> characters) ...
> <snip>
> List<String> cmds = new ArrayList<String>();
> cmds.add("C:\\Program Files\\PostgreSQL\\8.1\\bin\\pg_dump.exe");
> cmds.add("-i");
> cmds.add("-h");
> cmds.add("localhost");
> cmds.add("-p");
> cmds.add("5432");
> cmds.add("-U");
> cmds.add("pgsuperuser");
> cmds.add("-F");
> cmds.add("c");
> cmds.add("-b");
> cmds.add("-v");
> cmds.add("-f");
> cmds.add("\"D:\\PG_26jul06A.backup\"");
> cmds.add("devdb");
> Process process = new ProcessBuilder(cmds).start();
> </snip>
>
> Now this DOES work in that pg_dump.exe is invoked, and a new file is
> created at the correct location. However, the file size is zero
> (instead of the pgAdminIII version that is 47K)
>
> Any suggestions on what I'm doing wrong,
> or any pointers for a better way to do this??
>
> Many thanks,
> -Damian

Damian,
I'm not sure what the problem is. But when you execute a
command line application from inside Java, you can capture the
resulting print messages which normally go to STDOUT and STDERR.
I have a simple in-house application to do this. The code is
ugly, but it's all I need to get the job done. I've pasted a few
snippets below. You can use that to capture any messages that
pg_dump would otherwise print to the screen, to see what the problem
is.
I'm a relative Java newbie myself, someone with some experience
and a need for production-ready code can certainly make something
cleaner.

-Mike

// the command to run
Process proc = Runtime.getRuntime().exec(myCmd);
// the STDOUT results
BufferedReader bufIn = new BufferedReader(new
InputStreamReader(proc.getInputStream()));
// the STDERR results
BufferedReader bufErr = new BufferedReader(new
InputStreamReader(proc.getErrorStream()));
// the STDIN stream I can use to send additional data.
BufferedWriter bw = new BufferedWriter(new
OutputStreamWriter(proc.getOutputStream()));
...
int in;
try
{
Thread.sleep(30);
StringBuffer sb = new StringBuffer("");
while (bufIn.ready())
{
in = bufIn.read();
if (in > -1 && in < 65535)
{
String c = String.valueOf((char)in);
sb.append(c);
}
Thread.sleep(30);
}
while (bufErr.ready())
{
in = bufErr.read();
if (in > -1 && in < 65535)
{
String c = String.valueOf((char)in);
sb.append(c);
}
Thread.sleep(30);
}
System.out.println(sb.toString());
}
catch (IOException ioe)
{
System.err.println("IOException from reader, message: " +
ioe.getMessage());
return null;
}
catch (InterruptedException ie)
{
System.err.println("InterruptedException from reader, message: "
+ ie.getMessage());
return null;
}
...

On 7/26/06, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> wrote:
> Damian,
> I have a simple in-house application to do this. The code is
> ugly, but it's all I need to get the job done. I've pasted a few
> snippets below. You can use that to capture any messages that
> pg_dump would otherwise print to the screen, to see what the problem
> is.

Michael,
Thanks very much for your response. I actually didn't need it to solve
the issue, but no doubt I will be using your suggested technique
extensively as we move towards production-ready status. Thanks.

Just to record the answer for future googlers!!! Here is how I got it
to work ...
Firstly - refer to my previous post that has the "nearly working" code snip.
The issue was a missing password. The arguments that are passed to the
pg_dump have plenty of information (username, host, port, etc), but no
password. So when invoking the process I set a password into the
PGPASSWORD environment variable as follows...
<snip>
ProcessBuilder pb = new ProcessBuilder(cmds);
Map<String, String> env = pb.environment();
env.put("PGPASSWORD", "my-pg-password-goes-here");
Process process = pb.start();
</snip>

Obviously the above solution is brutal and not-production-ready, but
it did confirm the issue.
Setting PGPASSWORD environment variable is NOT the recommended
technique (see http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html).

Many thanks,
-Damian

> Michael,
> Thanks very much for your response. I actually didn't need it to solve
> the issue, but no doubt I will be using your suggested technique
> extensively as we move towards production-ready status. Thanks.
>
> Just to record the answer for future googlers!!! Here is how I got it
> to work ...
> Firstly - refer to my previous post that has the "nearly working" code snip.
> The issue was a missing password. The arguments that are passed to the
> pg_dump have plenty of information (username, host, port, etc), but no
> password. So when invoking the process I set a password into the
> PGPASSWORD environment variable as follows...
> <snip>
> ProcessBuilder pb = new ProcessBuilder(cmds);
> Map<String, String> env = pb.environment();
> env.put("PGPASSWORD", "my-pg-password-goes-here");
> Process process = pb.start();
> </snip>
>
> Obviously the above solution is brutal and not-production-ready, but
> it did confirm the issue.
> Setting PGPASSWORD environment variable is NOT the recommended
> technique (see http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html).
>
> Many thanks,
> -Damian
>

Damian,
Congratulations on figuring it out. The code snippet I sent may
have caught the error, had you used it. I think you would have
gotten a "password for user pgsuperuser:" in the output stream, just
as if you had run that pg_dump command inside windows cmd.exe or a
Linux shell without setting PGPASSWORD.

-Mike