Работа с базами данных на PostgreSQL

Заметки по работе с базами данных на PostgreSQL.

- Подключение к postgresql 
- Выбор базы данных для работы 
- Размер текущей базы данных Postgres 
- Размер 20-м самых больших таблиц 
- Узнать размер всех таблиц 
- Узнать общий размер таблиц 
- Размер базы данных 
- Размер таблицы 
- Размер всех баз данных на сервере 
- Количество записей в таблице

Сжатие базы данных 
Создание резервных копий 

Базовая команда 
Пользователь и пароль 
Сжатие данных 
Скрипт для автоматического резервного копирования 
Создание дампа на удаленном сервере 
Дамп определенной таблицы 
Размещение каждой таблицы в отдельный файл 
Только схемы 
Только данные 
Не текстовые форматы дампа 
Использование pg_basebackup 
pg_dumpall

Восстановление 

Возможные ошибки 

 

Подключение к postgresql

psql -U postgres

 

Выбор базы данных для работы

c DatabaseName

 

Размер текущей базы данных Postgres

select pg_size_pretty(pg_database_size(current_database()));

 

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

Запрос выводит 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; 

 

Узнать размер всех таблиц

Запрос выведет размер всех таблиц в базе.

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; 

 

Узнать общий размер таблиц

Этот запрос показывает общий размер таблиц, включая все индексы в текущей базе данных:

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 pg_size_pretty( pg_database_size( 'sample_db' ) );

 

Размер таблицы

SELECT pg_size_pretty( pg_total_relation_size( 'table' ) );

 

Размер всех баз данных на сервере

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;

 

Количество записей в таблице

SELECT count(*) FROM my_table;

 

Сжатие базы данных

Сильно разросшиеся базы данных postgresql можно сжать

1. Делаем бэкап базы или бэкапим /var/lib/postgresql
2. Подключаеся в postgres

su postgres
psql

смотрим список баз

\l

подключаемся к базе

\c database_name

Выполняем сжатие базы

vacuum full verbose;

Перестраиваем индексы

reindex database database_name;

 

Создание резервных копий

Базовая команда

Синтаксис:

pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>

Пример:

pg_dump users > /tmp/users.dump

 

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

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

 

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать дамп в архив:

pg_dump users | gzip > users.dump.gz

 

Скрипт для автоматического резервного копирования

#!/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB ( -name "*-1[^5].*" -o -name "*-[023]?.*" ) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz

unset PGPASSWORD

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к CУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи. 

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

crontab -e

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

 

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

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

 

Дамп определенной таблицы

Запускается с опцией -t или --table

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

 

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с использованием ключа -d

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

 

Только схемы

Для резервного копирования без данных (только таблицы и их структуры)

pg_dump --schema-only users > /tmp/users.schema.dump

 

Только данные

pg_dump --data-only users > /tmp/users.data.dump

 

Не текстовые форматы дампа

Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.

Бинарный с компрессией:

pg_dump -Fc users > users.bak

Тарбол:

pg_dump -Ft users > users.tar

Directory-формат:

pg_dump -Fd users > users.dir

 

Использование pg_basebackup

pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.

pg_basebackup -h node1 -D /backup

* в данном примере создается резервная копия для сервера node1 с сохранением в каталог /backup.

 

pg_dumpall

Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.

pg_dumpall > cluster.bak

Утилиту удобно использовать с ключом -g (--globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).

Для создание резервного копирования со сжатием:

pg_dumpall | gzip > cluster.tar.gz

 

Восстановление

Может понадобиться создать базу данных. Это можно сделать SQL-запросом:

=# CREATE DATABASE users WITH ENCODING='UTF-8';

* где users — имя базы; UTF-8 — используемая кодировка.

Базовая команда

Синтаксис:

Пример:

 

С авторизацией

При необходимости авторизоваться при подключении к базе вводим:

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

 

Из файла gz

Сначала распаковываем файл, затем запускаем восстановление:

Или одной командой:

zcat users.dump.gz | psql users

 

Определенную базу

Если резервная копия делалась для определенной базы, запускаем восстановление:

Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:

 

Определенную таблицу

Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:

Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:

pg_restore -a -t students users.dump

 

Возможные ошибки

Input file appears to be a text format dump. please use psql.

Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.
Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.

No matching tables were found

Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.
Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.

Too many command-line arguments

Причина: Утилита pg_dump чувствительна к лишним пробелам.
Решение: проверьте, что нет лишних пробелов.

Aborting because of server version mismatch

Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.
Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql//bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.

No password supplied

Причина: нет системной переменной PGPASSWORD или она пустая.
Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).