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

Обсудить

Установка и использование PostgreSQL на сервере Ubuntu 16.04Введение

Реляционные СУБД – это важные элементы многих веб-сайтов и приложений. Они позволяют удобно и структурированно хранить, организовывать и иметь доступ к информации.

PostgreSQL (или просто Postgres) – это реляционная СУБД, которая основывается на языке SQL. Это популярное решение для многих небольших и крупных проектов, так как сочетает в себе такие преимущества, как соответствие стандартам, эффективную сериализацию транзакций и многовариантное управление параллелизмом.

Из этого руководства вы узнаете, как установить PostgreSQL на сервер Ubuntu 16.04 и затем использовать эту СУБД.

Шаг 1: установка

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

Так как это первое использование apt в текущей сессии, необходимо обновить локальный индекс пакетов. А затем можно будет установить пакет PostgreSQL и еще пакет –contrib, который добавляет некоторые функции:

$ sudo apt-get update

$ sudo apt-get install postgresql postgresql-contrib

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

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

По умолчанию PostgreSQL использует концепцию под названием «роли» для работы с аутентификацией и авторизацией. Это в каком-то смысле похоже на стандартные Unix аккаунты, с тем различием, что PostgreSQL не делает различий между пользователями и группами, и поэтому здесь подходит более гибкий термин «роль».

После установки СУБД PostgreSQL настроена на использование идентичной аутентификации  – то есть PostgreSQL получает имя пользователя Unix/Linux системы, которое будет использоваться в качестве разрешенного имени пользователя базы данных. Иными словами, если такое имя пользователя есть у пользователя Unix/Linux системы, то он сможет авторизоваться под этой ролью в PostgreSQL.

Есть несколько способов использовать этот аккаунт для получения доступа в PostgreSQL.

Переключение на PostgreSQL аккаунт

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

Переключитесь на аккаунт postgres на сервере:

$ sudo -i -u postgres

Теперь нужно перейти в консоль PostgreSQL:

$ psql

После входа в консоль вы сможете взаимодействовать с СУБД.

Чтобы выйти из консоли, введите:

\q

Вы вернетесь в обычную консоль Linux.

Вход в консоль PostgreSQL без переключения аккаунтов

Еще один способ, как можно выполнять команды PostgreSQL, - это при помощи sudo.

Допустим, вы хотите попасть в консоль PostgreSQL. Это можно сделать, просто введя одну команду psql от имени пользователя postgres с sudo:

$ sudo -u postgres psql

Так вы сразу будете авторизованы в PostgreSQL без необходимо заходить в оболочку bash.

Выйти можно так же при помощи команды:

\q

Шаг 3: создание новой роли

Сейчас у вас есть роль postgres, настроенная внутри базы данных. Новые роли можно создавать в командной строке при помощи команды createrole. Использование ключа --interactive позволит вводить необходимые для системы значения.

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

$ createuser --interactive

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

$ sudo -u postgres createuser --interactive

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

Enter name of role to add: tommy

Shall the new role be a superuser? (y/n) y

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

$ man createuser

Шаг 4: создание новой базы данных

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

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

Если вы авторизованы под аккаунтом postgres, то введите:

$ createdb tommy

А без переключения аккаунтов можно ввести вот такую команду:

$ sudo -u postgres createdb tommy

Как открыть командную строку PostgreSQL с использованием новой роли

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

Если такого пользователя нет, его можно создать при помощи команды adduser:

$ sudo adduser tommy

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

$ sudo -i -u tommy

$ psql

Либо это можно сделать одной командой:

$ sudo -u tommy psql

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

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

$ psql -d postgres

После авторизации можно проверить информацию о соединении при помощи команды:

$ \conninfo

В выводе будет:

You are connected to database "tommy" as user "tommy" via socket in "/var/run/postgresql" at port "5432".

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

Шаг 5: создание и удаление таблиц

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

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

Базовый синтаксис команды выглядит вот так:

CREATE TABLE table_name (

column_name1 col_type (field_length) column_constraints,

column_name2 col_type (field_length),

column_name3 col_type (field_length)

);

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

В качестве примера создадим вот такую таблицу:

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

);

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

Два столбца - equip_id и install_date - не имеют заданной длины поля. Некоторые столбцы могут не иметь заданной длины, так как она определяется типом данных этого столбца.

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

Посмотреть эту новую таблицу можно, если ввести:

$ \d

В выводе вы увидите:

               List of relations

Schema |       Name             |   Type    | Owner

--------+-------------------------+----------+-------

public | playground              | table    | tommy

public | playground_equip_id_seq | sequence | tommy

(2 rows)

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

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

$ \dt

Вывод:

       List of relations

Schema | Name | Type  | Owner

-------+------------+-------+-------

public | playground | table | tommy

(1 row)

Шаг 6: работа с данными в таблице

Как добавить, запросить и удалить данные в таблице

Теперь нужно научиться выполнять различные действия в созданной таблице.

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

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');

INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

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

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

А теперь проверьте информацию, которую вы только что добавили:

SELECT * FROM playground;

Вы увидите:

equip_id | type  | color  | location  | install_date

----------+-------+--------+-----------+--------------

     1 | slide | blue   | south  | 2014-04-28

     2 | swing | yellow | northwest | 2010-08-16

(2 rows)

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

Теперь о том, как удалить данные.

Для того, чтобы убрать строку, нужно добавить следующее (в данном случае для удаления элемента slide):

DELETE FROM playground WHERE type = 'slide';

Если еще раз посмотреть таблицу, то видно, что этого элемента больше нет:

SELECT * FROM playground;

Вывод:

 equip_id | type  | color  | location  | install_date

----------+-------+--------+-----------+--------------

        2 | swing | yellow | northwest | 2010-08-16

(1 row)

Шаг 7: работа со столбцами в таблице

Как добавить и удалить столбцы в таблице

Если вы хотите изменить таблицу после того, как она была создана (например, добавить еще один столбец last_maint date), то сделать это можно вот таким образом:

ALTER TABLE playground ADD last_maint date;

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

Если после этого вы посмотрите  таблицу, то увидите, что новый столбец был добавлен, но никакой информации он пока не содержит.

SELECT * FROM playground;

Вывод:

 equip_id | type  | color  | location  | install_date | last_maint

----------+-------+--------+-----------+--------------+------------

        2 | swing | yellow | northwest | 2010-08-16   |

(1 row)

Так же легко можно и удалять ненужные столбцы:

ALTER TABLE playground DROP last_maint;

Шаг 8: обновление данных в таблице

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

Значения уже существующих данных можно изменить следующим образом: нужно ввести название записи и столбца, а также значение, которое вы хотите использовать. В примере ниже значение “color” у всех записей “swing” будет изменено на “red”:

UPDATE playground SET color = 'red' WHERE type = 'swing';

Проверьте, что изменение было сделано:

SELECT * FROM playground;

Вывод:

 equip_id | type  | color | location  | install_date

----------+-------+-------+-----------+--------------

        2 | swing | red   | northwest | 2010-08-16

(1 row)

Заключение

Теперь вы научились установке и работе c СУБД PosgreSQL, установленной на сервере с ОС Ubuntu 16.04. Конечно же, существует множество вещей, о которых не было рассказано в этой статье. Поэтому рекомендую обратиться к следующим ресурсам:

Серверы Таймвэб

Комментарии