Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to launch the command: pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydb from a remote computer. Both the database and the remote computer are under xp.

I face two troubles:

  1. when executed on the command line of the server, pgdump will first prompt for a password. How can I grant the password in the command line ? A quick look at the documentation of pg_dump doesn't obviously accept a flag to do that (though I may have missed it).

  2. if both machines had been under linux I would have tried an SSH connection with Putty to execute some commands on the psql server... but with both machines being under windows, I don't have an idea where to start. Does the psqlODBC driver allow access to pg_dump ? Is there an equivalent to SSL for windows ?

Only to explain the context, indeed my main goal is to achieve a diagnostic script on the remote machine that will gather some meaningful information, including a dump of some or all tables of the postgresql base. The diagnostic script will the copy the dump into another place on the domain where all diagnostic results are gathered.

The script has to be executed on the remote machine, because it must also performs other operations on other computers connected to the domain that aren't the postgresql database.

share|improve this question
1  
You're confusing SSH and SSL. SSL is about encrypting communication between client and server, and it's supported by PostgreSQL on all platforms. It's not about executing remote commands. SSH is about executing remote commands while encrypting the network traffic with SSL. –  Daniel Vérité Aug 20 '12 at 16:12
    
@DanielVérité Yep you're right, I had used Putty, that is an SSH client. So I'm gonna edit my question accordingly. Thx. –  Stephane Rolland Aug 20 '12 at 16:15

1 Answer 1

up vote 4 down vote accepted

Regarding the password, you have two choices:

  1. An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html
  2. A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html

I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any computer that can connect to the database. It is not a "server application".

But to run a program on a remote Windows computer you can use PsExec

share|improve this answer
1  
@StephaneRolland: correct. You just need to copy all the binaries. Unfortunately there is no "client only" setup. I usually take the ZIP file and unzip wherever I need it: enterprisedb.com/products-services-training/pgbindownload –  a_horse_with_no_name Aug 20 '12 at 14:25
    
nice, I understand a little better ;-) –  Stephane Rolland Aug 20 '12 at 14:34
    
from what I understand in the PsExec documentation, the flag -c would allow to copy the executable. Moreover, if the file is not copied, PsExec expect the executable to be in the system path: maybe I could put the psql/bin/ directory in the system path and it's ok. i'm gonna try. –  Stephane Rolland Aug 20 '12 at 15:26
    
@StephaneRolland: as I said: you don't really need PsExec. You can run pg_dump on any arbitrary computer that can connect to the database. –  a_horse_with_no_name Aug 20 '12 at 16:03
    
yep, but in that case I need to copy the pg_dump.exe into the remote machine. I want to try using PsExec too. Since the remote machine has already tools like PsKill, it would be acceptable, almost logical I add the PsExec to its list. I'm gonna try both and discuss them with my colleagues. –  Stephane Rolland Aug 20 '12 at 16:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.