Шпаргалка по основным командам PostgreSQL

Вся работа с PostgreSQL осуществляется под пользователем postgres.

$ sudo su postgres

Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа — psql.

Основные команды PostgreSQL в интерактивном режиме:

  • \connect db_name – подключиться к базе с именем db_name
  • \dx - список расширений
  • \du – список пользователей
  • \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
  • \di – индексы
  • \ds – последовательности
  • \dt – список таблиц
  • \dt+ — список всех таблиц с описанием
  • \dt *s* — список всех таблиц, содержащих s в имени
  • \dv – представления
  • \S – системные таблицы
  • \d+ – описание таблицы
  • \o – пересылка результатов запроса в файл
  • \l – список баз данных
  • \i – читать входящие данные из файла
  • \e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
  • \d “table_name” – описание таблицы
  • `\i запуск команды из внешнего файла, например \i /my/directory/my.sql
  • \pset – команда настройки параметров форматирования
  • \echo – выводит сообщение
  • \set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет).
  • \? – справочник psql
  • \help – справочник SQL
  • \q (или Ctrl+D) – выход с программы

Работа с PostgreSQL из командной строки:

  • -c (или –command) – запуск команды SQL без выхода в интерактивный режим
  • -f file.sql — выполнение команд из файла file.sql`
  • -l (или –list) – выводит список доступных баз данных
  • -U (или –username) – указываем имя пользователя (например postgres)
  • -W (или –password) – приглашение на ввод пароля
  • -d dbname — подключение к БД dbname`
  • -h – имя хоста (сервера)
  • -s – пошаговый режим, то есть, нужно будет подтверждать все команды
  • -S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
  • -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c "CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);” — выполнение команды в базе `dbname`.

psql -d dbname -H -c “SELECT * FROM my” -o my.html — вывод результата запроса в html-файл.

Утилиты (программы) PosgreSQL:

  • createdb и dropdb – создание и удаление базы данных (соответственно)
  • createuser и dropuser – создание и удаление пользователя (соответственно)
  • pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
  • postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
  • initdb – создание новых кластеров PostgreSQL
  • initlocation – программа для создания каталогов для вторичного хранения баз данных
  • vacuumdb – физическое и аналитическое сопровождение БД
  • pg_dump – архивация и восстановление данных
  • pg_dumpall – резервное копирование всего кластера PostgreSQL
  • pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

  1. Создание бекапа базы mydb, в сжатом виде.

    pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

  2. Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД.

    pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

  3. Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments.

    pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

  4. Дамп данных только одной, конкретной таблицы. Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечисляются с помощью ключа -t для каждой таблицы.

    pg_dump -a -t table_name -f file_name database_name

  5. Создание резервной копии с сжатием в .gz.

    pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Список наиболее часто используемых опций:

  • -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
  • -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
  • -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
  • -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
  • -b — включает в дамп большие объекты (blog’и).
  • -s, —schema-only — дамп только схемы.
  • -C, —create — добавляет команду для создания БД.
  • -c — добавляет команды для удаления (drop) объектов базы данных.
  • -v ON_ERROR_STOP=0 — игнорирование ошибок во время восстановления.

Пример полной команды:

psql -U postgres -h localhost -p 5432 -d my_database -f /backups/backup.sql