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):
-E: shows the SQL queries behind the backslash commands (useful for learning).-l: lists the databases and exits. Useful when the user has no default database, as on managed services such as AWS RDS.
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
- The original note also listed a query on
pg_stat_get_backend_pidandpg_stat_get_backend_activity. It was left out:pg_stat_activitygives the same information in a readable form. - Commands that create roles with a password must be run with a real password. Do not store it in a shared script.
- Credentials, host names and a schema name from the original note were replaced by placeholders.