Postgresql – pg_dump with ssl certificates

You are on Ubuntu and obviously have Martin Pitt's pg_wrapper installed (judging from pg_ctlcluster) that is provided by the package postgresql-common and comes with the standard Debian packages. I use the same on Debian.

On a Linux system, run which in the shell to see which executable is actually picked:

postgres@db:~$ which pg_dump
/usr/bin/pg_dump
postgres@db:~$ ls -l /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37  4. Jun 18:57 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper

pg_dump is actually a symlink to pg_wrapper, which dynamically picks the appropriate version of the client program for the db cluster you run pg_dump with. I quote the man page of pg_wrapper:

This program is run only as a link to names which correspond to PostgreSQL programs in /usr/lib/postgresql/version/bin. It determines the configured cluster and database for the user and calls the appropriate version of the desired program to connect to that cluster and database, supplying any specified options to that command.

   The target cluster is selected by the following means, in descending order of precedence:
   1.  explicit specification with the --cluster option
   2.  explicit specification with the PGCLUSTER environment variable
   3.  matching entry in ~/.postgresqlrc (see postgresqlrc(5)), if that file exists
   4.  matching entry in /etc/postgresql-common/user_clusters (see user_clusters(5)), if that file exists
   5.  If only one local cluster exists, that one will be selected.
   6.  If several local clusters exist, the one listening on the default port 5432 will be selected.

   If none of these rules match, pg_wrapper aborts with an error.

IOW, the right version should be picked automagically - unless you screwed up your installation somehow. You can always add the option --cluster to be specific.

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