Установка и базовая настройка PostgreSQL в Windows 10

PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.

Установка PostgreSQL 11 в Windows 10

Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.

В процессе установки установите галочки на пунктах:

• PostgreSQL Server – сам сервер СУБД
• PgAdmin 4 – визуальный редактор SQL
• Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
• Command Line Tools – инструменты командной строки

Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).

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

Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.

Доступ к PostgreSQL по сети, правила файерволла

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

Запустите командную строку от имени администратора. Введите команду:

netsh advfirewall firewall add rule name=»Postgre Port» dir=in action=allow protocol=TCP localport=5432

• Где rule name – имя правила
• Localport – разрешенный порт

Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:

New-NetFirewallRule -Name ‘POSTGRESQL-In-TCP’ -DisplayName ‘PostgreSQL (TCP-In)’ -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.

Измените значение в пункте port = 5432 . Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:

Restart-Service -Name postgresql-x64-11

Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.

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

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

Запустите командную строку.

Основные команды PostgreSQL:

• Проверка установленной версии СУБД: psql –V
• Для создания новой базы данных воспользуйтесь утилитой createdb: createdb -U postgres testdb (где postgres суперпользователь, testdb новая база данных)Введите пароль суперпользователя.
• Проверить список активных баз: Psql -U postgres –l (пароль)
• С помощью инструмента createuser cоздадим нового пользователя: createuser –U postgres operator (где operator -имя нового пользователя)
• Предоставим пользователю привилегии суперпользователя (на практике этого делать не надо). Запустите интерактивную командную оболочку управления PostgreSQL (shell): psql –U postgres . С помощью SQL команды ALTER ROLE предоставим нужные права нашему пользователю: ALTER ROLE operator SUPERUSER CREATEROLE CREATEDB; . Мы предоставили пользователю права суперпользователя, права на создание ролей и баз данных.
• Для выводы списка пользователей и ролей в СУБД выполните команду: \du

PgAdmin: Визуальный редактор для PostgresSQL

Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.

• Для запуска редактора запустите PgAdmin 4 в меню Пуск
• Для доступа нужно ввести пароль суперпользователя postgres
• В панели Servers вы можете раскрыть список активных БД.
  
• В панели управления возможно быстро создать нового пользователя и группу, предоставить ему права. Для этого Откройте меню Object -> Create -> Create Login/Group.
• Для создания новой базы данных достаточно выбрать: Database в меню Object -> Create. В новом поле указать имя базы и владельца.

По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.

Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.

Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.

Query Tool: использование SQL запросов в PostgreSQL

Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.

• Выберите базу данных, в панели Tools откройте Query Tool
• Создадим таблицу сотрудников:

CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);

Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.

После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.

Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.

Здесь вы можете заполнить данные в таблице.

После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;

Как установить PostgreSQL — решения для Windows, Mac OS X, Linux

Как установить PostgreSQL? Для Microsoft Windows , Mac OS X и Linux существует один установщик. Его можно скачать здесь . Кликните по иконке нужной версии установки PostgreSQL , соответствующей вашей операционной системе.

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

Запустите установку

Когда программа попросит выбрать один из вариантов, сделайте следующее:

• Когда вас попросят выбрать локаль, выберите UTF-8 . Если варианта UTF-8 в списке нет, выберите вариант UTF-8 в списке локалей для языка. Например, en_US.UTF-8 для английского языка США;
• Если программа попросит установить StackBuilder – он инсталлирует некоторые дополнительные инструменты Web , репликации и ODBC , которые не требуются для CollectionSpace . Но может потребоваться инсталляция некоторых модулей PostgreSQL , таких как PostGIS .

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

Настройка PostgreSQL

Чтобы установленный PostgreSQL работал с CollectionSpace, необходимо настроить пользователей и изменить некоторые параметры.

• Найдите конфигурационный файл pg_hba.conf . Он расположен в подкаталоге data каталога, в котором установлена PostgreSQL ;
• Откройте этот файл в текстовом редакторе и следуйте приведенным ниже инструкциям;

В Mac OS X по умолчанию это папка /Library/PostgreSQL/9.1.
В Mac OS X нужно иметь права супер пользователя, чтобы редактировать любой из следующих файлов конфигурации.

• Убедитесь, что для « local section » указано последнее слово md5 , а не ident :

• Измените раздел « IPv4 local connections » следующим образом:

Это ограничивает набор пользователей PostgreSQL , которым предоставляется локальный доступ к трем базам данных, используемым CollectionSpace: jbossdb , nuxeo и cspace . Например, только пользователь с именем cspace или супер пользователь PostgreSQL с именем postgres может получить доступ к базе данных cspace с локальной машины.

При установке PostgreSQL 9.x на Windows 7 может не принять термин samehost в столбце CIDR-ADDRESS . Если это произойдет, используйте адресную форму 127.0.0.1/32 :

• Убедитесь, что записи в разделе « IPv6 local connections » отключены. В качестве первого символа в строках указывается символ хэш ( # ), если только в вашей системе не активен IPv6 :

Современные системы, работающие под управлением Windows 7 и Windows Vista , могут поставляться с включенным по умолчанию IPv6 . Если в вашей системе включен IPv6 , можно поэкспериментировать со следующей конфигурацией — указать ::1/128 для адреса localhost :

• Если нужно разрешить создание отчетов при подключении к БД с удаленных хостов, тогда следует добавить запись, подобную приведенной ниже, но указав свой адрес подсети. Приведенная ниже конфигурация позволит создавать отчеты удаленно с хостов в определенной подсети UC Berkeley . Для получения дополнительной информации о том, как указать адрес подсети, ознакомьтесь с документацией по pg_hba.conf для используемого релиза PostgreSQL .

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

Если вы можете контролировать доступ к PostgreSQL через брандмауэр, то проще ввести значение listen_addresses «*» . Для большей безопасности можно ввести список разделенных запятыми IP-адресов , с которых будут связываться авторы отчетов.

Далее после PostgreSQL установки и настройки Windows нужно внести изменения в конфигурационный файл PostgreSQL :

• Найдите файл postgresql.conf , находящийся в папке data каталога, в котором установлен PostgreSQL ;
• Откройте этот файл в текстовом редакторе и внесите следующие изменения:

Значение переменной max_prepared_transactions , равное 64 , может оказаться большим для вашей системы. Если это так, установите меньшее значение.

Если это значение действительно слишком велико для вашей системы, вы получите сообщение об ошибке при перезапуске PostgreSQL :
Restarting PostgreSQL … database server: main The PostgreSQL server failed to start. Please check the log output … FATAL: could not create shared memory segment: …

Перезапустите службу PostgreSQL

Если при запуске сервера после установки PostgreSQL выводятся сообщения об ошибках, обратитесь к разделу « Сбои при запуске сервера » в документации PostgreSQL по запуску сервера .

Если вы являетесь пользователем root или sudo , можно выполнить следующую команду:

service postgresql restart

Убедитесь, что вы являетесь пользователем postgres :

Флаг -D указывает местоположение каталога данных PostgreSQL, который также может быть установлен как переменная среды PGDATA. По умолчанию его можно найти по адресу /Library/PostgreSQL/9.1/data.

Если при попытке запустить pg_ctl вы получаете сообщение об ошибке «not found», попробуйте указать полный путь к этому файлу в подкаталоге bin каталога, в котором установлен PostgreSQL. Например: /Library/PostgreSQL/9.1/bin/pg_ctl restart -D /Library/PostgreSQL/9.1/data

Чтобы добавить PGDATA в качестве глобальной переменной среды в / etc / profile , выполните следующие шаги:

sudo vi /etc/profile

Затем добавьте следующую строку:

После этого можно будет выполнить предыдущую команду для перезапуска сервера PostgreSQL без флага -D .

При установке PostgreSQL Windows у вас должны быть права администратора. Кликните правой кнопкой мыши по ярлыку « Мой компьютер » и выберите пункт « Управление ». В пользовательском интерфейсе « Управление » выберите « Службы и приложения » — « Службы ». Найдите службу PostgreSQL ( «postgresql-9.0 — PostgreSQL Server 9.0» ), выберите ее и нажмите на кнопку « Перезапустить службу ».

Настройка типов данных

Откройте консоль PostgreSQL для базы данных template1 :

Если при попытке запустить psql на экране появится сообщение not found», попробуйте указать полный путь к этому файлу в подкаталоге bin; например на Mac OS X: /Library/PostgreSQL/9.1/bin/psql.

Выполните в консоли две следующие команды:

Под пользователем postgres выполните следующие две команды:

При первом запуске команды psql на OS X 10.8.X вы можете увидеть сообщение об ошибке:

Это связано с тем, как Homebrew в Mac OS X и Mountain Lion конфликтуют с определенными системными библиотеками. Решение этой проблемы заключается в том, чтобы скопировать и связать версии библиотеки. PostgreSQL упакован в /usr/lib , где библиотеки хранятся в Mountain Lion .

Еще одним решением может стать пересмотр Homebrew и проверка совместимости с Mountain Lion .

При установке PostgreSQL на Mac OS X в один клик создается ярлык для оболочки PostgreSQL в каталоге Applications / PostgreSQL 9.0 / . Нажмите SQL Shell (psql).app , чтобы открыть новое окно терминала с запущенным psql .

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

psql c template1

psql запускается из командной строки ( cmd ) Windows . Убедитесь, что путь к папке postgres bin хранится в переменной PATH , или просто вызовите ее с полным путем к месту расположения исполняемого файла psql :

psql -d template1 -U postgres

Выполните команды консоли:

Создайте пользователя csadmin

Для PostgreSQL установки и настройки в командной строке psql ( заканчивающейся на # ) переключитесь с базы данных template1 на postgres , введя следующее:

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

CREATE ROLE csadmin LOGIN PASSWORD ‘replacemewithyourpassword’ SUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

В результате вы должны увидеть сообщение, подобное этому: CREATE ROLE .

Закройте программу psql , введя q ( или нажав Ctrl + d ).

Теперь перезапустите сервер PostgreSQL еще раз ( как описано выше ).

Дополнительная настройка

Дополнительная настройка базы данных является необязательной! CollectionSpace будет выполняться, даже если ни одно из описанных ниже действий не будет выполнено.

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

PostgreSQL установка и настройка Windows выполняется через postgresql.conf . Откройте этот файл в текстовом редакторе и отредактируйте следующие значения:

Теперь через командную строку задайте значение kernel.shmmax . Оно должно как минимум равняться shared_buffer + 128MB :

Затем перезапустите сервер PostgreSQL . Если на экране появится сообщение об ошибке, то нужно будет увеличить значение kernel.shmax .

Затем необходимо определить окончательное значение shmmax :

Откройте файл /etc/sysctl.conf .

И добавьте следующую строку — заменив значение для shmmax :

Наконец, установите необходимое значение cache_size :

Для этого запустите ( и выведите статистику в мегабайтах ):

В результате на экране вы увидите что-то наподобие этого:

Эффективное значение для cache_size определяется путем сложения значений buffers и cached . В приведенном выше примере:

253 + 1226 = 1479

К этому числу добавляется значение shared_buffers , поэтому в общей сложности мы получаем:

1479 + 512 = 1991

Снова отредактируйте файл postgres.conf и установите эффективный размер кэша — значение, выведенное нами в предыдущих шагах:

Также задайте следующие значения:

Перезагрузите установленный PostgreSQL , чтобы изменения вступили в силу.

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