Глава 04. Работа в среде psql

В этой главе:

• Предназначение psql.
• Способы подключения к серверу Получение справки.
• Справка по SQL.
• Автоматизация.
• Настройка.

Предназначение psql

$ psql -U postgres psql (15.5)
Type "help" for help.
postgres=#

• Стандартный клиент в поставке/
• Интерактивная работа в командной строке.
• Выполнение скриптов.
• Главный инструмент администратора.

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

Клиент psql предназначен для администрирования, поэтому он рассчитан на работу исключительно в командной строке. Клиент psql предоставляет средства для редактирования запросов SQL, хранимого кода и т.п., однако эти инструменты основаны на обычных текстовых редакторах, а не графических средствах.

Помимо администрирования клиент psql также необходим для выполнения скриптов.

Для выхода из интерактивного режима psql команда \q . https://www.postgresql.org/docs/15/app-psql.html.

Способы подключения к серверу

• Подключение суперпользователем.
• Опции для подключения.
• Строка подключения.
• Подключение по умолчанию.
• Подключение в сеансе psql.

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

$ sudo -i -u postgres psql psql (15.5)
Type "help" for help.
postgres=#

• Суперпользователь - владелец кластера баз данных.
• По умолчанию - postgres.
• Имеет неограниченные права.
• Исходно - единственный пользователь.

Суперпользователь имеет неограниченные полномочия.

Исходно суперпользователь - единственный пользователь в системе. Работа в сеансе суперпользователя должна выполняться только при наличии обоснованной необходимости тогда, когда привилегий обычных пользователей не хватает.

В примере на слайде для запуска сеанса psql использована команда ОС sudo, позволяющая запустить команду от имени заданного пользователя ОС. В данном случае student - это зарегистрированный в ОС пользователь, которому разрешено использовать команду sudo. В виртуальной машине для этого курса доступ к сеансу суперпользователя от имени пользователя ОС postgres разрешается без пароля (подробнее в главе "Авторизация и аутентификация").

Для дальнейшей работы нам потребуется пользователь student и принадлежащая ему одноименная база данных. В сеансе psql суперпользователя выполните команды:

# CREATE USER student PASSWORD 'student';
CREATE ROLE
# CREATE DATABASE student OWNER student;
CREATE DATABASE

https://www.postgresql.org/docs/15/sql-createuser.html.

https://www.postgresql.org/docs/15/sql-createdatabase.html.

Опции для подключения

$ psql -d student -U student -h localhost -p 5432 psql (15.5)
Type "help" for help.
student=>

• -d - имя базы данных, по умолчанию совпадающее с именем пользователя;
• -U - имя пользователя (точнее - роли), зарегистрированного в кластере баз данных;
• -h - имя хоста для подключения, либо его сетевой адрес, либо имя каталога, в котором находится UNIX сокет;
• -p - TCP порт для подключения, по умолчанию - 5432.

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

Для подключения необходимо иметь соответствующее разрешение в специальном конфигурационном файле pg_hba.conf, о котором будет рассказано в главе "Авторизация и аутентификация".

Строка подключения

$ psql "dbname=student user=student host=localhost" psql (15.5)
Type "help" for help.
student=>

Вместо указания параметров подключения с помощью опций командной строки часто используют строку соединения.

Важнейшие параметры строки соединения:

• dbname - имя базы данных, по умолчанию совпадающее с именем пользователя;
• user - имя пользователя (точнее - роли), зарегистрированного в кластере баз данных;
• host - имя хоста для подключения, либо его сетевой адрес, либо имя каталога, в котором находится UNIX сокет;
• port - TCP порт для подключения, по умолчанию - 5432.

Строка соединения предоставляет даже большие возможности, чем опции командной строки. https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-CONNSTRING.

Подключение по умолчанию

$ psql
psql (15.5)
Type "help" for help. student=> \conninfo
You are connected to database "student" as user "student" via socket in
"/tmp" at port "5432".

• имя базы данных совпадает с именем пользователя;
• имя пользователя по имени пользователя в сеансе ОС;
• подключение через локальный UNIX сокет;
• порт TCP 5432.

Можно вообще не указывать никаких аргументов в командной строке psql. В таком случае подключение выполняется от имени пользователя (роли) в СУБД, взятым по имени пользователя в ОС.

При этом подключение выполняется к одноименной базе данных посредством локального UNIX сокета (не путать с localhost) через порт TCP 5432. Встроенная команда \conninfo клиента psql позволяет узнать, каким образом выполнено подключение.

Любое подключение осуществляется от имени конкретной роли к единственной базе данных.

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

Подключение в сеансе psql

student=> \conninfo
You are connected to database "student" as user "student" via socket in "/tmp" at port "5432".
student=> \c postgres
You are now connected to database "postgres" as user "student". postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres". postgres=# \c student student localhost
You are now connected to database "student" as user "student" on host "localhost" (address "::1") at port "5432".

Если уже запущен клиент psql и необходимо подключиться к другой базе данных, сменить текущего пользователя или же выбрать иной способ подключения и тому подобное, следует использовать встроенную команду \c. При этом предыдущее подключение будет завершено при успешном выполнении нового подключения.

• Первый аргумент \c задает имя базы данных для нового подключения.
• Второй аргумент - имя роли для нового подключения.
• Третий - имя хоста для подключения.
• Далее можно указать номер TCP порта.

Если выполнить команду \c без аргументов, то текущее подключение будет завершено, а взамен его будет открыто новое с такими же параметрами.

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

Получить информацию о текущем подключении можно метакомандой \conninfo.

Получение справки

• Общая помощь.
• Помощь по метакомандам psql.
• Помощь по командам SQL.

Общая помощь

$ psql
psql (15.5)
Введите "help", чтобы получить справку.
student=> help
Вы используете psql - интерфейс командной строки к PostgreSQL. Азы: 
- \copyright - условия распространения;
- \h - справка по операторам SQL;
- \? - справка по командам psql;
- \g или ; в конце строки - выполнение запроса; 
- \q - выход.

В интерактивном режиме psql можно ввести команду help - она выведет общую подсказку. Наиболее важны подсказка по встроенным командам и помощь по SQL.

Помощь по метакомандам psql

student=> \? 
General
\copyright                             show PostgreSQL usage and distribution terms 
...

• ? или ? commands - помощь по метакомандам;
• ? options - помощь по опциям командной строки psql;
• ? variables - помощь по специальным встроенным переменным.

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

В выводе ? все команды psql представлены по категориям:

• General - команды общего назначения;
• Help - справочные команды;
• Query Buffer - команды работы с буфером запроса;
• Input/Output - команды перенаправляющие В\В;
• Informational - метакоманды для просмотра системного каталога;
• Large Objects - команды для работы с «большими объектами»;
• Formatting - команды настройки форматирования результата запроса - команды;
• Connection - управления подключением;
• Operating System - команды взаимодействия с ОС;
• Variables - управление переменными psql.

В зависимости от настроек системы при этом может быть запущена программа постраничного просмотра (less или иной пэйджер). Если это программы постраничного просмотра less (как в виртуальной машине курса) или more - то для выхода из режима постраничного просмотра необходимо нажать команду q (просто нажмите клавишу q). Клиент psql автоматически запускает программу постраничного просмотра и перенаправляет в неё вывод, когда выводимое число строк не помещается на экран.

Программа постраничного просмотра настраивается переменной окружения PSQL_PAGER. Настройку можно сделать в файле .psqlrc или помощью команды \setenv.

Помощь по командам SQL

student=> \h set

Command: SET

Description: change a run-time parameter

Syntax:

SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }

SET [ SESSION | LOCAL ] TIME ZONE { value | 'value' | LOCAL | DEFAULT }

URL: https://www.postgresql.org/docs/15/sql-set.html

Справку по SQL командам можно получить метакомандой \h или же получить справку по какой-либо конкретной sql-команде при помощи \h <sql-command>.

В данной справочной информации находится описание команды, синтаксис и ссылка на соответствующий раздел документации postgresql.org , где представлена более подробная информация об использовании данной sql-команды.

В данном случае важно, чтобы клиент psql соответствовал версии сервера. Иначе, в справке psql можно получить устаревшую или неполную информацию о команде SQL, которая была добавлена или переработана в одной из следующих версий PostgreSQL (и, наоборот, если утилита psql версии новее, чем сервер).

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

По умолчанию psql печатает собственную версию в первой строке при запуске в интерактивном режиме.

Работа с psql

• Форматирование вывода.
• Расширенный вывод.
• Буфер запроса.
• История команд.
• Вывод в файл или поток.
• Многократное выполнение буфера.
• Генерация скрипта.
• Выполнение скрипта из файла.
• Выполнение скрипта в psql.
• Переменные psql.
• Взаимодействие с ОС.
• Переменные окружения.
• Переменные окружения.
• Настройка приглашения.
• Файл ресурсов .psqlrc.

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

student=> select min(g.i), max(g.i), avg(g.i) from generate_series(0,10) as g(i); 

min  | max | avg
-----+-----+--------------------
   0 |  10 | 5.0000000000000000
(1 row)
student=> \a \t
Output format is unaligned.
Tuples only is on.
student=> select min(g.i), max(g.i), avg(g.i) from generate_series(0,10) as g(i); 0|10|5.0000000000000000

• \a - выравнивание;
• \t - вывод только строк;
• \f - разделитель полей;
• \pset - детальная настройка форматирования.

Клиент psql поддерживает различные форматы вывода результатов запроса. Например, можно отключить вывод заголовков - \t, а также отказаться от выравнивания - \a.

Это не совсем удобно для восприятия информации человеком, но необходимо, если результат запроса перенаправляется в другую программу или в файл, для последующей обработки.

Данные метакоманды работают как переключатели. Чтобы вернуть стандартные значения параметров форматирования, следует их выполнить повторно.

Посмотреть на все параметры psql, влияющие на форматирование можно командой \pset. Этой же командой можно устанавливать значение параметров \pset <параметр> <значение>, например \pset null null_value.

Изменение данных параметров форматирования, ровно и как изменение общих параметров psql командой \set, сохраняется только в рамках запущенного экземпляра psql и после перезапуска утилиты psql будут сброшены. Чтобы сделать настройки долговременными, следует поместить соответствующие команды в файл .psqlrc, тогда они будут выполняться при каждом запуске программы.

Расширенный вывод

student=> select min(i), max(i), avg(i) from generate_series(0,10) g(i) \gx 
-[ RECORD 1 ]-----------
min | 0
max | 10
avg | 5.0000000000000000

• \x - переключение в режим расширенного вывода;
• поля строк печатаются по вертикали;
• \gx - вывести в расширенном режиме результаты команды.

Расширенный (expanded) или «вертикальный» формат позволяет печатать поля строк по вертикали. Это удобно, когда в списке выборки запроса фигурирует много столбцов и вывод не помещается на экран по горизонтали.

Для разового использования расширенного вывода с конкретной командой SQL вместо завершения команды символом точка с запятой ; указывают метакоманду \gx . На слайде приводится именно этот пример.

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

student=> \p
select min(g.i), max(g.i), avg(g.i) from generate_series(0,10) as g(i); 
student=> \g
 min | max | avg 
 ----+-----+--------------------
   0 |  10 | 5.0000000000000000
(1 row)
student=> \r
Query buffer reset (cleared).

• \p - вывести содержимое буфера;
• \g - выполнить команду SQL в буфере;
• \r - стереть содержимое буфера;
• \w - записать в файл содержимое буфера.

Последний выполненный запрос сохраняется в специальном буфере psql.

Метакоманда \p позволяет посмотреть (print), что было сохранено в буфере.

Метакоманда \g повторно выполняет (go), сохранённый в буфере запрос.

Метакоманда \r очистить буфер запроса (reset). Для сохранения содержимого буфера в файл: \w <filename>.

История команд

student=> \s 
\set
\? command 
\? options 
\? variables

• \s - вывод истории команд, можно записать в файл;
• сканирование команд в истории посредством стрелок вверх-вниз;
• Ctrl+R - поиск последней выполненной команды по подстроке;
• предоставляется библиотекой readline;
• В Pangolin при выходе из psql история не сохраняется;
• Включить сохранение истории можно параметром psql.save_history.

Клиент psql сохраняет историю выполненных команд. Эта функциональность реализуется библиотекой readline, включенной во множество других программ, например, Bash. Совершенно аналогично Bash, нажатие на клавиши вверх-вниз позволяет просматривать историю выполненных команд, нажатие Ctrl+R позволяет перейти в режим интерактивного поиска выполненной команды по подстроке. Метакоманда \s выводит список выполненных ранее команд.

В Pangolin история команд, выполненных в psql по умолчанию выключена по соображениям безопасности. Для запоминания истории необходимо включить специальный параметр psql.save_history:

# \dconfig+ psql.save_history
                List of configuration parameters
     Parameter     |   Value  | Type |   Context  | Access
-------------------+----------+------+------------+---------------
 psql.save_history | off      | bool | postmaster |
(1 строка)

Включение происходит следующим образом:

# ALTER SYSTEM SET psql.save_history TO on;

Далее сервер СУБД должен быть перезагружен. Другой способ - добавить текстовым редактором строку конфигурации:

psql.save_history = 'on'

В файл postgresql.conf и перезагрузить сервер.

Подробнее о конфигурации в главе 6 "Конфигурация сервера".

Вывод в файл или поток

student=> \o time.txt 
student=> select now(); 
student=> \! cat time.txt 
2024-08-29 20:45:05.651236+03

• \o <файл> - запись в файл;
• \o | <команда ОС> - передача потока вывода команде ОС;
• \o - возвращает вывод на терминал.

Можно записать вывод команды в файл с помощью \o[ut] <файл>. После чего результаты всех запросов будут дописываться в файл. Вернуть вывод в консоль позволяет выполнение метакоманды \o без аргумента.

Также можно перенаправить вывод на вход команде shell \o | <команда>. В psql можно выполнять команды shell метакомандой ! - в примере на слайде выполняется чтение содержимого файла при помощи команды cat.

Многократное выполнение буфера

student=> select now(); 
student=> \watch
^C
student=> \! cat time.txt 
2024-08-29 20:45:05.651236+03 
2024-08-29 20:49:57.28469+03 
2024-08-29 20:50:06.476893+03 
2024-08-29 20:50:08.476894+03 
2024-08-29 20:50:10.476879+03

• \watch - многократно повторяет команду в буфере до тех пор, пока не будет прервана;
• по умолчанию повтор через две секунды.

Записанную в буфер команду можно повторять с заданной периодичностью при помощи метакоманды \watch.

Это может быть актуально для отслеживания хода выполнения сопроводительных операций в представлениях pg_stat_progress_copy, pg_stat_progress_basebackup и тд.

Можно задавать интервал в секундах \watch i[nterval]=1.

Генерация скрипта

student=> \a \t \f ' ' 
Output format is unaligned. 
Tuples only is on.
Field separator is " ". 
student=> \o db_sizes.sql
student=> select format('select ''%I:'', pg_size_pretty(pg_database_size(''%I''));', datname, datname) from pg_database;
student=> \q

• команда select в примере генерирует несколько команд select для получения размеров баз данных;
• вывод в примере направлен в файл.

Можно формировать SQL скрипты, перенаправив вывод psql в файл скрипта. Обращение к файлу будет выполняться от имени того пользователя, от которого был запущен psql.

На слайде вывод функции format() перенаправляется в файл db_sizes.sql. Функция format выдаёт текст, отформатированный в соответствии со строкой формата, подобно функции sprintf в C. Первым аргументом в функцию передаётся сам текст для форматирования, в котором могут содержаться спецпоследовательности(спецификаторы), на места которых будут подставлны значения, передаваемые 2,3,4.. авргументами в функцию. Спецификаторы формата предваряются символом % и имеют форму %[position][flags][width]type, где:

• position - порядковый номер аргумента для подстановки;
• flags - в настоящее время поддерживается только знак минус (-), который выравнивает результат спецификатора по левому краю. Этот флаг работает, только если также определено поле width;
• width - задаёт минимальное число символов, которое будет занимать результат данного спецификатора;
• type(обязательный):
  • %s форматирует значение аргумента как простую строку. Значение NULL представляется пустой строкой.
  • %I обрабатывает значение аргумента как SQL-идентификатор, при необходимости заключая его в кавычки. Значение NULL для такого преобразования считается ошибочным.
  • %L заключает значение аргумента в апострофы, как строку SQL.

Если в форматируемом тексте встречаются апострофы вида «'», их следует экранировать повторным символом.

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

$ 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')); 
$ psql -AtF' ' -f db_sizes.sql
postgres: 9151 kB
student: 9535 kB
template1: 9239 kB
template0: 8945 kB

Записанные в файл команды можно выполнить следующими способами:

• psql < filename;
• psql -f filename.

В примере на слайде используются ключи:

• A (--no-align) , аналогично \a в интерактивном режиме;
• -t (--tuples-only) , аналогично \t в интерактивном режиме;
• -F ' ' (--field-separator=STRING) , аналогично \pset fieldsep ' ' в интерактивном режиме;
• -f (--file=FILENAME) , аналогично \i в интерактивном режиме.

Выполнение скрипта в psql

$ psql
psql (15.5)
Type "help" for help. 
student=> \a \t \f ' ' 
Output format is unaligned. 
Tuples only is on.
Field separator is " ". 
student=> \i db_sizes.sql 
postgres: 9151 kB
student: 9535 kB
template1: 9239 kB 
template0: 8945 kB

В интерактивном режиме команды psql выполнить команды из скритпа SQL позволяет метакоманда \i <файл>.

Чтение файла будет выполняться от имени того пользователя ОС, от которого был запущен psql.

Переменные psql

student=> \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper' 
...
student=> \set myVar Привет! 
student=> \echo :myVar
Привет!

• \set выводит список переменных psql или устанавливает их;
• специальные переменные, например, AUTOCOMMIT влияют на поведение самого psql;
• \echo - вывод на терминал или в поток;
• для извлечения значения переменной перед именем ставят двоеточие.

По аналогии с shell, psql имеет собственные переменные, среди которых есть ряд встроенных (выступающих в качестве параметров psql, определяющих его поведение).

Как и с переменными shell, принято, что системные переменные названы в верхнем регистре (хотя это всего-лишь соглашение). Посмотреть список переменных psql можно командой \set без аргументов.

Создать собсвенную переменную и присвоить ей значение можно \set <var> <value>.

У переменных psql нет возможности задать тип данных, все значения хранятся как текстовые строки.

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

student=> \set osDate `date` 
student=> \echo :osDate
Fri Aug 30 09:28:20 MSK 2024 
student=> \! date
Fri Aug 30 09:28:35 MSK 2024

• Команда в открывающих кавычках команда выполняется в ОС, результат возвращается в psql. -! выполняет команду ОС.

В переменную psql можно записать результат выполнения команды shell, для этого команду shell необходимо написать в открывающих кавычках (апострофах `).

Будет запущена порожденная оболочка (subshell), в которой будет выполнена написанная команда, а результат её выполнения будет записан в переменную psql.

Чтобы посмотреть значение переменной, можно воспользоваться командой \echo.

Для извлечения значения из переменной psql перед ее именем ставят символ двоеточия.

Если результат shell-команды не требуется сохранять, то можно воспользоваться \! <команда> . При этом результат команды будет отправлен на экран.

Переменные окружения

student=> \getenv osUser USER 
student=> \echo :myVar :osUser 
Привет! student

Команда \getenv <переменная> <переменная окружения> считывает в ОС значение переменной окружения и записывает в переменную psql. Многие переменные окружения влияют на поведение самого psql.

Метакоманда \getenv считывает из переменной окружения в ОС значение и записывает его в переменную psql. В примере на слайде из переменной окружения USER было считано имя пользователя в сеансе оболочки операционной системы. Это имя было записано в переменную psql osUser, которое далее было выведено метакомандой \echo.

Переменные окружения

student=> \! echo $PAGER
student=> \setenv PAGER less  
student=> \setenv LESS -XS 
student=> \! echo $PAGER less
student=> \! echo $LESS -XS

• Команда \setenv устанавливает переменную окружения.
• В примере установлены две переменные окружения: PAGER - для указания программы постраничного просмотра и LESS - для передачи опций программе less.
• Удалить переменную окружения - \unset.

Наоборот, назначить значение переменной окружения непосредственно из psql можно метакомандой \setenv. При этом следует понимать, что переменные окружения не являются аналогами глобальных переменных в универсальных языках программирования. Окружение процесса, состоящее в том числе и из переменных, копируется в окружение дочерних процессов и никак иначе.

Поэтому использование \setenv уместно лишь для того чтобы установить переменную окружения, влияющую на сам процесс psql.

Например, можно установить переменную PAGER.

Настройка приглашения

• PROMPT# - приглашение: – 1 - ожидание команды; – 2 - команда не завершена; – 3 - вводе данных из потока ввода;
• %/ - имя текущей БД;
• %R - текущий режим: – = готовность ввода команды; – - продолжение ввода команды; – ' вводится литерал;
• %x - статус транзакции;
• %# - для суперпользователя #, для остальных >.

Приглашением называется текст, который выводится слева от курсора в новой строке.

В psql существует три переменные, определяющие формат приглашения. В данных переменных поддерживаются спецсимволы метаподстановок.

• PROMPT1 — формат приглашения перед началом ввода команды.
• PROMPT2 — формат приглашения во время начатой, но незаконченной команды. Команды можно «растягивать» не несколько строк, тогда psql склеит введённый текст команды от начала, до закрывающего символа точка с запятой.
• PROMPT3 — формат приглашения при вводе строк вручную в команде COPY.

По умолчанию, в приглашении выводится название базы данных, к которой выполнено текущее подключение.

Файл ресурсов .psqlrc

$ cat .psqlrc
\setenv PAGER less
\setenv LESS -XS
\set PROMPT1 %n@%/%R%x%#
\set PROMPT2 %n@%/%R%x%#
$ psql -q
student@student=>\! echo $PAGER less
student@student=>\echo :PROMPT1 %n@%/%R%x%#

• Команды в ~/.psqlrc автоматически выполняются при входе в сеанс psql.
• В примере в файле ресурсов установлен программа постраничного просмотра less и ее опции.
• Также задан вид приглашения командной строки psql, куда слева добавлено имя роли в сеансе.

При запуске psql выполняются два скрипта (при их наличии):

• сначала общий системный скрипт psqlrc;
• затем пользовательский файл .psqlrc.

Пользовательский файл должен располагаться в домашнем каталоге пользователя, запустившего psql, а расположение системного скрипта можно узнать при помощи утилиты pg_config:

pg_config --sysconfdir -> /etc/postgresql-common

По умолчанию оба файла отсутствуют. В эти файлы можно поместить команды для настройки сеанса, например:

• приглашение psql;
• программу постраничного просмотра результатов запросов;
• переменные для хранения текста часто используемых команд.

Обычное применение этих файлов заключается в выполнении SQL команд, которые автоматически должны срабатывать при входе в сеанс. Также часто настраивают значения переменных psql, например, задавая приглашение командной строки.

Итоги

• Стандартный клиент psql в комплекте поставки.
• Позволяет подключаться к БД, указывая параметры подключения либо в командной строке с помощью опций и аргументов команды psql, либо с помощью встроенных команд интерпретатора psql.
• Клиент предоставляет помощь по командам psql и языку SQL.
• Интерактивная оболочка psql предоставляет возможности редактирования и исполнения команд SQL.
• Клиент psql может гибко настраиваться с помощью собственных переменных и переменных окружения.
• Можно использовать файл настройки ресурсов.