Обзор базового инструментария

Управление файлами:

командная строка: ls, pwd, cd...
графические менеджеры: mc, Thunar(встроенный файловый менеджер)

Редакторы файлов:

vim - текстовый редактор
nano - текстовый редактор
gedit - графический редактор
mousepad - графический редактор
mc - управление файлами с встроенным редактором

СУБД PostgreSQL

Установка выполнена из пакета. Каталог установки PostgreSQL:

root$ ls -l /usr/lib/postgresql/9.6

total 8
drwxr-xr-x 2 root root 4096 дек 26  2016 bin
drwxr-xr-x 2 root root 4096 дек 26  2016 lib

Владелец ПО сервера - пользователь root.

В Ubuntu доступ к утилитам сервера выполняется через обертки. Например, для управления сервером вместо pg_ctl используется pg_ctlcluster.

При установке из пакета также инициализируется кластер баз данных, а в настройки запуска ОС добавляется запуск PostgreSQL. Поэтому после загрузки операционной системы, отдельно стартовать PostgreSQL не нужно. Если требуется явным образом остановить или запустить сервер, то это делается командами:

Остановить сервер: pg_ctlcluster 9.6 main stop
Запустить сервер : pg_ctlcluster 9.6 main start
Перезапустить : pg_ctlcluster 9.6 main restart

Кластер баз данных установлен в каталог: /var/lib/postgresql/9.6/main

Владельцем каталога является пользователь postgres.

Вот содержимое этого каталога:

postgres$ ls -l /var/lib/postgresql/9.6/main

total 88
drwx------ 28 postgres postgres 4096 июл 22 23:38 base
drwx------  2 postgres postgres 4096 июл 22 23:38 global
drwx------  2 postgres postgres 4096 дек 26  2016 pg_clog
drwx------  2 postgres postgres 4096 дек 26  2016 pg_commit_ts
drwx------  2 postgres postgres 4096 дек 26  2016 pg_dynshmem
drwxr-xr-x  2 postgres root     4096 июл 22 23:27 pg_log
drwx------  4 postgres postgres 4096 дек 26  2016 pg_logical
drwx------  4 postgres postgres 4096 дек 26  2016 pg_multixact
drwx------  2 postgres postgres 4096 июл 22 23:29 pg_notify
drwx------  2 postgres postgres 4096 дек 26  2016 pg_replslot
drwx------  2 postgres postgres 4096 дек 26  2016 pg_serial
drwx------  2 postgres postgres 4096 дек 26  2016 pg_snapshots
drwx------  2 postgres postgres 4096 июл 22 23:29 pg_stat
drwx------  2 postgres postgres 4096 янв 10  2017 pg_stat_tmp
drwx------  2 postgres postgres 4096 дек 26  2016 pg_subtrans
drwx------  2 postgres postgres 4096 июл 22 23:27 pg_tblspc
drwx------  2 postgres postgres 4096 дек 26  2016 pg_twophase
-rw-------  1 postgres postgres    4 дек 26  2016 PG_VERSION
drwx------  3 postgres postgres 4096 июл 22 23:28 pg_xlog
-rw-------  1 postgres postgres  107 июл 22 23:29 postgresql.auto.conf
-rw-------  1 postgres postgres  133 июл 22 23:29 postmaster.opts
-rw-------  1 postgres postgres   92 июл 22 23:29 postmaster.pid

Основной файл конфигурации postgresql.conf расположен в:

postgres$ ls -l /etc/postgresql/9.6/main

total 48
-rw-r--r-- 1 postgres postgres   315 дек 26  2016 environment
-rw-r--r-- 1 postgres postgres   143 дек 26  2016 pg_ctl.conf
-rw-r----- 1 postgres postgres  4641 июл 22 23:29 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 дек 26  2016 pg_ident.conf
-rw-r--r-- 1 postgres postgres 22465 июл 22 23:38 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 дек 26  2016 start.conf

Здесь же находятся и другие конфигурационные файлы.

Журнал сервера находится здесь:

postgres$ ls -l /var/log/postgresql/postgresql-9.6-main.log

-rw-r----- 1 postgres adm 7471 июл 22 23:29 /var/log/postgresql/postgresql-9.6-main.log

Заглянем в конец журнала:

postgres$ tail -n 10 /var/log/postgresql/postgresql-9.6-main.log

2017-07-22 23:29:03 MSK [1825-1] LOG:  autovacuum launcher started
2017-07-22 23:29:03 MSK [1827-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-07-22 23:29:05 MSK [1976-1] r@access_overview ERROR:  permission denied for relation t
2017-07-22 23:29:05 MSK [1976-2] r@access_overview STATEMENT:  UPDATE s.t SET key = key+1 WHERE key = 2;
2017-07-22 23:29:11 MSK [2057-1] student@student ERROR:  database "access_overview" is being accessed by other users
2017-07-22 23:29:11 MSK [2057-2] student@student DETAIL:  There are 2 other sessions using the database.
2017-07-22 23:29:11 MSK [2057-3] student@student STATEMENT:  DROP DATABASE access_overview;
2017-07-22 23:29:11 MSK [2057-4] student@student ERROR:  role "r" cannot be dropped because some objects depend on it
2017-07-22 23:29:11 MSK [2057-5] student@student DETAIL:  3 objects in database access_overview
2017-07-22 23:29:11 MSK [2057-6] student@student STATEMENT:  DROP ROLE r;

Параметры конфигурации

Проверим значение параметра work_mem:

=> SHOW work_mem;

 work_mem 
----------
 4MB
(1 row)

Параметр work_mem задает объем памяти, который будет использоваться для внутренних операций сортировки и хеш-таблиц, прежде чем будут задействованы временные файлы на диске.

4MB - это значение по умолчанию и оно слишком мало. Допустим мы хотим увеличить его для всех до 16MB. Для этого можно внести изменения в postgresql.conf и обновить конфигурацию.

Редактируем postgresql.conf любым текстовым редактором. Для целей демонстрации воспользуемся утилитами командной строки. Файл принадлежит пользователю postgres, поэтому вносим правки именно этим пользователем.

postgres$ sed '/^work_mem/d' -i /etc/postgresql/9.6/main/postgresql.conf

postgres$ echo 'work_mem = 16MB' >> /etc/postgresql/9.6/main/postgresql.conf

Заглянем в конец файла:

postgres$ tail -n 5 /etc/postgresql/9.6/main/postgresql.conf

# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
work_mem = 16MB

Теперь нужно обновить конфигурацию. Воспользуемся pg_ctlcluster. Эту утилиту также нужно запускать из под postgres или через sudo:

$ sudo pg_ctlcluster 9.6 main reload

Указание reload заставляет PostgreSQL перечитать файлы конфигурации. Это происходит без перезагрузки сервера.

Еще раз проверим значение параметра work_mem:

=> SHOW work_mem;

 work_mem 
----------
 16MB
(1 row)

Изменения вступили в силу.

Большинству параметров можно установить новое значение для текущего сеанса во время выполнения. Например, если мы собираемся выполнить запрос, сортирующий большой объем данных, то для сеанса можно увеличить значение work_mem:

=> SET work_mem = '64MB';

SET

=> SHOW work_mem;

 work_mem 
----------
 64MB
(1 row)

Новое значенние действует только в текущем сеансе или в текущей транзакции (SET LOCAL).

Работа в psql

Проверим текущее подключение:

=> \conninfo

You are connected to database "tools_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".

Команда \c[onnect] выполняет новое подключение, не покидая psql.

psql умеет выводить результат запросов в разных форматах:

формат с выравниванием значений;
формат без выравнивания;
расширенный формат.

Формат с выравниванием используется по умолчанию:

=> select name, setting, unit from pg_settings limit 7;

          name           |  setting   | unit 
-------------------------+------------+------
 allow_system_table_mods | off        | 
 application_name        | psql       | 
 archive_command         | (disabled) | 
 archive_mode            | off        | 
 archive_timeout         | 0          | s
 array_nulls             | on         | 
 authentication_timeout  | 60         | s
(7 rows)

Ширина столбцов выровнена по значениям.

Также выводится строка заголовков и итоговая строка.

Команды psql для переключения режима выравнивания:

\a - переключатель режима: с выравниванием/без выравнивания.
\t - переключатель отображения строки заголовка и итоговой строки.

Отключим выравнивание, заголовок и итоговую строку:

=> \a

Output format is unaligned.

=> \t

Tuples only is on.

=> select name, setting, unit from pg_settings limit 7;

allow_system_table_mods|off|
application_name|psql|
archive_command|(disabled)|
archive_mode|off|
archive_timeout|0|s
array_nulls|on|
authentication_timeout|60|s

=> \t

Tuples only is off.

=> \a

Output format is aligned.

Расширенный формат:

=> \x

Expanded display is on.

=> select * from pg_settings where name = 'work_mem';

-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name            | work_mem
setting         | 65536
unit            | kB
category        | Resource Usage / Memory
short_desc      | Sets the maximum memory to be used for query workspaces.
extra_desc      | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context         | user
vartype         | integer
source          | session
min_val         | 64
max_val         | 2097151
enumvals        | 
boot_val        | 4096
reset_val       | 16384
sourcefile      | 
sourceline      | 
pending_restart | f

Вернем установки по умолчанию.

=> \x

Expanded display is off.

Все возможности форматирования результатов запросов доступны через команду \pset.

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

Можно выполнять команды shell:

=> \! ls | head -n 10

dev1_01_tools_overview.sh
dev1_03_arch_mvcc.html
dev1_03_arch_mvcc.sh
dev1_04_arch_wal.html
dev1_04_arch_wal.sh
dev1_05_data_logical.html
dev1_05_data_logical.sh
dev1_06_data_physical.html
dev1_06_data_physical.sh
dev1_07_data_objects.html

=> \! pwd

/home/student/dev1/demo

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

Можно записать вывод команды в файл с помощью \o[ut]:

=> \o dev1_psql.log

=> select name, setting, unit from pg_settings limit 5;

На экран ничего не попало.

Посмотрим в файле:

=> \! cat dev1_psql.log

          name           |  setting   | unit 
-------------------------+------------+------
 allow_system_table_mods | off        | 
 application_name        | psql       | 
 archive_command         | (disabled) | 
 archive_mode            | off        | 
 archive_timeout         | 0          | s
(5 rows)

Вернем вывод на экран:

=> \o

Запишем в файл команды SQL.

=> \a \t

Output format is unaligned.
Tuples only is on.

=> \pset fieldsep ''

Field separator is "".

=> \o dev1_psql.log

=> select 'select '''||tablename||': '', count(*) from ',tablename||';' from pg_tables limit 3;

=> \o

Вот что получилось в файле:

=> \! cat dev1_psql.log

select 'pg_statistic: ', count(*) from pg_statistic;
select 'pg_user_mapping: ', count(*) from pg_user_mapping;
select 'pg_authid: ', count(*) from pg_authid;

И выполним теперь эти команды с помощью \i[nclude]:

=> \i dev1_psql.log

pg_statistic: 384
pg_user_mapping: 0
pg_authid: 9

Восстановим форматирование по умолчанию.

=> \t \a

Tuples only is off.
Output format is aligned.

Другие способы выполнить команды из файла:

psql < filename
psql -f filename
psql -c command (работает только для одной команды)

Переменные psql

По аналогии с shell, psql имеет собственные переменные.

Установим переменную:

=> \set TEST Hi!

Чтобы получить значение, надо предварить имя переменной двоеточием:

=> \echo :TEST

Hi!

Значение переменной можно сбросить:

=> \unset TEST

=> \echo :TEST

:TEST

Переменные можно использовать для хранения текста часто используемых запросов. Запрос на получение пяти самых больших по размеру таблиц:

=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) as bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Для выполнения запроса достаточно набрать:

=> :top5

   tablename    |  bytes  
----------------+---------
 pg_depend      | 1040384
 pg_proc        |  958464
 pg_rewrite     |  598016
 pg_attribute   |  548864
 pg_description |  483328
(5 rows)

Результат запроса можно записать в переменную с помощью \gset вместо точки с запятой:

=> select current_setting('work_mem') as current_work_mem \gset

=> \echo Значение work_mem: :current_work_mem

Значение work_mem: 64MB

Без параметров \set выдает значения всех переменных, включая встроенные:

=> \set

AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
SHOW_CONTEXT = 'errors'
VERSION = 'PostgreSQL 9.6.1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 32-bit'
HISTFILE = 'hist'
DBNAME = 'tools_overview'
USER = 'student'
HOST = '/var/run/postgresql'
PORT = '5432'
ENCODING = 'UTF8'
LASTOID = '0'
top5 = 'SELECT tablename, pg_total_relation_size(schemaname||'.'||tablename) as bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'
current_work_mem = '64MB'

Справка по встроенным переменным: \? variables

Конец демонстрации.