ПУТЕШЕСТВИЯ
РАЗНОЕ

PostgreSQL: команды

PostgreSQL: Объектно-реляционная система управления базами данных

Оглавление

Введение

PostgreSQL — это мощная объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом. Она поддерживает широкий спектр функций, включая сложные запросы, транзакции, полнотекстовый поиск, работу с JSON и многое другое. PostgreSQL идеально подходит для проектов, требующих высокой надежности, масштабируемости и гибкости.

Основные понятия

Перед погружением в технические детали важно понять основные понятия PostgreSQL:

  • База данных (Database): Хранилище структурированных данных.
  • Таблица (Table): Основная единица хранения данных, состоящая из строк и столбцов. Например, таблица "users" может содержать данные о пользователях:
CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL
    );
  • Столбец (Column): Поле таблицы, содержащее определённый тип данных.
  • Строка (Row): Запись данных в таблице.
  • Индекс (Index): Структура данных, ускоряющая поиск информации.
  • Транзакция (Transaction): Последовательность операций, выполняемых как единое целое.
  • JSON: Формат для хранения неструктурированных данных.
  • Массивы: Списки значений одного типа.

Установка PostgreSQL

Установка PostgreSQL на системах, основанных на Debian/Ubuntu, проста. Сначала обновите список пакетов:

sudo apt update

Затем установите PostgreSQL:

sudo apt install postgresql

Предварительная настройка

По умолчанию PostgreSQL разрешает подключения только с локальной системы. Чтобы разрешить подключение к серверу со всех компьютеров:

Отредактируйте файл конфигурации postgresql.conf:

sudo nano /etc/postgresql/[версия]/main/postgresql.conf

Найдите строку #listen_addresses = 'localhost' и измените её на:

listen_addresses = '*'

Отредактируйте файл pg_hba.conf:

sudo nano /etc/postgresql/[версия]/main/pg_hba.conf

Найдите строку в конец файла и выбирите нужный режим шифрования

Пример

local all postgres trust

Перезапустите службу PostgreSQL:

sudo systemctl restart postgresql

Работа со службой PostgreSQL

Для управления службой PostgreSQL используйте следующие команды:
  • Запуск службы:
sudo systemctl start postgresql
  • Автоматический запуск службы:
sudo systemctl enable postgresql
  • Остановка службы:
sudo systemctl stop postgresql
  • Перезапуск службы:
sudo systemctl restart postgresql
  • Проверка статуса службы:
sudo systemctl status postgresql
  • Подключение к PostgreSQL:
sudo su - postgres psql

Полезные команды psql

список алиасов psql
  • Выход из psql:
\q
  • Просмотр доступных баз данных:
\l
  • Просмотр списка таблиц:
\dt
  • Просмотр списка пользователей:
\du
  • Просмотр истории команд:
\s
  • Выполнение команд из файла:
\i filename.sql
  • Подключение к базе данных:
\connect db_name

Работа с базами данных

  • Создание базы данных:
CREATE DATABASE dbname;
  • Удаление базы данных:
DROP DATABASE dbname;

Работа с таблицами

  • Создание таблицы:
CREATE TABLE tablename (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype DEFAULT value
);
  • Удаление таблицы:
DROP TABLE tablename;
  • Просмотр структуры таблицы:
\d tablename
  • Добавление столбца:
ALTER TABLE tablename ADD COLUMN columnname datatype;
  • Удаление столбца:
ALTER TABLE tablename DROP COLUMN columnname;

Вставка, обновление и удаление данных

  • Вставка данных:
INSERT INTO tablename (column1, column2) VALUES (value1, value2);
  • Обновление данных:
UPDATE tablename SET column1 = value1 WHERE condition;
  • Удаление данных:
DELETE FROM tablename WHERE condition;

Выборка данных

  • Простая выборка:
SELECT * FROM tablename;
  • Выборка с условием:
SELECT column1, column2 FROM tablename WHERE condition;
  • Сортировка:
SELECT * FROM tablename ORDER BY column1 ASC|DESC;
  • Группировка:
SELECT column1, COUNT(*) FROM tablename GROUP BY column1;
  • Ограничение количества строк:
SELECT * FROM tablename LIMIT 10;

Индексы

  • Создание индекса:
CREATE INDEX indexname ON tablename (columnname);
  • Удаление индекса:
DROP INDEX indexname;

Транзакции

  • Начало транзакции:
BEGIN;
  • Фиксация транзакции:
COMMIT;
  • Откат транзакции:
ROLLBACK;

Расширенные возможности PostgreSQL

Работа с JSON

  • Вставка JSON:
INSERT INTO tablename (json_column) VALUES ('{"key": "value"}');
  • Выборка данных из JSON:
SELECT json_column->>'key' FROM tablename;

Работа с массивами

  • Вставка массива:
INSERT INTO tablename (array_column) VALUES ('{value1, value2}');
  • Выборка данных из массива:
SELECT array_column[1] FROM tablename;

Полнотекстовый поиск

  • Создание полнотекстового индекса:
CREATE INDEX idx_fts ON tablename USING GIN (to_tsvector('english', columnname));
  • Поиск по тексту:
SELECT * FROM tablename WHERE to_tsvector('english', columnname) @@ to_tsquery('search_term');

Восстановление резервных копий

Для восстановления данных из резервной копии используйте команду:

pg_restore -d название_базы_данных --no-owner название_файла_с_резервной_копией.backup -U postgres

Лучшие практики работы с PostgreSQL

  • Нормализация данных: Старайтесь нормализовать данные, чтобы избежать избыточности и улучшить целостность данных.
  • Использование индексов: Индексы ускоряют поиск данных, но их чрезмерное использование может замедлить вставку и обновление данных. Используйте индексы с умом.
  • Резервное копирование: Регулярно создавайте резервные копии базы данных, чтобы избежать потери данных в случае сбоя.

Частые ошибки и их решения

  • Ошибка подключения: Если вы не можете подключиться к PostgreSQL, убедитесь, что сервер запущен и настроен на прослушивание внешних подключений.
  • Ошибка прав доступа: Если у вас нет прав доступа к базе данных, проверьте настройки в файле pg_hba.conf и убедитесь, что вы используете правильного пользователя.

Заключение

В этой статье представлены базовые и расширенные возможности PostgreSQL, которые помогут вам эффективно работать с этой мощной СУБД.

Дополнительные ресурсы