Postgresql – I would like to launch a pg_dump command from a remote computer

dumppostgresqlremote

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.

Best Answer

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

Related Question