Postgresql инструкция по установке - Kompot.top - полезные статьи обо всем

Before you can use PostgreSQL you need to install it, of course. It is possible that PostgreSQL is already installed at your site, either because it was included in your operating system distribution or because the system administrator already installed it. If that is the case, you should obtain information from the operating system documentation or your system administrator about how to access PostgreSQL.

If you are not sure whether PostgreSQL is already available or whether you can use it for your experimentation then you can install it yourself. Doing so is not hard and it can be a good exercise. PostgreSQL can be installed by any unprivileged user; no superuser (root) access is required.

If you are installing PostgreSQL yourself, then refer to Chapter 17 for instructions on installation, and return to this guide when the installation is complete. Be sure to follow closely the section about setting up the appropriate environment variables.

If your site administrator has not set things up in the default way, you might have some more work to do. For example, if the database server machine is a remote machine, you will need to set the PGHOST environment variable to the name of the database server machine. The environment variable PGPORT might also have to be set. The bottom line is this: if you try to start an application program and it complains that it cannot connect to the database, you should consult your site administrator or, if that is you, the documentation to make sure that your environment is properly set up. If you did not understand the preceding paragraph then read the next section.

Источник

Установка PostgreSQL по шагам

1. Загружаем дистрибутив

Мы советуем скачивать актуальные версии на официальном сайте СУБД. Чтобы найти установщик PostgreSQL 15 для Windows нажимаем Download → Windows → Download the installer.

На сайте выбираем интересующую нас версию PostgreSQL и платформу Windows x86-64. Начинается загрузка установочного файла на компьютер.

2. Запускаем установочный файл

В ходе установки проводится проверка на наличие необходимых компонентов, например, Microsoft Visual C++. Если каких-то компонентов не хватает, они устанавливаются автоматически.

В открывшемся приветственном окне нажимаем Next.

3. Указываем каталог для установки

Выбираем путь, куда нужно установить PostgreSQL 15. По умолчанию предлагается C:\Program Files\PostgreSQL\15, его можно оставить.

4. Выбираем дополнительные компоненты

Вместе с СУБД можно установить следующие компоненты:

PostgreSQL Server — это сам сервер баз данных PostgreSQL;
pgAdmin 4 — приложение с графическим интерфейсом для администрирования СУБД;
Stack Builder — утилита, которая поможет установить дополнительные библиотеки и инструменты в PostgreSQL;
Command Line Tools — инструменты для работы с СУБД через командную строку.

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

Можно оставить путь по умолчанию C:\Program Files\PostgreSQL\15\data или выбрать свой. Здесь будет храниться вся информация, поэтому перед выбором каталога важно проверить, что на диске достаточно свободного места.

Разбираетесь в железе? Пройдите тест и проверьте себя

6. Устанавливаем пароль для системного пользователя

Рекомендуем выбирать пароль из 12 и более символов, со строчными и прописными буквами, цифрами и символами. Логин postgres устанавливается по умолчанию.

7. Выбираем порт и кодировку

Можно оставить порт по умолчанию 5432 или указать свой. Далее нажать Next.

В появившемся списке локалей выбираем подходящую для себя локализацию или оставляем значение по умолчанию [Default locale]. Нажимаем Next.

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

8. Проверяем параметры и завершаем установку

В финальном окне выводится сводная информация об установке PostgreSQL 15: дополнительные компоненты, выбранные порт и каталог, логин. Если параметры указаны верно, нажимаем Next или возвращаемся для корректировок с помощью кнопки Back.

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

Одновременно с завершением процесса установки можно запустить компонент Stack Builder. Если запуск не требуется, снимаем флажок и нажимаем Finish.

Установка приложений с помощью Stack Builder

Запустить Stack Builder после окончания установки PostgreSQL можно с помощью команды:

C:\"Program Files"\PostgreSQL\15\bin\stackbuilder.exe

При этом путь будет зависеть от того, какие диск и каталог были выбраны во время установки СУБД.

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

Далее выбираем дополнительные приложения, которые нужны для работы с базой данных. При клике на любой из компонентов внизу окна появляется его краткое описание. Мы выбрали Migration Toolkit и PEM-HTTPD.

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

Дожидаемся загрузки файлов.

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

Если устройство все-таки было перезапущено раньше, чем завершилась установка всех приложений, можно запустить инсталляцию с помощью .exe файла. Он находится в папке, которая была выбрана на предыдущем шаге. В нашем примере это папка C:\Program Files\PostgreSQL\additions.

После установки всех выбранных приложений Stack Builder сообщает об успешном завершении.

Запуск pgAdmin 4

pgAdmin 4 — это утилита с простым визуальным интерфейсом, которая помогает работать с PostgreSQL. Ее можно установить отдельно от СУБД, скачав дистрибутив на официальном сайте.

Запустить pgAdmin 4 на устройстве можно через поиск в панели Пуск.

Еще один способ запустить утилиту — ввести в командной строке:

C:\Users\Selectel>C:\"Program Files"\PostgreSQL\15\"pgAdmin 4"\bin\pgAdmin4

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

Чтобы подключиться к серверу СУБД кликните на Servers в левой панели и в раскрывшемся списке выберите PostgreSQL 15. Нажмите на него правой кнопки мыши и выберите Connect Server.

Для подключения нужно ввести пароль пользователя postgres — его мы задавали на этапе установки PostgreSQL 15.

После аутентификации мы подключаемся к своему серверу PostgreSQL 15 и видим все объекты, которые на нем расположены.

Чтобы изменить язык в pgAdmin 4 нажмите в левом верхнем углу File → Preferences. Далее в открывшемся окне в разделе Miscellaneous выберите пункт User language и в выпадающем списке с доступными языками найдите Russian. Язык изменится после перезагрузки страницы.

Сетевой доступ к PostgreSQL

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

PowerShell

New-NetFirewallRule -Name 'POSTGRESQL-PORT' -DisplayName 'Selectel Port PostgreSQL2' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

Командная строка

netsh advfirewall firewall add rule name="Selectel Port PostgreSQL" dir=in action=allow protocol=TCP localport=5432

Графический интерфейс

В меню Пуск ищем брандмауэр и выбираем Брандмауэр Защитника Windows.

В левом меню кликаем на Дополнительные параметры.

Заходим в Правила для входящих подключений и в правой части окна выбираем Создать правило.

Устанавливаем настройки так, как показано на скринах ниже:

На шаге Профиль вы сами решаете, для каких профилей использовать новое правило. Мы оставим все профили включенными.

Задаем имя для нового правила и его описание.

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

Смена порта для подключения к СУБД

Чтобы изменить порт подключения к PostgreSQL находим файл postgresql.conf. Путь к нему — C:\Program Files\PostgreSQL\15\data.

В этом файле ищем строку с указанием port=5432 и меняем номер порта на нужный. Для поиска нужной строки поможет Ctrl+F.

После этого перезапускаем службу postgresql-x64-15 используя встроенное в Windows приложение Службы. Его можно найти в меню Пуск или с помощью команды Win+R на клавиатуре и далее services.msc.

Еще postgresql-x64-15 можно перезапустить с помощью PowerShell. Для этого в консоли PowerShell нужно ввести команду:

Restart-Service-Name postgresql-x64-15

Проверка открытого порта

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

Telnet на Windows 11

Через меню Пуск находим и открываем приложение Параметры.
В открывшемся окне слева выбираем Приложения.

3. В окне Приложения открываем пункт Дополнительные компоненты.

4. В поисковой строке вводим Другие компоненты и открываем указанный параметр.

5. В открывшемся окне находим Клиент Telnet и включаем этот компонент, нажав кнопку ОК.

Telnet на Windows 10

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

2. В открывшемся окне находим Программы и компоненты.

3. Кликаем слева на Включение или отключение компонентов Windows.

4. В открывшемся окне находим Клиент Telnet и включаем этот компонент, нажав кнопку ОК.

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

telnet <IP-address> <Port>

Где IP-address это IP-адрес сервера или его имя, а Port — номер порта, по которому будет производится подключение. Например,

C:\Users\Selectel>telnet 10.0.10.55 5432

Если подключение произошло успешно и порт открыт, будет отображаться пустая командная строка, а имя окна изменится на Telnet <IP-address>.

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

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

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

Далее перейти в каталог \bin, так как все основные инструменты PostgreSQL лежат именно в этой папке:

C:\Users\Selectel>cd C:\Program Files\PostgreSQL\15\bin

С помощью параметра -V можно проверить версию СУБД, которая была установлена:

C:\Program Files\PostgreSQL\15\bin>psql -V

Для корректного отображения русских символов в Windows 10 и 11 советуем сменить кодировку на Windows-1251:

C:\Program Files\PostgreSQL\15\bin>chcp 1251

Чтобы создать новую базу данных есть команда:

C:\Program Files\PostgreSQL\15\bin>createdb -U postgres SelectelDatabase

Где:
createdb — сама команда создания новой базы данных;
-U — параметр для указания имени пользователя;
postgres — имя пользователя;
SelecterDatabase — название новой базы данных.

Теперь проверим была ли создана новая база данных. Введем команду с параметром -l, который выводит список доступных баз данных:

C:\Program Files\PostgreSQL\15\bin>psql -U postgres -l

Получаем список:

Чтобы создать нового пользователя воспользуемся командой:

C:\Program Files\PostgreSQL\15\bin>createuser -U postgres TRex

Где:
postgres — пользователь с правами создавать других пользователей;
TRex — имя нового пользователя.

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

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

postgres=# ALTER ROLE “TRex” WITH PASSWORD 'password';

В команде вместо ‘password’ указать новый пароль.

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

Чтобы изменить права для новой роли, используется команда ALTER ROLE. Параметр CREATEDB дает право на создание базы данных:

postgres=# ALTER ROLE "TRex" CREATEDB;

Для просмотра ролей и их описания используется команда:

postgres=# \du

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

Для выхода из postgres можно использовать команду \q или exit.

С помощью командной строки можно также создавать таблицы и заполнять их данными. Для этого сначала подключимся к базе данных SelectelDatabase:

C:\Program Files\PostgreSQL\15\bin>psql -U "postgres" "SelectelDatabase"

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

SelectelDatabase=# CREATE TABLE Colors
SelectelDatabase-# (idcolor serial PRIMARY KEY,
SelectelDatabase(# namecolor varchar(255) NOT NULL);

Теперь добавим в созданную таблицу данные:

Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('black');
Вывод:
INSERT 0 1

Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('red');
Вывод:
INSERT 0 1

Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('white');
Вывод:
INSERT 0 1

Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('blue');
Вывод:
INSERT 0 1

Выведем на экран содержимое таблицы Colors и увидим, что данные успешно записались:

SelectelDatabase=# SELECT * FROM Colors;

Таблица:

С помощью команды \dt можно посмотреть весь список существующих таблиц в базе данных:

SelectelDatabase=# \dt

Все таблицы:

Команда \d позволяет вывести информацию о конкретной таблице. Так, указав таблицу Colors, мы видим параметры с которыми создана данная таблица:

SelectelDatabase=# \d 'Colors'

Параметры таблицы:

Таблицу можно удалить с помощью функции DROP:

SelectelDatabase=# DROP TABLE Colors;

Query Tool — это редактор SQL запросов в pgAdmin 4 с удобным графическим интерфейсом. На русском редактор называется Запросник.

Открыть запросник можно, нажав на одну из указанных на скриншоте кнопок:

Появляется пустое стартовое окно запросника.

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

Вводим запрос:

CREATE TABLE "flight"
    (
   	 idflight SERIAL PRIMARY KEY,
   	 Apoint VARCHAR(255) NOT NULL,
   	 Bpoint VARCHAR(255) NOT NULL,
   	 Atime timestamp NOT NULL,
   	 Btime timestamp NOT NULL
    );
INSERT INTO "flight" (Apoint, Bpoint, Atime, Btime) VALUES
    ('Saint-Petersburg', 'Novosibirsk', '2022-11-24 10:05:00', '2022-11-24 14:15:00'),
    ('Saint-Petersburg', 'Samara', '2022-11-21 16:45:00', '2022-11-21 18:00:00'),
    ('Saint-Petersburg', 'Moscow', '2022-11-20 13:20:00', '2022-11-20 14:25:00'),
    ('Saint-Petersburg', 'Netherlands', '2022-11-23 02:30:00', '2022-11-23 05:15:00'),
    ('Saint-Petersburg', 'Tashkent', '2022-11-27 06:25:00', '2022-11-27 10:25:00');

Чтобы отобразить введенную информацию выполним команду:

SELECT * FROM "flight";

Получаем вот такую таблицу с рейсами:

С использованием SQL запроса можно отсортировать данные в таблице по определенным условиям. Например укажем, что дата вылета должна быть после полуночи 22 ноября 2022 года и точкой прилета не должен быть Ташкент.

SELECT * FROM "flight" WHERE
    "atime" > '2022-11-22 00:00:00' AND
    "bpoint" != 'Tashkent';

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

Заключение

В этой инструкции мы рассмотрели, как установить PostgreSQL на устройство с десктопной версией ОС Windows 10 и 11, а также:

установили дополнительные приложения с помощью утилиты Stack Builder;
создали правила файрвола для доступа к PostgreSQL по сети;
поработали с СУБД через командную строку Windows;
установили и русифицировали pgAdmin 4;
рассмотрели работу запросника Query Tool.

Источник

Установка PostgreSQL на Windows
- Шаг 1. Загрузите инсталлятор
- Шаг 2. Запустите установщик
- Шаг 3. Процесс установки
- Шаг 4. Каталог для хранения данных
- Шаг 5. Задайте пароль
- Шаг 6. Конфигурация порта
- Шаг 7. Проверьте настройки
- Шаг 8. Запустите установку
- Шаг 9. Установка завершена
Использование pgAdmin

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

PostgreSQL — это передовая open-source система управления базами данных корпоративного класса, известная высокой стабильностью, производительностью, расширенным функционалом и отказоустойчивостью. СУБД PostgreSQL поддерживает как реляционные запросы SQL, так и нереляционные JSON и по своим качествам ничем не уступает коммерческим аналогам (Oracle, SQL Server). При этом PostgreSQL распространяется по свободной лицензии и имеет открытый исходный код — в том числе и это уже более 30 лет привлекает к ней пользователей.

Прежде всего убедитесь, что выполнены следующие предварительные условия:

версия Windows совместима с версией PostgreSQL, которую вы планируете установить (обратите внимание, что на 32-битные версии Windows установить PostgreSQL не получится);
конфигурация системы соответствует необходимым аппаратным требованиям, таким как объём оперативной памяти и дискового пространства;
для установки нового программного обеспечения на ПК или виртуальную машину с Windows вам необходимы права администратора.

Шаг 1. Загрузите инсталлятор

Посетите страницу загрузки СУБД PostgreSQL для Windows на сайте проекта. Щёлкните по ссылке «Скачать инсталлятор», которая перенаправит вас на веб-сайт EnterpriseDB, где можно загрузить установщики версий для Windows. Обратите внимание, что enterprisedb.com использует Amazon CloudFront для доставки контента, поэтому доступ из некоторых регионов может быть заблокирован — в этом случае используйте альтернативные способы загрузки.

Выберите нужную версию и загрузите исполняемый файл установщика.

Шаг 2. Запустите установщик

Перейдите в папку, куда был загружен установщик. Дважды щёлкните исполняемый файл, чтобы начать процесс установки PostgreSQL. Если от службы контроля учётных записей появится запрос на подтверждение действия, нажмите «Да», чтобы разрешить установщику внести изменения в систему. Нажмите «Далее» (Next) на экране приветствия.

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

Шаг 3. Процесс установки

Отметьте галочкой следующие компоненты в мастере установки:

PostgreSQL Server: компонент, обязательный для работы самой базы данных.
pgAdmin 4: графический пользовательский интерфейс (GUI). Рекомендуется для более простого администрирования баз данных.
StackBuilder: менеджер пакетов, добавляющий расширения и драйвера.
Command Line Tools: поддержка командной строки.

Шаг 4. Каталог для хранения данных

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

Шаг 5. Задайте пароль

Задайте надёжный пароль для учётной записи суперпользователя. Суперпользователь создаётся автоматически и получает имя «postgres».

Шаг 6. Конфигурация порта

Укажите номер порта, который будет прослушивать сервер PostgreSQL. Порт по умолчанию — 5432. Оставьте его без изменений, если нет особых причин использовать другой порт. Нажмите «Далее».

Шаг 7. Проверьте настройки

Этот экран предоставляет сводку выбранных параметров установки. Внимательно проверьте их и внесите изменения, если необходимо. Нажмите «Далее» для подтверждения.

Шаг 8. Запустите установку

Установщик начнёт копирование файлов и настройку PostgreSQL, это может занять несколько минут. Если вы выбрали StackBuilder, то может появиться запрос на его запуск после установки. Вы можете запустить его для установки дополнений или отложить это действие.

Шаг 9. Установка завершена

После завершения установки нажмите «Готово», чтобы выйти из мастера.

Проверьте, как прошла установка. Откройте окно командной строки. Перейдите в каталог PostgreSQL C:\Program Files\PostgreSQL\16\bin (число обозначает ветку PostgreSQL). Введите следующую команду и нажмите «Enter»:

psql --version

Должна отобразиться информация об установленной версии:

Использование pgAdmin

Откройте приложение pgAdmin (ярлык находится в меню «Пуск», также найти его можно через строку поиска Windows).

Создайте подключение к серверу. Кликните по Servers в левой области окна →

Create → Server. Укажите следующие данные:

Имя: на ваше усмотрение.
Имя хоста: «localhost» для локальных установок, IP-адрес для удалённых.
Порт: порт, который вы настроили во время установки (по умолчанию — 5432).
Служебная база данных.
Имя пользователя: введите «Postgres» в качестве учётной записи суперпользователя.
Пароль: введите пароль, который вы задали во время установки.

Кроме того убедитесь, что порт PostgreSQL разрешён в брандмауэре (если требуется удалённый доступ).

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

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

В списке Databases будут перечислены все базы данных. Пока здесь также только одна — «postgres», созданная по умолчанию. Если кликнуть по названию БД, то откроется список с настройками.

Ниже находится пункт Login/Group Roles, предназначенный для управления ролями и группами.

Последний пункт Tablespaces — управление местом хранения таблиц.

Чтобы создать новую базу данных, кликните по Databases правой кнопкой мыши и в выпадающем списке выберите Create, затем Database. Укажите название, владельца (выберите из списка) и нажмите Save — БД появится в меню, после чего её можно редактировать. Все новые БД создаются по образцу «template1», измените параметры «template1», чтобы настроить шаблон.

Читайте в блоге:

Что такое Active Directory в Windows
Установка Windows Server: комплексный подход к сетевой инфраструктуре и защите данных
Настройка DFS на Windows Server

Источник

PostgreSQL — одна из самых популярных и мощных систем управления реляционными базами данных (СУБД). У неё открытый исходный код, плюс она абсолютно бесплатная. Система помогает создавать и хранить базы данных, а также работать с ними на языке SQL (Structured Query Language). Без СУБД это будет невозможно, поскольку технически БД — это просто файлы в определённом формате.

PostgreSQL используется как для небольших проектов, так и для крупных корпоративных приложений благодаря своей надёжности, расширяемости и поддержке обширного набора SQL-стандартов.

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

В прошлом году PostgreSQL заняла четвёртую строчку в рейтинге самых популярных СУБД, уступив только Oracle, MySQL и Microsoft SQL Server. Вот несколько её преимуществ:

Опенсорс. PostgreSQL — это СУБД с открытым исходным кодом. Это значит, что она доступна для использования, модификации и распространения без каких-либо лицензионных сборов.
Надёжность. Эта система предоставляет несколько уровней защиты данных и механизмов восстановления после сбоев, что гарантирует минимальные потери данных и стабильную работу системы. Среди механизмов надежности — журналирование транзакций (Write-Ahead Logging, WAL) и поддержка транзакции ACID.
Полная поддержка SQL-стандартов. PostgreSQL позволяет выполнять сложные запросы SQL, включая вложенные и агрегированные, поддерживает различные типы данных от базовых числовых и строковых типов до сложных JSON и XML, а также предлагает расширенные функции: оконные функции, общие табличные выражения (CTE) и множество встроенных функций для работы с датами, строками и географическими данными.
Объектно-реляционная модель. СУБД поддерживает одновременно реляционный подход, то есть данные хранятся в виде записей, которые связаны друг с другом с помощью relations (отношения), и объектный подход, то есть данные представлены в виде объектов с атрибутами и методами, как в объектно-ориентированном программировании (ООП).
Поддержка JSON и NoSQL. PostgreSQL предлагает средства для работы с JSON-документами, что позволяет использовать его как гибридную СУБД, объединяющую реляционные и NoSQL подходы. Она поддерживает два формата хранения JSON: JSON (текстовый) и JSONB (бинарный). JSONB оптимизирован для быстрого поиска и индексирования.
Мощные средства индексирования. Среди типов индексов в этой системе управления БД — B-Tree, Hash, GIN и GiST, BRIN.
Возможность использовать другие языки программирования. Postgre позволяет писать собственные функции и поддерживает больше языков программирования, чем остальные СУБД. Среди них — C, C++, Python, Java, PHP, Ruby, Lua и даже JavaScript (есть поддержка V8).

Кстати, на основе PostgreSQL создана отечественная СУБД Postgres Pro — в качестве замены Oracle. В рамках импортозамещения российским аналогом пользуются многие крупные компании и организации, например, Сбер, ФНС, Минфин, «Газпромнефть» и другие.

Как установить и запустить PostgreSQL

Перед тем как приступить к установке PostgreSQL, нужно определить, какая версия вам нужна. Каждая версия PostgreSQL включает обновления, улучшения и новые функции — выбор зависит от совместимости с вашими приложениями. Мы будем рассматривать установку и запуск на трёх операционных системах на примере последней 16 версии.

Для работы с PostgreSQL на Windows и macOS может понадобится графический интерфейс pgAdmin. В нем есть Query Tool, с помощью которого пишутся и выводятся SQL-запросы.

Установка и запуск на Windows

Скачайте установочный файл с официального сайта PostgreSQL.
Запустите установку. В появившемся мастере установки следуйте этим шагам:

Выберите директорию установки и отметьте галочкой эти компоненты:

Основы PostgreSQL для начинающих: от установки до первых запросов 1

Компоненты для установки PostgreSQL

Укажите данные и задайте пароль для суперпользователя (обычно это postgres):

Основы PostgreSQL для начинающих: от установки до первых запросов 2

Меню для ввода пароля

Настройте порт для сервера (по умолчанию это 5432).
Определите каталог для хранения данных.

3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.

Основы PostgreSQL для начинающих: от установки до первых запросов 3

Интерфейс pgAdmin

Установка на macOS

Установить СУБД на macOS можно тремя способами.

С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.
Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
Через Homebrew. Homebrew — популярный пакетный менеджер для macOS. Убедитесь, что он установлен, и выполните команду:

После установки инициализируйте базу данных и запустите сервер:

			initdb /usr/local/var/postgres
pg_ctl -D /usr/local/var/postgres start

Убедитесь, что сервер работает с помощью команды psql:

Установка на Linux

Для установки на Ubuntu/Debian используйте команду в консоли:

			sudo apt install -y postgresql-common

			sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Для CentOS/RHEL:

			sudo yum install postgresql-server postgresql-contrib

На Ubuntu и Debian PostgreSQL автоматически инициализируется и запускается.

На CentOS и RHEL выполните:

			sudo postgresql-setup initdb
sudo systemctl start postgresql

На Linux тоже есть pgAdmin — его нужно скачать отдельно, однако он открывается не в приложении, а окне браузера. Однако большинство разработчиков, использующих эту операционную систему, работают с PostgreSQL в основном в консоли с помощью psql.

Основные команды SQL

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

CREATE — и все её вариации: CREATE DATABASE, CREATE TABLE, CREATE DOMAIN, CREATE USER и другие.

ALTER | DROP — изменить/удалить данные СУБД.

INSERT | UPDATE | DELETE/TRUNCATE — вставить/изменить/удалить данные в таблице.

SELECT, TABLE, WITH — получить строки из таблицы или представления.

Типы данных PostgreSQL

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

Числовые типы

Числовые типы включают 2-, 4- и 8-байтные целые, 4- и 8-байтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью.

Основы PostgreSQL для начинающих: от установки до первых запросов 4

Числовые типы данных PostgreSQL

Денежные типы

Здесь все проще, тип всего один.

Основы PostgreSQL для начинающих: от установки до первых запросов 5

Денежные типы данных

Символьные типы

В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.

Основы PostgreSQL для начинающих: от установки до первых запросов 6

Символьные типы данных

Типы даты/времени

Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю.

Основы PostgreSQL для начинающих: от установки до первых запросов 7

Типы времени/даты

Логический тип

В СУБД есть стандартный SQL-тип boolean, который может иметь состояния «true», «false» и «unknown» (оно представляется SQL-значением NULL).

Основы PostgreSQL для начинающих: от установки до первых запросов 8

Логический тип данных

Создаем первую таблицу в PostgreSQL

Для начала нужно завести базу данных:

			CREATE DATABASE mydatabase

Это создаст новую базу данных с именем mydatabase. Затем подключаемся к нашей БД:

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

			CREATE TABLE table_name (
    column1_name data_type [column_constraint],
    column2_name data_type [column_constraint],
    ...
    table_constraint
);

где

table_name: название новой таблицы.

column1_name, column2_name: названия столбцов в таблице.

data_type: тип данных столбца (например, INTEGER, VARCHAR(100), DATE).

column_constraint: опциональные ограничения на уровне столбца (например, NOT NULL, UNIQUE).

table_constraint: опциональные ограничения на уровне таблицы (например, PRIMARY KEY, FOREIGN KEY).

Рассмотрим пример создания таблицы employees с различными типами данных и ограничениями:

			CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary NUMERIC(10, 2),
    department_id INTEGER REFERENCES departments(id)
);

После выполнения этого SQL-запроса в вашей БД появится таблица employees с указанными столбцами и ограничениями (PRIMARY KEY, UNIQUE, NOT NULL и так далее).

Чтобы удалить базу данных или таблицу, понадобится команда drop:

			drop database <database_name>
drop table <table_name>

Модификация таблиц

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

Добавление столбца

			ALTER TABLE employees ADD COLUMN bio TEXT;

Эта команда добавляет новый столбец bio типа TEXT в таблицу employees.

Удаление столбца

			ALTER TABLE employees DROP COLUMN bio;

Удаляет столбец bio из таблицы employees.

Изменение типа данных столбца

			ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(150);

Изменяет тип данных столбца email на VARCHAR(150).

Получение информации о таблице

Команда выводит информацию о структуре таблицы employees, включая список столбцов, их типы данных и ограничения.

Изменение данных в таблице

PostgreSQL также имеет много функций для модификации строк и колонок в таблице. Ниже несколько примеров.

Добавление новой записи

			INSERT INTO employees (last_name, email) VALUES ('Ivanov', 'ivanivanov@example.com');

Вставляет новую запись в таблицу employees с указанными значениями для last_name и email.

Изменение существующих данных

			UPDATE employees 
SET email = 'ivan_ivanov@example.com' 
WHERE last_name = 'ivanov';

Обновляет адрес электронной почты пользователя с фамилией ivanov.

Удаление записей

			DELETE FROM employees 
WHERE last_name = 'ivanov';

Удаляет записи из таблицы employees, где last_name равен ivanov.

Запросы с командой SELECT

Выбор всех записей из таблицы:

Фильтрация данных:

			SELECT * FROM employees 
WHERE email LIKE '%@example.com';

Сортировка данных:

			SELECT * FROM employees 
ORDER BY hire_date 
DESC;

Команда сортирует записи по столбцу hire_date в порядке убывания.

Группировка данных:

			SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

Запрос вернет список департаментов и количество сотрудников в каждом из них.

Это наиболее простые запросы в PostgreSQL. Сложные будут включать, например, объединение данных через оператор JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN и другие).

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

С помощью индексов

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

Создание индекса:

			CREATE INDEX idx_username ON employees(last_name);

Этот индекс ускоряет поиск записей по last_name.

Использование уникальных индексов:

			CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Он обеспечивает уникальность значений в столбце email.

С помощью транзакций и блокировок

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

Начало и завершение транзакции:

			BEGIN;
UPDATE employees 
SET email = 'ivan_updated@example.com' 
WHERE last_name = 'ivanov';
COMMIT;

Откат транзакции:

			BEGIN; 
UPDATE employees 
SET email = 'ivan_failed@example.com' 
WHERE last_name = 'ivanov'; 
ROLLBACK;

PostgreSQL — мощная и многофункциональная СУБД. В этом руководстве мы рассмотрели основные аспекты установки, создания баз данных и выполнения простых SQL-запросов. На самом деле PostgreSQL не такая страшная, как кажется на первый взгляд. Главное — знать азы.

Источник

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

В статье мы рассмотрим установку PostgreSQL на VDS с Ubuntu 18.04 и особенности работы с ней.

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

PostgreSQL присутствует в официальных репозиториях Ubuntu, поэтому для установки достаточно выполнить:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

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

При установке будет автоматически создана роль и пользователь в системе Linux postgres, под которым можно будет начать работу с PostgreSQL.

Пользователи (роли)

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

В зависимости от настройки, роль можно рассматривать как пользователя базы данных или как группу пользователей. Роли могут владеть объектами базы данных (например, таблицами) и выдавать другим ролям разрешения на доступ к этим объектам. Также можно предоставить одной роли членство в другой роли (схоже с добавлением пользователя в группу), чтобы одна роль могла использовать привилегии другой роли.

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

sudo su - postgres

После чего запустите консоль Postgres:

psql

После завершения работы вы сможете выйти из консоли Postgres командой \q:

postgres=# \q

Так как для каждой созданной роли Postgres предполагает наличие базы данных с таким же именем и по умолчанию подключается именно к ней, имеет смысл создавать новую роль для каждой базы. Кроме того, если имя роли совпадает с именем пользователя, созданного в системе Linux, подключение к БД также упрощается.

Создание новой роли

Создать роль из консоли системы (не psql) можно с помощью команды:

createuser -P --interactive

Ключ -P позволит сразу задать пароль пользователю, а —interactive запустит интерактивный режим для указания дополнительных параметров.

Система поочередно запросит имя новой роли, ее пароль и повтор пароля, а также позволит указать привилегии: сделать ли роль суперпользователем, должны ли быть права на создание баз данных и создание других ролей. Нажимайте y / n и Enter для выбора.




          Картинка с сайта: timeweb.cloud

Создать роль из консоли Postgres можно с помощью команды CREATE ROLE.

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

\h CREATE ROLE

Чтобы создать новую роль выполните:

CREATE ROLE имя_роли WITH LOGIN CREATEDB CREATEROLE;

Далее задайте новому пользователю пароль:

\password имя_роли

Просмотр существующих ролей

Чтобы просмотреть созданных пользователей и их привилегии, перейдите в консоль Postgres:

psql

И выполните команду:

\du

Пример вывода:

                                   List of roles
 Role name |                        Attributes                          | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tmweb     | Create role, Create DB                                     | {}
 tweb      | Create role, Create DB                                     | {}

Нажмите q, чтобы закрыть вывод, и \q, если нужно выйти из консоли Postgres.

Удаление роли

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

dropuser имя_роли

Либо команду в консоли Postgres:

DROP ROLE имя_роли;

Смена пароля пользователя

Для смены пароля одной из ролей подключитесь к Postgres от суперпользователя (postgres или другой роли с такими привилегиями), после чего выполните:

ALTER USER имя_роли WITH PASSWORD 'новый_пароль';

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

Как правило, файл размещается в директории /var/lib/postgresql. Проверить его расположение можно в терминале системы с помощью:

grep postgres /etc/passwd | cut -d ':' -f 6

Откройте файл, указав корректный путь к нему:

sudo nano /var/lib/postgresql/.psql_history

Удалите запись с паролем и сохраните изменения.

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

Создать базу из консоли системы можно следующим образом:

createdb имя_базы

Для создания базы из консоли Postgres, используйте:

CREATE DATABASE имя_базы;

Вывести список баз можно командой \l в консоли Postgres:

postgres=# \l

Подключение к базе данных

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

psql

Например, если на сервере создан пользователь timeweb, а в Postgres — одноименная роль и база, подключиться можно с помощью данной команды:




          Картинка с сайта: timeweb.cloud

После подключения можно выполнить команду \conninfo, чтобы посмотреть информацию о текущем соединении.

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

psql -d имя_базы

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

2.1. Если имя роли и имя базы совпадают, достаточно выполнить:

psql -U имя_роли -h localhost -W




          Картинка с сайта: timeweb.cloud

Как видно из вывода

\conninfo, мы подключены к базе tmweb от одноименной роли.

2.2. Если имя роли и базы отличаются, базу также нужно указать в команде:

psql -U имя_роли -d имя_базы -h localhost -W




          Картинка с сайта: timeweb.cloud

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

\c имя_базы




          Картинка с сайта: timeweb.cloud

Резервные копии (экспорт и импорт дампа)

При установке PostgreSQL на сервер устанавливаются утилиты pg_dump и pg_restore, с помощью которых вы сможете из консоли Linux создавать резервные копии базы данных (pg_dump) и восстанавливать данные из них (pg_restore).

Создание резервной копии

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

pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_базы

Параметры:

хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
формат_дампа — формат, в котором будет сохранен дамп; указывается буквами c, t или p: ‘с‘ (custom — архив .tar.gz), ‘t‘ (tar — архив .tar), ‘p‘ (plain — текстовый файл без сжатия, как правило, .sql);
путь_к_дампу — путь сохранения для файла дампа и имя файла;
имя_базы — имя базы данных, для которой создается резервная копия.

Например:

pg_dump -h localhost -U tmweb -F c -f /home/user/backups/dump.tar.gz tmweb

После выполнения команды будет запрошен пароль пользователя Postgres, указанного в команде (в примере — tmweb).

Восстановление из дампа

Импорт дампов, сохраненных в форматах .tar.gz и .tar, выполняется с помощью pg_restore:

pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу

Параметры:

хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
формат_дампа — формат, в котором был сохранен дамп; необходимо указать ‘с‘ для архива .tar.gz и ‘t‘ для архива .tar;
имя_базы — имя базы данных, в которую импортируется дамп;
путь_к_дампу — путь к файлу дампа и имя файла.

Например:

pg_restore -h localhost -U tmweb -F c -d new_db /home/user/backups/dump.tar.gz

Для импорта дампов в формате .sql используется cat:

cat путь_к_дампу | psql -h хост -U имя_роли имя_базы

Параметры:

путь_к_дампу — путь к файлу дампа и имя файла;
хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
имя_базы — имя базы данных, в которую импортируется дамп.

Например:

cat /home/user/backups/dump.sql | psql -h localhost -U tmweb new_db

Работа с шаблонами баз данных

При установке PostgreSQL по умолчанию создаются три базы данных: postgres, template0, template1.

Template0 и template1 — это шаблоны баз данных, из которых в дальнейшем будут создаваться пользовательские БД.

Фактически, когда вы выполняете команду CREATE DATABASE, Postgres создает клон базы template1. Если внести изменения в template1, они будут наследоваться всеми новыми создаваемыми базами. Это позволяет, например, добавить в template1 необходимые вам таблицы с данными или установить расширения, после чего не потребуется добавлять их для каждой новой базы.

Обратите внимание, что для установки расширений необходимо подключиться к template1 от суперпользователя (postgres или другой роли, имеющей данные привилегии).

Подключитесь к шаблону template1:

\c template1

И установите расширение:

CREATE EXTENSION название_расширения;

Например, если вы установите в template1 расширение pgcrypto, то в дальнейшем, при выполнении CREATE DATABASE, новые базы будут создаваться с уже установленным pgrypto.




          Картинка с сайта: timeweb.cloud




          Картинка с сайта: timeweb.cloud

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

Чтобы создать «чистую базу на основе template0, нужно выполнить:

CREATE DATABASE имя_базы TEMPLATE template0;

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

В этом случае нужно при создании базы указать template0 в качестве шаблона и указать требуемую кодировку и/или локаль, например:

CREATE DATABASE имя_базы TEMPLATE template0 ENCODING 'SQL_ASCII';

Пересоздание template1

С помощью template0 вы также можете вернуть базу template1 в исходный вид: для этого потребуется ее удалить, а после создать заново, на основе шаблона template0.

Сначала необходимо указать, что template1 не является шаблоном, чтобы удаление стало возможно:

UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';

Далее удалить template1:

DROP DATABASE template1;

И создать базу template1 заново, указав, что она будет являться шаблоном:

CREATE DATABASE template1 OWNER postgres TEMPLATE template0 is_template true;

Параметры и конфигурационные файлы

Узнать расположение конфигурационного файла (как правило, размещается по пути: /etc/postgresql/версия/main/postgresql.conf) можно с помощью:

SHOW config_file;

Узнать значение какого-либо параметра Postgres:

SHOW параметр;

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

pg_settings: будет выведено не только установленное значение, но также файл, в котором он назначен, максимальные и минимальные значения.

SELECT * FROM pg_settings WHERE name = 'параметр';

Например:

SELECT * FROM pg_settings WHERE name = 'max_connections';

Чтобы вывести информацию в удобном для восприятия формате, можно сменить представление командой \x:




          Картинка с сайта: timeweb.cloud

Другие полезные команды для управления Postgres

Вывести все параметры терминального командного меню Postgres:

psql --help

Просмотр баз данных из командной строки Linux:

psql -l

Список команд psql выводится с помощью \?:

postgres=# \?

Список SQL-команд (запросов):

postgres=# \h

Справка по конкретному SQL- запросу:

postgres=# \h запрос# Например:
postgres=# \h CREATE TABLE

Информация о подключении:

postgres=# \conninfo




          Картинка с сайта: timeweb.cloud

Выполнение shell-команд из командной строки Postgres:

postgres=# \! команда_shell# Например, чтобы вывести директорию нахождения:
postgres=# \! pwd

Подключение к базе данных:

postgres=# \c имя_базы




          Картинка с сайта: timeweb.cloud

Источник