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 (or cluster). 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 name of the database within the cluster (= IP/port combination).
- 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
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
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'
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.