Bezdepbonusu7Программирование: статьи и ответы

Базы данных

PostgreSQL: установка, создание базы и работа через psql

PostgreSQL - бесплатная реляционная СУБД, которую можно поставить на Windows или Ubuntu за несколько минут. Ниже: установка, подключение через psql, создание базы и пользователя, основные команды клиента и резервное копирование через pg_dump.

PostgreSQL: установка, создание базы и работа через psql
Содержание
  1. Что такое PostgreSQL и когда его выбирать
  2. Установка PostgreSQL на Windows
  3. Установка PostgreSQL на Ubuntu
  4. Первое подключение через psql
  5. Создание базы данных и пользователя
  6. Основные команды psql
  7. Основные типы данных
  8. Резервная копия: pg_dump и pg_restore
  9. Что делать, если не получается подключиться
  10. Частые вопросы

Что такое PostgreSQL и когда его выбирать

PostgreSQL (в разговоре "постгрес") - свободная реляционная СУБД: сервер, который хранит данные в таблицах и отвечает на запросы на языке SQL. Он бесплатен, работает на Windows, Linux и macOS и считается одним из самых надёжных вариантов для веб-проектов, аналитики и учебных задач. Поддерживает транзакции, индексы, JSON, расширения и сложные запросы.

Работа с базой выглядит так: сервер PostgreSQL запущен как служба, а вы подключаетесь к нему клиентом. Главный клиент командной строки - psql, он ставится вместе с сервером. Если вы ещё не знакомы с языком запросов, параллельно откройте примеры SQL-запросов для начинающих. А если выбираете между СУБД, пригодится обзор причин выбрать PostgreSQL.

Версии выходят раз в год, каждая ветка поддерживается пять лет. Актуальные стабильные ветки сейчас 18 и 19, а в командах ниже в качестве примера стоит номер 18: подставьте номер своей версии. Для обучения берите последнюю стабильную версию с официального сайта postgresql.org: команды из статьи одинаково работают на всех поддерживаемых версиях.

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

Для Windows есть официальный графический установщик. Его размещает компания EDB, ссылка на него ведёт со страницы postgresql.org/download/windows. Скачайте установщик для актуальной версии и запустите от имени администратора.

  1. Нажимайте "Next" до выбора каталога. Каталог по умолчанию подходит, как правило, он выглядит как C:\Program Files\PostgreSQL\18.
  2. В списке компонентов оставьте PostgreSQL Server, Command Line Tools и pgAdmin 4. Stack Builder нужен редко, его можно отключить.
  3. Выберите папку для данных. Подойдёт значение по умолчанию.
  4. Задайте пароль суперпользователя postgres. Запишите его в менеджер паролей: без него не подключиться.
  5. Порт оставьте 5432, локаль - по умолчанию. После "Next" и "Finish" служба запустится автоматически.

Окно с паролем и портом выглядит примерно так.

Окно установщика PostgreSQL на Windows с полями пароля и порта
Шаг установки: пароль суперпользователя postgres и порт сервера

Дальше проверьте, что всё работает. В меню "Пуск" появится папка PostgreSQL с пунктом SQL Shell (psql): запустите его и нажимайте Enter на вопросах о сервере, базе, порте и пользователе (значения по умолчанию подходят), а на запрос пароля введите пароль postgres. Если вы видите приглашение postgres=#, сервер работает.

Чтобы запускать psql из обычного терминала, добавьте папку bin в переменную PATH. Установщик по умолчанию PATH не меняет.

# PowerShell: проверка, что psql находится
& "C:\Program Files\PostgreSQL\18\bin\psql.exe" --version

# подключение из терминала
& "C:\Program Files\PostgreSQL\18\bin\psql.exe" -U postgres -h localhost

Первая команда печатает версию клиента, вторая запрашивает пароль и открывает сессию. Если вы не хотите ставить сервер в систему, на Windows можно работать через WSL и ставить PostgreSQL по инструкции для Ubuntu из следующего раздела.

Установка PostgreSQL на Ubuntu

В Ubuntu пакет есть в стандартных репозиториях. Этого достаточно для обучения, а свежие ветки можно получить из репозитория PostgreSQL (PGDG), инструкция лежит на странице postgresql.org/download/linux/ubuntu.

sudo apt update
sudo apt install postgresql postgresql-contrib

sudo systemctl status postgresql
sudo -u postgres psql -c "select version();"

Первые две строки обновляют список пакетов и ставят сервер с набором дополнительных расширений. Третья показывает состояние службы, она должна быть в статусе active. Последняя запускает psql от имени системного пользователя postgres и печатает версию сервера. Пароль не нужен, потому что локальный вход идёт по peer-аутентификации: вы заходите под именем системного пользователя.

Привычные команды работы со службами и пакетами собраны в статье про основные команды Linux. Если хотите вообще не ставить сервер на компьютер, запустите PostgreSQL в контейнере, как описано в материале про Docker.

ЗадачаWindowsUbuntu
Установкаграфический установщик EDBsudo apt install postgresql
Запуск клиентаSQL Shell (psql) или psql.exesudo -u postgres psql
Службаpostgresql-x64-18 в "Службах"systemctl status postgresql
Порт по умолчанию54325432
Пароль postgresзадаётся в установщикене нужен для локального входа через sudo

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

В psql два вида команд. Команды SQL заканчиваются точкой с запятой. Служебные команды самого клиента начинаются с обратной косой черты и точки с запятой не требуют. Пока вы не поставили ;, psql считает, что запрос не закончен, и показывает приглашение postgres-# вместо postgres=#.

postgres=# select version();
postgres=# \conninfo
postgres=# \q

Первая строка печатает версию сервера, вторая - с кем вы соединены (база, пользователь, порт), третья закрывает клиент. Параметры подключения задаются флагами: -U пользователь, -d база, -h адрес сервера, -p порт.

Создание базы данных и пользователя

Работать под суперпользователем postgres в проектах не стоит. Правильная схема: для приложения создаётся отдельная роль с паролем и отдельная база, владельцем которой она является.

CREATE ROLE app_user WITH LOGIN PASSWORD 'change_me_please';
CREATE DATABASE app_db OWNER app_user;

Первая команда создаёт роль, которой разрешён вход (LOGIN), вторая - базу с этим владельцем. Пароль замените своим. Владельцу базы не нужно выдавать отдельные права на создание таблиц, это важно для новых версий: начиная с PostgreSQL 15 обычные пользователи по умолчанию не могут создавать объекты в схеме public чужой базы.

Теперь подключитесь под новой ролью к новой базе. Из терминала Linux или PowerShell это делается так.

psql -U app_user -d app_db -h localhost

Параметр -h localhost принудительно включает парольную проверку по сети. Без него на Linux psql попробует войти через сокет под именем системного пользователя, и вход вернёт ошибку peer authentication, потому что системного пользователя app_user нет.

Создадим первую таблицу и заполним её данными, чтобы было что смотреть командами psql.

CREATE TABLE notes (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO notes (title) VALUES ('Первая заметка'), ('Вторая заметка');

SELECT id, title FROM notes ORDER BY id;

Столбец id заполняется автоматически, created_at получает текущее время. Последний запрос выведет обе строки. Если при отправке кириллицы консоль Windows показывает кракозябры, смените кодировку клиента командой \encoding UTF8 или откройте терминал с поддержкой UTF-8: подробнее об этой теме в статье про кодировку UTF-8.

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

Служебные команды psql начинаются с обратной косой черты. Вот набор, который нужен в первый месяц работы.

КомандаЧто делает
\lСписок баз данных
\c app_dbПереключиться на базу app_db
\dtСписок таблиц текущей схемы
\d notesСтруктура таблицы: столбцы, типы, индексы
\duСписок ролей (пользователей)
\dnСписок схем
\xВключить или выключить расширенный вывод строк столбцами
\timingПоказывать время выполнения запросов
\i file.sqlВыполнить команды из файла
\?Справка по командам psql
\qВыйти из psql

Так выглядит типичная сессия: сначала просмотр структуры, потом запрос.

Терминал psql со списком баз, таблиц и структурой таблицы notes
Сессия psql: команды \l, \c, \dt и \d

Команда \d notes удобнее всего, когда нужно вспомнить, какие столбцы есть у таблицы. Для вывода широких результатов включите \x: каждая строка будет показана вертикально, а не растянется за край экрана.

Частая ошибка новичков - забыть точку с запятой в конце SQL-команды. Приглашение меняется на postgres-#, и кажется, что psql завис. Просто допишите ; и нажмите Enter. Команды с обратной косой чертой точки с запятой не требуют.

Основные типы данных

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

ТипДля чегоПример значения
integer, bigintЦелые числа42
numeric(10, 2)Точные дробные, например деньги199.90
textСтроки любой длины'привет'
booleanИстина или ложьtrue
date, timestamptzДата и дата со временем и часовым поясом'2026-11-20'
jsonbДокумент JSON с индексацией'{"a": 1}'

Для хранения структур вида JSON полезно знать сам формат: он разобран в статье JSON.

Резервная копия: pg_dump и pg_restore

Утилита pg_dump выгружает одну базу в файл. Запускается она не внутри psql, а в обычном терминале. Подключение задаётся теми же флагами, что и у psql.

# 1. Сжатый дамп в "custom"-формате (восстанавливается pg_restore)
pg_dump -U app_user -h localhost -F c -f app_db.dump app_db

# 2. Обычный SQL-файл (читается глазами, восстанавливается через psql)
pg_dump -U app_user -h localhost -f app_db.sql app_db

# Восстановление custom-дампа в новую пустую базу
createdb -U postgres -h localhost app_db_restored
pg_restore -U postgres -h localhost -d app_db_restored --no-owner app_db.dump

# Восстановление SQL-файла
psql -U postgres -h localhost -d app_db_restored -f app_db.sql

Флаг -F c задаёт формат custom, -f - имя выходного файла, последний аргумент - имя базы. Чтобы восстановить файл, сначала создайте пустую базу (createdb), затем запустите pg_restore с флагом -d. Для всех баз сразу и для ролей используйте pg_dumpall.

Копию стоит хранить не на том же диске, что и сервер, а проверять восстановление хотя бы раз: бэкап, который не восстанавливали, нельзя считать рабочим. Для регулярных копий запускайте pg_dump по расписанию, как описано в статье про cron и crontab.

Что делать, если не получается подключиться

  • password authentication failed. Неверный пароль. Для суперпользователя его вводили при установке, для app_user - в команде CREATE ROLE. Пароль роли меняется командой ALTER ROLE app_user PASSWORD 'новый_пароль';.
  • psql не является внутренней командой. Папка bin не добавлена в PATH. Запустите SQL Shell из меню "Пуск" или укажите полный путь.
  • connection refused. Служба не запущена. На Windows проверьте её в оснастке "Службы", на Ubuntu выполните sudo systemctl start postgresql.
  • Peer authentication failed на Linux. Не указан -h localhost, и psql пытается войти по сокету. Либо заходите под sudo -u postgres psql, либо подключайтесь по сети с паролем.
  • database does not exist. Не создана база или опечатка в имени. Список баз смотрите командой \l.

Файлы настроек доступа (pg_hba.conf) и сервера (postgresql.conf) лежат в каталоге данных. Менять их нужно, только когда вы понимаете, зачем: например, чтобы разрешить подключения не только с локальной машины. Не открывайте порт 5432 в интернет без защиты и без ограничений по адресам. Для доступа к удалённому серверу безопаснее работать через SSH-туннель, а ключи для него создаются так, как описано в статье про SSH-ключ.

Частые вопросы

Где скачать PostgreSQL для Windows?

На официальной странице postgresql.org/download/windows: она ведёт на графический установщик EDB. Берите последнюю стабильную версию, установка занимает несколько минут.

Какой пароль у пользователя postgres?

Тот, который вы ввели при установке на Windows. На Ubuntu пароль не задаётся: для входа используйте sudo -u postgres psql, а пароль при необходимости задайте командой ALTER ROLE.

Как посмотреть список баз и таблиц в psql?

Список баз показывает команда \l, таблицы текущей схемы - \dt, структуру таблицы - \d имя_таблицы, список ролей - \du.

Как создать базу данных и пользователя в PostgreSQL?

Выполните CREATE ROLE имя WITH LOGIN PASSWORD '...'; и затем CREATE DATABASE имя OWNER имя_роли;. Подключайтесь командой psql -U роль -d база -h localhost.

Как сделать бэкап базы PostgreSQL?

Используйте pg_dump -U пользователь -F c -f файл.dump база. Восстановить такой файл можно командой pg_restore -d новая_база файл.dump.

Почему psql зависает и не выполняет запрос?

Скорее всего, в конце SQL-команды нет точки с запятой, и psql ждёт продолжения. Поставьте ; и нажмите Enter.

Чем PostgreSQL отличается от MySQL?

Обе СУБД реляционные и бесплатные. PostgreSQL строже следует стандарту SQL и богаче по возможностям (jsonb, расширения), MySQL традиционно проще в базовой настройке.

Источники

SQLPostgreSQL

Похожие материалы

Версии программ и команды проверены на дату публикации. Если что-то изменилось, сверяйтесь с официальной документацией. Нашли неточность? Напишите на admin@bezdepbonusu7.win.