Системы управления реляционными базами данных являются ключевым компонентом многих веб-сайтов и приложений. Они предоставляют структурированный способ хранения, организации и доступа к информации.
PostgreSQL, или Postgres, — это система управления реляционными базами данных, которая предоставляет собой реализацию языка запросов SQL. Она является популярным решением для множества небольших и крупных проектов и имеет ряд преимущество, включая соответствие стандартам и множество передовых функций, таких как надежность и параллельность операций без блокировки при чтении.
Настоящее руководство посвящено установке Postgres на VPS сервер с Ubuntu 18.04, а также содержит базовые рекомендации по администрированию баз данных.
Предварительные требования
Для выполнения данного руководства вам потребуется один сервер Ubuntu с конфигурацией, выполненной согласно руководству Начальная настройка сервера на Ubuntu 18.04. После выполнения всех предварительных действий ваш сервер должен иметь пользователя без прав root с разрешениями sudo и базовый брандмауэр.
Шаг 1 — Установка PostgreSQL
Используемые по умолчанию репозитории Ubuntu содержат пакеты Postgres, поэтому вы можете устанавливать их с помощью системы управления пакетами apt.
Поскольку это первый запуск apt в этой сессии, необходимо обновить локальный индекс пакетов. После этого установите пакет Postgres вместе с пакетом -contrib, который содержит дополнительные утилиты и функциональные возможности:
sudo apt update
sudo apt install postgresql postgresql-contrib
После установки программного обеспечения мы можем переходить к изучению его работы и возможных отличий от аналогичных систем управления базами данных, которые вы, возможно, использовали.
Шаг 2 — Использование ролей и баз данных в PostgreSQL
По умолчанию Postgres использует концепцию “ролей” для выполнения аутентификации и авторизации. В некоторых аспектах они напоминают обычные учетные записи в Unix, однако Postgres не делает различий между пользователями и группами и предпочитает использовать более гибкий термин “роль”.
После установки Postgres настроена на использование аутентификации ident, что значит, что выполняется привязка ролей Postgres с соответствующей системной учетной записью Unix/Linux. Если роль существует внутри Postgres, пользователь Unix/Linux с тем же именем может выполнить вход в качестве этой роли.
В ходе установки была создана учетную запись пользователя postgres, которая связана с используемой по умолчанию ролью Postgres. Чтобы использовать Postgres, вы можете войти в эту учетную запись.
Существует несколько способов использования этой учетной записи для доступа к Postgres.
Переключение на учетную запись postgres
Вы можете переключиться на учетную запись postgres на вашем сервере с помощью следующей команды:
sudo -i -u postgres
Теперь вы можете немедленно получить доступ к командной строке Postgres с помощью следующей команды:
psql
В результате вы можете получить доступ к командной строке postgreSQL, а уже отсюда свободно взаимодействовать с системой управления базами данных.
Закройте командную строку PostgreSQL с помощью следующей команды:
\q
В результате вы вернетесь в командную строку postgres в Linux.
Доступ к командной строке Postgres без переключения учетных записей
Также вы можете запустить необходимую вам команду с учетной записью postgres напрямую с помощью sudo.
Например, в последнем примере от вас требовалось перейти в командную строку Postgres с помощью переключения на пользователя postgres и последующего запуска psql, чтобы открыть командную строку Postgres. Вы можете сделать это в один прием с помощью отдельной команды psql, используя пользователя postgres с sudo следующим образом:
sudo -u postgres psql
Это позволит выполнить вход в Postgres без необходимости использования промежуточной командной строки bash.
Вы снова сможете выйти из интерактивной сессии Postgres с помощью следующей команды:
\q
Многие варианты использования требуют использования сразу нескольких ролей Postgres. Ниже вы узнаете, как выполнить настройку в таких случаях.
Шаг 3 — Создание новой роли
К настоящему моменту у вас есть только роль postgres, настроенная внутри базы данных. Вы можете создавать новые роли из командной строки с командой createrole. Флаг --interactive будет запрашивать имя новой роли, а также попросит указать, будут ли у этой роли права superuser.
Если вы выполнили вход в учетную запись postgres, то можете создать нового пользователя с помощью следующей команды:
createuser --interactive
Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:
sudo -u postgres createuser --interactive
Скрипт будет предлагать варианты на выбор и, исходя из ваших ответов, выполнять нужные команды Postgres для создания пользователя в соответствии с вашими спецификациями.
OutputEnter name of role to add: sammy
Shall the new role be a superuser? (y/n) y
Вы можете получить дополнительный контроль с помощью дополнительных флагов. Посмотрите возможные варианты на странице man:
man createuser
В результате этих действий у вас будет новый пользователь, но вам предстоит еще добавить базы данных. В следующем разделе описан этот процесс.
Шаг 4 — Создание новой базы данных
Еще одно предположение, которое система аутентификации Postgres использует по умолчанию, состоит в том, что для любой роли, используемой для входа, существует база данных с тем же именем, к который роль может получить доступ.
Это означает, что если созданный вами в последнем разделе пользователь будет иметь имя sammy, эта роль попытается подключиться к базе данных, которая также называется «sammy» по умолчанию. Вы можете создать соответствующую базу данных с помощью команды createdb.
Если вы используете учетную запись postgres, необходимо ввести следующее:
createdb sammy
Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:
sudo -u postgres createdb sammy
Подобная гибкость предоставляет несколько способов для создания баз данных в зависимости от необходимости.
Шаг 5 — Открытие командной строки Postgres с новой ролью
Чтобы выполнить аутентификацию с помощью ident, вам потребуется пользователь Linux с тем же именем, что и имя роли и базы данных в Postgres.
Если у вас нет соответствующего пользователя Linux, то вы можете создать его с помощью команды adduser. Вы должны воспользоваться учетной записи без прав root с привилегиями sudo (т.е. не выполняя вход в качестве пользователя postgres):
sudo adduser sammy
Когда новая учетная запись будет доступна, вы можете переключиться и подключиться к базе данных следующим образом:
sudo -i -u sammy
psql
Либо же вы можете сделать следующее:
sudo -u sammy psql
Эта команда позволит выполнить вход автоматически, полагая, что все компоненты были настроены должным образом.
Если вы хотите, чтобы ваш пользователь подключился к другой базе данных, то вы можете сделать это с помощью следующей команды:
psql -d postgres
После входа вы можете проверить данные о текущем подключении:
\conninfo
OutputYou are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".
Это полезно, если вы подключены к нестандартным базам данных или используете нестандартных пользователей.
Шаг 6 — Создание и удаление таблиц
Теперь, когда вы уже знаете, как подключиться к системе управления базами данных PostgreSQL, можно переходить к знакомству с основными задачами по управлению, которые решает Postgres.
Во-первых, создайте таблицу для хранения данных. Например, таблицу, где описано оборудование для детских площадок.
Базовый синтаксис этой команды выглядит следующим образом:
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);
Как видите, эти команды дают таблице имя, а затем определяют столбцы, тип столбцов и максимальную длину поля данных. Кроме того, вы можете добавить ограничения таблицы для каждой колонки.
Вы можете узнать больше о том, как создавать и управлять таблицами в Postgres, здесь.
Чтобы продемонстрировать процесс, создайте простую таблицу:
CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);
Эти команды создадут таблицу с описью оборудования для установки на игровых площадках. Она начинается с идентификатора оборудования, который имеет тип serial. Это тип данных представляет собой целое число с автоматическим инкрементированием. Вы также указали для этого столбца ограничение в виде primary key, что означает, что значения должны быть уникальными и не равны null.
Для двух из столбцов (equip_id и install_date) команды не указали длину поля. Это объясняется тем, что некоторые типы столбцов не требуют указания длины, поскольку длина подразумевается в зависимости от типа значения.
Следующие две команды создают столбцы type и color оборудования соответственно, каждый из которых не может быть пустым. После этого команда создает столбец location и ограничение, требующее, чтобы значение было одним из восьми возможных вариантов. Последняя команда создает столбец даты, которая указывает дату установки оборудования.
Вы можете просмотреть вашу новую таблицу, введя следующую команду:
\d
Output List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+-------
public | playground | table | sammy
public | playground_equip_id_seq | sequence | sammy
(2 rows)
Ваша таблица игрового оборудования готова, но здесь есть что-то под названием playground_equip_id_seq с типом данных sequence. Это представление типа serial, который присвоен столбцу equip_id. Оно отслеживает следующий номер последовательности и создается автоматически для столбцов данного типа.
Если вы хотите только просмотреть таблицу без последовательности, можете ввести следующую команду:
\dt
Output List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | playground | table | sammy
(1 row)
Шаг 7 — Добавление, запрос и удаление данных в таблице
Теперь, когда у вас есть таблица, вы можете ввести в нее данные .
В качестве примера добавьте строги swing (качели) и slide (горка), вызвав таблицу, куда вы хотите добавить данные, указав столбцы и предоставив данные для каждого столбца:
INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
Вы должны быть внимательны при вводе данных, чтобы не допустить нескольких общих проблем. Во-первых, оборачивайте в кавычки не названия столбцов, а значения в столбцах.
Еще один момент, который необходимо учитывать, состоит в том, что вы не должны указывать значения для столбца equip_id. Это объясняется тем, что они автоматически генерируются всякий раз, когда в таблице создается новая строка.
Получите добавленную вами информацию, введя следующую команду:
SELECT * FROM playground;
Output equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
1 | slide | blue | south | 2017-04-28
2 | swing | yellow | northwest | 2018-08-16
(2 rows)
Вы можете убедиться, что столбец equip_id уже заполнен успешно, а все другие данные были организованы корректно.
Если же строка slide в таблице разрывается, вы можете удалить строку из таблицы, использовав следующую команду:
DELETE FROM playground WHERE type = 'slide';
Запросите таблицу еще раз:
SELECT * FROM playground;
Output equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
Вы видите, что ваш строка slide уже не является частью таблицы.
Шаг 8 — Добавление и удаление столбцов таблицы
После создания таблицы ее легко можно изменить, добавляя или удаляя столбцы. Добавьте столбец для отображения даты последнего технического обслуживания для каждого элемента оборудования, введя следующую команду:
ALTER TABLE playground ADD last_maint date;
Если вы снова просмотрите данные таблицы, то увидите, что новый столбец был добавлен (но не было добавлено данных):
SELECT * FROM playground;
Output equip_id | type | color | location | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
2 | swing | yellow | northwest | 2018-08-16 |
(1 row)
Удаление столбца является таким же простым. Если вы обнаружите, что ваша команда использует отдельный инструмент для отслеживания истории обслуживания, то можете удалить столбец, введя следующую команду:
ALTER TABLE playground DROP last_maint;
Эта команда удаляет столбец last_maint и любые значения внутри него, но оставляет все другие данные нетронутыми.
Шаг 9 — Обновление данных в таблице
Вы уже знаете, как добавлять записи в таблицу и удалять их, но в этом руководстве еще не было упомянуто изменений существующих записей.
Вы можете обновить значения существующей записи, запросив нужную вам запись и установите для столбца значение, которое вы хотите использовать. Вы можете запросить записи «swing» (это будет соответствовать каждой записи swing в таблице) и изменить цвет на «red». Это может быть полезным, если вы дали задание на покраску качелей:
UPDATE playground SET color = 'red' WHERE type = 'swing';
Вы можете убедиться, что операция прошла успешной, запросив данные еще раз:
SELECT * FROM playground;
Output equip_id | type | color | location | install_date
----------+-------+-------+-----------+--------------
2 | swing | red | northwest | 2010-08-16
(1 row)
Как видите, ваша горка теперь имеет красный цвет.
Свежие комментарии