Skip to content

install.md

Latest commit

 

History

History
308 lines (223 loc) · 15.5 KB

install.md

File metadata and controls

308 lines (223 loc) · 15.5 KB

Установка

Установка postgres используемо по дефолту, зависит от зависимостей вашей системы.

sudo apt install postgresql postgresql-contrib

Установка postgres конкретной версии

sudo apt install postgresql-13 postgresql-client-13

В директории /usr/lib/postgresql/ находятся все доступные версии postgres на компьютере.

ls /usr/lib/postgresql

# Вывод
13 
9.6

Проверяем версию postgres

psql --version

При установке будет создан пользователь postgres в самой СУБД, а также создан новый пользователь в самой системе Linux с таким же названием postgres. Это делается из-за того что postgres создает идентичную аутентификацию, что значит что каждой роли в самой СУБД сопоставляется такая же роль в Linux.

Это можно увидеть командой:

// Команда выводит всех пользователей в Linux
cat /etc/passwd

// Вывод
...
...
serg:x:1000:1000:serg,,,:/home/serg:/bin/bash
redis:x:122:127::/var/lib/redis:/usr/sbin/nologin
sshd:x:123:65534::/run/sshd:/usr/sbin/nologin
mysql:x:124:128:MySQL Server,,,:/nonexistent:/bin/false
postgres:x:125:129:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

Аутентификация

PG устанавливает идентичную аутентификацию, то есть если роль есть в PG то PG ассоциирует свои роли с ролями в Unix, и пользователь Linux может войти в PG под тем же именем.

При начальной настройке конфиг файла pg_hba.conf который находится по пути /etc/postgres/10/main/pg_hba.conf устанавливает следующие правила:

# Database administrative login by Unix domain socket
# TYPE       DATABASE      USER            METHOD
local        all           postgres        peer

По дефолту можно соединяться как Linux пользователь postgres при помощи следующей команды sudo -u postgres psql то есть входим в pg как postgres пользователь.

USER postgres и METHOD peer - указывают что подключится может только пользователь postgres и только со входом от Linux на том же юзере.

Если поменять на следующее USER postgres и METHOD trust - то тогда метод trust укажет что теперь можно подключаться не только с начальным переходом в соответствующего пользователя в самой Linux системе, но и просто с использованием команды psql то есть следующим образом:

    psql -U postgres

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

    # Database administrative login by Unix domain socket
    # TYPE       DATABASE      USER            METHOD
    local        all           all             trust

Незабываем перезагружать сервис после изменения конфиг файлов.

   sudo service postgresql restart

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

    psql -U user1

Кончено вход произойдет если под этого пользователя уже создана соответствующая БД с таким же названием, как и у самого пользователя.

Но даже в случае если пользователь существует, но для него не создана БД с таким же названием, мы все равно можем войти в pg, но уже для этого потребуется указание конкретной БД к которой будем соединяться.

К примеру так: psql -U user3 demo мы указываем пользователя user3 такой юзер есть, но для него не создано БД, в место этого мы указываем БД demo и соединяемся с ней.

Сессии соединений пользователей к БД, и удаление БД

При работе пользователя с Бд создается сессия пользователя, все сессии можно посмотреть в таблице pg_stat_activity следующим запросом:

SELECT * FROM pg_stat_activity;

Если открыта сессия пользователя на работу с БД, то пока сессия открыта удалить БД не получиться.

Вот как это выглядит, тут можно увидеть id пользователей их имена и pid их процессов, тут колонка datname указывает БД к которой соединение.

datid | datname |  pid  | usesysid | usename  | application_name |
-------+---------+-------+----------+----------+------------------+
16386 | user1   |  9923 |    16384 | user1    | psql             |
16386 | user1   | 21809 |    16384 | user1    | psql             |

То есть можно отсортировать при помощи SQL запроса сколько есть подключений к конкретной БД при помощи следующего запроса:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'user1' AND 
pid <> pg_backend_pid();

После этого можно удалить БД

DROP DATABASE user1;

Схема в БД

Внутри самой БД все делится на схемы, схема это отдельный участок со своим пространством имен, может хранить набор своих собственных таблиц, видов, индексов ... В одной Бд может быть несколько схем, по дефолту каждая БД имеет схему public

В разных схемах могут быть таблицы с одинаковыми именами, можно работать с объектами в разных схемах, указывая с какой конкретной схемой вести работу в конкретный момент времени, это можно сделать при помощи SQL запроса, явно указывая с какой схемой мы будем работать.

У нас есть 2 схемы public и myschema по дефолту работа ведется со схемой public но указывая явно в SQL запросе нужную схему, можно обращаться именно к ней.

SELECT * FROM myschema.table1;

В начале работы с БД устанавливается сессия, которая берет из дефолтной настройки данные для параметра search_path который и устанавливает то с какой именно схемой будет вестись работа по дефолту:

Увидеть это можно при помощи спец команды:

SHOW search_path;

  search_path   
-----------------
"$user", public

Тут через запятую перечислены все те схемы в которых будет вестись поиск объектов по дефолту, public указывает на стандартную схему, в то время как переменная "$user" указывает на таблицу с именем самого пользователя, если конечно такую будут создавать.

\dn - команда показывает все схемы в текущей БД:

   List of schemas
Name   |  Owner   
----------+----------
myschema | user1
public   | postgres

Создать и удалить схему можно так:

CREATE SCHEMA myschema2;
DROP SCHEMA myschema2;

Для того чтобы работать с нужной схемой по дефолту, можно изменить параметр search_path при помощи следующей команды:

SET search_path = myschema;

Теперь если вызвать команду \dt то можно увидеть именно все таблицы из этой схемы, если указывать сразу 2 схемы при задании параметру, SET search_path = public, myschema; то при выводе всех таблиц увидим это:

        List of relations
  Schema  |    Name    | Type  | Owner 
----------+------------+-------+-------
 myschema | table6     | table | user1
 public   | demotable1 | table | user1
 public   | table2     | table | user1
 public   | table3     | table | user1
 public   | table4     | table | user1
 public   | table5     | table | user1
(6 rows)

Видим что тут присутствует только 1 таблица с названием table5 взятая из схемы public, именно по тому что public указана первой и одноименная таблица table5 из другой схемы myschema просто сюда не попадает.

Но если изменить порядок SET search_path = myschema, public; то можно увидеть что теперь тут есть именно таблица table5 из схемы myschema:

          List of relations
  Schema  |    Name    | Type  | Owner 
----------+------------+-------+-------
 myschema | table5     | table | user1
 myschema | table6     | table | user1
 public   | demotable1 | table | user1
 public   | table2     | table | user1
 public   | table3     | table | user1
 public   | table4     | table | user1
(6 rows)

Разница между СУБД MySQL и PostgreSQL

ХРАНЕНИЕ ДАННЫХ

Обе СУБД хранят данные на диске в спец отсортированных файлах, но различается только сам способ хранения.

MySQL - это реляционная база данных, в нутри которой есть реализауции нескольких движков MyISAM, InnoDB, MEMORY, Berkeley DB, каждый движок работает по разному с таблицами на чтение запись и прочее ...

Postgresql - это объектно реляционная база данных, и работате только с одними единственным движком storage engine тоетсь каждая таблица представлена в виде обьекта, также предоставляются специальные обьектные функции для изменения этих таблиц-обьектов.

СТАНДАРТ SQL

Стандарт SQL разработан в 1986 году.

MySQL - стремится к простоте, и не реализует все возможности стансдартов SQL.

Postgresql - разрабатывается энтузиастами, и стремится к максималььному соответствию всем современным стандартам SQL какими бы сложными они не были.

ВОЗМОЖНОСТИ ОБРАБОТКИ

MySQL - сервер MySQL получает запрос, выполняет его и отдает весь ответ клинту.

Postgresql - получает запрос, выполняет его и сохраняет на сервере самого Postgresql, клиент получает указатель, этот указатель можно сохранить межжду сеансами обращения к Postgresql, тоесть происходит кеширование полученых данных.

Postgresql - поддерживает наследование таблиц и рекурсивные запросы, поддерживается построение индексов сразу для нескольких столбцов таблицы, также можно создавать индексы сразу для нескольких столбцов таблицы, и при этом индексы будут разных типов, поддерживаются индексы типа:

  1. hash
  2. b-tree
  3. GiST
  4. SP-GiST для работы с городами
  5. GIN для поиска по тексту
  6. BRIN
  7. Bloom

ПРОИЗВОДИТЕЛЬНОСТЬ

MySQL - в большинстве случае используется таблицы с движком InnoDB, эта таблица представляет из себя B-дерево с индексами, что позволяет по этим индексам быстро получать данные с диска, но ! MySQL будет быстрее Postgresql только при использовании первичного ключа

Postgresql - Вся заголовочная информация таблиц Postgresql находится в оперативной памяти, а не на диске как у MySQL, записи таблицы сортируются по индексу и по этому их можно быстро извлекать, в целом Postgresql работает быстрее.

ТИПЫ ДАННЫХ

MySQL - поддерживает стандартные типы данных.

Postgresql - типов данных в Postgresql больше и они более разнообразны, также можно создавать свои собственные типы данных, тоетсь это расширяемая СУБД.

РАЗРАБОТКА

Оба проекта имеют открытый исходный код,

MySQL - разрабатывается компанией Oracle, от развития этой СУБД сделано множество форков и создано много разных реализаций, одна из которых MariaDB от оригиналььного разработчика.

Postgresql - разработка началась в университете Беркли, сейчас развивается энтузиастами.