Интерактивный режим psql

Предусловие: пройдет раздел «Начало работы с psql».

Cпособы подключения

1. Запустите psql в интерактивном режиме, подключившись к базе данных postgres:

   [student@ServerName ~]$ psql postgres

   psql (15.5)
   Type "help" for help.

   Проверьте параметры текущего подключения:

   student@postgres=> \conninfo

   You are connected to database "postgres" as user "student" via socket in "/tmp" at port "5432".

   Пользователь ОС student зарегистрирован в СУБД, одноименной БД не существует, поэтому мы подключились к базе данных postgres.

2. Разрешение на подключение пользователя к базе данных настраивается отдельно. В этой лабораторной работе ограничений на подключения нет.

   Выполните различные варианты подключений из интерактивного режима:

   1. Переключитесь на базу данных template1:

      student@postgres=> \c template1

      You are now connected to database "template1" as user "student".

   2. Смените пользователя на суперпользователя postgres, оставаясь в той же базе:

      student@template1=> \c - postgres

      You are now connected to database "template1" as user "postgres".

   3. Вернитесь к пользователю student и базе postgres:

      postgres@template1=# \c postgres student

      You are now connected to database "postgres" as user "student".

3. Подключитесь через localhost сетевой интерфейс:

   student@postgres=> \c - - localhost

   You are now connected to database "postgres" as user "student" on host "localhost" (address "127.0.0.1") at port "5432".

4. Подключитесь через локальный UNIX-сокет:

   student@postgres=> \c - - /tmp

   You are now connected to database "postgres" as user "student" via socket in "/tmp" at port "5432".

   Здесь локальный UNIX-сокет находится в каталоге /tmp. Расположение настраивается и может быть иным.

Помощь SQL

1. Получите список метакоманд psql:

   student@postgres=> \?

   General
     \copyright             show PostgreSQL usage and distribution terms
     \crosstabview [COLUMNS] execute query and display result in crosstab
     \errverbose            show most recent error message at maximum verbosity
     \g [(OPTIONS)] [FILE]  execute query (and send result to file or |pipe);
                            \g with no arguments is equivalent to a semicolon
     \gdesc                 describe result of query, without executing it
     \gexec                 execute query, then execute each value in its result
     \gset [PREFIX]         execute query and store result in psql variables
     \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
     \q                     quit psql
     \watch [SEC]           execute query every SEC seconds
   
   Help
     \? [commands]          show help on backslash commands
     \? options             show help on psql command-line options
     \? variables           show help on special variables
     \h [NAME]              help on syntax of SQL commands, * for all commands
   
   Query Buffer
     \e [FILE] [LINE]       edit the query buffer (or file) with external editor
     \ef [FUNCNAME [LINE]]  edit function definition with external editor
     \ev [VIEWNAME [LINE]]  edit view definition with external editor
     \p                     show the contents of the query buffer
     \r                     reset (clear) the query buffer
     \s [FILE]              display history or save it to file
     \w FILE                write query buffer to file
   
   Input/Output
     \copy ...              perform SQL COPY with data stream to the client host
     \echo [-n] [STRING]    write string to standard output (-n for no newline)
     \i FILE                execute commands from file
     \ir FILE               as \i, but relative to location of current script
     \o [FILE]              send all query results to file or |pipe
     \qecho [-n] [STRING]   write string to \o output stream (-n for no newline)
     \warn [-n] [STRING]    write string to standard error (-n for no newline)
   ...

   Поскольку вывод не помещается на один экран, автоматически должен запуститься постраничный просмотрщик (это можно отключить или настроить). Для выхода из программы просмотра нажмите q.

2. Метакоманда \h выводит помощь по командам SQL:

   student@postgres=> \h

   Available help:
     ABORT                            CREATE FOREIGN DATA WRAPPER      DROP ROUTINE
     ALTER AGGREGATE                  CREATE FOREIGN TABLE             DROP RULE
     ALTER COLLATION                  CREATE FUNCTION                  DROP SCHEMA
     ALTER CONVERSION                 CREATE GROUP                     DROP SEQUENCE
     ALTER DATABASE                   CREATE INDEX                     DROP SERVER
     ALTER DEFAULT PRIVILEGES         CREATE LANGUAGE                  DROP STATISTICS
   ...

3. Получите помощь по синтаксису команды CREATE VIEW:

   student@postgres=> \h CREATE VIEW

   Command:     CREATE VIEW
   Description: define a new view
   Syntax:
   CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
       [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
       AS query
       [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
   
   URL: https://www.postgresql.org/docs/15/sql-createview.html

Форматирование вывода

1. Получите текущее время:

   • с заголовком:

     student@postgres=> SELECT now();

                 now
     -------------------------------
      2026-03-17 16:27:49.761906+03
     (1 row)

   • и без него:

     student@postgres=> \t

     Tuples only is on.

     student@postgres=> SELECT now();

      2026-03-17 16:28:12.094638+03

2. Для наглядности рассмотрим таблицу pg_am, которая содержит список методов доступа к данным.

   1. У нас уже отключен вывод заголовков. Посмотрите на вывод таблицы:

      student@postgres=> SELECT * FROM pg_am;

          2 | heap   | heap_tableam_handler | t
        403 | btree  | bthandler            | i
        405 | hash   | hashhandler          | i
        783 | gist   | gisthandler          | i
       2742 | gin    | ginhandler           | i
       4000 | spgist | spghandler           | i
       3580 | brin   | brinhandler          | i
       5555 | stir   | stirhandler          | i

   2. Отключите выравнивание:

      student@postgres=> \a

      Output format is unaligned.

   3. Повторите запрос:

      student@postgres=> SELECT * FROM pg_am;

      2|heap|heap_tableam_handler|t
      403|btree|bthandler|i
      405|hash|hashhandler|i
      783|gist|gisthandler|i
      2742|gin|ginhandler|i
      4000|spgist|spghandler|i
      3580|brin|brinhandler|i
      5555|stir|stirhandler|i

   4. Верните стандартные настройки форматирования:

      student@postgres=> \a \t

      Output format is aligned.
      Tuples only is off.

3. Получите первую строку этой таблицы в подробном формате вывода:

   student@postgres=> \x

   Expanded display is on.

   student@postgres=> SELECT * FROM pg_am LIMIT 1;

   -[ RECORD 1 ]-------------------
   oid       | 2
   amname    | heap
   amhandler | heap_tableam_handler
   amtype    | t

   student@postgres=> \x

   Expanded display is off.

   student@postgres=> SELECT * FROM pg_am LIMIT 1 \gx

   -[ RECORD 1 ]-------------------
   oid       | 2
   amname    | heap
   amhandler | heap_tableam_handler
   amtype    | t

4. Включите формат вывода csv и выведите ту же таблицу:

   student@postgres=> \pset format csv

   Output format is csv.

   student@postgres=> SELECT * FROM pg_am LIMIT 1;

   oid,amname,amhandler,amtype
   2,heap,heap_tableam_handler,t

   student@postgres=> SELECT * FROM pg_am LIMIT 1 \gx

   oid,2
   amname,heap
   amhandler,heap_tableam_handler
   amtype,t

5. Установите выровненный формат вывода:

   student@postgres=> \pset format aligned

   Output format is aligned.

   student@postgres=> SELECT * FROM pg_am LIMIT 1 \gx

   -[ RECORD 1 ]-------------------
   oid       | 2
   amname    | heap
   amhandler | heap_tableam_handler
   amtype    | t

Буфер запроса

1. Выведите содержимое буфера запросов:

   student@postgres=> \p

   SELECT * FROM pg_am LIMIT 1

2. Выведите текущее время. Используя команду \watch, повторите команду в буфере несколько раз. Для остановки вывода Ctrl+C:

   student@postgres=> \watch

   Fri 09 May 2025 04:12:18 PM MSK (every 2s)
   
    oid | amname |      amhandler       | amtype
   -----+--------+----------------------+--------
      2 | heap   | heap_tableam_handler | t
   (1 row)
   
     Fri 09 May 2025 04:12:20 PM MSK (every 2s)
   
    oid | amname |      amhandler       | amtype
   -----+--------+----------------------+--------
      2 | heap   | heap_tableam_handler | t
   (1 row)
   
     Fri 09 May 2025 04:12:22 PM MSK (every 2s)
   
    oid | amname |      amhandler       | amtype
   -----+--------+----------------------+--------
      2 | heap   | heap_tableam_handler | t
   (1 row)

Ввод-вывод

1. Отключите вывод заголовков и выравнивание, установите в качестве разделителя пробел. Направьте весь вывод в файл:

   student@postgres=> \a \t \f ' '

    Output format is unaligned.
    Tuples only is on.
    Field separator is " ".

    student@postgres=> \o db_sizes.sql

2. Используя функцию форматирования, защищающую от SQL-вставок, сформируйте текст команд SQL, при выполнении которых будут получены данные о размерах БД:

   student@postgres=> SELECT format('SELECT ''%I:'', pg_size_pretty(pg_database_size(''%I''));', datname, datname) from pg_database;

   student@postgres=> \o

   student@postgres=> \! cat db_sizes.sql

   SELECT 'template1:', pg_size_pretty(pg_database_size('template1'));
   SELECT 'template0:', pg_size_pretty(pg_database_size('template0'));
   SELECT 'postgres:', pg_size_pretty(pg_database_size('postgres'));

3. Выполните скрипт, записанный в файл:

   student@postgres=> \i db_sizes.sql

   template1: 9126 kB
   template0: 9078 kB
   postgres: 9078 kB

4. Выйдите из psql:

   student@postgres=> \q

Переменные

1. Запустите psql с установленной переменной myVar:

   [student@ServerName ~]$$ psql -v myVar=123 postgres

   psql (15.5)
   Type "help" for help.

   student@postgres=> \echo :myVar

   123

2. Запишите в переменную bkndPID идентификатор обслуживающего процесса:

   student@postgres=> select pg_backend_pid() as bkndPID \gset

   student@postgres=> \echo :bkndpid

   35719

3. Получите из pg_stat_activity информацию об этом процессе:

   student@postgres=> SELECT pid, backend_type FROM pg_stat_activity WHERE pid = :bkndpid;

     pid  |  backend_type
   -------+----------------
    35719 | client backend
   (1 row)

Взаимодействие с ОС

1. В сеансе psql получите PID обслуживающего процесса и передайте его команде ОС для получения информации о процессе:

   student@postgres=> SELECT pg_backend_pid() \g (tuples_only=on) | xargs ps -fp

      UID          PID    PPID  C STIME TTY          TIME CMD
   postgres      35719   11935  0 16:16 ?        00:00:00 postgres: student postgres [local] idle

   Здесь использована метакоманда psql \g, которая записывает вывод команды SQL в файл, либо же передает его через конвейер команде ОС. В примере PID передан команде xargs, конструирующей и выполняющей другую команду: ps -fp <PID>.

2. Получите PID первого по порядку серверного процесса, обслуживающего сессию пользователя postgres, в интерактивном режиме команды psql:

   student@postgres=> \! ps -C postgres -o pid,cmd | awk '/student/{print $1}' | head -1

   35719

   В этой команде получены PID и командные строки процессов, в имени которых есть строка postgres, далее средствами потокового редактора awk выделены строки, в которых есть student (это серверные процессы, обслуживающие сессии пользователя student, их может быть несколько) и печатает первое поле — PID, а затем head -1 оставляет лишь первую строку.

   В результате получен PID первого серверного процесса, обслуживающего сессию student.

3. Поместите результат предыдущей команды в переменную psql и выведите информацию об этом процессе средствами СУБД:

   student@postgres=> SELECT pid, backend_type FROM pg_stat_activity WHERE pid = :bkndpid;

     pid  |  backend_type
   -------+----------------
    35719 | client backend
   (1 row)

4. Выйдите из psql:

   student@postgres=> \q

Самопроверка

Вопрос 1

В shell последовательно выполнены следующие команды:

$ sudo su - postgres

postgres$ psql
psql (15.5)
Введите "help", чтобы получить справку.

postgres=# \c - monitoring

К какой базе данных выполнялось подключение в последней команде?

monitoringpostgrestemplate0template1

Вопрос 2

Сразу после инициализации кластера баз данных было выполнено подключение к базе данных postgres с использованием клиента psql. Файлы psqlrc и .psqlrc не создавались.

В открытом сеансе psql была выполнена следующая последовательность команд (вывод команд не приведен):

postgres=# \t
postgres=# \l

Какое количество текстовых строк было отображено в выводе последней команды?

Вопрос 3

В открытом сеансе psql была выполнена следующая последовательность команд (вывод команд не приведен):

postgres=# \t
postgres=# \o query.sql
postgres=# SELECT 'SELECT 1;';
postgres=# \o
postgres=# \i query.sql

Файлы psqlrc и .psqlrc не создавались.

Какой вывод был отображен в консоли psql в результате выполнения последней команды?

SELECT 1;1Вывод в консоли psql отсутствовалОшибка выполнения команды

Вопрос 4

В открытом сеансе psql была выполнена следующая последовательность команд:

postgres=# \t
Режим вывода только кортежей выключен.

postgres=# SELECT 1\gx

Какое количество текстовых строк было отображено в выводе последней команды?