PostgreSQL DBA Part 4: psql Commands Every DBA Needs

This is Part 4 of the PostgreSQL DBA series on SQL Tips. psql is the command-line client that ships with PostgreSQL, and it is the tool a DBA opens first on every server. By the end of this part you will be able to connect with the right options, find any database object with backslash commands, switch output formats, and run SQL scripts that stop safely on the first error.

In Part 3 we walked through the data directory (PGDATA) and the files inside /var/lib/postgresql/18/main. Before that, Part 2 installed PostgreSQL 18 on Ubuntu from the official PGDG repository. Here we use that same 18 main cluster and learn the psql commands you will type every working day.

What you will learn

• How psql connects on Ubuntu, and what -d, -U, -h and -p do
• The meta-commands DBAs use most: \l, \c, \dt, \d, \du, \conninfo and friends
• How to change output: expanded display, CSV, unaligned output for shell scripts, NULL display and timing
• How to run SQL scripts safely with ON_ERROR_STOP, -1 and psql variables
• How to automate with \gset, \gexec and \watch, and set up a useful ~/.psqlrc

Prerequisites

• Parts 1 to 3 of this series. You know what the server processes are, and you have a running cluster called 18 main.
• One Ubuntu Server LTS machine with PostgreSQL 18 from the PGDG repository, as installed in Part 2.
• An Ubuntu account that can run sudo. Every command below says which OS user runs it.

How it works

psql is a client program. It does not store data and it is not the database server. It opens a connection to the server, sends your SQL, and prints the rows that come back. Think of it as a telephone: the server is the person you call, and psql is the handset you talk through.

Everything you type in psql falls into one of two groups. Ordinary SQL (SELECT, CREATE TABLE, VACUUM) is sent to the server when psql sees a semicolon. Lines that start with a backslash, such as \dt, are meta-commands: psql handles them itself. Many meta-commands quietly run a catalog query on the server for you, which is why \dt can list tables.

On Ubuntu, /usr/bin/psql is not the real program. It is a symbolic link to pg_wrapper, a small script from the postgresql-common package. pg_wrapper picks the right psql version and the right cluster, then starts the real binary, which for PostgreSQL 18 is /usr/lib/postgresql/18/bin/psql. When you later run several clusters on one machine, psql --cluster 18/main tells pg_wrapper which one you mean.

When you give no options, psql connects through a Unix-domain socket (a special file the server listens on, here /var/run/postgresql/.s.PGSQL.5432) instead of the network. It uses your operating system user name as the database user name, and that user name as the database name. On Ubuntu the default pg_hba.conf allows peer authentication on the socket: the server checks that your Linux user name matches the database role name. That is why the normal way in is sudo -u postgres psql. Part 6 covers authentication in depth.

your shell (OS user postgres)
   |
   |  psql -d dbalab
   v
/var/run/postgresql/.s.PGSQL.5432   (Unix socket, peer authentication)
   |
   v
PostgreSQL 18 server, cluster "18 main", port 5432

Connecting to PostgreSQL with psql

Step 1: Open psql as the postgres user

Where: on the database server, as your normal Ubuntu login user (who has sudo).

sudo -u postgres psql

psql (18.6 (Ubuntu 18.6-1.pgdg24.04+2))
Type "help" for help.

postgres=#

The first line is the psql version. If the server version differed from the client, psql would print both. The prompt postgres=# shows the current database (postgres), and the # means you are connected as a superuser. A normal user sees => instead.

Check it worked: run \conninfo. In PostgreSQL 18 it prints a table; before PostgreSQL 18 it printed one sentence such as "You are connected to database ...".

\conninfo

           Connection Information
      Parameter       |        Value
----------------------+---------------------
 Database             | postgres
 Client User          | postgres
 Socket Directory     | /var/run/postgresql
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 1759
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(12 rows)

You are on the socket in /var/run/postgresql, port 5432, with no password and no SSL. Backend PID is the operating system process ID of the server process serving you (from Part 1); yours will be a different number. Hot Standby off means this server is a primary, not a read-only standby.

Step 2: Create a practice database and connect to it with options

Where: inside the psql session from Step 1 (postgres superuser), then in the shell as your login user.

CREATE DATABASE dbalab;

CREATE DATABASE

Type \q to leave psql, then connect straight to the new database by name:

sudo -u postgres psql -d dbalab -p 5432

For a remote server you would add -h and -U, for example psql -h 10.0.0.11 -U app_user -d dbalab. That needs a pg_hba.conf rule and a password, which Part 6 sets up. The same details can go in a URI: psql postgresql://app_user@10.0.0.11:5432/dbalab.

Check it worked: ask the server where you are.

SELECT current_database(), current_user;

 current_database | current_user
------------------+--------------
 dbalab           | postgres
(1 row)

Step 3: Switch databases and quit

Where: inside psql, as postgres.

\c postgres
\c dbalab
\q

You are now connected to database "postgres" as user "postgres".
You are now connected to database "dbalab" as user "postgres".

\c (short for \connect) opens a new connection and closes the old one. In PostgreSQL there is no USE dbname statement as in MySQL; a session belongs to exactly one database, so switching means reconnecting. If the new connection fails in an interactive session, psql keeps the old one. \q exits psql (Ctrl+D does the same).

Exploring a database with meta-commands

Step 4: Create a sample table

Where: in the shell, as your login user. Open psql on dbalab and paste the SQL.

sudo -u postgres psql -d dbalab

CREATE TABLE orders (
    order_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer    text          NOT NULL,
    amount      numeric(10,2) NOT NULL,
    status      text          NOT NULL DEFAULT 'NEW',
    order_date  date          NOT NULL DEFAULT current_date,
    shipped_on  date
);
CREATE INDEX orders_status_idx ON orders (status);
INSERT INTO orders (customer, amount, status, order_date, shipped_on) VALUES
  ('Asha Rao',     1250.00, 'SHIPPED', '2026-10-01', '2026-10-03'),
  ('Vikram Singh',  499.50, 'NEW',     '2026-10-02', NULL),
  ('Meera Iyer',   3200.00, 'SHIPPED', '2026-10-03', '2026-10-05'),
  ('Rahul Verma',   150.00, 'PAID',    '2026-10-03', NULL),
  ('Sara Khan',     780.25, 'PAID',    '2026-10-04', NULL);

CREATE TABLE
CREATE INDEX
INSERT 0 5

While you type a statement across several lines, the prompt changes from dbalab=# to dbalab-#. The dash means psql is waiting for the rest of the command. Nothing is sent until the semicolon.

Step 5: List databases, tables, indexes and roles

Where: inside psql on dbalab, as postgres. These are the meta-commands you will use most. Add a pattern to filter, for example \dt ord*, add + for more detail, and (new in PostgreSQL 18) add x to show a list in expanded mode, for example \dtx.

\l

                                                 List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------
 dbalab    | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 postgres  | postgres | 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)

Four databases: your dbalab, the default postgres database, and the two templates that new databases are copied from (Part 8). Your Collate and Ctype columns show whatever locale the cluster was created with, often en_US.UTF-8 or C.UTF-8.

\d orders

                              Table "public.orders"
   Column   |     Type      | Collation | Nullable |           Default
------------+---------------+-----------+----------+------------------------------
 order_id   | integer       |           | not null | generated always as identity
 customer   | text          |           | not null |
 amount     | numeric(10,2) |           | not null |
 status     | text          |           | not null | 'NEW'::text
 order_date | date          |           | not null | CURRENT_DATE
 shipped_on | date          |           |          |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "orders_status_idx" btree (status)

\d is the quickest way to answer "what does this table look like and which indexes does it have?". The primary key created the index orders_pkey automatically. \d+ orders adds column storage details, comments and the table's access method.

\du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

Only the postgres superuser exists so far. You will create more roles in Part 7.

Check it worked: \dt should list exactly one table, public.orders.

           List of tables
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | orders | table | postgres
(1 row)

Step 6: See the SQL behind a meta-command, and get help

Where: in the shell, as your login user.

sudo -u postgres psql -d dbalab -E -c '\dn'

/******** QUERY *********/
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
/************************/

      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

This is how DBAs learn the system catalogs: let psql show you its query, then copy and adapt it for your own monitoring scripts. For help, \? lists every meta-command and \h CREATE DATABASE prints the SQL syntax of any statement, ending with a link to the matching documentation page.

Controlling output formats

Step 7: Use expanded display for wide rows

Where: inside psql on dbalab, as postgres. When a row has many columns, the normal table wraps and becomes unreadable, especially in a narrow terminal on a phone over SSH. Expanded mode prints one column per line.

\x
SELECT * FROM orders WHERE order_id = 1;
\x

Expanded display is on.
-[ RECORD 1 ]----------
order_id   | 1
customer   | Asha Rao
amount     | 1250.00
status     | SHIPPED
order_date | 2026-10-01
shipped_on | 2026-10-03

Expanded display is off.

Three handy variants: \x auto switches to expanded mode only when a result is too wide for the screen; \gx at the end of a query, instead of the semicolon, uses expanded mode for that one query; and psql -x starts the whole session in expanded mode.

SELECT order_id, customer, amount FROM orders WHERE order_id = 3 \gx

-[ RECORD 1 ]--------
order_id | 3
customer | Meera Iyer
amount   | 3200.00

Step 8: Show NULLs clearly and time your queries

Where: inside psql on dbalab, as postgres. By default a NULL prints as an empty cell, so you cannot tell NULL from an empty string. \pset null sets a visible marker, and \timing prints how long each statement took.

\pset null '(null)'
SELECT order_id, status, shipped_on FROM orders ORDER BY order_id;
\timing on
SELECT count(*) FROM orders;

Null display is "(null)".
 order_id | status  | shipped_on
----------+---------+------------
        1 | SHIPPED | 2026-10-03
        2 | NEW     | (null)
        3 | SHIPPED | 2026-10-05
        4 | PAID    | (null)
        5 | PAID    | (null)
(5 rows)

Timing is on.
 count
-------
     5
(1 row)

Time: 0.262 ms

The time is measured by psql, so it includes the network round trip as well as the server work. It is perfect for a quick "is this faster now?" check; Part 26 shows how to read EXPLAIN ANALYZE for the server-side detail. \pset on its own lists every print option and its current value.

Step 9: Get plain output for shell scripts, CSV and files

Where: in the shell, as the postgres OS user's command (through sudo). Shell scripts do not want borders, headers or a "(2 rows)" footer. They want just the values.

sudo -u postgres psql -d dbalab -At -c "SELECT customer, amount FROM orders WHERE status = 'PAID' ORDER BY order_id;"
sudo -u postgres psql -d dbalab --csv -c "SELECT order_id, customer, amount FROM orders ORDER BY order_id;"

Rahul Verma|150.00
Sara Khan|780.25
order_id,customer,amount
1,Asha Rao,1250.00
2,Vikram Singh,499.50
3,Meera Iyer,3200.00
4,Rahul Verma,150.00
5,Sara Khan,780.25

With -At a single value comes back bare, so a shell script can capture it: ROWS=$(sudo -u postgres psql -X -d dbalab -At -c "SELECT count(*) FROM orders;") sets ROWS to 5. The -X option (skip the startup file) is explained in Step 13.

To write a result to a file, use \copy. It runs the SQL COPY command, but psql reads or writes the file on the client side, with the permissions of your OS user, and it does not need superuser rights. The server-side SQL COPY ... TO '/path' instead writes as the server's OS user and needs superuser or the pg_write_server_files role.

sudo -u postgres psql -d dbalab -c "\copy (SELECT order_id, customer, amount FROM orders ORDER BY order_id) TO '/var/lib/postgresql/orders.csv' WITH (FORMAT csv, HEADER)"
sudo -u postgres head -3 /var/lib/postgresql/orders.csv

COPY 5
order_id,customer,amount
1,Asha Rao,1250.00
2,Vikram Singh,499.50

COPY 5 confirms five rows were written. /var/lib/postgresql is the home directory of the postgres OS user on Ubuntu, so the file is owned by postgres. A \copy command must fit on one line. Cleanup: sudo -u postgres rm /var/lib/postgresql/orders.csv.

Running SQL scripts safely

Step 10: Run a script with -f, and make it stop on errors

Where: on the database server, as the postgres OS user. Open a shell as that user so the files land in its home directory:

sudo -i -u postgres
cd ~
pwd

/var/lib/postgresql

Create a small change script with nano (nano fix_orders.sql). It has a typo on line 2 on purpose, the kind of mistake that happens in real change scripts:

UPDATE orders SET status = 'SHIPPED', shipped_on = DATE '2026-10-06' WHERE order_id = 4;
UPDATE ordrs SET status = 'SHIPPED' WHERE order_id = 5;
UPDATE orders SET status = 'CANCELLED' WHERE order_id = 2;

First run it the default way:

psql -d dbalab -f fix_orders.sql
echo "exit code: $?"

UPDATE 1
psql:fix_orders.sql:2: ERROR:  relation "ordrs" does not exist
LINE 1: UPDATE ordrs SET status = 'SHIPPED' WHERE order_id = 5;
               ^
UPDATE 1
exit code: 0

This is the trap. By default psql reports the error and carries on with the next statement, so rows 4 and 2 were changed and row 5 was not. Worse, the exit code is 0, so a cron job or deployment tool thinks everything went fine. The table is now half-changed.

Put the two rows back, then run the script the safe way:

psql -d dbalab -c "UPDATE orders SET status = 'NEW' WHERE order_id = 2;" -c "UPDATE orders SET status = 'PAID', shipped_on = NULL WHERE order_id = 4;"
psql -d dbalab -v ON_ERROR_STOP=1 -1 -f fix_orders.sql
echo "exit code: $?"

UPDATE 1
UPDATE 1
UPDATE 1
psql:fix_orders.sql:2: ERROR:  relation "ordrs" does not exist
LINE 1: UPDATE ordrs SET status = 'SHIPPED' WHERE order_id = 5;
               ^
exit code: 3

The first two UPDATE 1 lines come from the undo commands. Then the script ran line 1, hit the error on line 2, stopped and rolled back. Line 3 never ran, and exit code 3 tells the calling script that a SQL error stopped it. The documented exit codes are: 0 finished normally, 1 fatal psql error (for example file not found), 2 connection lost in a non-interactive session, 3 error in a script with ON_ERROR_STOP set.

Check it worked: nothing was changed.

SELECT order_id, status, shipped_on FROM orders WHERE order_id IN (2, 4, 5) ORDER BY order_id;

 order_id | status | shipped_on
----------+--------+------------
        2 | NEW    |
        4 | PAID   |
        5 | PAID   |
(3 rows)

Step 11: Pass values into a script with variables

Where: as the postgres OS user, in its home directory. psql variables let one script work for many inputs. Set them with -v name=value on the command line or \set name value inside psql. In SQL, :name inserts the value as is, :'name' inserts it as a quoted string literal, and :"name" as a quoted identifier.

Create orders_by_status.sql:

\echo Report for status :status
SELECT order_id, customer, amount
  FROM orders
 WHERE status = :'status'
 ORDER BY order_id;

psql -d dbalab -v status=PAID -f orders_by_status.sql

Report for status PAID
 order_id |  customer   | amount
----------+-------------+--------
        4 | Rahul Verma | 150.00
        5 | Sara Khan   | 780.25
(2 rows)

Use :'status' for values, never ':status': variables are not replaced inside quoted SQL text, and the quoting form also escapes any single quote in the value safely.

The reverse direction is \gset: it runs a query that returns exactly one row and stores each column in a psql variable named after the column.

SELECT count(*) AS order_count, sum(amount) AS total FROM orders \gset
\echo Orders: :order_count Total: :total

Orders: 5 Total: 5879.75

Step 12: Generate and run statements with \gexec, and repeat a query with \watch

Where: inside psql on dbalab, as postgres. \gexec runs a query, then executes every value in the result as a SQL statement. DBAs use it to apply the same command to many objects. Here it runs ANALYZE (which refreshes planner statistics, Part 32) on every user table.

\set ECHO queries
SELECT format('ANALYZE %I.%I', schemaname, relname)
  FROM pg_stat_user_tables
 ORDER BY relname \gexec

SELECT format('ANALYZE %I.%I', schemaname, relname)
  FROM pg_stat_user_tables
 ORDER BY relname 
ANALYZE public.orders
ANALYZE

With ECHO set to queries, psql prints the query it sends, then each generated statement (ANALYZE public.orders) before its result (ANALYZE). format() with %I quotes identifiers correctly, so table names with capital letters or spaces do not break the generated SQL. Undo the echo with \unset ECHO.

\watch re-runs the last query every few seconds until you press Ctrl+C. It is the simplest live monitor: for example, watch the client sessions during a deployment.

SELECT pid, state, left(query, 40) AS query
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
\watch interval=2 count=2

           Thu Oct 22 12:51:17 2026 (every 2s)

 pid  | state  |                  query
------+--------+------------------------------------------
 1904 | active | SELECT pid, state, left(query, 40) AS qu
(1 row)

           Thu Oct 22 12:51:19 2026 (every 2s)

 pid  | state  |                  query
------+--------+------------------------------------------
 1904 | active | SELECT pid, state, left(query, 40) AS qu
(1 row)

Each run is stamped with the time. The only client session is your own psql. Named options such as count= need psql 16 or later, min_rows= needs 17, and PostgreSQL 18 adds the WATCH_INTERVAL variable to change the default 2-second wait. Part 10 covers pg_stat_activity properly.

Setting up ~/.psqlrc

Step 13: Create a startup file for the postgres user

Where: on the database server, as the postgres OS user, in /var/lib/postgresql. psql reads a system-wide file called psqlrc and then your personal ~/.psqlrc every time it starts, unless you pass -X. On Ubuntu the system-wide file would be /etc/postgresql-common/psqlrc (the directory pg_config --sysconfdir reports); it does not exist by default. Create the personal file with nano ~/.psqlrc:

-- ~/.psqlrc for the postgres OS user
\set QUIET 1
\pset null '(null)'
\set PROMPT1 '%n@%/%R%x%# '
\set HISTFILE ~/.psql_history-:DBNAME
\set HISTCONTROL ignoredups
\x auto
\timing on
\set QUIET 0

Check it worked: start psql and open a transaction.

psql -d dbalab

psql (18.6 (Ubuntu 18.6-1.pgdg24.04+2))
Type "help" for help.

postgres@dbalab=# BEGIN;
BEGIN
Time: 0.103 ms
postgres@dbalab=*# UPDATE orders SET amount = amount WHERE order_id = 2;
UPDATE 1
Time: 0.585 ms
postgres@dbalab=*# ROLLBACK;
ROLLBACK
Time: 0.378 ms
postgres@dbalab=#

The prompt now names the user and database, and the * warns you that a transaction is open. That one character prevents a classic incident: an open transaction left in a forgotten terminal, holding locks for hours. (The default prompt shows it too, as dbalab=*#.)

Undo: rm ~/.psqlrc, or start psql with -X to ignore the file for one run. Use -X in automated scripts: with this file in place, psql -At -c "SELECT count(*) FROM orders;" prints an extra Time: ... ms line after the number, which breaks any script that reads the output. You can also make a file version-specific, for example ~/.psqlrc-18.

Common mistakes and how to fix them

psql: FATAL: role "ubuntu" does not exist

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "ubuntu" does not exist

You ran plain psql as your Linux user, so psql tried a database role with the same name. Fix: run sudo -u postgres psql, or create a role for yourself (Part 7). As root you see the same error with role "root".

FATAL: Peer authentication failed for user "postgres"

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

You ran psql -U postgres as a different Linux user. Peer authentication requires the OS user name to match the role. Fix: sudo -u postgres psql, not psql -U postgres.

fe_sendauth: no password supplied

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: fe_sendauth: no password supplied

Adding -h localhost switches from the socket to TCP, where Ubuntu's default pg_hba.conf asks for a password (scram-sha-256), and the postgres role has none. In a terminal psql first asks for a password; in a script, or with -w (never prompt), you get this error. Fix: drop -h for local admin work, or set a password and rules as shown in Part 6.

FATAL: database "salesdb" does not exist

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "salesdb" does not exist

A typo, or the database is on another cluster. Fix: list databases with sudo -u postgres psql -l and check the port with pg_lsclusters.

No such file or directory ... Is the server running locally?

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

Nothing listens on that port: wrong -p, or the cluster is down. Fix: run pg_lsclusters; if Status says down, start it with sudo systemctl start postgresql@18-main (Part 2).

The query does nothing and the prompt shows dbalab-#

You forgot the semicolon, so psql is waiting for more input. Type ; to send it, or \r to clear the query buffer and start again.

ERROR: syntax error at or near "order"

ERROR:  syntax error at or near "order"
LINE 1: SELECT * FROM order;
                      ^

A one-letter typo turned the table name into the reserved word ORDER. psql points at the exact spot with ^. Fix the name (orders); use \errverbose to see the most recent error at maximum detail.

Practice exercise

1. Write one shell command that prints only the number of orders with status SHIPPED, as a bare number with no header or footer.
2. Write a script total_for.sql that prints the total amount for a customer passed in with -v cust=..., and run it so it stops on any error.

Quick reference

# Connect as the postgres superuser (Unix socket, peer authentication)
sudo -u postgres psql
# Connect to a database on a port; remote form adds -h and -U
sudo -u postgres psql -d dbalab -p 5432
psql -h 10.0.0.11 -U app_user -d dbalab
# Inside psql: connection info, switch database, quit
\conninfo
\c dbalab
\q
# Inside psql: list databases, tables, one table, indexes, schemas, roles, extensions
\l
\dt
\d orders
\di
\dn
\du
\dx
# Show the catalog query behind a meta-command
sudo -u postgres psql -d dbalab -E -c '\dn'
# Inside psql: expanded display, one-off expanded query, NULL marker, timing
\x auto
SELECT * FROM orders WHERE order_id = 3 \gx
\pset null '(null)'
\timing on
# Script-friendly output: bare values, CSV
sudo -u postgres psql -d dbalab -At -c "SELECT count(*) FROM orders;"
sudo -u postgres psql -d dbalab --csv -c "SELECT * FROM orders;"
# Client-side export to a CSV file
sudo -u postgres psql -d dbalab -c "\copy (SELECT * FROM orders) TO '/var/lib/postgresql/orders.csv' WITH (FORMAT csv, HEADER)"
# Run a change script safely (stop on error, one transaction)
psql -X -d dbalab -v ON_ERROR_STOP=1 -1 -f fix_orders.sql
# Pass a variable into a script
psql -d dbalab -v status=PAID -f orders_by_status.sql
# Inside psql: store results in variables, generate statements, repeat a query
SELECT count(*) AS order_count FROM orders \gset
SELECT format('ANALYZE %I.%I', schemaname, relname) FROM pg_stat_user_tables \gexec
SELECT count(*) FROM pg_stat_activity \watch interval=2
# Ignore ~/.psqlrc for one run
psql -X -d dbalab

Summary and what's next

• psql is a client: SQL goes to the server at the semicolon, backslash meta-commands are handled by psql.
• On Ubuntu, sudo -u postgres psql connects over the Unix socket with peer authentication; /usr/bin/psql is pg_wrapper, which starts /usr/lib/postgresql/18/bin/psql.
• -d, -U, -h and -p choose database, user, host and port; \c switches database, \conninfo shows where you are.
• \l, \dt, \d, \di, \dn, \du and \dx answer most "what is in this database?" questions; -E shows the SQL behind them.
• \x auto, \gx, \pset null and \timing make interactive output readable; -At and --csv make it script-friendly.
• Production scripts run with -X -v ON_ERROR_STOP=1 -1 -f, and check for exit code 3.
• Variables (-v, :'name', \gset), \gexec and \watch automate routine DBA work.
• A ~/.psqlrc with a prompt showing the transaction state prevents forgotten open transactions.

In Part 5 you will learn postgresql.conf basics: how parameters are set, the difference between reload and restart, and how to read pg_settings.