PostgreSQL start

Примечание: Обратите внимение, что это не официальная документация, а попытка начинающего админа объяснить материал начинающим админам.

Теория

Виды баз данных

Базы данных бывают двух видов:

• Реляционные — (SQL) — база, где данные хранятся в формате таблиц, они строго структурированы и связаны друг с другом.

В них, обычно, планируются несерьезные, неглобальные изменения. Они могут использовать сложные запросы и набор отношений.

• Нереляционные — (NoSQL) — такие базы хранят данные без четких связей друг с другом и четкой структуры.

Они могут как иметь, так и не иметь отношения, не имеют четкой структуры и меняются в процессе.

Структура

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

БД (база данных)
└─СУБД (система управления базами данных)
  └─пользователь

Между базой данных и пользователем используется СУБД - система управления базами данных - специальное ПО обеспечивающее взаимодействие пользователя с базой данных.

Реляционная БД

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

• сущность (таблица) - представляет тип объектов, которые должны храниться в базе данных.
  • поле - часть таблицы, за которой закреплен определенный тип данных. (оно содержит название и описание)
    • запись - строка таблицы (поле) базы данных, содержащая набор значений и свойств размещенный в полях.

Примечание: В поле необходимо всегда указывать тип записи. При этом нельзя указывать одинаковые записи - т.к. это идентификатор.

Структура базы данных:

БД
├───таблица
│   ├───поле
│   │   └запись
│   ├───поле
│   │   └запись
│   └───поле
│       └запись
├───таблица
│   ├───поле
│   │   └запись
│   ├───поле
│   │   └запись
│   └───поле
│       └запись
└───таблица
    ├───поле
    │   └запись
    ├───поле
    │   └запись
    └───поле
        └запись

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

БД
├───автор
│   ├───библиография
│   ├───биография
│   ├───дата рождения
│   └───имя
├───книги
├───абонементы
└───сотрудник

В данном примере:

• БД - база данных;
  • автор, книги, абонементы, сотрудник - таблицы;
    • библиография, биография, дата рождения, имя - поля.

Типы данных

Так же, как переменные в программировании, поля в базах данных имеют различные типы. Какой тип Вы укажете (текстовый или числовой) такие данные там и будут хранится.

Числовые типы

• smallint - Маленькое целое число (2 байта)
• integer или int - Обычное целое число (4 байта)
• bigint - Большое целое число (8 байт)
• decimal(p,s) или numeric(p,s) - Точное число с фиксированной точностью
• real - Число с плавающей точкой (4 байта)
• double precision - Число с плавающей точкой (8 байт)
• serial - Автоинкрементное целое (4 байта)
• bigserial - Автоинкрементное большое целое (8 байт)

Строковые типы

• char(n) - Фиксированная длина строки
• varchar(n) - Строка переменной длины (до n символов)
• text - Строка произвольной длины

Типы даты и времени

• date - Только дата
• time - Только время
• timestamp - Дата и время без часового пояса
• timestamptz (или timestamp with time zone) - Дата и время с часовым поясом
• interval - Промежуток времени

Булев тип

• boolean Истина (true) или ложь (false)

Типы для идентификаторов

• uuid - Уникальный идентификатор (UUID)

Массивы

• integer[], text[], uuid[] и т.д.

JSON и XML

• json - Неструктурированные JSON-данные
• jsonb - Бинаризованный JSON (удобнее для индексации)
• xml - XML-данные

Специальные типы

• bytea - Бинарные данные ("byte array")
• inet - IP-адреса
• cidr - Сети IP-адресов
• macaddr - MAC-адреса

Практика

Установка PostgreSQL

Версии программы

версия	пакет
15	postgresql15-server
14	postgresql14-server
14-1C	postgresql14-1C-server
13	postgresql13-server
12	postgresql12-server
11	postgresql11-server
10	postgresql10-server
9.6	postgresql9.6-server

Установка

Установим сервер:

$ su -
# apt-get install postgresql15-server

Примечание: Перед запуском службы необходимо создать системные базы данных.

Внимание! Через systemctl postgresql initdb не работает!

# /etc/init.d/postgresql initdb

Примечание: При инициализации сервер запросит у вас создание нового пароля для администратора Postgresql

Добавим его в автозапуск и запустим:

# systemctl enable --now postgresql

Shell СУБД

Запуск

$ psql -U postgres
postgres=#

Примечание: Для входа введите пароль root, который вы создали при выполнении команды initdb.

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

Просмотр всех пользователей

postgres=# \du

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

=# DROP USER petr;

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

=# ALTER USER petr PASSWORD 'password'

Админские права

> GRANT ALL PRIVILEGES ON DATABASE "petrdb" to petr;

Создание ограниченного пользователя PostgresSQL

Примечание: Здесь у нас будут:

• имя пользователя - name
• пароль - Мойдодыр39 [en]
• имя db - petrdb

Создаем роль (пользователя) petr

• WITH LOGIN означает, что эта роль сможет входить в систему (подключаться к серверу PostgreSQL, как пользователь).
• PASSWORD 'Мойдодыр39' задаёт пароль для этой роли.

Примечание: В PostgreSQL "пользователь" = "роль с правами логина".

> CREATE ROLE petr WITH LOGIN PASSWORD 'Vjqljlsh39';

Подключаться к БД, создавать новые объекты и временные таблицы

• CONNECT — позволяет пользователю подключаться к базе petrdb.
• CREATE — позволяет пользователю создавать новые объекты внутри базы данных (например, новые схемы).
• TEMPORARY — позволяет создавать временные таблицы в базе данных.

Примечание: Эти права задаются на всю базу данных целиком.

> GRANT CREATE, CONNECT, TEMPORARY ON DATABASE petrdb TO petr;

Просмотр схемы, обращение к объектам в схеме

> GRANT USAGE ON SCHEMA public TO petr;

USAGE на схему public означает:

• пользователь может видеть схему,
• может обращаться к объектам в схеме (но без права изменять объекты, пока отдельно не выдано разрешение).

Примечание: Без права USAGE на схему пользователь не сможет использовать даже существующие таблицы.

Права на все существующие таблицы

> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public TO petr;

Данная команда даёт права на все существующие таблицы в схеме public:

• SELECT — читать данные,
• INSERT — вставлять данные,
• UPDATE — изменять данные,
• DELETE — удалять записи,
• REFERENCES — создавать внешние ключи (FOREIGN KEY) на эти таблицы.

Примечание: ⚡ Но только для уже существующих таблиц. Новые таблицы пока не затрагиваются.

Права на все существующие последовательности

> GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO petr;

Даёт права на все существующие последовательности (SEQUENCE, используются для генерации автоинкрементных ID) в схеме public:

• USAGE — право использовать последовательность (например, получать её текущее значение),
• SELECT — читать текущее значение последовательности,
• UPDATE — изменять значение (например, устанавливать nextval вручную).

Примечание: В PostgreSQL автонумерация часто завязана на последовательности.

Права по умолчанию на таблицы

> ALTER DEFAULT PRIVILEGES IN SCHEMA public
 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO petr;

Устанавливает права по умолчанию:

• На все будущие таблицы, которые будут созданы в схеме public.
• Пользователь name сразу будет иметь права SELECT, INSERT, UPDATE, DELETE, REFERENCES на новые таблицы без необходимости вручную давать права каждый раз.

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

Права по умолчанию для новых последовательностей

> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO petr;

• Аналогично предыдущему пункту, но для новых последовательностей:
  • Устанавливаются права на USAGE, SELECT, UPDATE для всех последовательностей, созданных в будущем в схеме public.

Подключение ограниченного пользователя к БД

Чтобы подключиться ограниченному пользователю к БД используйте следующую команду:

> psql -U user \database_name

Например:

> psql -U petr \petrdb

Управление БД

Создание/удаление

• Вывести список БД:

\l

• Создать БД:

CREATE DATABASE database_name;

• Создать, если не существует

SELECT 'CREATE DATABASE exp_db' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'exp_db')\gexec

• Удалить БД:

drop database name;

• Удалить, если существует

SELECT 'DROP DATABASE petr' 
WHERE EXISTS (SELECT FROM pg_database WHERE datname = 'petr')\gexec

Работа с сущностями

База данных
├подключиться к базе данных: \c ''name'';
├отобразить список баз данных: \l или \list;
├создать базу данных: createdb ''name'';
├удалить базу данных: drop database ''name''; или drop database if exists ''name'';
└── Сущность
    ├ вывести список сущностей (таблиц) (из-под подключенной БД): \dt
    ├ создать сущность (таблицу) (из-под подключенной БД): create table ''name''(''запись'' ''тип'' ''атрибуты'', ''запись'' ''тип'' ''атрибуты'');
    ├ удалить сущность (таблицу) (из-под подключенной БД): drop table ''name'';
    └── Записи
        ├ ввести данные соответственно типам: insert into ''имя таблицы'' values (''значение записи в соответствии с типом'', ''значение записи в соответствии с типом'');
        ├ вывести записи таблицы: \d имя_таблицы';
        ├ вывести все значения таблицы: select * from ''имя таблицы'';
        ├ вывести определенные значения таблицы: select ''имя столбца'', ''имя столбца'' from ''имя таблицы'' where '''(условие)''';
        ├ изменить все значения записи: update ''имя таблицы'' set ''запись'' = ''значение'';
        ├ изменить определенные значения записи: update ''имя таблицы'' set ''запись'' = ''значение'' where '''(условие)''';
        ├ удаление всех записей: delete from  ''имя таблицы'';
        └ удаление определенной записи: delete from  ''имя таблицы'' where '''(условие)''';

Создание БД

• смотрим, что у нас нет никаких БД
• создаем БД test
• подключаемся к ней.

>\l;
>create database test;
>\c test;

• Создаем таблицу "авторы"

>create table authors (id int, name varchar(30), lastname varchar(30), middlename varchar(30), books text, rating numeric(3,2));

ИЛИ:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(30),
    lastname VARCHAR(30),
    middlename VARCHAR(30),
    books text,
    rating NUMERIC(3,2) -- например, рейтинг 0.00–9.99
);

• смотрим список таблиц и выводим содержимое таблицы

>\dt
>\d authors;

   
> \d authors;                               Таблица "public.authors"
  Столбец   |          Тип          | Правило сортировки | Допустимость NULL | По умолчанию 
------------+-----------------------+--------------------+-------------------+--------------
 id         | integer               |                    |                   | 
 name       | character varying(30) |                    |                   | 
 lastname   | character varying(30) |                    |                   | 
 middlename | character varying(30) |                    |                   | 
 books      | text                  |                    |                   | 
 rating     | numeric(3,2)          |                    |                   | 

Ввод данных

По типам данных вводим данные

> insert into authors values (1, 'Лев', 'Толстой', 'Николаевич', 'Война и мир', '5');
> insert into authors values (2, 'Федор', 'Достоевский', 'Михаилович', 'Преступление и наказание', '4');

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

> INSERT INTO authors (name, lastname, rating) VALUES ('Лев', 'Толстой', '5');

И множественно:

> insert into authors(name, lastname, rating) values('noname','noname',3), ('noname2', 'noname2',2);

Вывести новые значения таблицы:

Вывод

SELECT * FROM authors;

petrdb=# SELECT * FROM authors;
 id |  name   |  lastname   | middlename |          books           | rating 
----+---------+-------------+------------+--------------------------+--------
  1 | Лев     | Толстой     | Николаевич | Война и мир              |   5.00
  2 | Федор   | Достоевский | Михаилович | Преступление и наказание |   4.00
    | Лев     | Толстой     |            |                          |   5.00
    | noname  | noname      |            |                          |   3.00
    | noname2 | noname2     |            |                          |   2.00
(5 строк)

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

Все

Изменить все значения записи:

update authors set rating = 1;

Вывести новые значения таблицы:

> select * from authors;

>select * from authors;
 id |  name   |  lastname   | middlename |          books           | rating 
----+---------+-------------+------------+--------------------------+--------
  1 | Лев     | Толстой     | Николаевич | Война и мир              |   1.00
  2 | Федор   | Достоевский | Михаилович | Преступление и наказание |   1.00
    | Лев     | Толстой     |            |                          |   1.00
    | noname  | noname      |            |                          |   1.00
    | noname2 | noname2     |            |                          |   1.00

Определенные

Изменить определенные значения записи:

update authors set rating = 1 where (условие);

Например:

update authors set rating = 5 where lastname='Толстой';

или

update authors set rating = 4 where rating<5;

или

update authors set rating = 3, middlename='nomiddlename' where lastname='noname';

Вывести новые значения таблицы:

> select * from authors;
 id |  name   |  lastname   |  middlename  |          books           | rating 
----+---------+-------------+--------------+--------------------------+--------
  1 | Лев     | Толстой     | Николаевич   | Война и мир              |   5.00
    | Лев     | Толстой     |              |                          |   5.00
  2 | Федор   | Достоевский | Михаилович   | Преступление и наказание |   4.00
    | noname2 | noname2     |              |                          |   4.00
    | noname  | noname      | nomiddlename |                          |   3.00
(5 строк)

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

Удаление определенной записи:

> delete from authors where name='Лев';

> select * from authors;
 id |  name   |  lastname   |  middlename  |          books           | rating 
----+---------+-------------+--------------+--------------------------+--------
  2 | Федор   | Достоевский | Михаилович   | Преступление и наказание |   4.00
    | noname2 | noname2     |              |                          |   4.00
    | noname  | noname      | nomiddlename |                          |   3.00
(3 строки)

Удаление определенной записи с логическим условием:

> delete from authors where rating=3 AND name='noname';

> select * from authors;
 id |  name   |  lastname   | middlename |          books           | rating 
----+---------+-------------+------------+--------------------------+--------
  2 | Федор   | Достоевский | Михаилович | Преступление и наказание |   4.00
    | noname2 | noname2     |            |                          |   4.00
(2 строки)

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

> delete from authors;

> select * from authors;
 id | name | lastname | middlename | books | rating 
----+------+----------+------------+-------+--------
(0 строк)

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

> select 'имя столбца', 'имя столбца' from 'имя таблицы' where '(условие)';

> select name, rating from authors where rating=5;
 name | rating 
------+--------
 Лев  |   5.00
 Лев  |   5.00
(2 строки)

Условия

• =(условие)
• !=(условие)
• >, <, >=, <=
• (условие)AND(условие), (условие)OR(условие)
• between (значение) AND (значение);
• in (значение)
• not (значение)

Параметры

(пишутся в конце)

• limit n - где вместо n кол-во выводимых строк, вместо 1000 по-умолчанию;
• order by столбец

Рабочий сценарий

Установка

$ su -
# apt-get install postgresql15-server

Настройка

Примечание: Перед запуском службы необходимо создать системные базы данных.

Внимание! Через systemctl postgresql initdb не работает!

# /etc/init.d/postgresql initdb

Примечание: При инициализации сервер запросит у вас создание нового пароля для администратора Postgresql

Добавим его в автозапуск и запустим:

# systemctl enable --now postgresql

Создаем пользователя и назначаем права

# psql -U postgres
> CREATE ROLE petr WITH LOGIN PASSWORD '123';

где petr - пользователь, 123 - пароль

> CREATE DATABASE petrdb;

где petrdb - имя БД

Назначаем права на пользователя:

> GRANT CREATE, CONNECT, TEMPORARY ON DATABASE petrdb TO petr;

Позволяем использовать пользователю схему public:

> GRANT USAGE ON SCHEMA public TO petr;

Позволяем править все таблицы в схеме public:

> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public TO petr;

Позволяем править все последовательности в схеме public:

> GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO petr;

Назначаем дефолтные права таблиц в схеме для нашего пользователя:

> ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO petr;

Назначаем дефолтные права последовательностей в схеме для нашего пользователя:

> ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO petr;

Выходим

>\q
#exit

Настройка БД пользователя

Подключаемся под админом к базе пользователя:

$ psql -U postgres -d petrdb

Назначаем права на чтение и правку пользователю:

petrdb=# GRANT USAGE, CREATE ON SCHEMA public TO petr;
petrdb=# \q

Подключаемся под пользователем:

$ psql -U petr -d petrdb

Создаем БД:

petrdb=> create table authors (id int, name varchar(30), lastname varchar(30), middlename varchar(30), books text, rating numeric(3,2));

Вводим данные в таблицу:

petrdb=> insert into authors values (1, 'Лев', 'Толстой', 'Николаевич', 'Война и мир', '5');

Выводим переменные таблицы:

petrdb=> \d authors;
                                  Таблица "public.authors"
  Столбец   |          Тип          | Правило сортировки | Допустимость NULL | По умолчанию 
------------+-----------------------+--------------------+-------------------+--------------
 id         | integer               |                    |                   | 
 name       | character varying(30) |                    |                   | 
 lastname   | character varying(30) |                    |                   | 
 middlename | character varying(30) |                    |                   | 
 books      | text                  |                    |                   | 
 rating     | numeric(3,2)          |                    |                   | 

Выводим данные таблицы:

petrdb=> select * from authors;

 id | name | lastname | middlename |    books    | rating 
----+------+----------+------------+-------------+--------
  1 | Лев  | Толстой  | Николаевич | Война и мир |   5.00
(1 строка)