Postgres login: How to log into a Postgresql database

Postgresql (Postgres) login FAQ: How do I log into a Postgres database from the command line?

Solution

To log into a Postgres database from your operating system command line, use the psql command. Let's take a look at a few psql command line options you can use.

Postgres login commands

If you are working on the same computer that Postgres is running on, you can use the following psql login command, specifying the database (mydb) and username (myuser):

psql -d mydb -U myuser

If you need to log into a Postgres database on a server named myhost, you can use this Postgres login command:

psql -h myhost -d mydb -U myuser

If for some reason you are not prompted for a password when issuing these commands, you can use the -W option, leading to these two command alternatives:

psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W

Postgres psql command help

Now, if for some reason none of those options work for you, you can get more help on the Postgresql client by typing this psql command:

psql --help

which leads to this output from the Postgresql 8.0.3 client:

This is psql 8.0.3, the PostgreSQL interactive terminal.

Usage:
  psql [OPTIONS]... [DBNAME [USERNAME]]

General options:
  -d DBNAME specify database name to connect to (default: "root")
  -c COMMAND run only single command (SQL or internal) and exit
  -f FILENAME execute commands from file, then exit
  -l list available databases, then exit
  -v NAME=VALUE set psql variable NAME to VALUE
  -X do not read startup file (~/.psqlrc)
  --help show this help, then exit
  --version output version information, then exit

Input and output options:
  -a echo all input from script
  -e echo commands sent to server
  -E display queries that internal commands generate
  -q run quietly (no messages, only query output)
  -o FILENAME send query results to file (or |pipe)
  -n disable enhanced command line editing (readline)
  -s single-step mode (confirm each query)
  -S single-line mode (end of line terminates SQL command)

Output format options:
  -A unaligned table output mode (-P format=unaligned)
  -H HTML table output mode (-P format=html)
  -t print rows only (-P tuples_only)
  -T TEXT set HTML table tag attributes (width, border) (-P tableattr=)
  -x turn on expanded table output (-P expanded)
  -P VAR[=ARG] set printing option VAR to ARG (see \pset command)
  -F STRING set field separator (default: "|") (-P fieldsep=)
  -R STRING set record separator (default: newline) (-P recordsep=)

Connection options:
  -h HOSTNAME database server host or socket directory (default: "local socket")
  -p PORT database server port (default: "5432")
  -U NAME database user name (default: "root")
  -W prompt for password (should happen automatically)

For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.