Linux

Основы работы с psql

Практическое руководство по клиенту psql: подключение к PostgreSQL, метакоманды, выполнение SQL, форматирование вывода и работа со скриптами.

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

В этой инструкции рассматриваются только базовые приёмы работы с psql.

Проверено на: Ubuntu Server 24.04 LTS
Уровень сложности: начальный
Время выполнения: около 20 минут
Требуемый доступ: установленный клиент psql и доступ к PostgreSQL

Что будет рассмотрено

После выполнения инструкции можно будет:

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

Проверка клиента psql

psql --version

Пример формата вывода:

psql (PostgreSQL) VERSION

Если команда не найдена:

sudo apt update
sudo apt install -y postgresql-client

Подключение от имени postgres

На сервере PostgreSQL:

sudo -u postgres psql

Ожидается приглашение:

postgres=#

Подключение к конкретной базе

sudo -u postgres   psql   -d app_db

Подключение по TCP

psql   -h 127.0.0.1   -p 5432   -U app_user   -d app_db

Параметры:

-h — адрес сервера
-p — порт
-U — роль PostgreSQL
-d — база данных

Краткая форма строки подключения

psql   'postgresql://app_user@127.0.0.1:5432/app_db'

Если пароль передаётся в URI, специальные символы должны быть URL-encoded.

Не размещайте реальный пароль в shell history.

Проверка текущего подключения

После входа:

\conninfo

Команда покажет:

базу;
пользователя;
host;
порт;
способ подключения.

Текущее имя базы и роли

SELECT
    current_database(),
    current_user;

Приглашение psql

Примеры:

postgres=#
app_db=>

Символ:

обычно указывает на суперпользователя.

Символ:

обычно отображается для обычной роли.

Завершение SQL-команды

SQL-команда завершается точкой с запятой:

SELECT now();

Если ; не указана, psql продолжает ожидать ввод.

Отмена незавершённой команды

Нажмите:

Ctrl+C

Это очищает текущий буфер запроса и возвращает обычное приглашение.

Выполнение простого запроса

SELECT version();

Текущая дата и время:

SELECT now();

Текущая база:

SELECT current_database();

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

Команды, начинающиеся с:

являются метакомандами psql.

Они не являются SQL и обычно не требуют ;.

Пример:

\l

Справка по метакомандам

\?

Справка по конкретной SQL-команде:

\h CREATE TABLE

Общая SQL-справка:

\h

Список баз данных

\l

Расширенный вариант:

\l+

Можно указать шаблон:

\l app*

Переключение между базами

\c app_db

С указанием роли:

\c app_db app_user

Переключение создаёт новое подключение.

Список ролей

\du

Расширенный вывод:

\du+

Конкретная роль:

\du app_user

Список схем

\dn

Расширенный вывод:

\dn+

Список таблиц

\dt

Таблицы конкретной схемы:

\dt public.*

Все таблицы во всех доступных схемах:

\dt *.*

Список представлений

\dv

Материализованные представления:

\dm

Список последовательностей

\ds

Список индексов

\di

Индексы конкретной таблицы:

\di public.test_items*

Описание таблицы

\d public.test_items

Расширенный вариант:

\d+ public.test_items

Показываются:

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

Описание всех объектов по шаблону

\d public.*

Список функций

\df

Функции конкретной схемы:

\df public.*

Список расширений

\dx

Доступные расширения:

SELECT
    name,
    default_version,
    installed_version
FROM pg_available_extensions
ORDER BY name;

Просмотр search_path

SHOW search_path;

Переключение схемы

На время текущего сеанса:

SET search_path TO app, public;

Проверка:

SHOW search_path;

Создание тестовой таблицы

CREATE TABLE public.psql_test (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Добавление данных

INSERT INTO public.psql_test (name)
VALUES
    ('Первая запись'),
    ('Вторая запись');

Просмотр данных

SELECT *
FROM public.psql_test
ORDER BY id;

Вывод только нужных столбцов

SELECT
    id,
    name
FROM public.psql_test
ORDER BY id;

Ограничение количества строк

SELECT *
FROM public.psql_test
ORDER BY id
LIMIT 10;

Расширенный вертикальный вывод

Для широких строк:

\x

Повторная команда выключает режим.

Автоматический режим:

\x auto

В нём psql сам выбирает обычное или вертикальное представление.

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

Обычный aligned-режим:

\a

Повторная команда переключает выравнивание.

Проверка текущих настроек:

\pset

Невыровненный вывод

\pset format unaligned

Разделитель столбцов:

\pset fieldsep '|'

Пример:

SELECT id, name
FROM public.psql_test;

Возврат к обычному формату:

\pset format aligned

CSV-вывод

\pset format csv

Проверка:

SELECT id, name
FROM public.psql_test;

Возврат:

\pset format aligned

Заголовки столбцов

Отключить:

\t

Повторная команда включает их обратно.

Пейджер

psql может использовать less для длинного вывода.

Отключить:

\pset pager off

Включить:

\pset pager on

Автоматический режим:

\pset pager always

Тайминг запросов

Включить:

\timing

После этого psql показывает время выполнения каждого запроса.

Повторная команда отключает режим.

Повтор предыдущего запроса

\g

Команда выполняет текущий буфер SQL.

Просмотр текущего буфера

\p

Очистка буфера

\r

Редактирование запроса

\e

psql откроет текущий запрос во внешнем редакторе.

Редактор определяется переменными:

PSQL_EDITOR
EDITOR
VISUAL

Выполнение команды shell

\! pwd

Список файлов:

\! ls -la

Команды shell выполняются от текущего Linux-пользователя.

Просмотр переменных psql

\set

Создание переменной

\set app_name 'example'

Использование:

SELECT :'app_name';

Переменная с идентификатором

\set table_name 'psql_test'

SELECT *
FROM public.:"table_name";

Используйте правильный вид подстановки:

:'name' — строковое значение
:"name" — SQL-идентификатор

Выполнение одной команды из shell

sudo -u postgres   psql   -d app_db   -c   'SELECT current_database(), current_user;'

Несколько команд через -c

sudo -u postgres   psql   -d app_db   -c 'SELECT now();'   -c 'SELECT current_user;'

Каждый параметр -c выполняется отдельно.

Вывод только значения

sudo -u postgres   psql   -d app_db   -Atc   'SELECT current_database();'

Параметры:

-A — unaligned
-t — без заголовков и служебных строк
-c — выполнить команду

Это удобно для shell-скриптов.

Остановка при первой ошибке

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

psql   -v ON_ERROR_STOP=1   -d app_db   -f script.sql

Без ON_ERROR_STOP psql может продолжить выполнение после SQL-ошибки.

Создание SQL-файла

cat > /tmp/psql-test.sql <<'EOF'
SELECT current_database();
SELECT current_user;
SELECT now();
EOF

Запуск SQL-файла

sudo -u postgres   psql   -d app_db   -f /tmp/psql-test.sql

Запуск файла внутри psql

\i /tmp/psql-test.sql

Относительный include

\ir child-script.sql

\ir ищет файл относительно каталога текущего SQL-скрипта.

Это удобно для набора миграций.

Вывод результата в файл

Внутри psql:

\o /tmp/query-result.txt

После этого:

SELECT *
FROM public.psql_test;

Вернуть вывод в терминал:

\o

Проверить файл:

\! cat /tmp/query-result.txt

Вывод одного запроса в файл через \g

SELECT *
FROM public.psql_test
\g /tmp/psql-test.txt

Экспорт в CSV через \copy

\copy (
    SELECT id, name, created_at
    FROM public.psql_test
    ORDER BY id
) TO '/tmp/psql-test.csv'
WITH (
    FORMAT csv,
    HEADER true
);

\copy читает и записывает файлы от имени локального пользователя, запустившего psql.

Отличие COPY от \copy

COPY

работает с файлами на стороне сервера PostgreSQL и требует соответствующих прав.

\copy

работает через клиент psql и использует локальную файловую систему клиента.

Для обычного администратора \copy часто удобнее.

Импорт CSV через \copy

Пример файла:

cat > /tmp/psql-import.csv <<'EOF'
name
Третья запись
Четвёртая запись
EOF

В psql:

\copy public.psql_test(name)
FROM '/tmp/psql-import.csv'
WITH (
    FORMAT csv,
    HEADER true
);

Проверка:

SELECT *
FROM public.psql_test
ORDER BY id;

Просмотр истории

История команд обычно хранится в:

~/.psql_history

Проверьте:

ls -la ~/.psql_history

Не вводите пароли и секреты как обычный SQL-текст.

Отключение истории для текущего сеанса

Перед запуском:

PSQL_HISTORY=/dev/null psql

Или внутри shell:

export PSQL_HISTORY=/dev/null

После завершения:

unset PSQL_HISTORY

Настройки через .psqlrc

Пользовательский файл:

~/.psqlrc

Пример:

cat > ~/.psqlrc <<'EOF'
\set QUIET 1
\timing on
\x auto
\pset pager off
\set QUIET 0
EOF

Не добавляйте настройки, мешающие автоматическим скриптам, без проверки.

Отключение .psqlrc

psql -X

Параметр:

-X

запрещает чтение startup-файлов.

Это полезно в скриптах и при диагностике.

Проверка кода завершения

psql   -v ON_ERROR_STOP=1   -d app_db   -c 'SELECT 1;'

Затем:

echo $?

Успешный код:

Проверка ошибки

psql   -v ON_ERROR_STOP=1   -d app_db   -c 'SELECT * FROM missing_table;'

Проверьте:

echo $?

Ненулевой код означает ошибку.

Выполнение транзакционного скрипта

psql   -v ON_ERROR_STOP=1   --single-transaction   -d app_db   -f migration.sql

Параметр:

--single-transaction

оборачивает файл в одну транзакцию, если команды совместимы с транзакционным выполнением.

Ручная транзакция

BEGIN;

Выполните изменения:

UPDATE public.psql_test
SET name = 'Изменённая запись'
WHERE id = 1;

Проверьте:

SELECT *
FROM public.psql_test
WHERE id = 1;

Отменить:

ROLLBACK;

Подтвердить:

COMMIT;

Проверка состояния транзакции

Приглашение psql может изменяться.

Пример:

app_db=*>

Звёздочка означает активную транзакцию.

После ошибки внутри транзакции:

app_db=!>

Транзакция находится в ошибочном состоянии и требует:

ROLLBACK;

Автокоммит

По умолчанию каждая отдельная SQL-команда автоматически фиксируется, если она не находится внутри:

BEGIN;

Для опасных изменений используйте явную транзакцию.

Отмена выполняющегося запроса

Нажмите:

Ctrl+C

psql отправит запрос на отмену текущей операции.

Сеанс обычно останется подключённым.

Завершение сеанса

\q

Также можно нажать:

Ctrl+D

Предпочтительнее использовать \q.

Проверка активности подключения

SELECT pg_backend_pid();

Показывает PID backend-процесса текущего сеанса.

Показ текущих параметров

SHOW ALL;

Для одного параметра:

SHOW statement_timeout;

Временное изменение параметра сеанса

SET statement_timeout = '5s';

Проверка:

SHOW statement_timeout;

Сброс:

RESET statement_timeout;

Изменение действует только в текущем сеансе.

Установка application_name

При подключении:

PGAPPNAME=manual-psql psql   -h 127.0.0.1   -U app_user   -d app_db

Проверка:

SELECT current_setting('application_name');

Это помогает отличать ручные подключения в pg_stat_activity.

Типичные проблемы

psql подключается не к той базе

Проверьте:

\conninfo

Или:

SELECT current_database();

Всегда указывайте -d в административных командах.

role does not exist

При запуске:

psql

клиент пытается использовать имя текущего Linux-пользователя как роль PostgreSQL.

Укажите:

psql -U app_user -d app_db

или:

sudo -u postgres psql

database does not exist

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

Явно укажите:

psql -U app_user -d app_db

peer authentication failed

Подключение через Unix socket регулируется peer-аутентификацией.

Для административного доступа:

sudo -u postgres psql

Для парольного подключения используйте TCP и корректное правило pg_hba.conf.

password authentication failed

Проверьте:

роль;
пароль;
host;
базу;
pg_hba.conf;
метод аутентификации.

Команда не выполняется

Проверьте наличие:

Просмотрите буфер:

\p

Очистите:

\r

Вывод завис в less

Нажмите:

Отключите pager:

\pset pager off

Скрипт продолжает работу после ошибки

Запускайте:

psql -v ON_ERROR_STOP=1

CSV содержит оформление таблицы

Используйте:

\copy

или:

psql --csv

а не обычный aligned-вывод.

Пароль попал в history

Смените пароль роли.

Удаление строки из истории не гарантирует, что секрет не сохранился в других журналах или резервных копиях.

Удаление тестовых объектов

Подключитесь к app_db:

sudo -u postgres psql -d app_db

Удалите таблицу:

DROP TABLE IF EXISTS public.psql_test;

Удалите временные файлы:

\! rm -f   /tmp/psql-test.sql   /tmp/psql-test.txt   /tmp/psql-test.csv   /tmp/psql-import.csv   /tmp/query-result.txt

Быстрый набор команд

Подключение:

psql   -h 127.0.0.1   -U app_user   -d app_db

Информация о подключении:

\conninfo

Базы:

\l

Роли:

\du

Таблицы:

\dt

Описание таблицы:

\d+ public.TABLE_NAME

Тайминг:

\timing

Выход:

\q

Запуск файла:

psql   -v ON_ERROR_STOP=1   -d app_db   -f script.sql

Итог

После выполнения инструкции:

выполнено подключение к PostgreSQL через psql;
изучены основные параметры подключения;
рассмотрены ключевые метакоманды;
настроены форматы вывода;
выполнены SQL-команды и транзакции;
запущены SQL-файлы;
выполнен импорт и экспорт CSV;
рассмотрены переменные, история и .psqlrc;
разобраны основные ошибки клиента.

Следующим этапом можно настроить PostgreSQL для удалённого подключения с другого сервера.