How to backup a postgres database using java

Question

this is the code I am using to back up my database using pg_dump 9.3 in java. The problem I have is that always the result file is empty, and the exit code is 1, any ideas?

public static void backupDb() throws IOException, InterruptedException {
    Runtime rt = Runtime.getRuntime();
    Process p;
    ProcessBuilder pb;
    rt = Runtime.getRuntime();
    pb = new ProcessBuilder(
            "C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dumpall.exe",
            "--host", "localhost",
            "--port", "5432",
            "--username", "postgres",
            "--no-password",
            "--format", "custom",
            "--blobs",
            "--verbose", "--file", "D:\\service_station_backup.backup", "service_station");
    p = pb.start();
    p.waitFor();
    System.out.println(p.exitValue());
}

Does the process / app have write access to the file location @Abdelwahhab ? Eg what is the user id of the process and does the user id have access to the output file? — user1123335
– user1123335, Commented May 3, 2016 at 3:53
@pnorton yes, the process can access the output file, the file is correctly created, but not content is dumped into it. i think the problem is with postgres server password. — Wolverine219
– Wolverine219, Commented May 3, 2016 at 8:46
Hi @no_pain_no_gain what does the postgresql log say (show log_destination ; log_destination ----------------- stderr (1 row) so for me its syslogs )? (journalctl |grep [Pp]ostgres |more ) You might need to turn up the logging. Could you show the contents of pg_hba.conf I might be able to help REF postgresql.org/docs/9.1/static/auth-pg-hba-conf.html — user1123335
– user1123335, Commented May 3, 2016 at 15:35

Wolverine219 · Accepted Answer · 2016-05-03 09:25:29Z

thanks everyone for help, finally could find the perfect code.

public static void exportDb2() throws IOException, InterruptedException {
    Runtime rt = Runtime.getRuntime();
    Process p;
    ProcessBuilder pb;
    rt = Runtime.getRuntime();
    pb = new ProcessBuilder(
            "C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dump.exe",
            "--host", "localhost",
            "--port", "5432",
            "--username", "postgres",
            "--no-password",
            "--format", "custom",
            "--blobs",
            "--verbose", "--file", "D:\\service_station_backup.backup", "service_station");
    try {
        final Map<String, String> env = pb.environment();
        env.put("PGPASSWORD", "admin");
        p = pb.start();
        final BufferedReader r = new BufferedReader(
                new InputStreamReader(p.getErrorStream()));
        String line = r.readLine();
        while (line != null) {
            System.err.println(line);
            line = r.readLine();
        }
        r.close();
        p.waitFor();
        System.out.println(p.exitValue());

    } catch (IOException | InterruptedException e) {
        System.out.println(e.getMessage());
    }
}

score 1 · Accepted Answer · 2016-05-03 08:46:14Z

1

For psql, the "long options" need an equal sign: =. So it needs to be e.g. --host=localhost. For that you need to pass those arguments as a single String argument to ProcessBuilder:

pb = new ProcessBuilder(
        "C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dumpall.exe",
        "--host=localhost",
        "--port=5432",
        "--username=postgres",
        "--no-password",
        "--format=custom",
        "--blobs",
        "--verbose", "--file=D:\\service_station_backup.backup", "service_station");

You should also capture the error output of the ProcessBuilder using ProcessBuilder.getErrorStream() to see any error message from psql. You probably want to capture the regular output as well (using getInputStream())

Edit

The error message you get:

fe_sendauth: no password supplied

means you have to provide a password.

You can do that by passing a connection URL to pg_dump.

pb = new ProcessBuilder(
        "C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dumpall.exe",
        "--dbname=postgres://postgres:password@localhost/postgres",
        "--format=custom",
        "--blobs",
        "--verbose", "--file=D:\\service_station_backup.backup", "service_station");

edited May 3, 2016 at 8:46

answered May 3, 2016 at 6:15

user330315

2 Comments

Wolverine219 Over a year ago

hi, thanks for following,i tried what you advised but still get the same result, and the same message from the process ErrorStream : " fe_sendauth: no password supplied".

user330315 Over a year ago

Then you need to provide a password. Either by setting the PGPASSWORD environment variable, by configuring it in pgpass.conf or by providing a connection URL. See the manual for details: postgresql.org/docs/9.3/static/libpq-connect.html#AEN39257

Collectives™ on Stack Overflow

How to backup a postgres database using java

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related