1

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.

Improve this question
2
  • 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, 2012 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, 2012 at 16:15

1 Answer 1

Reset to default
4

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

Improve this answer
5
  • 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
    – user1822
    Aug 20, 2012 at 14:25
  • nice, I understand a little better ;-)
    – Stephane Rolland
    Aug 20, 2012 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, 2012 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.
    – user1822
    Aug 20, 2012 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, 2012 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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