Практикум

Опции командной строки psql

Проверьте путь к исполняемому файлу psql:
```
$ which psql /usr/bin/psql
$ ls -l $(which psql)
lrwxrwxrwx 1 root root 22 авг 22 15:03 /usr/bin/psql -> /etc/alternatives/psql
$ file /etc/alternatives/psql /etc/alternatives/psql: symbolic link to /usr/pangolin-dbms-client-6.2.0/bin/psql
```
Команда ОС which находит в каталогах, перечисленных в переменной окружения PATH, путь к заданному аргументом исполняемому файлу (здесь - psql), если он имеется. Если таких файлов имеется несколько в разных каталогах, выводится путь к первому из них. Однако в этом примере команда which обнаружила не сам исполняемый файл, а символическую ссылку на него. Команда file информирует об этом и выдает путь к собственно исполняемому файлу.

Определите, из какого пакета ПО установлен исполняемый файл psql и получите информацию об этом пакете:

$ rpm -qif /usr/pangolin-dbms-client-6.2.0/bin/psql
Name
Version
Release
Architecture: x86_64
Install Date: Чт 22 авг 2024 15:03:31
: pangolin-dbms-6.2-client
: 6.2.0
: redos7.3.2
Group
Size
License
Signature
Source RPM  : pangolin-dbms-6.2-6.2.0-redos7.3.2.src.rpm
Build Date  : Сб 27 апр 2024 14:01:52
: Applications/Databases
: 12268029
: PostgreSQL
: (none)
Build Host  : localhost
Relocations : /usr/pangolin-dbms-client-6.2.0
URL         : https://sbertech.ru
Summary     : Pangolin DBMS client programs
Description :
Platform V Pangolin is an advanced Object-Relational database management system
(DBMS).
The pangolin-dbms-client package contains the client programs that you will need
to
access a Pangolin DBMS server. These client programs can be located on the same
machine as the
Pangolin server, or on a remote machine that accesses a Pangolin server
over a network connection.  The Pangolin server can be found in the
platform-v-pangolin-server sub-package.

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

$ psql --version
psql (PostgreSQL) 15.5
$ psql --help | tail -2
Об ошибках сообщайте по адресу <postgresql_sbt@sberbank.ru>. Домашняя страница PostgreSQL: <https://www.postgresql.org/>

Используя опцию -c команды psql получите информацию о версии сервера:

$ psql -c 'select version()' --------------------------------------------------------------------------------------------------------
PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-6), 64-bit (1 строка)
$ psql -c 'select product_version()' product_version
---------------------------
Platform V Pangolin 6.2.0
(1 строка)

Клиент psql может выполнять скрипты на SQL. Файл скрипта можно указать после опции -f или же содержимое скрипта можно передать через stdin:
```
$ echo 'SELECT sber_version();' | psql sber_version
---------------------------
Platform V Pangolin 6.2.0
(1 строка)
```
Если вместо имени скрипта после опции -f указано тире, то ввод команд SQL также осуществляется из stdin:
```
$ echo 'SELECT now();' | psql -f - now
------------------------------
2024-09-11 19:31:46.53685+03
(1 строка)
```

Клиент psql может вести собственный журнал команд, задаваемый с помощью опции -L:

$ psql -L psql.log -c 'select session_user' session_user
--------------
student
(1 строка)
$ cat psql.log
********* ЗАПРОС ********* select session_user **************************
session_user
--------------
student
(1 строка)

Команда ОС cat вывела в stdout содержимое файла журнала.

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

Клиент psql подключается к базе данных от имени зарегистрированного в СУБД пользователя. По умолчанию имя пользователя берется из ОС, а подключение идет к БД с таким же именем. Если пользователь или БД не существуют - подключение не состоится:

$ id
uid=1000(student) gid=1000(student) группы=1000(student),10(wheel)
$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+-----------+------------+------------+------------+------------------+-----------------------
postgres | student | template0 |
| template1 | |
(5 rows)
postgres student postgres
postgres
| UTF8 | UTF8 | UTF8 |
| UTF8 |
| en_US.UTF8 | en_US.UTF8 | | en_US.UTF8 | en_US.UTF8 | | en_US.UTF8 | en_US.UTF8 | | | | | en_US.UTF8 | en_US.UTF8 |
| libc |
| libc |
$ psql
psql (15.5)
Введите "help", чтобы получить справку.
| |
| |
student@student=> \conninfo
You are connected to database "student" as user "student" via socket in "/tmp" at port "5432".

Пользователь ОС student зарегистрирован в СУБД, одноименная БД существует.

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

student@student=> \?
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
describe result of query, without executing it
execute query, then execute each value in its result
execute query and store result in psql variables
\gdesc
\gexec
\gset [PREFIX]
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q
\watch [SEC]
Help
\? [commands]
\? options
\? variables
\h [NAME]
Query Buffer
\e [FILE] [LINE]
\ef [FUNCNAME [LINE]]  edit function definition with external editor
\ev [VIEWNAME [LINE]]  edit view definition with external editor
\p
\r
\s [FILE]
\w FILE
Input/Output
\copy ...
:
show the contents of the query buffer
reset (clear) the query buffer
display history or save it to file
write query buffer to file
perform SQL COPY with data stream to the client host

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

Если требуется выйти из интерактивного режима psql - нажмите \q (метакоманда выхода).

Разрешение на подключение пользователя к базе данных настраивается отдельно. В этой лабораторной работе ограничений на подключения нет. Выполните различные варианты подключений из интерактивного режима:
```
student@student=> \c postgres
You are now connected to database "postgres" as user "student".
student@postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres@postgres=# \c - student
You are now connected to database "postgres" as user "student".
student@postgres=> \c student
You are now connected to database "student" as user "student".
```

Подключение через loopback сетевой интерфейс:

student@student=> \c - - localhost
You are now connected to database "student" as user "student" on host "localhost" (address "::1") at port "5432".

Подключение через локальный UNIX сокет:
```
student@student=> \c - - /tmp
You are now connected to database "student" as user "student" via socket in "/tmp" at port "5432".
```
Здесь локальный UNIX сокет находится в каталоге /tmp. Расположение настраивается и может быть иным.

Помощь SQL

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

student@student=> \h Available help:
:
ABORT ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER
AGGREGATE COLLATION CONVERSION DATABASE
DEFAULT PRIVILEGES DOMAIN
EVENT TRIGGER EXTENSION
FOREIGN DATA WRAPPER FOREIGN TABLE FUNCTION
GROUP
INDEX
LANGUAGE
LARGE OBJECT MATERIALIZED VIEW OPERATOR
OPERATOR CLASS OPERATOR FAMILY POLICY PROCEDURE PUBLICATION ROLE
ROUTINE RULE SCHEMA SEQUENCE SERVER STATISTICS
CLOSE CLUSTER COMMENT COMMIT COMMIT COPY CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE
CREATE VIEW DEALLOCATE DECLARE DELETE DISCARD
DO
DROP ACCESS METHOD
DROP AGGREGATE
DROP CAST
DROP COLLATION
DROP CONVERSION
DROP DATABASE
DROP DOMAIN
DROP EVENT TRIGGER
DROP EXTENSION
DROP FOREIGN DATA WRAPPER DROP FOREIGN TABLE
DROP FUNCTION
DROP GROUP
DROP INDEX
DROP LANGUAGE
DROP MATERIALIZED VIEW DROP OPERATOR
DROP OPERATOR CLASS
DROP OPERATOR FAMILY
DROP OWNED
DROP POLICY
DROP PROCEDURE
DROP PUBLICATION
DROP ROLE
DROP USER MAPPING DROP VIEW
END
EXECUTE
EXPLAIN
FETCH
GRANT
IMPORT FOREIGN SCHEMA INSERT
LISTEN
LOAD
LOCK
MERGE
MOVE
NOTIFY
PREPARE
PREPARE TRANSACTION REASSIGN OWNED
REFRESH MATERIALIZED VIEW REINDEX
RELEASE SAVEPOINT RESET
REVOKE
ROLLBACK
ROLLBACK PREPARED ROLLBACK TO SAVEPOINT SAVEPOINT
SECURITY LABEL
SELECT SELECT INTO

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

student@student=> \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

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

Получите текущее время с заголовком и без него:

student@student=> select now(); now ------------------------------- 2024-09-11 20:30:34.727583+03
(1 row)
student@student=> \t Tuples only is on.
student@student=> select now(); 2024-09-11 20:30:40.355486+03

Выведите содержимое таблицы pg_am (содержит список методов доступа к данным) без заголовка, а затем выключите выравнивание и выведите таблицу снова. Затем включите вывод заголовков и выравнивание:

student@student=> select * from pg_am;
2 | heap
403 | btree
405 | hash
783 | gist
2742 | gin
4000 | spgist
3580 | brin
| heap_tableam_handler |t | bthandler |i | hashhandler |i | gisthandler |i | ginhandler |i | spghandler |i | brinhandler |i
student@student=> \a
Output format is unaligned.
student@student=> 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
student@student=> \a \t Output format is aligned. Tuples only is off.

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

student@student=> \x Expanded display is on.
student@student=> select * from pg_am limit 1; -[ RECORD 1 ]-------------------
oid | 2
amname | heap
amhandler | heap_tableam_handler
amtype | t
student@student=> \x Expanded display is off.
student@student=> select * from pg_am limit 1 \gx -[ RECORD 1 ]-------------------
oid |2
amname | heap
amhandler | heap_tableam_handler amtype |t

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

student@student=> \pset format csv Формат вывода: csv.
student@student=> select * from pg_am limit 1; oid,amname,amhandler,amtype 2,heap,heap_tableam_handler,t
student@student=> select * from pg_am limit 1 \gx oid,2
amname,heap
amhandler,heap_tableam_handler
amtype,t

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

student@student=> \pset format aligned Формат вывода: aligned.
student@student=> select * from pg_am limit 1 \gx -[ RECORD 1 ]-------------------
oid |2
amname | heap
amhandler | heap_tableam_handler amtype |t

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

Выведите содержимое буфера запросов:
```
student@student=> \p
select * from pg_am limit 1
```

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

student@student=> \watch
Ср 11 сен 2024 20:55:11 (обновление: 2 с)
now
-------------------------------
2024-09-11 20:55:11.801956+03
(1 строка)
Ср 11 сен 2024 20:55:13 (обновление: 2 с)
now
-----------------------------
2024-09-11 20:55:13.8019+03
(1 строка)
Ср 11 сен 2024 20:55:15 (обновление: 2 с)
now
-------------------------------
2024-09-11 20:55:15.801885+03
(1 строка)
Ср 11 сен 2024 20:55:17 (обновление: 2 с)
now
-------------------------------
2024-09-11 20:55:17.801996+03
(1 строка)

Ввод-вывод

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

student@student=> \a \t \f ' '
Формат вывода: unaligned.
Режим вывода только кортежей включён. Разделитель полей: " ".
student@student=> \o db_sizes.sql

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

student@student=> select format('select ''%I:'', pg_size_pretty(pg_database_size(''%I''));', datname, datname) from pg_database;
student@student=> \o
student@student=> \! cat db_sizes.sql
select 'postgres:', pg_size_pretty(pg_database_size('postgres')); select 'student:', pg_size_pretty(pg_database_size('student')); select 'template1:', pg_size_pretty(pg_database_size('template1')); select 'template0:', pg_size_pretty(pg_database_size('template0'));

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

student@student=> \i db_sizes.sql postgres: 9151 kB
student: 9535 kB
template1: 9033 kB
template0: 8945 kB

Переменные

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

$ psql -v myVar=123
psql (15.5)
Введите "help", чтобы получить справку.
student@student=> \echo :myVar 123

Запишите в переменную bkndPID идентификатор процесса обслуживающего процесса:
```
student@student=> select pg_backend_pid() as bkndPID \gset
student@student=> \echo :bkndPID 3160
```

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

student=> select pid, backend_type from pg_stat_activity where pid = :bkndpid; pid | backend_type
------+----------------
3256 | client backend
(1 строка)

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

В сеансе psql получите PID обслуживающего процесса и передайте его команде ОС для получения информации о процессе:
```
student@student=> select pg_backend_pid() \g (tuples_only=on) | xargs ps -fp UID PID PPID C STIME TTY TIME CMD
postgres 3331 819 0 22:01 ? 00:00:00 postgres: student student [local] idle
```
Здесь использована метакоманда psql \g, которая записывает вывод команды SQL в файл, либо же передает его через конвейер команде ОС. В примере PID передан команде xargs, конструирующей и выполняющей другую команду: ps -fp <PID>.
Получите PID первого по порядку серверного процесса, обслуживающего сессию пользователя student, в интерактивном режиме команды psql:
```
student=> \! ps -C postgres -o pid,cmd | awk '/student/{print $1}' | head -1 12155
```
В этой команде получены PID и командные строки процессов, в имени которых есть строка postgres, далее средствами потокового редактора awk выделены строки, в которых есть student (это серверные процессы, обслуживающие сессии пользователя student, их может быть несколько) и печатает первое поле - PID, а затем head -1 оставляет лишь первую строку.

В результате получен PID первого серверного процесса, обслуживающего сессию student.
Поместите результат предыдущей команды в переменную psql и выведите информацию об этом процессе средствами СУБД:
```
student=> select pid, backend_type from pg_stat_activity where pid = :bkndPID; pid | backend_type
-------+----------------
12155 | client backend
(1 строка)
```

Опции командной строки psql​

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

Помощь SQL​

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

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

Ввод-вывод​

Переменные​

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