how to create backup of postgres database using java

Question

i want to take backup of postgres database using java. I am using following code for this
but this is not working and not generating dump.

String pgDump = "C:\\Program Files\\PostgreSQL\\9.2\\bin\\pg_dump";
          String dumpFile = "D:\\test\\"+ tenant.getTenantAsTemplate()+".sql";
          String sql = pgDump+" -h localhost -U postgres -P postgres " + tenant.getTenantAsTemplate()+" > "+dumpFile;
          Process p = Runtime.getRuntime().exec(sql);
          int time = p.waitFor();
          System.out.println("time is "+time);
          if(time == 0){
            System.out.println("backup is created");
          }
          else{
            System.out.println("fail to create backup");
          }

Here i am getting time is 1.

This is also operating system dependent and we need also pg_dump. is there any other way to generate backup of database without pg_dump?

please reply soon.

Answer 1

No, there is no way to generate a database backup without pg_dump, using the regular SQL connection. It's a bit of an FAQ, but the people who want the feature never step up to do the work to implement the feature in PostgreSQL.

I guess technically you could use a replication connection to do a physical base backup like pg_basebackup does, but that's not really what you want, requires copying all databases on the machine, and would be a lot of work.

You should use the String[] form of Runtime.exec as I mentioned in a related answer regarding pg_restore.

You must also check the process exit value to see if it terminated successfully or not, and you must be careful to handle, not just swallow, any exceptions thrown.

Your code fails to check the exit value, and I think it's probably generating a malformed command that's failing with a non-zero exit code, probably because you are not correctly quoting the path to pg_dump. To see what's wrong, print the final assembled command line, you'll see something like:

C:\Program Files\PostgreSQL\9.2\bin\pg_dump -h localhost ....

which cmd.exe will split into:

c:\Program
Files\postgresql\9.2\bin\pg_dump
-h
localhost

... etc

See the problem?

Do not just quote the path to pg_dump to work around this. Use the String[] form of exec and you won't have to, plus it'll work correctly for other things like accidental %environmentvars% in paths.