PostgreSQL


Установка и управление PostgreSQL

Установка и подключение

Шаг 1: Установка сервера

sudo apt update && sudo apt install -y postgresql postgresql-contrib

Шаг 2: Подключение к консоли psql

PostgreSQL создает пользователя `postgres`. Для подключения к СУБД нужно сначала переключиться на этого системного пользователя.

sudo -u postgres psql

Шаг 3: Установка пароля для пользователя postgres

По умолчанию у пользователя `postgres` нет пароля. Зададим его для безопасности.

Сначала войдите в psql (команда выше), затем выполните:

\password postgres

После этого выйдите из консоли командой `\\q`.

Управление базами данных

Все команды выполняются в консоли `psql`.

Создание БД (`CREATE DATABASE`)

CREATE DATABASE my_new_app;

Просмотр всех БД

\l

Подключение к другой БД

\c my_new_app

Удаление БД (`DROP DATABASE`)

Внимание! Команда необратима.

DROP DATABASE my_new_app;

Управление ролями (пользователями)

Все команды выполняются в консоли `psql`.

Создание роли (пользователя)

`LOGIN` означает, что роль может входить в систему. `PASSWORD` задает пароль.

CREATE ROLE newuser WITH LOGIN PASSWORD 'a_very_strong_password';

Предоставление прав

Дает пользователю все права на определенную базу данных.

GRANT ALL PRIVILEGES ON DATABASE my_new_app TO newuser;

Работа с данными (SQL и psql-команды)

Основные команды для взаимодействия с таблицами и данными внутри базы. Команды, начинающиеся с `\\`, являются внутренними командами клиента `psql`.

Просмотр таблиц и их структуры

После подключения к базе (`\\c my_new_app`) можно посмотреть список таблиц:

\dt

Посмотреть структуру конкретной таблицы (столбцы, типы, индексы):

\d users

Поиск и изменение данных

Найти всех пользователей, зарегистрированных после определенной даты:

SELECT * FROM users WHERE registration_date > '2023-01-01';

Изменить статус пользователя с ID 123 на 'inactive':

UPDATE users SET status = 'inactive' WHERE id = 123;

Сохранение результатов запроса в файл

`psql` имеет очень удобную команду `\\copy`, которая выполняет SQL-запрос на сервере и сохраняет результат в файл **на вашем локальном компьютере**, от имени вашего локального пользователя.

\copy (SELECT id, email FROM users WHERE is_active = true) TO '/home/myuser/active_users.csv' WITH CSV HEADER;

Проверка и ремонт таблиц

В отличие от MySQL, в PostgreSQL нет прямого аналога `REPAIR TABLE`. Благодаря своей транзакционной архитектуре (MVCC и WAL), повреждение данных на уровне страниц происходит крайне редко. Основной инструмент обслуживания — это `REINDEX`.

Перестроение индексов (`REINDEX`)

Если вы подозреваете, что индексы таблицы "раздулись" или повреждены (что может приводить к замедлению запросов), вы можете их перестроить. Эта операция безопасна.

Перестроить все индексы одной таблицы:

REINDEX TABLE my_table;

Перестроить все индексы в базе данных:

REINDEX DATABASE my_database_name;

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

Резервное копирование и восстановление

Эти команды выполняются из обычной командной строки Linux, от имени пользователя `postgres`.

Резервное копирование (Backup)

`pg_dump` создает файл бэкапа.

sudo -u postgres pg_dump my_database_name > backup.sql

Восстановление (Restore)

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

1. Создаем пустую базу и назначаем владельца в `psql`:

CREATE DATABASE my_database_name OWNER newuser;

2. Импортируем данные в командной строке Linux:

sudo -u postgres psql my_database_name < backup.sql

Установка pgAdmin

Для Linux (Debian/Ubuntu)

pgAdmin для Linux устанавливается как веб-приложение. Вы будете получать доступ к нему через браузер.

Шаг 1: Установка зависимостей и добавление репозитория pgAdmin.


# Установка зависимостей
sudo apt update && sudo apt install -y curl gpg
# Добавление GPG ключа
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
# Добавление репозитория
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Шаг 2: Установка веб-версии.


sudo apt install -y pgadmin4-web

Шаг 3: Запуск скрипта начальной настройки. Он попросит вас создать email и пароль для входа в веб-интерфейс pgAdmin.


sudo /usr/pgadmin4/bin/setup-web.sh

После завершения вы сможете получить доступ к pgAdmin в браузере по адресу http://ip-вашего-сервера/pgadmin4.

Для Windows

Установка для Windows предельно проста — скачайте инсталлятор и следуйте инструкциям мастера установки.

Настройка сервера PostgreSQL для удаленного подключения

Чтобы pgAdmin (или любой другой клиент) мог подключиться к вашему серверу PostgreSQL по сети, необходимо выполнить два ключевых шага на самом сервере.

Шаг 1: Разрешить прослушивание сетевых интерфейсов

По умолчанию PostgreSQL слушает подключения только с самого себя (`localhost`). Это нужно изменить.

Откройте основной конфигурационный файл (путь может отличаться в зависимости от версии, например, `/14/`):


sudo nano /etc/postgresql/14/main/postgresql.conf

Найдите строку (она может быть закомментирована):


#listen_addresses = 'localhost'

Раскомментируйте ее и замените `localhost` на `*`:


listen_addresses = '*' # Разрешить слушать на всех IP-адресах

Шаг 2: Настроить правила аутентификации (pg_hba.conf)

Это самый важный файл для управления доступом. Он определяет, **кто**, **откуда** и **как** может подключаться.

Откройте файл:


sudo nano /etc/postgresql/14/main/pg_hba.conf

Добавьте в **конец** файла новую строку, разрешающую подключение для вашего IP-адреса:


# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.10/32         scram-sha-256

Разбор строки:

Пример из жизни: Вы — системный администратор, и ваш рабочий компьютер имеет статический IP `192.168.1.10`. Вы добавляете это правило, чтобы только вы (и никто другой из сети) могли подключаться к серверу баз данных с помощью pgAdmin для администрирования.

Шаг 3: Перезапуск PostgreSQL

Чтобы все изменения вступили в силу, перезапустите службу.


sudo systemctl restart postgresql

Теперь вы можете открывать pgAdmin, нажимать "Add New Server" и вводить IP-адрес вашего сервера, имя пользователя и пароль для подключения.