Как создать базу данных в PostgreSQL

Введение

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

Подготовка инфраструктуры

Для начала работы потребуется PostgreSQL. Мы уже рассказывали в Академии Selectel, как развернуть БД самостоятельно на виртуальной машине. А в этой статье рассмотрим управление на основе услуги Облачные базы данных, когда пользователю предоставляется доступ только к БД (без доступа к ОС).

Подробнее, что такое облачные базы данных и в чем отличие от аренды целого сервера, рассказываем ниже в разделе «PostgreSQL на инфраструктуре Selectel».

Как создать БД в панели управления Selectel

Чтобы создать новый кластер БД Selectel, выполните следующее.

• Перейдите в панель управления.
• В левой части окна выберите Облачная платформа.
• В открывшемся окне перейдите на вкладку Базы данных.
• Нажмите Создать кластер.
• Выберите необходимую конфигурацию кластера и нажмите кнопку Создать кластер.

Когда кластер будет создан, он отобразится в той же вкладке Базы данных в статуте Active.

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

В своем кластере БД перейдите на вкладку Пользователи и нажмите кнопку Создать пользователя.

Заполните имя и пароль нового пользователя и нажмите Создать.

Перейдите на вкладку Базы данных.

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

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

Кому подходит СУБД PostgreSQL на инфраструктуре Selectel

DBaaS (Database as a Service) — это облачное решение, которое позволяет пользователям управлять базами данных, не беспокоясь о настройке и обслуживании серверной инфраструктуры. Оно походит тем, кто хочет сосредоточиться на развитии продукта, а не на управлении инфраструктурой.

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

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

• Полностью управляемое решение. Selectel берет на себя все задачи по управлению инфраструктурой. Пользователю не нужно думать о настройке, резервировании и безопасности — все автоматизировано.
• Высокая отказоустойчивость. PostgreSQL на платформе Selectel разворачивается в надежных дата-центрах с обеспечением высокой доступности. Если один из узлов откажет, система продолжит работу без простоев, а резервные копии позволят быстро восстановить данные.
• Безопасность. Облачные базы данных соответствуют российским и международным стандартам: закону 152-ФЗ, приказу ФСТЭК № 21, PCI DSS, ISO 27001, 27017, 27018.
• Master Discovery. В услуге представлена встроенная система поиска актуального мастера на базе DNS. При аварийной ситуации и смене мастера система в реальном времени перенаправит запросы клиентского приложения к новому IP-адресу.
• Гибкость и масштабируемость. Вы можете гибкого изменять ресурсы базы данных в зависимости от потребностей приложения. При росте нагрузки просто поменяйте конфигурацию облачного сервера и количество реплик. Доступно как горизонтальное, так и вертикальное масштабирование.
• Интеграция с другими сервисами Selectel. DBaaS легко интегрируется с другими решениями, такими как выделенные серверы, DAVM, Kubernetes, сети и сервисы безопасности. Это позволяет создать полноценную и безопасную экосистему для работы с данными.
• Поддержка и документация. Selectel предоставляет детальную документацию и круглосуточную техническую поддержку, что упрощает процесс использования DBaaS и помогает быстро решать возникающие проблемы.

Как создать БД в графическом инструменте pgAdmin

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

Установка

Для начала скачайте установочный файл: перейдите на официальный сайт pgAdmin и выберите подходящую версию операционной системы (Windows, macOS или Linux).

После загрузки следуйте инструкции для вашей системы.

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

macOS. Откройте загруженный файл и перенесите pgAdmin в папку Applications. После этого pgAdmin можно запустить из папки приложений.

Linux. Для установки выполните несколько команд в терминале. Например, для Ubuntu:

• Установите открытый ключ для репозитория.

    curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

• Создайте файл конфигурации репозитория.

    sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

• Установите pgAdmin.

    sudo apt install pgadmin4

Запуск и подключение

После успешной установки запустите pgAdmin. При первом запуске вас попросят ввести пароль для администрирования интерфейса pgAdmin. Он будет использоваться для доступа к pgAdmin при каждом запуске.

Теперь, когда pgAdmin запущен, необходимо подключиться к серверу PostgreSQL.

В главном окне pgAdmin нажмите правой кнопкой мыши на Servers и выберите Register → Server...

В открывшемся окне создания нового подключения укажите:

• Name — имя для вашего сервера. Например, Selectel.

Перейдите на вкладку Connection и заполните следующие поля.

• Host name/address — адрес сервера PostgreSQL. Если вы работаете локально, используйте localhost.

• Port — порт для подключения к серверу. Мы советуем использовать 5433 – порт для подключения через пулер соединений, который позволяет снизить нагрузку на PostgreSQL.
• Maintenance Database — имя базы данных, которое вы задали в панели управления.
• Username — имя пользователя из панели управления.
• Password — пароль пользователя PostgreSQL.

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

Если при попытке добавления нового сервера вы получаете ошибку вида:

    Unable to connect to server: connection failed: connection to server at "127.0.0.1" port 5433 failed: FATAL: password authentication failed for user "postgres" connection to server at "127.0.0.1", port 5433 failed: FATAL: password authentication failed for user "postgres"

… подключитесь к PostgreSQL через терминал и сбросьте пароль для указанного пользователя:

    sudo -i -u postgres 

    psql

    \password postgres

    [Указать новый пароль]

    [Повторить ввод нового пароля]

Создание БД

После подключения к серверу PostgreSQL через pgAdmin для создания новой базы данных выполните следующие шаги:

В левом меню pgAdmin найдите подключенный сервер, нажмите на него правой кнопкой мыши и выберите Create → Database…

В открывшемся окне заполните поля:

• Database. Введите название для новой базы данных, например, database_from_pgadmin.
• Owner. Выберите пользователя, который будет владельцем базы данных. По умолчанию это postgres, но можно выбрать любого другого пользователя.

Нажмите Save, чтобы создать базу данных.

Создание таблицы

Теперь ваша база данных PostgreSQL отображается в списке pgAdmin. Создадим таблицу в одной из схем новой БД.

Разверните список Servers, выберите нужный сервер и базу данных, в которой хотите создать таблицу. Например, Databases → selectel_db → Schemas → public → Tables.

Нажмите правой кнопкой мыши на Tables в дереве объектов и выберите Create → Table…

Заполните следующие поля.

• Name — название таблицы. Например, employees.
• Owner — пользователь, который будет владельцем таблицы.
• Schema — обычно используется схема public, но если у вас есть другие схемы, можете выбрать нужную.

Перейдите на вкладку Columns для определения столбцов таблицы. Нажмите на кнопку +, чтобы добавить новый столбец, и укажите следующие параметры.

• Name — имя столбца, например, id, first_name, last_name, email.
• Data type — тип данных столбца. Например, serial, varchar, integer, и т.д.
• Primary Key? — если это ключевой столбец, например, id, поставьте галочку напротив чекбокса.
• Not NULL? — если столбец должен быть обязательным, отметьте этот параметр.

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

Как создать БД в командной строке

Для тех, кто предпочитает работу через терминал, PostgreSQL предоставляет утилиту командной строки — psql.

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

    sudo apt install postgresql postgresql-contrib

Подключение к БД

1. Откройте терминал. Подключитесь к серверу PostgreSQL:

    psql -h <IP_server> -p <port> -U <username> -d <db_name>

Базовые настройки через psql

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

Параметры конфигурации PostgreSQL

PostgreSQL позволяет настраивать различные параметры конфигурации: кодировку данных, язык сообщений, уровень логирования и многое другое. Для этого используются команды языка SQL. Чтобы изменить текущие настройки сеанса, используйте команду SET. Например, для изменения языка сообщений на русский:

    SET lc_messages = 'ru_RU.UTF-8';

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

    SET client_encoding = 'UTF8';

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

    SHOW lc_messages;

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

Пользователи и роли в PostgreSQL играют ключевую роль в управлении доступом к базе данных.

Чтобы добавить нового пользователя в PostgreSQL, введите команду CREATE ROLE или CREATE USER. Например:

    CREATE USER myuser WITH PASSWORD 'mypassword';

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

    ALTER USER myuser CREATEDB;

Получение справки и помощи в psql

Иногда в процессе работы с PostgreSQL бывает нужно быстро получить информацию о доступных командах или синтаксисе. В этом случае утилита psql предоставляет справочную систему.

Команда \help в psql выводит справку по командам SQL. Например, чтобы узнать больше о команде CREATE DATABASE, выполните:

    \help CREATE DATABASE

Помимо SQL-команд, psql поддерживает множество встроенных команд, которые начинаются с обратного слэша (\). Вот некоторые из наиболее полезных:

• \l — выводит список всех баз данных;
• \dt — отображает список всех таблиц в текущей базе данных;
• \du — выводит список всех пользователей и ролей;
• \c <db_name> — подключение к конкретной базе данных;
• \dn — выводит список всех схем в базе данных.

Чтобы узнать больше о встроенных командах, используйте \? в терминале psql. Например, чтобы увидеть список всех доступных команд psql:

    \?

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

Теперь перейдем к созданию новой базы данных. В PostgreSQL для этого используется команда CREATE DATABASE:

    CREATE DATABASE <db_name>;

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

    \c <db_name>

Работа с таблицами 

Создание таблицы: CREATE TABLE

Теперь, когда вы подключены к БД, можно приступить к созданию таблицы. В PostgreSQL таблицы создаются с помощью команды CREATE TABLE.

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

    CREATE TABLE IF NOT EXISTS employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    hire_date DATE
);

В этом примере создается таблица employees с пятью столбцами:

• employee_id — уникальный идентификатор сотрудника, создается автоматически при помощи типа данных SERIAL;
• first_name и last_name — текстовые поля для хранения имени и фамилии;
• department — строка, обозначающая отдел, к которому относится сотрудник;
• hire_date — дата приема на работу.

Для проверки, что таблица успешно создана, можно использовать команду:

    \d employees

Эта команда выведет описание таблицы employees, включая ее структуру и типы данных.

Удаление таблицы: DROP TABLE

Для удаления таблицы из базы данных PostgreSQL используется SQL-запрос:

    DROP TABLE <db_name>;

Создание резервной копии БД: pg_dump, pg_restore

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

Создание

    pg_dump -U <username> -h <host> -p <port> -d <database_name> -F c -f /path/to/backup_file.backup

• -U <username> — имя пользователя PostgreSQL;
• -h <host> — адрес сервера базы данных, например, localhost;
• -p <port> — порт для подключения к серверу;
• -d <db_name> — имя базы данных, которую нужно сохранить;
• -F c — формат архива, в данном случае c = custom;
• -f /path/to/backup_file.backup — путь и имя файла для сохранения резервной копии.

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

    pg_dump -U postgres -d employees -F c -f ~/backups/mydb_backup.backup

Восстановление

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

Сперва создайте новую базу данных — в нее будут восстанавливаться данные. Для этого используйте команду createdb:

    createdb -U <username> -h <host> -p <port> <new_db_name>

Затем восстановите данные из резервной копии. В этом поможет команда pg_restore:

    pg_restore -U <username> -h <host> -p <port> -d <new_db_name> /path/to/backup_file.backup

И в конце убедитесь, что данные восстановлены корректно, проверив таблицы в новой БД.

Заключение

В статье подробно рассказали, как создать базу данных в PostgreSQL различными способами: в панели управления Selectel, с помощью графического интерфейса pgAdmin и через командную строку утилитой psql.

Узнать больше о работе с PostgreSQL можно в одном из наших курсов в Академии Selectel.