Работа в базой PostgreSQL в Linux
Вход в терминал psql
Psql — это интерактивный терминал для работы с Postgres. Имеется обилие флагов, доступных для использования при работе с psql, но сосредоточимся на некоторых из наиболее важных, а затем, как подключиться
- -h флаг указания хоста;
- -U флаг указания пользователя;
- -p флаг указания порта (по умолчанию он равен 5432).
Для входа в терминал можно использовать следующую строку, которая запросит пароль
Другой вариант — использовать полную строку и позволить psql анализировать ее
Если вы в терминале, где уже установлена рабочая версия PostgreSQL, то вы можете войти под пользователя posgres, который по умолчанию является суперадминистратором СУБД PostgreSQL
Еще один способ входа, но уже без пароля
Как только вы подключитесь, вы можете начать уже делать запросы. В дополнение к основным запросам вы также можете использовать определенные команды. Команда \? предоставит вам список всех доступных команд.
Все по порядку про CRUD
Одна из хорошей вещей в PGSQL заключается в том, что он поставляется с некоторыми двоичными файлами, такими как createuser и createdb. Поэтому мы будем использовать их.
По умолчанию в Postgres сконфигурирован с единственным пользователем postgres, у которого есть все права на манипуляции в СУБД и является суперадминистратором.
Вход в консоль psql
Вышеупомянутая команда получает интерфейс командной строки psql в режиме полного администрирования.
Работа с пользователями
Создание пользователя посредством бинарника createuser
Задание пароля для пользователя
Создание пользователя посредством терминала psql
Работа с базой данных
Создание базы данных посредством бинарника createdb
Задание привилегий для пользователя на действия с базой данных
Создание базы данных посредством терминала psql
Установка прав пользователям
Установка пользователю роль администратора
Чтобы дать все права пользователю на операции с базой данных можно воспользоваться командой psql
Тоже самое по другому
Разрешение на соединение
Можно комбинировать несколько привилегий
Если мы хотим дать привилегии на действия всем пользователям, то пишем так
Если мы хотим дать привилегии на таблицы, то делаем так
Примечание. Ключевое слово SCHEMA означает тоже самое, что и имя базы данных.
Разрешение входа для пользователя postgres через phpPgAdmin
PostgresSQL по умолчанию имеет суперпользователя с логином postgres и вход через этот логин из вне запрещен. Поэтому, чтобы управлять базами через phpPgAdmin нам нужно войти, разрешив внешний вход. Делается через файл конфигурации phpPgAdmin, который называется config.inc.php.
В составе Vesta, данный файл конфигурации в Ubuntu находится по пути /etc/phppgadmin/config.inc.php.
В данном файле нужно поменять параметр $conf[‘extra_login_security’] поменять с true на false
Далее, если вы не знаете пароль супер администратора postgres, то есть возможность ее сменить через терминал
После данной команды сервер запросит ввести пароль и подтвердить еще одним вводом и дальше установится новый пароль, которым мы сможем войти через phpPgAdmin
PostgreSQL 9.2 Начало!
Мне хотелось создать прекрасный объемлющий мануал Getting Start без всякой воды, но включающий основные плюшки для начинающих по системе PostgreSQL в Linux.
PostgreSQL является объектно-реляционной системой управления базами данных (ОРСУБД) на основе POSTGRES, версия 4.2, разработанной в Университете Калифорнии в Беркли департаменте компьютерных наук.
PostgreSQL является open source потомком оригинального кода Berkeley. Он поддерживает большую часть стандарта SQL и предлагает множество современных функций:
Сборка и установка
Как и все любители мейнстрима PostgreSQL мы будем конечно же собирать, а не скачивать готовые пакеты (в репозитариях Debian, например, нет последней версии). Вот здесь лежит множество версий, скачивать конечно же лучше всего последнюю. На момент написания поста это версия 9.2.2
Теперь у нас есть директория с исходниками сей прекрасной базы данных.
По умолчанию файлы базы будут установлены в директорию /usr/local/pgsql, но эту директорию можно изменить задав
перед командой ./configure
Перед сборкой можно указать компилятор С++
PostgeSQL может использовать readline библиотеку, если у вас её нет и нет желания её ставить просто укажите опцию
Надеюсь у всех есть Autotools? Тогда вперед к сборке:
Все господа! Поздравляю!
Настройка
Нам необходимо указать хранилище данных наших баз данных (кластер) и запустить её.
Есть один нюанс — владельцем директории данных и пользователь, который может запускать базу должен быть не root. Это сделано в целях безопасности системы. Поэтому создадим специального пользователя
И далее все понятно
Важный процесс. Мы должны инициализировать кластер баз дынных. Сделать мы должны это от имени пользователя postgres
Теперь нужно добавить запуск PostgreSQL в автостарт. Для этого существует уже готовый скрипт и лежит он в postgresql-9.2.2/contrib/start-scripts/linux
Этот файл можно открыть и обратить внимание на следующие переменные:
- prefix — это место куда мы ставили PostgreSQL и задавали в ./configure
- PGDATA — это то, где хранится кластер баз данных и куда должен иметь доступ наш пользователь postgres
- PGUSER — это тот самый пользователь, от лица которого будет все работать
Если все стоит верно, то добвляем наш скрипт в init.d
Перезапускам систему, чтобы проверить что наш скрипт работает.
Вводим
И если появится окно работы с базой, то настройка прошла успешно! Поздравляю!
По умолчанию создается база данных с именем postgres
Теперь важно поговорить о методах авторизации.
В /usr/local/pgsql/data/pg_hba.conf как раз есть необходимые для этого настройка
Первая строка отвечает за локальное соединение, вторая — за соединение про протоколу IPv4, а третья по протоколу IPv6.
Самый последний параметр — это как раз таки метод авторизации. Его и рассмотрим (только основные)
- trust — доступ к базе может получить кто угодно под любым именем, имеющий с ней соединение.
- reject — отклонить безоговорочно! Это подходит для фильтрации определенных IP адресов
- password — требует обязательного ввода пароля. Не подходит для локальных пользователей, только пользователи созданные командой CREATE USER
- ident — позволяет только пользователем зарегистрированным в файле /usr/local/pgsql/data/pg_ident.conf устанавливать соединение с базой.
Вкратце расскажу об основных утилитах, которые пригодятся в работе.
Утилиты для работы с базой
pg_config
Возвращает информацию о текущей установленной версии PostgreSQL.
initdb
Инициализирует новое хранилище данных (кластер баз данных). Кластер представляет собой совокупность баз данных управляемых одним экземпляром севера. initdb должен быть запущен от имени будущего владельца сервера (как указано выше от имени postgres).
pg_ctl
Управляет процессом работы сервера PostgreSQL. Позволяет запускать, выполнять перезапуск, останавливать работу сервера, указать лог файл и другое.
psql
Клиент для работы с базой дынных. Позволяет выполнять SQL операции.
createdb
dropdb
Удаляет базу данных. Является оберткой SQL команды DROP DATABASE.
createuser
dropuser
Удаляет пользователя базы данных. Является оберткой SQL команды DROP ROLE.
createlang
droplang
Удаляет язык программирования. Является оберткой SQL команды DROP LANGUAGE.
pg_dump
pg_restore
pg_dumpall
Создает бэкап (дамп) всего кластера в файл.
reindexdb
Производит переиндексацию базы данных. Является оберткой SQL команды REINDEX.
clusterdb
Производит перекластеризацию таблиц в базе данных. Является оберткой SQL команды CLUSTER.
vacuumdb
Сборщик мусора и оптимизатор базы данных. Является оберткой SQL команды VACUUM.
Менеджеры по работе с базой
Что касается менеджера по работа с базой, то есть php менеджер — это phpPgAdmin и GUI менеджер pgAdmin. Должен заметить, что они оба плохо поддерживают последнюю версию PostgreSQL.
Работа с PostgreSQL: от полного нуля до полного просветления
Работа с PostgreSQL не отличается от работы с любой другой СУБД, но знать синтаксис все-таки полезно. Предлагаем вашему вниманию вводный курс по основам.
PostgreSQL – это опенсорсная реляционная СУБД. В статье будет рассматриваться процесс установки, настройки / управления, а также базовые операции с БД.
Установка
Если на вашей машине стоит MacOS, то процесс установки можно запустить командой:
На Linux СУБД устанавливается так:
Если у вас другая ОС, есть непонятные моменты или вопросы – обращайтесь в официальный хелп.
После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:
Разбираемся с настройками
Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. Попробуйте ввести следующую команду:
Этой командой вы запустите утилиту psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.
Если вам нужна помощь, введите \help (или -h) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести \help [имя команды], если вам нужна помощь по конкретной команде. Например, если ввести \help UPDATE в консоли psql, вы увидите синтаксис команды update.
Если у вас возникает много вопросов – не стоит отчаиваться. Поиск в интернете предоставит массу примеров, ну и официальную документацию psql никто не отменял.
Первым делом необходимо проверить наличие существующих пользователей и баз данных. Выполните следующую команду, чтобы вывести список всех баз данных:
На рисунке выше вы видите три базы данных по умолчанию и суперпользователя postgres, которые создаются при установке PostgreSQL.
Чтобы вывести список всех пользователей, выполните команду \du. Атрибуты пользователя postgres говорят нам, что он суперпользователь.
Основные операции с БД
Чтобы выполнять базовые действия в СУБД, нужно знать Structured Query Language (SQL).
Создание базы данных
Для создания базы данных используется команда create database. В приведенном ниже примере создается база данных с именем proglib_db.
Если вы забыли точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-», как показано на рисунке ниже. Это зачастую указывает на то, что необходимо завершить (дописать) запрос.
На картинке нам сообщают об ошибке из-за того, что в нашем случае база уже создана. Вы поймете, что к чему, когда начнете писать более длинные запросы.
Создание нового юзера
Для создания пользователя существует команда create user. В приведенном ниже примере создается пользователь с именем author.
При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.
Вы можете установить пароль для существующего пользователя. С этой задачей справится команда \password:
Чтобы задать пароль при создании пользователя, можно использовать следующую команду:
Удаление базы или пользователя
Для этой операции используется команда drop: она умеет удалять как пользователя, так и БД.
Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).
Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:
Давайте войдем в базу proglib_db под пользователем author. Нажмите \q, чтобы выйти из текущей БД, а затем выполните следующую команду:
Дополнительная литература
- Beginning PostgreSQL on the Cloud. Работа с PostgreSQL начинается с этой книги. Приступать к изучению чего-то нового лучше с практики. Эта публикация включает в себя огромное количество полезных рабочих примеров, взятых из реальных проектов.
- Администрирование PostgreSQL 9. Книга рецептов. Отличная настольная книга для разработчиков боевых проектов на PHP, Ruby, .NET, Java и Python. Рассматриваются распространенные общие вопросы по архитектуре, восстановлению, репликации и т. д.
- Семь баз данных за семь недель. Данная книга подойдет для общего развития специалиста с любым уровнем знаний. Автор рассказывает об организации каждой СУБД, а также о том, в каком случае выгодно выбрать ту или иную БД.
Надеемся, что наш небольшой туториал помог вам разобраться с основами.