The first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.
Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.
To create a new database, in this example named mydb, you use the following command:
$createdb mydb
If this produces no response then this step was successful and you can skip over the remainder of this section.
If you see a message similar to:
createdb: command not found
then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:
$/usr/local/pgsql/bin/createdb mydb
The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.
Another response could be this:
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.
Another response could be this:
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "joe" does not exist
where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 21 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.
If you have a user account but it does not have the privileges required to create a database, you will see the following:
createdb: error: database creation failed: ERROR: permission denied to create database
Not every user has authorization to create new databases. If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs. If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. [1]
You can also create databases with other names. PostgreSQL allows you to create any number of databases at a given site. Database names must have an alphabetic first character and are limited to 63 bytes in length. A convenient choice is to create a database with the same name as your current user name. Many tools assume that database name as the default, so it can save you some typing. To create that database, simply type:
$createdb
If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb, you can destroy it using the following command:
$dropdb mydb
(For this command, the database name does not default to the user account name. You always need to specify it.) This action physically removes all files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.
More about createdb and dropdb can be found in createdb and dropdb respectively.
PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.
Содержание:
- Установка PostgreSQL 11 в Windows 10
- Доступ к PostgreSQL по сети, правила файерволла
- Утилиты управления PostgreSQL через командную строку
- PgAdmin: Визуальный редактор для PostgresSQL
- Query Tool: использование SQL запросов в PostgreSQL
Установка PostgreSQL 11 в Windows 10
Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.
В процессе установки установите галочки на пунктах:
- PostgreSQL Server – сам сервер СУБД
- PgAdmin 4 – визуальный редактор SQL
- Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
- Command Line Tools – инструменты командной строки
Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).
По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.
Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.
Доступ к PostgreSQL по сети, правила файерволла
Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.
Запустите командную строку от имени администратора. Введите команду:
netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432
- Где rule name – имя правила
- Localport – разрешенный порт
Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:
New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432
После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.
Совет. Для изменения порта в установленной PostgreSQL отредактируйте файл postgresql.conf по пути C:\Program Files\PostgreSQL\11\data.
Измените значение в пункте
port = 5432
. Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
chcp 1251 - Перейдите в каталог bin выполнив команду:
CD C:\Program Files\PostgreSQL\11\bin
Основные команды PostgreSQL:
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.
Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.
Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.
Query Tool: использование SQL запросов в PostgreSQL
Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.
- Выберите базу данных, в панели Tools откройте Query Tool
- Создадим таблицу сотрудников:
CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);
Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.
После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.
Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Введение
PostgreSQL известна своей мощностью, гибкостью и поддержкой широкого спектра функций, что делает ее идеальным выбором для разработчиков и администраторов баз данных. Независимо от того, предпочитаете ли вы работать с графическими инструментами или командной строкой, эта статья поможет понять основные шаги и даст практические рекомендации для создания и управления базами данных в PostgreSQL.
Подготовка инфраструктуры
Для начала работы потребуется PostgreSQL. Мы уже рассказывали в Академии Selectel, как развернуть БД самостоятельно на виртуальной машине. А в этой статье рассмотрим управление на основе услуги Облачные базы данных, когда пользователю предоставляется доступ только к БД (без доступа к ОС).
Подробнее, что такое облачные базы данных и в чем отличие от аренды целого сервера, рассказываем ниже в разделе «PostgreSQL на инфраструктуре Selectel».
Как создать БД в панели управления Selectel
Чтобы создать новый кластер БД Selectel, выполните следующее.
- Перейдите в панель управления.
- В левой части окна выберите Облачная платформа.
- В открывшемся окне перейдите на вкладку Базы данных.
- Нажмите Создать кластер.
- Выберите необходимую конфигурацию кластера и нажмите кнопку Создать кластер.
Когда кластер будет создан, он отобразится в той же вкладке Базы данных в статуте Active.
Для организации БД в новом кластере нужно сначала создать нового пользователя.
Примечание
Знакомая всем база данных Postgres и одноименный суперпользователь по умолчанию в Selectel недоступны во избежание инцидентов ИБ.
В своем кластере БД перейдите на вкладку Пользователи и нажмите кнопку Создать пользователя.
Заполните имя и пароль нового пользователя и нажмите Создать.
Перейдите на вкладку Базы данных.
Теперь, чтобы начать работу, создайте базу данных: выберите для нее имя, владельца и локали. К созданной базе вы можете добавить расширения и дать доступ новым пользователям.
Теперь можно подключиться к созданной базе данных и управлять ею любым удобным способом. Другие возможности кластера БД от 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.
Примечание
Если требуется добавить сервер PostgreSQL, созданный в кластере БД Selectel, необходимо перейти к своему кластеру, скопировать на вкладке Подключение его DNS-адрес и вставить в pgAdmin в поле Host name/address.
Например, master.6e0c4e3c-f801-445e-ba5c-63ba087f67a1.c.dbaas.selcloud.ru.
Почему мы рекомендуем использовать DNS, а не IP-адреса?
Наша система Master-Discovery реализована на базе доменных имен. Таким образом DNS-адреса соответствуют ролям нод и ведут на актуальные IP-адреса мастера и реплик. IP-адреса соответствуют конкретным нодам. В случае недоступности мастера одна из реплик возьмет на себя его роль. IP-адрес мастера изменится, и подключение по IP перестанет работать.
- Port — порт для подключения к серверу. Мы советуем использовать 5433 – порт для подключения через пулер соединений, который позволяет снизить нагрузку на PostgreSQL.
- Maintenance Database — имя базы данных, которое вы задали в панели управления.
- Username — имя пользователя из панели управления.
- Password — пароль пользователя PostgreSQL.
- Нажмите 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. Создадим таблицу в одной из схем новой БД.
Схема (schema) — это логическая структура, которая организует таблицы, представления, функции, индексы и другие объекты внутри одной базы данных. Они помогают разделять объекты, обеспечивают более четкую организацию и контроль доступа к данным. Каждая БД может содержать несколько схем, и каждая схема содержит свои объекты.
Разверните список 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
Подключение к БД
- Откройте терминал. Подключитесь к серверу PostgreSQL:
psql -h <IP_server> -p <port> -U <username> -d <db_name>
Примечание
Для подключения к PostgreSQL, расположенной на локальном устройстве, можно просто выполнить команду psql без параметров.
Базовые настройки через 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.
PostgreSQL — одна из самых популярных и мощных систем управления реляционными базами данных (СУБД). У неё открытый исходный код, плюс она абсолютно бесплатная. Система помогает создавать и хранить базы данных, а также работать с ними на языке SQL (Structured Query Language). Без СУБД это будет невозможно, поскольку технически БД — это просто файлы в определённом формате.
PostgreSQL используется как для небольших проектов, так и для крупных корпоративных приложений благодаря своей надёжности, расширяемости и поддержке обширного набора SQL-стандартов.
На первый взгляд кажется, что работа с PostgreSQL — непростая задача. Действительно, начинающим программистам синтаксис и команды системы могут быть непривычны, но со временем это чувство проходит. В этом руководстве мы собрали всё, что нужно для старта программирования в PostgreSQL — от установки до первых запросов.
В прошлом году PostgreSQL заняла четвёртую строчку в рейтинге самых популярных СУБД, уступив только Oracle, MySQL и Microsoft SQL Server. Вот несколько её преимуществ:
- Опенсорс. PostgreSQL — это СУБД с открытым исходным кодом. Это значит, что она доступна для использования, модификации и распространения без каких-либо лицензионных сборов.
- Надёжность. Эта система предоставляет несколько уровней защиты данных и механизмов восстановления после сбоев, что гарантирует минимальные потери данных и стабильную работу системы. Среди механизмов надежности — журналирование транзакций (Write-Ahead Logging, WAL) и поддержка транзакции ACID.
- Полная поддержка SQL-стандартов. PostgreSQL позволяет выполнять сложные запросы SQL, включая вложенные и агрегированные, поддерживает различные типы данных от базовых числовых и строковых типов до сложных JSON и XML, а также предлагает расширенные функции: оконные функции, общие табличные выражения (CTE) и множество встроенных функций для работы с датами, строками и географическими данными.
- Объектно-реляционная модель. СУБД поддерживает одновременно реляционный подход, то есть данные хранятся в виде записей, которые связаны друг с другом с помощью relations (отношения), и объектный подход, то есть данные представлены в виде объектов с атрибутами и методами, как в объектно-ориентированном программировании (ООП).
- Поддержка JSON и NoSQL. PostgreSQL предлагает средства для работы с JSON-документами, что позволяет использовать его как гибридную СУБД, объединяющую реляционные и NoSQL подходы. Она поддерживает два формата хранения JSON: JSON (текстовый) и JSONB (бинарный). JSONB оптимизирован для быстрого поиска и индексирования.
- Мощные средства индексирования. Среди типов индексов в этой системе управления БД — B-Tree, Hash, GIN и GiST, BRIN.
- Возможность использовать другие языки программирования. Postgre позволяет писать собственные функции и поддерживает больше языков программирования, чем остальные СУБД. Среди них — C, C++, Python, Java, PHP, Ruby, Lua и даже JavaScript (есть поддержка V8).
Кстати, на основе PostgreSQL создана отечественная СУБД Postgres Pro — в качестве замены Oracle. В рамках импортозамещения российским аналогом пользуются многие крупные компании и организации, например, Сбер, ФНС, Минфин, «Газпромнефть» и другие.
Как установить и запустить PostgreSQL
Перед тем как приступить к установке PostgreSQL, нужно определить, какая версия вам нужна. Каждая версия PostgreSQL включает обновления, улучшения и новые функции — выбор зависит от совместимости с вашими приложениями. Мы будем рассматривать установку и запуск на трёх операционных системах на примере последней 16 версии.
Для работы с PostgreSQL на Windows и macOS может понадобится графический интерфейс pgAdmin. В нем есть Query Tool, с помощью которого пишутся и выводятся SQL-запросы.
Установка и запуск на Windows
- Скачайте установочный файл с официального сайта PostgreSQL.
- Запустите установку. В появившемся мастере установки следуйте этим шагам:
- Выберите директорию установки и отметьте галочкой эти компоненты:
Компоненты для установки PostgreSQL
- Укажите данные и задайте пароль для суперпользователя (обычно это postgres):
Меню для ввода пароля
- Настройте порт для сервера (по умолчанию это 5432).
- Определите каталог для хранения данных.
3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.
Интерфейс pgAdmin
Установка на macOS
Установить СУБД на macOS можно тремя способами.
- С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.
- Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
- Через Homebrew. Homebrew — популярный пакетный менеджер для macOS. Убедитесь, что он установлен, и выполните команду:
После установки инициализируйте базу данных и запустите сервер:
initdb /usr/local/var/postgres
pg_ctl -D /usr/local/var/postgres start
Убедитесь, что сервер работает с помощью команды psql:
Установка на Linux
- Для установки на Ubuntu/Debian используйте команду в консоли:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Для CentOS/RHEL:
sudo yum install postgresql-server postgresql-contrib
На Ubuntu и Debian PostgreSQL автоматически инициализируется и запускается.
- На CentOS и RHEL выполните:
sudo postgresql-setup initdb
sudo systemctl start postgresql
На Linux тоже есть pgAdmin — его нужно скачать отдельно, однако он открывается не в приложении, а окне браузера. Однако большинство разработчиков, использующих эту операционную систему, работают с PostgreSQL в основном в консоли с помощью psql.
Основные команды SQL
SQL-команд очень много — о них можно почитать в официальной документации PostgreSQL. Но есть те, которыми вы будете пользоваться большую часть времени. Вот несколько основных команд:
CREATE — и все её вариации: CREATE DATABASE, CREATE TABLE, CREATE DOMAIN, CREATE USER и другие.
ALTER | DROP — изменить/удалить данные СУБД.
INSERT | UPDATE | DELETE/TRUNCATE — вставить/изменить/удалить данные в таблице.
SELECT, TABLE, WITH — получить строки из таблицы или представления.
Типы данных PostgreSQL
Прежде чем создавать базы данных и таблицы, нужно разобраться в основных типах данных этой СУБД. В PostgreSQL есть базовые и расширенные типы данных. Ниже рассмотрим основные (подробнее можно почитать в документации на русском языке).
Числовые типы
Числовые типы включают 2-, 4- и 8-байтные целые, 4- и 8-байтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью.
Числовые типы данных PostgreSQL
Денежные типы
Здесь все проще, тип всего один.
Денежные типы данных
Символьные типы
В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.
Символьные типы данных
Типы даты/времени
Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю.
Типы времени/даты
Логический тип
В СУБД есть стандартный SQL-тип boolean, который может иметь состояния «true», «false» и «unknown» (оно представляется SQL-значением NULL).
Логический тип данных
Создаем первую таблицу в PostgreSQL
Для начала нужно завести базу данных:
CREATE DATABASE mydatabase
Это создаст новую базу данных с именем mydatabase. Затем подключаемся к нашей БД:
Теперь можно создавать таблицу. Синтаксис выглядит следующим образом:
CREATE TABLE table_name (
column1_name data_type [column_constraint],
column2_name data_type [column_constraint],
...
table_constraint
);
где
table_name: название новой таблицы.
column1_name, column2_name: названия столбцов в таблице.
data_type: тип данных столбца (например, INTEGER, VARCHAR(100), DATE).
column_constraint: опциональные ограничения на уровне столбца (например, NOT NULL, UNIQUE).
table_constraint: опциональные ограничения на уровне таблицы (например, PRIMARY KEY, FOREIGN KEY).
Рассмотрим пример создания таблицы employees с различными типами данных и ограничениями:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE NOT NULL,
salary NUMERIC(10, 2),
department_id INTEGER REFERENCES departments(id)
);
После выполнения этого SQL-запроса в вашей БД появится таблица employees с указанными столбцами и ограничениями (PRIMARY KEY, UNIQUE, NOT NULL и так далее).
Чтобы удалить базу данных или таблицу, понадобится команда drop:
drop database <database_name>
drop table <table_name>
Модификация таблиц
Если вы допустили ошибку при оформлении таблицы, PostgreSQL предоставляет мощный набор команд для изменения ее структуры без необходимости удаления.
Добавление столбца
ALTER TABLE employees ADD COLUMN bio TEXT;
Эта команда добавляет новый столбец bio типа TEXT в таблицу employees.
Удаление столбца
ALTER TABLE employees DROP COLUMN bio;
Удаляет столбец bio из таблицы employees.
Изменение типа данных столбца
ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(150);
Изменяет тип данных столбца email на VARCHAR(150).
Получение информации о таблице
Команда выводит информацию о структуре таблицы employees, включая список столбцов, их типы данных и ограничения.
Изменение данных в таблице
PostgreSQL также имеет много функций для модификации строк и колонок в таблице. Ниже несколько примеров.
Добавление новой записи
INSERT INTO employees (last_name, email) VALUES ('Ivanov', 'ivanivanov@example.com');
Вставляет новую запись в таблицу employees с указанными значениями для last_name и email.
Изменение существующих данных
UPDATE employees
SET email = 'ivan_ivanov@example.com'
WHERE last_name = 'ivanov';
Обновляет адрес электронной почты пользователя с фамилией ivanov.
Удаление записей
DELETE FROM employees
WHERE last_name = 'ivanov';
Удаляет записи из таблицы employees, где last_name равен ivanov.
Запросы с командой SELECT
Выбор всех записей из таблицы:
Фильтрация данных:
SELECT * FROM employees
WHERE email LIKE '%@example.com';
Сортировка данных:
SELECT * FROM employees
ORDER BY hire_date
DESC;
Команда сортирует записи по столбцу hire_date в порядке убывания.
Группировка данных:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
Запрос вернет список департаментов и количество сотрудников в каждом из них.
Это наиболее простые запросы в PostgreSQL. Сложные будут включать, например, объединение данных через оператор JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN и другие).
Как оптимизировать выполнение запросов
С помощью индексов
Индексы играют важную роль в оптимизации выполнения запросов. PostgreSQL позволяет создавать индексы на одном или нескольких столбцах таблицы.
Создание индекса:
CREATE INDEX idx_username ON employees(last_name);
Этот индекс ускоряет поиск записей по last_name.
Использование уникальных индексов:
CREATE UNIQUE INDEX idx_unique_email ON employees(email);
Он обеспечивает уникальность значений в столбце email.
С помощью транзакций и блокировок
Транзакции позволяют группировать несколько операций в одну атомарную операцию. PostgreSQL также поддерживает механизмы блокировки для обеспечения согласованности данных.
Начало и завершение транзакции:
BEGIN;
UPDATE employees
SET email = 'ivan_updated@example.com'
WHERE last_name = 'ivanov';
COMMIT;
Откат транзакции:
BEGIN;
UPDATE employees
SET email = 'ivan_failed@example.com'
WHERE last_name = 'ivanov';
ROLLBACK;
PostgreSQL — мощная и многофункциональная СУБД. В этом руководстве мы рассмотрели основные аспекты установки, создания баз данных и выполнения простых SQL-запросов. На самом деле PostgreSQL не такая страшная, как кажется на первый взгляд. Главное — знать азы.
Инструкция по установке PostgreSQL и созданию демонстрационной базы данных для самостоятельного запуска запросов из курса
Если вы хотите не просто смотреть видео курса «Основы SQL», но и самостоятельно экспериментировать с SQL запросами и видеть результаты их выполнения на живой базе данных, то можете установить бесплатную систему управления базами данных PostgreSQL и создать в ней демонстрационную базу с данными, которые показаны в видео. Эта статья содержит подробные инструкции по установке и настройке.
Установка PostgreSQL
В учебном курсе «Основы SQL» для демонстрации работы SQL используется PostgreSQL. Сейчас это самая популярная из бесплатных систем управления базами данных. Все SQL запросы в курсе проверены на работоспособность именно в PostgreSQL. Однако большая часть запросов использует синтаксис стандарта ANSI SQL, поэтому они будут работать и в других системах, включая MySQL, Microsoft SQL Server и Oracle. Вы можете использовать любую систему управления базами данных, которая вам нравится, но я рекомендую PostgreSQL.
1. Загрузите PostgreSQL для вашей операционной системы на странице Downloads официального сайта. Я устанавливал на Windows, если вы используете другую операционную систему, то выбирайте соответствующие ссылки для загрузки. Примеры в курсе проверены на PostgreSQL 13, поэтому рекомендую устанавливать именно эту версию. Однако на предыдущих версиях, начиная с PosgreSQL 10, также все должно работать.
Инсталлятор для Windows и Mac OS загружается с сайта компании EDB, которая предоставляет платную поддержку для PostgreSQL. Однако PostgreSQL, которую вы установите с помощью этого инсталлятора от EDB, будет полностью бесплатной.
2. Запустите скачанный инсталлятор PostgreSQL.
Если вы устанавливаете PostgreSQL только для использования в курсе «Основы SQL», то можете оставить почти все настройки по умолчанию, кроме локали, для которой нужно выбрать «Russian, Russia» (русский язык в стране Россия).
Не забудьте запомнить или записать пароль пользователя postgres, он вам понадобится для подключения к базе и выполнения запросов!
После завершения установки инсталлятор предложит вам запустить Stack Builder для установки дополнительных утилит и компонентов. Этого можно не делать, просто снимите галочку в пункте «Stack Builder…» и нажмите кнопку «Finish».
На этом установка PostrgreSQL завершена.
Запуск и настройка pgAdmin
В курсе мы будем работать с PostrgreSQL через Web-интерфейс pgAdmin. Несмотря на Admin в названии, этот инструмент подходит не только администраторам, но и разработчикам.
pgAdmin устанавливается вместе с PostgreSQL. В Windows запустить pgAdmin можно в меню Пуск.
При первом запуске pgAdmin просит задать Master Password. Он будет использоваться для безопасного сохранения паролей к базам данных PosgreSQL, с которыми вы работаете через pgAdmin. Master Password можно выбрать любой, главное, запомните или запишите его.
Для удобства можно переключить интерфейс pgAdmin на русский язык. Для этого выберите меню File->Preferences, в появившемся окне в меню слева выберите Miscellaneous -> User Language, а затем в поле User Language справа «Russian».
Для вступления в силу настроек языка интерфейса нужно нажать кнопку «Save» и перезапустить pgAdmin.
После перезапуска выбирайте в левом меню Servers -> PostgreSQL, после чего pgAdmin запросит пароль пользователя postgres, который вы задали в процессе установки PostgreSQL. Введите этот пароль (можете поставить галочку «Save Password» чтобы pgAdmin запомнил пароль) и вы подключитесь к базе PostgreSQL.
Список существующих на сервере баз данных показывается в левом окне pgAdmin. Нас интересует база данных postgres, схема public и таблицы в ней. pgAdmin показывает много другой информации, не пугайтесь, если вы пока не понимаете, что это такое. Многое мы разберем в курсе, но преимущественно все это нужно только администраторам базы данных.
Пока в нашей базе нет никаких таблиц. Давайте создадим демонстрационные таблицы, которые используются в курсе, и заполним их данными.
Создание демонстрационной базы
1. Скачайте файл с демонстрационной базой данных курса «Основы SQL». Файл называется «sql_foundation» и имеет расширение .sql.
Файл содержит набор операторов SQL, которые создают используемые в курсе таблицы и заполняют их данными. Если вы пока не понимаете, что именно делают эти операторы, не расстраивайтесь. В курсе мы подробно рассмотрим работу каждого оператора и все будет понятно. На начальном этапе изучения курса необходимо просто запустить этот файл в pgAdmin.
2. Загрузите скачанный файл в pgAdmin. Для этого в меню pgAdmin выберите Инструменты->Запросник (в английском вариант Query Tool). В панели инструментов Запросника выберите кнопку открытия файла и в появившемся окне выберите путь к загруженному sql файлу с демонстрационной базой курса.
3. Запустите загруженный файл в pgAgmin. Для этого нажмите на кнопку запуска в панели инструментов Запросника или на клавишу F5.
4. Проверьте правильность выполнения запроса. В нижней части экрана pgAdmin, на закладке «Сообщения» должны появиться результаты выполнения.
Основное, на что нужно обратить внимание — это сообщение «Запрос завершен успешно». Если такое сообщение появилось, значит все хорошо.
Если вы запускаете файл создания демонстрационной базы курса первый раз, то будет выведено несколько Замечаний, что таблицы не существуют. Их можно игнорировать.
Также в левой части интерфейса pgAdmin появится информация о созданных таблицах.
Итак, демонстрационные таблицы для курса «Основы SQL» созданы и заполнены данными, можно запускать SQL запросы.
Запуск SQL запросов в pgAdmin
В pgAdmin для запуска SQL запросов используется уже знакомый нам инструмент Запросник. Давайте откроем окно Запросника и напишем самый первый SQL запрос из видео про Оператор SELECT.
Запрос пишется в среднем окне, закладка Query Editor. Для запуска запроса нажимаем F5 или кнопку Execute в панели инструментов Запросника.
Полученные в ходе выполнения запроса данные показываются в нижней части окна, на закладке «Результат».
Использование SQL Shell (psql)
Если вы предпочитаете работать в командной строке, а не в громоздких приложениях типа pgAdmin, то можете использовать консольную утилиту для работы с PosgreSQL: SQL Shell (ранее она называлась psql).
SQL Shell, также как и pgAdmin, устанавливается совместно с PostgreSQL. В Windows запустить SQL Shell можно через меню Пуск.
После запуска SQL Shell задаст ряд вопросов о параметрах подключения к PostgreSQL, можно оставить все значения по умолчанию (если вы не меняли настройки при установке). После этого введите пароль пользователя postgres и можете начинать работать с базой данных.
Если вам не повезло, то в Windows SQL Shell запустится с неправильной кодировкой для русского языка, будет выдавать предупреждение и некоторые русские буквы будут выводиться неправильно.
Чтобы решить эту проблему, нужно прописать правильную кодировку в файл для запуска SQL Shell. В моем случае файл называется «C:\Program Files\PostgreSQL\13\scripts\runpsql.bat». В этот файл нужно добавить строку:
chcp 1251
После добавления строки с установкой правильной кодировки файл runpsql. bat стал выглядеть следующим образом.
Сохраняем файл runpsql. bat, перезапускаем SQL Shell, после этого проблем с русской кодировкой быть не должно.
Для создания демонстрационной базы курса в SQL Shell выполните следующую команду:
\i 'C:/путь/к/файлу/sql_foundation.sql'\i означает import — загрузка файла sql в базу данных.
Обратите, пожалуйста, внимание:
1. Путь к файлу sql_foundation.sql нужно указывать в одиночных кавычках. Если будете использовать двойные кавычки, то не заработает.
2. В пути используются прямые слеши (/), как в Linux/Unix, несмотря на то, что мы работаем под Windows. Если писать обратные слеши (\), как это принято в Windows, то будет выдаваться ошибка «Permission denied». Не очень информативное поведение SQL Shel.
Запросы в SQL Shell можно писать прямо в командной строке.
Запрос в SQL Shell может занимать несколько строк, как в примере на рисунке. Запрос запускается после ввода ; (точка с запятой) или команды \g.
Результаты выполнения запроса показываются сразу же после него.
Полезная функция SQL Shell — история команд. Если нажимать стрелки вверх или вниз, то можно увидеть, какие команды были запущены ранее и повторить интересующую вас команду.
Итоги
В этой статье вы узнали, как установить PostgreSQL и как создать в нем демонстрационную базу для курса «Основы SQL». Также вы настроили и научились использовать два инструмента работы с PostgreSQL: pgAdmin и SQL Shell. Не обязательно использовать оба, выбирайте тот, который вам больше нравится: pgAdmin с Web-интерфейсом или командную строку SQL Shell.
В процессе экспериментов с SQL запросами в курсе вы можете случайно повредить созданную базу. В этом нет ничего страшного, базу данных можно легко пересоздать повторно запустив sql файл с операторами по созданию базы. При этом все имеющиеся таблицы будут удалены, заново созданы и заполнены данными. Все изменения, которые вы вносили, будут потеряны.