Вокруг psql есть набор консольных утилит PostgreSQL для типовых операций без SQL: createdb/dropdb — создать и удалить базу, createuser — роль, pg_dump/pg_restore/pg_dumpall — резервные копии, pg_isready — проверка, что сервер принимает подключения. pg_lsclusters и pg_ctlcluster — обёртки Debian/Ubuntu для управления экземплярами («кластерами») сервера.
Синтаксис
createdb [опции подключения] [-O владелец] [-E кодировка] [-T шаблон] имя_базы
dropdb [опции подключения] [--if-exists] [-f] имя_базы
pg_dump [опции подключения] [опции] имя_базы
pg_restore [опции подключения] [-d база] [опции] файл
pg_isready [-h хост] [-p порт] [-t сек] [-q]
pg_ctlcluster версия кластер start|stop|restart|reload|status
pg_lsclusters
Подключение (общее для всех)
| Опция | Что задаёт | Переменная окружения |
|---|
-h хост | хост или каталог сокета; без него — Unix-сокет /var/run/postgresql | PGHOST |
-p порт | порт (5432) | PGPORT |
-U роль | от имени какой роли (по умолчанию — имя пользователя ОС) | PGUSER |
-d база | база для подключения (у pg_restore — куда восстанавливать) | PGDATABASE |
-W / -w | всегда спросить пароль / никогда не спрашивать | PGPASSWORD, ~/.pgpass |
createdb, dropdb, createuser
| Команда и опция | Что делает |
|---|
createdb имя | создать базу |
-O роль | владелец базы |
-E UTF8 | кодировка; отличная от шаблона требует -T template0 и подходящую локаль: -E LATIN1 -T template0 -l C |
-T template0 | из какого шаблона копировать |
-l локаль, --lc-collate, --lc-ctype | локаль сортировки и классов символов |
-e | показать выполненный SQL |
dropdb имя | удалить базу; --if-exists — без ошибки, если её нет; -f — оборвать чужие подключения |
createuser роль | создать роль; -d — может создавать базы, -s — суперпользователь, -P — спросить пароль |
pg_dump: резервная копия одной базы
| Опция | Что делает |
|---|
-f файл | куда писать (без неё — stdout) |
-F p | формат plain — обычный SQL-скрипт (по умолчанию); восстанавливается через psql |
-F c | custom — сжатый архив; восстанавливается через pg_restore, можно выбирать объекты |
-F d | directory — каталог с файлом на таблицу; единственный формат с параллельным -j N |
-F t | tar-архив |
-j N | дампить в N потоков (только -F d) |
-Z 0..9 | степень сжатия |
-s / -a | только схема / только данные |
-t шаблон / -T шаблон | только эти таблицы / кроме этих; повторяются, понимают * |
--exclude-table-data=шаблон | структуру взять, данные таблицы — нет (логи, кеш) |
-n схема / -N схема | только эта схема / кроме неё |
-c + --if-exists | добавить DROP … IF EXISTS перед созданием объектов |
-C | добавить CREATE DATABASE и подключение к ней |
-O | не сохранять владельцев (ALTER … OWNER) — для восстановления под другой ролью |
-x | не сохранять права (GRANT/REVOKE) |
--inserts / --column-inserts | данные как INSERT (с именами колонок) вместо COPY — медленнее, но переносимо |
-v | подробный ход работы |
pg_dumpall снимает весь сервер; pg_dumpall -g — только глобальные объекты: роли и табличные пространства.
pg_restore: восстановление из custom/directory/tar
| Опция | Что делает |
|---|
-d база | восстановить в существующую базу |
-C -d postgres | сначала создать базу (с исходным именем), подключившись к postgres |
-c + --if-exists | удалить объекты перед созданием |
-l | показать оглавление архива |
-L список | восстановить только то, что осталось в отредактированном оглавлении |
-t таблица, -n схема, -s, -a | выбрать, что восстанавливать |
-O, -x | без владельцев, без прав |
-j N | параллельно (custom и directory) |
-1 | всё в одной транзакции: либо всё, либо ничего |
-e | остановиться на первой ошибке (по умолчанию продолжает) |
-f файл | не восстанавливать, а сделать SQL-скрипт; -f - — в stdout |
pg_isready
| Опция | Что делает |
|---|
-h, -p | куда стучаться |
-t сек | сколько ждать (по умолчанию 3; 0 — бесконечно) |
-q | ничего не печатать, только код выхода |
| Код | Значение |
|---|
0 | сервер принимает подключения |
1 | сервер отвечает, но отклоняет подключения (например, ещё запускается) |
2 | ответа нет |
3 | проверка не выполнялась (ошибка в параметрах) |
pg_ctlcluster и pg_lsclusters (Debian/Ubuntu)
| Команда | Что делает |
|---|
pg_lsclusters | список кластеров: версия, имя, порт, статус (online/down), владелец, каталог данных, лог |
pg_ctlcluster 16 main start | запустить; код 2 — уже запущен |
pg_ctlcluster 16 main stop | остановить (режим fast); -m smart|fast|immediate — другой режим |
pg_ctlcluster 16 main restart | перезапустить |
pg_ctlcluster 16 main reload | перечитать конфиги без остановки |
pg_ctlcluster 16 main status | запущен ли (код 3 — нет) |
Конфиги кластера — в /etc/postgresql/16/main/, данные — в /var/lib/postgresql/16/main, журнал — в /var/log/postgresql/postgresql-16-main.log.
Примеры
# все утилиты — от имени системного пользователя postgres (вход по сокету без пароля)
sudo -u postgres createdb -O app shop # или: su postgres -c 'createdb shop'
# сервер поднят? — подходит для ожидания в скриптах
until pg_isready -q; do sleep 1; done
# запустить кластер без systemd и посмотреть статус
pg_ctlcluster 16 main start
pg_lsclusters
# дамп в SQL и восстановление через psql с остановкой на первой ошибке
sudo -u postgres pg_dump shop > shop.sql
sudo -u postgres createdb shop_copy
sudo -u postgres psql -v ON_ERROR_STOP=1 -q -d shop_copy -f shop.sql
# дамп в custom-формате и восстановление в новую базу
sudo -u postgres pg_dump -Fc -f shop.dump shop
sudo -u postgres createdb shop_copy2
sudo -u postgres pg_restore -d shop_copy2 shop.dump
# что внутри архива
pg_restore -l shop.dump
# восстановить поверх существующих объектов
sudo -u postgres pg_restore -c --if-exists -d shop_copy2 shop.dump
# только одна таблица, только данные, в виде INSERT
sudo -u postgres pg_dump -t items -a --inserts shop
# только структура всей базы
sudo -u postgres pg_dump -s shop > schema.sql
# параллельный дамп большой базы
sudo -u postgres pg_dump -Fd -j 4 -f shop_dir shop
# роли отдельно — pg_dump их не сохраняет
sudo -u postgres pg_dumpall -g > roles.sql
# сжатый дамп «на лету»
sudo -u postgres pg_dump shop | gzip > shop.sql.gz
Подводные камни
role "root" does not exist. Утилиты подключаются под ролью с именем текущего пользователя ОС. Запускай от postgres (sudo -u postgres …, su postgres -c '…') или указывай -U.-h 127.0.0.1 — это уже не сокет. По TCP обычно требуется пароль (fe_sendauth: no password supplied), даже если без -h всё работало: правила pg_hba.conf для сокета и TCP разные.psql < dump.sql не останавливается на ошибках и возвращает 0, даже если половина команд упала (relation … already exists). Добавляй -v ON_ERROR_STOP=1 — тогда код будет 3.pg_restore тоже не останавливается: по умолчанию пропускает ошибки, в конце пишет errors ignored on restore: N и возвращает 1. Нужен «всё или ничего» — -1 или -e.- Формат определяет инструмент. Plain-дамп
pg_restore не возьмёт (input file appears to be a text format dump. Please use psql), а custom-архив бесполезно кормить psql.
pg_restore -d в несуществующую базу падает. Сначала createdb, или -C -d postgres — но тогда база создастся с исходным именем из дампа, а не с тем, что передано в -d.-j у pg_dump работает только с -F d (parallel backup only supported by the directory format).pg_dump не сохраняет роли и права на уровне сервера. На новом сервере сначала восстанови pg_dumpall -g, иначе ALTER … OWNER TO app упадёт. Либо снимай дамп с -O -x.pg_isready не проверяет логин и базу. Он вернёт 0 для несуществующей базы и роли — это только «сервер слушает».systemctl в контейнере без systemd не работает. Используй pg_ctlcluster или service postgresql start.
См. также
psql · sqlite3 · redis-cli · gzip · sudo · cron