Как развернуть бд postgresql

Установка и базовая настройка PostgreSQL в Windows 10

30.08.2022
itpro
Windows 10, Windows Server 2016
комментариев 11

PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.

Установка PostgreSQL 11 в Windows 10

Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.

В процессе установки установите галочки на пунктах:

• PostgreSQL Server – сам сервер СУБД
• PgAdmin 4 – визуальный редактор SQL
• Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
• Command Line Tools – инструменты командной строки

Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).

По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.

Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.

Доступ к PostgreSQL по сети, правила файерволла

Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.

Запустите командную строку от имени администратора. Введите команду:

netsh advfirewall firewall add rule name=»Postgre Port» dir=in action=allow protocol=TCP localport=5432

• Где rule name – имя правила
• Localport – разрешенный порт

Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:

New-NetFirewallRule -Name ‘POSTGRESQL-In-TCP’ -DisplayName ‘PostgreSQL (TCP-In)’ -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.

Измените значение в пункте port = 5432 . Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:

Restart-Service -Name postgresql-x64-11

Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.

Утилиты управления PostgreSQL через командную строку

Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.

Запустите командную строку.

Основные команды PostgreSQL:

• Проверка установленной версии СУБД: psql –V
• Для создания новой базы данных воспользуйтесь утилитой createdb: createdb -U postgres testdb (где postgres суперпользователь, testdb новая база данных)Введите пароль суперпользователя.
• Проверить список активных баз: Psql -U postgres –l (пароль)
• С помощью инструмента createuser cоздадим нового пользователя: createuser –U postgres operator (где operator -имя нового пользователя)
• Предоставим пользователю привилегии суперпользователя (на практике этого делать не надо). Запустите интерактивную командную оболочку управления PostgreSQL (shell): psql –U postgres . С помощью SQL команды ALTER ROLE предоставим нужные права нашему пользователю: ALTER ROLE operator SUPERUSER CREATEROLE CREATEDB; . Мы предоставили пользователю права суперпользователя, права на создание ролей и баз данных.
• Для выводы списка пользователей и ролей в СУБД выполните команду: \du

PgAdmin: Визуальный редактор для PostgresSQL

Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.

По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.

Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.

Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.

Query Tool: использование SQL запросов в PostgreSQL

Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.

• Выберите базу данных, в панели Tools откройте Query Tool
• Создадим таблицу сотрудников:

CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);

Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.

После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.

Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.

Здесь вы можете заполнить данные в таблице.

После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;

Настройка и начало работы с PostgreSQL

В прошлый раз я познакомил тебя с основами реляционных баз данных и SQL. Теперь пришло время настроить рабочую среду. Ты будешь использовать PostgreSQL в качестве своей СУБД, поэтому для начала ее нужно скачать и установить.

Загрузка и установка PostgreSQL

PostgreSQL поддерживает все основные операционные системы. Процесс установки прост, поэтому я постараюсь рассказать о нем как можно быстрее.

Для Windows и Mac ты можешь загрузить установщик с веб-сайта EDB.

EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер пакетов твоего дистрибутива.

Установщики включают в себя разные компоненты.

Вот самые важные из них:

• Сервер PostgreSQL (очевидно)
• pgAdmin, графический инструмент для управления базами данных
• Менеджер пакетов для загрузки и установки дополнительных инструментов и драйверов

Windows

Скачав установщик, запусти его как любой другой исполняемый файл. Процесс довольно прямолинеен, но некоторые вещи все же заслуживают внимания.

Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты. Если у тебя нет веской причины что-то менять — оставляй все как есть.

По умолчанию PostgreSQL создает суперпользователя с именем postgres (воспринимай его как учетную запись администратора сервера базы данных).

Во время установки тебе нужно будет указать пароль для суперпользователя (root).

Позже ты сможешь создать других пользователей и назначать им отдельные доступы и роли. Мы вернемся к этому позже, а сейчас тебе понадобится учетная запись суперпользователя, чтобы начать использовать СУБД.

Чтобы запустить сервер разработки на твоем компьютере или localhost , необходимо назначить ему порт.

Порт по умолчанию — 5432. Если ты устанавливаешь PostgreSQL впервые, то он скорее всего свободен. Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, ты можешь указать другое значение, например 5433.

После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.

Шаг за шагом ты выберешь сервер, какую базу данных использовать, порт, имя пользователя и пароль.

Используй данные, которые ты вводил на предыдущих шагах.

Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.

Ниже список вариантов установки для других операционных систем.

macOS

Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.

Кроме того, можно использовать Postgres.app , простое приложение для macOS.

После запуска у тебя появится сервер PostgreSQL, готовый к использованию. Завершить работу сервера можно просто закрыв приложение.

Кроме того, ты также можете использовать Homebrew , менеджер пакетов для macOS.

GNU/Linux

Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши из выбранного графического диспетчера пакетов.

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

Ubuntu

Fedora

openSUSE

Запуск оболочки PostgreSQL

После установки PostgreSQL, нужно запустить оболочку(shell), с помощью которой ты получишь возможность управлять базой данных.

Открой терминал и введи:

psql — это оболочка Postgres, аргумент -U используется для указания пользователя.

Поскольку ты еще не создавал других пользователей, ты войдешь в систему как суперпользователь postgres .

После этого нужно будет ввести пароль суперпользователя, который ты выбрал во время установки.

Как только пароль установлен, база данных PostgreSQL готова к работе!

Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.

Понимание модели клиент-сервер

Я уже упоминал PostgreSQL Server как важный компонент базы данных. Но что такое сервер в этом контексте и зачем он нам нужен?

Для начала тебе необходимо понимать модель клиент-сервер.

Почти все СУБД (PostgreSQL, MySQL и другие) следуют клиент-серверной модели. В ней база данных находится на сервере, и клиент отправляет запросы на сервер, который их обрабатывает.

Под клиентом здесь подразумевается бекэнд нашего приложения, а запросы в — это SQL операции, такие как SELECT, INSERT, UPDATE и DELETE.

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

С точки зрения клиента удаленный и локальный сервер идентичны. После разработки и тестирования ты можешь заставить свой продукт взаимодействовать с удаленным сервером вместо локального, просто изменив пару параметров.

Некоторые базы данных не используют эту модель, например SQLite, которая хранит все в простом файле на диске. Это хорошо работает для небольших приложений, но для большинства реальных приложений тебе понадобится архитектура клиент-сервер.

Мета-команды PostgreSQL

Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд. Это не SQL запросы, а команды специфичные для PostgreSQL.

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

Всем мета-командам предшествует обратная косая черта \ , за которой следует фактическая команда.

Список всех баз данных

Чтобы получить список всех баз данных на сервере, ты можешь использовать команду \ l .

Ввод этой мета-команды в оболочке Postgres выведет:

Это список всех имеющихся баз данных и служебная информация, такая как владелец базы данных, кодировка и права доступа.

На данный момент мы пока ничего не создали, а базы данных которые ты видишь на экране — создаются по умолчанию при установке Postgres.

• postgres — это просто пустая база данных.
• «template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.

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

Подключаемся к базе данных PostgreSQL

Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы). Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.

Когда ты находишься внутри оболочки (shell), то можешь использовать команду \c (или \connect ), за которой следует имя базы данных. Если бы у тебя была другая база данных под названием hello_world , то подключиться к ней можно было бы так:

Полностью в терминале у тебя получится что-то такое:

Обрати внимание, что приглашение оболочки изменилось с postgres на hello_world . Это значит, что теперь ты подключен к базе данных hello_world , а не postgres .

Получить список всех таблиц в базе данных

Как и в случае со списком существующих баз данных, ты можешь получить список таблиц внутри конкретной базы данных с помощью команды \dt .

Перед выполнением этой команды вам необходимо войти в базу данных.

Предположим, ты уже находишься внутри базы hello_world , и в ней есть таблица с именем my_table . Набрав \dt , ты получишь следующее:

Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных руководствах) и владельца.

Владелец (owner) — это пользователь, который создал таблицу.

Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.

Список пользователей и ролей

Как ты уже знаешь, при установке Postgres создается суперпользователь с именем postgres . Список всех пользователей базы данных можно вывести на экран используя команду \dg .

Обрати внимание, что первый столбец называется — роль (role name). И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей.

В PostgreSQL пользователи и роли практически одинаковы.

У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание других новых ролей.

Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.

Здесь мы видим только одну роль, суперпользователя по умолчанию.

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

Если у одной из ролей есть доступ только на чтение данных, то с помощью этой роли будет невозможно удалить таблицу или поле.

Твой первый SQL оператор

Наконец, мы все настроили и готовы к работе и знаем основные мета-команды, специфичные для PostgreSQL.

Теперь приступим к изучению языка запросов SQL.

Я покажу тебе несколько базовых примеров, чтобы разобраться в структурированном языке запросов и получить представление о SQL. А более подробно мы рассмотрим операции CRUD это в следующей статье.

Создание новой базы данных

Первое, что тебе нужно узнать при изучении баз данных, — это как создать базу данных. Создать базу можно сделать с помощью команды CREATE DATABASE , за которой следует имя базы данных:

Команды и ключевые слова SQL обычно пишутся в верхнем регистре.

На самом деле это не является обязательным требованием, и обычно они нечувствительны к регистру.

То есть ты мог бы написать

И все сработало бы нормально.

Но при написании операторов SQL обычно предпочтительнее прописные буквы. Это хорошая практика, потому что она может помочь тебе визуально отличить ключевые слова SQL от других частей оператора, таких как имена таблиц и столбцов.

Заметь, что все стандартные команды в PostgreSQL должны заканчиваться точкой с запятой ; . Это часть стандарта.

Для мета-команд PostgreSQL точка с запятой не нужна.

Создание таблиц

После того как ты создал новую базу данных, можно приступать к созданию таблиц.

Но, для начала, подключимся к новой базе данных с помощью команды \c , за которой следует имя базы данных:

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

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

Это создаст таблицу под названием products , которая содержит 3 столбца:

• id типа INT (целое число)
• name типа TEXT (строка)
• quantity также типа INT

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

Вставка данных в таблицы PostgreSQL

Чтобы добавить данные в таблицу, используют команду INSERT INTO следующим образом:

Посмотрим на команду INSERT INTO подробнее:

• Команда INSERT INTO означает, что вы собираетесь вставить новые данные
• products — это имя таблицы в базе данных, в которую ты хочешь вставить данные
• (id, name, quantity) — это список столбцов в нашей таблице, разделенных запятыми. Тебе не нужно указывать все столбцы (иначе какой в смысл?). В некоторых случаях вы хотите выборочно вставлять данные в некоторые столбцы. Остальные столбцы будут автоматически заполнены значениями по умолчанию.
• VALUES (1, ‘first product’, 20) — это фактические данные, которые будут вставлены в таблицу. «1» — это id , «first product» — это name , «20» — это quantity .

Выборка данных из SQL таблицы

Теперь, когда ты добавил в таблицу первую запись, ты можешь использовать SQL для получения содержимого таблицы.

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

Мы используем команду SELECT . За ней следует список столбцов, которые мы хотим получить.

Затем мы используем команду FROM , чтобы указать, из какой таблицы брать данные. На этот раз это таблица products .

Для ситуаций когда ты хочешь выбрать все столбцы которые есть в таблице, ты можешь поставить звездочку вместо списка полей.

Звездочка означает: выбрать все столбцы. Результат останется прежним.

Ты должен обратить внимание на то, как команда SELECT выбирает столбцы и строки. Столбцы указываются в виде списка и разделяются запятыми. Затем команда переходит к выбору запрошенных строк.

Если условия не указаны (как в этом случае), будут выбраны все строки в таблице.

Позже мы увидим, как использовать условия с командой WHERE для создания эффективных запросов.

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

Представь, что ты запустил свое потрясающее приложение для магазина и получили первый заказ на один из продуктов.

Первое, что нужно сделать — это обновить доступное количество в вашем инвентаре, чтобы в дальнейшем у вас не возникли проблемы с отсутствием товара на складе.

Для обновления данных ты можешь использовать команду UPDATE :

Давайте разберемся с тем как работает UPDATE .

Начинаем мы с ключевого слова UPDATE , за которым следует имя таблицы.

Затем мы используем SET , чтобы установить новые значения для наших столбцов.

После SET — пишем имена столбцов, которые хотим обновить.

За ними — знак равенства и новое обновленное значение.

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

Но стоп, какие строки обновляются этой командой?

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

В этом случае мы сопоставляем строки, используя их столбец id , и обновляем строку с id 1.

Удаление данных из SQL таблицы

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

Для этого можно использовать команду DELETE :

Как и при обновлении данных, чтобы определить, какие именно строки мы хотим удалить, нам нужно условие WHERE .

Удаление таблиц в PostgreSQL

Если вдруг ты решил изменить структуру базы и для этого нужно удалить всю таблицу, то тебе подойдет команда DROP TABLE :

Это приведет к удалению всей таблицы products из базы данных.

Будь очень осторожен с командой DELETE !

Я бы не позавидовал тому, кто “случайно” удалит не ту таблицу из базы данных.

Удаление баз данных PostgreSQL

Точно так же ты можешь удалить из системы всю базу данных:

Заключение

Поздравляю, у тебя все получилось!

Ты установил и запустили PostgreSQL. Ты изучил основные команды SQL и проделали с ними несколько интересных вещей.

Эти несколько простых команд — основа, которую ты будешь использовать большую часть времени при взаимодействии с базами данных, поэтому тебе следует пойти и потренироваться и изучить самостоятельно. В следующий раз мы погрузимся глубже и обсудим Базы данных, роли и таблицы в PostgreSQL

Запускаем PostgreSQL в Docker: от простого к сложному

Трудно представить современную разработку без контейнеризации. Docker и Kubernetes прочно обосновались на рынке, и, если вы ещё не знакомы с этими технологиями, им явно стоит уделить внимание.

Запуск баз данных и других stateful приложений в контейнере – это тема интересная, но способная вызвать очередной Большой взрыв в комментариях. Оговорюсь сразу, мы не используем в production окружении PostgreSQL в Docker. Но делаем это в локальной разработке и на dev-стендах. Почему? Потому что это чертовски удобно!

В этой статье я хочу рассмотреть типовые способы запуска ванильного (то есть чистого/оригинального) PostgreSQL в контейнере, а также проблемы и их возможные решения, с которыми может столкнуться software engineer. Статья задумывалась как небольшое руководство для новых ребят, приходящих в мою команду, но, уверен, будет полезна и более широкой аудитории.

Прежде чем мы начнём

Вам понадобится Docker на машине, где вы будете проводить эксперименты. Скачайте его с официального сайта и установите. Настоятельно рекомендуется использовать актуальную версию.

Все приведенные в этой статье команды и скрипты запускались на версии Docker Desktop 3.5.2 и выше. Их работоспособность на старых версиях Docker не гарантируется.

Работоспособность скриптов на более старых версиях Docker не гарантируется

Так же подразумевается, что у вас есть базовые навыки работы с Docker. Если ещё нет, то для начала крайне желательно ознакомиться со статьями один и два.

Где взять образы PostgreSQL?

Официальные образы PostgreSQL опубликованы на Docker Hub. Там же можно найти базовые инструкции по использованию этих образов. Я буду опираться в том числе и на них.

Hello Postgres

Официальный образ Постгреса очень продвинутый и позволяет настраивать множество параметров. Для быстрого старта большинство из них можно оставить как есть, но вот пароль суперпользователя придётся задать явно:

Эта команда запустит нам контейнер PostgreSQL в фоновом (detached) режиме и присвоит ему имя habr-pg:

Контейнер с PostgreSQL, запущенный в Docker

Классно, не правда ли? А что мы можем делать с этой базой данных? К сожалению, на текущий момент не так уж и много. Через интерфейс Docker можно запустить CLI, подключиться к контейнеру и уже оттуда запустить, например, psql:

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

И тут мы сталкиваемся с первой проблемой: что вернёт нам запрос select version(); , выполненный в консоли? Мы не указали явным образом версию БД, которую хотим использовать. Давайте это исправим:

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

Отлично! С этого момента к базе данных можно подключиться, например, из IntelliJ IDEA:

Настройка подключения к БД в IntelliJ IDEA

Сейчас мы используем пользователя и базу данных в контейнере, создаваемых по умолчанию, я же предпочитаю указывать их явно. Финальная версия команды для запуска будет иметь вид:

psql можно запустить так: psql -U habrpguser -d habrdb

И соответствующий compose-файл:

Инициализация структуры БД

К текущему моменту мы научились запускать в контейнере необходимую нам версию PostgreSQL, переопределять суперпользователя и создавать базу данных с нужным именем.

Это хорошо, но чистая база данных вряд ли будет сильно полезна. Для работы/тестов/экспериментов нужно наполнить эту базу таблицами и другими объектами. Разумеется, всё это можно сделать вручную, но, согласитесь, гораздо удобнее, когда сразу после запуска вы автоматически получаете полностью готовую БД.

Разработчики официального образа PostgreSQL естественно предусмотрели этот момент и предоставили нам специальную точку входа для инициализации базы данных — docker-entrypoint-initdb.d. Любые *.sql или *.sh файлы в этом каталоге будут рассматриваться как скрипты для инициализации БД. Здесь есть несколько нюансов:

если БД уже была проинициализирована ранее, то никакие изменения к ней применяться не будут;

если в каталоге присутствует несколько файлов, то они будут отсортированы по имени с использованием текущей локали (по умолчанию en_US.utf8).

Инициализацию БД можно запустить через однострочник, но в этом случае требуется указывать абсолютный путь до каталога со скриптами:

Например, на моей машине это выглядит так:

В качестве обходного варианта можно использовать макрос, на лету определяя рабочую директорию, и запускать команду из каталога со скриптами:

Использование docker-compose файла в этом случае более удобно и позволяет указывать относительные пути:

Здесь хотелось бы акцентировать ваше внимание на одной простой вещи, о которой уже говорил в предыдущей статье: при создании миграций БД для ваших приложений отдавайте предпочтение чистому (plain) SQL. В этом случае их можно будет переиспользовать с минимальными затратами.

А куда сохраняются мои данные?

Базы данных – это в первую очередь история про персистентность. И. Хьюстон, кажется у нас проблема… К настоящему моменту мы никак не управляем долговременным хранением нашей базы данных. Эту задачу целиком на себя берёт Docker, автоматически создавая volume для контейнера с БД. Есть целый ворох причин, почему это плохо, начиная от банальной невозможности просматривать содержимое volume’ов в бесплатной версии Docker Desktop и заканчивая лимитами дискового пространства.

Разумеется, хорошей практикой является полностью ручное управление физическим размещением создаваемых баз данных. Для этого нам нужно подмонтировать соответствующий каталог (куда будут сохраняться данные) в контейнер и при необходимости переопределить переменную окружения PGDATA:

Вариант с макросом, использующий для инициализации БД скрипты из предыдущего раздела:

С однострочниками на этом закончим. Все дальнейшие шаги будем осуществлять только через compose-файл:

При запуске этого скрипта рядом с ним создастся директория pgdata, где будут располагаться файлы БД.

Healthcheck? Нет, не слышал…

Проверка состояния/работоспособности – healthcheck – вполне устоявшийся архитектурный шаблон, который вы должны взять на вооружение для всех ваших приложений. База данных, запускаемая в контейнере, не является исключением.

Основная задача healthcheck’а – как можно скорее уведомить среду, управляющую контейнером, о том, что с контейнером что-то не так. И самая простая стратегия решения проблемы – перезапуск контейнера.

Так же стоит сразу позаботиться об ограничении ресурсов для контейнера с БД. Для экспериментов и локального запуска вполне подойдёт секция resources (флаг —compatibility больше не требуется).

Healthcheck для PostgreSQL обычно основывается на использовании утилиты pg_isready как показано ниже:

А если хочу изменить параметры БД?

Для администраторов PostgreSQL не секрет, что конфигурация СУБД из коробки далека от идеальной и не очень подходит для эксплуатации. Я немного рассказывал об этом в своей статье про pg-index-health: ряд параметров нужно изменить в обязательном порядке, так как они влияют на производительность. Так же существует большое количество расширений для Постгреса, которые сделают эксплуатацию БД более удобной, наблюдаемой и управляемой. Одно из таких расширений — pg_stat_statements (кстати, оно пригодится нам позднее для мониторинга БД).

Ванильный образ PostgreSQL позволяет тюнить параметры и добавлять расширения на старте контейнера БД:

Разумеется, можно указать свой postgresql.conf. Оставлю это в качестве домашнего задания.

Посмотреть список установленных расширений можно с помощью запроса select * from pg_extension; .

А команда show позволит узнать текущее значение того или иного параметра, например: show random_page_cost; .

Не люблю консоль; дайте мне человеческий UI!

Далеко не все пользователи любят работать с БД из командной строки. Очень многие предпочитают использовать для этого продвинутый графический интерфейс, например pgAdmin.

Запустить ещё один контейнер, в котором будет бежать GUI, не сложно, но для удобной коммуникации с БД их лучше объединить в одну сеть:

pgAdmin стартует на порту 5050: перейдя на нужный адрес, можно будет настроить подключение к БД.

К БД можно подключиться как по имени контейнера, так и по имени сервиса

А как насчёт мониторинга?

В современной разработке любой микросервис или инфраструктурный компонент должен быть поставлен на мониторинг, то есть непрерывно отдавать метрики — ключевые показатели, позволяющие определить, как ведёт себя система в данный момент времени.

PostgreSQL не имеет встроенной интеграции с системами мониторинга наподобие Prometheus (или Zabbix). Вместо этого он полагается на использование внешних агентов — экспортеров. Мы у себя активно используем postgres_exporter, позволяющий добавлять свои собственные sql-запросы и кастомные метрики на их основе:

После запуска скрипта экспортер будет доступен на порту 9187 и отдавать метрики в формате Prometheus:

Разумеется, для полноценной постановки на мониторинг нужно ещё поднять сам Prometheus + Grafana, а так же загрузить подходящий dashboard, но это уже выходит за рамки данной статьи. Более того, если ваша служба информационной безопасности исповедует Zero Trust, то экспортер придётся прикрыть с помощью nginx и настроить mTLS.

В качестве заключения

На этом у меня всё. Приведенной выше конфигурации более чем достаточно для развёртывания БД PostgreSQL в Docker-контейнере на стенде разработки или локально.

Все приведённые в статье команды и docker-compose файлы также доступны на GitHub.

Установка и использование PostgreSQL на Ubuntu 18.04

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

PostgreSQL, или Postgres, — это система управления реляционными базами данных, которая предоставляет собой реализацию языка запросов SQL. Она является популярным решением для множества небольших и крупных проектов и имеет ряд преимущество, включая соответствие стандартам и множество передовых функций, таких как надежность и параллельность операций без блокировки при чтении.

Настоящее руководство посвящено установке Postgres на VPS сервер с Ubuntu 18.04, а также содержит базовые рекомендации по администрированию баз данных.

Предварительные требования

Для выполнения данного руководства вам потребуется один сервер Ubuntu 18.04 с конфигурацией, выполненной согласно руководству Начальная настройка сервера на Ubuntu 18.04. После выполнения всех предварительных действий ваш сервер должен иметь пользователя без прав root с разрешениями sudo и базовый брандмауэр.

Шаг 1 — Установка PostgreSQL

Используемые по умолчанию репозитории Ubuntu содержат пакеты Postgres, поэтому вы можете устанавливать их с помощью системы управления пакетами apt .

Поскольку это первый запуск apt в этой сессии, необходимо обновить локальный индекс пакетов. После этого установите пакет Postgres вместе с пакетом -contrib , который содержит дополнительные утилиты и функциональные возможности:

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

Шаг 2 — Использование ролей и баз данных в PostgreSQL

По умолчанию Postgres использует концепцию “ролей” для выполнения аутентификации и авторизации. В некоторых аспектах они напоминают обычные учетные записи в Unix, однако Postgres не делает различий между пользователями и группами и предпочитает использовать более гибкий термин “роль”.

После установки Postgres настроена на использование аутентификации ident, что значит, что выполняется привязка ролей Postgres с соответствующей системной учетной записью Unix/Linux. Если роль существует внутри Postgres, пользователь Unix/Linux с тем же именем может выполнить вход в качестве этой роли.

В ходе установки была создана учетную запись пользователя postgres, которая связана с используемой по умолчанию ролью Postgres. Чтобы использовать Postgres, вы можете войти в эту учетную запись.

Существует несколько способов использования этой учетной записи для доступа к Postgres.

Переключение на учетную запись postgres

Вы можете переключиться на учетную запись postgres на вашем сервере с помощью следующей команды:

Теперь вы можете немедленно получить доступ к командной строке Postgres с помощью следующей команды:

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

Закройте командную строку PostgreSQL с помощью следующей команды:

В результате вы вернетесь в командную строку postgres в Linux.

Доступ к командной строке Postgres без переключения учетных записей

Также вы можете запустить необходимую вам команду с учетной записью postgres напрямую с помощью sudo.

Например, в последнем примере от вас требовалось перейти в командную строку Postgres с помощью переключения на пользователя postgres и последующего запуска psql , чтобы открыть командную строку Postgres. Вы можете сделать это в один прием с помощью отдельной команды psql , используя пользователя postgres с sudo следующим образом:

Это позволит выполнить вход в Postgres без необходимости использования промежуточной командной строки bash .

Вы снова сможете выйти из интерактивной сессии Postgres с помощью следующей команды:

Многие варианты использования требуют использования сразу нескольких ролей Postgres. Ниже вы узнаете, как выполнить настройку в таких случаях.

Шаг 3 — Создание новой роли

К настоящему моменту у вас есть только роль postgres, настроенная внутри базы данных. Вы можете создавать новые роли из командной строки с командой createrole . Флаг —interactive будет запрашивать имя новой роли, а также попросит указать, будут ли у этой роли права superuser.

Если вы выполнили вход в учетную запись postgres, то можете создать нового пользователя с помощью следующей команды:

Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:

Скрипт будет предлагать варианты на выбор и, исходя из ваших ответов, выполнять нужные команды Postgres для создания пользователя в соответствии с вашими спецификациями.

Вы можете получить дополнительный контроль с помощью дополнительных флагов. Посмотрите возможные варианты на странице man :

В результате этих действий у вас будет новый пользователь, но вам предстоит еще добавить базы данных. В следующем разделе описан этот процесс.

Шаг 4 — Создание новой базы данных

Еще одно предположение, которое система аутентификации Postgres использует по умолчанию, состоит в том, что для любой роли, используемой для входа, существует база данных с тем же именем, к который роль может получить доступ.

Это означает, что если созданный вами в последнем разделе пользователь будет иметь имя sammy, эта роль попытается подключиться к базе данных, которая также называется «sammy» по умолчанию. Вы можете создать соответствующую базу данных с помощью команды createdb .

Если вы используете учетную запись postgres, необходимо ввести следующее:

Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:

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

Шаг 5 — Открытие командной строки Postgres с новой ролью

Чтобы выполнить аутентификацию с помощью ident , вам потребуется пользователь Linux с тем же именем, что и имя роли и базы данных в Postgres.

Если у вас нет соответствующего пользователя Linux, то вы можете создать его с помощью команды adduser . Вы должны воспользоваться учетной записи без прав root с привилегиями sudo (т.е. не выполняя вход в качестве пользователя postgres):

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

Либо же вы можете сделать следующее:

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

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

После входа вы можете проверить данные о текущем подключении:

Это полезно, если вы подключены к нестандартным базам данных или используете нестандартных пользователей.

Шаг 6 — Создание и удаление таблиц

Теперь, когда вы уже знаете, как подключиться к системе управления базами данных PostgreSQL, можно переходить к знакомству с основными задачами по управлению, которые решает Postgres.

Во-первых, создайте таблицу для хранения данных. Например, таблицу, где описано оборудование для детских площадок.

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

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

Вы можете узнать больше о том, как создавать и управлять таблицами в Postgres, здесь.

Чтобы продемонстрировать процесс, создайте простую таблицу:

Эти команды создадут таблицу с описью оборудования для установки на игровых площадках. Она начинается с идентификатора оборудования, который имеет тип serial . Это тип данных представляет собой целое число с автоматическим инкрементированием. Вы также указали для этого столбца ограничение в виде primary key , что означает, что значения должны быть уникальными и не равны null.

Для двух из столбцов ( equip_id и install_date ) команды не указали длину поля. Это объясняется тем, что некоторые типы столбцов не требуют указания длины, поскольку длина подразумевается в зависимости от типа значения.

Следующие две команды создают столбцы type и color оборудования соответственно, каждый из которых не может быть пустым. После этого команда создает столбец location и ограничение, требующее, чтобы значение было одним из восьми возможных вариантов. Последняя команда создает столбец даты, которая указывает дату установки оборудования.

Вы можете просмотреть вашу новую таблицу, введя следующую команду:

Ваша таблица игрового оборудования готова, но здесь есть что-то под названием playground_equip_id_seq с типом данных sequence . Это представление типа serial, который присвоен столбцу equip_id. Оно отслеживает следующий номер последовательности и создается автоматически для столбцов данного типа.

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

Шаг 7 — Добавление, запрос и удаление данных в таблице

Теперь, когда у вас есть таблица, вы можете ввести в нее данные .

В качестве примера добавьте строги swing (качели) и slide (горка), вызвав таблицу, куда вы хотите добавить данные, указав столбцы и предоставив данные для каждого столбца:

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

Еще один момент, который необходимо учитывать, состоит в том, что вы не должны указывать значения для столбца equip_id . Это объясняется тем, что они автоматически генерируются всякий раз, когда в таблице создается новая строка.

Получите добавленную вами информацию, введя следующую команду:

Вы можете убедиться, что столбец equip_id уже заполнен успешно, а все другие данные были организованы корректно.

Если же строка slide в таблице разрывается, вы можете удалить строку из таблицы, использовав следующую команду:

Запросите таблицу еще раз:

Вы видите, что ваш строка slide уже не является частью таблицы.

Шаг 8 — Добавление и удаление столбцов таблицы

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

Если вы снова просмотрите данные таблицы, то увидите, что новый столбец был добавлен (но не было добавлено данных):

Удаление столбца является таким же простым. Если вы обнаружите, что ваша команда использует отдельный инструмент для отслеживания истории обслуживания, то можете удалить столбец, введя следующую команду:

Эта команда удаляет столбец last_maint и любые значения внутри него, но оставляет все другие данные нетронутыми.

Шаг 9 — Обновление данных в таблице

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

Вы можете обновить значения существующей записи, запросив нужную вам запись и установите для столбца значение, которое вы хотите использовать. Вы можете запросить записи «swing» (это будет соответствовать каждой записи swing в таблице) и изменить цвет на «red». Это может быть полезным, если вы дали задание на покраску качелей:

Вы можете убедиться, что операция прошла успешной, запросив данные еще раз:

Как видите, ваша горка теперь имеет красный цвет.

Заключение

Вы настроили PostgreSQL на сервере Ubuntu 18.04. Однако вам еще очень многое предстоит узнать о Postgres. Ниже вы найдете несколько руководств по использованию Postgres:

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.