PostgreSQL: справочник по командам psql, pg_dump, pg_restore

Все команды запускаются под unix-пользователем с именем postgres. Этот unix-пользователь является суперпользователем в рамках установленной в системе БД PostgreSQL. То есть, чтобы войти в текстовую консоль управления PostgreSQL, нужно вначале переключиться на unix-пользователя postgres, и только тогда текстовая консоль (по-сути, это бинарник с именем psql) буден доступен для исполнения. Для переключения на пользователя postgres используется команда:

$ su - postgres

Убедиться, что переключение на данного пользователя цспешно состоялось, можно с помощью команды:

$ whoami

postgres

Основные команды

• 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-файл.

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

psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

# или список всех конфигурационных параметров

psql > show all;

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

psql > SELECT * FROM pg_stat_activity;

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

# primary key

psql > ALTER TABLE tableName ADD PRIMARY KEY (id);

# unique index

psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Команды 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_dumpall. pg_dump используется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем).

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

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

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

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

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

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

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

pg_dump -a -t table_name -f file_name database_name

Создание резервной копии с сжатием в 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) объектов (таблиц, видов и т.д.).
• -O - не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
• -F, --format {c|t|p} - выходной формат дампа, custom, tar, или plain text.
• -t, --table=TABLE - указываем определенную таблицу для дампа.
• -v, --verbose - вывод подробной информации.
• -D, --attribute-inserts - дамп используя команду INSERT с списком имен свойств.

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

pg_dumpall > all.sql

# проверка бекапа

grep "^[\]connect" all.sql

\connect db1

\connect db2

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

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

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

psql -h localhost -U someuser -d dbname -f mydb.sql

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

psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sql

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

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

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

gunzip mydb.gz

psql -U postgres -d mydb -f mydb

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

# создаем БД

CREATE DATABASE mydb2;

# восстанавливаем

pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

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

VACUUM ANALYZE table;

REINDEX DATABASE dbName;

REINDEX TABLE tabName;

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

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

# способ 1

$ su - postgres

$ psql

psql > SHOW data_directory;

# способ 2

$ ps ax | grep 'postgres -D'

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

mkdir -p /pathto/postgresql/data

chown -R postgres:postgres /pathto/postgresql

su - postgres

initdb -D /pathto/postgresql/data

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

# под arch linux

sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service

Environment=PGROOT=/pathto/postgresql/

PIDFile=/pathto/postgresql/data/postmaster.pid

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

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

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

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

Утилиты

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

Установка:

pip install pgcli

Запуск

pgcli -U postgres -W dbname