Перейти к основному содержимому

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

Что такое базы данных и зачем они нужны​

Проблема, которую решают базы данных​

Представьте, что вы создаете приложение для интернет-магазина. Вам нужно хранить:

  • Информацию о товарах (название, цена, количество)
  • Данные о клиентах (имя, email, адрес)
  • Историю заказов
  • Отзывы покупателей

Плохие решения:

  • 📄 Excel-файлы — медленно, нет многопользовательского доступа, легко сломать
  • 📝 Текстовые файлы — сложно искать, нет структуры, нет защиты от ошибок
  • 💾 Переменные в коде — данные исчезают при перезапуске программы

Хорошее решение: База данных!

Что такое база данных?​

База данных (БД) — это организованная коллекция данных, которая:

  • Хранит информацию постоянно (не исчезает при выключении)
  • Позволяет быстро искать нужные данные
  • Обеспечивает одновременный доступ многих пользователей
  • Защищает данные от потери и повреждения
  • Проверяет корректность данных

Что такое СУБД?​

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

Примеры СУБД:

  • PostgreSQL — о ней мы и будем говорить
  • MySQL — популярна в веб-разработке
  • Oracle — используется в крупных компаниях
  • SQLite — маленькая, встраиваемая БД
  • MongoDB — нереляционная (NoSQL) база данных

Почему PostgreSQL?​

PostgreSQL (часто называют просто Postgres) — одна из самых мощных и надежных СУБД.

Преимущества:

  1. 🆓 Полностью бесплатная — даже для коммерческого использования
  2. 🔒 Надежная — не потеряет ваши данные даже при сбое
  3. 📚 Мощная — может обрабатывать миллионы записей
  4. 🌍 Популярная — используется Instagram, Apple, Spotify, Reddit
  5. 📖 Хорошо документирована — легко найти ответы на вопросы
  6. 🎓 Стандартная — знания PostgreSQL легко переносятся на другие СУБД

Где используется PostgreSQL:

  • Веб-приложения (сайты, интернет-магазины)
  • Мобильные приложения (серверная часть)
  • Аналитические системы
  • Финансовые приложения (банки, биржи)
  • Геоинформационные системы (карты)

Как работает база данных?​

┌─────────────┐
│ Приложение  │  ← Ваш сайт или программа
└──────┬──────┘
       │
       │ SQL-запросы (команды)
       ▼
┌─────────────┐
│ PostgreSQL  │  ← Обрабатывает запросы
└──────┬──────┘
       │
       │ Чтение/запись
       ▼
┌─────────────┐
│   Данные    │  ← Файлы на диске
└─────────────┘

Пример взаимодействия:

  1. Пользователь открывает страницу товара на сайте
  2. Сайт отправляет запрос к PostgreSQL: "Дай информацию о товаре #123"
  3. PostgreSQL ищет товар в базе данных
  4. PostgreSQL отправляет данные обратно сайту
  5. Сайт показывает товар пользователю

Установка PostgreSQL​

Для Windows​

Шаг 1: Скачивание

  1. Откройте браузер и перейдите на https://www.postgresql.org/download/windows/
  2. Нажмите "Download the installer"
  3. Выберите последнюю версию для Windows (На данный момент это PostgreSQL 18.x)
  4. Скачайте установщик для вашей системы (обычно x86-64)

Шаг 2: Установка

  1. Запустите скачанный файл (например, postgresql-18.x-windows-x64.exe)
  2. Нажмите "Next" на приветственном экране
  3. Выберите папку установки (можно оставить по умолчанию)
  4. Выберите компоненты для установки:
    • ✅ PostgreSQL Server (основной сервер)
    • ✅ pgAdmin 4 (графический интерфейс)
    • ✅ Command Line Tools (консольные инструменты)
    • Stack Builder можно не ставить
  5. Выберите папку для данных (оставьте по умолчанию)
  6. ВАЖНО: Придумайте пароль для пользователя postgres и ЗАПИШИТЕ его!
    • Пример хорошего пароля: MySecret123!
    • Этот пароль понадобится для подключения
  7. Оставьте порт 5432 (по умолчанию)
  8. Оставьте локаль по умолчанию
  9. Нажмите "Next" и дождитесь завершения установки

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

  1. Откройте меню "Пуск"
  2. Найдите папку "PostgreSQL 18"
  3. Запустите "SQL Shell (psql)"
  4. Нажимайте Enter на всех вопросах (оставляя значения по умолчанию)
  5. Когда появится запрос Password for user postgres:, введите ваш пароль
  6. Если вы увидели postgres=#, установка прошла успешно!

Для macOS​

Способ 1: Через Homebrew (рекомендуется)

  1. Установите Homebrew, если еще не установлен:

    /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

  2. Установите PostgreSQL:

    brew install postgresql@18

  3. Запустите PostgreSQL:

    brew services start postgresql@18

  4. Проверьте установку:

    psql postgres

Способ 2: Postgres.app (самый простой)

  1. Скачайте Postgres.app с https://postgresapp.com/
  2. Перетащите приложение в папку Applications
  3. Откройте Postgres.app
  4. Нажмите "Initialize" для создания нового сервера
  5. Сервер запущен! Можете подключаться

Настройка PATH (для Homebrew): Добавьте в ~/.zshrc или ~/.bash_profile:

export PATH="/opt/homebrew/opt/postgresql@18/bin:$PATH"

Для Linux (Ubuntu/Debian)​

Шаг 1: Обновление системы

sudo apt update
sudo apt upgrade

Шаг 2: Установка PostgreSQL

# Установка PostgreSQL и дополнительных инструментов
sudo apt install postgresql postgresql-contrib

# Проверка статуса
sudo systemctl status postgresql

Шаг 3: Настройка

# Переключение на пользователя postgres
sudo -i -u postgres

# Запуск psql
psql

# Выход
\q
exit

Шаг 4: Создание собственного пользователя (опционально)

sudo -u postgres createuser --interactive
# Введите ваше имя пользователя
# Ответьте 'y' на вопрос о суперпользователе

Для Linux (Fedora/CentOS/RHEL)​

# Установка
sudo dnf install postgresql-server postgresql-contrib

# Инициализация
sudo postgresql-setup --initdb

# Запуск и автозапуск
sudo systemctl start postgresql
sudo systemctl enable postgresql

Первое подключение через psql​

Что такое psql?​

psql — это консольный (текстовый) интерфейс для работы с PostgreSQL. Профессионалы часто предпочитают его графическим интерфейсам за скорость и мощность.

Запуск psql​

Windows:

  • Меню Пуск → PostgreSQL → SQL Shell (psql)

macOS/Linux:

psql postgres
# или
psql -U postgres

Первое подключение​

После запуска psql вы увидите приглашение:

postgres=#

Это значит, что вы подключены к базе данных postgres (она создается по умолчанию).

Основные команды psql​

Важно: Команды psql начинаются с обратного слэша \ и НЕ требуют точки с запятой.

-- Справка по командам
\?

-- Список всех баз данных
\l

-- Подключиться к другой базе данных
\c имя_базы

-- Список таблиц в текущей базе
\dt

-- Описание таблицы (структура)
\d имя_таблицы

-- Список пользователей
\du

-- Выход из psql
\q

-- Очистить экран
\! clear    # Linux/macOS
\! cls      # Windows

Выполнение SQL-команд​

SQL-команды должны заканчиваться точкой с запятой ;

-- Посмотреть текущую дату
SELECT CURRENT_DATE;

-- Простая арифметика
SELECT 2 + 2;

-- Посмотреть версию PostgreSQL
SELECT version();

Полезные настройки psql​

-- Включить расширенный вывод (для широких таблиц)
\x

-- Включить отображение времени выполнения запросов
\timing

-- Установить кодировку
\encoding UTF8

Пример первой сессии​

-- Подключились к psql
postgres=# SELECT CURRENT_DATE;
    date    
------------
 2024-01-15
(1 row)

postgres=# SELECT 10 * 5 AS результат;
 результат 
-----------
        50
(1 row)

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    
-----------+----------+----------+-------------+-------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8

postgres=# \q

Работа с pgAdmin​

Что такое pgAdmin?​

pgAdmin — это графический (визуальный) интерфейс для PostgreSQL. Он упрощает многие задачи и отлично подходит для начинающих.

Запуск pgAdmin​

Windows:

  • Меню Пуск → PostgreSQL → pgAdmin 4

macOS:

  • Applications → pgAdmin 4

Linux:

pgadmin4

При первом запуске pgAdmin попросит установить мастер-пароль — придумайте и запомните его.

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

  1. В левой панели найдите "Servers"
  2. Раскройте список (если сервера нет, создайте его):
    • Правый клик на "Servers" → Create → Server
  3. На вкладке "General":
    • Name: Local PostgreSQL (или любое имя)
  4. На вкладке "Connection":
    • Host: localhost
    • Port: 5432
    • Username: postgres
    • Password: ваш пароль, который вы установили при установке
    • ✅ Save password (чтобы не вводить каждый раз)
  5. Нажмите "Save"

Интерфейс pgAdmin​

После подключения вы увидите:

Servers
└── Local PostgreSQL
    ├── Databases
    │   ├── postgres (база по умолчанию)
    │   └── ...
    ├── Login/Group Roles (пользователи)
    └── Tablespaces

Основные разделы:

  • Databases — список баз данных
  • Schemas — схемы (обычно работаем с public)
  • Tables — таблицы с данными
  • Views — представления (виртуальные таблицы)

Query Tool — окно для SQL-запросов​

  1. Выберите базу данных postgres в левой панели
  2. Нажмите на иконку молнии ⚡ или Tools → Query Tool
  3. Откроется редактор SQL

Попробуйте выполнить:

SELECT 'Привет, PostgreSQL!' AS приветствие;

Нажмите F5 или кнопку ▶️ (Execute).

Просмотр данных​

Когда у вас появятся таблицы:

  1. Раскройте Databases → postgres → Schemas → public → Tables
  2. Правый клик на таблице → View/Edit Data → All Rows
  3. Данные отобразятся в удобной таблице

Создание базы данных через pgAdmin​

  1. Правый клик на "Databases"
  2. Create → Database
  3. В поле "Database" введите имя, например: test_db
  4. Нажмите "Save"

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

Планирование базы данных​

Перед созданием базы нужно понять, что мы будем хранить. Давайте создадим БД для интернет-магазина.

Что нам нужно хранить:

  • Товары (название, цена, количество)
  • Клиенты (имя, email, телефон)
  • Заказы (кто, когда, что заказал)

Но пока мы создадим только саму базу данных. Таблицы будем создавать на следующей неделе.

Создание базы через psql​

-- Подключитесь к psql
-- Создайте новую базу данных
CREATE DATABASE my_shop;

-- Посмотрите список баз данных
\l

-- Подключитесь к новой базе
\c my_shop

-- Проверьте, что вы в правильной базе
SELECT current_database();

Вывод будет примерно таким:

postgres=# CREATE DATABASE my_shop;
CREATE DATABASE

postgres=# \c my_shop
You are now connected to database "my_shop" as user "postgres".

my_shop=# SELECT current_database();
 current_database 
------------------
 my_shop
(1 row)

Создание базы через pgAdmin​

  1. Откройте pgAdmin
  2. Подключитесь к серверу
  3. Правый клик на "Databases" → Create → Database
  4. General:
    • Database: my_shop
    • Owner: postgres
  5. Definition:
    • Encoding: UTF8
    • Template: template0
    • Collation: оставьте по умолчанию
    • Character type: оставьте по умолчанию
  6. Нажмите "Save"

Удаление базы данных (если нужно)​

Внимание: Это удалит все данные безвозвратно!

Через psql:

-- Сначала отключитесь от базы
\c postgres

-- Удалите базу
DROP DATABASE my_shop;

Через pgAdmin:

  • Правый клик на базе данных → Delete/Drop
  • Подтвердите удаление

Параметры создания базы данных​

При создании можно указать дополнительные параметры:

CREATE DATABASE my_shop
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Параметры:

  • OWNER — владелец базы (обычно postgres)
  • ENCODING — кодировка (UTF8 для поддержки всех языков)
  • CONNECTION LIMIT — максимум подключений (-1 = без ограничений)

Структура PostgreSQL​

Иерархия объектов​

Кластер PostgreSQL (сервер)
│
├── База данных 1 (my_shop)
│   ├── Схема public
│   │   ├── Таблица products
│   │   ├── Таблица customers
│   │   └── Таблица orders
│   └── Схема archive
│       └── Таблица old_orders
│
└── База данных 2 (test_db)
    └── Схема public
        └── ...

Кластер — это один запущенный сервер PostgreSQL. В нем может быть много баз данных.

База данных — изолированная коллекция данных. Базы не видят друг друга.

Схема — пространство имен внутри базы. По умолчанию все создается в схеме public.

Таблица — структура для хранения данных (строки и столбцы).

Зачем нужны разные базы данных?​

  1. Изоляция проектов — база для магазина и база для блога не мешают друг другу
  2. Безопасность — можно дать пользователю доступ только к одной базе
  3. Резервное копирование — можно сохранить только нужную базу
  4. Организация — проще управлять отдельными проектами

Служебные базы данных​

После установки PostgreSQL создает три базы:

  1. postgres — база по умолчанию, для подключения и административных задач
  2. template0 — шаблон для создания новых баз (не трогайте!)
  3. template1 — еще один шаблон (тоже не трогайте!)

Правило: Не храните свои данные в базе postgres! Создавайте отдельные базы.

Пользователи и права​

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

Просмотр пользователей:

-- В psql
\du

-- Или SQL-запросом
SELECT usename FROM pg_user;

На следующих неделях мы научимся создавать дополнительных пользователей с ограниченными правами.

Практическое задание​

Задание 1: Установка и проверка​

  1. Установите PostgreSQL на свой компьютер
  2. Установите пароль для пользователя postgres и запишите его
  3. Запустите psql и выполните команду: 
    SELECT version();
  4. Скопируйте версию PostgreSQL (например, "PostgreSQL 18.1")

Задание 2: Работа с psql​

Выполните в psql следующие команды и запишите результаты:

-- 1. Посмотрите текущую дату
SELECT CURRENT_DATE;

-- 2. Вычислите 123 * 456
SELECT 123 * 456 AS результат;

-- 3. Посмотрите список баз данных
\l

-- 4. Посмотрите список пользователей
\du

Задание 3: Создание базы данных​

  1. Создайте базу данных с именем my_shop через psql
  2. Подключитесь к ней
  3. Проверьте, что вы подключены к правильной базе: 
    SELECT current_database();
  4. Сделайте скриншот результата

Задание 4: Работа с pgAdmin​

  1. Установите и запустите pgAdmin
  2. Подключитесь к серверу PostgreSQL
  3. Найдите созданную базу my_shop в дереве объектов
  4. Откройте Query Tool
  5. Выполните запрос: 
    SELECT 'Я научился работать с PostgreSQL!' AS сообщение;
  6. Сделайте скриншот окна pgAdmin с результатом

Задание 5: Эксперименты​

Попробуйте выполнить и понять результат:

-- Текущее время
SELECT CURRENT_TIME;

-- Текущие дата и время
SELECT NOW();

-- Сколько будет через 7 дней?
SELECT NOW() + INTERVAL '7 days';

-- Длина строки
SELECT LENGTH('PostgreSQL');

-- Верхний регистр
SELECT UPPER('привет, мир!');

-- Случайное число от 0 до 1
SELECT RANDOM();

-- Случайное число от 1 до 100
SELECT FLOOR(RANDOM() * 100 + 1)::INTEGER;

Контрольные вопросы​

Проверьте себя, ответив на эти вопросы:

  1. Что такое база данных и чем она лучше Excel-файла?
  2. Что такое СУБД? Приведите три примера СУБД.
  3. Какие основные преимущества PostgreSQL?
  4. В чем разница между psql и pgAdmin?
  5. Какая команда показывает список баз данных в psql?
  6. Как подключиться к другой базе данных в psql?
  7. Какие три служебные базы создаются при установке PostgreSQL?
  8. Почему не стоит хранить свои данные в базе postgres?
  9. Какая команда создает новую базу данных?
  10. Как выйти из psql?

Типичные проблемы и решения​

Проблема: Забыл пароль postgres​

Решение (Windows):

  1. Найдите файл pg_hba.conf (обычно в C:\Program Files\PostgreSQL\18\data\)
  2. Откройте его в блокноте от имени администратора
  3. Найдите строки с md5 и замените на trust
  4. Перезапустите службу PostgreSQL
  5. Подключитесь без пароля и смените его: 
    ALTER USER postgres PASSWORD 'новый_пароль';
  6. Верните md5 обратно в pg_hba.conf
  7. Снова перезапустите службу

Проблема: psql не найден (command not found)​

Решение: Добавьте PostgreSQL в PATH.

Windows: Добавьте в PATH путь C:\Program Files\PostgreSQL\18\bin

macOS/Linux: Добавьте в ~/.bashrc или ~/.zshrc:

export PATH="/usr/lib/postgresql/18/bin:$PATH"

Проблема: Не могу подключиться к серверу​

Проверьте:

  1. Запущена ли служба PostgreSQL?
    • Windows: Службы → PostgreSQL
    • Linux: sudo systemctl status postgresql
  2. Правильный ли порт? (по умолчанию 5432)
  3. Правильный ли пароль?

Проблема: Кириллица отображается неправильно​

Решение:

-- В psql установите кодировку
\encoding UTF8

-- При создании базы указывайте UTF8
CREATE DATABASE my_db ENCODING 'UTF8';

Полезные ресурсы​

📚 Официальная документация:

💬 Сообщества:

  • Stack Overflow с тегом [postgresql]
  • Reddit: r/PostgreSQL
  • Telegram: PostgreSQL Russian Community

🛠️ Инструменты: