Как установить PostgreSQL в Ubuntu 20.04 LTS

Введение

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

В этом руководстве мы объясним, как установить PostgreSQL на VPS под управлением Ubuntu 20.04 LTS Focal Fossa.

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

Подключение к серверу

Чтобы получить доступ к серверу, вам нужно знать IP-адрес. Вам также потребуется ваше имя пользователя и пароль для аутентификации. Чтобы подключиться к серверу как root, введите следующую команду:

ssh root@IP_DEL_SERVER

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

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

ssh nome_utente@IP_DEL_SERVER

Затем вам будет предложено ввести пароль пользователя.

Стандартный порт для подключения по ssh — 22, если ваш сервер использует другой порт, вам нужно будет указать его с помощью параметра -p, затем введите следующую команду:

ssh nome_utente@IP_DEL_SERVER -p PORTA

Теперь вы подключены к своему серверу и готовы начать установку Postgres.

Установить PostgreSQL

Вы можете установить PostgresSQL, используя официальный репозиторий Ubuntu с помощью apt.

Обновите список пакетов:

sudo apt update

Установить PostgreSQL

sudo apt install postgresql postgresql-contrib

Используйте PostgreSQL

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

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

Процедура установки создала учетную запись пользователя с именем postgres, связанную с ролью Postgres по умолчанию.

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

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

Доверие: роль может подключаться без пароля, если выполняются условия, определенные в pg_hba.conf.
Пароль: Роль может подключиться, указав пароль. Пароли могут храниться как scram-sha-256, md5 и password (в виде открытого текста).
Ident: Поддерживается только для соединений TCP/IP. Он работает, получая имя пользователя клиентской ОС с дополнительным сопоставлением имени пользователя.
Peer: идентичен Ident, но поддерживается только для локальных подключений.

Аутентификация клиента PostgreSQL определяется в файле конфигурации с именем pg_hba.conf. По умолчанию PostgreSQL использует одноранговый метод аутентификации для локальных подключений.

Пользователь postgres создается автоматически при установке PostgreSQL. Этот пользователь является суперпользователем экземпляра PostgreSQL и эквивалентен привилегированному пользователю MySQL.

Для использования этой учетной записи и доступа к postgres существует несколько процедур.

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

sudo -i -u postgres

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

psql

psql (12.2 (Ubuntu 12.2-4))
 Type "help" for help.

 postgres=#

Чтобы выйти из командной строки Postgres, введите:

\q

Создать новую роль

В настоящее время в базе данных настроена только роль postgres. Вы можете создавать новые роли из командной строки с помощью команды « createrole ». Параметр --interactive спросит у вас имя новой роли и следует ли включить права суперпользователя.

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

\q

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

createuser --interactive

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

sudo -u postgres createuser --interactive

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


Enter name of role to add: noviello
 Shall the new role be a superuser? (y/n) y

Создать новую базу данных

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

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

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

createdb noviello

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

sudo -u postgres createdb noviello

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

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

Выполните эту процедуру с учетной записью пользователя, отличной от postgres.

Чтобы создать нового пользователя:

sudo adduser noviello

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

sudo -i -u noviello

psql

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

psql -d postgres

Чтобы получить информацию о текущем соединении:

\conninfo

Вы должны увидеть ответное сообщение, подобное этому:


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

Добавлять и удалять данные в таблицах

Ниже приведен пример создания таблицы:

CREATE TABLE test (
 id serial PRIMARY KEY,
 esempio1 varchar (50) NOT NULL,
 esempio2 varchar (25) NOT NULL,
 esempio3 varchar(25) check (esempio3 in ('valore1', 'valore2', 'valore3', 'valore4', 'valore5', 'valore6', 'valore7', 'valore8')),
 datains date
 );

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

\d

Вы должны увидеть ответное сообщение, подобное этому:


 List of relations
 Schema | Name | Type | Owner
 --------+-------------------------+----------+---------
 public | test | table | noviello
 public | testidseq | sequence | noviello
 (2 rows)

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

\dt

Вы должны увидеть ответное сообщение, подобное этому:


 List of relations
 Schema | Name | Type | Owner
 --------+------------+-------+---------
 public | test | table | noviello
 (1 rows)

Чтобы вставить новые значения в таблицу:

INSERT INTO test (esempio1, esempio2, esempio3, datains) VALUES ('valore1', 'valore2', 'valore3', '2018-06-29');

INSERT INTO test (esempio1, esempio2, esempio3, datains) VALUES ('valore4', 'valore5', 'valore6', '2018-06-29');

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

SELECT * FROM test;

Вы должны увидеть ответное сообщение, подобное этому:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 1 | valore1 | valore2 | valore3 | 2018-06-29
 2 | valore4 | valore5 | valore6 | 2018-06-29
 (2 row)

Чтобы удалить строку из базы данных:

DELETE FROM test WHERE esempio1 = 'valore1';

Снова извлеките информацию из базы данных, чтобы убедиться, что строка была удалена:

SELECT * FROM test;

Вы должны увидеть ответное сообщение, подобное этому:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 2 | valore4 | valore5 | valore6 | 2018-06-29
 (1 row)

Удалить и добавить столбцы в таблицы

Чтобы добавить столбец в базу данных:

ALTER TABLE test ADD altro date;

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

SELECT * FROM test;

Вы должны увидеть ответное сообщение, подобное этому:


 id | esempio1 | esempio2 | esempio3 | datains | altro
 ----+-----------+-----------+-----------+------------+-----------
 2 | valore4 | valore5 | valore6 | 2018-06-29 |
 (1 row)

Чтобы удалить столбец:

ALTER TABLE test DROP altro;

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

SELECT * FROM test;

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


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 2 | valore4 | valore5 | valore6 | 2018-06-29
 (1 row)

Изменить данные в таблице

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

UPDATE test SET esempio2 = 'nuovovalore' WHERE esempio1 = 'valore4';

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

SELECT * FROM test;

Вы должны увидеть ответное сообщение, подобное этому:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 2 | valore4 | nuovovalore | valore6 | 2018-06-29
 (1 row)

Создать базу данных и роль PostgreSQL

Только суперпользователи и роли с привилегией CREATEROLE могут создавать новые роли.

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

Создайте новую роль PostgreSQL:

sudo su - postgres -c "createuser john"

Создайте новую базу данных PostgreSQL:

sudo su - postgres -c "createdb johndb"

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

sudo -u postgres psql

Запустите следующий запрос:

grant all privileges on database johndb to john;

Включить удаленный доступ к серверу PostgreSQL

По умолчанию сервер PostgreSQL прослушивает только локальный интерфейс 127.0.0.1.

Чтобы разрешить удаленный доступ к вашему серверу PostgreSQL, откройте файл конфигурации postgresql.conf и добавьте listen_addresses = '*' в раздел CONNECTIONS AND AUTHENTICATION.

sudo nano /etc/postgresql/12/main/postgresql.conf

#------------------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #------------------------------------------------------------------------------

 # - Connection Settings -

 listen_addresses = '*' # what IP address(es) to listen on;

/etc/postgresql/12/main/postgresql.conf

Сохраните файл и перезапустите службу PostgreSQL:

sudo service postgresql restart

Проверьте изменения с помощью утилиты ss:

ss -nlt | grep 5432

Вывод показывает, что сервер PostgreSQL прослушивает все интерфейсы 0.0.0.0:

LISTEN 0 244 0.0.0.0:5432 0.0.0.0:*
 LISTEN 0 244 [::]:5432 [::]:*

Следующим шагом является настройка сервера для приема удаленных подключений путем редактирования pg_hba.conf.

Вот несколько примеров, показывающих различные варианты использования:

# TYPE DATABASE USER ADDRESS METHOD

 # The user jane can access all databases from all locations using md5 password
 host all jane 0.0.0.0/0 md5

 # The user jane can access only the janedb from all locations using md5 password
 host janedb jane 0.0.0.0/0 md5

 # The user jane can access all databases from a trusted location (192.168.1.134) without a password
 host all jane 192.168.1.134 trust

/etc/postgresql/12/main/pg_hba.conf

Последний шаг — открыть порт 5432 брандмауэра.

Предполагая, что вы используете UFW для управления брандмауэром и хотите разрешить доступ из подсети 192.168.1.0/24, выполните следующую команду:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

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

Вывод

Установка PostgreSQL на Ubuntu 20.04 LTS Focal Fossa завершена.