Jump to content
TacTics
TacTics

Работа с базой данных PostgreSQL

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

В этой статье мы разберемся, как работать с PostgreSQL. В качестве примера я буду использовать Ubuntu 18.04.

Установка PostgreSQL

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

sudo apt-get update

Для выполнения команды нужны права суперпользователя. Так что придется вспомнить пароль.

После установки апдейтов инсталлируем PostgreSQL:

sudo apt-get install postgresql postgresql-contrib

PostgreSQL установится вместе с пакетом contrib, в котором содержится дополнительная функциональность, а также утилиты для работы СУБД.

Чтобы убедиться, что все работает, проверим версию:

postgres --version

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

Настройка PostgreSQL

Работать с PostgreSQL мы будем через терминал с помощью встроенной утилиты psql. Запускаем ее следующей командой:

psql postgres

Можно установить сторонние инструменты для администрирования PostgreSQL, но в этом мало смысла — psql справляется со всеми основными задачами.

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

\help

Если нужна справка по конкретной команде, пишем: 

\help [имя команды]

Выйти из psql можно командой \q.

Управление пользователями

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

По умолчанию была создана роль postgres. Давайте создадим еще одну роль. Для этого из консоли системы выполняем команду: 

createuser -P --interactive

Система запросит имя для новой роли, пароль, а также позволит настроить привилегии — например, нужно ли давать права суперпользователя или разрешать создавать другие роли и базы данных.

Если вы уже зашли в psql, то создать новую роль можно командой:

CREATE ROLE имя_новой_роли WITH LOGIN CREATEDB CREATEROLE; // В конце обязательно ставим ;

Затем задаем пароль:

\password имя_роли

Вывести список всех ролей можно командой /du. Кроме имен отобразятся привилегии каждого роли.

Чтобы закрыть список ролей, выполняем команду q

Для удаления пользователя выполняем команду: 

DROP ROLE имя_роли;

Это можно также сделать из консоли системы с помощью команды:

drop user имя_роли

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

ALTER USER имя_роли WITH PASSWORD 'новый_пароль';

Эта операция сохраняется в файле .psql_history вместе с паролем, который не будет зашифрован. В качестве дополнительной меры безопасности эту запись рекомендуется удалить. Файл обычно находится в директории /var/lib/postgresql. 

Работа с базами данных в PostgreSQL

Создать базу данных из консоли можно следующей командой:

createdb имя_БД

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

CREATE DATABASE имя_БД;

Чтобы посмотреть список всех БД, выполняем команду \l.

Для удаления базы данных используется та же команда, что и для удаления роли — drop. В терминале системы синтаксис будет таким:

drop database имя_БД

В клиенте psql синтаксис похожий:

DROP DATABASE имя_БД;

Подключение к базе данных

По умолчанию psql подключается от имени текущего пользователя Linux к БД с таким же названием. Если эти данные совпадают, достаточно выполнить запуск самого терминального клиента:

psql

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

\conninfo

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

psql -d имя_БД

Если имя роли не совпадает с именем пользователя в Linux, прописываем дополнительные параметры. 

Имя роли и название БД совпадают:

psql -U имя_роли -h localhost -W

Название базы данных отличается от имени роли:

psql -U имя_роли -d имя_базы -h localhost -W // Разница в том, что явно указано название БД

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

\c имя_БД

Создание резервной копии и восстановление из бэкапа

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

pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_БД

Чтобы было проще разобраться, рассмотрим каждый параметр:

  • хост – сервер, на котором располагается БД. Например, можно указать localhost, домен, IP-адрес.
  • имя_роли имя пользователя PostgreSQL, под которым мы работаем с базой данных.
  • формат_дампа формат, в котором дамп сохранится на сервере. Доступны следующие форматы: c (custom) архив .tar.gz, t (tar) архив .tar, p (plain) текст без сжатия, обычно .sql.
  • путь_к_дампу путь, по которому будет сохранена резервная копия.
  • имя_БД название БД, для которой будет создана резервная копия.

Выглядит это примерно так:

pg_dump -h localhost -U mybase -F c -f /home/user/backups/dump.tar.gz mybase

Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).

Восстановление из резервной копии выполняется аналогичным образом: 

pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу

Параметры похожие, отличия минимальные. Важно знать хост, помнить формат и путь к бэкапу.

Мы разобрались с основными действиями и настройками PostgreSQL. На этом все!



User Feedback

Recommended Comments

There are no comments to display.



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Privacy Policy We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.