`psql`

Консольный клиент PostgreSQL — подключение, мета-команды \l \dt \d \du, запросы из скриптов, роли и права

psql подключается к серверу PostgreSQL и выполняет SQL. Кроме SQL понимает мета-команды с обратной косой чертой: \dt, \d, \du показывают структуру базы без запросов к системным таблицам.

Синтаксис

psql [-h хост] [-p порт] [-U пользователь] [-d база] [-c 'SQL'] [-f файл.sql]
psql 'postgresql://пользователь:пароль@хост:порт/база'

Без -c и -f открывается интерактивная консоль (выход — \q). Если -d не указан, psql подключается к базе с тем же именем, что и пользователь.

Опции

Подключение

Опция	Что делает
`-h хост`	подключение по TCP; без `-h` — через Unix-сокет на этой машине
`-p порт`	порт сервера (по умолчанию 5432)
`-U имя`	роль PostgreSQL; по умолчанию — имя пользователя ОС
`-d база`	база данных
`-W` / `-w`	всегда спрашивать пароль / никогда не спрашивать (для скриптов)
`-l`	показать список баз и выйти

Выполнение

Опция	Что делает
`-c 'SQL'`	выполнить команду и выйти; можно повторять несколько раз
`-f файл`	выполнить SQL из файла (`-f -` — из stdin)
`-v ON_ERROR_STOP=1`	остановиться на первой ошибке и вернуть ненулевой код (для `-f` — 3)
`-1`	выполнить весь `-f`/`-c` одной транзакцией: ошибка откатит всё
`-v имя=значение`	задать переменную psql; в файле она подставляется как `:имя` или `:'имя'`
`-X`	не читать `~/.psqlrc`
`-q`	не печатать служебные ответы вроде `CREATE TABLE`
`-E`	показать SQL, который выполняют мета-команды

Формат вывода

Опция	Что делает
`-A`	без выравнивания
`-t`	только строки: без заголовков и `(N rows)`
`-Atc 'SQL'`	частая связка для скриптов: голое значение без рамок
`-F ','`	разделитель полей в режиме `-A` (по умолчанию `\|`)
`--csv`	вывод в CSV
`-x`	вертикальный вывод: каждая колонка на своей строке
`-o файл`	результат запросов — в файл

Мета-команды

Структура

Команда	Что показывает
`\l` / `\l+`	список баз (с размерами)
`\c база [роль]`	переключиться на другую базу
`\dt` / `\dt+`	таблицы текущей базы (с размерами); `\dt схема.*` — в схеме
`\d`	все таблицы, представления, последовательности
`\d таблица` / `\d+ таблица`	колонки, типы, значения по умолчанию, индексы, ограничения
`\di`, `\dv`, `\ds`, `\df`	индексы, представления, последовательности, функции
`\dn`	схемы
`\du` (или `\dg`)	роли и их атрибуты
`\dp таблица` (или `\z`)	права на таблицу (кто что может)
`\conninfo`	к какой базе и под какой ролью подключён клиент

Работа в консоли

Команда	Что делает
`\x`	переключить вертикальный вывод
`\timing on`	показывать время выполнения запросов
`\pset format csv`	сменить формат вывода (`aligned`, `unaligned`, `csv`, `html`…)
`\i файл.sql`	выполнить файл
`\o файл` / `\o`	писать результаты в файл / вернуть на экран
`\copy таблица TO 'файл' CSV HEADER`	выгрузить таблицу в файл на стороне клиента
`\copy таблица FROM 'файл' CSV HEADER`	загрузить файл в таблицу
`\password роль`	сменить пароль, не показывая его в истории
`\h команда` / `\?`	справка по SQL-команде / по мета-командам
`\q`	выход

Роли и права (SQL)

Запрос	Что делает
`CREATE ROLE app LOGIN PASSWORD 'pw'`	роль, которая может входить (`CREATE USER` — то же с `LOGIN` по умолчанию)
`CREATE ROLE readers NOLOGIN`	групповая роль без входа
`ALTER ROLE app CREATEDB` / `SUPERUSER`	выдать атрибут роли
`ALTER ROLE app PASSWORD 'new'`	сменить пароль
`GRANT readers TO app`	включить роль в группу: права группы наследуются
`CREATE DATABASE shop OWNER app`	база, владелец которой — роль
`GRANT CONNECT ON DATABASE shop TO app`	право подключаться к базе
`GRANT USAGE ON SCHEMA public TO app`	право обращаться к объектам схемы
`GRANT SELECT, INSERT ON orders TO app`	права на таблицу: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `ALL`
`GRANT SELECT ON ALL TABLES IN SCHEMA public TO app`	на все существующие таблицы схемы
`GRANT USAGE ON SEQUENCE orders_id_seq TO app`	нужно для `INSERT` в таблицу с `serial`
`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app`	права на таблицы, созданные в будущем
`ALTER TABLE orders OWNER TO app`	сменить владельца
`REVOKE SELECT ON orders FROM app`	отозвать право
`DROP ROLE app`	удалить роль (сначала отзови её права)

Утилиты рядом с psql

Команда	Что делает
`createdb база` / `dropdb база`	создать / удалить базу
`createuser --login --pwprompt имя`	создать роль с паролем
`pg_isready [-h хост]`	принимает ли сервер подключения (код 0 — да)
`pg_dump база > dump.sql`	выгрузка базы в SQL; `-Fc` — в сжатый формат для `pg_restore`
`pg_restore -d база dump.file`	загрузка из формата `-Fc`

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

PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD заменяют соответствующие флаги. Пароль можно хранить в ~/.pgpass строкой хост:порт:база:роль:пароль; файл должен иметь права 600, иначе psql его проигнорирует.

Примеры

# запустить сервер в песочнице и проверить, что он принимает подключения
service postgresql start
pg_isready

# root не является ролью PostgreSQL — работаем от системного пользователя postgres
sudo -u postgres psql -c '\l'

# создать базу и посмотреть её таблицы
sudo -u postgres createdb shop
sudo -u postgres psql -d shop -c '\dt'

# структура таблицы
sudo -u postgres psql -d shop -c '\d orders'

# одно значение без рамок — удобно присвоить переменной
count=$(sudo -u postgres psql -d shop -Atc 'SELECT count(*) FROM orders')

# выполнить файл одной транзакцией и остановиться на первой ошибке
sudo -u postgres psql -d shop -1 -v ON_ERROR_STOP=1 -f schema.sql

# подключиться по TCP под другой ролью; пароль — из переменной окружения
PGPASSWORD='secret' psql -h localhost -U app -d shop -c 'SELECT current_user'

# то же через URI
psql 'postgresql://app:secret@localhost:5432/shop' -c '\conninfo'

# выгрузить результат в CSV-файл на стороне клиента
sudo -u postgres psql -d shop -c "\copy (SELECT * FROM orders) TO '/tmp/orders.csv' CSV HEADER"

# резервная копия и восстановление в другую базу
sudo -u postgres pg_dump -Fc shop > /tmp/shop.dump
sudo -u postgres createdb shop_copy
sudo -u postgres pg_restore -d shop_copy /tmp/shop.dump

Выдача прав роли на чтение:

CREATE ROLE app LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app;

Подводные камни

role "root" does not exist. Без -U клиент берёт имя пользователя ОС. Под root работай через sudo -u postgres psql или указывай -U.
Сокет и TCP аутентифицируются по-разному. Без -h используется метод peer: имя роли должно совпадать с пользователем ОС. С -h localhost спрашивается пароль. Правила — в pg_hba.conf (путь покажет SHOW hba_file). После правки нужна перезагрузка конфигурации сервера.
Без -d база = имя роли. psql -U app упадёт с database "app" does not exist, если такой базы нет.
Несколько -c не прерываются на ошибке, и код возврата всё равно 0. В скриптах добавляй -v ON_ERROR_STOP=1.
GRANT … ON ALL TABLES касается только уже существующих таблиц. Таблицы, созданные позже, останутся недоступны. Для них нужен ALTER DEFAULT PRIVILEGES — он действует на таблицы, которые потом создаст роль, выполнившая эту команду.
INSERT в таблицу с serial требует ещё и права на последовательность: GRANT USAGE ON SEQUENCE ….
DROP ROLE откажет, пока у роли есть права или объекты: role … cannot be dropped because some objects depend on it. Сначала отзови права (REVOKE) или в каждой базе выполни REASSIGN OWNED BY роль TO postgres и DROP OWNED BY роль.
Имена без кавычек приводятся к нижнему регистру. SELECT * FROM Orders ищет orders, а "Orders" — именно с заглавной. Строки пиши в одинарных кавычках.
COPY … FROM '/путь' читает файл на сервере от имени процесса PostgreSQL и требует особых прав. Файл на своей стороне загружай через \copy.
Переменные :имя не подставляются в -c. Они работают в файлах (-f) и в интерактивной консоли.

См. также

sqlite3 · sudo · service · variables