Introduction

If you are a PostgreSQL database administrator like me where you operate many databases, it is a vital task to organize your sql-scripts in a way for easy lookup and execution. I have tons of scripts. How am I supposed to have them at hand when I need them? I need to lookup these scripts from within psql to stay focused. I will share with you my solution to this problem.

I need fast answers to all kinds of questions:

• Who is logged in? (w.sql)
• What queries are running? - active sessions (as.sql)
• Bloat, is vacuuming happening?
• Who has ingested the most data? schema size, database size

We need a PostgreSQL server to play with (let’s use docker compose)

-- we 
$ cat docker-compose.yml
services:
  db:
    image: "postgres:latest"
    container_name: db
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: "root"
      POSTGRES_PASSWORD: "secret"
      POSTGRES_DB: "db"
    command: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all
    volumes:
      - ./data:/var/lib/postgresql

Startup the service in detached mode

$ docker compose up -d

For convenience, set these environment variables

$ cat env.sh
export PGHOST=localhost
export PGUSER=root
export PGPASSWORD=secret
export PGPORT=5432
export PGDATABASE=db
export PGOPTIONS=--search_path=public

Now, let’s go ahead and look for scripts.

$ . env.sh
$ psql

Timing is on.
Pager is always used.
Pager usage is off.
Line style is unicode.
Border style is 2.
psql (16.4, server 16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
[localhost] root@db  =# -- let us list scripts where filename contains the word 'size' 
[localhost] root@db  =# \set f size
[localhost] root@db  =# :ls
/Users/BJBRA/projects/postgesql/psql/tablesize.sql
/Users/BJBRA/projects/postgesql/psql/tuplesize_by_page.sql
/Users/BJBRA/projects/postgesql/psql/dbsize.sql
/Users/BJBRA/projects/postgesql/psql/tablesize1.sql
/Users/BJBRA/projects/postgesql/psql/schemas_by_size.sql
/Users/BJBRA/projects/postgesql/psql/objectsize.sql
/Users/BJBRA/projects/postgesql/psql/tablesize_partitioned.sql

[localhost] root@db  =# -- now output the size of each schema in a database
[localhost] root@db  =# \i /Users/BJBRA/projects/postgesql/psql/schemas_by_size.sql
┌────────────────────┬─────────┐
│    table_schema    │ size_mb │
├────────────────────┼─────────┤
│ public             │  760.48 │
│ pg_catalog         │    7.53 │
│ information_schema │    0.24 │
└────────────────────┴─────────┘
(3 rows)
[localhost] root@db  =# -- output the size of each database 
[localhost] root@db  =# \i /Users/BJBRA/projects/postgesql/psql/dbsize.sql
┌───────────────┬────────────┐
│ database_name │ size_in_mb │
├───────────────┼────────────┤
│ db            │ 768 MB     │
│ template1     │ 7556 kB    │
│ postgres      │ 7492 kB    │
│ template0     │ 7337 kB    │
└───────────────┴────────────┘
(4 rows)

How did I do this?

There is a magic script behind this. First the dotfile ‘~/.psqlrc’ has the alias ’ls’. The alias ’ls’ executes the ’ls.sql’ script.

[localhost] root@db  =# \! grep -E '^\\set ls' ~/.psqlrc
\set ls              '\\i ~/projects/postgesql/psql/ls.sql'

Note

I have all my scripts in a git repo in this directory ~/projects/postgesql/psql/ . Please adjust the ’ls.sql’ to point the find command to your favorite directory.

ls.sql

--------------------------------------------------------------------------------
-- name:    ls.sql
-- purpose: filter psql scripts from within the psql client.
-- author:  Bjarte Brandt
-- date:    01.11.2022
-- usage:   \set f <scriptname>
--          :ls
-- notes:
--   set variable 'f' and thereafter run ':ls'
--   all scripts: '\set f *'
--------------------------------------------------------------------------------

-- set session
\set QUIET yes
-- will turn both header and footer off
\pset tuples_only
\unset QUIET

-- variable concatenation in psql and output to file '_ls.sql'
\set c '\\! find ~/projects/postgesql/psql -maxdepth 1 -type f -name'
\set q '"'
\out _ls.sql
\qecho :c :q*:f*.sql:q
\out
-- now execute the generated '_ls.sql'
\i _ls.sql
-- cleanup
\! /bin/rm _ls.sql

-- restore session
\set QUIET yes
-- will turn both header and footer on
\pset tuples_only
\unset QUIET

Next

I can of course ‘cat’ my scripts from within psql.

[localhost] root@db  =# \set c schemas_by_size
[localhost] root@db  =# :cat
select
  table_schema,
  round(sum(pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)))::decimal / 1024 / 1024, 2) as size_mb
from
  information_schema.tables
group by
  table_schema
order by size_mb desc;

Enjoy!