While postgresql is a great database with lots of features that you would normally only get in an enterprise database, navigating through it on the command line the first few lines can be a bit of an experience. So, I thought that if I write down all of the tricks I learn, they may help somebody else someday. As connecting to postgresql with psql is a requirement for nearly anything you will need to do, we will discuss using Postgresql Command Line Login / Using .pgpass.
Psql Command Line Examples
You can login to the database directly from a linux shell using psql. Typically, one would list the parameters used to connect directly on the command line.
| # psql -h www.uptimemadeeasy.com -p 5432 -U mary -W -d mydatabase Password for user mary: psql (9.3.1) Type “help” for help.mydatabase=> \q |
The example above used the single character options. You can also use the longer more descriptive command line options:
| # psql –host=www.uptimemadeeasy.com –port=5432 –username=mary –password –dbname=mydatabase Password for user mary: psql (9.3.1) Type “help” for help.acadoo=> \q |
You can get more psql options from the linux shell just by typing: psql –help
| # psql –help psql is the PostgreSQL interactive terminal.Usage: psql [OPTION]… [DBNAME [USERNAME]]General options: -c, –command=COMMAND run only single command (SQL or internal) and exit -d, –dbname=DBNAME database name to connect to (default: “root”) -f, –file=FILENAME execute commands from file, then exit -l, –list list available databases, then exit -v, –set=, –variable=NAME=VALUE set psql variable NAME to VALUE -V, –version output version information, then exit -X, –no-psqlrc do not read startup file (~/.psqlrc) -1 (“one”), –single-transaction execute as a single transaction (if non-interactive) -?, –help show this help, then exitInput and output options: -a, –echo-all echo all input from script -e, –echo-queries echo commands sent to server -E, –echo-hidden display queries that internal commands generate -L, –log-file=FILENAME send session log to file -n, –no-readline disable enhanced command line editing (readline) -o, –output=FILENAME send query results to file (or |pipe) -q, –quiet run quietly (no messages, only query output) -s, –single-step single-step mode (confirm each query) -S, –single-line single-line mode (end of line terminates SQL command)Output format options: -A, –no-align unaligned table output mode -F, –field-separator=STRING set field separator (default: “|”) -H, –html HTML table output mode -P, –pset=VAR[=ARG] set printing option VAR to ARG (see \pset command) -R, –record-separator=STRING set record separator (default: newline) -t, –tuples-only print rows only -T, –table-attr=TEXT set HTML table tag attributes (e.g., width, border) -x, –expanded turn on expanded table output -z, –field-separator-zero set field separator to zero byte -0, –record-separator-zero set record separator to zero byte Connection options: For more information, type “\?” (for internal commands) or “\help” (for SQL Report bugs to <pgsql-bugs@postgresql.org>. |
Postgresql Psql Using .pgpass
Now that you can connect, you may not want to have to type all of those parameters if you plan to do it frequently or if you plan to create some scripts using psql. One method for getting around having to use all of these parameters is to create a .pgpass file to store the parameters. The password file is typically located at $HOME/.pgpass or ~/.pgpass whichever way you prefer to say it.
.pgpass file format
A typical .pgpass file will have the following fields:
| host:port:dbname:user:password |
Below is an example .pgsql file:
| www.uptimemadeeasy.com:5432:marydb:mary:marypassword |
Now that we have our .pgpass file setup for the mary user, it becomes much easier to login because we no longer need to state all of the parameters.
| [mary@www.uptimemadeeasy.com ~]$ psql WARNING: password file “/home/mary/.pgpass” has group or world access; permissions should be u=rw (0600) or less psql (9.2.3) Type “help” for help.mary=> |
Oops! We forgot to set the proper permissions on the file. This is very important. If we don’t set the permissions so only the owner has permissions then anybody can read the file and get access to the database. So, we set the .pgpass permissions:
| [mary@www.uptimemadeeasy.com ~]$ chmod 0600 .pgpass [mary@www.uptimemadeeasy.com ~]$ ls -l .pgpass -rw——- 1 mary mary 45 Nov 26 21:44 .pgpass |
And that clears up the error message:
| [mary@www.uptimemadeeasy.com ~]$ psql psql (9.2.3) Type “help” for help.mary=> |
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014
