Работа с PostgreSQL

Материал из darklurker wiki
Перейти к: навигация, поиск

Установка, авторизация, настройка

Установка postgresql:

sudo apt-get install postgresql

По умолчанию доступ к СУБД имеет пользователь postgres. Заходим под ним:

sudo -u postgres psql

Авторизация под иным пользователем:

psql -h localhost mydatabase myuser

Интерактивный (shell) режим

   \connect db_name – подключиться к базе с именем db_name
   \du – список пользователей
   \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
   \di – индексы
   \ds – последовательности
   \dt – список таблиц
   \dt+ — список всех таблиц с описанием
   \dt *s* — список всех таблиц, содержащих s в имени
   \dv – представления
   \dS – системные таблицы
   \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)

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

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

pg_dump -h localhost -p 5440 -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

Восстановление таблиц из резервных копий (бэкапов):

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

Пользователи

Создание пользователя:

CREATE USER myuser WITH password 'password';

Назначение прав пользователю:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Назначение прав администратора:

ALTER USER myuser WITH SUPERUSER;

Отозвать права администратора:

ALTER USER myuser WITH NOSUPERUSER;

Дампы

Снять дамп:

pg_dump -h localhost -U myuser mydatabase > mydump.sql

Импортировать дамп в БД:

psql -h localhost -U username dbname < dump.sql

SQL

Синтаксис SQL-запросов для реляционных БД практически идентичен, можно посмотреть здесь.

Полезности

Физический размер файлов (хранилища) базы данных:

SELECT pg_size_pretty(pg_database_size(current_database()));   //current_database() можно заменить на имя БД, например 'mydb'

Получить перечень таблиц:

SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');

Подключенные пользователи:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Активность пользователя:

SELECT datname FROM pg_stat_activity WHERE usename = 'myuser';

Удаление дублирующих строк:

DELETE FROM table WHERE ctid NOT IN
(SELECT max(ctid) FROM table GROUP BY table.*);

Безопасное изменение типа поля:

ALTER TABLE table ALTER COLUMN column TYPE integer USING (column::integer);

Подсчёт количества строк в таблице:

SELECT count(*) FROM table;

Подсчёт строк без учета NULL:

SELECT count(col_name) FROM table;

Подсчёт только уникальных строк по указанному полю:

SELECT count(distinct col_name) FROM table;

Просмотр и завершение исполняемых запросов:

SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Остановить конкретный запрос:

SELECT pg_cancel_backend(procpid);

Прекратить работу запроса:

SELECT pg_terminate_backend(procpid);

Изменение настроек без перезагрузки:

SELECT pg_reload_conf();