Category Archive: PostgreSQL

Бэкапирование базы данных PostgreSQL

Все мы знаем, что нужно периодически делать резервные копии баз данных.

И почему-то делаем это только для боевых баз данных, но не для тестовых.

Хотя потеря тестовых данных тоже критична, т.к. на восстановление данных может уйти достаточное количество времени.

База данных может повредиться по многим причинам.

Например, операции update или delete с некорректным условием и вообще без условия, каскадные удаления и т.д.

Ниже будет приведен скрипт, который делает следующие операции:

  1. Создает копию указанной базы данных
  2. Упаковывает копию в zip-архив
  3. Удаляет копию для экономии места
Создайте скрипт backup.sh со следующим содержимым
if [ -z "$1" ]; then
	echo "Error! Please select database."
	echo "Example:"
	echo "         $0 <YOUR_DATABASE>"
	exit 1
fi
DB_NAME=$1
BACKUP_DIR="/root/scripts/"
DUMP_NAME="$BACKUP_DIR$DB_NAME.dump"
ZIP_NAME="$BACKUP_DIR$DB_NAME.zip"
echo "DB_NAME   : $DB_NAME"
echo "BACKUP_DIR: $BACKUP_DIR"
echo "DUMP_NAME : $DUMP_NAME"
echo "ZIP_NAME  : $ZIP_NAME"
 
sudo -u postgres pg_dump $DB_NAME > $DUMP_NAME \
        && zip $ZIP_NAME $DUMP_NAME \
        && rm $DUMP_NAME

BACKUP_DIR - путь к директории, где планируется хранить резерные копии баз данных.

Не забудьте дать скрипту право на исполнение (executable):

chmod +x backup.sh

Скрипту передается название базы данных следующим образом:

./backup.sh <your_database>

Скрипт можно доработать под ваши нужны.

Добавить дополнительное копирование на другой сервер.

Изменить способ сжатия копий. Например, для сжатия больших файлов использовать 7-Zip.

Теперь сделаем запуск скрипта ежедневно в 4 утра.

Выполните команду:

sudo crontab -e

Обратите внимание, что скрипт запускается через sudo. Это сделано чтобы скрипт запускался от имени администратора, иначе вы настроите запуск от текущего пользователя. Добавьте следующую запись:

0 4 * * * /your_path/backup.sh 
Скрипт восстановления из бэкапа:
psql your_database < your_database.dump

p.s. Поменьше вам восстанавливаться из бэкапов =)

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

Надыбал такой запрос, который вытягивает размеры таблиц и их индексов, а также суммарный размер:
SELECT
    TABLE_NAME,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        TABLE_NAME,
        pg_table_size(TABLE_NAME) AS table_size,
        pg_indexes_size(TABLE_NAME) AS indexes_size,
        pg_total_relation_size(TABLE_NAME) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

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

Сегодня возникла необходимость просмотреть права доступа всех пользователей. Быстрого и подходящего решения не получилось найти. В итоге нашел пример запроса выводящего привилегии для одной таблицы, а дальше соединил этот запрос с запросом всех таблиц. В итоге получился следующий запрос.
SELECT rtg.grantee, rtg.privilege_type, t.table_name 
FROM information_schema.tables t
JOIN information_schema.role_table_grants rtg ON t.table_name = rtg.table_name
ORDER BY rtg.grantee
Результат запроса содержит логин пользователя, тип привилегии и название таблицы:

Установка PostgreSQL из исходников на Ubuntu 16.04

Склонируйте репозиторий PostgreSQL
git clone https://github.com/postgres/postgres.git
После окончания загрузки файлов перейдите в созданную директорию
cd postgres
Переключитесь на ветку REL_10_STABLE
git checkout REL_10_STABLE
Перед непосредственно сборкой проект нужно установить вспомогательно ПО.
sudo apt-get install libreadline-dev
sudo apt-get install bison
sudo apt-get install flex
Сконфигурируйте проект
./configure
Соберите проект
make
Установить postgres
sudo make install
Переключитесь в режим супер-пользователя:
sudo su
Добавьте нового пользователя postgres:
adduser postgres
Задайте пароль для пользователя postgres:
passwd postgres
Создайте директорию для данных postgres:
mkdir /usr/local/pgsql/data
Поменяйте владельца и группу для директории:
chown postgres:postgres /usr/local/pgsql/data
Далее поменяйте пользователя на postgres:
su - postgres
Проинициализируйте новый кластер баз данных:
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

Target lists can have at most 1664 entries в PostgreSQL

Давно не писал... На прошлой неделе поймал такую ошибку:
ERROR: target lists can have at most 1664 entries
Данная ошибка возникла из-за того, что появилось много зависимостей между сущностями(Entity). Когда идет запрос данных через Hibernate, то JOIN'ится много таблиц, и общее количество получаемых полей начинает превышать 1664. В нашем случае это было около 1700 полей. Такое разумное ограничение есть у PostgreSQL. Решить можно такими путями: 1) Переписать запросы, взяв только используемые поля; 2) Сделать некоторые поля Lazy. Скорее всего, такая проблема и у вас решится вторым путем.