Реляционные СУБД являются ключевыми элементами для многих сайтов и веб-приложений. Они дают возможность структурированно хранить информацию и удобно работать с ней.
PostgreSQL – это реляционная СУБД, базирующаяся на языке SQL. Ее используют в проектах разного масштаба, так как она совместима со многими Unix-подобными ОС (а также macOS и Microsoft Windows) и обладает расширенной функциональностью.
Далее вы узнаете, как установить PostgreSQL на свой сервер, а также получите общую информацию о работе с ролями, базами данных и таблицами.
Требования к серверу
Для выполнения инструкций из этой статьи у вас должен быть сервер с установленной ОС Ubuntu 18.04, пользователем, который может выполнять команды sudo, и базовым файрволом.
Шаг 1: Установка PostgreSQL
Установка выполняется стандартно с использованием утилиты apt, так как PostgreSQL по умолчанию есть в репозиториях Ubuntu.
Сначала обновите локальный индекс пакетов:
$ sudo apt update
Затем установите PostgreSQL вместе с пакетом –contrib, который содержит дополнительные полезные инструменты для работы с СУБД:
$ sudo apt install postgresql postgresql-contrib
Следующая задача – проверить, все ли все работает, и понять, чем PostgreSQL отличается от других похожих СУБД.
Шаг 2: Использование ролей и баз данных
По умолчанию PostgreSQL использует концепцию ролей (role) для процедур аутентификации и авторизации. Роли в чем-то похожи на стандартные Unix-аккаунты, но отличие заключается в том, что PostgreSQL не выделяет пользователей и группы по отдельности, а вместо них использует более гибкий термин – «роль».
После установки СУБД настроена на использование идентичной (ident) аутентификации: роли PostgreSQL будут связаны с такими же аккаунтами в системах Unix/Linux. Если роль существует в PostgreSQL, то пользователь Unix/Linux с таким же именем сможет авторизоваться в СУБД под этой ролью.
Во время установки будет создан аккаунт postgres, который будет ассоциироваться с ролью PostgreSQL по умолчанию. Под ним нужно авторизоваться для того, чтобы начать работу с PostgreSQL.
Есть несколько способов получить доступ к PostgreSQL через этот аккаунт.
Во-первых, вы можете переключиться на этот аккаунт:
$ sudo -i -u postgres
А дальше перейти в консоль PostgreSQL:
$ psql
Для того чтобы выйти из нее, надо ввести:
\q
И тогда вы вернетесь в консоль Linux.
Во-вторых, можно выполнить нужную команду от аккаунта postgres без переключения аккаунтов, т.е. с использованием sudo.
Например, попасть в консоль PostgreSQL можно всего одной командой:
$ sudo -u postgres psql
Теперь перейдем к работе с ролями, так как зачастую для работы с PostgreSQL требуется больше одной роли.
Шаг 3: Создание новой роли
Для создания ролей используется команда createrole. Если использовать ключ --interactive, то у вас будет запрошено имя новой роли. Кроме того, этой роли можно выдать и права суперпользователя.
После авторизации под аккаунтом postgres создайте нового пользователя:
$ createuser --interactive
Если вам удобнее использовать вариант без переключения аккаунтов, тогда команда будет выглядеть следующим образом:
$ sudo -u postgres createuser --interactive
Затем вам нужно будет выбрать имя для роли и решить, будут ли у нее права суперпользователя.
Enter name of role to add: testuser Shall the new role be a superuser? (y/n) y
Если вам нужно еще больше настроек для создаваемой роли, посмотрите все возможные ключи при помощи команды:
$ man createuser
Итак, у вас появился новый пользователь, но пока нет ни одной добавленной базы данных. Далее рассмотрим, как добавить новую БД.
Шаг 4: Создание новой базы данных
Еще одна предустановка PostgreSQL заключается в том, что для каждой роли, используемой для авторизации, предполагается база данных с таким же названием, к которой данная роль имеет доступ.
Если ранее вы создали роль testuser, то по умолчанию роль будет пытаться получить доступ к базе данных testuser. Для создания базы данных используется команда createdb. Авторизуйтесь под аккаунтом postgres и введите команду:
createdb testuser
Команда без переключения аккаунта будет выглядеть следующим образом:
$ sudo -u postgres createdb testuser
Шаг 5: Создание таблицы
Перейдем к работе с таблицами.
Базовый синтаксис для создания таблицы:
CREATE TABLE table_name ( column_name1 col_type (field_length) column_constraints, column_name2 col_type (field_length), column_name3 col_type (field_length) );
Сначала команда дает таблице имя, затем определяет названия столбцов, а также их тип и максимальную длину значений.
Существует множество типов данных, например:
- date – календарная дата;
- int – стандартный целочисленный тип;
- varchar – символьная строка переменной длины, которая указывается в скобках;
- real – число одинарной точности с плавающей точкой;
- point – геометрическая точка в плоскости.
Пример команды с заполненными данными:
CREATE TABLE weather ( city varchar(80), temp_lo int, -- минимальная температура дня temp_hi int, -- максимальная температура дня prcp real, -- уровень осадков date date );
Дефисы используются для того, чтобы задать комментарий для столбца.
Можно задать комментарий и для таблицы:
COMMENT ON TABLE название_таблицы IS 'Это моя таблица.';
Если вы хотите удалить комментарий, то нужно ввести:
COMMENT ON TABLE mytable IS NULL;
Для того чтобы посмотреть созданную таблицу, введите:
\d
Шаг 6: Добавление и удаление данных
Для добавления новой информации используется команда INSERT INTO. Можно использовать два варианта команды: короткий и длинный.
Синтаксис короткого варианта команды:
INSERT INTO название_таблицы VALUES (1, 'Milk', 9.99);
В этом случае вам нужно помнить последовательность столбцов, чтобы ввести правильное значение для каждого столбца.
Второй вариант – использовать команду с указанием столбцов:
INSERT INTO название_таблицы (product_no, name, price) VALUES (1, 'Cheese', 9.99); INSERT INTO название_таблицы (name, price, product_no) VALUES ('Cheese', 9.99, 1);
В этом случае данные будут записаны в соответствующие указанные столбцы.
Для удаления данных используйте команду DELETE FROM. Если вы введете команду...
DELETE FROM название_таблицы WHERE название_столбца = значение;
...будут удалены все строки, в которых значение выбранного столбца равняется заданному значению.
Пример команды:
DELETE FROM products WHERE price = 10;
Тут важно помнить, что команда без указания уточняющих данных удалит все строки таблицы:
DELETE FROM products;
Шаг 7: Добавление и удаление столбцов
Для добавления столбцов используется команда ALTER TABLE:
ALTER TABLE название_таблицы ADD название_нового_столбца;
После ввода этой команды в таблице появится новый столбец с выбранным названием.
Удалять столбцы легко, просто замените ADD на DROP:
ALTER TABLE название_таблицы DROP название_существующего_столбца;
После этого указанный столбец и его данные будут удалены.
Вместо заключения
В этой статье дана только базовая информация, которая поможет вам разобраться в том, как установить PostgreSQL и работать с этой СУБД. Для более детального изучения я рекомендую обратиться к официальной документации на английском языке либо к ее русскому переводу.
Комментарии