PostgreSQL: Up and Running, 3rd Edition

Chapter 3. psql

psql is the de rigueur command-line utility packaged with PostgreSQL. Aside from its common use of running queries, you can use psql to execute scripts, import and export data, restore tables, do other database administration, and even generate reports. If you have access only to a server’s command line with no GUI, psql is your only choice to interact with PostgreSQL. If you fall into this group, you have to be intimate with myriad commands and options. We suggest that you print out the dump of psql help as discussed in “psql Interactive Commands” and enshrine it above your workstation.

Environment Variables

As with other command-line tools packaged with PostgreSQL, you can forgo specifying your connection settings—host, port, user—by initializing the PGHOST, PGPORT, and PGUSER environment variables. To avoid having to retype the password, you can initialize the variable PGPASSWORD. For more secure access, create a password file as described in PostgreSQL Password File. Since version 9.2 psql accepts two new environment variables:

PSQL_HISTORY

Sets the name of the psql history file that lists all commands executed in the recent past. The default is ~/.psql_history.

PSQLRC

Specifies the location and name of a custom configuration file. Should you decide to create this file, you can place most of your settings in here. At startup, psql will read settings from your configuration file before loading default values, and your file’s settings will override the defaults.

If you ...