Как настроить сервер postgresql

PostgreSQL (Русский)

Эта статья или раздел нуждается в переводе

Эта статья описывает как настроить PostgreSQL и интегрировать ее с PHP и Apache. Она также описывает, как сделать PostgreSQL доступным из клиента удалённого доступа. Считаем, что PHP и Apache уже установлены. Если вам нужна помощь настройки любой из этих программ, смотрите LAMP и следуйте всем разделам, кроме связанного с MySQL.

Contents

Установка PostgreSQL

• Устанавливаем postgresql

• Инициализируем кластер с нужной локалью (она должна быть доступна в системе). Обратите внимание, что в данном примере используем ru_RU.UTF-8

• Если получили ошибку «Невозможно создать директорию, недостаточно прав», то изменяем владельца PGROOT директории и пробуем еще раз

• Запускаем сервер PostgreSQL

• Проверяем запустилось ли:

• Дополнительно его можно добавить в автозагрузку

Создание Вашей первой базы данных

• Становимся пользователем postgres (пользователь postgres не имеет пароля по умолчанию, поэтому таким вот образом)

• Добавляем нового пользователя базы данных

-D Пользователь не может создавать базы данных
-R Пользователь не может создавать аккаунты
-S Пользователь не является суперпользователем
-P Запрашивать пароль при создании

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

• Если имя созданного пользователя совпадает с именем пользователя ($USER), вы получите доступ к базе данных оболочки PostgreSQL без указания имени пользователя (что весьма удобно).

• Создаём новую базу данных. Создавать можно только от пользователя (например, postgres, за которого мы зашли), имеющего разрешение на чтение и запись (read/write). Если кодировку не указать, то она будет той, что вы указали в разделе «Установка PostgreSQL».

• Вот и всё! Ваша база данных создана. Теперь можете уже под любым пользователем управлять БД:

• Если имя базы данных И имя пользователя БД совпадают с текущим именем пользователя ($USER), то можно просто:

Знакомство с PostgreSQL

Доступ к оболочке базы данных

• Становимся postgres пользователем, чтобы иметь возможность задать ваши права (как у основного пользователя)

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

— Вы также можете использовать `psql ` для редактирования конкретной базы данных.

• Список всех возможных команд (например, CREATE TABLE ) для запросов

• Подробное описание команды

• Подключаем определённую базу данных

• Список всех пользователей и их уровни доступа

• Краткая информация о всех таблицах в текущей базе данных

• Меняем пароль

• Показать все используемые настройки

• Выйти из psql

Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу.

Настройка удалённого доступа к PostgreSQL

Файл настроек сервера баз данных PostgreSQL postgresql.conf . Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data . В этой же папке находятся основные файлы настроек включая и pg_hba.conf .

Из-под пользователя root редактируем файл

В разделе connections and authentications раскомментируйте или исправьте строку listen_addresses по вашему желанию на

и внимательно просмотрите другие строки.
Далее добавляем следующую строку в основной файл настройки проверки подлинности /var/lib/postgres/data/pg_hba.conf . (если вы планируете подключатся только со своего компьютера, то пропустите данный шаг) Этот файл определяет, каким хостам разрешено подключаться, так что будьте осторожны.

где your_desired_ip_address — IP-адрес клиента.

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

Если возникли проблемы взгляните на лог-файл сервера

Не менее важные ошибки можно увидеть командой:

Настройка PostgreSQL для работы с PHP

1. Установите модуль PHP-PostgreSQL
2. Откройте файл /etc/php/php.ini в удобном для вас текстовом редакторе, например,
3. Найдите строку, начинающуюся с ;extension=pgsql.so , и из неё уберите ; ( ; значит, что строка закомментирована). Если вы используете PDO, сделайте то же самое с ;extension=pdo.so и ;extension=pdo_pgsql.so . Если этих строк нет, добавьте их (без ; ). Эти строки надо искать в разделе файла «Dynamic Extensions» (по умолчанию) или в самом конце файла.
4. Перезапустите веб-сервер Apache
5. Либо, если у вас nginx + php-fpm, то

Настройка PostgreSQL для работы с HHVM

Если вы используете не ночную версию, то выполните это команду (проверено на HHVM 3.6.1), чтобы избежать ошибок компиляции:

Затем надо собрать (если улучшенная поддержка языка Hack не нужна, то уберите -DHACK_FRIENDLY=ON):

Скопируем скомпилированное расширение:

Затем в /etc/hhvm/server.ini добавляем:

Изменение кодировки новой базы данных на UTF-8 (по вашему усмотрению)

Когда создаётся новая база данных (например, createdb blog ) PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 — ваниль, и template1 используемый по умолчанию. Один из вариантов изменения кодировки новой базы данных, заключается в изменении шаблона template1. Для этого, заходим в оболочку PostgresSQL (psql) и делаем вот что:

1. Первое, мы должны сбросить template1. Шаблоны не могут быть сброшены, так что мы сначала изменим его, как обычную базу данных:

2. Сейчас уже можно сбросить её:

3. Создаём новую базу данных, с новой кодировкой по умолчанию из template0:

4. Теперь снова сделаем template1 шаблоном:

5. (Рекомендация) Документация по PostgreSQL advises рекомендует «замораживать» изменения шаблона функцией VACUUM FREEZE:

6. (По желанию) Если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE:

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

Если снова войти в PSQL и проверить базу данных, вы должны увидеть правильную кодировку новой базы данных:

Решение проблем

Ускорение мелких транзакций

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

Запретить запись на диск во время бездействия

PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать (и изнашивает) жёсткому диску, заставляя его шуршать. Однако можно легко и безопасно поменять локацию файла внутрь ФС (/run) расположенной в ОЗУ с помощью такой настройки:

Источник

Sysadminium

База знаний системного администратора

Способы конфигурирования PostgreSQL

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

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

Конфигурационный файл postgresql.conf

Главный конфигурационный файл для кластера PostgreSQL – это postgresql.conf, в разных системах он может находится в разных местах. Так как мы собирали сервер из исходников и не меняли путь хранения этого файла, то по умолчанию он будет находится в каталоге PGDATA:

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

Самый точный способ узнать расположение этого файла, посмотреть из терминала psql:

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

Второй способ – из терминала psql:

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

Конфигурация сервера используя ALTER SYSTEM

Для настройки сервера также существует другой файл – postgresql.auto.conf. Он были придуман для настройки сервера из консоли psql. Читается этот файл после postgresql.conf, поэтому параметры из него имеют приоритет. Этот файл всегда находится в каталоге с данными (PGDATA).

Для создания параметров в файле postgresql.auto.conf нужно выполнить подобный запрос:

ALTER SYSTEM SET TO ;

Чтобы удалить параметр используем другой запрос:

ALTER SYSTEM RESET ;

А чтобы удалить все параметры из postgresql.auto.conf выполним:

ALTER SYSTEM RESET ALL;

Чтобы применить изменения нужно перечитать конфигурационные файлы как было описано выше.

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

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

• pg_file_settings – какие параметры записаны в файлах postgresql.conf и postgresql.auto.conf;
• pg_settings – текущие параметры, с которыми работает сервер.

Например посмотрим значение параметра config_file из представления pg_settings, который покажет конфигурационный файл текущего кластера:

Внесём изменения в параметр work_mem в postgresql.conf и postgresql.auto.conf. Затем посмотрим на все не закомментированные параметры в этих файлах:

Как можно заметить в примере выше, у меня 2 одинаковых параметра work_mem. Колонка applied показывает, может ли быть применён параметр. Первый work_mem не может быть применен, так как второй его перезапишет. При этом реальное значение с которым работает сервер отличается, так как сервер не перечитал конфигурацию.

Теперь посмотрим на реальное, текущее значение этого параметра:

В примере выше мы использовали расширенный режим (в конце запроса \gx), поэтому табличка перевёрнута. Разберём колонки:

• name – имя параметра;
• setting – текущее значение;
• unit – единица измерения;
• boot_val – значение по умолчанию (жёстко задано в коде postgresql);
• reset_val – если перечитаем конфигурацию, то применится это значение;
• source – источник, это значение по умолчанию;
• sourcefile – если бы источником был конфигурационный файл, то тут был бы указан этот файл;
• sourceline – номер строки в этом файле;
• pending_restart – параметр изменили в конфигурационном файле и требуется перезапуск сервера. У нас требуется всего лишь перечитать конфигурацию;
• context – действия, необходимые для применения параметра, может быть таким:
  • internal – изменить нельзя, задано при установке;
  • postmaster – требуется перезапуск сервера;
  • sighup – требуется перечитать файлы конфигурации;
  • superuser – суперпользователь может изменить для своего сеанса;
  • user – любой пользователь может изменить для своего сеанса на лету.

Перечитаем конфигурацию сервера:

Как видим, параметр изменился. Он был взят из postgresql.auto.conf и теперь равняется 10 MB.

Установка параметров на лету

Для своего сеанса можно изменить параметры с context=user. Для этого используется конструкция:

Например сделаем это для work_mem:

Как видим, теперь источником является текущая сессия, а параметр равен 64 MB, но если мы перечитаем конфигурацию параметр снова станет равным 10 MB.

Чтобы вернуть все на место нужно просто перезайти в psql. Или выполнить команду RESET :

Тоже самое может проделывать приложение для одной транзакции, и если транзакция откатывается, то и значение параметра откатывается вместе с ней:

Как вы могли заметить посмотреть текущее значение параметра ещё можно так:

Какие параметры требуют перезапуск сервера?

Чтобы это выяснить нужно посмотреть все параметры у которых context = postmaster:

Источник

Установка и настройка PostgreSQL 12 на Windows 10 – инструкция для начинающих

Приветствую Вас на сайте Info-Comp.ru! В этом материале мы с Вами подробно рассмотрим процесс установки PostgreSQL 12 на операционную систему Windows 10. Кроме этого мы также установим и настроим pgAdmin 4 – это стандартный и бесплатный графический инструмент управления СУБД PostgreSQL, который мы можем использовать для написания SQL запросов, разработки процедур, функций, а также для администрирования PostgreSQL.

Что такое PostgreSQL?

PostgreSQL — это бесплатная объектно-реляционная система управления базами данных (СУБД). PostgreSQL реализована для многих операционных систем, например, таких как: BSD, Linux, macOS, Solaris и Windows.

В PostgreSQL используется язык PL/pgSQL.

PL/pgSQL – это процедурное расширение языка SQL, разработанное и используемое в СУБД PostgreSQL.

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

PostgreSQL — одна из самых популярных систем управления базами данных (ТОП 5 популярных систем управления базами данных).

На момент написания статьи самая актуальная версия PostgreSQL 12, именно ее мы и будем устанавливать.

Системные требования для установки PostgreSQL 12 на Windows

PostgreSQL 12 можно установить не на все версии Windows, в частности официально поддерживаются следующие версии и только 64 битные:

• Windows Server 2012 R2;
• Windows Server 2016;
• Windows Server 2019.

Как видим, в официальном перечне нет Windows 10, однако установка на данную систему проходит без проблем, как и последующее функционирование PostgreSQL.

Кроме этого есть и другие требования:

• Процессор как минимум с частотой 1 гигагерц;
• 2 гигабайта оперативной памяти;
• Как минимум 512 мегабайт свободного места на диске (рекомендуется больше для установки дополнительных компонентов);
• Также рекомендовано, чтобы все обновления операционной системы Windows были установлены.

Установка PostgreSQL 12 и pgAdmin 4 на Windows 10

Итак, давайте перейдем к процессу установки, и рассмотрим все шаги, которые необходимо выполнить, чтобы установить PostgreSQL 12 и pgAdmin 4 на Windows 10.

Шаг 1 – Скачивание установщика для Windows

Как было уже отмечено, PostgreSQL реализован для многих платформ, но, так как мы будем устанавливать PostgreSQL на Windows, нам, соответственно, нужен установщик под Windows. Скачать данный дистрибутив можно, конечно же, с официального сайта PostgreSQL, вот страница загрузки — https://www.postgresql.org/download/windows/

После перехода на страницу необходимо нажимать на ссылку «Download the installer», в результате Вас перенесёт на сайт компании EnterpriseDB, которая и подготавливает графические дистрибутивы PostgreSQL для многих платформ, в том числе и для Windows, поэтому можете сразу переходить на этот сайт, вот ссылка на страницу загрузки https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Здесь Вам необходимо выбрать версию PostgreSQL и платформу, в нашем случае выбираем PostgreSQL 12 и Windows x86-64.

В итоге должен загрузиться файл postgresql-12.2-2-windows-x64.exe размером примерно 191 мегабайт (на момент написания статьи доступна версия 12.2-2).

Шаг 2 – Запуск установщика PostgreSQL

Теперь, чтобы начать установку, необходимо запустить скаченный файл (установка PostgreSQL требует прав администратора).

После запуска откроется окно приветствия, нажимаем «Next».

Шаг 3 – Указываем каталог для установки PostgreSQL 12

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

Шаг 4 – Выбираем компоненты для установки

Затем выбираем компоненты, которые нам необходимо установить, для этого оставляем галочки напротив нужных нам компонентов, а обязательно нам нужны PostgreSQL Server и pgAdmin 4. Утилиты командной строки и Stack Builder устанавливайте по собственному желанию, т.е. их можно и не устанавливать.

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

Шаг 5 – Указываем каталог для хранения файлов баз данных

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

Шаг 6 – Задаем пароль для системного пользователя postgres

Далее нам нужно задать пароль для пользователя postgres – это администратор PostgreSQL Server с максимальными правами.

Вводим и подтверждаем пароль. Нажимаем «Next».

Шаг 7 – Указываем порт для экземпляра PostgreSQL

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

Шаг 8 – Указываем кодировку данных в базе

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

Однако можно оставить и по умолчанию, жмем «Next».

Шаг 9 – Проверка параметров установки PostgreSQL

Все готово к установке, на данном шаге проверяем введенные нами ранее параметры и, если все правильно, т.е. все то, что мы и вводили, нажимаем «Next».

Шаг 10 – Запуск процесса установки

Далее появится еще одно дополнительное окно, в котором мы должны нажать «Next», чтобы запустить процесс установки PostgreSQL на компьютер.

Установка началась, она продлится буквально минуту.

Шаг 11 – Завершение установки

Когда отобразится окно с сообщением «Completing the PostgreSQL Setup Wizard», установка PostgreSQL 12, pgAdmin 4 и других компонентов будет завершена.

Также в этом окне нам предложат запустить Stack Builder для загрузки и установки дополнительных компонентов, если Вам это не нужно, то снимайте галочку «Lanch Stack Builder at exit?».

Запуск и настройка pgAdmin 4

PostgreSQL 12 и pgAdmin 4 мы установили, теперь давайте запустим pgAdmin 4, подключимся к серверу и настроим рабочую среду pgAdmin.

Чтобы запустить pgAdmin 4, зайдите в меню пуск, найдите пункт PostgreSQL 12, а в нем pgAdmin 4.

Подключение к серверу PostgreSQL 12

pgAdmin 4 имеет веб интерфейс, поэтому в результате у Вас должен запуститься браузер, а в нем открыться приложение pgAdmin 4.

При первом запуске pgAdmin 4 появится окно «Set Master Password», в котором мы должны задать «мастер-пароль», это можно и не делать, однако если мы будем сохранять пароль пользователя (галочка «Сохранить пароль»), например, для того чтобы каждый раз при подключении не вводить его, то настоятельно рекомендуется придумать и указать здесь дополнительный пароль, это делается один раз.

Вводим и нажимаем «ОК».

Чтобы подключиться к только что установленному локальному серверу PostgreSQL в обозревателе серверов, щелкаем по пункту «PostgreSQL 12».

В итоге запустится окно «Connect to Server», в котором Вам нужно ввести пароль системного пользователя postgres, т.е. это тот пароль, который Вы придумали, когда устанавливали PostgreSQL. Вводим пароль, ставим галочку «Save Password», для того чтобы сохранить пароль и каждый раз не вводить его (благодаря функционалу «мастер-пароля», все сохраненные таким образом пароли будут дополнительно шифроваться).

В результате Вы подключитесь к локальному серверу PostgreSQL 12 и увидите все объекты, которые расположены на данном сервере.

Установка русского языка в pgAdmin 4

Как видите, по умолчанию интерфейс pgAdmin 4 на английском языке, если Вас это не устраивает, Вы можете очень просто изменить язык на тот, который Вам нужен. pgAdmin 4 поддерживает много языков, в том числе и русский.

Для того чтобы изменить язык pgAdmin 4, необходимо зайти в меню «File -> Preferences».

Затем найти пункт «User Languages», и в соответствующем поле выбрать значение «Russian». Для сохранения настроек нажимаем «Save», после этого перезапускаем pgAdmin 4 или просто обновляем страницу в браузере.

В результате pgAdmin 4 будет русифицирован.

Пример написания SQL запроса в Query Tool (Запросник)

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

Для написания SQL запросов в pgAdmin 4 используется инструмент Query Tool или на русском «Запросник», его можно запустить с помощью иконки на панели или из меню «Инструменты».

После того как Вы откроете Query Tool, напишите

Этот запрос показывает версию PostgreSQL.

Как видите, все работает!

Видео-инструкция – Установка PostgreSQL 12 и pgAdmin 4 на Windows 10

На сегодня это все, надеюсь, материал был Вам полезен, удачи!

Источник