PostgreSQL — полезные команды | Записки системного администратора

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

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

Evgeniy Kamenev

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

postgres=# \?

1	postgres=# \?

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

postgres=# \l

1	postgres=# \l

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

postgres=# \l+

1	postgres=# \l+

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

postgres=# \c databasename;

1	postgres=# \c databasename;

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

postgres=# \dt

1	postgres=# \dt

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

postgres=# \dt+

1	postgres=# \dt+

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

postgres=# \d tablename;

1	postgres=# \d tablename;

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

postgres=# \du

1	postgres=# \du

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

postgres=# \df+

1	postgres=# \df+

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

postgres=# \i /path/to/FILE

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

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

postgres=# \o /path/to/FILE

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

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

postgres-# \timing

1	postgres-# \timing

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

postgres-# \timing

1	postgres-# \timing

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

postgres=# \h

1	postgres=# \h

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

postgres=# \h create database

1	postgres=# \h create database

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

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

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

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

postgres-# CREATE DATABASE mytestdb OWNER = myuser;

1	postgres-# CREATE DATABASE mytestdb OWNER = myuser;

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

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

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

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

postgres=# DROP DATABASE mytestdb;

1	postgres=# DROP DATABASE mytestdb;

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

postgres=# DROP USER myuser;

1	postgres=# DROP USER myuser;

Просмотр активных/текущих подключений

SELECT * from pg_stat_activity;

1	SELECT * from pg_stat_activity;

Просмотр количества активных/текущих подключений

SELECT count(*) from pg_stat_activity;

1	SELECT count(*) from pg_stat_activity;

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

truncate tablename;

1	truncate tablename;

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

SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

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

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

SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

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

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

SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

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

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

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;

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;

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

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

site_categories |         2338

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)

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

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;

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

postgres=# SELECT version();

1	postgres=# SELECT version();

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

psql -U postgres -c "show all"

1	psql -U postgres -c "show all"

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

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

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

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

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

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

pg_dump mydatabase > mydatabase.sql

1	pg_dump mydatabase > mydatabase.sql

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

pg_dumpall -U postgres > all.sql

1	pg_dumpall -U postgres > all.sql

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

grep "^[\]connect" 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);

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

psql mydatabase < database.sql

1	psql mydatabase < database.sql

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

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

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

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

Или

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

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

или

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

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

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

psql -U postgres -f all.sql

1	psql -U postgres -f all.sql

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

# pgdump -Fc mydatabase > mydatabase.sql

1	# pgdump -Fc mydatabase > mydatabase.sql

# pg_restore -d mydatabase mydatabase.sql

1	# pg_restore -d mydatabase mydatabase.sql

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

postgres=# SELECT * FROM pg_stat_activity;

1	postgres=# SELECT * FROM pg_stat_activity;

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

postgres=# SELECT * FROM pg_locks;

1	postgres=# SELECT * FROM pg_locks;

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

apt-get install pgtop

pg_top –help

pg_top -U postgres -s 2

apt-get install pgtop

pg_top –help

pg_top -U postgres -s 2

Источники:

http://proft.me/2013/06/9/postgresql-spravochnik-po-komandam-psql-pg_dump/

https://romantelychko.com/blog/1211/

http://proft.me/2010/05/30/monitoring-resursov-v-postgressql/

Опубликовано в рубрике Other

Метки: pg_dump, postgresql, psql

Комментирование и размещение ссылок запрещено.

Комментарии закрыты.

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

Страницы

Свежие записи

Архивы

Рубрики

Админ-панель