Postgresql что это

Чем PostgreSQL лучше других SQL баз данных с открытым исходным кодом. Часть 1

Сегодня давайте поговорим о преимуществах Postgres перед другими системами с открытым кодом. Эту тему мы обязательно раскроем более подробно на PG Day’16 Russia, до которой осталось всего два месяца.

Возможно, вы спрашиваете себя: «Почему PostgreSQL?» Ведь есть и другие варианты реляционных баз данных с открытым исходным кодом (в рамках этой статьи мы рассматривали MySQL, MariaDB и Firebird), так что же Постгрес может предложить такого, чего нет у них? В слогане PostgreSQL заявляется, что это «Самая продвинутая база данных с открытым исходным кодом в мире». Мы приведем несколько причин, почему Постгрес делает такие заявления.

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

Модель данных

PostgreSQL не просто реляционная, а объектно-реляционная СУБД. Это даёт ему некоторые преимущества над другими SQL базами данных с открытым исходным кодом, такими как MySQL, MariaDB и Firebird.

Фундаментальная характеристика объектно-реляционной базы данных — это поддержка пользовательских объектов и их поведения, включая типы данных, функции, операции, домены и индексы. Это делает Постгрес невероятно гибким и надежным. Среди прочего, он умеет создавать, хранить и извлекать сложные структуры данных. В некоторых примерах ниже вы увидите вложенные и составные конструкции, которые не поддерживаются стандартными РСУБД.

Структуры и типы данных

Существует обширный список типов данных, которые поддерживает Постгрес. Кроме числовых, с плавающей точкой, текстовых, булевых и других ожидаемых типов данных (а также множества их вариаций), PostgreSQL может похвастаться поддержкой uuid, денежного, перечисляемого, геометрического, бинарного типов, сетевых адресов, битовых строк, текстового поиска, xml, json, массивов, композитных типов и диапазонов, а также некоторых внутренних типов для идентификации объектов и местоположения логов. Справедливости ради стоит сказать, что MySQL, MariaDB и Firebird тоже имеют некоторые из этих типов данных, но только Постгрес поддерживает их все.

Давайте рассмотрим подробнее некоторые из них:

Сетевые адреса

У MySQL и MariaDB тоже есть INET функции для конвертации сетевых адресов, но они не предоставляют типы данных для внутреннего хранения сетевых адресов. У Firebird тоже нет типов для хранения сетевых адресов.

Многомерные массивы

Поскольку Постгрес — это объектно-реляционная база данных, массивы значений могут храниться для большинства существующих типов данных. Сделать это можно путём добавления квадратных скобок к спецификации типа данных для столбца или с помощью выражения ARRAY. Размер массива может быть задан, но это необязательно. Давайте рассмотрим меню праздничного пикника для демонстрации использования массивов:

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

Геометрические данные

Геоданные быстро становятся основным требованием для многих приложений. PostgreSQL уже давно поддерживает множество геометрических типов данных, таких как точки, линии, круги и многоугольники. Один из этих типов – PATH, он состоит из множества последовательно расположенных точек и может быть открытым (начальная и конечная точки не связаны) или закрытым (начальная и конечная точки связаны). Давайте рассмотрим в качестве примера туристическую тропу. В данном случае туристическая тропа — это петля, поэтому начальная и конечная точки связаны, и, значит, мой путь является закрытым. Круглые скобки вокруг набора координат указывают на закрытый путь, а квадратные — на открытый.

Расширение PostGIS для PostgreSQL дополняет существующие свойства геометрических данных вспомогательными пространственными типами, функциями, операторами и индексами. Оно обеспечивает поддержку местоположения и поддерживает как растровые, так и векторные данные. Оно также обеспечивает совместимость с множеством сторонних геопространственных инструментов (защищённых авторским правом и с открытым исходным кодом) для отображения, отрисовки и работы с данными.

Заметьте, что в MySQL 5.7.8 и в MariaDB, начиная с версии 5.3.3, были добавлены расширения типов данных для поддержки стандарта географической информации OpenGIS. Эта версия MySQL и последующие версии MariaDB предлагают хранение типов данных, аналогичное штатным геоданным Постгреса. Тем не менее, в MySQL и MariaDB значения данных сначала должны быть сконвертированы в геометрический формат простыми командами перед тем, как будут вставлены в таблицу. Firebird на данный момент не поддерживает геометрические типы данных.

Поддержка JSON

Поддержка JSON в PostgreSQL позволяет вам перейти к хранению schema-less данных в SQL базе данных. Это может быть полезно, когда структура данных требует определённой гибкости: например, если в процессе разработки структура всё ещё меняется или неизвестно, какие поля будет содержать объект данных.

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

В MySQL 5.7.8 и MariaDB 10.0.1 была добавлена поддержка встроенных объектов JSON. Но, хотя существует множество функций и операторов для JSON, которые теперь доступны в этих базах данных, они не индексируются так, как JSONB в PostgreSQL. Firebird пока что не присоединился к тренду и поддерживает объекты JSON только в виде текста.

Создание нового типа

Если вдруг так случится, что обширного списка типов данных Постгреса вам окажется недостаточно, вы можете использовать команду CREATE TYPE, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:

Поскольку они не являются объектно-реляционными, MySQL, MariaDB и Firebird не предоставляют такую мощную функциональность.

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

PostgreSQL может обрабатывать много данных. Текущие опубликованные ограничения перечислены ниже:

В Compose [прим. пер.: организация, в которой трудится автор оригинальной статьи] мы автоматически масштабируем вашу инсталляцию, чтобы вам не приходилось волноваться о росте количества данных. Но, как известно любому администратору баз данных, стоит с опаской относиться к слишком большим и неограниченным возможностям. Мы советуем руководствоваться здравым смыслом при создании таблиц и добавлении индексов.

Для сравнения, MySQL и MariaDB печально известны ограничением размера строк в 65 535 байт. Firebird также предлагает всего лишь 64Кб в качестве максимального размера строки. Обычно объём данных ограничивается максимальным размером файлов операционной системы. Поскольку PostgreSQL умеет хранить табличные данные в множестве файлов меньшего размера, он может обойти это ограничение. Но стоит отметить, что слишком большое количество файлов может негативно сказаться на производительности. MySQL и MariaDB поддерживают большее количество столбцов в таблице (до 4,096 в зависимости от типа данных) и большие индивидуальные размеры таблицы, чем PostgreSQL, но необходимость превысить существующие ограничения Постгреса возникает лишь в крайне редких случаях.

Целостность данных

Постгрес стремится соответствовать стандарту ANSI-SQL:2008, отвечает требованиям ACID (атомарность, согласованность, изолированность и надежность) и известен своей ссылочной и транзакционной целостностью. Первичные ключи, ограничивающие и каскадные внешние ключи, уникальные ограничения, ограничения NOT NULL, проверочные ограничения и другие функции обеспечения целостности данных дают уверенность, что только корректные данные будут сохранены.

MySQL и MariaDB больше работают на то, чтобы соответствовать стандарту SQL с движками таблиц InnoDB/XtraDB. Теперь они предлагают опцию STRICT с использованием режимов SQL, которая устанавливает проверки корректности используемых данных. Несмотря на это, в зависимости от того, какой режим вы используете, недостоверные и даже урезанные без вашего ведома данные могут быть вставлены или созданы при обновлении. Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения. Кроме того, у них существует множество особенностей в отношении ограничений ссылочной целостности по внешним ключам. В дополнение к вышесказанному, целостность данных может существенно пострадать в зависимости от выбранного движка хранения. MySQL (и fork MariaDB) не делают секрета из того, что променяли целостность и соответствие стандартам на скорость и эффективность.

Подводя итоги

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

Если вам кажется, что PostgreSQL не соответствует вашим потребностям, или вы предпочитаете “стрелять от бедра”, тогда вам стоит обратить внимание на NoSQL базы данных, которые мы предлагаем в Compose, или подумать о других SQL базах данных, которые мы упоминали. У каждой из них есть свои преимущества. Compose твёрдо уверен, что очень важно выбрать правильную базу данных для конкретной задачи… иногда это означает, что нужно выбрать несколько баз данных!

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

Источник

Postgresql что это

История развития PostgreSQL

Краткую историю PostgreSQL можно прочитать в документации, распространяемой с дистрибутивом или на сайте. Также, есть перевод на русский язык. Из нее следует, что современный проект PostgreSQL ведет происхождение из проекта POSTGRES, который разрабатывался под руководством Майкла Стоунбрейкера (Michael Stonebraker), профессора Калифорнийского университета в Беркли (UCB). Мне захотелось несколько подробнее показать взаимосвязи родословных баз данных, чтобы лучше понять место PostgreSQL среди основных игроков современного рынка баз данных.

Я попытался графически ( большая версия картинки откроется в новом окне) отобразить все наиболее заметные RDBMS и связи между ними и приблизительно привел даты их создания и конца. Пересечение объектов означает поглощение, при этом поглощаемый объект более бледен и не окантован. Знак доллара означает, что база данных является коммерческой. При этом, я основывался на информации, доступной в интернете, в частности в Wikipedia, в научных статьях, которые я читал и комментариях непосредственных пользователей БД, которые я получил после публикации этой картинки в интернете.

«System R» сыграла большую роль в развитии реляционных баз данных, создании языка SQL (изначально SEQUEL, но из-за проблем с уже существующей торговой маркой пришлось выкинуть все гласные буквы). Из «System R» развилась SQL/DS и DB2. На самом деле, в IBM было еще несколько проектов, но они были чисто внутренними. Подробнее об этой ветви можно прочитать в весьма поучительном документе «The 1995 SQL Reunion: People, Projects, and Politics», русский перевод которого доступен по адресу www.citforum.ru/database/digest/sql1.shtml.

INGRES (или Ingres89), в отличие от «System R», вполне в духе Беркли развивалась как открытая база данных, коды которой распространялись на лентах практически бесплатно (оплачивались почтовые расходы и стоимость ленты). К 1980 году было распространено порядка 1000 копий. Название расшифровывается как «INteractive Graphics Retrieval System» и совершенно случайно связано с французским художником Jean Auguste Dominique Ingres. Отличительной особенностью этой системы являлось то, что она разрабатывалась для операционной системы UNIX, которая работала на распространенных тогда PDP 11, что и предопределило ее популярность, в то время как «System R» работала только на больших и дорогих mainframe. Был разработан язык запросов QUEL, который, как писал Стоунбрейкер, похож на SEQUEL в том отношении, что программист свободен от знания о структуре данных и алгоритмах, что способствует значительной степени независимости от данных. Доступность INGRES и очень либеральная лицензия BSD, а также творческая деятельность, способствовали появлению большого количества реляционных баз данных, как показано на рисунке.

Стоунбрейкер лично способствовал их появлению, так он конце 70-х он организовал компанию Ingres Corporation (как он сам объясняет, ему пришлось на это пойти, так как Аризонский университет, потребовал поддержки), которая выпустила коммерческую версию Ingres, в 1994 году она была куплена CA (Computer Associates) и которая в 2004 году стала открытой как Ingres r3.

«NonStop SQL» компании Tandem Computers являлась модифицированной версией Ingres, которая эффективно работала на параллельных компьютерах и с распределенными данными. Она умела выполнять запросы параллельно и масштабировалась почти линейно с количеством процессоров. Ее авторами были выпускники из Беркли. Впоследствии, Tandem Computers была куплена компанией Compaq (2000 г.), а затем компанией HP.

Informix тоже возник из Ingres, но на это раз людьми не из Беркли, хотя Стоунбрейкер все-таки поработал в ней CEO после того, как Informix купила в 1995 году компанию Ilustra, чтобы прибавить себе объектно-реляционности и расширяемости (DataBlade), которую организовал все тот же Майкл Стоунбрейкер как результат коммерциализации Postgres в 1992 году. В 2001 году она была куплена IBM, которая приобретала немалое количество пользователей Informix и технологию. Таким образом, DB2 также приобрела немного объектно-реляционности.

Проект Postgres возник как результат осмысления ошибок Ingres и желания преодолеть ограниченность типов данных, за счет возможности определения новых типов данных. Работа над проектом началась в 1985 и в период 1985-1988 было опубликовано несколько статей, описывающих модель данных, язык запросов POSTQUEL, и хранилище Postgres.

Еще при проектировании оригинальной версии POSTGRES основное внимание было уделено расширяемости и объектно-ориентированным возможностям. Уже тогда было ясна необходимость расширения функциональности DMBS от управления данными (data management) в сторону управления объектами (object management) и знаниями (knowledge management). При этом объектная функциональность позволит эффективно хранить и манипулировать нетрадиционными типами данных, а управление знаниями позволяет хранить и обеспечивать выполнения коллекции правил (rules), которые несут семантику приложения. Стоунбрейкер так и определил основную задачу POSTGRES как «обеспечить поддержку приложений, которые требуют службы управления данными, объектами и знаниями«.

На сегодняшний день выпущена версия PostgreSQL v8 (19 января 2005 года), которая является значительным событием в мире баз данных, так как количество новых возможностей добавленных в этой версии, позволяет говорить о возникновении интереса крупного бизнеса как в использовании, так и его продвижении. Так, крупнейшая компания в мире, Fujitsu поддержала работы над версией 8, выпустила коммерческий модуль Extended Storage Management. Либеральная BSD-лицензия позволяет коммерческим компаниям выпускать свои версии PostgreSQL под своим именем и осуществлять коммерческую поддержку. Например, компания Pervasive объявила о выпуске Pervasive Postgres.

PostgreSQL поддерживается на всех современных Unix системах (34 платформы), включая наиболее распространенные, такие как Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, а также под Mac OS X. Начиная с версии 8.X PostgreSQL работает в «native» режиме под MS Windows NT, Win2000, WinXP, Win2003. Известно, что есть успешные попытки работать с PostgreSQL под Novell Netware 6 и OS2.

Основные возможности и функциональность

Дистрибутив PostgreSQL в поддиректории contrib/ содержит большое количество (около 80) так называемых контриб-модулей, реализующих разнообразную дополнительную функциональность, такую как, полнотекстовый поиск, работа с xml, функции математической статистики, поиск с ошибками, криптографические модули и т.д. Также, есть утилиты, облегчающие миграцию с mysql, oracle, для административных работ.

Некоторые ограничения PostgreSQL

Сводная таблица основных реляционных баз данных

За основу взяты данные из Wikipedia

Что ожидается в будущих версиях

Это относится и к тем предложениям, которые уже имеют или рассчитывают на финансовую поддержку коммерческих компаний.

Цикл разработки

На карте обозначены точки, где живут и работают члены PGDG, оригинальная версия с большей функциональностью находится на официальном сайте разработчиков.

Структура

Где используется

Сообщество

Поддержка

Небольшая статистика списков рассылок PostgreSQL по данным www.pgsql.ru на 1 апреля 2005 года.

Разработка

Заключение

Благодарности

Текст написан Олегом Бартуновым в 2005 году, поправки и комментарии приветствуются.

Источник

Национальная библиотека им. Н. Э. Баумана
Bauman National Library

Персональные инструменты

PostgreSQL

Содержание

История

PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной как open-source проект в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер, руководитель более раннего проекта Ingres, на тот момент уже приобретённого компанией Computer Associates. Название расшифровывалось как «Post Ingres», и при создании Postgres были применены многие уже ранее сделанные наработки.

Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет с 1986 по 1994 год. За этот период в синтаксис были введены процедуры, правила, пользовательские типы и другие компоненты. В 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres — Postgres95, в которой язык запросов POSTQUEL — наследие Ingres — был заменен на SQL.

Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL. [Источник 2]

О продукте

PostgreSQL поддерживается на всех современных Unix системах (34 платформы), включая наиболее распространенные, такие как Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, а также под macOS. Начиная с версии 8.X PostgreSQL работает в «native» режиме под MS Windows NT, Win2000, WinXP, Win2003. Известно, что есть успешные попытки работать с PostgreSQL под Novell Netware 6 и OS2.

PostgreSQL неоднократно признавалась базой года, например, Linux New Media AWARD 2004, 2003 Editors’ Choice Awards, 2004 Editors’ Choice Awards. [Источник 3]

Основные возможности и функциональность

Надежность

Надежность PostgreSQL является проверенным и доказанным фактом и обеспечивается следующими возможностями:

Производительность

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

Расширяемость

Расширяемость PostgreSQL означает, что пользователь может настраивать систему путем определения новых функций, агрегатов, типов,языков, индексов и операторов. Объектно-ориентированность PostgreSQL позволяет перенести логику приложения на уровень базы данных, что сильно упрощает разработку клиентов, так как вся бизнес логика находится в базе данных. Функции в PostgreSQL однозначно определяются названием, количеством и типами аргументов. [Источник 4]

Поддержка SQL

Кроме основных возможностей, присущих любой SQL базе данных, PostgreSQL поддерживает:

Типы данных

PostgreSQL поддерживает большой набор встроенных типов данных:

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

Начиная с PostgreSQL 9.2, может использоваться тип данных, представляющий диапазон данных, которые называются типами диапазонов. Это могут быть дискретные диапазоны (например, все целые значения от 1 до 10) или непрерывные диапазоны (например, любой момент времени между 10:00 и 11:00). Доступные типы доступных диапазонов включают диапазоны целых чисел, большие целые числа, десятичные числа, отметки времени (с часовым поясом и без него) и даты.

Пользовательские типы диапазонов могут быть созданы для обеспечения доступности новых типов диапазонов, таких как диапазоны IP-адресов, с использованием типа inet в качестве базы или диапазонов с плавающей точкой, используя тип данных float в качестве базы. Типы диапазонов поддерживают включенные и исключительные границы диапазона, используя символы и () соответственно. (например, представляет все целые числа, начиная с 4 включительно, но не включая 9.) Типы диапазонов также совместимы с существующими операторами, используемыми для проверки наложения, сдерживания, права и т. д. [Источник 4]

Наследование

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

По состоянию на 2010, эта функция поддерживается не полностью, но, в частности, таблица ограничений в настоящее время не наследуемая. Все проверочные ограничения и ненулевые ограничения на родительской таблице автоматически наследуются его детьми. Другие типы ограничений (уникальные, первичный ключ, и иностранные ключевые ограничения) не наследуются.

Наследование обеспечивает способ отображения особенности иерархий обобщения, изображенных на сущность-связь диаграммах (ERD) непосредственно в базе данных PostgreSQL [Источник 4]

Функции запроса

Поддержка стандартов, возможности, особенности

PostgreSQL PostgreSQL поддерживает большинство возможностей стандарта SQL: 2011, ACID-совместимая и транзакционная (включая большинство DDL утверждения) избегает проблемы блокировки с помощью механизма Многоверсионное управление параллельным доступом (MVCC), обеспечивает иммунитет к «грязному» чтению и полую сериализационность; управляет комплексными SQL запросами используя множество индексированных методов, которые недоступны в других базах данных; имеет обновляемые представления и материализованные представления, триггеры, внешние ключи; поддерживает функции и хранимые процедуры, и другие возможности расширения, и имеет множество расширений, написанных третьими лицами. В дополнение к возможности работы с основными фирменными и с открытым исходным кодом базами данных, PostgreSQL поддерживает миграцию из них, путем своей обширной поддержки стандарта SQL и доступных инструментов миграции. Фирменные расширения в базах данных, таких как Oracle можно эмулировать с помощью встроенных и сторонних расширений совместимости с открытым исходным кодом. Последние версии также обеспечивают репликацию самой базы данных для доступности и масштабируемости.

PostgreSQL является кросплотформенной и работает на множестве операционных систем, включая Linux, FreeBSD, macOS, Solaris, и Microsoft Windows. Начиная с Mac OS X 10.7 Lion Server, PostgreSQL это стандартная база данных по умолчанию, и клиентские инструменты PostgreSQL идут в комплекте с настольной версией. Подавляющее большинство дистрибутивов Linux имеет PostgreSQL доступным в поддерживаемых пакетах.

PostgreSQL разработан PostgreSQL Global Development Group, разнообразной группой из многих компаний и отдельных вкладчиков. Это свободное и открытое программное обеспечение, распространяемое по условиям Лицензии PostgreSQL, разрешительной лицензии свободного программного обеспечения.

Поскольку СУБД PostgreSQL выпускается под либеральной лицензией, её можно бесплатно использовать, модифицировать и распространять для любых целей, включая личные, коммерческие или академические.

На данный момент (версия 9.4.5), в PostgreSQL имеются следующие ограничения: [Источник 5]

Сильными сторонами PostgreSQL считаются:

Разработка

Это относится и к тем предложениям, которые уже имеют или рассчитывают на финансовую поддержку коммерческих компаний.

Цикл работой над новой версией обычно длится 10-12 месяцев (сейчас ведется дискуссия о более коротком цикле 2-3 месяца) и состоит из нескольких этапов. [Источник 1]

Другие функции хранения

Ограничения ссылочной целостности, включая ограничения внешнего ключа, ограничения столбцов и проверки строк Двоичное и текстовое хранилище больших объектов Табличные Сравнение столбцов Онлайн-резервная Восстановление по времени, реализованное с использованием записи на основе записи Обновление на месте с помощью pg_upgrade за меньшее время простоя (поддерживает обновления с 8.3.x и выше) [Источник 1]

Установка и настройка

В данном разделе представлена инструкция по установки и настройке PostgreSQL для разных ОС [Источник 6]

Установка

Если установка происходит на macOS, то процесс установки можно запустить командой: [Источник 7]

На Linux СУБД устанавливается так:

После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:

Инструкция по установке в цифровом формате

Настройка

Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. [Источник 7]

Необходимо ввести следующую команду:

Этой командой запускается утилита psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.

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

На рисунке выше вы видите три базы данных по умолчанию и суперпользователя postgres, которые создаются при установке PostgreSQL.

Основные операции с БД

Чтобы выполнять базовые действия в СУБД, нужно знать язык запросов к базе данных SQL.

Создание базы данных

Для создания базы данных используется команда: [Источник 7]

В приведенном ниже примере создается база данных с именем proglib_db.

Если забыть точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-». Это зачастую указывает на то, что необходимо завершить (дописать) запрос.

На рисунке 4 видно сообщение об ошибке из-за того, что в нашем случае база уже создана.

Создание нового юзера

Для создания пользователя существует команда:

В приведенном ниже примере создается пользователь с именем author.

При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.

Можно установить пароль для существующего пользователя. [Источник 7]

С этой задачей справится команда \password :

Чтобы задать пароль при создании пользователя, можно использовать следующую команду:

Удаление базы или пользователя

Для этой операции используется команда drop : она умеет удалять как пользователя, так и БД. [Источник 7]

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

Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:

Источник

PostgreSQL: The World’s Most Advanced Open Source Relational Database

New to PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

There is a wealth of information to be found describing how to install and use PostgreSQL through the official documentation. The PostgreSQL community provides many helpful places to become familiar with the technology, discover how it works, and find career opportunities. Reach out to the community here.

Latest Releases

The PostgreSQL Global Development Group has released an update to all supported versions of PostgreSQL, including 14.5, 13.8, 12.12, 11.17, and 10.22, as well as the third beta release of PostgreSQL 15. This release closes one security vulnerability and fixes over 40 bugs reported over the last three months.

For the full list of changes, please review the release notes.

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 15 on your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 15 Beta 3 in production environments, we encourage you to find ways to run your typical application workloads against this beta release.

PostgreSQL 10 will stop receiving fixes on November 10, 2022. If you are running PostgreSQL 10 in a production environment, we strongly advise that you make plans to upgrade to a newer, supported version of PostgreSQL so you can continue to receive bug and security fixes. Please see our versioning policy for more information.

Upcoming Events

indicates that an event is recognised under the community event guidelines and is directly helping the PostgreSQL community.

Mailing Lists

The PostgreSQL mailing lists enable you to interact with active community participants on subjects related to the development of PostgreSQL, discovering how to use PostgreSQL, or learning about upcoming events and product releases.

In order to manage your mailing list subscription, you need a PostgreSQL community account. Signing up is easy and gives you direct access to the global PostgreSQL community.

Learning Opportunities Ahead

Want to learn more about PostgreSQL and help build the community? Come to one of the many events, local user groups, & training sessions where you can meet experienced PostgreSQL users and enhance your database skills.

Latest News

PostgreSQL 14.5, 13.8, 12.12, 11.17, 10.22, and 15 Beta 3 Released!

The PostgreSQL Global Development Group has released an update to all supported versions of PostgreSQL, including 14.5, 13.8, 12.12, 11.17, and 10.22, as well as the third beta release of PostgreSQL 15. This release closes one security vulnerability and fixes over 40 bugs reported over the last three months.

For the full list of changes, please review the release notes.

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 15 on your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 15 Beta 3 in production environments, we encourage you to find ways to run your typical application workloads against this beta release.

PostgreSQL 10 will stop receiving fixes on November 10, 2022. If you are running PostgreSQL 10 in a production environment, we strongly advise that you make plans to upgrade to a newer, supported version of PostgreSQL so you can continue to receive bug and security fixes. Please see our versioning policy for more information.

Источник

FAQ/ru

Перевод на русский язык выполнил Виктор Вислобоков (corochoone@gmail.com) 03.12.2010 ([1])

Contents

Переводы на другие языки

Вопросы специфичные для платформы

Пользователи Windows также должны прочитать platform FAQ for Windows. А также FAQs for other platforms.

Общие вопросы

Что такое PostgreSQL? Как произносится это название? Что такое Postgres?

PostgreSQL произносится Post-Gres-Q-L (Пост-Грес-Кью-Эл). (Для особо любопытствующих как произносить «PostgreSQL», существует аудиофайл audio file.)

Разработку PostgreSQL выполняет команда разработчиков, разбросанная по всему миру и связанная через Интернет. Разработка является общественным проектом и не управляется какой-либо компанией. Подробности смотрите в FAQ для разработчиков, FAQ для разработчиков.

Postgres — это широко используемое сокращение для PostgreSQL. Первоначальным именем проекта в Беркли было Postgres и этот ник теперь наиболее популярен в разговорах о PostgreSQL по сравнению с другими. Если вам трудно полностью проговаривать ‘PostgreSQL’, можно просто говорить ‘Postgres’.

Кто управляет PostgreSQL?

Что представляет из себя Всемирная Группа Разработчиков PostgreSQL (PGDG)?

Что представляет из себя PostgreSQL Core Team?

Это комитет, состоящий из 5-7 членов (в настоящий момент 6), которые внесли наибольший вклад в PostgreSQL и которые: (а) устанавливают даты выпусков, (b) управляют конфиденциальными вопросами проекта, (c) общаются как спикеры с PGDG, когда это требуется и (d) выступают арбитрами в тех вопросах, по которым в сообществе не достигнуто консенсуса. В настоящий момент Основная Команда представлена вверху страницы списка участников

Что можно сказать о различных фондах PostgreSQL?

Хотя проект PostgreSQL использует некоммерческие фирмы в США, Европе, Бразилии и Японии для координации проектом и сбора средств, эти фирмы не являются владельцами кода PostgreSQL.

Какова лицензия на PostgreSQL?

PostgreSQL распространяется по лицензии сходной с BSD и MIT. В своей основе она позволяет пользователям делать с кодом всё что угодно, включая перепродажу скомпилированных файлов без исходного кода. Единственное ограничение состоит в том, что вы не можете возложить на нас юридическую ответственность за проблемы с этим программным обеспечением. Также существует требование о том, что все копии данного программного обеспечения должны включать в себя данные сведения об авторских правах. Вот лицензия, которую мы используем:

На каких платформах работает PostgreSQL?

Обычно, PostgreSQL может работать на любой современной платформе совместимой с Unix.

Платформы, которые прошли явное тестирование перечислены на сайте Build farm. Документацию, которая содержит множество подробностей о поддерживаемых платформах можно найти на http://www.postgresql.org/docs/current/static/supported-platforms.html.

PostgreSQL также работает на операционных системах Microsoft Windows, основанных на NT, таких как Win2000 SP4, WinXP и Win2003. Пакет инсталлятора доступен по адресу http://www.postgresql.org/download/windows. Версии Windows, основанные на MS-DOS (Win95, Win98, WinMe) могут запускать PostgreSQL с помощью Cygwin.

Где можно взять PostgreSQL?

Дистрибутивы в скомпилированном виде для разных операционных систем и платформ; см. страницу скачивания.

Исходный код можно получить через веб-браузер или по ftp.

Какая на данный момент наиболее свежая версия?

Последнюю версию PostgreSQL можно узнать перейдя на главную страницу нашего сайта.

Обычно мы выпускаем новые старшие версии каждый год, а младшие версии каждые несколько месяцев. Младшие версии обычно выпускаются в одно и то же время для всех поддерживаемых старших версий. Больше информации о старших и младших версиях можно найти на http://www.postgresql.org/support/versioning.

Где получить поддержку?

Сообщество PostgreSQL предоставляет помощь множеству пользователей через E-mail. Основной web-сайт для подписки на списки рассылки по E-mail это: http://www.postgresql.org/community/lists/. Хорошим местом для того, чтобы начать задавать вопросы являются списки general (общие вопросы) или bugs (ошибки). Для наилучших результатов, прочитайте guide to reporting problems перед тем как отправлять сообщение об ошибке, чтобы убедиться, что вы включили всю необходимую информацию для того, чтобы вам помогли.

Главным IRC каналом является #postgreql, расположенный на сервере Libera (irc.libera.chat). На этом же сервере существуют каналы на испанском (#postgresql-es), французском (#postgresqlfr) и бразильском (#postgresql-br) языках. Также существует канал по PostgreSQL на сервере EFNet.

Как мне сообщить об ошибке?

Посетите страничку со специальной формой отчёта об ошибке в PostgreSQL по адресу: http://www.postgresql.org/support/submitbug, чтобы отправить сообщение об ошибке в список рассылки pgsql-bugs. Также проверьте наличие более свежей версии PostgreSQL на нашем FTP сайте ftp://ftp.postgresql.org/pub/.

Чтобы получить полезный и информативный ответ важно, чтобы вы прочитали guide to reporting problems, чтобы убедиться, что вы включили в ваше сообщение всю информацию, которая требуется для полного понимания и работы над вашим сообщением.

На ошибки, уведомления о которых были сделаны через специальную форму или отправленные в какой-либо список рассылки PostgreSQL, обычно генерируется один из следующих ответов:

Как найти информацию об известных ошибках или отсутствующих возможностях?

PostgreSQL поддерживает расширенный подкласс SQL:2008. См. список TODO на предмет известных ошибок, отсутствующих возможностей и будущих планов.

На запрос какой-либо возможности обычно приходят следующие ответы:

Ошибка, с которой я столкнулся исправлена в новой старшей версии PostgreSQL, но я не могу обновляться. Могу ли я получить патч, чтобы решить проблему?

Нет. Никто не будет делать специальный патч для вас, извлекая исправление, например из 8.4.3, чтобы применить его к 8.4.1. Это потому, что никогда не должно быть необходимости делать это.

PostgreSQL имеет строгую политику, по которой исправление ошибок осуществляется только в текущих выпусках, согласно политике версий. Это позволяет безопасно обновляться, например, с 8.4.1 до 8.4.3.

Поддерживается совместимость на бинарном (двоичном) уровне, не требуется выполнять дамп и восстановление, ничего не будет разрушено, но ошибки, которые могут вызывать проблемы, исправляются. Даже, если вы никогда не сталкивались с какой-то конкретной ошибкой, это может произойти в будущем и таким образом имеет смысл оперативно обновляться. Для этого просто нужно установить обновление и перезапустить сервер баз данных, ничего более.

Обновление с 8.3 до 8.4 или с 8.4 до 9.0 является обновлением старшей версии и не может производиться также. Однако, если ошибка найдена в 9.0, то она обычно исправляется во всех обслуживаемых старых версиях, т.е. в 8.4 и 8.3, если это безопасно и имеет практическую пользу.

Таким образом, если у вас работает версия 8.1.0, то выполнение обновления до 8.1.21 сильно рекомендуется и является безопасным. С другой стороны, обновление на следующую старшую версию, т.е. 8.2.x, может потребовать изменений в вашем приложении и потребует выполнения дампа, восстановления и перезагрузки.

Если вы хотите быть осторожным при любых обновлениях, вам необходимо прочитать замечания к выпуску для каждого выпуска между текущим и последней младшей версией. Если у вас просто параноидальная боязнь обновлений, вы можете получить исходный код для каждого списка изменений, внесённых в выпуски из PostgreSQL’s репозитория git и опробовать его.

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

У меня есть программа, которая хочет версию PostgreSQL x.y.1. Могу я использовать вместо этого версию PostgreSQL x.y.2?

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

Подробности смотрите в предыдущем вопросе.

Какая документация имеется в наличии?

PostgreSQL содержит много документации, включая большое руководство, страницы электронного руководства man и некоторые маленькие тестовые примеры. Смотрите в каталог /doc. Вы также можете просматривать документацию в Интернет по адресу http://www.postgresql.org/docs.

Коллекцию технических статей по PostgreSQL вы можете найти в wiki.

Как мне научиться SQL?

Во-первых, возьмите одну из книг по PostgreSQL, о которых говорилось выше. Многим из наших пользователей также нравится книга The Practical SQL Handbook, Bowman, Judith S., et al., Addison-Wesley. Другим нравится The Complete Reference SQL, Groff et al., McGraw-Hill.

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

Существует также множество прекрасных учебников доступных в online:

Как мне прислать исправление или присоединиться к команде разработчиков?

Как сравнить PostgreSQL с другими СУБД?

Существует несколько методов сравнения программного обеспечения: возможности, производительность, надежность, поддержка и цена.

Возможности

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

Производительность

Производительность PostgreSQL сходна с другими коммерческими СУБД и с СУБД с открытым исходным кодом. В каких-то вещах мы быстрее, в каких-то медленнее. Наша производительности обычно +/-10% по сравнению с другими СУБД.

Надёжность

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

Поддержка

Наши списки рассылки предоставляют возможность общения с большой группой разработчиков и пользователей, которые могут помочь решить любые возникшие проблемы. В то же время, мы не гарантируем какие-либо исправления, но и разработчики коммерческих СУБД не всегда делают исправления. Прямой доступ к разработчикам, сообществу пользователей, руководствам и исходным текстам часто делают поддержку PostgreSQL превосходящей другие СУБД. Существует коммерческая поддержка по результатам возникших инцидентов, которая доступна для тех кому она нужна. (См. вопрос 3.10).

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

Может ли PostgreSQL быть встраиваемой СУБД?

PostgreSQL разрабатывается по архитектуре клиент/сервер, которая требует отдельных процессов для каждого клиента и сервера, а также несколько вспомогательных процессов. Многие встраиваемые архитектуры могут соответствовать таким требованиям. Однако, если ваша встраиваемая архитектура требует сервер баз данных для запуска внутри прикладного процесса, вы не можете использовать Postgres и вам лучше бы выбрать для базы данных какое-либо другое облегченное решение.

Популярные встраиваемые решения включают SQLite и Firebird SQL.

Как мне отписаться от списков рассылки PostgreSQL? Как избежать получения дублирующихся сообщений?

Страница PostgreSQL Majordomo позволяет подписаться или отписаться от любых списков рассылки. (Вам может понадобиться ваш пароль в Majordomo для авторизации).

Все списки рассылки PostgreSQL настраиваются как группа, отвечающая в список и первоначальному автору E-mail сообщения. Это сделано для того, чтобы пользователи получали ответы как можно быстрее. Если вы предпочитаете не получать дублирующихся сообщений из списка в случае если вы уже получили E-mail сообщение напрямую, установите флажок eliminatecc на странице настроек Majordomo. Вы также можете предотвратить получение копий вашего же сообщения в список самому себе, сняв флажок selfcopy.

Вопросы пользователей по клиентской части

Какие интерфейсы есть для PostgreSQL?

Ядро исходных кодов PostgreSQL включает только C и встроенные (embedded) в C интерфейсы. Все другие интерфейсы являются независимыми проектами и загружаются отдельно; самостоятельность проектов позволяет им организовать собственное расписание выпусков новых версий и иметь собственную команду разработчиков.

Некоторые языки программирования, такие как PHP включают в себя интерфейс к PostgreSQL. Интерфейсы для таких языков как Perl, TCL, Python и многих других, доступны на http://pgfoundry.org.

Какие инструменты существуют для использования PostgreSQL через Web?

Прекрасное введение во взаимодействие баз данных и Web можно найти на: http://www.webreview.com

Для интеграции с Web, PHP (http://www.php.net) является неплохим интерфейсом.

В сложных случаях многие пользуются Perl и DBD::Pg с CGI.pm или mod_perl.

Есть ли у PostgreSQL графический интерфейс пользователя?

Для PostgreSQL существует большое количество инструментов с графическим интерфейсом как коммерческих, так и открытых. Подробности можно найти в Community Guide to PostgreSQL GUI Tools.

Вопросы администрирования

Как мне установить PostgreSQL в место отличное от /usr/local/pgsql?

Я установил PostgreSQL и не знаю пароль пользователя postgres

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

Как мне управлять соединениями от других компьютеров?

Как мне настроить СУБД для получения лучшей производительности?

Существует три главных области, которые потенциально могут увеличить производительность:

Изменение запроса

Это означает модификацию запросов для получения лучшей производительности:

Настройка сервера

Некоторые установки в postgresql.conf влияют на производительность. Подробный полный список установок см. в Administration Guide/Server Run-time Environment/Run-time Configuration.

Какие возможности для отладки есть в наличии?

Есть множество установок в настройках сервера, начинающихся на log_* на http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html, позволяющих протоколировать запросы и статистику работы процесса, которая очень полезна для отладки и измерения производительности.

Почему я получаю сообщение «Sorry, too many clients» когда пытаюсь подключиться к базе?

Вы достигли установленного по умолчанию ограничения в 100 сессий подключения к базе данных. Вам необходимо увеличить лимит на количество конкурентных backend процессов для вашего сервера БД, изменив значение max_connections в файле postgresql.conf и перестартовать сервер БД.

Как выполнить обновление PostgreSQL?

Будет ли PostgreSQL работать с последними изменениями в разных странах, касающимися дневного времени?

PostgreSQL, начиная с версии 8.0, зависит от базы данных часовых поясов tzdata (которая также называется базой данных zoneinfo Olson timezone database) в том, что касается информации о зимнем/летнем времени. Чтобы PostgreSQL работала с летним/зимнем временем, установите набор файлов tzdata и перезапустите сервер.

Все обновления выпусков PostgreSQL, включая последние доступные файлы tzdata, также обновляются в младших версиях для каждой старшей версии, чего обычно вполне достаточно.

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

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

Какое компьютерное «железо» я должен использовать?

Поскольку «железо» персональных компьютеров является наиболее совместимым, люди склонны верить, что такое «железо» имеет одинаковое качество. Это не так. Память ECC, SCSI и качественные материнские платы являются более надёжными и имеют более лучшую производительность, чем менее дорогое «железо». PostgreSQL будет работать на любом «железе», но если для вас важны надёжность и производительность, то с вашей стороны будет мудро поставить соответствующее «железо».

Серверы баз данных, в отличие от многих других приложений, обычно упираются в производительность подсистемы ввода/вывода и память, так что вам нужно в первую очередь уделить внимание подсистеме ввода/вывода, а затем ёмкости памяти и, наконец, производительности процессора. Например, дисковый контроллер с кэшем, который работает на резервной батарейке, часто наиболее лёгкий и правильный способ увеличить производительность сервера базы данных. Обсудить разное «железо» можно в наших списках рассылки.

Как PostgreSQL использует ресурсы процессора?

Сервер PostgreSQL основан на процессах (не тредах(нитях)) и использует один процесс операционной системы на одну сессию с базой данных. Одиночная сессия с базой данных (соединение) не может использовать более чем один процессор. Разумеется, несколько соединений автоматически распределяются по всем доступным процессорам, которые доступны операционной системе. Клиентские приложения могут легко использовать треды(нити) и создавать несколько соединений к базе данных из каждого треда.

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

Почему у PostgreSQL так много процессов, даже когда СУБД простаивает?

Как отмечено в ответе выше, PostgreSQL основан на процессах, так что он запускает один процесс postgres (или postgres.exe на Windows) на соединение. Процесс postmaster (который принимает соединения и запускает для них новые процессы postgres) запущен всегда. В дополнение, PostgreSQL обычно имеет один или более «вспомогательных» процессов, таких как сборщики статистики, фоновой записи, демон автовакуума, walsender, и т.д., и все они отображаются как экземпляры «postgres» в большинстве инструментов системного мониторинга.

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

Почему PostgreSQL использует так много памяти?

Несмотря на видимость это абсолютно нормально и на самом деле PostgreSQL используется не так много памяти как показывают инструменты типа top или монитора процессов в Windows.

Такие инструменты как top и монитор процессов Windows могут показывать множество экземпляров postgres (см. выше), каждый из которых использует огромное количество памяти. Часто, если просуммировать всю эту память, окажется что её больше, чем фактически установлено всего памяти на компьютере!

Вопросы эксплуатации

Как выполнить SELECT только для нескольких первых строчек запроса? Произвольной строки?

Для получения только нескольких строк, если вы знаете их количество на момент выполнения SELECT, используйте LIMIT. Если есть какой-либо индекс, который совпадает с ORDER BY, то возможно, что весь запрос выполнен и не будет. Если вы не знаете количества необходимых строк на момент выполнения SELECT, используйте курсор и FETCH.

Для выбора случайной строки с помощью SELECT используйте:

Подробности на эту тему см. также в blog entry by Andrew Gierth.

Как мне найти какие таблицы, индексы, базы данных и пользователи существуют? Как мне увидеть запросы, которые использует psql для получения этой информации?

Также существуют системные таблицы, начинающиеся с pg_, в которых есть эта же информация.

Также посмотрите файл pgsql/src/tutorial/syscat.source. Он показывает многие из операторов SELECT необходимых для получения информации из системных таблиц базы данных.

Как изменить тип данных колонки?

В 8.0 и более поздних версиях, изменение типа колонки выполняется очень легко через ALTER TABLE ALTER COLUMN TYPE.

В более ранних версиях сделайте так:

Чтобы освободить дисковое пространство, использованное устаревшими строками, вы можете затем захотеть воспользоваться командой VACUUM FULL.

Каковы максимальные размеры для строк в таблице, таблиц и базы данных?

Существуют следующие ограничения:

Разумеется, понятие «неограничено» на самом деле ограничивается доступным дисковым пространством и размерами памяти/своппинга. Когда значения, перечисленные выше, неоправданно большие, может пострадать производительность.

Максимальный размер таблицы в 32 TB не требует, чтобы операционная система поддерживала файлы больших размеров. Большие таблицы хранятся как множество файлов размером в 1 GB, так что ограничения, которые накладывает файловая система, не важны.

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

Существует ограничение, по которому индексы не могут создаваться для колонок длиннее чем 2,000 символов. К счастью такие индексы вряд ли действительно кому-то нужны. Уникальность гарантируется наилучшим образом с помощью функционального индекса из хэша MD5 длинной колонки, а полнотекстовое индексирование позволяет искать слова внутри колонки.

Как много дискового пространства в базе данных нужно для сохранения данных из обычного текстового файла?

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

В качестве примера, рассмотрим файл в 100,000 строк, в каждой из которых целое число и текстовое описание. При этом длина текста, в среднем, составляет 20 байт. Размер простого файла составит 2.8 MB. Размер базы PostgreSQL, содержащей эти же данные составит приблизительно 5.2 MB из которых:

Размер страницы данных в PostgreSQL составляет 8192 байт (8 KB), так что:

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

Значения NULL хранятся как битовые карты и поэтому они занимают очень мало места.

Обратите внимание, что значения типа long могут быть прозрачно сжаты.

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

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

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

Обычно индексы не используются для ORDER BY или для выполнения соединений таблиц. Последовательный перебор, следующий за явной сортировкой, обычно быстрее, чем поиск по индексам в большой таблице. Однако, ORDER BY часто комбинируется с LIMIT и в этом случае индекс будет использоваться, поскольку при выполнении будет возвращаться небольшая часть таблицы.

Если вам кажется, что оптимизатор некорректно выбирает последовательный перебор, используйте SET enable_seqscan TO ‘off’ и запустите запрос снова, чтобы увидеть, действительно ли сканирование индексов быстрее.

Когда используются операции с шаблонами, например LIKE или

, индексы могут быть использованы в следующих случаях:

* не использует индексы. Вместо него используйте индексы выражений, которые описываются в

Также возможно использовать полнотекстовое индексирование для поиска слов.

Статья SlowQueryQuestions содержит несколько больше советов и примеров.

Как посмотреть на то, как оптимизатор выполняет мой запрос?

Это выполняется с помощью команды EXPLAIN. См. страницу, посвященную Using EXPLAIN.

Как мне изменить порядок сортировки текстовых данных?

PostgreSQL сортирует текстовые данные в соответствии с порядком, который определяется текущей локалью, которая была выбрана при initdb. (В версиях, начиная с 8.4, возможно выбрать другую локаль при создании новой базы данных.) Если вам не нравится порядок сортировки, вам необходимо использовать другую локаль. В частности, большинство локалей, отличных от «C» сортируют по алфавиту, что игнорирует пунктуацию и пробелы. Если это не то, что вы хотите, то вам нужна локаль «C».

Как мне выполнить поиск по регулярному выражению и регистро-независимый поиск по регулярному выражению? Как мне использовать индекс для регистро-независимого поиска?

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

* производит регистро-независимый поиск по регулярному выражению. Регистро-независимый вариант LIKE называется ILIKE.

Регистро-независимое сравнение обычно выглядит так:

Эта конструкция не будет использовать стандартный индекс на «col». Однако, если вы создадите функциональный индекс «lower(col)», он будет использован:

Если вышеуказанный индекс создаётся как UNIQUE, то колонка, для которой он создаётся может хранить символы и в верхнем, и в нижнем регистре, но индекс не может иметь идентичных значений, которые отличаются только регистром. Чтобы в колонке можно было хранить символы только в определённом регистре, используйте ограничение CHECK или проверку через триггер.

В PostgreSQL, начиная с версии 8.4, вы также можете использовать дополнительный тип данных CITEXT, который внутри реализует вызовы «lower()», так что вы можете фактически считать его полностью регистро-независимым типом данных. CITEXT также доступен для 8.3, его более ранняя версия работала только с ASCII символами и для 8.2 и более ранних версий доступна на pgFoundry.

Как мне определить, что значение поля в каком-либо запросе равно NULL? Как мне соединить возможные NULL? Могу я сортировать поля NULL или нет?

Вы можете проверять значение с помощью IS NULL или IS NOT NULL, как здесь:

Конкатенация NULL с чем-либо другим даёт другой NULL. Если это не то, что бы вам хотелось, используйте COALESCE() для замены NULL как здесь:

Чтобы отсортировать данные по значению используйте IS NULL или IS NOT NULL в выражении ORDER BY. Когда они будут генерировать значения истина, то при сортировке они будут выше, чем значения ложь, так что записи с NULL будут в отсортированном списке сверху:

В PostgreSQL, начиная с версии 8.3, вы также можете управлять порядком сортировки значений NULL, используя недавно стандартизированные модификаторы NULLS FIRST/NULLS LAST, как здесь:

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

Внутреннее имя вы можете увидеть, когда смотрите системные каталоги и в некоторых сообщениях об ошибках.

Первые четыре типа являются «varlena» типами (т.е. первые четыре байта на диске являются длинной, за которой следуют данные). Таким образом, фактически используемое пространство больше, чем обозначенный размер. Однако, длинные значения также сжимаются, так что занимаемое дисковое пространство может также быть и меньше, чем ожидалось.

Тип «char» (кавычки требуются, чтобы отличать его от CHAR(n)) является специализированным типом данных, который может хранить точно один байт. Он есть в системных каталогах, но его использование в таблицах пользователей обычно не рекомендуется.

Как мне создать серийное поле/поле с авто увеличением?

PostgreSQL поддерживает тип данных SERIAL. Фактически он не является реальным типом данных. При его использовании создаётся колонка целого типа и связанная с этой колонкой последовательность.

автоматически транслируется в:

Также существует тип BIGSERIAL, который похож на SERIAL, за исключением того, что колонка создаётся с типом BIGINT, а не INTEGER. Используйте этот тип, если вы считаете, что вам будет нужно более чем 2 миллиарда серийных значений в таблице.

Как мне получить значение при вставке SERIAL?

Простейший способ получить назначенное значение SERIAL это использовать RETURNING. Используя в качестве примера таблицу из прошлого вопроса, это будет выглядеть так:

Вы также можете вызвать функцию nextval() и использовать полученное значение в INSERT, или вызвать функцию currval() после INSERT.

Не может ли получиться так, что использование currval() и nextval() приведет к зациклированию с другими пользователями?

Почему числа из моей последовательности не используются снова при отмене транзакции? Почему создаются разрывы при нумерации в колонке, где я использую последовательность/SERIAL?

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

Что такое OID?

Для уникального значения в строках таблицы пользователя, лучшим способом является создание уникального индекса на колонку OID (но обратите внимание, что выражение WITH OIDS само по себе не создаёт такой индекс).

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

PostgreSQL использует OID для идентификаторов объектов в системных каталогах, где вышеописанное ограничение размера не может создавать проблему.

Для уникальной нумерации строк в таблицах пользователей, наилучшим решением будет использование типа SERIAL, вместо колонки OID или BIGSERIAL, если ожидается, что таблица будет иметь более 2-х миллиардов записей.

Что такое CTID?

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

Почему я получаю ошибку «ERROR: Memory exhausted in AllocSetAlloc()»?

Предположительно у вас закончилась виртуальная память или что ваше ядро имеет маленький лимит на определенные ресурсы. Попытайтесь перед запуском сервера БД выполнить следующие команды:

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

Как мне узнать, какая версия PostgreSQL запущена?

Из psql, наберите SELECT version();

Существует ли какой-либо способ аудита операций с базой данных?

Ничего встроенного нет, но не так уж и трудно сделать такую возможность для себя.

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

Как мне выполнить внешнее соединение таблиц?

PostgreSQL поддерживает внешние соединения, используя стандартный синтаксис SQL. Вот два примера:

Это идентичные запросы соединения t1.col и t2.col, также возвращают любые несоединённые строки из t1 (которые не совпадают с t2). RIGHT соединение должно добавить несоединённые строки из t2. FULL соединение должно возвратить совпавшие строки плюс все несоединённые строки из t1 и t2. Слово OUTER является необязательным и назначается в LEFT, RIGHT и FULL соединениях. Обычные соединения называются INNER соединениями.

Как выполнять запросы, использующие несколько баз данных?

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

contrib/dblink позволяет запросы между базами, используя вызовы функций. Разумеется, клиент может одновременно также устанавливать соединения с различными базами данных и таких образом объединять информацию из них.

Как мне вернуть из функции несколько строк таблицы?

Вы можете легко использовать функции, возвращающие список, Return more than one row of data from PL/pgSQL functions.

Почему я получаю ошибку «relation with OID #### не существует», когда обращаюсь к временным таблицам в функциях PL/PgSQL?

В PostgreSQL до версии 8.3, PL/PgSQL кэширует сценарии функции и один из негативных эффектов этого состоит в том, что если функция PL/PgSQL обращается к временной таблице и эта таблица позднее удаляется и пересоздается, а функция затем вызывается снова, то ее вызов приведет к ошибке, потому что скэшированное содержимое функции содержит указатель на старую временную таблицу. Чтобы решить эту проблему, используйте EXECUTE для доступа к временным таблицам в PL/PgSQL. Использование этого оператора заставит запрос перегенерироваться каждый раз.

В PostgreSQL 8.3 и позднее, этой проблемы нет.

Какие есть решения для репликации?

Репликация Master/slave позволяет иметь один главный (master) сервер для выполнения запросов чтения/записи, в то время как подчинённые (slave) сервера могут производить только запросы чтения/SELECT. Наиболее популярным решением для репликации master-slave в PostgreSQL является Slony-I.

Репликация Multi-master позволяет выполнять запросы чтения/записи на нескольких, реплицируемых друг с другом компьютерах. Эта особенность также приводит к потере производительности, потому что необходима синхронизация изменений между несколькими серверами. Наиболее популярным решением для такой репликации в PostgreSQL является PGcluster.

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

Возможно ли создать кластер серверов PostgreSQL с разделяемым устройством хранения?

PostgreSQL не поддерживает кластеризацию, используя shared storage на SAN, SCSI-платах, iSCSI томах или других разделяемых носителях. Кластеры такого типа как «RAC-style» не поддерживаются. В настоящий момент поддерживаются только кластеры основанные на репликации.

Использование разделяемых устройств хранения в режиме ‘failover’ возможно, но не безопасно, так как в одно и тоже время более запущен и имеет доступ одним и тем же данным более чем один процесс postmaster. Рекомендуется Heartbeat и STONITH или какие-либо другие жёсткие решения по разрыву соединения.

Почему имена таблицы и колонок не распознаются в в моём запросе? Почему не сохраняются заглавные буквы?

Наиболее часто имена не распознаются из-за использования двойных кавычек в имени таблицы или колонки при создании таблицы. При использовании двойных кавычек, имя таблицы и колонки (которые называют идентификаторами) сохраняются в регистро-зависимом виде; это означает, что вы должны использовать двойные кавычки, когда указываете эти имена в запросе. Некоторые интерфейсы, такие как pgAdmin, во время создания таблицы добавляют двойные кавычки автоматически. Таким образом, чтобы идентификаторы распознавались вы должны следовать одному из следующих правил:

Я потерял пароль от базы данных. Как я могу его восстановить?

Никак. Однако, вы можете сбросить этот пароль в какое-либо значение. Чтобы сделать это, вы должны

Есть ли в PostgreSQL хранимые процедуры?

В чистом виде нет. Однако PostgreSQL имеет очень мощные функции и определяемые пользователями функции, которые в своём большинстве совместимы с тем, что в других СУБД называют хранимыми процедурами (процедурами и функциями), а в некоторых случаях они могут больше.

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

Функции могут быть вызваны несколькими способами. Если вы хотите вызвать функцию также как хранимую процедуру в других СУБД (обычно функцию выполняющую что-либо, но о получении результатов работы которой вы не волнуетесь, потому что она ничего не возвращает), то один из способов может быть использование языка PL/pgSQL для вашей процедуры и команды PERFORM. Например:

Обратите внимание, что если вместо этого вы вызовите:

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

Для того, чтобы избежать получения такого ненужного результата, можно использовать PERFORM.

Основные ограничения хранимых функций в PostgreSQL по сравнению с настоящими хранимыми процедурами это:

Почему не работают BEGIN, ROLLBACK и COMMIT внутри хранимых процедур/функций?

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

Если вам нужна хранимая процедура для управления транзакциями, вы можете посмотреть на интерфейс dblink или делать необходимую работу с помощью скрипта на клиенте. В некоторых случаях, вы можете сделать то, что вам нужно, используя блоки исключений в PL/PgSQL, потому что каждый блок BEGIN/EXCEPTION/END создаёт подтранзакцию.

Почему «SELECT count(*) FROM bigtable;» работает медленно?

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

Вы можете получить некоторую оценку. Колонка reltuples в таблице pg_class содержит информацию из результата выполнения последнего оператора ANALYZE на эту таблицу. На большой таблице, точность этого значения составляет тысячные доли процента, что вполне достаточно для многих целей.

«Точный» результат count, часто не будет точным долгое время в любом случае; из-за конкурентности MVCC, count будет точным только на момент вызова запущенного запроса SELECT count(*) (или ограничиваться уровнями изоляции транзакций данной транзакции), и может потерять актуальность уже в момент завершения запроса. При постоянной работе транзакций, изменяющий таблицу, два вызова count(*), которые завершатся в одно и то же время могут показать разные значения, если изменяющая транзакция завершилась между их вызовами.

Почему мой запрос намного медленнее, чем когда он запускается в виде подготовленного запроса?

Когда PostgreSQL получает полный запрос со всеми параметрами, он может использовать статистику таблицы, чтобы понять являются ли значения, используемые в запросе, часто употребимыми или часто неупотребимыми в какой-либо колонке. Это позволяет изменить способ извлечения данных на более эффективный, так как известно ожидается ли очень много или очень мало результатов из определённой части запроса. Например, PostgreSQL может выбрать последовательное сканирование вместо использования индекса, если осуществляется поиск ‘active=y’ и при этом известно, что 99% записей в таблице имеют ‘active=y’, поскольку в этом случае последовательное сканирование будет намного быстрее.

В подготовленном запросе, PostgreSQL не получает значение всех параметров, когда создаёт план запроса. Он должен попытаться создать «безопасный» план, который должен работать хорошо вне зависимости от того какое значение вы предоставите как параметр, когда вы вызовите подготовленный запрос. К сожалению, такой план может не оказаться очень хорошим, если значение, которое вы предоставили гораздо более употребительно или гораздо менее употребительно, чем среднее арифметическое из выбираемых значений в таблице.

Иногда люди, у которых возникает эта проблема пытаются использовать подготовленные запросы как меру безопасности для предотвращения SQL иньекций, а не как инструмент тонкой настройки производительности тяжёлых запросов, часто запускающихся с различными параметрами. Такие люди должны подумать на использованием подготовленных операторов на стороне клиента, если их клиентский интерфейс (например PgJDBC) их поддерживает.

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

См. Using_EXPLAIN. Если вы собираетесь обратится за помощью в список рассылки, прочтите Guide to reporting problems.

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

Источник

Часто Задаваемые Вопросы

Перевод на русский язык выполнил Виктор Вислобоков (corochoone@gmail.com) 03.12.2010 ([1])

Contents

Переводы на другие языки

Вопросы специфичные для платформы

Пользователи Windows также должны прочитать platform FAQ for Windows. А также FAQs for other platforms.

Общие вопросы

Что такое PostgreSQL? Как произносится это название? Что такое Postgres?

PostgreSQL произносится Post-Gres-Q-L (Пост-Грес-Кью-Эл). (Для особо любопытствующих как произносить «PostgreSQL», существует аудиофайл audio file.)

Разработку PostgreSQL выполняет команда разработчиков, разбросанная по всему миру и связанная через Интернет. Разработка является общественным проектом и не управляется какой-либо компанией. Подробности смотрите в FAQ для разработчиков, FAQ для разработчиков.

Postgres — это широко используемое сокращение для PostgreSQL. Первоначальным именем проекта в Беркли было Postgres и этот ник теперь наиболее популярен в разговорах о PostgreSQL по сравнению с другими. Если вам трудно полностью проговаривать ‘PostgreSQL’, можно просто говорить ‘Postgres’.

Кто управляет PostgreSQL?

Что представляет из себя Всемирная Группа Разработчиков PostgreSQL (PGDG)?

Что представляет из себя PostgreSQL Core Team?

Это комитет, состоящий из 5-7 членов (в настоящий момент 6), которые внесли наибольший вклад в PostgreSQL и которые: (а) устанавливают даты выпусков, (b) управляют конфиденциальными вопросами проекта, (c) общаются как спикеры с PGDG, когда это требуется и (d) выступают арбитрами в тех вопросах, по которым в сообществе не достигнуто консенсуса. В настоящий момент Основная Команда представлена вверху страницы списка участников

Что можно сказать о различных фондах PostgreSQL?

Хотя проект PostgreSQL использует некоммерческие фирмы в США, Европе, Бразилии и Японии для координации проектом и сбора средств, эти фирмы не являются владельцами кода PostgreSQL.

Какова лицензия на PostgreSQL?

PostgreSQL распространяется по лицензии сходной с BSD и MIT. В своей основе она позволяет пользователям делать с кодом всё что угодно, включая перепродажу скомпилированных файлов без исходного кода. Единственное ограничение состоит в том, что вы не можете возложить на нас юридическую ответственность за проблемы с этим программным обеспечением. Также существует требование о том, что все копии данного программного обеспечения должны включать в себя данные сведения об авторских правах. Вот лицензия, которую мы используем:

На каких платформах работает PostgreSQL?

Обычно, PostgreSQL может работать на любой современной платформе совместимой с Unix.

Платформы, которые прошли явное тестирование перечислены на сайте Build farm. Документацию, которая содержит множество подробностей о поддерживаемых платформах можно найти на http://www.postgresql.org/docs/current/static/supported-platforms.html.

PostgreSQL также работает на операционных системах Microsoft Windows, основанных на NT, таких как Win2000 SP4, WinXP и Win2003. Пакет инсталлятора доступен по адресу http://www.postgresql.org/download/windows. Версии Windows, основанные на MS-DOS (Win95, Win98, WinMe) могут запускать PostgreSQL с помощью Cygwin.

Где можно взять PostgreSQL?

Дистрибутивы в скомпилированном виде для разных операционных систем и платформ; см. страницу скачивания.

Исходный код можно получить через веб-браузер или по ftp.

Какая на данный момент наиболее свежая версия?

Последнюю версию PostgreSQL можно узнать перейдя на главную страницу нашего сайта.

Обычно мы выпускаем новые старшие версии каждый год, а младшие версии каждые несколько месяцев. Младшие версии обычно выпускаются в одно и то же время для всех поддерживаемых старших версий. Больше информации о старших и младших версиях можно найти на http://www.postgresql.org/support/versioning.

Где получить поддержку?

Сообщество PostgreSQL предоставляет помощь множеству пользователей через E-mail. Основной web-сайт для подписки на списки рассылки по E-mail это: http://www.postgresql.org/community/lists/. Хорошим местом для того, чтобы начать задавать вопросы являются списки general (общие вопросы) или bugs (ошибки). Для наилучших результатов, прочитайте guide to reporting problems перед тем как отправлять сообщение об ошибке, чтобы убедиться, что вы включили всю необходимую информацию для того, чтобы вам помогли.

Главным IRC каналом является #postgreql, расположенный на сервере Libera (irc.libera.chat). На этом же сервере существуют каналы на испанском (#postgresql-es), французском (#postgresqlfr) и бразильском (#postgresql-br) языках. Также существует канал по PostgreSQL на сервере EFNet.

Как мне сообщить об ошибке?

Посетите страничку со специальной формой отчёта об ошибке в PostgreSQL по адресу: http://www.postgresql.org/support/submitbug, чтобы отправить сообщение об ошибке в список рассылки pgsql-bugs. Также проверьте наличие более свежей версии PostgreSQL на нашем FTP сайте ftp://ftp.postgresql.org/pub/.

Чтобы получить полезный и информативный ответ важно, чтобы вы прочитали guide to reporting problems, чтобы убедиться, что вы включили в ваше сообщение всю информацию, которая требуется для полного понимания и работы над вашим сообщением.

На ошибки, уведомления о которых были сделаны через специальную форму или отправленные в какой-либо список рассылки PostgreSQL, обычно генерируется один из следующих ответов:

Как найти информацию об известных ошибках или отсутствующих возможностях?

PostgreSQL поддерживает расширенный подкласс SQL:2008. См. список TODO на предмет известных ошибок, отсутствующих возможностей и будущих планов.

На запрос какой-либо возможности обычно приходят следующие ответы:

Ошибка, с которой я столкнулся исправлена в новой старшей версии PostgreSQL, но я не могу обновляться. Могу ли я получить патч, чтобы решить проблему?

Нет. Никто не будет делать специальный патч для вас, извлекая исправление, например из 8.4.3, чтобы применить его к 8.4.1. Это потому, что никогда не должно быть необходимости делать это.

PostgreSQL имеет строгую политику, по которой исправление ошибок осуществляется только в текущих выпусках, согласно политике версий. Это позволяет безопасно обновляться, например, с 8.4.1 до 8.4.3.

Поддерживается совместимость на бинарном (двоичном) уровне, не требуется выполнять дамп и восстановление, ничего не будет разрушено, но ошибки, которые могут вызывать проблемы, исправляются. Даже, если вы никогда не сталкивались с какой-то конкретной ошибкой, это может произойти в будущем и таким образом имеет смысл оперативно обновляться. Для этого просто нужно установить обновление и перезапустить сервер баз данных, ничего более.

Обновление с 8.3 до 8.4 или с 8.4 до 9.0 является обновлением старшей версии и не может производиться также. Однако, если ошибка найдена в 9.0, то она обычно исправляется во всех обслуживаемых старых версиях, т.е. в 8.4 и 8.3, если это безопасно и имеет практическую пользу.

Таким образом, если у вас работает версия 8.1.0, то выполнение обновления до 8.1.21 сильно рекомендуется и является безопасным. С другой стороны, обновление на следующую старшую версию, т.е. 8.2.x, может потребовать изменений в вашем приложении и потребует выполнения дампа, восстановления и перезагрузки.

Если вы хотите быть осторожным при любых обновлениях, вам необходимо прочитать замечания к выпуску для каждого выпуска между текущим и последней младшей версией. Если у вас просто параноидальная боязнь обновлений, вы можете получить исходный код для каждого списка изменений, внесённых в выпуски из PostgreSQL’s репозитория git и опробовать его.

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

У меня есть программа, которая хочет версию PostgreSQL x.y.1. Могу я использовать вместо этого версию PostgreSQL x.y.2?

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

Подробности смотрите в предыдущем вопросе.

Какая документация имеется в наличии?

PostgreSQL содержит много документации, включая большое руководство, страницы электронного руководства man и некоторые маленькие тестовые примеры. Смотрите в каталог /doc. Вы также можете просматривать документацию в Интернет по адресу http://www.postgresql.org/docs.

Коллекцию технических статей по PostgreSQL вы можете найти в wiki.

Как мне научиться SQL?

Во-первых, возьмите одну из книг по PostgreSQL, о которых говорилось выше. Многим из наших пользователей также нравится книга The Practical SQL Handbook, Bowman, Judith S., et al., Addison-Wesley. Другим нравится The Complete Reference SQL, Groff et al., McGraw-Hill.

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

Существует также множество прекрасных учебников доступных в online:

Как мне прислать исправление или присоединиться к команде разработчиков?

Как сравнить PostgreSQL с другими СУБД?

Существует несколько методов сравнения программного обеспечения: возможности, производительность, надежность, поддержка и цена.

Возможности

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

Производительность

Производительность PostgreSQL сходна с другими коммерческими СУБД и с СУБД с открытым исходным кодом. В каких-то вещах мы быстрее, в каких-то медленнее. Наша производительности обычно +/-10% по сравнению с другими СУБД.

Надёжность

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

Поддержка

Наши списки рассылки предоставляют возможность общения с большой группой разработчиков и пользователей, которые могут помочь решить любые возникшие проблемы. В то же время, мы не гарантируем какие-либо исправления, но и разработчики коммерческих СУБД не всегда делают исправления. Прямой доступ к разработчикам, сообществу пользователей, руководствам и исходным текстам часто делают поддержку PostgreSQL превосходящей другие СУБД. Существует коммерческая поддержка по результатам возникших инцидентов, которая доступна для тех кому она нужна. (См. вопрос 3.10).

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

Может ли PostgreSQL быть встраиваемой СУБД?

PostgreSQL разрабатывается по архитектуре клиент/сервер, которая требует отдельных процессов для каждого клиента и сервера, а также несколько вспомогательных процессов. Многие встраиваемые архитектуры могут соответствовать таким требованиям. Однако, если ваша встраиваемая архитектура требует сервер баз данных для запуска внутри прикладного процесса, вы не можете использовать Postgres и вам лучше бы выбрать для базы данных какое-либо другое облегченное решение.

Популярные встраиваемые решения включают SQLite и Firebird SQL.

Как мне отписаться от списков рассылки PostgreSQL? Как избежать получения дублирующихся сообщений?

Страница PostgreSQL Majordomo позволяет подписаться или отписаться от любых списков рассылки. (Вам может понадобиться ваш пароль в Majordomo для авторизации).

Все списки рассылки PostgreSQL настраиваются как группа, отвечающая в список и первоначальному автору E-mail сообщения. Это сделано для того, чтобы пользователи получали ответы как можно быстрее. Если вы предпочитаете не получать дублирующихся сообщений из списка в случае если вы уже получили E-mail сообщение напрямую, установите флажок eliminatecc на странице настроек Majordomo. Вы также можете предотвратить получение копий вашего же сообщения в список самому себе, сняв флажок selfcopy.

Вопросы пользователей по клиентской части

Какие интерфейсы есть для PostgreSQL?

Ядро исходных кодов PostgreSQL включает только C и встроенные (embedded) в C интерфейсы. Все другие интерфейсы являются независимыми проектами и загружаются отдельно; самостоятельность проектов позволяет им организовать собственное расписание выпусков новых версий и иметь собственную команду разработчиков.

Некоторые языки программирования, такие как PHP включают в себя интерфейс к PostgreSQL. Интерфейсы для таких языков как Perl, TCL, Python и многих других, доступны на http://pgfoundry.org.

Какие инструменты существуют для использования PostgreSQL через Web?

Прекрасное введение во взаимодействие баз данных и Web можно найти на: http://www.webreview.com

Для интеграции с Web, PHP (http://www.php.net) является неплохим интерфейсом.

В сложных случаях многие пользуются Perl и DBD::Pg с CGI.pm или mod_perl.

Есть ли у PostgreSQL графический интерфейс пользователя?

Для PostgreSQL существует большое количество инструментов с графическим интерфейсом как коммерческих, так и открытых. Подробности можно найти в Community Guide to PostgreSQL GUI Tools.

Вопросы администрирования

Как мне установить PostgreSQL в место отличное от /usr/local/pgsql?

Я установил PostgreSQL и не знаю пароль пользователя postgres

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

Как мне управлять соединениями от других компьютеров?

Как мне настроить СУБД для получения лучшей производительности?

Существует три главных области, которые потенциально могут увеличить производительность:

Изменение запроса

Это означает модификацию запросов для получения лучшей производительности:

Настройка сервера

Некоторые установки в postgresql.conf влияют на производительность. Подробный полный список установок см. в Administration Guide/Server Run-time Environment/Run-time Configuration.

Какие возможности для отладки есть в наличии?

Есть множество установок в настройках сервера, начинающихся на log_* на http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html, позволяющих протоколировать запросы и статистику работы процесса, которая очень полезна для отладки и измерения производительности.

Почему я получаю сообщение «Sorry, too many clients» когда пытаюсь подключиться к базе?

Вы достигли установленного по умолчанию ограничения в 100 сессий подключения к базе данных. Вам необходимо увеличить лимит на количество конкурентных backend процессов для вашего сервера БД, изменив значение max_connections в файле postgresql.conf и перестартовать сервер БД.

Как выполнить обновление PostgreSQL?

Будет ли PostgreSQL работать с последними изменениями в разных странах, касающимися дневного времени?

PostgreSQL, начиная с версии 8.0, зависит от базы данных часовых поясов tzdata (которая также называется базой данных zoneinfo Olson timezone database) в том, что касается информации о зимнем/летнем времени. Чтобы PostgreSQL работала с летним/зимнем временем, установите набор файлов tzdata и перезапустите сервер.

Все обновления выпусков PostgreSQL, включая последние доступные файлы tzdata, также обновляются в младших версиях для каждой старшей версии, чего обычно вполне достаточно.

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

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

Какое компьютерное «железо» я должен использовать?

Поскольку «железо» персональных компьютеров является наиболее совместимым, люди склонны верить, что такое «железо» имеет одинаковое качество. Это не так. Память ECC, SCSI и качественные материнские платы являются более надёжными и имеют более лучшую производительность, чем менее дорогое «железо». PostgreSQL будет работать на любом «железе», но если для вас важны надёжность и производительность, то с вашей стороны будет мудро поставить соответствующее «железо».

Серверы баз данных, в отличие от многих других приложений, обычно упираются в производительность подсистемы ввода/вывода и память, так что вам нужно в первую очередь уделить внимание подсистеме ввода/вывода, а затем ёмкости памяти и, наконец, производительности процессора. Например, дисковый контроллер с кэшем, который работает на резервной батарейке, часто наиболее лёгкий и правильный способ увеличить производительность сервера базы данных. Обсудить разное «железо» можно в наших списках рассылки.

Как PostgreSQL использует ресурсы процессора?

Сервер PostgreSQL основан на процессах (не тредах(нитях)) и использует один процесс операционной системы на одну сессию с базой данных. Одиночная сессия с базой данных (соединение) не может использовать более чем один процессор. Разумеется, несколько соединений автоматически распределяются по всем доступным процессорам, которые доступны операционной системе. Клиентские приложения могут легко использовать треды(нити) и создавать несколько соединений к базе данных из каждого треда.

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

Почему у PostgreSQL так много процессов, даже когда СУБД простаивает?

Как отмечено в ответе выше, PostgreSQL основан на процессах, так что он запускает один процесс postgres (или postgres.exe на Windows) на соединение. Процесс postmaster (который принимает соединения и запускает для них новые процессы postgres) запущен всегда. В дополнение, PostgreSQL обычно имеет один или более «вспомогательных» процессов, таких как сборщики статистики, фоновой записи, демон автовакуума, walsender, и т.д., и все они отображаются как экземпляры «postgres» в большинстве инструментов системного мониторинга.

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

Почему PostgreSQL использует так много памяти?

Несмотря на видимость это абсолютно нормально и на самом деле PostgreSQL используется не так много памяти как показывают инструменты типа top или монитора процессов в Windows.

Такие инструменты как top и монитор процессов Windows могут показывать множество экземпляров postgres (см. выше), каждый из которых использует огромное количество памяти. Часто, если просуммировать всю эту память, окажется что её больше, чем фактически установлено всего памяти на компьютере!

Вопросы эксплуатации

Как выполнить SELECT только для нескольких первых строчек запроса? Произвольной строки?

Для получения только нескольких строк, если вы знаете их количество на момент выполнения SELECT, используйте LIMIT. Если есть какой-либо индекс, который совпадает с ORDER BY, то возможно, что весь запрос выполнен и не будет. Если вы не знаете количества необходимых строк на момент выполнения SELECT, используйте курсор и FETCH.

Для выбора случайной строки с помощью SELECT используйте:

Подробности на эту тему см. также в blog entry by Andrew Gierth.

Как мне найти какие таблицы, индексы, базы данных и пользователи существуют? Как мне увидеть запросы, которые использует psql для получения этой информации?

Также существуют системные таблицы, начинающиеся с pg_, в которых есть эта же информация.

Также посмотрите файл pgsql/src/tutorial/syscat.source. Он показывает многие из операторов SELECT необходимых для получения информации из системных таблиц базы данных.

Как изменить тип данных колонки?

В 8.0 и более поздних версиях, изменение типа колонки выполняется очень легко через ALTER TABLE ALTER COLUMN TYPE.

В более ранних версиях сделайте так:

Чтобы освободить дисковое пространство, использованное устаревшими строками, вы можете затем захотеть воспользоваться командой VACUUM FULL.

Каковы максимальные размеры для строк в таблице, таблиц и базы данных?

Существуют следующие ограничения:

Разумеется, понятие «неограничено» на самом деле ограничивается доступным дисковым пространством и размерами памяти/своппинга. Когда значения, перечисленные выше, неоправданно большие, может пострадать производительность.

Максимальный размер таблицы в 32 TB не требует, чтобы операционная система поддерживала файлы больших размеров. Большие таблицы хранятся как множество файлов размером в 1 GB, так что ограничения, которые накладывает файловая система, не важны.

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

Существует ограничение, по которому индексы не могут создаваться для колонок длиннее чем 2,000 символов. К счастью такие индексы вряд ли действительно кому-то нужны. Уникальность гарантируется наилучшим образом с помощью функционального индекса из хэша MD5 длинной колонки, а полнотекстовое индексирование позволяет искать слова внутри колонки.

Как много дискового пространства в базе данных нужно для сохранения данных из обычного текстового файла?

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

В качестве примера, рассмотрим файл в 100,000 строк, в каждой из которых целое число и текстовое описание. При этом длина текста, в среднем, составляет 20 байт. Размер простого файла составит 2.8 MB. Размер базы PostgreSQL, содержащей эти же данные составит приблизительно 5.2 MB из которых:

Размер страницы данных в PostgreSQL составляет 8192 байт (8 KB), так что:

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

Значения NULL хранятся как битовые карты и поэтому они занимают очень мало места.

Обратите внимание, что значения типа long могут быть прозрачно сжаты.

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

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

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

Обычно индексы не используются для ORDER BY или для выполнения соединений таблиц. Последовательный перебор, следующий за явной сортировкой, обычно быстрее, чем поиск по индексам в большой таблице. Однако, ORDER BY часто комбинируется с LIMIT и в этом случае индекс будет использоваться, поскольку при выполнении будет возвращаться небольшая часть таблицы.

Если вам кажется, что оптимизатор некорректно выбирает последовательный перебор, используйте SET enable_seqscan TO ‘off’ и запустите запрос снова, чтобы увидеть, действительно ли сканирование индексов быстрее.

Когда используются операции с шаблонами, например LIKE или

, индексы могут быть использованы в следующих случаях:

* не использует индексы. Вместо него используйте индексы выражений, которые описываются в

Также возможно использовать полнотекстовое индексирование для поиска слов.

Статья SlowQueryQuestions содержит несколько больше советов и примеров.

Как посмотреть на то, как оптимизатор выполняет мой запрос?

Это выполняется с помощью команды EXPLAIN. См. страницу, посвященную Using EXPLAIN.

Как мне изменить порядок сортировки текстовых данных?

PostgreSQL сортирует текстовые данные в соответствии с порядком, который определяется текущей локалью, которая была выбрана при initdb. (В версиях, начиная с 8.4, возможно выбрать другую локаль при создании новой базы данных.) Если вам не нравится порядок сортировки, вам необходимо использовать другую локаль. В частности, большинство локалей, отличных от «C» сортируют по алфавиту, что игнорирует пунктуацию и пробелы. Если это не то, что вы хотите, то вам нужна локаль «C».

Как мне выполнить поиск по регулярному выражению и регистро-независимый поиск по регулярному выражению? Как мне использовать индекс для регистро-независимого поиска?

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

* производит регистро-независимый поиск по регулярному выражению. Регистро-независимый вариант LIKE называется ILIKE.

Регистро-независимое сравнение обычно выглядит так:

Эта конструкция не будет использовать стандартный индекс на «col». Однако, если вы создадите функциональный индекс «lower(col)», он будет использован:

Если вышеуказанный индекс создаётся как UNIQUE, то колонка, для которой он создаётся может хранить символы и в верхнем, и в нижнем регистре, но индекс не может иметь идентичных значений, которые отличаются только регистром. Чтобы в колонке можно было хранить символы только в определённом регистре, используйте ограничение CHECK или проверку через триггер.

В PostgreSQL, начиная с версии 8.4, вы также можете использовать дополнительный тип данных CITEXT, который внутри реализует вызовы «lower()», так что вы можете фактически считать его полностью регистро-независимым типом данных. CITEXT также доступен для 8.3, его более ранняя версия работала только с ASCII символами и для 8.2 и более ранних версий доступна на pgFoundry.

Как мне определить, что значение поля в каком-либо запросе равно NULL? Как мне соединить возможные NULL? Могу я сортировать поля NULL или нет?

Вы можете проверять значение с помощью IS NULL или IS NOT NULL, как здесь:

Конкатенация NULL с чем-либо другим даёт другой NULL. Если это не то, что бы вам хотелось, используйте COALESCE() для замены NULL как здесь:

Чтобы отсортировать данные по значению используйте IS NULL или IS NOT NULL в выражении ORDER BY. Когда они будут генерировать значения истина, то при сортировке они будут выше, чем значения ложь, так что записи с NULL будут в отсортированном списке сверху:

В PostgreSQL, начиная с версии 8.3, вы также можете управлять порядком сортировки значений NULL, используя недавно стандартизированные модификаторы NULLS FIRST/NULLS LAST, как здесь:

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

Внутреннее имя вы можете увидеть, когда смотрите системные каталоги и в некоторых сообщениях об ошибках.

Первые четыре типа являются «varlena» типами (т.е. первые четыре байта на диске являются длинной, за которой следуют данные). Таким образом, фактически используемое пространство больше, чем обозначенный размер. Однако, длинные значения также сжимаются, так что занимаемое дисковое пространство может также быть и меньше, чем ожидалось.

Тип «char» (кавычки требуются, чтобы отличать его от CHAR(n)) является специализированным типом данных, который может хранить точно один байт. Он есть в системных каталогах, но его использование в таблицах пользователей обычно не рекомендуется.

Как мне создать серийное поле/поле с авто увеличением?

PostgreSQL поддерживает тип данных SERIAL. Фактически он не является реальным типом данных. При его использовании создаётся колонка целого типа и связанная с этой колонкой последовательность.

автоматически транслируется в:

Также существует тип BIGSERIAL, который похож на SERIAL, за исключением того, что колонка создаётся с типом BIGINT, а не INTEGER. Используйте этот тип, если вы считаете, что вам будет нужно более чем 2 миллиарда серийных значений в таблице.

Как мне получить значение при вставке SERIAL?

Простейший способ получить назначенное значение SERIAL это использовать RETURNING. Используя в качестве примера таблицу из прошлого вопроса, это будет выглядеть так:

Вы также можете вызвать функцию nextval() и использовать полученное значение в INSERT, или вызвать функцию currval() после INSERT.

Не может ли получиться так, что использование currval() и nextval() приведет к зациклированию с другими пользователями?

Почему числа из моей последовательности не используются снова при отмене транзакции? Почему создаются разрывы при нумерации в колонке, где я использую последовательность/SERIAL?

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

Что такое OID?

Для уникального значения в строках таблицы пользователя, лучшим способом является создание уникального индекса на колонку OID (но обратите внимание, что выражение WITH OIDS само по себе не создаёт такой индекс).

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

PostgreSQL использует OID для идентификаторов объектов в системных каталогах, где вышеописанное ограничение размера не может создавать проблему.

Для уникальной нумерации строк в таблицах пользователей, наилучшим решением будет использование типа SERIAL, вместо колонки OID или BIGSERIAL, если ожидается, что таблица будет иметь более 2-х миллиардов записей.

Что такое CTID?

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

Почему я получаю ошибку «ERROR: Memory exhausted in AllocSetAlloc()»?

Предположительно у вас закончилась виртуальная память или что ваше ядро имеет маленький лимит на определенные ресурсы. Попытайтесь перед запуском сервера БД выполнить следующие команды:

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

Как мне узнать, какая версия PostgreSQL запущена?

Из psql, наберите SELECT version();

Существует ли какой-либо способ аудита операций с базой данных?

Ничего встроенного нет, но не так уж и трудно сделать такую возможность для себя.

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

Как мне выполнить внешнее соединение таблиц?

PostgreSQL поддерживает внешние соединения, используя стандартный синтаксис SQL. Вот два примера:

Это идентичные запросы соединения t1.col и t2.col, также возвращают любые несоединённые строки из t1 (которые не совпадают с t2). RIGHT соединение должно добавить несоединённые строки из t2. FULL соединение должно возвратить совпавшие строки плюс все несоединённые строки из t1 и t2. Слово OUTER является необязательным и назначается в LEFT, RIGHT и FULL соединениях. Обычные соединения называются INNER соединениями.

Как выполнять запросы, использующие несколько баз данных?

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

contrib/dblink позволяет запросы между базами, используя вызовы функций. Разумеется, клиент может одновременно также устанавливать соединения с различными базами данных и таких образом объединять информацию из них.

Как мне вернуть из функции несколько строк таблицы?

Вы можете легко использовать функции, возвращающие список, Return more than one row of data from PL/pgSQL functions.

Почему я получаю ошибку «relation with OID #### не существует», когда обращаюсь к временным таблицам в функциях PL/PgSQL?

В PostgreSQL до версии 8.3, PL/PgSQL кэширует сценарии функции и один из негативных эффектов этого состоит в том, что если функция PL/PgSQL обращается к временной таблице и эта таблица позднее удаляется и пересоздается, а функция затем вызывается снова, то ее вызов приведет к ошибке, потому что скэшированное содержимое функции содержит указатель на старую временную таблицу. Чтобы решить эту проблему, используйте EXECUTE для доступа к временным таблицам в PL/PgSQL. Использование этого оператора заставит запрос перегенерироваться каждый раз.

В PostgreSQL 8.3 и позднее, этой проблемы нет.

Какие есть решения для репликации?

Репликация Master/slave позволяет иметь один главный (master) сервер для выполнения запросов чтения/записи, в то время как подчинённые (slave) сервера могут производить только запросы чтения/SELECT. Наиболее популярным решением для репликации master-slave в PostgreSQL является Slony-I.

Репликация Multi-master позволяет выполнять запросы чтения/записи на нескольких, реплицируемых друг с другом компьютерах. Эта особенность также приводит к потере производительности, потому что необходима синхронизация изменений между несколькими серверами. Наиболее популярным решением для такой репликации в PostgreSQL является PGcluster.

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

Возможно ли создать кластер серверов PostgreSQL с разделяемым устройством хранения?

PostgreSQL не поддерживает кластеризацию, используя shared storage на SAN, SCSI-платах, iSCSI томах или других разделяемых носителях. Кластеры такого типа как «RAC-style» не поддерживаются. В настоящий момент поддерживаются только кластеры основанные на репликации.

Использование разделяемых устройств хранения в режиме ‘failover’ возможно, но не безопасно, так как в одно и тоже время более запущен и имеет доступ одним и тем же данным более чем один процесс postmaster. Рекомендуется Heartbeat и STONITH или какие-либо другие жёсткие решения по разрыву соединения.

Почему имена таблицы и колонок не распознаются в в моём запросе? Почему не сохраняются заглавные буквы?

Наиболее часто имена не распознаются из-за использования двойных кавычек в имени таблицы или колонки при создании таблицы. При использовании двойных кавычек, имя таблицы и колонки (которые называют идентификаторами) сохраняются в регистро-зависимом виде; это означает, что вы должны использовать двойные кавычки, когда указываете эти имена в запросе. Некоторые интерфейсы, такие как pgAdmin, во время создания таблицы добавляют двойные кавычки автоматически. Таким образом, чтобы идентификаторы распознавались вы должны следовать одному из следующих правил:

Я потерял пароль от базы данных. Как я могу его восстановить?

Никак. Однако, вы можете сбросить этот пароль в какое-либо значение. Чтобы сделать это, вы должны

Есть ли в PostgreSQL хранимые процедуры?

В чистом виде нет. Однако PostgreSQL имеет очень мощные функции и определяемые пользователями функции, которые в своём большинстве совместимы с тем, что в других СУБД называют хранимыми процедурами (процедурами и функциями), а в некоторых случаях они могут больше.

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

Функции могут быть вызваны несколькими способами. Если вы хотите вызвать функцию также как хранимую процедуру в других СУБД (обычно функцию выполняющую что-либо, но о получении результатов работы которой вы не волнуетесь, потому что она ничего не возвращает), то один из способов может быть использование языка PL/pgSQL для вашей процедуры и команды PERFORM. Например:

Обратите внимание, что если вместо этого вы вызовите:

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

Для того, чтобы избежать получения такого ненужного результата, можно использовать PERFORM.

Основные ограничения хранимых функций в PostgreSQL по сравнению с настоящими хранимыми процедурами это:

Почему не работают BEGIN, ROLLBACK и COMMIT внутри хранимых процедур/функций?

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

Если вам нужна хранимая процедура для управления транзакциями, вы можете посмотреть на интерфейс dblink или делать необходимую работу с помощью скрипта на клиенте. В некоторых случаях, вы можете сделать то, что вам нужно, используя блоки исключений в PL/PgSQL, потому что каждый блок BEGIN/EXCEPTION/END создаёт подтранзакцию.

Почему «SELECT count(*) FROM bigtable;» работает медленно?

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

Вы можете получить некоторую оценку. Колонка reltuples в таблице pg_class содержит информацию из результата выполнения последнего оператора ANALYZE на эту таблицу. На большой таблице, точность этого значения составляет тысячные доли процента, что вполне достаточно для многих целей.

«Точный» результат count, часто не будет точным долгое время в любом случае; из-за конкурентности MVCC, count будет точным только на момент вызова запущенного запроса SELECT count(*) (или ограничиваться уровнями изоляции транзакций данной транзакции), и может потерять актуальность уже в момент завершения запроса. При постоянной работе транзакций, изменяющий таблицу, два вызова count(*), которые завершатся в одно и то же время могут показать разные значения, если изменяющая транзакция завершилась между их вызовами.

Почему мой запрос намного медленнее, чем когда он запускается в виде подготовленного запроса?

Когда PostgreSQL получает полный запрос со всеми параметрами, он может использовать статистику таблицы, чтобы понять являются ли значения, используемые в запросе, часто употребимыми или часто неупотребимыми в какой-либо колонке. Это позволяет изменить способ извлечения данных на более эффективный, так как известно ожидается ли очень много или очень мало результатов из определённой части запроса. Например, PostgreSQL может выбрать последовательное сканирование вместо использования индекса, если осуществляется поиск ‘active=y’ и при этом известно, что 99% записей в таблице имеют ‘active=y’, поскольку в этом случае последовательное сканирование будет намного быстрее.

В подготовленном запросе, PostgreSQL не получает значение всех параметров, когда создаёт план запроса. Он должен попытаться создать «безопасный» план, который должен работать хорошо вне зависимости от того какое значение вы предоставите как параметр, когда вы вызовите подготовленный запрос. К сожалению, такой план может не оказаться очень хорошим, если значение, которое вы предоставили гораздо более употребительно или гораздо менее употребительно, чем среднее арифметическое из выбираемых значений в таблице.

Иногда люди, у которых возникает эта проблема пытаются использовать подготовленные запросы как меру безопасности для предотвращения SQL иньекций, а не как инструмент тонкой настройки производительности тяжёлых запросов, часто запускающихся с различными параметрами. Такие люди должны подумать на использованием подготовленных операторов на стороне клиента, если их клиентский интерфейс (например PgJDBC) их поддерживает.

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

См. Using_EXPLAIN. Если вы собираетесь обратится за помощью в список рассылки, прочтите Guide to reporting problems.

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

Источник