PostgreSQL: How to scroll up in long output

Question

Say I get a large query back. Postgres gives me the --More-- indicator. Pressing <space> moves down a page. Pressing <enter> moves down a line. Is there a way to scroll back up? Is it possible to pipe the output to something like less?

I'm accessing PostgreSQL 9.5 on CentOS7 through PuTTY.

For example:

pundb=# \x on
pundb=# select * from pg_roles;

-[ RECORD 1 ]--+-------------
rolname        | dinner
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | t
rolconfig      |
oid            | 10
-[ RECORD 2 ]--+-------------
rolname        | sushi
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | f
rolconfig      |
oid            | 16384
-[ RECORD 3 ]--+-------------
rolname        | drum
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
--More--

EDIT: I know that h takes me to the help. It says

b or ctrl-B Skip backwards k screenfuls of text [1]

but this does not work. Maybe because I'm in PuTTY?

Answer 1

You're probably using a $PAGER that doesn't support scrolling upwards. E.g. more.

Try executing postgresql client using a different PAGER variable:

PAGER=less psql [...]

Or:

export PAGER=less
psql [...]

If you want to make the change permanent, insert the above export line into your ~/.bash_profile.

Note: This will affect many things that use the $PAGER environment variable, but hey, it'll only enhance the experience right?

Answer 2

The --More-- indicator in the lower-left corner suggests that you're using the default pager more, which doesn't allow backward movement. You can switch to less from inside psql using this command:

\setenv PAGER 'less'

BTW, setting the pager to less -S (or typing -S and Enter inside less) will allow you to scroll sideways without wrapping (thus making the expanded mode unnecessary). And if you want to go fancy, you could use pspg :)