CertGrid CertGrid
Hands-on Lab·PostgreSQL

PostgreSQL psql Client

psql's backslash meta-commands are a second language beside SQL - `\l`, `\du`, `\dt`, `\conninfo` - and `pg_settings` is how you read the server's configuration as a table rather than a file.

Foundations Guide 3 of 47 Beginner

Written against the versions above. Meta-commands are psql features, not server features. They will not work from an application driver, which is why the SQL equivalents are worth knowing too.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. List databases and roles

    \l lists databases; \du lists roles with their attributes.

    Both are meta-commands - interpreted by psql, not sent to the server. They are shorthand for queries against the system catalogues, which is why an application driver cannot use them and why knowing the underlying catalogue matters eventually.

    In the role list, postgres shows Superuser and the role you created shows the narrower attributes it was given.

    bash Example session 
    sudo -u postgres psql -c "\du"                             List of roles Role name |                         Attributes-----------+------------------------------------------------------------ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLSsudo -u postgres psql -c "\l"                                                     List of databases   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+----------------------- postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +           |          |          |                 |             |             |        |           | postgres=CTc/postgres template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +           |          |          |                 |             |             |        |           | postgres=CTc/postgres(3 rows)

    Expected resultThe role list, then the database list with encodings and collations.

    Success conditionYou can enumerate roles and databases in two keystrokes each.

  2. Read server settings as data

    pg_settings is a view, so configuration can be queried, filtered and joined like any table. That is a genuine advantage over reading a config file: it shows the value in effect, not what some file says.

    -x turns on expanded output, one field per line - the equivalent of MySQL's \G and just as necessary once rows get wide.

    The four settings here are the ones worth knowing on any server: how much memory is cached, how many connections are allowed, what it listens on and where.

    bash Example session 
    psql -d postgres -x -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','max_connections','listen_addresses','port')"-[ RECORD 1 ]-------------name    | listen_addressessetting | localhostunit    |-[ RECORD 2 ]-------------name    | max_connectionssetting | 100unit    |-[ RECORD 3 ]-------------name    | portsetting | 5432unit    |-[ RECORD 4 ]-------------name    | shared_bufferssetting | 16384unit    | 8kB

    Expected resultshared_buffers, max_connections, listen_addresses and port with their values.

    Success conditionYou can read any server setting without opening a file.

Troubleshooting

Official sources

← All PostgreSQL guides