Psql как запустить sql файл

Обновлено: 05.07.2024

Apr 11, 2016 23:51 · 2249 words · 11 minute read postgres перевод

Мне настолько понравилась эта статья, что я решил её перевести. Получилось весьма вольно, но сама суть сохранена.

Я уже больше 4 лет работаю с PostgreSQL. В начале использовал графические программы, сейчас же обхожусь только встроенными утилитами командной строки. Так получается более эффективно.

Этот пост разделён на несколько секций согласно задачам, которые можно решать с помощью psql:

Настройка psql

psql отлично настроена и из коробки, однако я бы хотел остановиться на 2х вещах, которые использую каждый день и которые делают psql более удобным инструментом. Первая по важности - получение более удобочитаемового вывода. По умолчанию psql старается представить данные как таблицу:

Пока ширина таблицы меньше ширины экрана всё нормально, но потом строки начнут переноситься и вывод станет просто отвратительным:

Чтобы избежать этого воспользуемся командой активации “расширенного дисплея” (expanded display):

Теперь запрос будет выводиться в две колонки: в первой имя поля, во второй значение.

Ещё одна хитрость: можно включать такой режим только когда он действительно нужен ( \x auto ). В таком случае если таблица помещается по ширине, то будет табличный вывод, в противном случае - построчный.

Вторая возможность - указать как будет выводиться значение NULL. По умолчанию оно неотличимо от пустой строки.

Хотел бы рассказать ещё об одной фиче psql. Если вам, как и мне, нравятся SQL выражения в ALL-CAPS, то это можно настроить в автодополнении командой \set COMP_KEYWORD_CASE upper . Теперь, когда будете набирать SQL-запрос, последовательность sel<tab> будет автоматически преобразовываться в SELECT<space> .

Конечно, прописывать все эти команды каждый раз при запуске psql будет слишком утомительным, так что я предлагаю прописать их один раз в

/.psqlrc, который выполняется при каждой новой сессии psql.

Это лишь самая малость команд для настройки psql. Гораздо больше вы можете найти в th и psql’s doc.

Получение справки

Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через \h .

\h без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис SELECT или ALTER TABLE . Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. \h alter поможет в этом и предоставит более чем подробную информацию. psql использует пагинацию для большого вывода, так что там работает поиск. Наберите / + текст для поиска + <enter> и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно. Поискав NULL, находится вот такая команда: ALTER [ COLUMN ] column_name < SET | DROP >NOT NULL . Теперь можно составить нужную команду, не заглядывая в Google.

Как видите, \h даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть \? .

Ваша песочница

В psql заданы настройки по умолчанию для подключения к базе данных. Чтобы подключиться к локальной БД достаточно набрать psql db_name . Вы можете не указывать даже имя базы данных. psql трактует это как подключение к БД с именем текущего пользователя. Это позволяет быстро попасть в psql-сессию.

Мне удобно бывает просто набрать psql и проверить синтаксис запроса или прочитать справку без необходимости соединения с какой-нибудь реальной БД.

Описание БД

Для получения информации о конкретном объекте таблицы или представления служит команда \d . Она показывает следующие вещи,

список столбцов вместе с их типами и значениями по умолчанию
индексы
ограничения
внешние ключи
триггеры

Для функций есть подобная команда \df. При вызове без аргументов, она покажет список всех функций. У меня их слишком много, так что с вашего позволения воспользуюсь фильтром:

Когда работаете со своими собственными функциями, то простого вывода сигнатуры оказывается мало. Тут на помощь приходит команда \ef для редактирования. Передайте ей имя редактируемой функции в качестве первого параметра, и она откроется в $EDITOR. Если определение функции было задано с помощью CREATE OR REPLACE FUNCTION , то после сохранения и закрытия редактора она будет обновлена. Если же вам нужно только посмотреть описание функции, то закройте редактор с помощью ненулевого кода возврата (для vim это :cq ). Таким образом она не будет обновлена и выполнена. psql в таком случае понимает, что что-то пошло не так, и не будет ничего делать.

Построение запросов

psql превосходно подходит для построения небольших запросов, однако для многострочных и вложенных надо использовать более адекватный инструмент. Например, \e - она открывает последний запрос в вашем любимом редакторе, который может уже обеспечить и подсветку синтаксиса, и автодополнение, и прочие полезные вещи. Для выполнения достаточно сохранить этот псевдо-файл и выйти (ну прям как с git). \e удобно использовать для итеративного построения большого запроса.

В режиме такого редактирования можно также сохранить запрос в какой-нибудь отдельный файл на диске. Открыть же его можно с помощью команды \i filename.sql . psql прочитает запрос из filename.sql и выполнит его.

\e работает только с уже существующими файлами. Передавая имя в качестве аргумента, вы откроете файл в текстовом редакторе и выполните содержимое при выходе. Но как быть когда вы только начинаете писать новый запрос? Выходить из psql, создавать файл и возвращаться обратно слишком сложно. Можно выполнить команду оболочки прямо из psql с помощью \! , например, \! touch foo.sql .

\i и \e существенно упрощают работу над сложными sql запросами. Лично у меня открыто 2 окна side-by-side: один с vim, а другой с psql, где я выполняю только \i query.sql . Переключаюсь - правлю, переключаюсь - выполняю. Прошли те времена, когда я копипастил из java-приложений с помощью незнакомых сочетаний горячих клавиш.

Сравнение вывода

Когда вы запускаете команду с помощью psql, результат оказывается в том же терминале. Команда \o позволяет указать имя файла, куда будет записан вывод. \o output.txt перенаправит вывод, а \o вернёт всё обратно.

Ну и зачем это надо? Некоторые используют для экспорта данных (позже покажу способ получше). Я же использую \o для сравнение чего-либо тяжёлого, например, планов выполнения с помощью \! vimdiff .

Теперь я могу просмотреть оба плана выполнения одновременно и легко выявить различия.

Клонирование базы данных

Время от времени приходится создавать миграции для баз данных. Процесс разработки такой миграции полон боли и ошибок, а это значит, что я создаю их итерационно, проверяя каждую написанную команду. Я накатываю и откатываю миграцию много раз, убеждаясь что ничего не ломается.

Локальная база данных, на которой я тренируюсь, обычно заполнена вручную или восстановлена из дампа с прода. Т.к. я гоняю преобразование схемы много раз, то обязательно случается, что данные приходят в некорректное состояние. В таком случае я восстанавливаю базу из дампа, что может отнимать достаточно много времени.

В данном случае проще будет воспользоваться клонированием базы данных, а если что-то пойдёт не так, то восстановить из бэкапа. Благо это делается в пару строчек. Для копирования достаточно указать существующую в качестве шаблона.

Для восстановления удалим существующую и склонируем обратно.

Получение данных

Зачастую нам нужно поделиться данными из postgres с сотрудниками, руководством или клиентами, причём желательно в каком-нибудь удобочитаемом формате типа CSV или Excel. Вы уже подготовили запрос, свели всё в одну таблицу, осталось только куда-нибудь это выгрузить. Погуглив psql csv export, можно найти 2 способа. Первый более примитивный.

CSV фйалы представляют собой просто строки со значениями, которые разделены запятыми. Таким образом, указав psql как форматировать вывод, можно получить похожую структуру. Тут есть одна большая проблема - если ваши данные содержат запятую, то её нужно экранировать, а т.к. количество полей может быть огромным, то такой способ ведёт в тупик.

Правильным способом будет выгрузка в CSV с помощью мета-команды \copy или SQL команды COPY . выглядит это примерно так:

COPY накладывает несколько ограничений. Во-первых, путь к файлу должен быть абсолютным. Во-вторых, вы можете писать только на локальную файловую систему. То есть подключиться к удалённой БД и выгрузить данные на локальный компьютер не получится. И тут на помощь приходит \copy, которая представляет из себя всего лишь более удобную оболочку для COPY. Запрос выше можно переписать так:

Обратите внимание, что используется относительный путь до файла. Также можно задать и другие параметры CSV. Единственное ограничение состоит в том, что команда должна быть одной строкой. Причина в том, что окончанием выражения для команды с \ является перевод строки, а не точка с запятой. Это не касается \e - там управление передаётся в редактор. Под капотом \copy выполняет всё тот же COPY, перенаправляя вывод в STDOUT вместо файла. Далее psql забирает со STDOUT и записывает в локальный файл. Если вам нужно выгрузить данные в Excel, то убедитесь, что задали правильную кодировку. Он не дружит с UTF-8, так что лучше откатиться до latin1 (2015 год на дворе, я солидарен с негодованиями автора):

Если sql-запрос у вас находится в файле, то использовать его для команды \copy не получится. Вам придётся скопировать всё тело запроса в выражение в команде, удалив переносы строк.

В случае запроса из файла:

Параметры вызова утилиты совпадают с параметрами psql. Вы можете заменить вызов psql на psql2csv, передав запрос в качестве аргумента, а на выходе получить валидный CSV. Но это ещё не всё - почему бы не подать вывод на вход какой-нибудь другой утилите?! psql2csv также принимает аргументы --no-header --encoding=ENCODING для совместимости с Excel.

Я уже очень привык работать с PostgreSQL через командную строку. Всё, что раньше делал в pgAdmin, можно делать и тут, причём быстрее. Я надеюсь, что эта статья убедила вас сделать psql основным инструментом для работы с PostgreSQL, показав удобство и гибкость.

Для управления сервером баз данных PostgreSQL есть много разных инструментов, но при установки сервера по умолчанию устанавливается только утилита psql. Это консольная утилита с помощью которой можно подключится к серверу баз данных и начать с ним работать.

Официальную документацию по этой утилите вы можете найти тут и тут! А в этой статье даны некоторые практические примеры для её использования.

Подключение к серверу баз данных

Итак, утилита psql позволяет вам управлять сервером баз данных PostgreSQL. Но вначале нужно подключиться к серверу. Подключиться с помощью этой утилиты вы можете к локально установленному серверу, или удалённому используя сеть. Подключение выполняется таким способом:

По умолчанию при подключении вы используете:

Таким образом если вы в системе находитесь под пользователем postgres, то следующие команды будут равнозначными:

Для получения информации о узле к которому вы подключены используется команда \conninfo:

Не выходя из терминала psql вы можете подключится к другому узлу с помощью команды \c <база> <роль> <узел> <порт> .

Чтобы выйти из терминала psql можно использовать 2 равносильные команды \q и exit .

Получение справочной информации

Получить справку о psql из ОС можно следующими способами:

Вы можете получить справку находясь в терминале psql такими способами:

Файлы, которые использует psql

.psqlrc

При входе в psql будут выполнятся команды записанные в

/.psqlrc. Этого файла может и не быть, но если он будет создан в домашнем каталоге пользователя, то psql будет его читать при каждом подключении к серверу.

Вот некоторые примеры настроек, которые можно ввести в

Настраивая приглашение PROMPT1 и PROMPT2 используются следующие символы:

В качестве практики давайте настроим свой .psqlrc:

Как видим выше, после каждого запроса выводится время его выполнения. Также в приглашении ввода команды видно имя пользователя и имя базы.

.psql_history

Другой полезный файл это

/.psql_history. В нем хранится история команд введенных в терминале psql. Перемещаться по истории команд в терминале psql можно клавишами вверх и вниз. Количество хранимых команд изменяется установкой переменной HISTSIZE.

Формат выводимой информации

Когда вы вводите и выполняете какой-нибудь запрос в терминале psql, то в выводе видите результат этого запроса. По умолчанию такой вывод показывается в форме таблички. Но вы можете настроить формат выводимой информации сами:

Например, получим информацию из представления pg_tables. Затем поменяем формат и снова получим информацию. А затем все вернем на место:

А вот пример использования расширенного режима (\x). Выглядит это так, как будто табличку перевернули:

Взаимодействие psql с операционной системой

Можно установить переменную окружения в систему с помощью команды \setenv:

В предыдущем листинге с помощью последней команды мы выключили расширенный режим.

В примере выше мы проделали следующее:

Переменные окружения

В примере выше мы результат функции now() записали в переменную curr_time!

Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:

Для установки PostgreSQL сервера:

Проверим, можем ли мы подключиться к базе данных PostgreSQL:

Вывод команды должен быть примерно таким:

PostgreSQL Подключение, Пользователи (Роли) и Базы Данных

Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:

Для подключения к базе данных PostgreSQL можно использовать команду:

Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.

После ввода пароля и успешного подключения к базе данных PostgreSQL, можно посылать SQL-запросы и psql-команды.

PostgreSQL создание новой роли и базы данных

Создать новую роль c именем admin (указывайте нужное имя):

Создание новой базы данных:

Дать права роли на базу данных:

Включить удаленный PostgreSQL доступ для пользователей

Нам нужно отредактировать файл /etc/postgresql/<VERSION>/main/pg_hba.conf, задав опцию md5 вместо peer.

<VERSION> может быть 10, 11, 12 и т.д.

После этого сделать restart PostgreSQL:

Полезные команды PostgreSQL

Выйти из клиента PostgreSQL:

Показать список баз данных PostgreSQL:

Показать список таблиц:

\dt

Показать список пользователей (ролей):

\du

Показать структуру таблицы:

Переименовать базу данных:

Удалить базу данных:

Изменить текущую базу данных в 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 ):

Выбор shema psql в консоли:

Посмотреть список всех схем:

Подключиться к конкретной схеме:

Sequences

Получить имена всех созданных sequences:

Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи: