Установка PostgreSQL, создание пользователя и базы данных

Откройте терминал и выполните следующие команды:

Обновите список пакетов:

   sudo apt update

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

   sudo apt install postgresql postgresql-contrib

Проверьте статус сервиса PostgreSQL:

   sudo systemctl status postgresql

Если сервис работает, вы увидите сообщение о том, что PostgreSQL активен.

2. Настройка PostgreSQL

PostgreSQL по умолчанию создает пользователя с именем postgres. Для взаимодействия с базой данных от имени этого пользователя выполните:

Войдите под пользователем postgres:

   sudo -i -u postgres

Откройте PostgreSQL shell:

   psql

Теперь вы находитесь в консоли PostgreSQL.

3. Создание базы данных

Создайте новую базу данных:

   CREATE DATABASE имя_базы;

Создайте нового пользователя и установите пароль:

   CREATE USER имя_пользователя WITH PASSWORD 'пароль';

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

   GRANT ALL PRIVILEGES ON DATABASE имя_базы TO имя_пользователя;

Выйдите из PostgreSQL shell:

\q

4. Подключение к базе данных

Теперь вы можете подключиться к базе данных как пользователь, которого вы создали:

Отключитесь от пользователя postgres:

   exit

Подключитесь к базе данных:

   psql -U имя_пользователя -d имя_базы

Теперь вы подключены к вашей базе данных и можете начинать с ней работать.

Дополнительно

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

\l

Для выхода из psql:

\q

Теперь PostgreSQL установлен, база данных создана, и вы можете взаимодействовать с ней.

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

Список основных команд для работы с базой данных в psql:

1. Подключение и выход

Подключение к базе данных:

  psql -U имя_пользователя -d имя_базы

Выход из psql:

\q

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

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

  CREATE DATABASE имя_базы;

Удаление базы данных:

  DROP DATABASE имя_базы;

Просмотр списка баз данных:

\l

3. Работа с пользователями

Создание пользователя:

  CREATE USER имя_пользователя WITH PASSWORD 'пароль';

Удаление пользователя:

  DROP USER имя_пользователя;

Изменение пароля пользователя:

  ALTER USER имя_пользователя WITH PASSWORD 'новый_пароль';

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

  GRANT ALL PRIVILEGES ON DATABASE имя_базы TO имя_пользователя;

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

Создание таблицы:

  CREATE TABLE имя_таблицы (
    id SERIAL PRIMARY KEY,
    имя_поля тип_данных,
    ...
  );

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

  \d имя_таблицы

Просмотр всех таблиц в базе данных:

\dt

Удаление таблицы:

  DROP TABLE имя_таблицы;

5. Вставка данных

Вставка данных в таблицу:

  INSERT INTO имя_таблицы (имя_поля1, имя_поля2, ...) VALUES (значение1, значение2, ...);

6. Чтение данных

Выбор всех данных из таблицы:

  SELECT * FROM имя_таблицы;

Выбор данных с условием:

  SELECT * FROM имя_таблицы WHERE имя_поля = 'значение';

7. Обновление данных

Обновление данных в таблице:

  UPDATE имя_таблицы SET имя_поля = 'новое_значение' WHERE имя_поля = 'значение';

8. Удаление данных

Удаление строки из таблицы:

  DELETE FROM имя_таблицы WHERE имя_поля = 'значение';

9. Управление транзакциями

Начало транзакции:

  BEGIN;

Завершение транзакции (подтверждение изменений):

  COMMIT;

Откат транзакции (отмена изменений):

  ROLLBACK;

10. Дополнительные команды

Просмотр версии PostgreSQL:

  SELECT version();

Смена базы данных:

  \c имя_базы

Транзакции

Транзакции в PostgreSQL (и в других СУБД) — это логическая единица работы, которая состоит из одного или нескольких SQL-запросов, выполняемых последовательно. Транзакция гарантирует, что все изменения в базе данных, которые происходят в рамках этой транзакции, будут выполнены полностью или не будут выполнены вовсе. Это помогает обеспечить целостность данных.

Основные свойства транзакций

Транзакции обладают четырьмя важными свойствами, известными как ACID:

Atomicity (Атомарность): Транзакция выполняется как единое целое. Либо все операции внутри транзакции завершаются успешно, либо ни одна не завершается.
Consistency (Согласованность): Транзакция переводит базу данных из одного согласованного состояния в другое. В случае неудачи база данных должна вернуться к исходному состоянию.
Isolation (Изолированность): Каждая транзакция изолирована от других. Текущие изменения транзакции не видны другим транзакциям до тех пор, пока транзакция не завершится (не произойдет фиксация).
Durability (Долговечность): После завершения транзакции (фиксации) ее изменения сохраняются и не теряются даже в случае сбоя системы.

Как работают транзакции в PostgreSQL

В PostgreSQL транзакции начинаются с команды BEGIN и завершаются с помощью команд COMMIT или ROLLBACK.

BEGIN: Начало транзакции. Все SQL-операции, которые будут выполнены после этой команды, будут частью транзакции.

   BEGIN;

COMMIT: Завершает транзакцию и подтверждает все изменения, сделанные в базе данных. После этого изменения становятся видимыми для всех пользователей и других транзакций.

   COMMIT;

ROLLBACK: Откатывает все изменения, сделанные в рамках транзакции, возвращая базу данных к состоянию, которое было до начала транзакции.

   ROLLBACK;

Пример использования транзакции:

Предположим, у нас есть таблица accounts, и мы хотим перевести средства с одного счета на другой:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

В этом примере два обновления (дебет одного счета и кредит другого) объединены в одну транзакцию. Если одно из обновлений не удастся (например, баланс станет отрицательным), можно отменить всю транзакцию с помощью ROLLBACK.

Зачем нужны транзакции

Целостность данных: Если в процессе выполнения произошла ошибка, можно откатить все изменения и вернуть базу данных к исходному состоянию.
Изолированность операций: Пока транзакция не завершена, её изменения не видны другим пользователям, что предотвращает возможные конфликты.
Логическая группировка операций: Позволяет сгруппировать несколько операций, чтобы они были выполнены как единое целое.

Транзакции — это важный механизм для обеспечения надежности и согласованности данных в базе данных.