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?

Ineradicable

Tragically mine seems to be "only down" as will (linux). Must be some kind of forward only paging? – Hoodwink

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 [...]

Another viable option is most which some people like better than less, but it's not quite as ubiquitous as less; you may have to install it.

If you want to make the change permanent, insert the above export line into your ~/.bash_profile. Then, of course, to apply to your shells, you will need to log out and log in again.

Keep in mind that you can also add arguments to less inside $PAGER. I like the following options for less(1):

# ~/.bash_profile
# -R: Handle colors correctly
# -S: Chop long lines (scroll left/right using arrow keys or h/l)
export PAGER='less -RS'

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

Wernick

If you want to scroll horizontally, use PAGER="less -S" psql [...] – Groveman

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 :)

Easeful