psql - это штатный текстовый клиент postgresql, и для администратора (DBA) он остается главным инструментом на каждый день. Через него вы подключаетесь к серверу, смотрите список баз и таблиц, гоняете запросы, запускаете SQL-скрипты и разбираетесь, что вообще происходит внутри. В этом уроке мы разберем, как соединиться с сервером разными способами, какие метакоманды экономят часы работы, как настроить вывод и поведение клиента под себя через psqlrc и переменные. Цель - чтобы консоль перестала пугать и стала удобной.
Как это работает
psql - обычная клиентская программа: она открывает сетевое соединение к серверному процессу postgres и обменивается с ним сообщениями по протоколу. Все, что вы вводите, делится на две части. Строки, начинающиеся с обратной косой черты (\), - это метакоманды, их обрабатывает сам psql на вашей стороне. Все остальное - это SQL, который клиент отправляет на сервер и ждет ответ.
Чтобы соединиться, серверу нужно знать четыре вещи: адрес хоста, порт, имя пользователя и имя базы. Для этого есть флаги -h (host), -p (port), -U (user), -d (dbname). Если что-то не указать, psql берет значение по умолчанию: хост - локальный сокет, порт 5432, а имя пользователя и базы совпадают с вашим системным логином. Поэтому на своей машине часто хватает просто psql.
Важно понимать разницу между подключением через Unix-сокет и через TCP. Без -h psql идет в локальный сокет, и тут обычно работает аутентификация peer (сервер просто доверяет вашему системному пользователю). С -h localhost соединение идет уже по TCP, и сервер чаще требует пароль. Это типичная путаница у новичков: одна и та же команда то просит пароль, то нет.
Метакоманды осмотра (их называют backslash-команды) - это удобные обертки над системными каталогами. Когда вы пишете \dt, psql под капотом выполняет SELECT по pg_class и pg_namespace и красиво печатает результат. Можно запустить psql с флагом -E, и он будет показывать сам скрытый SQL каждой метакоманды - отличный способ научиться читать системные каталоги.
Пароль в открытую вводить неудобно и небезопасно. Для этого есть файл .pgpass в домашнем каталоге со строками формата host:port:database:user:password. psql сам подставит подходящий пароль. Файл обязан иметь права 0600, иначе клиент его проигнорирует. На современных серверах (PG 14+) метод хранения пароля по умолчанию - scram-sha-256, более стойкий, чем старый md5.
SQL и примеры
Подключение разными способами. Первая строка - полная форма с флагами, вторая - то же самое одной строкой подключения (connection URI):
Код: Выделить всё
psql -h db.local -p 5432 -U app_user -d demo
psql "postgresql://app_user@db.local:5432/demo?sslmode=require"
Код: Выделить всё
\l
\dt bookings.*
\d bookings.flights
Осмотр пользователей, функций и схем. \du - роли и их атрибуты, \df - функции, \dn - схемы:
Код: Выделить всё
\du
\df bookings.*
\dn+
Код: Выделить всё
SELECT a.airport_name, count(*) AS departures
FROM bookings.flights f
JOIN bookings.airports a ON a.airport_code = f.departure_airport
GROUP BY a.airport_name
ORDER BY departures DESC
LIMIT 5;
Код: Выделить всё
\x
SELECT * FROM bookings.tickets LIMIT 1;
\x
Код: Выделить всё
\timing on
\i /home/dba/reports/daily.sql
Код: Выделить всё
\set port_min 1000
SELECT count(*) FROM bookings.tickets WHERE ticket_no > :'port_min';
Код: Выделить всё
\set PROMPT1 '%n@%/%R%# '
\timing on
\pset null '(null)'
\set HISTSIZE 5000
Частые грабли
- Забыли точку с запятой - psql не отправляет запрос на сервер и молча ждет продолжения, показывая приглашение со скобкой или кавычкой. Кажется, что зависло, а на деле он ждет конца команды.
- Путаница peer и password: psql без -h идет в сокет (часто peer, без пароля), а psql -h localhost - по TCP (просит пароль). Это не баг, это разные правила в pg_hba.conf.
- Файл .pgpass с правами шире 0600 тихо игнорируется. Никакой ошибки, просто снова спрашивает пароль.
- \d без аргумента показывает только объекты текущей схемы из search_path. Таблицу из другой схемы не видно - пишите \dt bookings.* или \dt *.* для всех схем.
- \dt не показывает индексы, представления и последовательности. Для них свои буквы: \di, \dv, \ds. Буква s в конце (\dtS) добавляет системные объекты.
- ON_ERROR_STOP по умолчанию выключен: скрипт через \i после ошибки продолжает выполняться дальше. Для миграций ставьте \set ON_ERROR_STOP on, иначе половина скрипта молча применится.
- Подключитесь к своей базе двумя способами: через флаги -U -d и через строку подключения postgresql://. Сравните, у какого варианта спрашивается пароль.
- Создайте файл ~/.pgpass с одной строкой под свой сервер, выставьте chmod 600 и проверьте, что пароль больше не спрашивается.
- Запустите psql с флагом -E, выполните \dt и \du, прочитайте сгенерированный SQL к системным каталогам.
- Выполните любой SELECT на одну строку, включите \x и повторите - сравните плоский и вертикальный вывод.
- Сохраните пару запросов в файл script.sql, добавьте в начало \timing on и запустите его через \i.
- Создайте ~/.psqlrc с настройкой \pset null '(null)' и убедитесь, что NULL теперь виден в выводе.
- Чем отличается подключение через Unix-сокет от подключения по TCP с -h localhost и почему в одном случае пароль спрашивают, а в другом нет?
- Какие метакоманды покажут таблицы, индексы, представления и роли, и как заставить их вывести объекты из конкретной схемы?
- Зачем нужен флаг -E при запуске psql и чему он помогает научиться?
- Какие требования к файлу .pgpass обязательны, чтобы psql его использовал?
- Что делает \set ON_ERROR_STOP on и почему это критично при запуске миграций через \i?
- В каком файле задаются настройки клиента по умолчанию и как через него включить \timing на каждом старте?
