Блог Александра Смирнова

Устанвка PostgreSQL на ОС Debian

Необходимо развернуть PostgreSQL версии не ниже 12, создать базу данных из четырёх столбцов произвольного названия и наполнить произвольными тестовыми данными, не менее 10-ти значений.

Установка PostgreSQL

Debian включает PostgreSQL в свои репозитории по умолчанию. Следующая команда установит PostgreSQL версии 15:

$ sudo apt install postgresql-15

После установки следует запустить сервис, используя systemctl:

$ sudo systemctl start postgresql
$ sudo systemctl status postgresql

Следующим этапом будет создание базы данных и заполенение её тестовыми данными.

Работа с PostgreSQL

При установке СУБД PostgreSQL в операционной системе автоматически создаётся пользователь postgres, наделённый правами администратора базы данных. Используя команду sudo, можно получить возможность получить доступ к командной оболочке (с помощью ключа -i) от имени данного пользователя (ключ -u):

$ sudo -iu postgres

Следующим шагом является запуск консоли Postgres:

$ psql

Далее для удобства была создана схема devops, в которой и будут дальше создаваться таблицы:

postgres=# CREATE SCHEMA devops;

В созданной схеме создадим отношение “Автор” с атрибутами: идентификатор (первичный ключ), ФИО, страна рождения, дата рождения:

postgres=# CREATE TABLE devops.author(
    id SERIAL PRIMARY KEY,
    fio VARCHAR NOT NULL,
    country VARCHAR NOT NULL,
    dob DATE NOT NULL);

Заполним таблицу данными:

postgres=# INSERT INTO devops.author(fio,country,dob) VALUES
('Александр Сергеевич Пушкин', 'Россия', '1799-06-06'),
('Лев Николаевич Толстой', 'Россия', '1828-09-09');

Проверим результат:

postgres=# SELECT * FROM devops.author;
    id |            fio             | country |    dob     
   ----+----------------------------+---------+------------
     1 | Александр Сергеевич Пушкин | Россия  | 1799-06-06
     2 | Лев Николаевич Толстой     | Россия  | 1828-09-09
   (2 строки)

Создадим отношение “Издательство” с атрибутами: идентификатор (первичный ключ), название, город:

postgres=# CREATE TABLE devops.izd(
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    city VARCHAR NOT NULL);

Заполним таблицу данными:

postgres=# INSERT INTO devops.izd(name,city) VALUES
('Мир','Москва'),
('Питер','Санкт-Петербург'),
('АСТ','Москва');

Проверим результат:

postgres=# SELECT * FROM devops.izd;
id | name  |      city       
----+-------+-----------------
  1 | Мир   | Москва
  2 | Питер | Санкт-Петербург
  3 | АСТ   | Москва
(3 строки)

Создадим отношение “Книга” с атрибутами: идентификатор (первичный ключ), идентификатор автора (внешний ключ в отношение “Автор” ), название, идентификатор издателя (внешний ключ в отношение “Издатель”), дата издания, номер издания (если есть):

postgres=# CREATE TABLE devops.book(
    id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL,
    CONSTRAINT fk_book_author
    FOREIGN KEY(author_id)
    REFERENCES devops.author(id),
    name VARCHAR NOT NULL,
    izd_id SERIAL,
    CONSTRAINT fk_book_izd
    FOREIGN KEY(izd_id)
    REFERENCES devops.izd(id),
    izd_num INTEGER);

Наполним таблицу данными:

postgres=# INSERT INTO
devops.book(author_id, name, izd_id, izd_num)
    VALUES
    (1, 'Евгений Онегин', 1, NULL),
    (2, 'Война и Мир', 3, NULL),
    (2, 'Воскресенье', 2, NULL),
    (1, 'Капитанская дочка',1,NULL),             
    (1, 'Сочинения А.С. Пушкина', 2, 3);

Проверим результат:

postgres=# SELECT * FROM devops.book;
id | author_id |          name          | izd_id | izd_num 
----+-----------+------------------------+--------+---------
1 |         1 | Евгений Онегин         |      1 |        
2 |         2 | Война и Мир            |      3 |        
3 |         2 | Воскресенье            |      2 |        
4 |         1 | Капитанская дочка      |      1 |        
5 |         1 | Сочинения А.С. Пушкина |      2 |       3

Создадим отношение “Наличие книг в магазине” с атрибутами: идентификатор (первичный ключ), идентификатор книги (внешний ключ в отношение “Книга”), цена, наличие в магазине:

postgres= #CREATE TABLE devops.shop(
    id SERIAL PRIMARY KEY,
    book_id INTEGER NOT NULL,
    CONSTRAINT fk_shop_book
    FOREIGN KEY(book_id)
    REFERENCES devops.book(id),
    price INTEGER NOT NULL,
    available BOOLEAN NOT NULL);

Заполним таблицу значениями:

postgres=# INSERT INTO
 devops.shop(book_id, price, available) VALUES
(1, 300, TRUE),
(2, 450, FALSE),
(3, 120, TRUE),
(4,80, TRUE),
(6,650, TRUE);

Проверим результат:

postgres=# SELECT sh.id, b.name,
 price, available FROM devops.shop sh  
JOIN devops.book b on sh.book_id=b.id;
    id |          name          | price | available 
----+------------------------+-------+-----------
    1 | Евгений Онегин         |   300 | t
    2 | Война и Мир            |   450 | f
    3 | Воскресенье            |   120 | t
    4 | Капитанская дочка      |    80 | t
    5 | Сочинения А.С. Пушкина |   650 | t
(5 строк)

Сделаем дамп схемы devops, отображённый в приложении А – резервная копия базы данных:

$ sudo -u postgres pg_dump -d postgres -n devops > dump.sql

Таким образом, была создана база данных с четырьмя таблицами, заполненными тестовыми данными.