СУБД PostgreSQL

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

student$ sudo 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
• Обновить конфигурацию: pg_ctlcluster 9.6 main reload

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

В последующих темах мы будем ссылаться на этот каталог как $PGDATA, по имени переменной ОС, которую можно установить для использования в некоторых утилитах сервера.

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

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

total 88
drwx------ 27 postgres postgres 4096 авг 25 22:13 base
drwx------  2 postgres postgres 4096 авг 25 22:13 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 авг 25 17:48 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 авг 25 17:50 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 авг 25 17:50 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 авг 25 17:48 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 авг 25 17:50 pg_xlog
-rw-------  1 postgres postgres  107 авг 25 17:50 postgresql.auto.conf
-rw-------  1 postgres postgres  133 авг 25 17:50 postmaster.opts
-rw-------  1 postgres postgres   93 авг 25 17:50 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 авг 25 17:50 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 дек 26  2016 pg_ident.conf
-rw-r--r-- 1 postgres postgres 22465 авг 25 22:13 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 6596 авг 25 22:13 /var/log/postgresql/postgresql-9.6-main.log

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

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

2017-08-25 17:50:13 MSK [1806-1] LOG:  shutting down
2017-08-25 17:50:13 MSK [1800-4] LOG:  database system is shut down
2017-08-25 17:50:14 MSK [10809-1] LOG:  database system was shut down at 2017-08-25 17:50:13 MSK
2017-08-25 17:50:14 MSK [10809-2] LOG:  MultiXact member wraparound protections are now enabled
2017-08-25 17:50:14 MSK [10808-1] LOG:  database system is ready to accept connections
2017-08-25 17:50:14 MSK [10813-1] LOG:  autovacuum launcher started
2017-08-25 17:50:15 MSK [10815-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-08-25 17:50:17 MSK [10946-1] r@access_overview ERROR:  permission denied for relation t
2017-08-25 17:50:17 MSK [10946-2] r@access_overview STATEMENT:  UPDATE s.t SET key = key+1 WHERE key = 2;
2017-08-25 22:13:07 MSK [10808-2] LOG:  received SIGHUP, reloading configuration files

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

Проверим значение параметра 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:

student$ 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 name, setting, unit, category, context, source, sourcefile, 
          sourceline, boot_val, reset_val, pending_restart
   from pg_settings where name = 'work_mem';

-[ RECORD 1 ]---+------------------------
name            | work_mem
setting         | 65536
unit            | kB
category        | Resource Usage / Memory
context         | user
source          | session
sourcefile      | 
sourceline      | 
boot_val        | 4096
reset_val       | 16384
pending_restart | f

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

=> \x

Expanded display is off.

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

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

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

=> \! ls | head -n 10

dev1_01_tools_overview.sh
dev1_03_arch_mvcc.sh
dev1_04_arch_wal.sh
dev1_05_data_logical.sh
dev1_06_data_physical.sh
dev1_07_bstore_schema.sh
dev1_08_bstore_interaction.sh
dev1_09_sql_func.sh
dev1_10_sql_row.sh
dev1_11_plpgsql_introduction.sh

=> \! 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: 5

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

=> \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)

Присвоение значения переменной top5 лучше записать в стартовый файл .psqlrc в домашнем каталоге пользователя. Команды из .psqlrc будут автоматически выполняться каждый раз при старте psql.

Результат запроса можно записать в переменную с помощью \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

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