PostgreSQL — это мощная и широко используемая система управления базами данных (СУБД), отличающаяся высокой степенью масштабируемости и надежности. В этой статье мы рассмотрим основные шаги по установке и начальной настройке PostgreSQL на системе Debian, а также базовые операции с базами данных, ролями, резервным копированием и настройкой удаленного доступа через PgAdmin.
Чтобы установить PostgreSQL на Debian, выполните следующие шаги:
Обновите пакеты системы:
sudo apt update
Установите PostgreSQL:
sudo apt install postgresql postgresql-contrib
Пакет postgresql-contrib содержит дополнительные модули, которые могут быть полезны для расширения функциональности СУБД.
Запуск и проверка состояния службы PostgreSQL:
После установки PostgreSQL запускается автоматически. Чтобы проверить состояние службы, выполните команду:
sudo systemctl status postgresql
Запуск и остановка PostgreSQL вручную:
sudo systemctl start postgresql
Чтобы остановить службу:
sudo systemctl stop postgresql
После установки PostgreSQL вы можете создавать новые базы данных:
sudo -i -u postgres
psql
Теперь вы находитесь в консоли PostgreSQL.
Создание новой базы данных:
CREATE DATABASE имя_базы;
Роли в PostgreSQL управляют доступом к базам данных и их объектам. Создание новой роли и назначение прав выполняется следующим образом:
Создание роли:
CREATE ROLE имя_роли WITH LOGIN PASSWORD 'пароль';
Назначение роли прав администратора базы данных:
GRANT ALL PRIVILEGES ON DATABASE имя_базы TO имя_роли;
Назначение конкретных прав.
Например, для предоставления права на создание таблиц:
GRANT CREATE ON DATABASE имя_базы TO имя_роли;
Теперь, когда у вас есть база данных и роли, можно создать таблицы и определить первичные ключи:
Создайте таблицу:
CREATE TABLE имя_таблицы (
id SERIAL PRIMARY KEY,
имя_поля тип_данных,
...
);
Поле id автоматически увеличивается и служит первичным ключом.
Пример создания таблицы:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(50),
salary NUMERIC
);
Резервное копирование (бэкап) базы данных — важная задача для защиты данных от потери. В PostgreSQL для этого используется утилита pg_dump.
Создание резервной копии базы данных:
pg_dump имя_базы > имя_файла.sql
Восстановление базы данных из резервной копии:
psql имя_базы < имя_файла.sql
Если вы хотите создать резервную копию всех баз данных сразу, используйте pg_dumpall:
pg_dumpall > all_databases.sql
PgAdmin — это популярный графический инструмент для управления PostgreSQL. Для подключения к удаленному серверу необходимо выполнить несколько шагов.
Разрешите подключение по IP-адресу:
Откройте файл postgresql.conf и измените параметр listen_addresses:
sudo nano /etc/postgresql/XX/main/postgresql.conf
Замените XX на версию PostgreSQL (например, 15).
Найдите строку:
listen_addresses = 'localhost'
И измените её на:
listen_addresses = '*'
Разрешите доступ в pg_hba.conf:
sudo nano /etc/postgresql/XX/main/pg_hba.conf
Добавьте строку, разрешающую доступ для конкретного IP или диапазона IP:
host all all 0.0.0.0/0 md5
Перезапустите PostgreSQL для применения изменений:
sudo systemctl restart postgresql
20 самых больших таблиц.
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;
Размер всех таблиц.
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC;
Размер всех таблиц вместе с индексами.
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;
Размер базы.
SELECT pg_size_pretty( pg_database_size( 'zabbix' ) );
Размер таблицы.
SELECT pg_size_pretty( pg_total_relation_size( 'table' ) );
Размер всех баз в кластере.
select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;
Количество записей в таблице.
SELECT count(*) FROM my_table;
pgAdmin — это самая популярная и многофункциональная платформа администрирования и разработки с открытым исходным кодом для PostgreSQL.
Существует два режима работы pgAdmin: как самостоятельная программа, или работа в серверном режиме, с доступом через веб-браузер. На мой взгляд, второй вариант удобнее.
Рассмотрим ее установку на примере Ubuntu 22.04. Устанавливать будем в режиме web.
Для начала установите публичный ключ для репозитория:
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'
Теперь установим приложение, в режиме web:
sudo apt install pgadmin4-web
После установки необходимо сконфигурировать наш веб-сервер:
sudo /usr/pgadmin4/bin/setup-web.sh
После запуска скрипта система предложат создать логин и пароль, для доступа к сервису. После успешного завершения настройки pgAdmin будет доступен по адресу http://ip-server/pgadmin4