Jump to content

PostgreSQL/ClientServerComm Client

From Wikibooks, open books for an open world

Before a client program like createdb, psql, pg_dump, vacuumdb, ... can perform any action on a database, it must establish a connection to that database. To do so, it must provide concrete values for the essential boundary conditions.

  • The IP address or DNS name of the server, where the instance is running.
  • The port on this server, to whom the instance is listening. (The combination of IP address and port identifies the instance. Multiple instances at the same IP address are possible as long as the port is different.)
  • The name of the database within the instance (respective within the cluster).
  • The name of the user (= role) with which the client program wants to work.
  • The password of this user.

You can specify these values in three different ways:

  • As explicit parameters of the client program.
  • As environment variables.
  • As a fixed line of text in the special file pgpass.

Parameters

[edit | edit source]

You can specify the parameters in the usual short (-) or long (--) format of createdb, psql, pg_dump, vacuumdb, and other standard PostgreSQL command-line tools.

$ # Example
$ psql -h www.dbserver.com --port=5432   ....

The parameter names and their meanings are:

Short Form Long Form Meaning
-h --host IP or DNS
-p --port port number (default: 5432)
-d --dbname database within the cluster
-U --username name of the user

If necessary, the client program will prompt for the password.

Environment Variables

[edit | edit source]

As an alternative to the parameter passing, you can define environment variables within your shell.

Environment Variable Meaning
PGHOST IP or DNS
PGPORT port number (default: 5432)
PGDATABASE database within the cluster
PGUSER name of the user
PGPASSWORD password of this user (not recommended)
PGPASSFILE name of a file where those values are stored as plain text, see below (default: .pgpass)

File 'pgpass'

[edit | edit source]

Instead of using parameters or environment variables as shown above you can store those values in a file. Use one line per definition in the form:

 hostname:port:database:username:password

The default filename on UNIX systems is ~/.pgpass and on Windows: C:\Users\MyUser\AppData\Roaming\postgresql\pgpass.conf. On UNIX systems the file protections must disallow any access of world or group: chmod 0600 ~/.pgpass.

You can create the file with any text editor. This is not necessary if you use pgAdmin. pgAdmin creates the file automatically after a successful connection and stores the actual connection values.