Category Archive: PostgreSQL

Обработка казахский букв в PostgreSQL

22 ноября, 2023 | BY | Category: PostgreSQL, SQL | Нет комментариев »

С хранением казахских символов в PostgreSQL нет проблем, но могут возникнуть проблемы с сортировкой данных и некоторыми строковыми функциями, например, lower и upper, которые не смогут преобразовать символы в нужный регистр. Для решения проблемы можно поменять для колонки правила сортировки и чувствительности к регистру. 

ALTER TABLE your_table
ALTER COLUMN your_column
SET DATA TYPE text COLLATE "kk-KZ-x-icu"

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

create table your_table(
	your_column text COLLATE "kk-KZ-x-icu"
)

Аудит PostgreSQL

12 февраля, 2022 | BY | Category: Audit, pg_audit, PostgreSQL | Нет комментариев »

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

База данных была на PostgreSQL, и особого выбора, кроме как pgAudit, не было.

Приступим к установке и настройке pgAudit.

Расширение pgAudit инсталируется из исходных кодов.

Установка

  1. Склонируйте официальный репозиторий: 
    git clone https://github.com/pgaudit/pgaudit.git
  2. Открываем созданную директорию pgaudit: 
    cd pgaudit
  3. Переключите ветку на необходимый релиз (вашу версию PostgreSQL) 
    git checkout REL_12_STABLE
  4. Сделайте сборку и установку pgAudit (обратите внимание на правильность пути к утилите pg_config): 
    sudo make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-12/bin/pg_config
    Может потребоваться установка утилиты make и других зависимостей.

Настройка

  1. Внесем корректировки в конфигурационный файл /var/lib/pgsql/12/data/postgresql.conf: 
    shared_preload_libraries = 'pgaudit'
  2. Перезапустите PostgreSQL 
    sudo service postgresql-12.service restart
  3. Зайдите в консоль psql под пользователем postgres 
    sudo -u postgres psql
  4. Установить расширение pgaudit: 
    CREATE EXTENSION pgaudit
  5. В конфигурационном файле /var/lib/pgsql/12/data/postgresql.conf с помощью опции pgaudit.log укажите какие классы запросов/команд необходимо аудировать (весь перечень и другие опции можно посмотреть в описании на github): 
    pgaudit.log = 'write, role, ddl'
  6. В конфигурационном файле postgresql.conf укажите роль, которая будет использоваться для аудита в опции pgaudit.role: 
    pgaudit.role = 'auditor'
  7. В конфигурационном файле postgresql.conf дополните префикс логирования следующим образом: 
    log_line_prefix = '%m %d %u %h '

    Префикс содержит следующие данные и добавляется к каждой строке лога: дату(%m), название базы данных (%d), логин пользователя (%u) и удаленный адрес пользователя (%h).

  8. После изменения настроек postgresql.conf не забывайте перезагружать PostgreSQL, чтобы настройки применились.
  9. Создайте роль auditor: 
    create role auditor;

    Аудит настраивается путем предоставления/отзыва прав на определенные операции для роли auditor.

  10. Добавить аудит на определенные действия можно следующими командами: 
    grant insert ON your_table to auditor;
grant insert ON your_table to auditor;
grant update ON your_table to auditor;
grant delete ON your_table to auditor;
  11. Убрать аудит на определенные действия можно следующими командами: 
    revoke insert ON your_table to auditor;
revoke insert ON your_table to auditor;
revoke update ON your_table to auditor;
revoke delete ON your_table to auditor;

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

28 апреля, 2021 | BY | Category: Backup, PostgreSQL, Скрипты | Нет комментариев »

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

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

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

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

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

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

  1. Создает копию указанной базы данных
  2. Упаковывает копию в zip-архив
  3. Удаляет копию для экономии места
Создайте скрипт backup.sh со следующим содержимым 
if [ -z "$1" ]; then
	echo "Error! Please select database."
	echo "Example:"
	echo "         $0 "
	exit 1
fi
DB_NAME=$1
BACKUP_DIR="/root/scripts/"
DUMP_NAME="$BACKUP_DIR$DB_NAME.dump"
DATESTAMP=`date +%F_%H%M%S`
ZIP_NAME="$BACKUP_DIR${DB_NAME}_$DATESTAMP.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 -j $ZIP_NAME $DUMP_NAME \
        && rm $DUMP_NAME

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

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

chmod +x backup.sh

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

./backup.sh

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

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

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

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

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

sudo crontab -e

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

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

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

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

26 апреля, 2019 | BY | Category: PostgreSQL, Базы данных | 1 комментарий »
Надыбал такой запрос, который вытягивает размеры таблиц и их индексов, а также суммарный размер: 
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

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

8 июня, 2018 | BY | Category: PostgreSQL | Нет комментариев »
Сегодня возникла необходимость просмотреть права доступа всех пользователей. Быстрого и подходящего решения не получилось найти. В итоге нашел пример запроса выводящего привилегии для одной таблицы, а дальше соединил этот запрос с запросом всех таблиц. В итоге получился следующий запрос. 
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
Результат запроса содержит логин пользователя, тип привилегии и название таблицы:
TAG: , ,

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

5 апреля, 2018 | BY | Category: PostgreSQL | Нет комментариев »
Склонируйте репозиторий 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

19 декабря, 2017 | BY | Category: PostgreSQL | Нет комментариев »
Давно не писал... На прошлой неделе поймал такую ошибку: 
ERROR: target lists can have at most 1664 entries
Данная ошибка возникла из-за того, что появилось много зависимостей между сущностями(Entity). Когда идет запрос данных через Hibernate, то JOIN'ится много таблиц, и общее количество получаемых полей начинает превышать 1664. В нашем случае это было около 1700 полей. Такое разумное ограничение есть у PostgreSQL. Решить можно такими путями: 1) Переписать запросы, взяв только используемые поля; 2) Сделать некоторые поля Lazy. Скорее всего, такая проблема и у вас решится вторым путем.
Naik's blog is proudly powered by WordPress
designed by WPart.org