Создать базу данных postgresql debian
Обновлено: 08.07.2024
Реляционные базы данных - камень преткновения во всех приложениях, которые активно работают с данными. Без них не обходится ни один онлайн магазин, ни запуск ракеты. Одним из примеров достойных уважения БД является PostgreSQL. Она построена по основным стандартам SQL, применяет ACID транзакции, поддерживает внешние ключи и виды (views), а так же все еще в активной стадии разработки.
Вы не найдете лучшего кандидата чем Debian 8 (кодовое имя Jessie) если для вашего приложения требуется стабильная среда и легкое администрирование. Эта ОС возможно работает немного медленнее своих конкурентов, но её стабильность легко восполняет этот недостаток. А пара Debian и PostgreSQL будет лучшей средой для приложений, которые активно работают с данными. В этой статье я покажу как установить PostgreSQL на Debian 8 и начать пользоваться преимуществами этой пары.
Прежде чем начать
Сначала обновим репозитории пакетов на Debian:
Существует несколько пакетов, чье имя начинается с postgresql:
При вопросе нажмите Y для продолжения установки. В результате вы получите все нужные пакеты.
Проверим установку
Для того, чтобы проверить, что всё установилось как положено, выполним следующую команду:
Отлично! PostgreSQL установлен и запущен.
Доступ к БД PostgreSQL
По-умолчанию PostgreSQL создает на Debian пользователя и БД по имени postgres . Для начала вам потребуется войти под этим пользователем, выполнив следующую команду от имени root (sudo не сработает):
Теперь следует запустить консоль PostgreSQL:
Вот и все! Вы вошли в PostgreSQL и должны увидеть следующее:
Для выхода из консоли введите \q.
Создание новых ролей
По-умолчанию PostgreSQL применяет идею ролей для помощи в аутентификации и авторизации. По идеи, подход очень схож с аккаунтами на Unix системах, но PostgreSQL не отличает пользователей и группы, а использует более гибкий подход - роли. При установке PostgreSQL использует аутентификацию ident , то есть PostgreSQL сопоставляет роли с существующими аккаунтами в Unix/Linux системе. Если роль существует в PostgreSQL, то человек может зайти под ней, введя свои данные из ОС.
Во время установки создается аккаунт postgres , который ассоциируется в ролью по-умолчанию для Postgres. Для создания дополнительных ролей следует обратиться к команде createuser . Помните, что команда должна выполняться от имени postgres , не в терминале PostgreSQL консоли:
Это всего лишь интерактивный скрипт, который выполняет соответствующие команды PostgreSQL по вашим требованиям. Он задаст вам ряд вопросов: название роли, выдать ли ей права суперпользователя, дать ли доступ к созданию новых БД и новых ролей. На странице man вы найдете более подробную информацию:
Создание новой базы данных
Для создания соответствующей БД выполните следующую команду от пользователя postgres :
Будет создана новая БД test1 .
Подключение к PostgreSQL под новым пользователем
Допустим у вас есть аккаунт Linux под именем test1 , ролью под таким же именем и соответствующая БД. Перейдем под нового пользователя:
Затем подключаемся под именем test1 к БД test1:
Вы должны увидеть приглашение к вводу от PostgreSQL под именем test1 .
Создание и удаление таблиц
После того как мы познакомились с процессом подключения к БД, перейдем к базовым задачам. Для начала создадим таблицу для хранения данных, в которой будут поля для описания оборудования на площадке. Базовый синтаксис для такой таблицы выглядит следующим образом:
Как видите, мы задаём имя таблицы и определяем поля в ней, их тип и максимальное значение. Так же допускается задавать ограничения для каждого поля.
Для наших целей достаточно следующей таблицы:
Мы создали таблицу содержащую информацию об оборудовании на игровой площадке. Каждая запись содержит ID оборудования - серийный тип. Такой тип данных имеет свойства автоинкремента. Мы задали это поля в качестве первого ключа, то есть оно не может повторяться и иметь значение null .
Двум полям мы не задали длину значения. Для некоторых полей не обязательно указывать это значение, так как тип данных подразумевает его.
Затем мы определяем поля type и color , оба они не могут иметь пустое значение. После чего мы задаем поле location , которое может принимать одно из восьми возможных значений. Последнее поле - дата добавления оборудования в БД. Чтобы вывести все таблицы в БД, выполните \dt в консоли:
Как видите, мы создали таблицу playground .
Добавление, запрос и удаление данных из таблицы
Итак, у нас есть таблица, надо внести в неё данные. Добавим горку и качели. Достаточно указать имя таблицы, перечислить нужные столбцы и их значения. Сделаем это следующим образом:
Наверняка вы обратили внимание на несколько моментов. Первый - имена полей должны быть указаны без кавычек, что не скажешь о значениях этих полей. Второй момент - мы не указываем значение для поля equip_id . Так как оно генерируется автоматически. Теперь просмотрим результат команды:
Мы видим, что поле equip_id заполнилось автоматически, а все остальные поля соответствуют тем данным, которые мы вводили ранее. Если горка сломается, то мы просто удалим её из этой таблицы:
Снова прочтём данные из таблицы:
Как видите, запись удалена:
Полезные команды
Приведу список часто используемых команд, чтобы вы могли иметь большее представление о БД:
- \?: Выводит полный список команд psql .
- \h: Вывод помощи по отдельным командам.
- \q: Выход из psql и возвратв терминал Linux.
- \d: Вывод доступных таблиц, видом и функций в текущей БД.
- \du: Вывод доступных ролей.
- \dp: Вывод списка доступных прав.
- \dt: Список таблиц
- \l: Список БД
- \c: Подключение к другой БД. Следует указат имя БД.
- \password: Изменение пароля пользователя, указанного как аргумент команды.
- \conninfo: Информация о текущей БД и подключении.
Этого набора должно хватить для ориентировании в PostgreSQL.
Заключение
В итоге вы должны получить полностью рабочую PostregSQL на Debian системе. Поздравляю! А теперь взглянем на набор документации:
PostgreSQL имеет обширную и хорошую документацию по установке и использованию, используйте её как первоисточник документации по PostgreSQL. Данная страница описывает основные отличия установки PostgreSQL на Debian от общей установки PostgreSQL.
Установка
Требуемые пакеты: postgresql postgresql-client
- postgresql-doc - документация.
- pgadmin3 - PostgreSQL administration GUI.
- phppgadmin - веб-интерфес для утилит администратора
Обратите внимание, что процедурные языки устанавливаются отдельно (plpgsql устанавливается по-умолчанию). Выполните поиск по базе данных пакетов, для вывода списка возможностей PostgreSQL:
Пользовательский доступ
По умолчанию пользователя и базу данных называют postgres.
Вы можете подключиться к базе данных, используя следующую команду:
Новый Пользователь и база данных
Создать системного пользователя можно используя adduser (пропустите этот шаг, чтобы использовать существующую учетную запись):
Подключаемся к БД и создаем нового пользователя и базу данных:
Подключаемся пользователем mypguser к новой БД:
Если вы получаете ошибки, как например эта:
то отредактируйте файл pg_hba.conf в /etc/postgresql/X.Y/main/pg_hba.conf
Документация
Для ознакомления с представлением PostgreSQL в архитектуре Debian, инструкциями по быстрому запуску, ссылками на программы и страницами руководств, обратитесь к /usr/share/doc/postgresql-common/README.Debian.gz.
Учебные файлы
Документация PostgreSQL ссылается на учебник, который входит в пакет postgresql-doc. За более подробной информацией обратитесь к /usr/share/doc/postgresql-doc-[version]/tutorial/README.
Список существующих кластеров баз данных
Используйте команду pg_lsclusters для проверки установленных кластеров, а также для получения некоторых основных сведений, такие как: версия (основная версия), имя, порт, статус (вкл. или выкл.), владелец, директория данных и файл лога.
Замена pg_ctl
pg_ctl это команда для управления PostgreSQL из командной строки, которая используется для управления базой данных. Debian имеет Perl-обёртку для pg_ctl, которая вызывается из /usr/bin/pg_ctlcluster. Используйте pg_ctlcluster, когда вам потребуется использовать pg_ctl. Файл настроек находится в /etc/postgresql/[version]/[cluster]/pg_ctl.conf
Debian устанавливает SysV-init-совместимый (стандартный) скрипт автозагрузки в /etc/init.d/postgresql-[version]. Скрипт может использоваться для запуска, остановки, перезапуска и перезагрузки настроек без перезапуска системы. Скрипт вызывает pg_ctlcluster через внутренние вызовы.
Расположение файлов
Debian разделяет файл настроек базы данных от файлов базы данных, в то время как, общая установка PostgreSQL, напротив, ставит всё в один и тот же каталог. Обратите внимание, что Debian позволяет нескольким кластерам и даже различным версиям PostgreSQL находится на одном хосте.
Конфигурационные файлы: /etc/postgresql/[version]/[cluster]/
Бинарные: /usr/lib/postgresql/[version]
Файлы данных: /var/lib/postgresql/[version]/[cluster]
Log-файлы: При установки PostgreSQL создает директорию для лог-файлов /var/log/postgresql/. При запуске ядра СУБД создает файл журнала с именем postgresql-[version]-[cluster].log.
Изменение значений по-умолчанию в установке на Debian
Внимание!
Предыдущая операция, как очевидно, удалит всё, что у вас было в кластере баз данных. Выполняйте эту операцию сразу после установки базового пакета. Рассмотрите руководство PostgreSQL, если вам нужно изменить стандарт для существующей базы данных (это не стандартная операция).
PostgreSQL (иногда именуемая Postgres) - это самая продвинутая, универсальная, объектно-ориентированная система баз данных с открытым исходным кодом с проверенной архитектурой, которая работает на всех основных операционных системах. Это высокопроизводительная, стабильная, масштабируемая и расширяемая система баз данных, которая обеспечивает хорошую целостность данных и поддерживает мощные дополнения.
Важно то, что PostgreSQL позволяет вам определять свои собственные типы данных, добавлять пользовательские функции, даже писать код на разных языках программирования, таких как C/C++, Java, и т. д., без перекомпиляции вашей базы данных. PostgreSQL используется известными техническими компаниями, такими как Apple, Fujitsu, Red Hat, Cisco, Juniper Network и т. д. В этой статье мы покажем вам, как установить, защитить и настроить сервер баз данных PostgreSQL 11 Debian 10.
Установка PostgreSQL на Debian 10
Чтобы установить PostgreSQL Debian 10, используйте менеджер пакетов APT, который установит и сервер и клиент PostgreSQL 11.
apt install postgresql-11 postgresql-client-11
В Debian, база данных postgres инициализируется сразу после завершения установки пакета, как показано на следующем снимке.
Чтобы проверить, действительно ли база данных Postgres инициализирована, вы можете использовать утилиту pg_isready, которая проверяет статус соединения сервера PostgreSQL 11 следующим образом:
Кроме того, в systemd служба Postgres также запускается автоматически и включается при загрузке системы. Чтобы убедиться, что служба работает нормально, выполните следующую команду.
systemctl status postgresql
Далее следуют другие полезные команды systemctl для управления службой Postgres в systemd.
systemctl start postgresql
systemctl restart postgresql
systemctl stop postgresql
Чтобы перечитать конфигурацию после перезагрузки выполните:
systemctl reload postgresql
Настройка PostgreSQL
По умолчанию Postgres использует концепцию ролей для управления разрешениями доступа к базе данных, а роли базы данных концептуально полностью отделены от пользователей операционной системы. Роль может быть пользователем или группой, а роль с правом входа в сиcтему называется пользователем.
Только что инициализированная система всегда содержит одну предопределенную роль, называемую postgres, и имеет то же имя, что и учетная запись пользователя операционной системы, называемая postgres, которая используется для доступа к psql (оболочка Postgres) и другим программам баз данных.
Учетная запись пользователя системы Postgres не защищена паролем, для ее защиты вы можете создать пароль с помощью утилиты passwd.
Кроме того, роль Postgres (или, если угодно, суперпользователь базы данных) по умолчанию не защищена. Вам также необходимо защитить ее паролем. Теперь переключитесь на учетную запись пользователя системы postgres и роль postgres (не забудьте установить надежный и безопасный пароль), как показано ниже.
su - postgres
psql -c "ALTER USER postgres WITH PASSWORD 'новый_пароль';"
Далее выйдите из учетной записи postgres, чтобы продолжить настройку. Основной файл конфигурации Postgres находится по пути /etc/postgresql/11/main/postgresql.conf. В дополнение к этому файлу Postgres использует два других файла конфигурации, которые управляют аутентификацией клиента.
Аутентификация клиента контролируется файлом конфигурации /etc/postgresql/11/main/pg_hba.conf. Postgres предоставляет множество различных методов аутентификации клиента, включая аутентификацию на основе пароля. Клиентские соединения аутентифицируются на основе адреса хоста клиента, базы данных и пользователя.
Если вы решите использовать аутентификацию на основе пароля, вы можете воспользоваться одним из этих методов: md5 или пароль, работающий аналогичным образом, за исключением способа передачи пароля по соединению, а именно, хэширования MD5 и открытого текста соответственно.
Использование аутентификации по паролю md5 предотвращает перехват паролей хакерами и позволяет избежать хранения паролей на сервере в виде простого текста. Парольный метод можно использовать только в том случае, если соединение защищено шифрованием SSL.
В этом руководстве мы покажем, как настроить аутентификацию по паролю md5 для аутентификации клиента.
Найдите следующую строку и измените метод аутентификации на md5, как показано на скриншоте.
local all all md5
Сохраните изменения в файле и выйдите из него. Затем примените последние изменения, перезапустив службу Postgres следующим образом.
systemctl restart postgresql
Создание новой базы данных и роли базы данных/пользователя в PostgreSQL
В этом последнем разделе мы покажем, как создать нового пользователя базы данных и роль базы данных для управления им. Сначала переключитесь на учетную запись postgres и откройте оболочку Postgres следующим образом.
Чтобы создать базу данных с именем «test_db», выполните следующую SQL команду:
CREATE DATABASE test_db;
Затем создайте пользователя базы данных (роль с правом входа), который будет управлять новой базой данных:
CREATE USER test_user PASSWORD ‘новый_пароль’;
На этом настройка postgresql Debian 10 практически завершена. Чтобы подключиться к test_db от имени пользователя test_user, выполните:
psql -d test_db -U test_user
Для получения дополнительной информации см. Документацию PostgreSQL 11.
Выводы
На этом, пока что, все! В этой статье мы показали, как делается установка Postgresql Debian 10, а также как защитить и настроить полученный сервер баз данных. Есть ли у вас какие-либо вопросы или мысли, которыми хочется поделиться? Используйте форму комментариев ниже.
Нет похожих записей
Статья распространяется под лицензией Creative Commons ShareAlike 4.0 при копировании материала ссылка на источник обязательна.
Начало работы с PostgreSQL 10
В данной статье я расскажу о базовых операциях PostgreSQL 10: основные команды в оболочке psql, создание и удаление базы данных, работа с табличными пространствами, создание и удаление ролей, создание простых таблиц, работа со схемами и т.д.
Исходные данные: Debian 9.3 (Stretch), PostgreSQL 10.2
Задача: Научиться базовым навыкам работы с PostgreSQL
Давайте зайдем под пользователем postgres, запустим оболочку psql и попробуем поработать:
Перед нами открылась оболочка с приглашением ввести команды:
Первым делом давайте посмотрим информацию о версии PostgreSQL:
Давайте пройдемся по списку наиболее нужных команд:
1. Список баз данных.
Список БД можно посмотреть 3-мя способами:
Ключ -l командной строки приложения psql, метакоманда \l или select запрос к системному каталогу pg_database, например
2. Создание баз данных.
Базу данных можно создать 2-мя способами: Через утилиту createdb или с помощью команды CREATE DATABASE имя;
Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет SQL-команду CREATE DATABASE.
Пример этих команд и вывод списка БД:
или из командной оболочки под пользователем postgres:
Просмотр флагов datistemplate и datallowconn:
3. Удаление баз данных.
Тут так же как и при создании, есть 2 подхода: Через утилиту dropdb или с помощью команды DROP DATABASE имя;
Пример:
Пример этих команд и вывод списка БД:
4. Работа с табличными пространствами.
Табличные пространства в PostgreSQL позволяют администраторам организовать логику размещения файлов объектов базы данных в файловой системе. К однажды созданному табличному пространству можно обращаться по имени на этапе создания объектов.
Табличные пространства позволяют администратору управлять дисковым пространством для инсталляции PostgreSQL. Это полезно минимум по двум причинам. Во-первых, это нехватка места в разделе, на котором был инициализирован экземпляр Pg или кластера Pg и невозможность его расширения. Табличное пространство можно создать в другом разделе и использовать его до тех пор, пока не появится возможность переконфигурирования системы. Во-вторых, табличные пространства позволяют администраторам оптимизировать производительность согласно бизнес-процессам, связанным с объектами базы данных. Например, часто используемый индекс можно разместить на очень быстром и надёжном, но дорогом SSD-диске. В то же время таблица с архивными данными, которые редко используются и скорость к доступа к ним не важна, может быть размещена в более дешёвом и медленном хранилище.
Для создания табличного пространства используется команда CREATE TABLESPACE.
Каталог нового табличного пространства должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен PostgreSQL.
Создаем каталог и выставляем права:
Подключаемся к Pg и создаем новое табличное пространство:
Посмотреть список табличных пространств можно 2-мя способами: метакоманда \db или \db+ или select запрос к системному каталогу pg_tablespace;
Создавать табличное пространство должен суперпользователь базы данных, но после этого можно разрешить обычным пользователям его использовать. Для этого необходимо предоставить привилегию CREATE на табличное пространство.
Таблицы, индексы и целые базы данных могут храниться в отдельных табличных пространствах. Для этого пользователь с правом CREATE на табличное пространство должен указать его имя в качестве параметра соответствующей команды.
Например, далее создаётся база данных в табличном пространстве fast:
В колонке Tablespace мы видим что база test2 находится в новом табличном пространстве fast.
Табличное пространство, связанное с базой данных, также используется для хранения её системных каталогов. Более того, это табличное пространство используется по умолчанию для таблиц, индексов и временных файлов, создаваемых в базе данных, если не указано иное в выражении
TABLESPACE, или переменной default_tablespace, или temp_tablespaces (соответственно). Если база данных создана без указания конкретного табличного пространства, то используется пространство, к которому принадлежит копируемый шаблон.
При инициализации экземпляра Pg или кластера Pg автоматически создаются два табличных пространства. Табличное пространство pg_global используется для общих системных каталогов. Табличное пространство pg_default используется по умолчанию для баз данных template1 и template0 (в свою очередь, также является пространством по умолчанию для других баз данных, пока не будет явно указано иное в выражении TABLESPACE команды CREATE DATABASE).
5. Работа с ролями.
В PostgreSQL используется концепция ролей (roles) для управления разрешениями на доступ к базе данных. Роль можно рассматривать как пользователя базы данных или как группу пользователей, в зависимости от того, как роль настроена. Роли могут владеть объектами базы данных (например, таблицами и функциями) и выдавать другим ролям разрешения на доступ к этим объектам, управляя тем, кто имеет доступ и к каким объектам. Кроме того, можно предоставить одной роли членство в другой роли, таким образом одна роль может использовать привилегии других ролей.
Концепция ролей включает в себя концепцию пользователей (user) и групп (groups). До версии 8.1 в PostgreSQL пользователи и группы были отдельными сущностями, но теперь есть только роли. Любая роль может использоваться в качестве пользователя, группы, и того и другого.
Роли базы данных концептуально полностью отличаются от пользователей операционной системы.На практике поддержание соответствия между ними может быть удобным, но не является обязательным. Роли базы данных являются глобальными для всего инстана Pg или кластера Pg базы данных (не для отдельной базы данных). Для создания роли используется SQL-команда CREATE ROLE, а для удаления DROP ROLE.
5.1 Создание ролей.
Роль можно создать/удалить 2-мя путями: через консольную утилиту createuser или dropuser и через psql с помощью CREATE ROLE или DROP ROLE.
Давайте попробуем обя способа создания роли:
Для просмотра списка ролей можно использовать метакоманду \du или сделать select запрос к системному каталогу pg_roles.
Для каждой роли можно назначит определенный атрибуты, определяющие её полномочия и взаимодействие с системой аутентификации клиентов.
Доступны следующие атрибуты (в скобках указаны столбцы из каталогу pg_roles):
1) Право подключения (rolcanlogin):
Только роли с атрибутом LOGIN могут использоваться для начального подключения к базе данных. Роль с атрибутом LOGIN можно рассматривать как пользователя базы данных. Для создания роли такой роли можно использовать любой из вариантов:
CREATE ROLE имя LOGIN;
или
CREATE USER имя;
или использование консольной утилиты createuser.
Как мы уже сказали выше, команда CREATE USER эквивалентна CREATE ROLE за исключением того, что CREATE USER по умолчанию предполагает атрибут LOGIN, в то время как CREATE ROLE нет.)
2) Статус суперпользователя (rolsuper):
Суперпользователь базы данных обходит все проверки прав доступа, за исключением права на вход в систему. Это опасная привилегия и она не должна использоваться небрежно. Лучше всего выполнять большую часть работы не как суперпользователь. Для создания нового супер пользователя используется CREATE ROLE имя SUPERUSER. Это нужно выполнить из под роли, которая также является суперпользователем.
3) Создание базы данных (rolcreatedb):
Роль должна явно иметь разрешение на создание базы данных (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEDB.
4) Создание роли (rolcreaterole):
Роль должна явно иметь разрешение на создание других ролей (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEROLE. Роль с привилегией CREATEROLE может также изменять и удалять другие
роли, а также выдавать и отзывать членство в ролях. Однако, для создания, изменения, удаления суперпользовательских ролей, а также изменения в них членства, требуется иметь статус суперпользователя, привилегии CREATEROLE в таких случаях недостаточно.
5) Запуск репликации (rolreplication):
Роль должна иметь явное разрешение на запуск потоковой репликации (за исключением суперпользователей, которые пропускают все проверки). Роль, используемая для потоковой репликации, также должна иметь атрибут LOGIN. Для создания такой роли используется CREATE ROLE имя REPLICATION LOGIN.
7) Игнорировать систему защиты строк (rolbypassrls):
В дополнение к стандартной системе прав SQL, управляемой командой GRANT, на уровне таблиц можно определить политики защиты строк, ограничивающие для пользователей наборы строк, которые могут быть возвращены обычными запросами или добавлены, изменены и удалены командами, изменяющими данные. Это называется также защитой на уровне строк (RLS, Row-Level Security). Суперпользователи и роли с атрибутом BYPASSRLS всегда обращаются к таблице, минуя систему защиты строк. Более детально о RLS читайте в официальной документации или в переводе на русский.
Атрибуты ролей могут быть изменены после создания командой ALTER ROLE. Для получения более детальной информации Вам следует обратиться к справке по командам CREATE ROLE и ALTER ROLE.
Давайте добавим право LOGIN и CREATEDB для нашей роли utest2:
А теперь отберем у роли utest2 право CREATEDB и добавим CREATEROLE и REPLICATION:
Отдельно нужно упомянуть про такую вещь как членство в роли.
Часто бывает удобным сгруппировать пользователей для упрощения администрирования привилегий: привилегии выдаются или отзываются на всю группу. В PostgreSQL для этого создаётся роль, которая представляет группу, а затем членство (membership) в этой группе выдаётся ролям
индивидуальных пользователей.
Сделаем групповую роль, добавим членов и создадим новую БД в табличном пространстве fast и укажем эту групповую роль владельцем БД:
Теперь мы можем попробовать подключиться к базе group_db под пользователем utest2 из консоли:
После ввода пароля mysecretpwd нас пустят в базу group_db. База пока у нас пустая, чуть ниже мы попробуем создать там простую таблицу.
Так же хочу обратить внимание на такую вещь как владелец табличного пространства, чуть выше когда мы создавали табличное пространство fast, мы его создали под пользователем postgres и его владельцем стал он, см. столбец Owner при выводе информации метакомандой \db
Поменять владельца табличного пространства можно командой ALTER TABLESPACE OWNER TO ;
Например:
5.2 Удаление ролей.
Так как роли могут владеть объектами баз данных и иметь права доступа к объектам других, удаление роли не сводится к немедленному действию DROP ROLE. Сначала должны быть удалены и переданы другим владельцами все объекты, принадлежащие роли, также должны быть отозваны все права, данные роли.
То есть наша роль является владельцем базы group_db. Чтобы решить эти проблемы нужно поменять владельца БД и только после этого удалить роль:
Как мы видим и вывода \l+ у нашей БД group_db поменялся владелец и роль group_role1 была удалена (вывод \du).
6. Работа с таблицами.
Посмотрим список таблиц в БД с помощью метакоманды \dt
Теперь удалим табличку my_first_table с помощью DROP TABLE
Попытка удаления несуществующей таблицы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP TABLE IF EXISTS
После удаления вывод \dt скажет нам, что в БД нет таблиц.
7. Работа со схемами.
Для создания схемы используется команда CREATE SCHEMA ;
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
При создании схемы так же как и при создании таблиц её владельцем становится текущий пользователь, об этом не стоит забывать!
Создадим схему и таблицу в ней:
Посмотрим список схем (метакоманда \dn):
Чтобы найти нашу таблицу мы должны будем обращаться к ней по полному пути, но вывод списка таблиц через метакоманду \dt не выведет нашу новую таблицу из схемы sc_test, чтобы он вывел таблицу мы должны добавить путь поиска схемы, это делается командой:
Чтобы не вводить SET search_path TO каждый раз мы можем задать search_path для конкретного пользователя на постоянной основе командой:
После этого мы сможем вывести список наших таблиц из схемы public и новой sc_test:
Указав путь поиска схемы мы можем выполнить DROP TABLE my_first_table; без указания схемы, но тут есть нюанс:
ВНИМАНИЕ! Т.к. мы можем создавать одинаковые по именам объекты в разных схемах, то порядок поиска схем в путь будет иметь значение при разных операциях (создание, модификацию, удаления и др.) над объектами, вывести список путей поиска схем можно командой:
Таким образом DROP TABLE my_first_table; первым делом удалит таблицу из схемы sc_test если она там есть, а повторный вызов DROP TABLE my_first_table; уже удалит таблицу из схемы public, при её наличии там.
Вот поэтому если Вы используете схемы, то и все операции над объектами должны выполнятся с указанием полного имени объекта.
Для удаления схемы нужно воспользоваться командой DROP SCHEMA ;
Попытка удаления несуществующей схемы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP SCHEMA IF EXISTS ;
Если в схеме есть объекты, то при попытке её удаление будет выведена ошибка, пример:
В таком случае нужно воспользоваться доп. опцией CASCADE, например:
Как мы видим, схема sc_test и таблица my_first_table были удалены.
На этом все, до скорых встреч.
Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
Всем привет! Сегодня мы разберем процесс установки PostgreSQL 12 на операционную систему Debian 10, а также выполним первоначальную настройку PostgreSQL. В результате у нас получится полноценный сервер баз данных, реализованный на базе Debian + PostgreSQL, и данный сервер можно использовать в сети предприятия.
Установка PostgreSQL 12 на Debian 10
Итак, давайте перейдем к установке и рассмотрим все шаги, которые необходимо выполнить, чтобы установить PostgreSQL 12 на Debian 10.
Установку я буду производить удаленно, используя программу PuTTY.
Шаг 1 – Подключение репозитория и обновление списка пакетов в системе
Установку и настройку PostgreSQL необходимо выполнять с правами пользователя root, поэтому давайте сразу переключимся на root.
Для этого пишем команду su и вводим пароль.
Далее, давайте проверим, нет ли в системе необходимых нам пакетов, так как вдруг нам и не нужно подключать дополнительный репозиторий.
Для этого вводим следующую команду.
Как видим, в Debian 10 нужной нам версии PostgreSQL нет, поэтому нам нужно подключить дополнительный репозиторий от разработчиков. Если у Вас более новая версия Debian и в стандартных репозиториях есть 12 версия PostgreSQL, то дополнительный репозиторий Вам подключать не нужно, т.е. данный шаг Вы можете пропустить.
А всем остальным, у которых также как у меня нет нужной версии, для подключения репозитория необходимо ввести следующую команду, которая создает файл в источниках с адресом нужного репозитория.
Примечание! Здесь обязательно стоить отметить, что этот репозиторий предназначен для Debian 10, для других версий адрес репозитория будет другим, например, если Вам нужно установить PostgreSQL на Debian 9, то в адресе репозитория вместо buster напишите stretch, т.е. замените кодовое имя версии.
После этого нам необходимо импортировать ключ подписи репозитория, для этого вводим команду.
Осталось обновить список пакетов в системе, это делаем стандартной командой.
И для проверки того, что теперь нам доступны пакеты PostgreSQL 12, давайте снова запустим команду поиска пакетов.
Теперь нужные пакеты нам доступны и мы можем переходить к установке PostgreSQL 12.
Шаг 2 – Установка необходимых пакетов для PostgreSQL
Для установки PostgreSQL 12 и базовых стандартных утилит необходимо установить пакет postgresql-12, это делается следующей командой.
Шаг 3 – Проверка установки
Чтобы проверить, установился и запущен ли Postgres, давайте выполним следующую команду, которая покажет статус сервиса PostgreSQL.
Как видим, PostgreSQL 12 установился и работает.
Настройка PostgreSQL 12 в Debian 10
PostgreSQL у нас установлен, однако на текущий момент им пользоваться пока нельзя, поэтому нам необходимо его настроить, в частности создать пользователя, указать какие сетевые интерфейсы будет прослушивать сервер, а также разрешить подключения по сети.
Создание пользователя и базы данных в PostgreSQL
После установки к серверу PostgreSQL мы можем подключиться только с помощью системного пользователя postgres, причем без пароля.
Давайте переключимся на пользователя postgres (данная учетная запись была создана автоматически во время установки PostgreSQL).
Затем запускаем утилиту psql – это консоль для PostgreSQL.
Первым делом нам нужно задать пароль для пользователя postgres.
Затем создаем нового пользователя на сервере PostgreSQL, так как работать от имени postgres крайне не рекомендуется.
Далее давайте создадим базу данных.
где test_db – это имя новой базы данных.
Теперь давайте дадим права на управление базой данных нашему новому пользователю.
Все готово, выходим из консоли.
Для проверки давайте подключимся к PostgreSQL от имени нового пользователя, на предложение о вводе пароля вводим пароль от новой учетной записи.
Все работает, я подключился. Для выхода снова набираем \q.
Для переключения обратно на root вводим exit.
Разрешаем подключение к PostgreSQL по сети
По умолчанию PostgreSQL прослушивает только адрес localhost, поэтому, для того чтобы мы могли подключаться по сети, нам нужно указать, какие сетевые интерфейсы будет просушивать PostgreSQL. Я для примера укажу, что прослушивать нужно все доступные интерфейсы. Если у Вас несколько сетевых интерфейсов, и Вы хотите, чтобы PostgreSQL использовал только один конкретный, то Вы его можете указать именно здесь.
Чтобы это сделать, открываем файл postgresql.conf, например, редактором nano.
Находим следующую строку.
и вносим следующие изменения (вместо звездочки Вы в случае необходимости указываете IP адрес нужного интерфейса).
Сохраняем изменения сочетанием клавиш CTRL+O и подтверждаем нажатием Enter, затем просто закрываем редактор nano сочетанием клавиш CTRL+X.
Теперь давайте разрешим подключение из сети, я для примера разрешу подключаться из сети 192.168.1.0/24 с методом аутентификации md5.
Для этого открываем файл pg_hba.conf
Ищем следующие строки.
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
Далее точно так же сохраняем изменения сочетанием клавиш CTRL+O, подтверждаем нажатием Enter и закрываем редактор nano сочетанием клавиш CTRL+X.
Перезапускаем PostgreSQL, чтобы изменения вступили в силу.
Удаленное подключение к PostgreSQL 12 с помощью pgAdmin 4
Сервер PostgreSQL настроен, поэтому, чтобы проверить его работоспособность, давайте подключимся к серверу удаленно с помощью pgAdmin 4.
Для этого запускаем на клиенте pgAdmin 4, в обозревателе кликаем на контейнер «Servers» и выбираем «Создать -> Сервер».
Далее откроется окно создания сервера, в котором нам нужно на вкладке «Общие» ввести название сервера.
А на вкладке «Соединение» указать IP адрес сервера и данные пользователя, под которым мы подключимся к серверу.
В результате мы подключимся к серверу, а в обозревателе отобразятся все объекты на данном сервере.
Читайте также: