Установка и управление 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