LLI Wiki — My Digital Knowledge Base

PostgreSQL: psql cheat sheet

psql cheat sheet: connecting, meta-commands, roles, logs, configuration and common administration queries.

psql is the PostgreSQL interactive client. This page gathers the commands used most often.

Connecting

psql -U postgres
psql -U <user> -h localhost -d postgres
sudo -u postgres psql

If a connection fails with an authentication error, check the host-based authentication file pg_hba.conf (see below).

Useful options (run psql --help for the full list):

Meta-commands

Command Action
\q Quit
\c <database> or \connect <database> Connect to a database
\l List databases
\dn List schemas
\dt *.* List tables of all schemas (without *.*, only the schemas of search_path)
\dv List views
\di List indexes
\df List functions
\df+ <function> Show the SQL code of a function
\dy List event triggers
\d <table> Show a table definition, including its triggers
\du List roles (users)
\du <role> Show one role
\x Toggle expanded display, easier to read than the ASCII tables
\copy (SELECT * FROM <table>) TO 'file.csv' WITH CSV Export a query result to a CSV file

Most \d commands accept a schema.name argument and wildcards such as *.*.

Roles and users

List the roles with their attributes:

SELECT u.usename AS "Role name",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
CREATE ROLE role_test1;                                  -- role without login
CREATE ROLE role_test2 NOINHERIT LOGIN PASSWORD '<password>';
SET ROLE role_test2;                                     -- change role for the current session
GRANT role_test2 TO role_test1;                          -- role_test1 can switch to role_test2

Configuration and logs

Find the configuration files:

SHOW config_file;
SHOW hba_file;

The main files are postgresql.conf (server settings) and pg_hba.conf (who may connect, and how).

Service management on Debian or Ubuntu:

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

To increase the log verbosity, edit postgresql.conf, restart the service, then read the log:

log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1
log_line_prefix = '%t %u %d %a '
sudo service postgresql restart
tail -f /var/log/postgresql/postgresql-<version>-main.log

log_line_prefix adds the time (%t), the user (%u), the database (%d) and the application name (%a) to each line. debug5 logs every statement, error and background task, including VACUUM: restore a normal level afterwards.

Handy queries

Sessions and activity:

SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
FROM pg_stat_activity
WHERE datname = '<database>';

Sessions waiting for a lock or for data (possibly hung). The waiting column was removed in PostgreSQL 9.6; on newer versions use wait_event_type:

SELECT * FROM pg_stat_activity WHERE waiting = 't';          -- PostgreSQL 9.5 and older
SELECT * FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;  -- PostgreSQL 9.6 and newer

Objects and sizes:

SELECT * FROM pg_proc WHERE proname = '<function_name>';      -- functions
SELECT * FROM pg_views WHERE viewname = '<view_name>';        -- views, with their definition
SELECT * FROM pg_indexes WHERE tablename = '<table>' AND schemaname = '<schema>';
SELECT pg_size_pretty(pg_total_relation_size('<table>'));     -- size of a table, with indexes
SELECT pg_size_pretty(pg_database_size('<database>'));        -- size of a database
SHOW statement_timeout;                                       -- timeout of the current session

All indexes of all tables of a schema:

SELECT
   t.relname AS table_name,
   i.relname AS index_name,
   a.attname AS column_name
FROM
   pg_class t,
   pg_class i,
   pg_index ix,
   pg_attribute a,
   pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)
   AND t.relnamespace = n.oid
   AND n.nspname = '<schema>'
ORDER BY
   t.relname,
   i.relname;

Data location and casting:

SHOW data_directory;                 -- where the database files are stored
SELECT * FROM pg_tablespace;         -- tablespaces
SELECT CAST(<column> AS <type>);     -- or <column>::<type>
SELECT '<table>'::regclass::oid;     -- OID of a table

Query analysis:

EXPLAIN <query>;           -- execution plan only
EXPLAIN ANALYZE <query>;   -- executes the query and shows the real timings
ANALYZE <table>;           -- collects planner statistics

Notes

Mastodon LLIMastodon SocialFosstodon