Работаем с PostgreSQL через командную строку в Linux
Установка PostgreSQL на Linux (Mint)
Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:
Для установки PostgreSQL сервера:
Проверим, можем ли мы подключиться к базе данных PostgreSQL:
Вывод команды должен быть примерно таким:
PostgreSQL Подключение, Пользователи (Роли) и Базы Данных
Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:
Для подключения к базе данных PostgreSQL можно использовать команду:
После ввода пароля и успешного подключения к базе данных 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
Удалить роль (пользователя):
Нужно удалить привелегии у роли, например если нужно удалить роль admin2, нужно выполнить последовательность комманд с Drop Owned:
Дать права пользователю/роли на логин ( role is not permitted to log in ):
Выбор shema psql в консоли:
Посмотреть список всех схем:
Подключиться к конкретной схеме:
Sequences
Получить имена всех созданных sequences:
Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи:
Linux и Windows: помощь админам и пользователям
Администрируем и настраиваем Windows, Linux.
15 команд для управления PostgreSQL
В этой статье я покажу 15 наиболее полезных команд для управления postgreSQL.
1. Как изменить root пароль в PostgreSQL?
Изменение пароля для обычного пользователя происходит таким же образом. Пользователь root может поменять пароль любому пользователю.
2. Как установить PostgreSQL в автозапуск?
3. Проверяем состояние сервера
4. Как запустить, остановить, перезапустить PostgreSQL?
5. Как посмотреть какая версия PostgreSQL запущена?
5. Как создать пользователя в PostgreSQL?
Для этого существуют два метода..
Метод 1: Создаем пользователя в через PSQL шелл, командой CREATE USER.
Метод 2: Создаем пользователя в через шелл команду createuser.
Для этого существует 2 метода.
Метод 1: Создаем базу черезе PSQL шелл, с помощью команды CREATE DATABASE.
Метод 2: Используем команду createdb.
7. Получаем список всех баз в Postgresql?
8. Как удалить базу в PostgreSQL?
9. Пользуемя встроенным хелпом к командам
Команда \? отобразит строку помощи для команда PSQL. \h CREATE покажет хелп для всех команд который начинаются с CREATE.
10. Как получить список всех таблиц в базе данный в Postgresql?
Для пустой базы вы получите сообщение “No relations found.”
11. Как узнать время выполнения запроса?
# \timing — после выполения данной команды каждый последующий запрос будет показывать время выполнения.
12. Как бэкапить и восстанавливать базы и таблицы в PostgreSQL?
Этот вопрос довольно велик и я опубликую его позднее отдельной статьей.
Для того чтобы получить список доступных функций, скажите \df+
14. Как отредактировать запрос к PostgreSQL в редакторе?
\e откроет редактор, в котором вы можете отредактировать запрос и сохранить его.
15. Где я могу найти файл истории postgreSQL?
/.bash_history, postgreSQL хранит все sql команды в файле
Разное
Лучшие выставочные стенды в Москве. Отличное качество выполнения, невысокие цены.
Курьерская служба, доставка по Москве и Московской области, срочная доставка, рассылки писем, счетов, журналов
Комментариев: 10
Очень хорошая статья, люблю живые примеры
С удовольствием прочитаю статью про бэкапы в postgresql
Спасибо, подборка очень выручила когда пришлось аврально разбираться, как с postgesql работать.
И да, примерах с кодом у меня повылазили тэги , лучше бы их убрать, читать мешает.
Не получается :((( команда postgresql status говорит что он остановлен.
Странно. Я пользуюсь Ruby On Rails и у меня иногда удаляется база данных. Причём, непонятно почему.
Как посмотреть какая версия PostgreSQL запущена?
Полная ахинея. Нужно быть придурком чтобы таким образом определять версию постгреса,
Автор просто скопипастил текст «статьи» откуда-то даже не удосужившись удалить html-тэги.
Также хочется отметить, что способов запуска/остановки процессов в разных системах минимум 2, тут выделен самый неудачный в плане длинного пути и отсутствия стандартизации запуска процессов.
Есть команда показывающая место расположения базы данных:
Команды postgresql консоль linux
Описание
Параметры
Запуск в однострочном режиме, при котором символ новой строки завершает SQL-команды, так же как это делает точка с запятой.
Примечание
Этот режим реализован для тех, кому он нужен, но это не обязательно означает, что и вам нужно его использовать. В частности, если смешивать в одной строке команды SQL и метакоманды, порядок их выполнения может быть не всегда понятен для неопытного пользователя.
Принудительно запрашивать пароль перед подключением к базе данных, даже если он не будет использоваться.
Код завершения
Использование
Подключение к базе данных
Этот способ также позволяет использовать LDAP для получения параметров подключения, как описано в Разделе 32.17. Более полно все имеющиеся параметры соединения описаны в Подразделе 32.1.2.
Если соединение не может быть установлено по любой причине (например, нет прав, сервер не работает и т. д.), psql вернёт ошибку и прекратит работу.
Ввод SQL-команд
Комментарии в стиле C передаются для обработки на сервер, в то время как комментарии в стандарте SQL psql удаляет перед отправкой.
Метакоманды
Формат команды psql следующий: обратная косая черта, сразу за ней команда, затем аргументы. Аргументы отделяются от команды и друг от друга любым количеством пробелов.
Чтобы включить пробел в значение аргумента, нужно заключить его в одинарные кавычки. Чтобы включить одинарную кавычку в значение аргумента, нужно написать две одинарные кавычки внутри текста в одинарных кавычках. Всё, что содержится в одинарных кавычках подлежит заменам, принятым в языке C: \n (новая строка), \t (табуляция), \b (backspace), \r (возврат каретки), \f (подача страницы), \ цифры (восьмеричное число), и \x цифры (шестнадцатеричное число). Если внутри текста в одинарных кавычках встречается обратная косая перед любым другим символом, то она экранирует этот символ.
Текст аргумента, заключённый в обратные кавычки ( ` ), считается командной строкой, которая передаётся в командную оболочку ОС. Вывод от этой команды (с удалёнными в конце символами новой строки) заменяет текст в обратных кавычках.
Определены следующие метакоманды:
Если новое подключение успешно установлено, предыдущее подключение закрывается. Если попытка подключения не удалась (неверное имя пользователя, доступ запрещён и т. д.), то предыдущее соединение останется активным, только если psql находится в интерактивном режиме. Если скрипт выполняется неинтерактивно, обработка немедленно останавливается с сообщением об ошибке. Различное поведение выбрано для удобства пользователя в качестве защиты от опечаток с одной стороны и в качестве меры безопасности, не позволяющей случайно запустить скрипты в неправильной базе, с другой.
Примеры: \C [ заголовок ]
Подсказка
С указанием program psql выполняет команду и данные, поступающие из/в неё, передаются между сервером и клиентом. Это опять же означает, что для выполнения программ используются привилегии локального пользователя, а не сервера, и не требуются привилегии суперпользователя SQL.
Подсказка
Подсказка
Для некоторых типов отношений \d показывает дополнительную информацию по каждому столбцу: значения столбца для последовательностей, индексируемые выражения для индексов и параметры обёртки сторонних данных для сторонних таблиц.
Примечание
Внимание
\deu+ также может отображать имя и пароль удалённого пользователя, поэтому следует позаботиться о том, чтобы не раскрывать их.
Подсказка
Если указан номер строки, psql будет позиционировать курсор на указанную строку файла или буфера запроса. Обратите внимание, что если указан один аргумент и он числовой, psql предполагает, что это номер строки, а не имя файла.
Подсказка
Как настроить редактор и изменить его поведение, рассказывается в разделе Переменные окружения.
Печатает аргументы в стандартный вывод, разделяя их одним пробелом и добавляя в конце перевод строки. Команда полезна для формирования вывода из скриптов. Например:
Подсказка
Если используется команда \o для перенаправления вывода запросов, возможно, следует применять команду \qecho вместо этой.
Если указан номер строки, psql будет позиционировать курсор на указанную строку тела функции. (Обратите внимание, что тело функции обычно не начинается на первой строке файла).
Подсказка
Как настроить редактор и изменить его поведение, рассказывается в разделе Переменные окружения.
Устанавливает кодировку набора символов на клиенте. Без аргумента команда показывает текущую кодировку. \errverbose
Если указан номер строки, psql установит курсор на заданную строку в определении представления. \f [ строка ]
Устанавливает разделитель полей для невыровненного режима вывода запросов. По умолчанию используется вертикальная черта ( | ). См. также \pset для универсального способа настройки параметров вывода. \g [ имя_файла ]
\g [ | команда ]
Отправляет буфер ввода текущего запроса на сервер, а затем обрабатывает содержимое каждого столбца каждой строки результата запроса (если он непустой) как SQL-оператор, то есть исполняет его. Например, следующая команда создаст индексы по каждому столбцу my_table :
Отправляет буфер текущего запроса на сервер для выполнения и сохраняет результат запроса в переменных psql (см. раздел Переменные). Выполняемый запрос должен возвращать ровно одну строку. Каждый столбец строки результата сохраняется в отдельной переменной, которая называется так же, как и столбец. Например:
Если значение столбца NULL, то вместо присвоения значения соответствующая переменная удаляется.
Если запрос завершается ошибкой или не возвращает одну строку, то никакие переменные не меняются. \h или \help [ команда ]
Примечание
Читает ввод из файла имя_файла и выполняет его, как будто он был набран на клавиатуре.
Примечание
Подсказка
Используйте \lo_list для получения OID больших объектов.
Удаляет большой объект с oid_БО из базы данных.
Подсказка
Используйте \lo_list для получения OID больших объектов.
Результаты запросов будут сохраняться в файле имя_файла или перенаправляться команде оболочки (заданной аргументом команда ). Если аргумент не указан, результаты запросов перенаправляются на стандартный вывод.
« Результаты запросов » включают в себя все таблицы, ответы команд, уведомления, полученные от сервера базы данных, а также вывод от метакоманд, запрашивающих базу данных (таких как \d ), но не сообщения об ошибках.
Подсказка
Печатает содержимое буфера текущего запроса в стандартный вывод. \password [ имя_пользователя ]
Эта команда устанавливает параметры, влияющие на вывод результатов запросов. Указание параметр определяет, какой параметр требуется установить. Семантика значения зависит от выбранного параметра. Для некоторых параметров отсутствие значения означает переключение значения, либо сброс значения, как описано ниже в разделе конкретного параметра. Если такое поведение не упоминается, то пропуск значения приводит к отображению текущего значения параметра.
\pset без аргументов выводит текущий статус всех параметров команды.
Имеются следующие параметры:
Устанавливает разделитель полей для невыровненного режима вывода в нулевой байт. footer
В формате unaligned все столбцы размещаются на одной строке и отделяются друг от друга текущим разделителем полей. Это полезно для создания вывода, который будут читать другие программы (например, для вывода данных с разделителем Tab или через запятую).
Формат aligned это стандартный, удобочитаемый, хорошо отформатированный текстовый вывод. Используется по умолчанию.
Стиль unicode использует символы Юникода для рисования линий. Символы новой строки в данных показываются с использованием символа возврата каретки в правом поле. Когда при формате wrapped происходит перенос данных на новую строку (без символа новой строки), ставится символ многоточия в правом поле первой строки и в левом поле следующей строки.
Когда значение border больше нуля, параметр linestyle также определяет символы, которыми будут рисоваться границы. Обычные символы ASCII работают везде, но символы Юникода смотрятся лучше на терминалах, распознающих их. null
Если в pager_min_lines задаётся число, превышающее высоту страницы, программа постраничного вывода не будет вызываться, пока не наберётся заданное число строк для вывода. Значение по умолчанию — 0. recordsep
Устанавливает разделитель записей (строк) для невыровненного режима вывода. По умолчанию используется символ новой строки. recordsep_zero
Устанавливает разделитель записей для невыровненного режима вывода в нулевой байт. tableattr (или T )
Устанавливает заголовок таблицы для любых впоследствии выводимых таблиц. Это можно использовать для задания описательных тегов при формировании вывода. Если значение не задано, заголовок таблицы удаляется. tuples_only (или t )
Устанавливает стиль рисования границ для стиля линий unicode : single (одинарный) или double (двойной). unicode_column_linestyle
Устанавливает стиль рисования колонок для стиля линий unicode : single (одинарный) или double (двойной). unicode_header_linestyle
Устанавливает стиль рисования заголовка для стиля линий unicode : single (одинарный) или double (двойной).
Иллюстрацию того, как могут выглядеть различные форматы, можно увидеть в разделе Примеры.
Подсказка
Сбрасывает (очищает) буфер запроса. \s [ имя_файла ]
\set без аргументов выводит имена и значения всех psql переменных, установленных в настоящее время.
Имена переменных могут содержать буквы, цифры и знаки подчёркивания. Подробнее см. раздел Переменные ниже. Имена переменных чувствительны к регистру.
Хотя вы можете задать любой переменной любое значение, psql рассматривает несколько переменных особым образом. Они документированы в разделе о переменных.
Примечание
Задаёт для переменной среды имя значение или, если значение не задано, удаляет переменную среды. Пример: \sf[+] описание_функции
При добавлении + к команде строки вывода нумеруются, первая строка тела функции получит номер 1. \sv[+] имя_представления
При добавлении + к команде строки вывода нумеруются, начиная с 1. \t
Включает/выключает отображение имён столбцов и результирующей строки с количеством выбранных записей для запросов. Эта команда эквивалентна \pset tuples_only и предоставлена для удобства. \T параметры_таблицы
Включает/отключает отображение времени выполнения команд SQL в миллисекундах. Без параметра переключает текущий режим отображения времени выполнения. \unset имя
Эта команда многократно выполняет текущий запрос в буфере (как \g ), пока не будет прервана или не возникнет ошибка. Аргумент задаёт количество секунд ожидания между выполнениями запроса (по умолчанию 2). Результат каждого запроса выводится с заголовком, включающим строку \pset title (если она задана), время запуска запроса и интервал задержки. \x [ on | off | auto ]
Это псевдоним для \dp ( « показать права доступа » ). \! [ команда ]
Выполняет команду ОС. Без указания команды запускает отдельную командную оболочку ОС. Последующие аргументы не интерпретируются, командная оболочка ОС увидит их как есть. В частности, не применяются правила подстановки переменных и экранирование с обратной косой чертой. \? [ тема ]
Шаблоны поиска
Расширенные возможности
Переменные
psql предоставляет возможности подстановки переменных подобные тем, что используются в командных оболочках Unix. Переменные представляют собой пары имя/значение, где значением может быть любая строка любой длины. Имя должно состоять из букв (включая нелатинские буквы), цифр и знаков подчёркивания.
Это работает как в обычных SQL-командах, так и в метакомандах; подробности в разделе Интерполяция SQL ниже.
Примечание
Примечание
Примечание
Имя базы данных, к которой вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. ECHO
Содержит текущую кодировку набора символов клиента. FETCH_COUNT
Если переменная установлена в целое значение > 0, результаты запросов SELECT извлекаются из базы данных и отображаются группами с таким количеством строк, в отличие от поведения по умолчанию, когда перед отображением результирующий набор накапливается целиком. Это позволяет использовать ограниченный размер памяти независимо от размера выборки. При включении этой функциональности обычно используются значения от 100 до 1000. Имейте в виду, что запрос может завершиться ошибкой после отображения некоторого количества строк.
Подсказка
Хотя можно использовать любой формат вывода, формат по умолчанию aligned как правило выглядит хуже, потому что каждая группа по FETCH_COUNT строк форматируется отдельно, что может привести к разной ширине столбцов в разных группах. Остальные форматы вывода работают лучше.
Примечание
Имя файла для хранения истории команд. Значение по умолчанию
Примечание
Количество команд для хранения в истории. Значение по умолчанию 500.
Примечание
Имя компьютера, где работает сервер базы данных, к которому вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. IGNOREEOF
Примечание
Содержит порт сервера базы данных, к которому вы сейчас подключены. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. PROMPT1
PROMPT2
PROMPT3
Содержит имя пользователя базы данных, который сейчас подключён. Устанавливается всякий раз при подключении к базе данных (в том числе при старте программы), но эту переменную можно удалить. VERBOSITY
Интерполяция SQL
Когда значение будет использоваться в качестве SQL литерала или идентификатора, безопаснее заключить его в кавычки. Если значение переменной используется как SQL литерал, то после двоеточия нужно написать имя переменной в одинарных кавычках. Если значение переменной используется как SQL идентификатор, то после двоеточия нужно написать имя переменной в двойных кавычках. Эти конструкции корректно работают с кавычками и другими специальными символами, которые могут содержаться в значении переменной. Предыдущий пример более безопасно выглядит так:
Один из примеров использования данного механизма — это копирование содержимого файла в столбец таблицы. Сначала загрузим содержимое файла в переменную, затем подставим значение переменной как строку в кавычках:
(Отметим, что это пока не будет работать, если my_file.txt содержит байт NUL. psql не поддерживает NUL в значениях переменных.)
Настройка приглашений
Значения этих переменных выводятся буквально, за исключением случаев, когда в них встречается знак процента ( % ). В зависимости от следующего символа будет подставляться определённый текст. Существуют следующие подстановки:
Номер порта, который прослушивает сервер базы данных. %n
Имя текущей базы данных. %
, если текущая база данных совпадает с базой данных по умолчанию. %#
PID обслуживающего процесса для текущего подключения. %R
Подставляется символ с указанным восьмеричным кодом. %: имя :
выдаст жирное ( 1; ), желтое на черном ( 33;40 ) приглашение для VT100 совместимых цветных терминалов.
Примечание
Редактирование командной строки
Переменные окружения
Параметры подключения по умолчанию (см. Раздел 32.14). PSQL_EDITOR
EDITOR
VISUAL
Значение по умолчанию + в Unix-подобных системах (соответствует редактору по умолчанию vi и многим другим распространённым редакторам). На платформе Windows нет значения по умолчанию. PSQL_HISTORY
Альтернативное расположение файла с историей команд. Допускается использование тильды (
Файлы
История командной строки хранится в файле
/.psql_history или %APPDATA%\postgresql\psql_history на Windows.
Замечания
В PostgreSQL до 8.4 программа psql могла принять первый аргумент однобуквенной команды с обратной косой чертой сразу после команды, без промежуточного пробела. Теперь разделительный пробельный символ обязателен.
Замечания для пользователей Windows
Примеры
Первый пример показывает, что для ввода одной команды может потребоваться несколько строк. Обратите внимание, как меняется приглашение:
Теперь посмотрим на определение таблицы:
Теперь изменим приглашение на что-то более интересное:
Предположим, что вы внесли данные в таблицу и хотите на них посмотреть:
Таблицу можно вывести разными способами при помощи команды \pset :
Также можно использовать короткие команды:
Когда это уместно, результаты запроса можно просмотреть в виде перекрёстной таблицы с помощью команды \crosstabview :
Второй пример показывает таблицу умножения, строки в которой отсортированы в обратном числовом порядке, а столбцы — независимо, по возрастанию числовых значений.