Шпаргалка по PostgreSQL

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

Посмотрите заметку об установке PostgreSQL на WSL. Кроме установки там есть информация как создать пользователя и базу данных.

Утилита psql

sudo -u postgres psql - войти в утилиту

Обратите внимание, команды самой утилиты psql начинаются с "\"

Список команд psql
\l Список баз данных
\с db_name Подключение к определенной базе данных
\dt Список таблиц в текущей БД
\d table_name Получение информации о конкретной таблице
\dn Список всех схем  в текущей БД
\df Список всех хранимых процедур и функций  в текущей БД
\dv Список всех представлений  в текущей БД
\q Выход из консоли postgres (psql)
\h Помощь по командам SQL
\? Помощь по командам psql

Создание резервных копий

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

Например, нужно создать дамп бд blog от пользователя django (в этой заметке я создал такую БД с таким пользователем), для этого в консоли выполняем команду:

sudo pg_dump -U django -h localhost -Fc blog > blog.dump

В папке откуда мы выполнили команду, должен появится файл blog.dump

Ниже перечислены опции утилиты pg_dump:

-d <имя_бд>, —dbname=имя_бд — база данных, к которой выполняется подключение.

-h <сервер>, —host=сервер — имя сервера.

-p <порт>, —port=порт — порт для подключения.

-U <пользователь>, —username=пользователь) — учетная запись, используемое для подключения.

-w, —no-password — деактивация требования ввода пароля.

-W, —password — активация требования ввода пароля.

—role=имя роли — роль, от имени которой генерируется резервная копия.

-a, —data-only — вывод только данных, вместо схемы объектов (DDL).

-b, —blobs — параметр добавляет в выгрузку большие объекты.

-c, —clean — добавление команд DROP перед командами CREATE в файл резервной копии.

-C, —create — генерация реквизитов для подключения к базе данных в файле резервной копии.

-E <кодировка>, —encoding=кодировка — определение кодировки резервной копии.

-f <файл>, —file=файл — задает имя файла, в который будет сохраняться вывод утилиты.

-F <формат>, —format=формат — параметр определяет формат резервной копии. Доступные форматы:

  • p, plain) — формирует текстовый SQL-скрипт;
  • c, custom) — формирует резервную копию в архивном формате;
  • d, directory) — формирует копию в directory-формате;
  • t, tar) — формирует копию в формате tar.

-j <число_заданий>, —jobs=число_заданий — параметр активирует параллельную выгрузку для одновременной обработки нескольких таблиц (равной числу заданий). Работает только при выгрузке копии в формате directory.

-n <схема>, —schema=схема — выгрузка в файл копии только определенной схемы.

-N <схема>, —exclude-schema=схема — исключение из выгрузки определенных схем.

-o, —oids — добавляет в выгрузку идентификаторы объектов (OIDs) вместе с данными таблиц.

-O, —no-owner — деактивация создания команд, определяющих владельцев объектов в базе данных.

-s, —schema-only —добавление в выгрузку только схемы данных, без самих данных.

-S <пользователь>, —superuser=пользователь — учетная запись привилегированного пользователя, которая должна использоваться для отключения триггеров.

-t <таблица>, —table=таблица — активация выгрузки определенной таблицы.

-T <таблица>, —exclude-table=таблица —исключение из выгрузки определенной таблицы.

-v, —verbose — режим подробного логирования.

-V, —version — вывод версии pg_dump.

-Z 0..9, —compress=0..9 — установка уровня сжатия данных. 0 — сжатие выключено.

Восстановление из резервной копии

Для восстановлении базы данных PostgreSQL из резервной копии, я использую утилиту pg_restore.

Восстановим дамп базы из файла  blog.dump созданный на предыдущем шаге, для этого введем команду:

sudo pg_restore -v --clean --no-owner --host=localhost --port=5432 --username=django --dbname=blog blog.dump

Ниже перечислены опции утилиты pg_restore:

-h <сервер>, —host=сервер — имя сервера, на котором работает база данных.

-p <порт>, —port=порт — TCP-порт, через база данных принимает подключения.

-U <пользователь>, —username=пользователь — имя пользователя для подключения..

-w, —no-password — деактивация требования ввода пароля.

-W, —password — активация требования ввода пароля.

—role=имя роли — роль, от имени которой выполняется восстановление резервная копия.

<имя_файла> — расположение восстанавливаемых данных.

-a, —data-only — восстановление данных без схемы.

-c, —clean — добавление операторов DROP перед операторами CREATE.

-C, —create — создание базы данных перед запуском процесса восстановления.

-d <имя_бд>, —dbname=имя_бд — имя целевой базы данных.

-e, —exit-on-error — завершение работы в случае возникновения ошибки при выполнении SQL-команд.

-f <имя_файла>, —file=имя_файла — файл для вывода сгенерированного скрипта.

-F <формат>, —format=формат — формат резервной копии. Допустимые форматы:

  • p, plain — формирует текстовый SQL-скрипт;
  • c, custom — формирует резервную копию в архивном формате;
  • d, directory — формирует копию в directory-формате;
  • t, tar — формирует копию в формате tar.

-I <индекс>, —index=индекс — восстановление только заданного индекса.

-j <число-заданий>, —jobs=число-заданий — запуск самых длительных операций в нескольких параллельных потоках.

-l, —list) — активация вывода содержимого архива.

-L <файл-список>, —use-list=файл-список — восстановление из архива элементов, перечисленных в файле-списке в соответствующем порядке.

-n <пространство_имен>, —schema=схема — восстановление объектов в указанной схеме.

-O, —no-owner — деактивация генерации команд, устанавливающих владение объектами по образцу исходной базы данных.

-P <имя-функции(тип-аргумента[, …])>, —function=имя-функции(тип-аргумента[, …]) — восстановление только указанной функции.

-s, —schema-only — восстановление только схемы без самих данных.

-S <пользователь>, —superuser=пользователь — учетная запись привилегированного пользователя, используемая для отключения триггеров.

-t <таблица>, —table=таблица — восстановление определенной таблицы.

-T <триггер>, —trigger=триггер — восстановление конкретного триггера.

-v, —verbose — режим подробного логирования.

-V, —version — вывод версии утилиты pg_restore.