Работаем с PostgreSQL через командную строку в Linux
Установка PostgreSQL на Linux (Mint)
Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:
Для установки PostgreSQL сервера:
Проверим, можем ли мы подключиться к базе данных PostgreSQL:
Вывод команды должен быть примерно таким:
PostgreSQL Подключение, Пользователи (Роли) и Базы Данных
Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:
Для подключения к базе данных PostgreSQL можно использовать команду:
Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.
После ввода пароля и успешного подключения к базе данных PostgreSQL, можно посылать SQL-запросы и psql-команды.
PostgreSQL создание новой роли и базы данных
Создать новую роль c именем admin (указывайте нужное имя):
Создание новой базы данных:
Дать права роли на базу данных:
Включить удаленный PostgreSQL доступ для пользователей
Нам нужно отредактировать файл /etc/postgresql/ /main/pg_hba.conf, задав опцию md5 вместо peer.
может быть 10, 11, 12 и т.д.
После этого сделать restart PostgreSQL:
Полезные команды PostgreSQL
Выйти из клиента PostgreSQL:
Показать список баз данных PostgreSQL:
Показать список таблиц:
Показать список пользователей (ролей):
Показать структуру таблицы:
Переименовать базу данных:
Удалить базу данных:
Изменить текущую базу данных в PostgreSQL (вы не сможете переименовать или удалить текущую базу данных):
\connect db_name или более короткий alias: \c db_name
Удалить роль (пользователя):
Роль не будет удалена, если у нее есть привелегии — возникнет ошибка ERROR: role cannot be dropped because some objects depend on it .
Нужно удалить привелегии у роли, например если нужно удалить роль admin2, нужно выполнить последовательность комманд с Drop Owned:
Дать права пользователю/роли на логин ( role is not permitted to log in ):
Linux как запустить postgresql
Чтобы кто-либо смог обратиться к базе данных, необходимо сначала запустить сервер баз данных. Программа сервера называется postgres . Для работы программа postgres должна знать, где найти данные, которые она будет использовать. Указать это местоположение позволяет параметр -D . Таким образом, проще всего запустить сервер, выполнив команду:
в результате которой сервер продолжит работу в качестве процесса переднего плана. Запускать эту команду следует под именем учётной записи PostgreSQL . Без параметра -D сервер попытается использовать каталог данных, указанный в переменной окружения PGDATA . Если и эта переменная не определена, сервер не будет запущен.
Однако обычно лучше запускать postgres в фоновом режиме. Для этого можно применить обычный синтаксис, принятый в оболочке Unix:
Важно где-либо сохранять информацию, которую выводит сервер в каналы stdout и stderr , как показано выше. Это полезно и для целей аудита, и для диагностики проблем. (Более глубоко работа с файлами журналов рассматривается в Разделе 24.3.)
Программа postgres также принимает несколько других параметров командной строки. За дополнительными сведениями обратитесь к справочной странице postgres и к следующей Главе 19.
Такой вариант запуска довольно быстро может оказаться неудобным. Поэтому для упрощения подобных задач предлагается вспомогательная программа pg_ctl . Например:
запустит сервер в фоновом режиме и направит выводимые сообщения сервера в указанный файл журнала. Параметр -D для неё имеет то же значение, что и для программы postgres . С помощью pg_ctl также можно остановить сервер.
Обычно возникает желание, чтобы сервер баз данных сам запускался при загрузке операционной системы. Скрипты автозапуска для разных систем разные, но в составе PostgreSQL предлагается несколько типовых скриптов в каталоге contrib/start-scripts . Для установки такого скрипта в систему требуются права root.
В различных системах приняты разные соглашения о порядке запуска служб в процессе загрузки. Во многих системах для этого используется файл /etc/rc.local или /etc/rc.d/rc.local . В других применяются каталоги init.d или rc.d . Однако при любом варианте запускаться сервер должен от имени пользователя PostgreSQL , но не root или какого-либо другого пользователя. Поэтому команду запуска обычно следует записывать в форме su postgres -c ‘. ‘ . Например:
Ниже приведены более конкретные предложения для нескольких основных ОС. (Вместо указанных нами шаблонных значений необходимо подставить правильный путь к каталогу данных и фактическое имя пользователя.)
Для запуска во FreeBSD воспользуйтесь файлом contrib/start-scripts/freebsd в дереве исходного кода PostgreSQL .
В OpenBSD , добавьте в файл /etc/rc.local следующие строки:
В системах Linux вы можете либо добавить
в /etc/rc.d/rc.local или в /etc/rc.local , либо воспользоваться файлом contrib/start-scripts/linux в дереве исходного кода PostgreSQL .
Используя systemd , вы можете применить следующий файл описания службы (например, /etc/systemd/system/postgresql.service ):
Для использования Type=notify требуется, чтобы сервер был скомпилирован с указанием configure —with-systemd .
Особого внимания заслуживает значение тайм-аута. На момент написания этой документации по умолчанию в systemd принят тайм-аут 90 секунд, так что процесс, не сообщивший о своей готовности за это время, будет уничтожен. Но серверу PostgreSQL при запуске может потребоваться выполнить восстановление после сбоя, так что переход в состояние готовности может занять гораздо больше времени. Предлагаемое значение 0 отключает логику тайм-аута.
В NetBSD можно использовать скрипт запуска для FreeBSD или для Linux , в зависимости от предпочтений.
В Solaris , создайте файл с именем /etc/init.d/postgresql , содержащий следующую стоку:
Затем создайте символическую ссылку на него в каталоге /etc/rc3.d с именем S99postgresql .
18.3.1. Сбои при запуске сервера
Есть несколько распространённых причин, по которым сервер может не запуститься. Чтобы понять, чем вызван сбой, просмотрите файл журнала сервера или запустите сервер вручную (не перенаправляя его потоки стандартного вывода и ошибок) и проанализируйте выводимые сообщения. Ниже мы рассмотрим некоторые из наиболее частых сообщений об ошибках более подробно.
Это обычно означает именно то, что написано: вы пытаетесь запустить сервер на том же порту, на котором уже работает другой. Однако, если сообщение ядра не Address already in use или подобное, возможна и другая проблема. Например, при попытке запустить сервер с номером зарезервированного порта будут выданы такие сообщения:
может означать, что установленный для вашего ядра предельный размер разделяемой памяти слишком мал для рабочей области, которую пытается создать PostgreSQL (в данном примере 4011376640 байт). Возможно также, что в вашем ядре вообще отсутствует поддержка разделяемой памяти в стиле System-V. В качестве временного решения можно попытаться запустить сервер с меньшим числом буферов (shared_buffers), но в итоге вам, скорее всего, придётся переконфигурировать ядро и увеличить допустимый размер разделяемой памяти. Вы также можете увидеть это сообщение при попытке запустить несколько серверов на одном компьютере, если запрошенный ими объём разделяемой памяти в сумме превышает этот предел.
не означает, что у вас закончилось место на диске. Это значит, что установленное в вашем ядре предельное число семафоров System V меньше, чем количество семафоров, которое пытается создать PostgreSQL . Как и в предыдущем случае можно попытаться обойти эту проблему, запустив сервер с меньшим числом допустимых подключений (max_connections), но в конце концов вам придётся увеличить этот предел в ядре.
Если вы получаете ошибку « illegal system call » (неверный системный вызов), то, вероятнее всего, ваше ядро вовсе не поддерживает разделяемую память или семафоры. В этом случае вам остаётся только переконфигурировать ядро и включить их поддержку.
18.3.2. Проблемы с подключениями клиентов
Хотя ошибки подключений, возможные на стороне клиента, довольно разнообразны и зависят от приложений, всё же несколько проблем могут быть связаны непосредственно с тем, как был запущен сервер. Описание ошибок, отличных от описанных ниже, следует искать в документации соответствующего клиентского приложения.
Это общая проблема « я не могу найти сервер и начать взаимодействие с ним » . Показанное выше сообщение говорит о попытке установить подключение по TCP/IP. Очень часто объясняется это тем, что сервер просто забыли настроить для работы по протоколу TCP/IP.
Кроме того, при попытке установить подключение к локальному серверу через Unix-сокет можно получить такое сообщение:
Путь в последней строке помогает понять, к правильному ли адресу пытается подключиться клиент. Если сервер на самом деле не принимает подключения по этому адресу, обычно выдаётся сообщение ядра Connection refused (В соединении отказано) или No such file or directory (Нет такого файла или каталога), приведённое выше. (Важно понимать, что Connection refused в данном контексте не означает, что сервер получил запрос на подключение и отверг его. В этом случае были бы выданы другие сообщения, например, показанные в Разделе 20.4.) Другие сообщения об ошибках, например Connection timed out (Тайм-аут соединения) могут сигнализировать о более фундаментальных проблемах, например, о нарушениях сетевых соединений.
Как установить и начать использовать PostgreSQL в Ubuntu 16.04
Posted March 21, 2017
Введение
Системы управления реляционными базами данных являются ключевым компонентом работы многих веб-сайтов и приложений. Они предоставляют средство структурированного хранения, организации и доступа к информации.
PostgreSQL или Postgres — это система управления реляционной базой данных, предоставляющая реализацию языка запросов SQL. Это популярный выбор для множества больших и малых проектов. Postgres также имеет преимущество соответствия стандартам (в том числе SQL), и, кроме того, обладает множеством продвинутых возможностей, например, надёжные транзакции (reliable transactions) и параллелизм без блокировки чтения (concurrency without read locks).
В этой статье мы продемонстрируем, как установить Postgres на виртуальный сервер с Ubuntu 16.04, а также осветим несколько простых сценариев его использования.
Перед установкой
Стандартные репозитории Ubuntu содержат пакеты Postgres, поэтому для установки мы будем использовать пакетный менеджер apt .
Сначала обновим локальный список пакетов. Далее мы установим пакет Postgres и пакет -contrib , который добавляет дополнительную функциональность:
Теперь, когда наше программное обеспечение установлено, мы можем детальнее остановиться на том, как оно работает, и почему работа с Postgres может отличаться от использования других баз данных, с которыми вы могли работать ранее.
Использование ролей и баз данных PostgreSQL
По умолчанию Postgres использует концепцию ролей для осуществления аутентификации и авторизации. Они, в некотором роде, подобны Unix аккаунтам, но Postgres не делает различий между пользователями и группами, а вместо этого предпочитает более гибкий термин “роль”.
После установки Postgres настраивает так называемую идентичную аутентификацию (ident authentication). Это означает, что Postgres ассоциирует свои роли с подходящими Unix/Linux аккаунтами. Если роль существует в Postgres, пользователь с этим же именем в Unix/Linux сможет осуществлять вход под этой ролью.
Существует несколько способов использовать это для доступа к Postgres.
Переключение на аккаунт postgres
В процессе установки был создан аккаунт пользователя с именем postgres , который был ассоциирован с ролью Postgres по умолчанию. Для использования Postgres мы можем осуществить вход с этим аккаунтом.
Переключимся на использование postgres аккаунта на вашем сервере этой командой:
Теперь вы можете получить доступ к командной строке Postgres напечатав команду:
После этого вы сможете войти и начать взаимодействовать с системой управления базами данных.
Для выхода из PostgreSQL напечатайте:
После этого вы должны увидеть стандартное приглашение командной строки Linux (Linux command prompt).
Доступ к командной строке Postgres без переключения аккаунтов
Вы также можете выполнять команды postgres напрямую с помощью sudo .
Например, выше мы хотели получить доступ к интерфейсу командной строки Postgres. Мы можем сделать это одной строкой, используя команду psql от имени пользователя postgres с sudo :
Это позволит вам войти в интерфейс командной строки Postgres без использования оболочки bash .
Для выхода из интерактивной сессии Postgres напечатайте:
Создание новой роли
На данный момент у нас есть всего одна роль postgres . Мы можем создавать новые роли используя команду createrole из командной строки. Флаг —interactive позволит сделать это в интерактивном режиме, когда система будет запрашивать необходимые значения у вас.
Если вы осуществили вход с помощью аккаунта postgres , вы можете создать нового пользователя напечатав команду:
Если вы предпочитаете использовать sudo перед вашими командами и не переключаться между пользователями, вы можете сделать это следующим образом:
Этот скрипт задаст вам несколько вопросов, и, в зависимости от ваших ответов, выполнит необходимые команды Postgres для создания желаемого аккаунта пользователя.
Вы можете использовать дополнительные флаги при выполнении этой команды. Ознакомиться с полным списком флагов можно изучив страницу man :
Создание новой базы данных
Ещё одно допущение, которое делает Postgres, заключается в том, что для каждой роли, которая была использована для входа, необходимо создать базу данных с тем же именем, что и имя роли, к которой можно будет осуществлять доступ с помощью этой роли.
Поэтому для созданного нами ранее пользователя с именем sammy соответствующая роль будет по умолчанию пытаться соединиться с базой данных sammy . Вы можете создать эту базу данных с помощью команды createdb .
Если вы вошли с использованием аккаунта postgres , вам необходимо ввести нечто такое:
Если вы предпочитаете использовать sudo перед вашими командами и не переключаться между пользователями, вы можете сделать это следующим образом:
Запуск командной строки Postgres с новой ролью
Для использования идентичной аутентификации (ident authentication) вам необходимо иметь пользователя Linux с тем же именем, что и имя роли и базы данных Postgres.
Если у вас нет такого пользователя, вы можете создать его с помощью команды adduser . Для этого вам потребуется аккаунт с привилегиями sudo (вы не сможете использовать для этого пользователя postgres ):
После создания пользователя вы можете сменить пользователя и зайти в Postgres командами:
Или вы можете сделать это следующим образом:
Вы автоматически войдёте в Postgres, если все компоненты были сконфигурированы правильным образом (как описано выше).
Если вы хотите войти в другую базу данных, вы можете указать имя базы данных в явном виде:
После осуществления входа вы можете проверить информацию о текущем подключении командой:
Эта информация может быть полезна при подключении к базам данных и с использованием пользователей, отличных от созданных по умолчанию.
Создание и удаление таблиц
Теперь, когда вы знаете, как осуществлять доступ к СУБД Postgres, мы можем кратко рассмотреть вопрос выполнения некоторых распространённых задач в Postgres.
Для начала создадим таблицу для хранения данных. Давайте создадим таблицу, которая описывает параметры инвентаря детской площадки.
Базовый синтаксис для этой команды выглядит примерно так:
Как видно из этого примера, вы задаём имя таблицы и затем определяем необходимые столбцы, их тип и максимальную длину хранимых значений. Мы также можем добавлять ограничения на значения в каждом из столбцов.
Для целей нашего примера мы создадим простую таблицу:
Мы создали таблицу для инвентаря детской площадки. Первый столбец содержит идентификатор инвентаря с типом serial . Это целочисленный тип данных с автоинкрементом. Мы задали этот столбец с параметром первичного ключа ( primary key ), что означает, что все значения должны быть ненулевыми и уникальными.
Для двух наших столбцов ( equip_id и install_date ) мы не задали максимальную длину значений. Некоторые типы столбцов не требует задания максимальной длины значения, потому что длина определяется типом данных в этом столбце.
Кроме того мы создали столбцы type и color , которые не могут быть пустыми. Мы также создали столбец location и настроили его таким образом, что его значение может принимать одно из восьми заданных значений. Последний столбец задаёт дату установки данной единицы инвентаря детской площадки.
Мы можем убедиться, что таблица создана, следующей командой:
Наша таблица на месте, но помимо неё у нас теперь есть и нечто под названием playground_equip_id_seq с типом sequence . Это нечто представляет собой реализацию типа serial , который мы задали для столбца equip_id . Таким образом Postgres будет следить за следующим номером в последовательности идентификаторов, эта связь создаётся автоматически для столбцов данного типа.
Если вы хотите увидеть список только таблиц, без объектов-последовательностей, вы можете набрать следующую команду:
Добавление, запрос и удаление данных из таблицы
Теперь, когда у нас есть таблица, мы можем добавить в неё данные.
Сначала добавим горку (slide) и качели (swing). Для того, чтобы это сделать, нам необходимо указать имя таблицы, названия столбцов, а также сами данные:
При вводе данных необходимо иметь в виду некоторые наиболее распространённые ошибки. Прежде всего, названия столбцов должны быть указаны без кавычек, а данные для этих столбцов — с кавычками.
Также надо иметь в виду, что мы не указываем значения для столбца equip_id . Это значение будет задаваться автоматически при добавление новой записи в таблицу.
Мы можем просмотреть добавленные данные следующей командой:
Как видно из этого консольного вывода, наше поле equip_id было успешно заполнено автоматически, а остальные поля также получили корректные значения.
Если горка на нашей детской площадке сломалась и нам необходимо её убрать, мы можем удалить эту запись из нашей базы следующей командой:
Теперь при просмотре содержимого таблицы мы увидим, что запись горки более в ней не представлена:
Как добавлять и удалять столбцы из таблицы
При необходимости мы можем легко добавить дополнительные столбцы и после создания таблицы.
Для примера добавим в нашу таблицу столбец, содержащий информацию о дате последнего осмотра единицы инвентаря детской площадки:
Теперь если мы выведем содержимое таблицы, мы увидим, что новый столбец был добавлен (но существующие записи не содержат никаких значений в этом столбце):
Мы также легко можем и удалять столбцы. Например, если наш ремонтный отдел использует отдельный инструмент для отслеживания истории технического обслуживания инвентаря нашей площадки, мы можем удалить созданный ранее столбец следующим образом:
Как обновлять данные в таблице
Мы уже знаем, как добавлять записи в таблицу и как удалять их из таблицы. Теперь мы рассмотрим вопрос изменения существующих в таблице записей.
Вы можете обновлять значения существующих записей путём выборки необходимых записей и установки значений необходимых столбцов. Например, мы можем найти все записи для качелей (swing) и изменить их цвет на красный, потому что ранее мы покрасили все качели на нашей детской площадке в красный цвет:
Мы можем убедиться, что операция завершилась успешно, запросив содержимое таблицы:
Как видите, теперь наши качели красного цвета.
Заключение
Теперь у вас есть установленный и настроенный PostgreSQL на вашем сервере с Ubuntu 16.04. Тем не менее, полученных нами знаний не достаточно для полноценной работы с Postgres. Мы рекомендуем ознакомиться с этими статьями о работе с данной СУБД: