PostgreSQL (произносится «Постгре-Эс-Кю-Эль», коротко называется «Постгрес») - свободная объектно-реляционная система управления базами данных (СУБД). Использует порт 5432/tcp/udp. PostgreSQL использует только один механизм хранения данных под названием Postgres storage system (система хранения Postgres), в котором транзакции и внешние ключи полностью функциональны, в отличии от MySQL, в котором InnoDB и BDB являются единственными типами таблиц, которые поддерживают транзакции.  

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

 

MVCC - одна из ключевых технологий доступа к данным, которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность, данные из таблицы сразу не удаляются, а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных, и созданием новых записей с измененными полями. Таким образом, история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям, которые были удалены или изменены в параллельных незакрытых транзакциях. Техника, используемая в этом подходе, относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.

  • xmin - хранит номер транзакции, в которой запись была создана.
  • xmax - хранит номер транзакции, в которой запись была удалена или изменена.

Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота, полей xmin, xmax осуществляется фильтрация записей.

 

PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (Настройка переменных локализации в Linux), иначе не будут работать строковые функции сортировки, upper/lower и т.п.

 

Клиенты для администрирования:

-pgAdmin - Лучший графический клиент из существующих.

-Tora

-phpPgAdmin

-OpenOffice и SDBC

 

psql - интерактивный терминал PostgreSQL

\?  - список команд

\h grant   -узнать подробности о команде

\l  -посмотреть список баз

\c dbname  -подключиться к базе данных

\conninfo   -статус текущего подключения

\dt(S+) - информация о таблицах текущей БД

\d table  - информация о структуре таблицы table

\dg  - посмотреть группы

\du  -посмотреть роли

\di  -список индексов

\dn -список схем

\dp -права доступа

\i FILE - выполнить команды из файла FILE.

\o FILE - сохранить результат запроса в файл FILE.

Команды \pset, \а, \С, \f, \H, \t, \T, \х управляют форматированием выходных данных.

\q    -выйти из PostgreSQL

\password [USERNAME]   -поменять пароль для пользователя

\echo `date`   - вывести текущую дату

\timing — после выполения данной команды каждый последующий запрос будет показывать время выполнения.

Версия Postres:

# SELECT version();

Все конфигурационные параметры базы:

# show all;

просмотр всех пользователей:

# select * from pg_user;

Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):

# show server_encoding;

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

# sudo -u postgres psql

# psql -U user_name -d database_name -h 127.0.0.1 -W

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

# psql -h 192.168.1.20 -U postgres

 

Пример выполнения SQL запроса из командой строки:

# sudo -u postgres psql -d mybd --command="select * from pg_stat_user_indexes where schemaname='public';"

 

Список пользователей:

# select * from pg_shadow;

# \du

Список баз данных:

# select * from pg_database;

# \l

Из командной строки:
# psql -U postgres -A -q -t -c "select datname from pg_database"
или:
# psql -l

 Список таблиц активной базы данных:

# \dt

 

Импорт базы данных:

# sudo -u postgres psql test777 < lost_cdr.sql

Время запуска сервера и uptime:

# SELECT pg_postmaster_start_time();

# SELECT current_timestamp - pg_postmaster_start_time() AS uptime;

 

Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет - запрос все равно продолжит свое выполнение, необходимо вызывать функцию pg_cancel_backend.

 

Посмотреть активные запросы:

# select * from pg_stat_activity;

Посмотреть все SELECT запросы:

# select * from pg_stat_activity WHERE current_query like 'SELECT%';

# select * from pg_stat_activity WHERE current_query like 'INSERT%';

 

Снять все активные select запросы:

# SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'SELECT%';

Снять запрос VACUUM:

# SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like 'VACUUM%';

 

SELECT запросы можно снимать из ОС командой kill:

# ps auxww | grep ^postgres

# kill 15724

 

Создать базу:

# CREATE DATABASE mydb WITH OWNER joe;

# createdb mydb -O joe

 

Удалить базу:

# DROP DATABASE mydb;

----------------------------------------------------------------------------------------------------------------------------------------------------+

 

psql [OPTION]... [DBNAME [USERNAME]]

 

Вывод ключей с описанием:

# psql --help

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

# psql -U postgres -d mydb --command="INSERT INTO countries (english,russian) VALUES ('Ivory Coast', 'Кот-д''Ивуар');"

 

-f, –file=FILENAME Вместо запуска в интерактивном режиме psql читает код SQL из файла с заданным именем и выполняет его так, как если бы он был введен непосредственно в командной строке. После обработки файла psql завершает работу. Например:

# sudo -u postgres psql -d mbillcz5054 -f /var/lib/pgsql/transaction.sql
BEGIN
DELETE 636
DELETE 1
COMMIT

 

-l, –list Вывод списка баз данных, к которым может подключиться клиент:

# psql -U postgres -l

 -U пользователь, -username пользователь. Подключение к базе данных с заданным именем пользователя.

 ----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Размер базы данных.

 

Физический размер БД, при помощи команды du:

# du -hsx /var/lib/pgsql

Просмотр размера базы по имени БД или ее OID:

# SELECT pg_database_size('имя базы');
# SELECT pg_database_size('oid');
# psql -U postgres -A -c "select pg_database_size('имя базы')"
# SELECT pg_database_size(current_database());

 Вывести суммарный размер всех баз данных:

# SELECT sum(pg_database_size(datname)) FROM pg_database;

 Каков размер таблицы(только данные) pg_relation_size:

# SELECT pg_relation_size('имя таблицы');
# psql -U postgres имя базы -c "SELECT pg_relation_size('имя таблицы')"

 Каков полный размер таблицы (включая индексы и другие связанные с нею пространства):   

# pg_total_relation_size или \dt+

# SELECT pg_total_relation_size('имя таблицы');

 Размер столбца:

# select pg_column_size('имя столбца') from 'имя таблицы';

 ----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Создать пользователя, назначить права.

 

Создать пользователя с настройками и паролем:

# createuser -P --interactive joe

# CREATE ROLE joe WITH password ‘tmppassword’;

 

Дать все права на таблицу для пользователя jerry:

# GRANT ALL PRIVILEGES ON TABLE test_db TO jerry;

 Дать все права на базу:

# GRANT ALL PRIVILEGES on DATABASE test_db to jerry;

 Дать привилегии на таблицу read:

# GRANT SELECT ON test_db to jerry;

 При ошибке в работе скрипта: Query failed: ERROR:  permission denied for sequence test_db, помогает:

# GRANT ALL ON SEQUENCE test_db to jerry;

 Дать право на SELECT столбцов id, name таблицы users для пользователя test:

# GRANT SELECT (id,name) ON users TO test;

 

Снять все права:

# REVOKE ALL PRIVILEGES ON DATABASE demo FROM joe1;

 ----------------------------------------------------------------------------------------------------------------------------------------------------+

Создание бэкапа.

pg_dump

pg_dump - это утилита позволяющая делать бэкап базы данных из postgresql.

 

Бэкап базы demo, принадлежащей пользователю postgres:

# pg_dump -U postgres demo -f demo.sql

 --format=t  -для упаковки дампа в .tar

 

Бэкап базы с сжатием в .gz:

# pg_dump -h localhost  -O -F p -c -U postgres mydb | gzip -c > mydb.gz

 

pg_dumpall

pg_dumpall - утилита для бэкапа всех баз данных.

 

Заходим под пользователем postgres:

# su - postgres

И делаем дамп:

# pg_dumpall > all_databases.sql

 

Проверяем наличие баз в дампе:

# psql -l

# grep "^[\]connect" all.sql

 

Бэкап таблицы:

# pg_dump --table auth_user -U postgres demo -f auth_user_table.sql

 --table  -название таблицы

--schema  - если таблица используется в нескольких схемах

 

Восстановление из бэкапа.

 

Восстановление одной базы demo:

# psql -U postgres -d demo -f demo.sql

# psql -h localhost -U postgres -d demo -f demo.sql

Восстановление всех баз:

# su - postgres
# psql -f all_databases.sql

Восстановление одной таблицы demo:

# psql -f auth_user_table.sql demo

 ----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Транзакции в PostgreSQL

 

В PostgreSQL Транзакция - это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.



PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

 

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2

# nano delprices.sql
BEGIN;
DELETE FROM prices WHERE ratesheet_id=2;
DELETE FROM ratesheets WHERE id=2;
COMMIT;

 

Выполним транзакцию для test777:

# sudo -u postgres psql -l
# sudo -u postgres psql test777 < delprices.sql

----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Лог файлы

Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.

----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Мониторинг

 

-Zabbix и PostgreSql

-pgFouine - a PostgreSQL log analyzer

 

Текущую активность базы данных можно узнать с помощью команды:

# watch -n 1 'ps auxww | grep ^postgres'

 

«пользователь», «база» и «хост» соответствуют имени пользователя «пользователь» подсоединявшегося к базе «база» с компьютера «хост». «статус» может принимать следующие параметры:

  • idle - ожидание команды от клиента,
  • idle in transaction - ожидание команды от клиента внутри транзакции (между BEGIN и окончанием транзакции),
  • SQL- команда - выполняется эта команда, например, SELECT,
  • waiting - ждём когда разблокируется занятая другим процессом таблица. Для уточнения из-за чего возникла блокировка, нужно анализировать представление pg_locks.

----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Views сборщик статистики  

Представления (Views) сборщика статистики.     

 

Statistics Collector (Standard Statistics Views): описание системных таблицах, собирающих информацию об активности базы данных.

 

Если в postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.

Информация собранная "статистическим сборником" может оказаться полезной для оценки эффективности базы данных и запросов. Из этих представлений можно узнать, например

 

  • Для каких таблиц стоит создать новые индексы (индикатором служит большое количество полных просмотров и большое количество прочитанных блоков).
  • Какие индексы вообще не используются в запросах. Их имеет смысл удалить, если, конечно, речь не идёт об индексах, обеспечивающих выполнение ограничений PRIMARY KEY и UNIQUE.
  • Достаточен ли объём буфера сервера.
  • Возможен "дедуктивный" подход, при котором сначала создаётся большое количество индексов, а затем неиспользуемые индексы удаляются.

 

Вывести все служебные таблицы:

# select schemaname,viewname,viewowner from pg_views where schemaname='pg_catalog';

 Активность по процессам:

# select * from pg_stat_activity;

 Активность по базам:

# select * from pg_stat_database;

 

# pg_stat_bgwriter

 

Вывести соотношение hit/read(должно лежать в пределах 5000-10000):

select datname, case when blks_read = 0 then 0 else blks_hit/blks_read end as ratio from pg_stat_database;

 

pg_stat_all_tables:

# select * from pg_stat_all_tables where schemaname='public';

# select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where schemaname='public';

 

pg_stat_sys_tables - То же что и pg_stat_all_tables, только системные таблицы.

 

pg_stat_user_tables - То же что и pg_stat_all_tables, только пользовательские таблицы. Статистика seq scan/index scan:

# select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100 else seq_scan/idx_scan end as ratio from pg_stat_user_tables order by ratio desc;

# select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100 else seq_scan/idx_scan end as ratio,n_live_tup,n_dead_tup from pg_stat_user_tables order by ratio desc;

 

Количество модификаций, произошедших в таблице:

select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables order by n_tup_ins desc;

 

pg_stat_all_indexes - Поможет оценить эффективность и частоту использования индексов при реальной работе:

select * from pg_stat_all_indexes;

 

pg_stat_sys_indexes - То же что и pg_stat_all_indexes, только системные таблицы

pg_stat_user_indexes - То же что и pg_stat_all_indexes, только пользовательские таблицы:

# select * from pg_stat_user_indexes where schemaname='public';

 

pg_statio_all_tables - Для каждой таблицы текущей базы данных (включая TOAST таблицы)

pg_statio_sys_tables - То же что и pg_statio_all_tables, только системные таблицы.

pg_statio_user_tables - То же что и pg_statio_all_tables, только пользовательские таблицы.

pg_statio_all_indexes - Для каждого индекса текущей базы данных

pg_statio_all_sequences - Для каждой последовательности в текущей базе данных: OID последовательности, схема и имя последовательности, количество прочитанных блоков с диска и попаданий в буфер.

pg_statio_sys_sequences - То же что и pg_statio_all_sequences, только системные таблицы.

pg_statio_user_sequences - То же что и pg_statio_all_sequences, только пользовательские таблицы.

pg_stat_user_functions - Значения времени указано в миллисекундах.

pg_locks - информация о блокировках в базе данных.

 

Для сброса системной статистики:

# select pg_stat_reset();

----------------------------------------------------------------------------------------------------------------------------------------------------+

 

Блокировки

 

  • Explicit Locking - документация о типах блокировок в PostgreSQL.

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.

----------------------------------------------------------------------------------------------------------------------------------------------------+

Automatic Vacuuming



VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]

 

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение SQL- запросов.

Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов. Регулярное исполнение команд VACUUM и ANALYZE обязательно.

Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс, который, соответственно, так и называется autovacuum daemon. Все настройки для этого процесса хранятся в postgresql.conf. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать, то в любом случае необходимо производить сборку мусора и набор статистики в ручную.

 

Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции:

  1. удаление всех данных, оставшихся в результате отмены транзакций и других операций, оставляющих временные данные;
  2. анализ операций с базами данных, по результатам которого PostgreSQL конструирует более эффективные запросы.

 

# sudo -u postgres psql mybd

mybd=# VACUUM ANALYZE VERBOSE;

 

Разница между vacuum и vacuum full заключается в том, что full физически переписывает на диске всю таблицу таким образом, чтобы в ней не оставалось "дырок" от удаленных или обновленных записей. Но его недостаток в том, что во время работы таблица полностью блокируется(включая и select запросы), что может привести к проблемам на популярном сервере

Простой VACUUM (Без FULL) просто восстанавливает пространство и делает его доступным для повторного использования. Эта форма команды умеет работать параллельно с обычными чтение и запись таблицы, без монопольной блокировки.



Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.

 

# vacuumdb -U postgres --verbose --analyze --full --dbname=mydb
# crontab
# Запуск "vacuumdb" каждый день в 22:01
1 22 * * *  root vacuumdb -U postgres --quiet --analyze --dbname=mydb &

----------------------------------------------------------------------------------------------------------------------------------------------------+

История команд psql хранится в файле .psql_history, расположенном в домашней директории пользователя postgres.

 

Экспорт таблицы из схемы bl:

# select table_name, table_schema from information_schema.tables where table_schema = 'bl';

# select * from bl.table_name limit 10;

# pg_dump -t bl.table_name database_name > /var/lib/postgresql/data/bl.table_name.sql

 

----------------------------------------------------------------------------------------------------------------------------------------------------+

Статья о репликации в postgresql: https://cp38.ru/index.php/po/102-postgresql-replication

 

Материалы:

Команды: https://postgrespro.ru/docs/postgresql/9.6/app-psql.html

https://wiki.dieg.info/postgresql

https://wiki.dieg.info/postgresql.conf  - настройка файла postgresql.conf

https://wiki.dieg.info/pg_locks

https://wiki.dieg.info/razmer_bazy_dannyx_postgresql

https://wiki.dieg.info/dopolnitelnye_vozmozhnosti_klienta_psql

https://the-bosha.ru/2016/06/01/backup-restore-postgresql-bazy-dannykh-s-pg_dump/

http://proft.me/2013/06/9/postgresql-spravochnik-po-komandam-psql-pg_dump/

 

Права:

https://postgrespro.ru/docs/postgresql/9.6/sql-grant.html

https://www.dmosk.ru/miniinstruktions.php?mini=postgresql-users

 

Репликация баз данных:

https://romantelychko.com/blog/1583/#2

https://eax.me/postgresql-replication/

 

Проектирование БД(http://dbdesigner.net/):

https://eax.me/database-design/

 

SQL: http://darkraha.com/rus/sql/sql33.php