PostgreSQL
PostgreSQL — это объектно-реляционная система управления базами данных (ORDBMS), наиболее развитая из открытых СУБД в мире. Имеет открытый исходный код и является альтернативой коммерческим базам данных.
«IT-специалист с нуля» наш лучший курс для старта в IT
Ранние версии системы были основаны на старой программе POSTGRES University, созданной университетом Беркли: так появилось название PostgreSQL. И сейчас СУБД иногда называют «Постгрес». Существуют сокращения PSQL и PgSQL — они тоже обозначают PostgreSQL.
СУБД позволяет гибко управлять базами данных (БД). С ее помощью можно создавать, модифицировать или удалять записи, отправлять транзакцию — набор из нескольких последовательных запросов на особом языке запросов SQL.
Кто пользуется PostgreSQL
- Бэкенд-разработчики, которым приходится взаимодействовать с базами данных при работе с «серверной» частью сайта.
- Администраторы и разработчики баз данных — специалисты, основная задача которых заключается в обслуживании и поддержке работоспособности базы.
- DevOps-инженеры, обслуживающие рабочую инфраструктуру проекта.
Профессия / 8 месяцев
IT-специалист с нуля
Попробуйте 9 профессий за 2 месяца и выберите подходящую вам
Для чего нужна PostgreSQL
- Гибкий доступ к базам данных, их организация и хранение.
- Управление записями в базах данных: создание, редактирование и удаление, обновление версий и так далее.
- Просмотр нужной информации из базы по запросу, например для ее отправки на сайт или в интерфейс приложения.
- Отправка транзакций, последовательных запросов, собранных в подобие скрипта.
- Настройка и контроль доступа к той или иной информации, группировка пользователей по уровню прав.
- Контроль версий и организация одновременного доступа к базе из разных источников так, чтобы предотвратить сбои.
- Защита информации от возможных утечек и потерь.
- Контроль состояния базы в целом.
Преимущества PostgreSQL
Иногда СУБД называют бесплатным аналогом Oracle Database. Обе системы адаптированы под большие проекты и высокую нагрузку. Но есть разница: они по-разному хранят данные, предоставляют разные инструменты и различаются возможностями. Важная особенность PostgreSQL в том, что эта система — feature-rich: так называют проекты с широким функционалом.
Объектно-реляционная модель. Традиционно популярные СУБД — реляционные. Это значит, что данные, которые в них хранятся, представляются в виде записей, связанных друг с другом отношениями, — relations. Получаются связанные списки, которые могут иметь между собой те или иные отношения, — так и образуется таблица.
Существует еще одна популярная модель — объектная. Данные представляются в виде объектов, их атрибутов, методов и классов. Объектная модель поддерживает возможности, о которых мы подробно рассказывали в статье про ООП, например наследование.
PostgreSQL — объектно-реляционная СУБД. Это значит, что она поддерживает и объектный, и реляционный подход.
Станьте аналитиком данных и получите востребованную специальность
Поддержка множества типов данных. Еще одна особенность PostgreSQL — поддержка большого количества типов записи информации. Это не только стандартные целочисленные значения, числа с плавающей точкой, строки и булевы значения («да/нет»), но и денежный, геометрический, перечисляемый, бинарный и другие типы. PostgreSQL «из коробки» поддерживает битовые строки и сетевые адреса, массивы данных, в том числе многомерные, композитные типы и другие сложные структуры. В ней есть поддержка XML, JSON и NoSQL-баз.
При необходимости к СУБД можно подключить поддержку типов данных, которые нужны в конкретном проекте. В PostgreSQL есть несколько внутренних форматов, которые используются только в ней.
Работа с большими объемами. В большинстве СУБД, рассчитанных на средние и небольшие проекты, есть ограничения по объему базы и количеству записей в ней. В PostgreSQL ограничений нет.
Ограничения касаются только конкретных записей. Одна таблица может занимать не больше 32 Тб, а одна запись — 1,6 Тб. В одном поле записи может быть не больше 1 Гб данных, а максимальное количество полей зависит от типа и составляет от 250 до 1600 штук. Максимальных значений хватает, чтобы хранить в БД любые данные.
Поддержка сложных запросов. PostgreSQL работает со сложными, составными запросами. Система справляется с задачами разбора и выполнения трудоемких операций, которые подразумевают и чтение, и запись, и валидацию одновременно. Она медленнее аналогов, если речь заходит только о чтении, но в других аспектах превосходит конкурентов.
Написание функций на нескольких языках. В PostgreSQL можно писать собственные функции — пользовательские блоки кода, которые выполняют те или иные действия. Эта возможность есть практически в любых СУБД, но PostgreSQL поддерживает больше языков, чем аналоги. Кроме стандартного SQL, в PostgreSQL можно писать на C и C++, Java, Python, PHP, Lua и Ruby. Он поддерживает V8 — один из движков JavaScript, поэтому JS тоже можно использовать совместно с PgSQL. Реализована поддержка Delphi, Lisp и прочих редких языков. При необходимости можно расширить систему под другие ЯП.
Модификация SQL, которая используется в PostgreSQL, называется PL/pgSQL. Это процедурное расширение, которое поддерживает сложные вычисления и дополняет «классический» SQL новыми возможностями.
Читайте также SQL, R или Python: какой язык учить аналитику данных?
Одновременная модификация базы. Важная особенность PostgreSQL — возможность одновременного доступа к базе с нескольких устройств. В СУБД реализована клиент-серверная архитектура, когда база данных хранится на сервере, а доступ к ней осуществляется с клиентских компьютеров. Так, например, реализуются разнообразные сайты. Одна из возможных сложностей — ситуация, когда несколько человек одновременно модифицируют базу и нужно избежать конфликтов.
В PostgreSQL для этого используется технология MVCC — Multiversion Concurrency Control, многоверсионное управление параллельным доступом. Каждый пользователь получает снапшот — «снимок» базы, в который вносятся изменения. Только после фиксации транзакции они поступают в исходную базу данных. Пока человек вносит изменения, они не видны другим пользователям. Конфликтов не возникает, как и необходимости блокировать чтение или запись.
Соответствие ACID. ACID — это набор принципов для обеспечения целостности данных. Аббревиатура расшифровывается как Atomicity, Consistency, Isolation, Durability — атомарность, согласованность, изолированность, прочность. Если база данных соответствует этим принципам, она ведет себя максимально предсказуемо и надежно. В ней низок риск конфликта или непредвиденного поведения системы.
PostgreSQL соблюдает требования ACID благодаря технологии MVCC. Это делает систему надежной и безопасной в использовании, а данные — защищенными от возможных сбоев, ошибок и потерь.
Возможность расширения. Разработчик может написать для СУБД собственные типы и их преобразования, операции и функции, ограничения и индексы, собственный процедурный язык для запросов. PostgreSQL можно модифицировать практически под любую нестандартную задачу.
Высокая мощность и широкая функциональность. PostgreSQL — возможно, единственная бесплатная СУБД с открытым исходным кодом, которая рассчитана на работу с объемными и сложными проектами. Она мощная, производительная, способна эффективно работать с большими массивами данных. Есть примеры реального использования СУБД для баз данных в несколько петабайт с сотнями тысяч запросов в секунду. На главной странице официального сайта PostgreSQL называют «самой продвинутой бесплатной СУБД». Система действительно имеет высокую функциональность и не уступает платным продуктам.
Открытость. PostgreSQL — ПО с открытым исходным кодом, которое распространяется по свободной лицензии. Это означает, что любой разработчик может посмотреть, как написана система, или предложить для нее свои правки. СУБД разрабатывается сообществом энтузиастов и в определенной степени никому не принадлежит, а значит, ее можно свободно и без ограничений использовать в своих проектах.
На базе PostgreSQL существуют коммерческие продукты с платным доступом — ими обычно пользуются крупные компании, которым нужна дополнительная функциональность. Это, например, связь с Oracle Database или продвинутый веб-интерфейс для администрирования БД.
Минимальное количество багов. PostgreSQL — проект, который известен высоким качеством отладки. Каждая версия системы появляется в доступе только после полной проверки, поэтому СУБД очень стабильна. Частая проблема бесплатных проектов — новые версии с большим количеством багов, но в случае с PostgreSQL такой проблемы нет.
Согласно независимым автоматизированным исследованиям, в исходном коде СУБД есть одна ошибка на 39 000 строк кода. Это в пять раз меньше, чем в MySQL, и в пятьдесят раз меньше, чем в ядре операционной системы Linux.
Кроссплатформенность. Чаще всего PostgreSQL используют на серверах с операционными системами семейства Linux, но СУБД поддерживает и другие ОС. Ее можно установить в системы на базе Windows, BSD, macOS и Solaris. Кроме того, у PostgreSQL есть автономный веб-сервер PostgREST, с которым можно работать с помощью REST API. СУБД можно развернуть и в облаке.
PostgreSQL не имеет однозначных недостатков. Она подходит для проектов, где нужна мощная база данных с высокой функциональностью. Но для большинства приложений и сайтов требуется менее мощная и более быстрая, легковесная, простая в освоении СУБД. Обычно в таких случаях используется MySQL.
Недостатки
Несмотря на все достоинства этой выдающейся СУБД, у неё также есть некоторые недостатки:
Сложности при настройке. У PostgreSQL очень обширный набор возможностей. Очевидно, что такое разнообразие функций влечёт за собой множество настроек, что может вызвать затруднения у новичков. Корректная настройка базы данных требует глубокого понимания архитектуры и параметров.
Повышенное потребление ресурсов. В сравнении с некоторыми другими СУБД, PostgreSQL может потреблять больше ресурсов (включая оперативную память и процессорное время). Это особенно заметно при работе с большими объёмами данных и выполнении сложных запросов.
Отсутствие некоторых функций. По сравнению с определенными коммерческими аналогами PostgreSQL может немного уступать в функциональности.
Стоит отметить, что перечисленные недостатки в основном применимы к конкретным сценариям использования. В общем плане PostgreSQL остаётся одной из самых мощных и популярных открытых СУБД.
Как скачать и начать работу с PostgreSQL
Скачать программное обеспечение можно с официального сайта: на странице загрузки представлены готовые к установке пакеты для разных операционных систем. Там же есть ссылка на репозиторий, где в том числе можно посмотреть, как менялись версии проекта, и ознакомиться с новыми правками.
После скачивания потребуется место для размещения серверной части СУБД. Обычно для этого арендуются мощности веб-серверов хостингов.
Более сложный процесс — настройка и развертывание базы данных, управление ей. Для этого потребуются знания бэкенд-разработчика или администратора БД. В случае с PostgreSQL нужно заранее ознакомиться с документацией или мануалами. Также получить все перечисленные знания можно на курсах SkillFactory.
Создание таблицы
Для создания таблицы в PostgreSQL используется язык SQL (Structured Query Language). Вот пример запроса, который создаст простую таблицу с несколькими столбцами:
CREATE TABLE имя_таблицы ( столбец1_имя тип_данных_столбца1, столбец2_имя тип_данных_столбца2, . столбецN_имя тип_данных_столбцаN );
Пояснение к коду:
- имя_таблицы — это имя, которое вы хотите присвоить таблице;
- столбец1_имя , столбец2_имя , и так далее — это имена столбцов;
- тип_данных_столбца1 , тип_данных_столбца2 , и так далее — это типы данных для соответствующих столбцов.
Пример создания таблицы с именем users, содержащей столбцы id, username и email:
CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50), email VARCHAR(100) );
В этом примере используется тип данных SERIAL для автоматически генерируемого идентификатора, VARCHAR для строковых значений переменной длины.
После выполнения этого SQL-запроса, вам будет доступна новая таблица users с указанными столбцами. Это всего лишь базовый пример, и PostgreSQL поддерживает множество других типов данных и параметров настройки таблиц.
Data Scientist
Дата-сайентисты решают поистине амбициозные задачи. Научитесь создавать искусственный интеллект, обучать нейронные сети, менять мир и при этом хорошо зарабатывать. Программа рассчитана на новичков и плавно введет вас в Data Science.
Статьи по теме:
Postgresql как работать
Создав базу данных, вы можете обратиться к ней:
Запустив терминальную программу Postgres Pro под названием psql , в которой можно интерактивно вводить, редактировать и выполнять команды SQL .
Используя существующие графические инструменты, например, pgAdmin или офисный пакет с поддержкой ODBC или JDBC , позволяющий создавать и управлять базой данных. Эти возможности здесь не рассматриваются.
Чтобы работать с примерами этого введения, начните с psql . Подключиться с его помощью к базе данных mydb можно, введя команду:
$psql mydb
Если имя базы данных не указать, она будет выбрана по имени пользователя. Об этом уже рассказывалось в предыдущем разделе, посвящённом команде createdb .
В psql вы увидите следующее сообщение:
psql (10.23.1) Type "help" for help. mydb=>
Последняя строка может выглядеть и так:
mydb=#
Что показывает, что вы являетесь суперпользователем, и так скорее всего будет, если вы устанавливали экземпляр Postgres Pro сами. В этом случае на вас не будут распространяться никакие ограничения доступа, но для целей данного введения это не важно.
Если вы столкнулись с проблемами при запуске psql , вернитесь к предыдущему разделу. Команды createdb и psql подключаются к серверу одинаково, так что если первая работает, должна работать и вторая.
Последняя строка в выводе psql — это приглашение, которое показывает, что psql ждёт ваших команд и вы можете вводить SQL -запросы в рабочей среде psql . Попробуйте эти команды:
mydb=>SELECT pgpro_version();version ------------------------------------------------------------------------------------------ PostgresPro 10.23.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 row)mydb=>SELECT current_date;date ------------ 2016-01-07 (1 row)mydb=>SELECT 2 + 2;?column? ---------- 4 (1 row)
В программе psql есть множество внутренних команд, которые не являются SQL-операторами. Они начинаются с обратной косой черты, « \ » . Например, вы можете получить справку по различным SQL -командам Postgres Pro , введя:
mydb=>\h
Чтобы выйти из psql , введите:
mydb=>\q
и psql завершит свою работу, а вы вернётесь в командную оболочку операционной системы. (Чтобы узнать о внутренних командах, введите \? в приглашении командной строки psql .) Все возможности psql документированы в справке psql . В этом руководстве мы не будем использовать эти возможности явно, но вы можете изучить их и применять при удобном случае.
| Пред. | Наверх | След. |
| 1.3. Создание базы данных | Начало | Глава 2. Язык SQL |
Работа с базой данных PostgreSQL
PostgreSQL – опенсорсная реляционная СУБД. Отличается гибкостью и надежностью, поддерживает большое количество полезных возможностей. Часто используется в проектах, где требуется работа со сложными структурами данных, с которыми не справляются простые СУБД.
В этой статье мы разберемся, как работать с PostgreSQL. В качестве примера я буду использовать Ubuntu 18.04.
Установка PostgreSQL
PostgreSQL есть в репозитории Ubuntu, поэтому установка выполняется одной командой. Но сначала нужно проверить обновления самой системы:
sudo apt-get update
Для выполнения команды нужны права суперпользователя. Так что придется вспомнить пароль.
После установки апдейтов инсталлируем PostgreSQL:
sudo apt-get install postgresql postgresql-contrib
PostgreSQL установится вместе с пакетом contrib, в котором содержится дополнительная функциональность, а также утилиты для работы СУБД.
Чтобы убедиться, что все работает, проверим версию:
postgres --version
При установке автоматически создается роль и пользователь postgres.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Настройка PostgreSQL
Работать с PostgreSQL мы будем через терминал с помощью встроенной утилиты psql. Запускаем ее следующей командой:
psql postgres
Можно установить сторонние инструменты для администрирования PostgreSQL, но в этом мало смысла — psql справляется со всеми основными задачами.
Чтобы получить поддержку, вводим в терминале команду:
\help
Если нужна справка по конкретной команде, пишем:
\help [имя команды]
Выйти из psql можно командой \q.
Управление пользователями
В PostgreSQL используется концепция ролей. Одну роль можно рассматривать как отдельного пользователя или как группу пользователей. Роли могут владеть объектами БД и выдавать разрешения другим ролям.
По умолчанию была создана роль postgres. Давайте создадим еще одну роль. Для этого и з консоли системы выполняем команду:
createuser -P --interactive
Система запросит имя для новой роли, пароль, а также позволит настроить привилегии — например, нужно ли давать права суперпользователя или разрешать создавать другие роли и базы данных.
Если вы уже зашли в psql, то создать новую роль можно командой:
CREATE ROLE имя_новой_роли WITH LOGIN CREATEDB CREATEROLE; // В конце обязательно ставим ;
Затем задаем пароль:
\password имя_роли
Вывести список всех ролей можно командой /du. Кроме имен отобразятся привилегии каждого роли.
Чтобы закрыть список ролей, выполняем команду q.
Для удаления пользователя выполняем команду:
DROP ROLE имя_роли;
Это можно также сделать из консоли системы с помощью команды:
drop user имя_роли
Чтоб сменить пароль пользователя, подключаемся к psql с правами суперпользователя. Затем выполняем следующую команду:
ALTER USER имя_роли WITH PASSWORD 'новый_пароль';
Эта операция сохраняется в файле .psql_history вместе с паролем, который не будет зашифрован. В качестве дополнительной меры безопасности эту запись рекомендуется удалить. Файл обычно находится в директории /var/lib/postgresql.
Работа с базами данных в PostgreSQL
Создать базу данных из консоли можно следующей командой:
createdb имя_БД
Если вы используете терминальный клиент psql, то команда будет немного отличаться:
CREATE DATABASE имя_БД;
Чтобы посмотреть список всех БД, выполняем команду \l.
Для удаления базы данных используется та же команда, что и для удаления роли — drop. В терминале системы синтаксис будет таким:
drop database имя_БД
В клиенте psql синтаксис похожий:
DROP DATABASE имя_БД;
Подключение к базе данных
По умолчанию psql подключается от имени текущего пользователя Linux к БД с таким же названием. Если эти данные совпадают, достаточно выполнить запуск самого терминального клиента:
psql
Чтобы вывести информацию о текущем соединении, выполняем команду:
\conninfo
Если имя базы данных отличается от имени пользователя, нужно указать его явно:
psql -d имя_БД
Если имя роли не совпадает с именем пользователя в Linux, прописываем дополнительные параметры.
Имя роли и название БД совпадают:
psql -U имя_роли -h localhost -W
Название базы данных отличается от имени роли:
psql -U имя_роли -d имя_базы -h localhost -W // Разница в том, что явно указано название БД
Чтобы переключиться на другую базу данных внутри psql используем команду:
\c имя_БД
Создание резервной копии и восстановление из бэкапа
Для создания резервной копии базы данных используется сложная команда:
pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_БД
Чтобы было проще разобраться, рассмотрим каждый параметр:
- хост – сервер, на котором располагается БД. Например, можно указать localhost, домен, IP-адрес.
- имя_роли – имя пользователя PostgreSQL, под которым мы работаем с базой данных.
- формат_дампа – формат, в котором дамп сохранится на сервере. Доступны следующие форматы: c (custom) – архив .tar.gz, t (tar) – архив .tar, p (plain) – текст без сжатия, обычно .sql.
- путь_к_дампу – путь, по которому будет сохранена резервная копия.
- имя_БД – название БД, для которой будет создана резервная копия.
Выглядит это примерно так:
pg_dump -h localhost -U mybase -F c -f /home/user/backups/dump.tar.gz mybase
Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).
Восстановление из резервной копии выполняется аналогичным образом:
pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу
Параметры похожие, отличия минимальные. Важно знать хост, помнить формат и путь к бэкапу.
Мы разобрались с основными действиями и настройками PostgreSQL. На этом все!
Руководство по PostgreSQL
PostgreSQL — это реляционная СУБД с открытым кодом и свободным распространением. База данных Postgre базируется на стандартном языке запросов SQL, а точнее, на его расширении — процедурном языке PL/pgSQL. PostgreSQL работает в системах Unix (FreeBSD и Linux: дистрибутивах Ubuntu, Debian, CentOS) и Windows.
В статье мы рассмотрим, как установить PostgreSQL на Ubuntu и выполнить настройку, а также как запустить PostgreSQL-сервер и использовать его с фреймворком Django.
PostgreSQL: установка и настройка на Ubuntu
Для установки PostgreSQL (install Ubuntu):
Обновите список пакетов:
sudo apt update