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

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


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


СУБД 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 не нужно. Если требуется явным образом остановить или запустить сервер, то это делается командами:


Кластер баз данных установлен в каталог: /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
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

По аналогии с 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


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