Sysadminium
Для управления сервером баз данных PostgreSQL есть много разных инструментов, но при установки сервера по умолчанию устанавливается только утилита psql. Это консольная утилита с помощью которой можно подключится к серверу баз данных и начать с ним работать.
Подключение к серверу баз данных
Итак, утилита psql позволяет вам управлять сервером баз данных PostgreSQL.
Официальную документацию по этой утилите вы можете найти тут и тут! А в этой статье даны некоторые практические примеры для её использования.
Но вначале нужно подключиться к серверу. Подключиться с помощью этой утилиты вы можете к локально установленному серверу, или удалённому используя сеть. Подключение выполняется таким способом:
По умолчанию при подключении вы используете:
- в качестве имени базы и роли — имя пользователя ОС;
- в качестве адреса сервера — локальный сокет, который находится в каталоге /tmp/ и порт 5432.
Таким образом если вы в системе находитесь под пользователем postgres, то следующие команды будут равнозначными:
$ psql $ psql -d postgres -U postgres -h /tmp/ -p 5432
Для получения информации о узле к которому вы подключены используется команда \conninfo:
postgres@s-pg13:~$ psql psql (13.3) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432". postgres=# \q postgres@s-pg13:~$ psql -d postgres -U postgres -h /tmp -p 5432 psql (13.3) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432". postgres=# \q
Не выходя из терминала psql вы можете подключится к другому узлу с помощью команды \c .
Все команды psql начинаются с символа обратного слеша «\». Но помимо этих команд можно выполнять запросы SQL, для них обратный слеш не нужен, например SELECT.
Чтобы выйти из терминала psql можно использовать 2 равносильные команды \q и exit .
Получение справочной информации
Получить справку о psql из ОС можно следующими способами:
Вы можете получить справку находясь в терминале psql такими способами:
- \? — список команд psql
- \? variables — переменные psql
- \h — список команд SQL
- \h — синтаксис определённой команды SQL
Файлы, которые использует psql
.psqlrc
При входе в psql будут выполнятся команды записанные в ~/.psqlrc. Этого файла может и не быть, но если он будет создан в домашнем каталоге пользователя, то psql будет его читать при каждом подключении к серверу.
Вот некоторые примеры настроек, которые можно ввести в ~/.psqlrc:
- \setenv PAGER ‘less -XS’ — результат запроса, будет попадать в утилиту less, при этом листать можно будет как вправо (если не влезает по ширине) так и вниз (если не влезает по высоте);
- \timing on — после запроса показывать время его выполнения;
- \set PROMPT1 ‘%n@%/%R%# ‘ — приглашение вода команды, когда psql ждет новую команду;
- \set PROMPT2 ‘%n@%/%R%# ‘ — приглашение вода команды, когда psql ждет дополнительный ввод;
- \set HISTSIZE 2000 — история команд будет хранить 2000 строк.
Настраивая приглашение PROMPT1 и PROMPT2 используются следующие символы:
- %n — имя пользователя;
- %/ — имя базы;
- %R — заменяет символ в зависимости от режима ввода;
- # — # если суперпользователь,
- > — если обычный пользователь.
В качестве практики давайте настроим свой .psqlrc:
postgres@s-pg13:~$ cat > .psqlrc > \setenv PAGER 'less -XS' > \timing on > \set PROMPT1 '%n@%/%R%# ' > \set PROMPT2 '%n@%/%R%# ' > \set HISTSIZE 2000 > EOT postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# CREATE DATABASE test_database; CREATE DATABASE Time: 62,978 ms postgres@postgres=# \c test_database You are now connected to database "test_database" as user "postgres". postgres@test_database=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# DROP DATABASE test_database; DROP DATABASE Time: 9,911 ms
Как видим выше, после каждого запроса выводится время его выполнения. Также в приглашении ввода команды видно имя пользователя и имя базы.
.psql_history
Другой полезный файл это ~/.psql_history. В нем хранится история команд введенных в терминале psql. Перемещаться по истории команд в терминале psql можно клавишами вверх и вниз. Количество хранимых команд изменяется установкой переменной HISTSIZE.
Формат выводимой информации
Когда вы вводите и выполняете какой-нибудь запрос в терминале psql, то в выводе видите результат этого запроса. По умолчанию такой вывод показывается в форме таблички. Но вы можете настроить формат выводимой информации сами:
- \a — с выравниванием / без выравнивания
- \t — отображение строки заголовка и итоговой строки / без такого отображения
- \pset fieldsep ‘ ‘ — можно задать разделитель (по умолчанию используется вертикальная черта ‘|’)
- \x — расширенный режим, когда нужно вывести много столбцов одной таблицы, они будут выведены в один столбец
Например, получим информацию из представления pg_tables. Затем поменяем формат и снова получим информацию. А затем все вернем на место:
postgres@s-pg13:~$ psql psql (13.3) Type "help" for help. postgres=# SELECT * FROM pg_tables LIMIT 5; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+-----------------------+------------+------------+------------+----------+-------------+------------- pg_catalog | pg_statistic | postgres | | t | f | f | f pg_catalog | pg_type | postgres | | t | f | f | f pg_catalog | pg_foreign_table | postgres | | t | f | f | f pg_catalog | pg_authid | postgres | pg_global | t | f | f | f pg_catalog | pg_statistic_ext_data | postgres | | t | f | f | f (5 rows) postgres=# \t \a \pset fieldsep ' ' Tuples only is on. Output format is unaligned. Field separator is " ". postgres=# SELECT * FROM pg_tables LIMIT 5; pg_catalog pg_statistic postgres t f f f pg_catalog pg_type postgres t f f f pg_catalog pg_foreign_table postgres t f f f pg_catalog pg_authid postgres pg_global t f f f pg_catalog pg_statistic_ext_data postgres t f f f postgres=# \t \a \pset fieldsep '|' Tuples only is off. Output format is aligned. Field separator is "|". postgres=# SELECT * FROM pg_tables LIMIT 5; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+-----------------------+------------+------------+------------+----------+-------------+------------- pg_catalog | pg_statistic | postgres | | t | f | f | f pg_catalog | pg_type | postgres | | t | f | f | f pg_catalog | pg_foreign_table | postgres | | t | f | f | f pg_catalog | pg_authid | postgres | pg_global | t | f | f | f pg_catalog | pg_statistic_ext_data | postgres | | t | f | f | f (5 rows)
А вот пример использования расширенного режима (\x). Выглядит это так, как будто табличку перевернули:
postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_tables LIMIT 5; -[ RECORD 1 ]---------------------- schemaname | pg_catalog tablename | pg_statistic tableowner | postgres tablespace | hasindexes | t hasrules | f hastriggers | f rowsecurity | f -[ RECORD 2 ]---------------------- schemaname | pg_catalog tablename | pg_type tableowner | postgres tablespace | hasindexes | t hasrules | f hastriggers | f rowsecurity | f -[ RECORD 3 ]---------------------- schemaname | pg_catalog tablename | pg_foreign_table tableowner | postgres tablespace | hasindexes | t hasrules | f hastriggers | f rowsecurity | f -[ RECORD 4 ]---------------------- schemaname | pg_catalog tablename | pg_authid tableowner | postgres tablespace | pg_global hasindexes | t hasrules | f hastriggers | f rowsecurity | f -[ RECORD 5 ]---------------------- schemaname | pg_catalog tablename | pg_statistic_ext_data tableowner | postgres tablespace | hasindexes | t hasrules | f hastriggers | f rowsecurity | f
Взаимодействие psql с операционной системой
Терминал psql умеет выполнять команды операционной системы. Для этого нужно использовать команду «\!«. Например так:
Можно установить переменную окружения в систему с помощью команды \setenv:
postgres=# \setenv TEST Hello postgres=# \! echo $TEST Hello
А для того чтобы перевести вывод команд в файл нужно использовать ‘\o имя_файла’. И чтобы вернуть всё обратно используем «\o» без имени файла. Например:
postgres=# \o dba.log postgres=# SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5; postgres=# \! cat dba.log -[ RECORD 1 ]--------------------- schemaname | pg_catalog tablename | pg_statistic tableowner | postgres -[ RECORD 2 ]--------------------- schemaname | pg_catalog tablename | pg_type tableowner | postgres -[ RECORD 3 ]--------------------- schemaname | pg_catalog tablename | pg_foreign_table tableowner | postgres -[ RECORD 4 ]--------------------- schemaname | pg_catalog tablename | pg_authid tableowner | postgres -[ RECORD 5 ]--------------------- schemaname | pg_catalog tablename | pg_statistic_ext_data tableowner | postgres postgres=# \o postgres=# \x Expanded display is off.
В предыдущем листинге с помощью последней команды мы выключили расширенный режим.
Помимо вывода в файл psql умеет выполнять команды из файла. Это делается с помощью команды «\i имя файла». Вот пример:
postgres=# \q postgres@s-pg13:~$ cat > dba1.log > SELECT 'pg_statistic: '|| count(*) FROM pg_statistic; > SELECT 'pg_type: '|| count(*) FROM pg_type; > SELECT 'pg_foreign_table: '|| count(*) FROM pg_foreign_table; > EOT postgres@s-pg13:~$ psql psql (13.3) Type "help" for help. postgres=# \! cat dba1.log SELECT 'pg_statistic: '|| count(*) FROM pg_statistic; SELECT 'pg_type: '|| count(*) FROM pg_type; SELECT 'pg_foreign_table: '|| count(*) FROM pg_foreign_table; postgres=# \a \t \pset fieldsep ' ' Output format is unaligned. Tuples only is on. Field separator is " ". postgres=# \i dba1.log pg_statistic: 402 pg_type: 411 pg_foreign_table: 0 postgres=# \a \t \pset fieldsep '|' Output format is aligned. Tuples only is off. Field separator is "|".
В примере выше мы проделали следующее:
- вышли из psql;
- создали скрипт dba1.log, который подсчитывает количество строк из:
- pg_statistic — статистическая информация о содержимом базы данных;
- pg_type — информация о типах данных;
- pg_foreign_table — дополнительная информация о сторонних таблицах.
Переменные окружения
Утилита psql может использовать переменные окружения. Для установки переменной используется команда «\set», которой нужно передать имя и значение переменной. Если команде «\set» не передать никаких параметров, то она выведет все переменные окружения.
Вывести значение переменной можно с помощью команды «\echo», которой нужно передать имя переменной, но перед именем нужно обязательно поставить двоеточие:
postgres=# \set TEST Hi! postgres=# \echo :TEST Hi! postgres=# \set AUTOCOMMIT = 'on' COMP_KEYWORD_CASE = 'preserve-upper' DBNAME = 'postgres' ECHO = 'none' ECHO_HIDDEN = 'off' ENCODING = 'UTF8' ERROR = 'false' FETCH_COUNT = '0' HIDE_TABLEAM = 'off' HISTCONTROL = 'none' HISTSIZE = '500' HOST = '/tmp' IGNOREEOF = '0' LAST_ERROR_MESSAGE = '' LAST_ERROR_SQLSTATE = '00000' ON_ERROR_ROLLBACK = 'off' ON_ERROR_STOP = 'off' PORT = '5432' PROMPT1 = '%/%R%x%# ' PROMPT2 = '%/%R%x%# ' PROMPT3 = '>> ' QUIET = 'off' ROW_COUNT = '1' SERVER_VERSION_NAME = '13.2' SERVER_VERSION_NUM = '130002' SHOW_CONTEXT = 'errors' SINGLELINE = 'off' SINGLESTEP = 'off' SQLSTATE = '00000' TEST = 'Hi!' USER = 'postgres' VERBOSITY = 'default' VERSION = 'PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit' VERSION_NAME = '13.2' VERSION_NUM = '130002'
Чтобы удалить переменную окружения используйте команду «\unset», например:
postgres=# \unset TEST postgres=# \echo :TEST :TEST
Как видно выше, если переменной нет, то «\echo» просто выводит переданный текст.
Но переменную можно создавать не только с помощью «\set», также можно в переменную записать результат запроса. Чтобы это сделать, нужно использовать в конце запроса вместо точки с запятой (;) команду «\gset»:
postgres=# SELECT now() AS curr_time \gset postgres=# \echo :curr_time 2021-04-09 10:31:49.521543+00
В примере выше мы результат функции now() записали в переменную curr_time!