Skip to content

psql

The command line client of PostgreSQL: connect to a database, run SQL and inspect databases, roles and tables with backslash commands.

psql output

Syntax

psql [-h HOST] [-p PORT] [-U USER] [-d DATABASE] [-c 'SQL']

Options

Option Description
-h HOST Connect over TCP instead of the local socket
-U USER, -d DB Database role and database
-c 'SQL' Run one command and exit
-f FILE Run the SQL commands from a file
-l List all databases
-t, -A Only rows, no header; unaligned (for scripts)

Inside psql: \l databases, \du roles, \dt tables, \d TABLE structure, \c DB switch database, \x vertical output, \conninfo current connection, \q quit.

Examples

All databases on the server:

sudo -u postgres psql -l
                                                 List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 shop      | app      | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |         |         |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |         |         |        |           | postgres=CTc/postgres
(4 rows)

Roles and their attributes:

sudo -u postgres psql -c '\du'
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 app       |
 nox       |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

Connect as an application user with a password:

psql -h localhost -U app -d shop -c '\dt'
Password for user app:
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | orders | table | app
(1 row)

Run a query:

psql -h localhost -U app -d shop -c 'SELECT customer, total FROM orders ORDER BY total DESC;'
Password for user app:
 customer | total
----------+--------
 Ben      | 120.00
 Anna     |  49.90
 Clara    |  15.50
(3 rows)

Only the value, for scripts:

sudo -u postgres psql -d shop -tAc 'SELECT count(*) FROM orders'
3

Peer authentication fails:

psql -d shop -U app
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "app"

Over the socket, PostgreSQL only lets the Linux user app in as role app. Connect over TCP with a password instead: psql -h localhost -U app -d shop.

Size of all databases:

sudo -u postgres psql -c 'SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY 1;'
  datname  | pg_size_pretty
-----------+----------------
 postgres  | 7510 kB
 shop      | 7606 kB
 template0 | 7353 kB
 template1 | 7510 kB
(4 rows)

When do I need it?

  • To look into a database: which tables, how many rows, who owns what
  • To create roles, grant rights or change settings as an admin
  • In scripts and health checks with -tAc

Passwords for scripts in ~/.pgpass

A line localhost:5432:shop:app:secret in ~/.pgpass (mode 600) saves the password prompt. Never put passwords into the command line or PGPASSWORD in shared scripts.

Every statement runs at once

psql commits every statement immediately. Before a risky UPDATE or DELETE type BEGIN;, check the result, then COMMIT; or ROLLBACK;.

Distribution differences

Debian Ubuntu RHEL Arch
Package postgresql-client postgresql-client postgresql postgresql-libs

createuser · createdb · pg_isready · pg_dump