PostgreSQL — полезные команды

 Сентябрь 7th, 2015  Evgeniy Kamenev

Внутренние команды (\команда)   Помощь по внутренним командам

1	postgres=# \?

  Просмотр существующих баз данных

1	postgres=# \l

Просмотр существующих баз данных c более детальным выводом(размер, описание баз данных)

1	postgres=# \l+

Подключение к базе данных databasename

1	postgres=# \c databasename;

Просмотр существующих таблиц в текущей базе данных

1	postgres=# \dt

Просмотр списка всех таблиц с описанием в текущей базе данных

1	postgres=# \dt+

Просмотр структуры, индексов и прочих элемнтов таблицы

1	postgres=# \d tablename;

Просмотр списка всех пользователей и их привилегий

1	postgres=# \du

Просмотр списка доступных функций

1	postgres=# \df+

Выполнить команды из файла FILE

1	postgres=# \i /path/to/FILE

Сохранить результат запроса в файл FILE

1	postgres=# \o /path/to/FILE

Включить отображение времени выполнения запроса

1	postgres-# \timing

Timing is on. После чего все запросы станут отображаться в консольной утилите со временем выполнения (Отключение тайминга аналогично включению)

1	postgres-# \timing

Timing is off.   Помощь по SQL-командам

1	postgres=# \h

Например,просмотр синтаксиса создания базы данных

1	postgres=# \h create database

Полезные SQL команды     Создание пользователя myuser с паролем ‘123’

1	postgres-# CREATE USER myuser WITH PASSWORD '123';

Создание базы данных mytestdb с владельцем myuser

1	postgres-# CREATE DATABASE mytestdb OWNER = myuser;

Предоставление пользователю myuser всех прав доступа к базе mytestdb

1	postgres-# GRANT ALL PRIVILEGES ON database mytestdb TO myuser;

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

1	postgres=# DROP DATABASE mytestdb;

Удаление пользователя myuser

1	postgres=# DROP USER myuser;

Просмотр активных/текущих процессов

1	SELECT * from pg_stat_activity;

Обнуление/усечение таблицы

1	truncate tablename;

Просмотр размера базы данных

1	SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

Просмотр размера таблицы

1	SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

Просмотр размера таблицы без индексов

1	SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

Просмотр размера самых больших таблиц

1 2 3 4 5 6 7 8 9

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

Просмотр размера самой большой таблицы

1 2 3

SELECT relname AS "table_name", relpages AS "size_in_pages" FROM pg_class ORDER BY relpages DESC LIMIT 1;   site_categories |         2338

Результатом будет самая большая таблица (в примере site_categories) в страницах. Размер одной страницы равен 8KB (т.е. размер таблицы в примере — 18 MB)

Просмотр самых больших объектов в  базе данных

1 2 3 4 5 6 7

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

Просмотр запущенной версии PostgreSQL

1	postgres=# SELECT version();

Полезные PSQL-команды   Просмотр всех переменных и их значения в PostgreSQL

1	psql -U postgres -c "show all"

Вывод результата запроса в файл

1	psql -U postgres -c "show all" -o /tmp/all.txt

 

Бекап и восстановление с бекапа баз данных PostgreSQL  

 

Бекап баз данных

 

Бекап кокретной базы данных

1	pg_dump mydatabase > mydatabase.sql

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

1	pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f mydatabase.sql mydatabase

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

1	pg_dump -h localhost -p 5432 -U postgres -C -F p -b -v -f mydatabase.sql mydatabase

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

1	pg_dump -h localhost -p 5432 -U postgres -O -F p -c mydatabase | gzip -c > mydatabase.gz

Создание дампа c удаленного сервера

1	pg_dump –h <remotehost> -p <remoteport> -U postgres  mydatabase > mydatabase.sql

 

Создание дампа c удаленного сервера и заливка на локальный сервер

1	pg_dump -c –h <remotehost> -p <remoteport> -U postgres mydatabase | psql -U postgres mydatabase

Бекап всех баз даннях

1	pg_dumpall -U postgres > all.sql

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

1	grep "^[\]connect" all.sql

\connect db1

\connect db2

 

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

-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 с списком имен свойств.

 

Восстановление баз данных  

 

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

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

pg_restore — восстановление сжатых бекапов (tar);

 

Восстановлени кокретной базы данных

1	psql mydatabase < database.sql

(предварительно перед восстановлением дампа нужно создать новую базу данных mydatabase) (CREATE DATABASE mydatabase) Кроме того для корректного вливания дампа необходимо создать пользователя/пользователей,которые владеют базой данной/имеют привилегии на объекты базы данных (CREATE USER myuser)

 

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

1	# gunzip mydatabase.gz && psql -U postgres -d mydb -f mydatabase

Или

1	# gunzip -c mydatabase.gz | psql -U postgres -d mydb

или

1	# cat madatabase.gz | gunzip | psql -U postgres -d mydb

Восстановление всех баз данных

1	psql -U postgres -f all.sql

Восстановление бекапа c пмощью pg_restore имеющего пользовательский(custom)формат созданного командой

1	# pgdump -Fc mydatabase > mydatabase.sql

1	# pg_restore -d mydatabase mydatabase.sql

Мониторинг PostgreSQL   Просмотр активных/текущих процессов

 

1	postgres=# SELECT * FROM pg_stat_activity;

 Просмотр активных блокировок

1	postgres=# SELECT * FROM pg_locks;

 Использование утилиты pg_top http://ptop.projects.pgfoundry.org/

1 2 3 4 5

apt-get install pgtop   pg_top –help   pg_top -U postgres -s 2