УСТАНОВКА И ИСПОЛЬЗОВАНИЕ POSTGRESQL В CENTOS 8

Thank you for reading this post, don't forget to subscribe!

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

1: Установка PostgreSQL

Пакет PostgreSQL мож­но загру­зить из стан­дарт­но­го репо­зи­то­рия CentOS 8, кото­рый назы­ва­ет­ся AppStream. Здесь вы най­де­те мно­же­ство доступ­ных вер­сий. Что­бы выбрать необ­хо­ди­мую вер­сию, вы долж­ны акти­ви­ро­вать соот­вет­ству­ю­щую кол­лек­цию паке­тов (кол­лек­ции назы­ва­ют­ся пото­ка­ми модулей).

В DNF (стан­дарт­ном мене­дже­ре паке­тов CentOS 8) моду­ля­ми назы­ва­ют­ся спе­ци­аль­ные набо­ры RPM-паке­тов, кото­рые вме­сте состав­ля­ют боль­шее при­ло­же­ние. Такой под­ход дела­ет уста­нов­ку паке­тов и их зави­си­мо­стей более интуитивным.

Запро­си­те спи­сок доступ­ных пото­ков для моду­ля postgresql с помо­щью dnf:

dnf module list postgresql
postgresql              9.6                             client, server [d]                          PostgreSQL server and client module

postgresql              10 [d]                          client, server [d]                          PostgreSQL server and client module

postgresql              12                              client, server                              PostgreSQL server and client module

В этом выво­де пока­за­но, что в AppStream доступ­ны три вер­сии PostgreSQL: 9.6, 10 и 12. Поток, кото­рый предо­став­ля­ет вер­сию 10, явля­ет­ся пото­ком по умол­ча­нию (на это ука­зы­ва­ет [d]). Если вы хоти­те уста­но­вить эту вер­сию, вы може­те про­сто запу­стить команду:

sudo dnf install postgresql-server

и перей­ти к сле­ду­ю­ще­му раз­де­лу ману­а­ла. Хотя вер­сия 10 все еще под­дер­жи­ва­ет­ся, в этом мануа­ле мы уста­но­вим вер­сию 12, послед­ний релиз на момент напи­са­ния этой статьи.

Что­бы уста­но­вить PostgreSQL вер­сии 12, вы долж­ны вклю­чить поток моду­лей этой вер­сии. При вклю­че­нии это­го пото­ка вы пере­опре­де­ли­те поток по умол­ча­нию и сде­ла­е­те все паке­ты, свя­зан­ные с вклю­чен­ным пото­ком, доступ­ны­ми в систе­ме. Обра­ти­те вни­ма­ние: в систе­ме мож­но вклю­чить толь­ко один поток любо­го из суще­ству­ю­щих модулей.

Что­бы вклю­чить поток Postgres для вер­сии 12, выпол­ни­те сле­ду­ю­щую команду:

sudo dnf module enable postgresql:12

При появ­ле­нии запро­са нажми­те y, а затем Enter, что­бы под­твер­дить дей­ствие, после чего вы увидите:

====================================================================
Package        Architecture  Version          Repository      Size
====================================================================
Enabling module streams:
postgresql                   12
Transaction Summary
====================================================================
Is this ok [y/N]: y

Вклю­чив поток для вер­сии 12, вы може­те уста­но­вить пакет postgresql-server, после чего у вас появит­ся PostgreSQL 12 и все зави­си­мо­сти этой СУБД.

sudo dnf install postgresql-server

Под­твер­ди­те установку:

. . .
Install  4 Packages
Total download size: 16 M
Installed size: 62 M
Is this ok [y/N]: y

Уста­нов­ка PostgreSQL завер­ше­на. Теперь мож­но озна­ко­мить­ся с её функ­ци­я­ми и создать новый кластер.

2: Создание кластера PostgreSQL

Для нача­ла нуж­но создать новый кла­стер базы дан­ных PostgreSQL. Кла­стер баз дан­ных – это набор баз, кото­рые управ­ля­ют­ся одним экзем­пля­ром сер­ве­ра. Что­бы создать кла­стер, нуж­но создать ряд ката­ло­гов, в кото­рые будут поме­ще­ны дан­ные БД, создать таб­ли­цы и базы дан­ных template1 и postgres.

База дан­ных template1 необ­хо­ди­ма для созда­ния новой БД. Все, что хра­нит­ся в ней, будет поме­ще­но в новую базу дан­ных при ее созда­нии. postgres – это база дан­ных по умол­ча­нию, пред­на­зна­чен­ная для рабо­ты с поль­зо­ва­те­ля­ми, ути­ли­та­ми и сто­рон­ни­ми приложениями.

Пакет Postgres, кото­рый мы уста­но­ви­ли на преды­ду­щем эта­пе, постав­ля­ет­ся с удоб­ным скрип­том postgresql-setup, кото­рый берет на себя низ­ко­уров­не­вое адми­ни­стри­ро­ва­ние кла­сте­ра базы данных.

Что­бы создать кла­стер базы дан­ных, запу­сти­те скрипт с пара­мет­ром —initdb:

sudo postgresql-setup --initdb

Вы уви­ди­те сле­ду­ю­щий вывод:

* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Теперь запу­сти­те сер­вис PostgreSQL с помо­щью systemctl:

sudo systemctl start postgresql

Затем нуж­но вклю­чить сер­вис, что­бы доба­вить его в автозагрузку:

sudo systemctl enable postgresql

Вы уви­ди­те такой вывод:

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

PostgreSQL запу­ще­на и рабо­та­ет. Теперь давай­те посмот­рим, как рабо­та­ет Postgres и чем эта СУБД отли­ча­ет­ся от ана­ло­гич­ных систем, кото­рые вы, веро­я­то­но, исполь­зо­ва­ли ранее.

3: Роли и базы данных PostgreSQL

По умол­ча­нию PostgreSQL исполь­зу­ет так назы­ва­е­мые роли. Они чем-то похо­жи на учёт­ные запи­си поль­зо­ва­те­лей или груп­пы в Unix-подоб­ных систе­мах. Толь­ко систе­ма PostgreSQL не раз­ли­ча­ет поль­зо­ва­те­лей и груп­пы, она объ­еди­ня­ет их под поня­ти­ем «роли».

Во вре­мя уста­нов­ки PostgreSQL исполь­зу­ет модель аутен­ти­фи­ка­ции ident, кото­рая свя­зы­ва­ет роли PostgreSQL со стан­дарт­ны­ми поль­зо­ва­те­ля­ми Unix/Linux. Открыть суще­ству­ю­щую роль мож­но при помо­щи свя­зан­ной с ней учёт­ной запи­си Linux.

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

Суще­ству­ет несколь­ко спо­со­бов полу­чить доступ к PostgreSQL при помо­щи это­го аккаунта.

Пользователь postgres

Что­бы перей­ти в сес­сию поль­зо­ва­те­ля postgres, введите:

sudo -i -u postgres

Что­бы полу­чить доступ к команд­ной стро­ке, введите:

psql

Коман­да выпол­нит вход и откро­ет доступ к управ­ле­нию СУБД.

Что­бы закрыть команд­ную стро­ку PostgreSQL, наберите:

\q

Это вер­нёт вас в команд­ную стро­ку Linux. Что­бы вер­нуть­ся в сес­сию обыч­но­го поль­зо­ва­те­ля sudo, введите:

exit

Прямой доступ к командной строке PostgreSQL

Так­же мож­но полу­чить доступ к команд­ной стро­ке PostgreSQL, не изме­няя при этом сес­сии поль­зо­ва­те­ля. Для это­го нужен доступ к коман­де sudo.

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

sudo -u postgres psql

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

Что­бы закрыть эту команд­ную стро­ку, введите:

\q

4: Создание роли

На дан­ный момент в СУБД есть толь­ко одна роль, postgres. Что­бы создать новую роль, исполь­зуй­те коман­ду createrole. Что­бы создать роль в интер­ак­тив­ном режи­ме, добавь­те флаг —interactive.

В сес­сии поль­зо­ва­те­ля postgres создать ново­го поль­зо­ва­те­ля мож­но с помо­щью команды:

createuser --interactive

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

sudo -u postgres createuser --interactive

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

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

У это­го скрип­та есть мно­го раз­лич­ных фла­гов. Озна­ко­мить­ся со спис­ком доступ­ных опций мож­но на стра­ни­це мануала:

man createuser

5: Создание базы данных

По умол­ча­нию роль Postgres запра­ши­ва­ет одно­имен­ную базу дан­ных. Пото­му сле­ду­ет создать такую БД для ново­го пользователя.

Толь­ко что вы созда­ли ново­го поль­зо­ва­те­ля (в мануа­ле он услов­но назы­ва­ет­ся 8host). Для созда­ния БД исполь­зу­ет­ся коман­да createdb.

В сес­сии поль­зо­ва­те­ля postgres создать новую БД мож­но с помо­щью команды:

createdb 8host

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

sudo -u postgres createdb 8host

6: Аутентификация новой роли PostgreSQL

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

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

sudo adduser 8host

После это­го мож­но перей­ти в сес­сию ново­го поль­зо­ва­те­ля и под­клю­чить­ся к БД:

sudo - sudo -i -u 8host
psql

Или же под­клю­чить­ся к БД напрямую:

sudo -u 8host psql

Если все преды­ду­щие настрой­ки были выпол­не­ны пра­виль­но, коман­да авто­ма­ти­че­ски под­клю­чит­ся к БД.

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

psql -d postgres

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

\conninfo

You are connected to database «8host» as user «8host» via socket in «/var/run/postgresql» at port «5432».

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

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

Вы уже зна­е­те, как под­клю­чить­ся к систе­ме баз дан­ных 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)
);

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

Для при­ме­ра создай­те про­стую таблицу:

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, кото­рый ука­зы­ва­ет поряд­ко­вый номер запи­си. Этот стол­бец был огра­ни­чен по PRIMARY KEY, что зна­чит, что зна­че­ния долж­ны быть уни­каль­ны­ми и не долж­ны быть нулём.

Дли­на поля столб­цов equip_id и install_date не огра­ни­че­на; дело в том, что зада­вать дли­ну неко­то­рых столб­цов необя­за­тель­но, так как она авто­ма­ти­че­ски зада­ёт­ся типом данных.

Столб­цы type и color не могут быть пусты­ми. Стол­бец location огра­ни­чен восе­мью воз­мож­ны­ми зна­че­ни­я­ми. Стол­бец date ука­зы­ва­ет дату уста­нов­ки оборудования.

Про­смот­реть таб­ли­цу мож­но при помо­щи команды:

[codesyntax lang="php"]

[/codesyntax]

 

playground_equip_id_seq име­ет тип дан­ных sequence. Это пред­став­ле­ние типа дан­ных serial, уста­нов­лен­но­го для столб­ца equip_id. Этот тип опре­де­ля­ет сле­ду­ю­щий номер в последовательности.

Что­бы про­смот­реть таб­ли­цу вне это­го типа дан­ных, введите:

[codesyntax lang="php"]

[/codesyntax]

 

8: Добавление, запрос и удаление данных

Теперь попро­буй­те доба­вить в таб­ли­цу данные.

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

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;
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;
equip_id | type  | color  | location  | install_date
---------+-------+--------+-----------+--------------
2        | swing | yellow | northwest | 2018-08-16
(1 row)

Теперь зна­че­ния slide нет в таблице.

9: Добавление и удаление столбцов

PostgreSQL поз­во­ля­ет изме­нять коли­че­ство столб­цов таб­ли­цы после её создания.

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

ALTER TABLE playground ADD last_maint date;

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

SELECT * FROM playground;
equip_id | type  | color  | location  | install_date | last_maint
---------+-------+--------+-----------+--------------+------------
2        | swing | yellow | northwest | 2018-08-16   |
(1 row)

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

ALTER TABLE playground DROP last_maint;

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

10: Обновление данных в таблице

Суще­ству­ю­щие запи­си в таб­ли­це мож­но редактировать.

Что­бы обно­вить зна­че­ние запи­си, нуж­но запро­сить эту запись и ука­зать стол­бец, зна­че­ние кото­ро­го нуж­но изме­нить. Для при­ме­ра попро­буй­те обно­вить зна­че­ние запи­си swing; заме­ни­те yellow в столб­це color новым зна­че­ни­ем (напри­мер, 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)

Как види­те, зна­че­ние было успеш­но обнов­ле­но на red.