Disabling the psql Pager for the postgres User
By default, psql pipes query output through a pager (less by default) whenever the output is longer than the terminal height. That is convenient in an interactive session, but it gets in the way in two situations I kept running into: running SQL from scripts where you want the full output printed directly, and using psql as the postgres system user (for example in cron jobs or Ansible tasks), where an interactive pager makes no sense at all.
The Problem
The postgres user's home directory is /var/lib/postgresql, and any psql session started with sudo -i -u postgres reads its startup file from there. There is no .psqlrc for that user by default, so every query result is pushed through the pager, and long results leave the session stuck inside less.
The Fix: a .psqlrc File
sudo -i -u postgres
printf '\\pset pager off\n' > /var/lib/postgresql/.psqlrc
Then verify the setting is picked up:
psql
psql (16.x ...)
Type "help" for help.
postgres=# \pset
pager : off
You can also confirm it end-to-end by running a query with more output than the terminal height — results should scroll straight through without opening less.
Why This Works
psql reads ~/.psqlrc at startup for interactive sessions (unless you start it with psql -X). Placing \pset pager off in that file makes the setting apply to every interactive session for that user, with no per-command typing.
Alternatives and Gotchas
- Per-session:
\pset pager off(or\pset pager 0) inside a session disables the pager just for that session. - No file needed: for scripted runs, set the environment variable
PSQL_PAGER=cat(orPAGER=cat) — the pager never opens. - Skipping rc files entirely:
psql -Xignores all.psqlrcfiles, which is useful when you want to test with clean defaults or when a.psqlrccontains something broken. - Non-interactive output: when
psqloutput is not a terminal (redirected to a file or piped), no pager is used anyway — the pager only matters for interactive sessions. - On Debian/Ubuntu systems the
postgresuser's home may be/var/lib/postgresql; on RHEL derivatives it is usually/var/lib/pgsql. Useeval echo ~postgresto confirm the real home directory before creating the file.