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
- Укажите данные и задайте пароль для суперпользователя (обычно это postgres):
Меню для ввода пароля
- Настройте порт для сервера (по умолчанию это 5432).
- Определите каталог для хранения данных.
3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.
Интерфейс 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
Денежные типы
Здесь все проще, тип всего один.
Денежные типы данных
Символьные типы
В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.
Символьные типы данных
Типы даты/времени
Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю.
Типы времени/даты
Логический тип
В СУБД есть стандартный SQL-тип boolean, который может иметь состояния «true», «false» и «unknown» (оно представляется SQL-значением NULL).
Логический тип данных
Создаем первую таблицу в 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 по шагам
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 — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.
Содержание:
- Установка PostgreSQL 11 в Windows 10
- Доступ к PostgreSQL по сети, правила файерволла
- Утилиты управления PostgreSQL через командную строку
- PgAdmin: Визуальный редактор для PostgresSQL
- Query Tool: использование SQL запросов в 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.
Совет. Для изменения порта в установленной PostgreSQL отредактируйте файл postgresql.conf по пути C:\Program Files\PostgreSQL\11\data.
Измените значение в пункте
port = 5432
. Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
chcp 1251 - Перейдите в каталог bin выполнив команду:
CD C:\Program Files\PostgreSQL\11\bin
Основные команды PostgreSQL:
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
По умолчанию все созданные базы хранятся в каталоге 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 и созданию демонстрационной базы данных для самостоятельного запуска запросов из курса
Если вы хотите не просто смотреть видео курса «Основы SQL», но и самостоятельно экспериментировать с SQL запросами и видеть результаты их выполнения на живой базе данных, то можете установить бесплатную систему управления базами данных PostgreSQL и создать в ней демонстрационную базу с данными, которые показаны в видео. Эта статья содержит подробные инструкции по установке и настройке.
Установка PostgreSQL
В учебном курсе «Основы SQL» для демонстрации работы SQL используется PostgreSQL. Сейчас это самая популярная из бесплатных систем управления базами данных. Все SQL запросы в курсе проверены на работоспособность именно в PostgreSQL. Однако большая часть запросов использует синтаксис стандарта ANSI SQL, поэтому они будут работать и в других системах, включая MySQL, Microsoft SQL Server и Oracle. Вы можете использовать любую систему управления базами данных, которая вам нравится, но я рекомендую PostgreSQL.
1. Загрузите PostgreSQL для вашей операционной системы на странице Downloads официального сайта. Я устанавливал на Windows, если вы используете другую операционную систему, то выбирайте соответствующие ссылки для загрузки. Примеры в курсе проверены на PostgreSQL 13, поэтому рекомендую устанавливать именно эту версию. Однако на предыдущих версиях, начиная с PosgreSQL 10, также все должно работать.
Инсталлятор для Windows и Mac OS загружается с сайта компании EDB, которая предоставляет платную поддержку для PostgreSQL. Однако PostgreSQL, которую вы установите с помощью этого инсталлятора от EDB, будет полностью бесплатной.
2. Запустите скачанный инсталлятор PostgreSQL.
Если вы устанавливаете PostgreSQL только для использования в курсе «Основы SQL», то можете оставить почти все настройки по умолчанию, кроме локали, для которой нужно выбрать «Russian, Russia» (русский язык в стране Россия).
Не забудьте запомнить или записать пароль пользователя postgres, он вам понадобится для подключения к базе и выполнения запросов!
После завершения установки инсталлятор предложит вам запустить Stack Builder для установки дополнительных утилит и компонентов. Этого можно не делать, просто снимите галочку в пункте «Stack Builder…» и нажмите кнопку «Finish».
На этом установка PostrgreSQL завершена.
Запуск и настройка pgAdmin
В курсе мы будем работать с PostrgreSQL через Web-интерфейс pgAdmin. Несмотря на Admin в названии, этот инструмент подходит не только администраторам, но и разработчикам.
pgAdmin устанавливается вместе с PostgreSQL. В Windows запустить pgAdmin можно в меню Пуск.
При первом запуске pgAdmin просит задать Master Password. Он будет использоваться для безопасного сохранения паролей к базам данных PosgreSQL, с которыми вы работаете через pgAdmin. Master Password можно выбрать любой, главное, запомните или запишите его.
Для удобства можно переключить интерфейс pgAdmin на русский язык. Для этого выберите меню File->Preferences, в появившемся окне в меню слева выберите Miscellaneous -> User Language, а затем в поле User Language справа «Russian».
Для вступления в силу настроек языка интерфейса нужно нажать кнопку «Save» и перезапустить pgAdmin.
После перезапуска выбирайте в левом меню Servers -> PostgreSQL, после чего pgAdmin запросит пароль пользователя postgres, который вы задали в процессе установки PostgreSQL. Введите этот пароль (можете поставить галочку «Save Password» чтобы pgAdmin запомнил пароль) и вы подключитесь к базе PostgreSQL.
Список существующих на сервере баз данных показывается в левом окне pgAdmin. Нас интересует база данных postgres, схема public и таблицы в ней. pgAdmin показывает много другой информации, не пугайтесь, если вы пока не понимаете, что это такое. Многое мы разберем в курсе, но преимущественно все это нужно только администраторам базы данных.
Пока в нашей базе нет никаких таблиц. Давайте создадим демонстрационные таблицы, которые используются в курсе, и заполним их данными.
Создание демонстрационной базы
1. Скачайте файл с демонстрационной базой данных курса «Основы SQL». Файл называется «sql_foundation» и имеет расширение .sql.
Файл содержит набор операторов SQL, которые создают используемые в курсе таблицы и заполняют их данными. Если вы пока не понимаете, что именно делают эти операторы, не расстраивайтесь. В курсе мы подробно рассмотрим работу каждого оператора и все будет понятно. На начальном этапе изучения курса необходимо просто запустить этот файл в pgAdmin.
2. Загрузите скачанный файл в pgAdmin. Для этого в меню pgAdmin выберите Инструменты->Запросник (в английском вариант Query Tool). В панели инструментов Запросника выберите кнопку открытия файла и в появившемся окне выберите путь к загруженному sql файлу с демонстрационной базой курса.
3. Запустите загруженный файл в pgAgmin. Для этого нажмите на кнопку запуска в панели инструментов Запросника или на клавишу F5.
4. Проверьте правильность выполнения запроса. В нижней части экрана pgAdmin, на закладке «Сообщения» должны появиться результаты выполнения.
Основное, на что нужно обратить внимание — это сообщение «Запрос завершен успешно». Если такое сообщение появилось, значит все хорошо.
Если вы запускаете файл создания демонстрационной базы курса первый раз, то будет выведено несколько Замечаний, что таблицы не существуют. Их можно игнорировать.
Также в левой части интерфейса pgAdmin появится информация о созданных таблицах.
Итак, демонстрационные таблицы для курса «Основы SQL» созданы и заполнены данными, можно запускать SQL запросы.
Запуск SQL запросов в pgAdmin
В pgAdmin для запуска SQL запросов используется уже знакомый нам инструмент Запросник. Давайте откроем окно Запросника и напишем самый первый SQL запрос из видео про Оператор SELECT.
Запрос пишется в среднем окне, закладка Query Editor. Для запуска запроса нажимаем F5 или кнопку Execute в панели инструментов Запросника.
Полученные в ходе выполнения запроса данные показываются в нижней части окна, на закладке «Результат».
Использование SQL Shell (psql)
Если вы предпочитаете работать в командной строке, а не в громоздких приложениях типа pgAdmin, то можете использовать консольную утилиту для работы с PosgreSQL: SQL Shell (ранее она называлась psql).
SQL Shell, также как и pgAdmin, устанавливается совместно с PostgreSQL. В Windows запустить SQL Shell можно через меню Пуск.
После запуска SQL Shell задаст ряд вопросов о параметрах подключения к PostgreSQL, можно оставить все значения по умолчанию (если вы не меняли настройки при установке). После этого введите пароль пользователя postgres и можете начинать работать с базой данных.
Если вам не повезло, то в Windows SQL Shell запустится с неправильной кодировкой для русского языка, будет выдавать предупреждение и некоторые русские буквы будут выводиться неправильно.
Чтобы решить эту проблему, нужно прописать правильную кодировку в файл для запуска SQL Shell. В моем случае файл называется «C:\Program Files\PostgreSQL\13\scripts\runpsql.bat». В этот файл нужно добавить строку:
chcp 1251
После добавления строки с установкой правильной кодировки файл runpsql. bat стал выглядеть следующим образом.
Сохраняем файл runpsql. bat, перезапускаем SQL Shell, после этого проблем с русской кодировкой быть не должно.
Для создания демонстрационной базы курса в SQL Shell выполните следующую команду:
\i 'C:/путь/к/файлу/sql_foundation.sql'\i означает import — загрузка файла sql в базу данных.
Обратите, пожалуйста, внимание:
1. Путь к файлу sql_foundation.sql нужно указывать в одиночных кавычках. Если будете использовать двойные кавычки, то не заработает.
2. В пути используются прямые слеши (/), как в Linux/Unix, несмотря на то, что мы работаем под Windows. Если писать обратные слеши (\), как это принято в Windows, то будет выдаваться ошибка «Permission denied». Не очень информативное поведение SQL Shel.
Запросы в SQL Shell можно писать прямо в командной строке.
Запрос в SQL Shell может занимать несколько строк, как в примере на рисунке. Запрос запускается после ввода ; (точка с запятой) или команды \g.
Результаты выполнения запроса показываются сразу же после него.
Полезная функция SQL Shell — история команд. Если нажимать стрелки вверх или вниз, то можно увидеть, какие команды были запущены ранее и повторить интересующую вас команду.
Итоги
В этой статье вы узнали, как установить PostgreSQL и как создать в нем демонстрационную базу для курса «Основы SQL». Также вы настроили и научились использовать два инструмента работы с PostgreSQL: pgAdmin и SQL Shell. Не обязательно использовать оба, выбирайте тот, который вам больше нравится: pgAdmin с Web-интерфейсом или командную строку SQL Shell.
В процессе экспериментов с SQL запросами в курсе вы можете случайно повредить созданную базу. В этом нет ничего страшного, базу данных можно легко пересоздать повторно запустив sql файл с операторами по созданию базы. При этом все имеющиеся таблицы будут удалены, заново созданы и заполнены данными. Все изменения, которые вы вносили, будут потеряны.
Полезные ссылки
Установка 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