Reloading .sql file from java into PostgreSQL - Windows

Question

I am trying to reload a .sql script file into PostgreSQL from my sample Java application.

Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs...

This is the code I am using:

C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f "H:/Data/samplescript_postgres.sql" 

Can anyone help me? I am using PostgreSQL 9.2

Java code:

String path = "H:/Data/samplescript_postgres.sql";
final String cmd = "C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f " + "\""+ path + "\" ";
try {
 reloadProcess = Runtime.getRuntime().exec(cmd);
 if(null!=reloadProcess){
 if(reloadProcess.waitFor()==0){
 System.out.println("Reloaded");
 }
 }
} catch (IOException e) {
 e.printStackTrace();
} catch (InterruptedException e) {
 e.printStackTrace();
}

My pg_hba.conf file:

# TYPE DATABASE USER ADDRESS METHOD 
# IPv4 local connections: 
host all all 127.0.0.1/32 md5 
# IPv6 local connections: 
host all all ::1/128 md5

• Craig Ringer: Please show the Java code you are using to invoke the command.
• sufleR: waits for password?
• András Váczi: When it 'hangs', does it produce any (error) meassages? Does it start and hang at a later point in execution or looks like doing nothing at all?
• Haseena: The sql script contains 7 tables, first 2 are only loaded from java.
• Haseena: How I give password with the above command?

Answer 1

This is probably not the best way to do what you want to do. The "proper" way would probably be to execute the SQL commands in your file via jdbc.

That said, that's a long way from where you are now, and we can probably make what you are trying to do work.

The likely problem is that there is a space in the path to psql.exe.

When you use that particular format of exec, java uses a vanilla string tokenizer to split your string into an array, each entry being separated by a space. So it's going to be trying to execute a command "c:/Program" and pass it parameters, the first of which is "Files/PostgreSQL/9.2/bin/psql.exe" and, um, that isn't going to go so well.

Use exec (String[]) instead: http://docs.oracle.com/javase/6/docs/api/java/lang/Runtime.html#exec(java.lang.String[])

This method doesn't apply the tokenizer, because it's accepting an array, so it doesn't think it needs to.

Try this:

String path = "H:/Data/samplescript_postgres.sql";
final String [] cmd = { "C:/Program Files/PostgreSQL/9.2/bin/psql.exe",
 "-U", "postgres",
 "-d", "sampledb",
 "-f", path 
 };
try {
 reloadProcess = Runtime.getRuntime().exec(cmd);
 if(null!=reloadProcess){
 if(reloadProcess.waitFor()==0){
 System.out.println("Reloaded");
 }
 }
} catch (IOException e) {
 e.printStackTrace();
} catch (InterruptedException e) {
 e.printStackTrace();
}

You can see that I've created the array manually. For readability, I've put the option "-U" and the value "postgres" on the same line, but they're separated by a comma.

• András Váczi: Do you know any method in Java for running batch SQL against PostgreSQL?
• GregHNZ: This looks like a pretty good tutorial: zetcode.com/db/postgresqljavatutorial

Answer 2

Using psql this way isn't quite what it was developed for, so you may be surprised when it doesn't behave the same as called from the command line.

As sufleR suggested in a comment, it is possible that psql waits for a password. But you don't have any means to provide one... So, you have to avoid using a password by adding a row to your pg_hba.conf file:

local sampledb postgres trust

(Rows starting with local generally go before rows starting with host.)

If I understand you correctly, this is only a test setup, and then it is OK. In production applications it is a bad idea to connect to a database with the postgres user. Ideally, every application-database pair should have its own user (or role in database terms).

• Haseena: From commandline also I cannot give any password. But it works properly
• Haseena: Is this issue with jdbc?
• András Váczi: @Haseena from command line it looks up the password in the .pgpass file. I don't think that Java can do that. (And it looks like there is nothing JDBC does in this piece of code.) Have you tried what I've suggested?
• Haseena: Tried.... But when I trying to reopen the pgAdmin tool, it shows an error like : Error connecting to the server: FATAL: could not load pg_hba.conf
• András Váczi: Why pgAdmin? Otherwise, you have to reload PostgreSQL config to get the new pg_hba.conf working.