Все команды запускаются под пользователем postgres (postgresql-суперпользователь)
$ su - 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 -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 связана с другой таблицей.
Удаление NULL у поля
ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;
Утилиты
pgcli утилита командной строки с авто-дополнениям и подсветкой синтаксиса.
Установка
pip install pgcli
Запуск
pgcli -U postgres -W dbname
Дополнительное чтиво