PostgreSQL – dictionary psql, pg_dump, pg_restore

 

Индексы PostgreSQL

Все команды запускаются под пользователем postgres (postgresql-суперпользователь)

psql -l – список баз данных.

psql -d dbname – подключение к БД dbname.

psql -f file.sql – выполнение команд из файла file.sql.

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

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

Просмотр списка и путей к конфигурационным файлам

Список активных соединений с информацией о: pid процесса, выполняющегося запроса, пользователя, базы данных.

Создание индексов

Команды psql

\c dbname – подсоединение к БД dbname.

\l – список баз данных.

\dt – список всех таблиц.

\d table – структура таблицы table.

\du – список всех пользователей и их привилегий.

\dt+ – список всех таблиц с описанием.

\dt *s* – список всех таблиц, содержащих s в имени.

\i FILE – выполнить команды из файла FILE.

\o FILE – сохранить результат запроса в файл FILE.

\a – переключение между режимами вывода: с/без выравнивания.

Бекап и восстановление таблиц

В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpallpg_dump используется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем).

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

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

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

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

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

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

-h host – хост, если не указан то используется localhost или значение из переменной окружения PGHOST.

-p port – порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.

-u – пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.

-a, --data-only – дамп только данных, по-умолчанию сохраняются данные и схема.

-b – включать в дамп большие объекты (blog’и).

-s, --schema-only – дамп только схемы.

-C, --create – добавляет команду для создания БД.

-c – добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

-O – не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

-F, --format {c|t|p} – выходной формат дампа, custom, tar, или plain text.

-t, --table=TABLE – указываем определенную таблицу для дампа.

-v, --verbose – вывод подробной информации.

-D, --attribute-inserts – дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

В PostgreSQL есть две утилиты для восстановления базы из бекапа.

  • psql – восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
  • pg_restore – восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-архива нам понадобится сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore –dbname=mydb –jobs=4 –verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

 

Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции --section

Обслуживание таблицы

Перенос директории с данным (data directory)

Узнать текущий путь

Создадим новую директорию, назначим пользователя и инициализируем

Теперь надо подправить файл с сервисом, который стартует postgresql

Очищение таблицы

Очищение таблицы tablename и обнуление счетчика с ID.

CASCADE нужен на случай если tablename связана с другой таблицей.

Удаление NULL у поля

Утилиты

pgcli утилита командной строки с авто-дополнениям и подсветкой синтаксиса.

Установка

Запуск

update

размер базы и таблицы

Удаление базы

Если ошибка

проваливаемся в psql

Возвращаемся в bash и пытаемся удалить снова

 

Максимальное количество соединений

Количество подключенных к серверу соединений

 

Индексы PostgreSQL

haru-atari com/ru/blog/6-indexes-in-postgresql-full-manual

Создание индекса

Удаление индекса

Пересоздание индексов (реиндекс)

 

Членство в роли postgrespro ru/docs/postgresql/10/role-membership