###### tags: `netology` `devops`

# Домашнее задание к занятию "6.2. SQL"

## Задача 1

*Используя docker поднимите инстанс PostgreSQL (версию 12) c 2 volume, в который будут складываться данные БД и бэкапы.*

docker run -d --name pg_docker \
-e POSTGRES_PASSWORD=postgres \
-p 5432:5432 \
-v $HOME/docker/volumes/postgres/data:/var/lib/postgresql/data \
-v $HOME/docker/volumes/postgres/bckp:/var/lib/postgresql/bckp \
postgres:12

## Задача 2

- итоговый список БД после выполнения пунктов выше

postgres=# \l
                                    List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges
-----------+----------+----------+------------+------------+------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
 test_db   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres                +
           |          |          |            |            | postgres=CTc/postgres       +
           |          |          |            |            | test_admin_user=CTc/postgres

- описание таблиц (describe)

test_db=# \d+ orders
                                   Table "public.orders"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           | not null |         | plain    |              |
 name   | text    |           |          |         | extended |              |
 price  | integer |           |          |         | plain    |              |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "clients" CONSTRAINT "clients_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
Access method: heap

test_db=# \d+ clients
                                           Table "public.clients"
  Column   |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description
-----------+------------------------+-----------+----------+---------+----------+--------------+-------------
 id        | integer                |           | not null |         | plain    |              |
 last_name | text                   |           |          |         | extended |              |
 country   | character varying(100) |           |          |         | extended |              |
 order_id  | integer                |           |          |         | plain    |              |
Indexes:
    "clients_pkey" PRIMARY KEY, btree (id)
    "clients_country_idx" btree (country)
Foreign-key constraints:
    "clients_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
Access method: heap

- SQL-запрос для выдачи списка пользователей с правами над таблицами test_db

SELECT grantee, table_catalog, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee IN ('test_admin_user', 'test_simple_user')
ORDER BY grantee;

- список пользователей с правами над таблицами test_db

     grantee      | table_catalog | table_name | privilege_type
------------------+---------------+------------+----------------
 test_admin_user  | test_db       | orders     | INSERT
 test_admin_user  | test_db       | orders     | SELECT
 test_admin_user  | test_db       | orders     | UPDATE
 test_admin_user  | test_db       | orders     | DELETE
 test_admin_user  | test_db       | orders     | TRUNCATE
 test_admin_user  | test_db       | orders     | REFERENCES
 test_admin_user  | test_db       | orders     | TRIGGER
 test_admin_user  | test_db       | clients    | INSERT
 test_admin_user  | test_db       | clients    | SELECT
 test_admin_user  | test_db       | clients    | UPDATE
 test_admin_user  | test_db       | clients    | DELETE
 test_admin_user  | test_db       | clients    | TRUNCATE
 test_admin_user  | test_db       | clients    | REFERENCES
 test_admin_user  | test_db       | clients    | TRIGGER
 test_simple_user | test_db       | clients    | INSERT
 test_simple_user | test_db       | orders     | INSERT
 test_simple_user | test_db       | orders     | SELECT
 test_simple_user | test_db       | orders     | UPDATE
 test_simple_user | test_db       | orders     | DELETE
 test_simple_user | test_db       | clients    | SELECT
 test_simple_user | test_db       | clients    | UPDATE
 test_simple_user | test_db       | clients    | DELETE

## Задача 3

Вычислите количество записей для каждой таблицы

test_db=# SELECT COUNT(*) FROM orders;
 count
-------
     5
(1 row)

test_db=# SELECT COUNT(*) FROM clients;
 count
-------
     5
(1 row)

## Задача 4

- *Приведите SQL-запросы для выполнения операций связывания.*

UPDATE clients
SET order_id = (SELECT id FROM orders WHERE name = 'Книга')
WHERE last_name = 'Иванов Иван Иванович';

UPDATE clients
SET order_id = (SELECT id FROM orders WHERE name = 'Монитор')
WHERE last_name = 'Петров Петр Петрович';

 UPDATE clients
 SET order_id = (SELECT id FROM orders WHERE name = 'Гитара')
 WHERE last_name = 'Иоганн Себастьян Бах';

- *Приведите SQL-запрос для выдачи всех пользователей, которые совершили заказ, а также вывод данного запроса.*

SELECT * FROM clients WHERE order_id IS NOT NULL;

 id |      last_name       | country | order_id
----+----------------------+---------+----------
  1 | Иванов Иван Иванович | USA     |        3
  2 | Петров Петр Петрович | Canada  |        4
  3 | Иоганн Себастьян Бах | Japan   |        5
(3 rows)

## Задача 5

Получите полную информацию по выполнению запроса выдачи всех пользователей из задачи 4 (используя директиву EXPLAIN).

Приведите получившийся результат и объясните что значат полученные значения.

EXPLAIN SELECT * FROM clients WHERE order_id IS NOT NULL;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on clients  (cost=0.00..12.80 rows=279 width=258)
   Filter: (order_id IS NOT NULL)
(2 rows)

cost - стоимость операции
row - ожидаемое число строк
width - средняя ширина строки в байтах

## Задача 6

Приведите список операций, который вы применяли для бэкапа данных и восстановления.

# создаем дамп БД в простом текстовом формате
pg_dump -U postgres test_db > /var/lib/postgresql/bckp/dump.sql

# останавливаем старый контейнер и запускаем новый
# заходим в новый контейнер и восстанавливаем данные командой
psql -U postgres test_db -f /var/lib/postgresql/bckp/dump.sql