Postgresql 10 что это

Postgresql 10 что это

PostgreSQL — это объектно-реляционная система управления базами данных ( ОРСУБД, ORDBMS ), основанная на POSTGRES, Version 4.2 — программе, разработанной на факультете компьютерных наук Калифорнийского университета в Беркли. В POSTGRES появилось множество новшеств, которые были реализованы в некоторых коммерческих СУБД гораздо позднее.

PostgreSQL — СУБД с открытым исходным кодом, основой которого был код, написанный в Беркли. Она поддерживает большую часть стандарта SQL и предлагает множество современных функций:

• сложные запросы
• внешние ключи
• триггеры
• изменяемые представления
• транзакционная целостность
• многоверсионность

Кроме того, пользователи могут всячески расширять возможности PostgreSQL , например создавая свои

• типы данных
• функции
• операторы
• агрегатные функции
• методы индексирования
• процедурные языки

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

Postgresql 10 что это

PL/pgSQL это процедурный язык для СУБД PostgreSQL . Целью проектирования PL/pgSQL было создание загружаемого процедурного языка, который:

используется для создания функций и триггеров,

добавляет управляющие структуры к языку SQL ,

может выполнять сложные вычисления,

наследует все пользовательские типы, функции и операторы,

может быть определён как доверенный язык,

Функции PL/pgSQL могут использоваться везде, где допустимы встроенные функции. Например, можно создать функции со сложными вычислениями и условной логикой, а затем использовать их при определении операторов или в индексных выражениях.

В версии PostgreSQL 9.0 и выше PL/pgSQL устанавливается по умолчанию. Тем не менее это по-прежнему загружаемый модуль и администраторы, особо заботящиеся о безопасности, могут удалить его при необходимости.

42.1.1. Преимущества использования PL/pgSQL

PostgreSQL и большинство других СУБД используют SQL в качестве языка запросов. SQL хорошо переносим и прост в изучении. Однако каждый оператор SQL выполняется индивидуально на сервере базы данных.

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

PL/pgSQL позволяет сгруппировать блок вычислений и последовательность запросов внутри сервера базы данных, таким образом, мы получаем силу процедурного языка и простоту использования SQL при значительной экономии накладных расходов на клиент-серверное взаимодействие.

Исключаются дополнительные обращения между клиентом и сервером

Промежуточные ненужные результаты не передаются между сервером и клиентом

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

Кроме того, PL/pgSQL позволяет использовать все типы данных, операторы и функции SQL .

42.1.2. Поддерживаемые типы данных аргументов и возвращаемых значений

Функции на PL/pgSQL могут принимать в качестве аргументов все поддерживаемые сервером скалярные типы данных или массивы и возвращать в качестве результата любой из этих типов. Они могут принимать и возвращать любой именованный составной тип (тип кортежа). Также есть возможность объявить функцию на PL/pgSQL как возвращающую record , то есть её результатом будет тип кортежа, столбцы которого определит спецификация вызывающего запроса, как описано в Подразделе 7.2.1.4.

Использование маркера VARIADIC позволяет объявлять функции на PL/pgSQL с переменным числом аргументов. Это работает точно так же, как и для функций на SQL, как описано в Подразделе 37.4.5.

Функции на PL/pgSQL могут принимать и возвращать полиморфные типы anyelement , anyarray , anynonarray , anyenum и anyrange . В таких случаях фактические типы данных могут меняться от вызова к вызову, как описано в Подраздел 37.2.5. Пример показан в Подразделе 42.3.1.

Функции на PL/pgSQL могут возвращать « множества » (или таблицы) любого типа, которые могут быть возвращены в виде одного объекта. Такие функции генерируют вывод, выполняя команду RETURN NEXT для каждого элемента результирующего набора или RETURN QUERY для вывода результата запроса.

Наконец, при отсутствии полезного возвращаемого значения функция на PL/pgSQL может возвращать void .

Функции на PL/pgSQL можно объявить с выходными параметрами вместо явного задания типа возвращаемого значения. Это не добавляет никаких фундаментальных возможностей языку, но часто бывает удобно, особенно для возвращения нескольких значений. Нотация RETURNS TABLE может использоваться вместо RETURNS SETOF .

Конкретные примеры рассматриваются в Подразделе 42.3.1 и Подразделе 42.6.1.

Вышел PostgreSQL 10

На самом деле прошло уже два дня, но статью на Хабр никто до сих пор не написал, так что придется мне устранять это упущение, что и делаю с удовольствием.

Итак, что же нового в этой версии PostgreSQL?

Во-первых, изменилось само версионирование. До «десятки» мы наблюдали множество минорных версий 9.x, которые выходили примерно раз в год и при этом вносили серьезные, далеко не минорные изменения. Поэтому с версии 10 было принято решение сделать нумерацию 10, 11, 12 и т.д. Кстати, MySQL, похоже пошел по тому же пути, прыгнул с 5.7 на 8.0

Ладно, это всё мелочи, перейдем к существу вопроса

Логическая репликация

Это то, чего все ждали давным-давно. Замена различным расширениям а ля slony (репликация на триггерах) и другим костылям.

Теперь вы можете из коробки делать репликацию отдельных таблиц на другие базы.

Репликация делается с помощью команд CREATE PUBLICATION и CREATE SUBSCRIPTION. Всё достаточно просто.

Понятно, что фича достаточно новая, поэтому на данный момент в логической репликации отсутствуют некоторые фичи.

• нет репликации схемы/DDL
• нет репликации сиквенсов
• не реплицируется команда TRUNCATE

Тем не менее это все равно огромный шаг вперед, в каких-то случаях можно выкинуть slony!

Партиционирование

Если раньше партиции можно было накостылять через наследование таблиц, то в десятке появилось для этого встроенное средство, которое называется declarative partitioning.

Для этого у главной таблицы добавляется ключевое слово PARTITON BY RANGE (или LIST ), которое говорит, что эта таблица партициирована (или как это правильно сказать по-русски?).

У конкретных партиций с помощью выражения PARTITION OF . FOR VALUES FROM (. ) TO (. ) задается диапазон их данных.

У партиций есть ряд ограничений, которые стоит иметь в виду, прежде чем использовать на продакшене: Limitations of declarative partitioning in PostgreSQL 10

Identity columns

Если вкратце, то появилась возможность писать

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

id serial PRIMARY KEY 

Что это дает?
Дело в том, что слово serial — это грубо говоря алиас к конструкции DEFAULT nextval(‘имя сиквенса’). Т.е. по сути таблица отдельно, сиквенс отдельно. Это бывает удобно, а бывает нет. Например, это приводит к тому, что если вы даете права на вставку в таблицу (GRANT INSERT), вам приходится еще отдельно давать гранты на сиквенс.

Кстати, новая запись соответствует стандарту SQL.

Прочее

• Улучшился паралеллизм, в частности Parallel Bitmap Heap Scan, Parallel Index Scan, Parallel Merge Join и т.д. Подробно можно прочесть в блоге Роберта Хааса
• Улучшена производительность физической репликации
• Hash-индексы стали реплицируемы
• Поддержка полнотекстового поиска на jsonb колонках
• SCRAM-аутентификация
• улучшенная поддержка работы с xml
• улучшен планировщик запросов при использовании join: если планировщик поймет, что объединенные строки не могут произвести в джойне больше одной строки, то можно не тратить лишнее время на поиск других строк. Примеры запросов можно посмотреть в тестах этого комита

Полный список изменений можно посмотреть здесь.

Если кто-то уже успел попробовать v10 в бою, поделитесь, плиз, своими впечатлениями в комментариях.

UPDATE. Есть мнение, что система версий не очень. Надо было называть PostgreSQL X, чтобы быть в тренде 🙂

• postgresql
• postgresql 10
• логическая репликация
• партиционирование
• partitioning

• Высокая производительность
• Веб-разработка
• PostgreSQL
• Программирование
• SQL

Postgresql 10 что это

postgres это сервер баз данных PostgreSQL . Для получения доступа к базе данных клиент устанавливает соединение (локально или по сети) с сервером postgres . После установки соединения сервер postgres поднимает выделенный процесс для его обслуживания.

Один экземпляр postgres всегда управляет данными ровно одного кластера баз данных. Кластер — это коллекция баз данных, хранящихся в файловой системе в определённом размещении ( « области данных » ). На одном физическом сервере можно запустить несколько экземпляров postgres одновременно, при условии, что они используют различные области данных и порты. При запуске postgres необходимо указать размещение данных, которое задаётся в параметре -D или переменной окружения PGDATA , значение по умолчанию отсутствует. Обычно -D или PGDATA указывает на каталог, созданный во время развёртывания кластера с помощью initdb . Иные варианты рассмотрены в Разделе 19.2.

По умолчанию postgres запускается не в фоновом режиме, а вывод журнала осуществляет в стандартный поток ошибок. На практике postgres должен запускаться в фоновом режиме, возможно, при старте системы.

Команду postgres также возможно использовать в однопользовательском режиме. В основном этот режим используется на этапе инициализации при выполнении initdb . Иногда он также применяется в целях отладки или после аварийного сбоя. Заметьте, что однопользовательский режим не вполне подходит для отладки сервера ввиду отсутствия в нём межпроцессного взаимодействия и блокировок. Когда сервер запускается в однопользовательском режиме из командной строки, он может принимать запросы и выводить их результаты на экран, но формат этого вывода ориентирован больше на разработчиков, чем на обычных пользователей. В этом режиме текущим пользователем считается пользователь под номером 1, который неявно наделяется правами суперпользователя. При этом данный пользователь может фактически не существовать, поэтому в ряде случаев этот режим даёт возможность вручную восстановить базу при повреждении системных каталогов.

Параметры

Программа postgres принимает следующие аргументы командной строки. За подробным описанием параметров обратитесь к Главе 19. От необходимости вводить большинство этих параметров можно избавиться, записав их в файл конфигурации. Некоторые (безопасные) параметры можно также задать со стороны подключающегося клиента (в зависимости от приложения), чтобы они применялись только к одному сеансу. Например, если установлена переменная окружения PGOPTIONS , клиенты на базе libpq передадут эту строку серверу, который воспримет её как параметры, передаваемые в командной строке postgres .

Параметры общего назначения

-B количество-буферов

Устанавливает количество разделяемых между процессами буферов. Значение по умолчанию выбирается автоматически при развёртывании кластера с помощью initdb . Установка флага аналогична конфигурации параметра shared_buffers. -c имя = значение

Устанавливает заданный параметр времени выполнения. Конфигурационные параметры, поддерживаемые PostgreSQL , описаны в Главе 19. Большинство других параметров командной строки на самом деле представляют собой краткие формы такого присваивания значений параметрам. Для установления нескольких параметров -c можно указывать многократно. -C имя

Отображает значение заданного параметра времени выполнения и завершается (подробнее см. выше). Можно применять на работающем сервере, при этом будут возвращены значения postgresql.conf с учётом проведённых в рамках вызова изменений. Значения, переданные при старте кластера, не отображаются.

Параметр предназначен для приложений, взаимодействующих с сервером, например pg_ctl , и запрашивающих параметры конфигурации. Пользовательские приложения должны использовать команду SHOW или представление pg_settings . -d уровень-отладки

Устанавливает уровень отладки (от 1 до 5). Чем выше значение, тем подробнее осуществляется вывод в журнал сервера. Также возможно передать -d 0 для отдельной сессии, что предотвратит в её рамках влияние выставленного для postgres значения. -D datadir

Указывает размещение конфигурационных файлов базы в пределах файловой системы. За подробностями обратитесь к Разделу 19.2. -e

Устанавливает формат вводимых дат по умолчанию в « European » с последовательностью значений DMY . Также влияет на вывод дня, идущего перед значением месяца, более подробно см. Раздел 8.5. -F

Отключает вызовы fsync для увеличения производительности, но с увеличением рисков потери данных в случае краха системы. Этот параметр работает аналогично параметру конфигурации fsync. Внимательно прочтите документацию перед использованием данного параметра! -h компьютер

Указывает IP-адрес или имя компьютера, на котором сервер postgres принимает клиентские подключения по TCP/IP. Значением может быть список адресов, разделённых запятыми, либо символ * , обозначающий все доступные интерфейсы. Если значение опущено, то подключения принимаются только через Unix-сокеты. По умолчанию принимаются подключения только к localhost . Флаг работает аналогично конфигурационному параметру listen_addresses. -i

Позволяет клиентам подключаться по TCP/IP. Без этого параметра допускаются лишь локальные подключения. Действие этого параметра аналогично действию параметра конфигурации listen_addresses со значением * в postgresql.conf или ключа -h .

Параметр устарел, так как не даёт полной функциональности listen_addresses. Лучше устанавливать значение listen_addresses напрямую. -k каталог

Указывает каталог Unix-сокета, через который postgres будет принимать подключения. Значением параметра может быть список каталогов через запятую. Если это значение пустое, использование Unix-сокетов запрещается, разрешаются только подключения по TCP/IP. По умолчанию выбирается каталог /tmp , но его можно сменить на этапе компиляции. Этот параметр действует аналогично параметру конфигурации unix_socket_directories. -l

Включает поддержку безопасных соединений с использованием SSL шифрования. PostgreSQL необходимо скомпилировать с поддержкой SSL для использования этого флага. Подробнее использование SSL описано в Раздел 18.9. -N максимальное количество соединений

Устанавливает максимально возможное количество одновременных клиентских соединений. Значение по умолчанию устанавливается автоматически на этапе развёртывания с помощью initdb . Флаг работает аналогично конфигурационному параметру max_connections. -o дополнительные-параметры

Аргументы командной строки, поступившие через дополнительные-параметры , передаются всем обслуживающим процессам, запускаемым этим процессом postgres .

Пробелы в строке дополнительные-параметры воспринимаются как разделяющие аргументы, если перед ними нет обратной косой черты ( \ ); чтобы представить обратную косую черту буквально, продублируйте её ( \\ ). Также можно задать несколько аргументов, используя -o несколько раз.

Использование этого параметра считается устаревшим, так как на данный момент все параметры postgres можно задать в командной строке. -p порт

Указывает порт TCP/IP или расширение файла локального Unix-сокета, через который postgres принимает подключения клиентских приложений. По умолчанию принимает значение переменной окружения PGPORT , или, если значение PGPORT не установлено, то используется значение, установленное на этапе компиляции (обычно это 5432). Если значение порта меняется, то на стороне клиентов это необходимо учитывать, установив, либо PGPORT , либо флаг командной строки. -s

Отображает информацию о времени и другую статистику после каждой выполненной команды, что полезно для оценки производительности во время настройки количества буферов. -S рабочая-память

Указывает объём памяти, который сервер будет использовать для внутренних операций сортировки и хеширования, прежде чем прибегнуть к использованию временных файлов на диске. Обратитесь к описанию параметра work_mem , приведённому в Подразделе 19.4.1. -V
—version

Отображает версию postgres и прерывает дальнейшее выполнение. — имя = значение

Устанавливает заданный параметр времени выполнения. Является короткой формой ключа -c . —describe-config

Выводит значения конфигурационных переменных сервера, их описаний и значений по умолчанию в формате команды COPY со знаком табуляции в качестве разделителя. В основном это предназначено для средств администрирования. -?
—help

Выводит помощь по аргументам команды postgres .

Параметры для внутреннего использования

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

Запрещает использование специфических методов сканирования и объединения: s и i выключают последовательное сканирование и по индексу соответственно, а o , b и t выключает сканирование только по индексу, сканирование по битовым векторам, и сканирование по ID кортежей соответственно, в то время как n , m и h выключает вложенные циклы, слияния и хеширование соответственно.

Ни последовательное сканирование, ни вложенные циклы невозможно выключить полностью. Флаги -fs и -fn просто указывают планировщику избегать выполнения этих операций при наличии других альтернатив. -n

Параметр предназначен для отладки сервера в случае аномального завершения процесса. Обычная практика в таком случае — завершение порождённых процессов с дальнейшей инициализацией разделяемой памяти и семафоров. Это связано с тем, что потерянный процесс мог повредить область разделяемой памяти. Параметр указывает postgres не производить повторной инициализации общих структур данных, что позволяет произвести дальнейшую отладку текущего состояния памяти и семафоров. -O

Разрешает модифицировать структуру системных таблиц. Используется командой initdb . -P

Игнорировать системные индексы при чтении, но продолжать обновлять их при изменениях системных таблиц. Это используется при их восстановлении после повреждения. -t pa[rser] | pl[anner] | e[xecutor]

Выводит статистику по времени исполнения каждого запроса в контексте каждого системного модуля. Использование флага совместно с -s невозможно. -T

Параметр предназначен для отладки сервера в случае аномального завершения процесса. Обычная практика в таком случае — завершение порождённых процессов с дальнейшей инициализацией разделяемой памяти и семафоров. Это связано с тем, что потерянный процесс мог повредить область разделяемой памяти. Параметр указывает postgres на необходимость остановки порождённых процессов сигналом SIGSTOP , но не завершит их, что позволяет разработчикам сделать снимки памяти процессов. -v протокол

Указывает для данного сеанса версию протокола взаимодействия сервера с клиентом. Флаг используется лишь для внутренних целей. -W секунды

При старте сервера производится задержка на указанное количество секунд, после чего производится процедура аутентификации, что позволяет подключить отладчик к процессу.

Параметры для однопользовательского режима

Следующие параметры применимы только для однопользовательского режима (см. Однопользовательский режим).

Устанавливает однопользовательский режим. Параметр должен идти первым в командной строке. база_данных

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

Выводить все команды в устройство стандартного вывода прежде чем выполнять их. -j

Считать признаком окончания ввода команды точку с запятой с двумя переводами строки, а не один перевод строки. -r имя_файла

Отправляет вывод журнала сервера в файл filename . Этот параметр применяется лишь при запуске из командной строки.

Переменные окружения

PGCLIENTENCODING

Кодировка, используемая клиентом по умолчанию. Может переопределяться на стороне клиента, а также устанавливаться в конфигурационном файле сервера. PGDATA

Каталог размещения данных кластера по умолчанию PGDATESTYLE

Значение по умолчанию для параметра времени выполнения DateStyle. Применение этой переменной является устаревшим. PGPORT

Порт по умолчанию, лучше устанавливать в конфигурационном файле.

Диагностика

Ошибки с упоминанием о semget или shmget говорят о возможной необходимости проведения более оптимального конфигурирования ядра. Подробнее это обсуждается в Разделе 18.4. Отложить переконфигурирование можно, уменьшив shared_buffers для снижения общего потребления разделяемой памяти PostgreSQL и/или уменьшив max_connections для снижения затрат на использование семафоров.

Необходимо внимательно проверять сообщения об ошибке с упоминанием о другом запущенном экземпляре, например, с использованием команды

$ ps ax | grep postgres 

$ ps -ef | grep postgres 

в зависимости от ОС. Если есть полная уверенность, что противоречий нет, необходимо самостоятельно удалить упомянутый в сообщении запирающий файл и повторить попытку.

Упоминание о невозможности привязки к порту в сообщениях об ошибках может указывать на то, что он уже занят другим процессом помимо PostgreSQL . Также сообщение может возникнуть при мгновенном рестарте postgres на том же порту. В этом случае нужно немного подождать, пока ОС не закроет порт, и повторить попытку. Ещё возможна ситуация, в которой используется резервный системный порт. Например, многие Unix-подобные ОС резервируют « доверительные » порты от 1024 и ниже, и лишь суперпользователь имеет к ним доступ.

Замечания

Для комфортного запуска и остановки сервера можно использовать утилиту pg_ctl .

Если возможно, не используйте сигнал SIGKILL для головного процесса postgres . В этом случае postgres не освободит системные ресурсы, например, разделяемую память и семафоры. Это может привести к проблемам при повторном запуске postgres .

Для корректного завершения postgres используются сигналы SIGTERM , SIGINT или SIGQUIT . При первом будут ожидаться все дочерние процессы до их завершения, второй приведёт к принудительному закрытию соединений, а третий — к незамедлительному выходу без корректного завершения, приводящему к необходимости выполнения процедуры восстановления на следующем старте.

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

Для отмены исполняющегося запроса, отправьте SIGINT обслуживающему его процессу. Для чистого завершения серверного процесса отправьте ему SIGTERM . Также см. pg_cancel_backend и pg_terminate_backend в Подразделе 9.26.2, которые являются аналогами в форме SQL-инструкций.

Сервер postgres обрабатывает SIGQUIT для завершения дочерних процессов в грязную, и сигнал не должен отправляться пользователем. Также не стоит посылать SIGKILL серверному процессу — головной postgres процесс расценит это как аварию и принудительно завершит остальные порождённые, как это было бы сделано при процедуре восстановления после сбоя.

Ошибки

Флаги, начинающиеся с — не работают в ОС FreeBSD или OpenBSD . Чтобы обойти это, используйте -c . Это ошибка ОС. В будущих релизах PostgreSQL будет предоставлен обходной путь, если ошибка так и не будет устранена.

Однопользовательский режим

Для запуска сервера в однопользовательском режиме используется, например, команда

postgres --single -D /usr/local/pgsql/data другие параметры my_database 

Необходимо указать корректный путь к каталогу хранения данных в параметре -D , или установить переменную окружения PGDATA . Также замените имя базы данных на необходимое.

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

Но если применить аргумент командной строки -j , одиночный символ перевода строки не будет завершать ввод команды; это будет делать последовательность «точка с запятой, перевод строки, перевод строки». То есть для завершения команды нужно ввести точку с запятой, и сразу за ней пустую строку. Просто точка с запятой, дополненная переводом строки, в этом режиме не имеет специального значения. Внутри строковых констант и комментариев такая завершающая последовательность воспринимается в том же ключе.

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

Для завершения сеанса введите символ конца файла ( EOF , обычно это сочетание Control + D ). Если вы вводили текст после окончания ввода последней команды, символ EOF будет воспринят как символ завершения команды, и для выхода потребуется ещё один EOF .

Заметьте, что однопользовательский режим не предоставляет особых возможностей для редактирования команд (например, нет истории команд). Также в однопользовательском режиме не производятся никакие фоновые действия, например, не выполняются автоматические контрольные точки или репликация.

Примеры

Для запуска postgres в фоновом режиме с параметрами по умолчанию:

$ nohup postgres >logfile 2>&1  

$ postgres -p 1234 

$ psql -p 1234 

$ export PGPORT=1234 $ psql 

$ postgres -c work_mem=1234 $ postgres --work-mem=1234