🐘 PostgreSQL – FAQ

Ответы на часто задаваемые вопросы начинающих DBA / FAQ for DBA beginners.

Введение

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

Оглавление / Table of Contents

Сообщество
Вопросы
2.1. Не могу подсоединиться к PostgreSQL. Что делать?
2.2. Получаю ошибку что имя таблицы/колонки/etc неверное, хотя это не так. В чем дело?
2.3. Как публиковать SQL запросы, определения функций, выводы команд вспомогательных утилит и прочую текстовую информацию при запросе помощи у сообщества?
2.4. У меня медленно работает SQL запрос и я хочу попросить помощи у сообщества. Какую информацию мне необходимо предоставить?
2.5. У меня не получается написать SQL запрос/etc и/или я получаю ошибки. Какую информацию мне необходимо предоставить для получения помощи от сообщества?
2.6. В каком регистре писать команды/функции/etc в PostgreSQL?
Книги/курсы
Полезные ссылки
Переводы / Translations
5.1. English

1. Сообщество 👨‍👩‍👧‍👦

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

Чат русскоязычного сообщества PostgreSQL в Telegram;
PGDay.ru - Ежегодная конференция по PostgreSQL (Санкт-Петербург);
PGConf.ru - Ежегодная конференция по PostgreSQL (Москва).

2. Вопросы ⁉️

2.1. Не могу подсоединиться к PostgreSQL. Что делать?

Пожалуй, это наиболее частый вопрос у начинающих пользователей PostgreSQL, ежедневно звучащий в информационной среде сообщества. Как правило, они получают ошибки, содержащие ключевые слова Connection refused или Connection ... failed. Например:

error: connection to server at "localhost", port 5432 failed: Connection refused

или (в случае попытки подключения через unix domain socket):

error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

Следует провести диагностику проблемы поэтапно (в настоящее время инструкция применительна только для Unix-based ОС):

2.1.1. Запущен ли основной процесс PostgreSQL?

Посмотреть, запущен ли основной процесс PostgreSQL, можно выполнив след. команду на сервере:

ps -ef | grep "postgresql.*config_file"

В случае, если PostgreSQL запущен, в выводе команды вы должны увидеть нечто вроде:

<PG_PATH> -D <DATA_DIR> -c config_file=<CONFIG_PATH>

Где <PG_PATH> — путь до исполняемого файла PostgreSQL, <DATA_DIR> — путь до директории с данными кластера, <CONFIG_PATH> — пусть до конфигурационного файла postgresql.conf (потребуется для дальнейшей диагностики).

В противном случае, проблема найдена: PostgreSQL не запущен.

2.1.2. Верен ли TCP-порт, открываемый сервером PostgreSQL?

Теперь необходимо убедиться, что PostgreSQL слушает нужный вам TCP-порт (если, конечно, вы не собираетесь работать только через Unix-сокеты). Откройте конфигурационный файл postgresql.conf (путь до него мы определили этапом выше) и найдите параметр port. Там должно быть значение необходимого вам порта, который по умолчанию равен 5432. В противном случае, исправьте номер порта на нужное вам значение. После применения изменений необходимо произвести рестарт PostgreSQL.

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

После рестарта PostgreSQL можно удостовериться, что нужный вам порт действительно прослушивается. Для этого можно выполнить след. команду на сервере (для порта 5432):

ss -ln | grep ":5432"

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

2.1.3. Верны ли адреса TCP/IP, по которым PostgreSQL принимает подключения?

Параметром в файле postgresql.conf, отвечающим за то, через какие сетевые интерфейсы PostgreSQL будет принимать соединения, является listen_addresses.

Если вы хотите подключиться к PostgreSQL локально (т.е. с того же сервера), подойдут значения 127.0.0.1 (для подключения по IPv4), ::1 (для подключения по IPv6) или localhost (в современных ОС данное доменное имя, как правило, транслируется в ::1. Подробнее см. тут). Стоит отметить, что в параметре listen_addresses, как следует из его названия, можно указать несколько значений через запятую. Учтите, что старые клиентские приложения, в подавляющем большинстве случаев, работают по IPv4.

Если вы хотите подключиться к PostgreSQL удаленно (т.е. с другого сервера), то необходимо принимать подключения с соотв. внешних интерфейсов. С помощью значения 0.0.0.0 можно принимать подключения со всех адресов IPv4, а :: — все адреса IPv6. В то же время, если указать значение *, то PostgreSQL будет принимать подключения со всех имеющихся сетевых интерфейсов.

Подробнее о подключениях и аутентификации к PostgreSQL см. тут. Напомним, что после применения изменений в файл postgresql.conf необходимо произвести рестарт PostgreSQL.

❗ В случае, если вы указали PostgreSQL прослушивать внешние интерфейсы (т.е., что-то, отличное от значений 127.0.0.1, ::1 или localhost параметра listen_addresses), то ваш сервер может быть доступне извне (т.е. из Интернета) и потенциально находится под угрозой. Чтобы избежать негативных последствий, необходимо корректно настроить ваш firewall и файл pg_hba.conf. Подробнее о них см. ниже.

2.1.4. Верна ли конфигурация аутентификации клиентов PostgreSQL?

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

Подробнее о файле pg_hba.conf см. тут (включая примеры настройки).

2.1.5. Верна ли конфигурация firewall?

Даже если PostgreSQL настроен верно (с точки зрения подключения клиентов), вы всё ещё можете иметь неудачные попытки подключения. В этом случае вам необходимо обратить внимание на правильность настройки firewall (он может быть как на уровне ОС, так и на уровне ваших сетевых аппаратных/виртуальных устройств, напр., роутера). Конкретные шаги выходят за рамки данного FAQ.

2.2. Получаю ошибку что имя таблицы/колонки/etc неверное, хотя это не так. В чем дело?

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

Пример воспроизведения и решения проблемы:

SELECT * FROM TableName; -- error: relation "tablename" does not exist
SELECT * FROM "TableName"; -- OK

Обратите внимание, что идентификатор TableName без двойных кавычек был преобразован в tablename, что вызвало ошибку.

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

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

Прежде всего, не используйте скриншоты для того, чтобы показать SQL запросы (и/или их результаты), определения функций, выводы команд вспомогательных утилит (таких, как psql и др.) и прочую текстовую информацию. Заметно эффективнее будет публикация оных, напр., на gist или pastebin.com. Если вы задаете вопрос в tg-чате и кол-во содержимого невелико, его допускается опубликовать в режиме форматирования Monospace прямо в чат. Как правило, этот вопрос тесно связан с п. 2.4 и п. 2.5.

Многие участники сообщества принципиально не рассматривают скриншоты (и на это есть рациональные причины), поэтому постарайтесь оформить свой текст правильно.

2.4. У меня медленно работает SQL запрос и я хочу попросить помощи у сообщества. Какую информацию мне необходимо предоставить?

Чтобы получить качественную помощь по оптимизации запроса и не уничтожить желание у сообщества помочь вам, необходимо немного постараться и собрать некоторые данные. Как в своё время метко подметил один уважаемый пользователь tg-чата, минимальная информация для получения помощи следующая:

Версия PostgreSQL на сервере, где запускается запрос;
Непосредственно SQL запрос;
Вывод метакоманды \d из утилиты psql для каждой используемой в запросе таблицы;
EXPLAIN (ANALYZE, BUFFERS) для запроса (подробнее об EXPLAIN см. тут).

❗ Внимательно отнеситесь к тому, как публиковать информацию, которая требуется для ответа на ваш вопрос.

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

2.5. У меня не получается написать SQL запрос/etc и/или я получаю ошибки. Какую информацию мне необходимо предоставить для получения помощи от сообщества?

Кратко опишите предметную область и то, что вы хотите сделать;
Продемонстрируйте то, что вы уже сделали. Это докажет то, что вы попытались решить проблему самостоятельно,
а также даст начальную точку для участников сообщества, которые захотят вам помочь. Если вы получаете ошибки, то их также стоит приложить к своему вопросу;
Выполните хотя бы один из пунктов:
3.1. Предоставьте вывод метакоманды \d из утилиты psql для каждой таблицы, которая будет участвовать в запросе;
3.2. Создайте тестовое окружение, которое воспроизводит ваши таблицы/данные и непосредственно проблему.
Это поможет другим участникам сообщества легко и быстро приступить к изучению вашей проблемы (и, как следствие, повысит желание помогать в её решении).
Для этого следует использовать такие сервисы как sqlize.online или db-fiddle.com.

❗ Внимательно отнеситесь к тому, как публиковать информацию, которая требуется для ответа на ваш вопрос.

2.6. В каком регистре писать команды/функции/имена/etc в PostgreSQL?

Стоит отметить, что у сообщества нет единого мнения по данному вопросу. Единственный технический нюанс, который следует учитывать, описан выше. В остальном, это зависит исключительно от того, как принято в вашей команде и/или организации (т.н. code style). Внутри команды и/или организации важно соблюдать единый стандарт оформления кода для того чтобы в дальнейшем его было легко читать/поддерживать как вам, так и вашим коллегам.

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

Все ключевые слова,
включая слова из DML (SELECT/INSERT/UPDATE/DELETE),
DDL (CREATE/ALTER/DROP)
а также DCL (GRANT/REVOKE) пишутся в верхнем регистре. Например:
```
SELECT * FROM table;
```
Идентификаторы (имена таблиц, столбцов, функций и т.д.) в большинстве случаев пишутся в нижнем регистре в формате snake_case. Например:
```
SELECT floor(col_name) FROM table;
```