Be efficient, use pqsl!

PGConfEu, Lisbon
24/10/2018

Lætitia AVROT
Loxodata

Who

    Lætitia Avrot
  • PostgreSQL consultant and teacher
  • DBA PostgreSQL for more than 10 years
    (and also Oracle and SQL Server)
  • @l_avrot

LOXODATA

Company built on 3 essential pillars

PostgreSQL DevOps Cloud

PgConfEU

    This is PgConfEU, have you tweetted ?

    #PgConfEu
    @PgConfEu

PgConfEU

    This is PgConfEU, have you tweetted yet ?

    #PgConfEu
    @PgConfEu

What



    Psql is different, does it make it bad ?
  • Why ?
  • Usage
  • Other tools

Why ?


    Graphical User Interfaces
  • Easy to use
  • Limited features
  • No recording of your actions and their results
  • No scripting

    When should I ?
  • Occasional querying
  • That's it!

    Why using command-line tools
  • Much more features
  • "Recording"
  • Scripting
  • Slow and more difficult learning

    "Hostile" environments
  • No customization
  • Can't use your own scripts
  • That's called security

    When should I use command line tools ?
  • Always and forever!

Basics


    2 use cases
  • interractive
  • batch
      $psql --host your_host\
    --port your_port\
    --user your_user\
    your_database
    Host
  • socket connection (unix_socket_directories)
  • network connection
  • $PGHOST
      $psql --host your_host\
    --port your_port\
    --user your_user\
    your_database
    Port
  • $PGPORT
  • 5432
$psql "service=myservice"
    Connection - Service file
  • ~/.pgservice.conf
  • $PGSERVICEFILE
    Connection - Service file 
    [myservice]
dbname=mydb
user=myuser
host=myhost
    $psql "service=myservice"
    Connection - Connection string 
    postgresql://[user[:password]@][netloc][:port]
[,...][/dbname][?param1=value1&...]
    psql postgresql://myuser@127.0.0.1:5432/mydb
    Psql's default prompt 
          laetitia@:~$ psql log
Expanded display is used automatically.
psql (11.0 (Ubuntu 11.0-1.pgdg18.04+2))
Type "help" for help.

mydb=>
  • database name
  • "helping" character
  • the symbol '>'
    "Helping" character 
    diff=> '
diff'> '
diff-> (
diff(> )
  • =: waiting for a new order
  • -: the previous command wasn't terminated
  • (: there is an unmatched left parenthesis
  • ': there is an unfinished quoted string
  • ": there is an unfinished quoted identifier
    Exiting
  • \q
  • exit or quit (postgres 11)
    Getting some help
  • man
  • psql help command (\h and \?)
  • PostgreSQL documentation (32 pages)

https://www.postgresql.org/docs/current/
static/app-psql.html

https://www.postgresql.org/community/

Use


    Setting
  • From console prompt
  • In a .psqlrc file
    Setting
  • \set : sets psql variables
  • \pset : sets output variables
  • \setenv : sets environment variables
    Setting variables 
log=> \set table_name pg_class
log=> select relname from :table_name;
    Displaying query output 
 select * from pg_class limit 1;

            relname       | relnamespace | reltype | reloftype | relowner |
relam | relfilenode | reltablespace | relpages | reltuples | relallvisible |
reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts
| relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers |
relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated |
relreplident | relispartition | relfrozenxid | relminmxid | relacl | reloptions
| relpartbound
--------------------+--------------+---------+-----------+----------+-------+
-------------+---------------+----------+-----------+---------------+---------------+
-------------+-------------+----------------+---------+----------+-----------+
------------+------------+-------------+----------------+----------------+
----------------+---------------------+----------------+--------------+----------------+
--------------+------------+--------+------------+--------------
 line_id_seq |         2200 |   16652 |         0 |    16384 |     0 |
16651 |             0 |        1 |         1 |             0 |             0 | f
| f           | p              | S       |        3 |         0 | f          | f
| f           | f              | f              | f              | f
| t              | n            | f              |            0 |          0 |
|            |
(1 row)
    Displaying query output
  • \x to set expanded table formatting mode
  • combined with \g to replay the last query 
    \gx

-[ RECORD 1 ]-------+-------------------
relname             | line_id_seq
relnamespace        | 2200
reltype             | 16652
reloftype           | 0
relowner            | 16384
relam               | 0
relfilenode         | 16651
reltablespace       | 0
relpages            | 1
reltuples           | 1
relallvisible       | 0
reltoastrelid       | 0
relhasindex         | f
relisshared         | f
relpersistence      | p
relkind             | S
relnatts            | 3
relchecks           | 0
relhasoids          | f
relhaspkey          | f
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | n
relispartition      | f
relfrozenxid        | 0
relminmxid          | 0
relacl              |
reloptions          |
relpartbound        |
    Formatting display 
    \pset format

  • html
  • latex
  • asciidoc

    Input/Output
  • \o for output
  • \i for input
    Input/Output
  • \o [FILENAME]
    • log=> \o /tmp/output.out
log=> \o
  • \i [FILENAME]
    • log=> \i sql/my_awesome_query.sql

    Buffer management
  • \e
  • \g
    Buffer management 
    log=> \e sql/my_awesome_query.sql
log=> \e sql/my_awesome_query.sql 30
    log=> \ef my_awesome_function
log=> \ef my_awesome_function 24
    log=> \ev my_awesome_view
log=> \ev my_awesome_view 56
    log=> \g
  • \e [FILE [ROW]]
  • \ef [FUNCTION [ROW]]
  • \ev [VIEW [ROW]]
  • \g

    Cool stuffs
  • autocompletion
  • Navigation through commands history
  • Search in commands history
    Cool stuffs
  • Autocompletion with tab
    Cool stuffs
  • navigation through history like in shell
    (Up and down arrows, CTRL+r)
    Cool stuffs
  • shell commands (\!)
    • log=> \! mkdir /tmp/out
  • Disabling pager
    • \pset pager off

Explore a database


    Informations
  • \conninfo
  • \l
  • \c
    Informations
  • \conninfo
    • log=> \conninfo
You are connected to database "log" as user "laetitia"
via socket in "/var/run/postgresql" at port "5432".
    Informations
  • \l[+] [PATTERN]
    • log=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 log       | laetitia | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
           |          |          |             |             | postgres=CTc/postgres
(4 rows) 
    log=> \l+ log
                            List of databases
 Name |  Owner   | Encoding |  |  |  | Size   | Tablespace | Description
------+----------+----------+--+--+--+---------+------------+-------------
 log  | laetitia | UTF8     |  |  |  | 5773 MB | pg_default |
(1 row)
    Informations
  • \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-]}
    • mydb=> \c postgres
You are now connected to database "postgres" as user
"laetitia".
    postgres=> \c mydb laetitia /var/run/postgresql 5432
You are now connected to database "mydb" as user
"laetitia".

    Objects
  • \dn
  • \dt
  • \d [OBJET]
    Objects
  • \dn[S+] [PATTERN]
    •  List of schemas
       Name        |  Owner   |  Access privileges   |      Description
-------------------+----------+----------------------+------------------------
 my_awesome_schema | laetitia |                      |
 public            | postgres | postgres=UC/postgres+| standard public schema
                   |          | =UC/postgres         |
(2 rows)
    Objects
  • \dt[S+] [PATTERN]
    • log=> \dtS pg_att*
              List of relations
   Schema   |     Name     | Type  |  Owner
------------+--------------+-------+----------
 pg_catalog | pg_attrdef   | table | postgres
 pg_catalog | pg_attribute | table | postgres
(2 rows)
    log=> \dtS+ pg_class
                        List of relations
   Schema   |   Name   | Type  |  Owner   |  Size  | Description
------------+----------+-------+----------+--------+-------------
 pg_catalog | pg_class | table | postgres | 120 kB |
(1 row)

    Objects
  • \d [OBJET]
    • log=> \d+ pg_available_extensions
                     View "pg_catalog.pg_available_extensions"
      Column       | Type | Collation | Nullable | Default | Storage  | Description
-------------------+------+-----------+----------+---------+----------+-------------
 name              | name |           |          |         | plain    |
 default_version   | text |           |          |         | extended |
 installed_version | text |           |          |         | extended |
 comment           | text |           |          |         | extended |
View definition:
 SELECT e.name,
    e.default_version,
    x.extversion AS installed_version,
    e.comment
   FROM pg_available_extensions() e(name, default_version, comment)
     LEFT JOIN pg_extension x ON e.name = x.extname;

    Objects
  • \d [OBJET]
    • log=> \d+ example
                          Sequence "public.example"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      | 1

    Objects
  • \d [OBJET]
    • log=> \d pg_cast
              Table "pg_catalog.pg_cast"
   Column    |  Type  | Collation | Nullable | Default
-------------+--------+-----------+----------+---------
 castsource  | oid    |           | not null |
 casttarget  | oid    |           | not null |
 castfunc    | oid    |           | not null |
 castcontext | "char" |           | not null |
 castmethod  | "char" |           | not null |
Indexes:
    "pg_cast_oid_index" UNIQUE, btree (oid)
    "pg_cast_source_target_index" UNIQUE, btree (castsource, casttarget)
    Learning by querying
  • \set ECHO_HIDDEN on
    • employees=# \set ECHO_HIDDEN on
employees=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN
'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

              List of relations
  Schema   |     Name     | Type  |  Owner
-----------+--------------+-------+----------
 employees | departments  | table | postgres
 employees | dept_emp     | table | postgres
 employees | dept_manager | table | postgres
 employees | employees    | table | postgres
 employees | salaries     | table | postgres
 employees | titles       | table | postgres
(6 rows)

    Superhero tips and tricks
  • \gexec
  • \gdesc (v11)
  • \timing on|off
  • \watch
    Superhero tips and tricks
  • \gexec
    • log=> select 'alter table ' || pg_class.relname || ' owner to laetitia'
log-> from pg_class
log-> inner join pg_namespace
log-> on pg_class.relnamespace = pg_namespace.oid
log-> where pg_namespace.nspname='public'
log-> and pg_class.relkind='r';
                ?column?
-----------------------------------------
 alter table table1 owner to laetitia
 alter table table2 owner to laetitia
 alter table table3 owner to laetitia
(3 rows)

log=> \gexec
ALTER TABLE
ALTER TABLE
ALTER TABLE
    Superhero tips and tricks
  • \gdesc (v11)
    • log=> select relname from pg_class limit 1;
 relname
---------
 example
(1 row)

log=> \gdesc
 Column  | Type
---------+------
 relname | name
(1 row)
    Superhero tips and tricks
  • \timing on|off
    • log=> \timing on
Timing is on.
log=> select pg_class.relname
log-> from pg_class
log-> inner join pg_namespace
log-> on pg_class.relnamespace = pg_namespace.oid
log-> where pg_namespace.nspname='public'
log-> and pg_class.relkind='r';

  relname
-----------
table1
table2
table3
(3 rows)

Time: 3,137 ms
    Superhero tips and tricks
  • \watch [SEC]
    • log=> select pid from pg_stat_activity ;
 pid
------
 1162
 1164
 7738
 1160
 1159
 1161
(6 rows)

log=> \watch 10
mar. 23 oct. 2018 09:15:35 CEST (every 10s)

 pid
------
 1162
 1164
 7738
 1160
 1159
 1161
(6 rows)

Other tools


    Vipsql

Conclusion


Be carefull with quicker, easier and more seductive tools!

2018.pgconf.eu/feedback